Skip to the work

The New York Sun: subscription sources

Unknown subscription sources cut from over 90% to about 7%.

nysun.comThe New York Sun, opens in a new tab

A checkout change hid where new subscribers came from. I rebuilt the SQL report so the finance team could see which sources brought them in.

Tools. Snowflake SQL, Stripe metadata, Equals.

Run the rebuilt report and watch each subscription find its source.Sample

90%+of new subscriptions: source unknown

Why sources went missing

A checkout update saved each subscriber’s source under a new name.

Old report: didn’t look there, so Unknown.

Unknown
Email
Search
Social
Direct
Partner

No AI here. Eight rewrites of the SQL that reads the payment records.

How it was built

What The Sun got

  • Over 90% to about 7% unknown. The conservative March result, after a checkout change broke source reporting.
  • Useful marketing reports again. The finance team could trace subscriptions to their sources.
  • No AI needed. SQL repaired the report. Missing evidence stayed missing.

How I built it

  1. Trace the missing sources

    A checkout change renamed the fields in Stripe. The report still looked for the old names.

  2. Read both formats

    I rewrote the Snowflake SQL to read old and new names. It also checks source tags inside saved URLs.

  3. Recover what is there

    The query checks the offer page and the customer’s signup source when campaign tags are missing. It leaves records with no evidence unknown.

  4. Check, rewrite and restore the report

    Eight rewrites corrected the matching rules, and the report kept the dashboard’s columns. The conservative March result was about 7% with no known source.