Marketing Data Stack for Subscription Apps, in 3 Stages

The one join that matters

Subscription app acquisition math rests on a single join: ad spend by campaign and day, matched to revenue by the same campaign and cohort day. The cohort day is the day the user clicked or installed. With those two keys shared by spend and revenue, you can compute CAC, ROAS and payback for any slice you care about. Without them, the team argues about whose number is right.

Illustrative example with round numbers. Say a campaign spent $1,000 on one day, and the users from that day became 40 paying subscribers at $20 for the first charge. First-charge revenue is 40 x $20 = $800, so ROAS at that point is $800 divided by $1,000 = 0.8. Now say half of those payers, 20 people, renew once at $20. Renewal revenue is 20 x $20 = $400, and total revenue is $800 + $400 = $1,200, so ROAS becomes $1,200 divided by $1,000 = 1.2. The same cohort looks unprofitable after the first charge and profitable after one renewal, which is why the join has to carry cohort day and not only a campaign total.

Everything else in the stack exists to make that join reliable. Platform-reported conversions are an input. They use the platform's own attribution window and cannot see renewals, refunds or chargebacks in your payment processor. Compare them with your numbers, and read performance on actual payments.

Stage 1: spreadsheet and platform exports

Stage 1 needs three things: a daily spend export from each ad platform, a payments export from Stripe or your subscription tool, and one sheet that calculates CAC and ROAS by campaign and week. This holds up until roughly a handful of channels and one funnel. It costs almost nothing, and it teaches you which questions your team asks each week.

Use one tab for spend (date, platform, campaign, spend, currency), one tab for payments (date, user or order id, campaign tag, amount, charge type such as first charge or renewal) and one summary tab with formulas. The join only works if campaign names or tags match between spend and payments, so agree on a naming rule before you launch campaigns.

The main risk is manual copy errors. Add a checksum row that compares total spend in your sheet with the totals shown in each ad platform for the same dates. Illustrative example: your sheet shows $9,400 and the platforms show $9,850. The gap is $9,850 - $9,400 = $450, which usually means a missing campaign or a missing day in an export. Fix the gap before you read any ROAS figure.

Plan the move to Stage 2 when you see these signs:

• The weekly refresh takes more than an hour.
• You run a second funnel or a second app.
• Two people edit the sheet and the numbers start to disagree.
• You need user-level cohorts, which a spreadsheet handles poorly.

Stage 2: connectors, warehouse and BI

Stage 2 moves the data into a warehouse. BigQuery is a common option, and others work as well. You pull ad platforms, your MMP such as AppsFlyer or Adjust, Stripe and your subscription layer into it, either with managed connectors or with small scheduled scripts. I built acquisition and analytics infrastructure from scratch, including BI setup, funnel tooling, traffic-source integration and end-to-end data flow.

Model three tables and resist adding more at first. The spend table holds date, platform, campaign, ad set and spend in one currency. The users table holds user id, click id, install or signup date, campaign, country and platform. The revenue events table holds event id, user id, timestamp, amount, event type and product. Join users to spend on campaign and date, and join revenue events to users on user id.

Put a BI tool on top of those tables. Looker Studio is free and covers many teams, and Looker or Tableau fit better when you need governed models or heavier dashboards. Keep the logic in the tables and keep the dashboards thin, so that two dashboards cannot show two different answers for the same metric.

Stage 3: modeling and forecasting

Stage 3 adds cohort revenue curves and payback by channel. Build a table with cohort day, channel and cumulative revenue per user at day 0, 7, 14, 30 and 60. Payback day is the first day on which cumulative revenue per user reaches CAC. Reading this table by channel shows you which sources recover cost quickly and which ones need patience.

Forecasting comes from early signals such as trial-to-paid rate and day-7 retention. Take older cohorts from the same channel, fit a simple curve that links those early signals to later revenue, and apply it to young cohorts. Forecasts get weaker when cohorts are small or the product has changed, so show a range and not a single line.

Alerts and agents can read the same modeled tables. An alert can fire when spend rises while trial-to-paid falls, and an LLM-based agent can summarize daily changes from those tables for the team. Because they read modeled data, they share the definitions your dashboards use.

Source map for a subscription app

A typical subscription app has five kinds of sources. For each one, name the key you join on before you build anything:

• Ad platforms (Meta, TikTok, Google, Apple Search Ads (now Apple Ads)): spend and delivery by campaign and day. Join on campaign id plus date.
• MMP or web tracking (AppsFlyer, Adjust, or Meta Pixel and Conversions API for web funnels): clicks and installs. Join on click id or install id.
• Payment processor (Stripe): charges, refunds and chargebacks. Join on order id or customer id, mapped to your user id.
• Subscription layer (RevenueCat, Adapty): trials, renewals and cancellations. Join on app user id.
• Product analytics: in-app events such as trial start. Join on user id.

Then write down who owns each source and how it can break. Renamed campaigns break the spend join. An SDK update can drop the click id. A failed webhook leaves holes in payments. Two systems may report in different currencies or time zones, and a user id can change at login. The owner is the person who gets the message when the source breaks, and one row per source in a shared document is enough.

Build vs buy decision rule

Buy connectors when engineering time costs more than the tool. Build small scripts for sources with a clean API and a stable schema, where a scheduled job and a few dozen lines do the work. Mixed setups are normal: a managed connector for the awkward ad platform and a script for the simple one.

Illustrative comparison. Say script maintenance takes 6 hours a month at $60 an hour. That is 6 x $60 = $360 a month. If a managed connector costs $150 a month, buying saves $360 - $150 = $210 a month. If the connector costs $500 a month, the script is cheaper by $500 - $360 = $140 a month. Add setup time and the cost of a broken report to either side before you decide.

Avoid tools you cannot export from. You should be able to pull raw tables into your own warehouse or into files, or switching later becomes expensive. Also check that a connector delivers the fields your join needs, such as campaign id and click id, before you sign up.

Data quality checks

Run a small set of checks each morning, and automate them once Stage 2 is in place:

• Compare row counts and spend totals for the previous day against the platform UI.
• Convert to one currency and state the rate rule.
• Use one reporting time zone for ad platforms and payments.
• Record the attribution window for each source and compare like with like.
• Tag refunds and chargebacks so they reduce revenue.

Set a tolerance, for example a 1 percent gap between warehouse spend and the platform UI, and pause reporting when the gap is larger until you find the cause. The right tolerance depends on your spend level and platform delays.

Keep a named metric dictionary with one definition for CAC, payback and ROAS. Each entry lists the formula, the time window and the source table. For example, ROAS can be revenue within 30 days of the cohort day divided by spend for that cohort day. If a definition changes, record the date, so old and new reports are not compared by mistake.

Next step: build the stage you are in

Pick your current stage and finish it this week before you plan the next one.

• On a spreadsheet: add the checksum row, then build the spend-to-revenue join for one campaign and one cohort day.
• On a warehouse: create the spend, users and revenue events tables, then write the metric dictionary.