You acquire customers. They buy. You calculate revenue divided by spend. The number looks good, the channel gets more budget, everyone moves on.

Normal marketing routine.

And then you build a cohort revenue waterfall in BigQuery and realise that two channels with the same first-purchase ROAS can behave completely differently after that.

One cohort buys once and disappears.

Another cohort starts slower, but keeps buying through day 30, day 60, day 90.

If you only look at the first purchase, these cohorts look almost the same. So you make the same budget decision for both.

That’s the trap.

First-purchase ROAS answers a narrow question:

“Did this acquisition pay back immediately?”

Useful, yes. But for many businesses it’s not the real business question.

The better question is:

“When does this cohort actually become profitable?”

That’s where the waterfall helps.

In BigQuery, the logic is pretty straightforward:

  1. Find each customer’s first purchase date. This becomes the cohort anchor.
  2. Take every later purchase from the same customer.
  3. Calculate how many days passed since the first purchase.
  4. Sum cumulative revenue by cohort at day 7, 14, 30, 60, 90.
  5. Join CAC or ad spend data to see when cumulative revenue crosses the acquisition cost line.

Now you don’t just have ROAS.

You have a curve.

And the curve is usually where the interesting part starts.

I’ve built this for e-commerce clients several times. The most common surprise is simple: the channel with the best 30-day ROAS often goes flat after day 30.

Meanwhile, another channel looks average at day 30, but keeps growing through day 60 and day 90.

Same budget report, different conclusion.

At that point the conversation changes. It’s no longer:

“Which channel has the best ROAS this month?”

It becomes:

“Which channel brings customers who keep paying back?”

That is a much better conversation for a CMO.

Because the goal is not to reward the channel that looks best in the first report. The goal is to understand where the business actually earns back acquisition cost and where it just creates nice early numbers.

First-purchase ROAS feels useful until you see the 90-day curve.

After that, it’s hard to unsee how much budget was being decided too early.

Want all my best GA4-BQ queries in one place? I turned them into a Chrome extension — top SQL queries you can search and copy in seconds.

Go here to install it for FREE.

Prefer the web version? It’s here.