How to automate CAC reporting across Meta, Google and TikTok

CAC across Meta, Google and TikTok is one number built from five systems — four ad platforms for spend, an MMP for the customers — which is why it's both the metric leadership watches and the one most likely to be quietly wrong. This guide covers the pipeline: getting the inputs at the right grain, deduplicating the denominator, and keeping blended CAC honest per channel. (For the definition itself, see the CAC guide.)

The two inputs, and where each goes wrong

Spend comes from each platform's API — Meta insights with the attribution setting pinned, Google's GAQL with cost in micros and recent days restating, TikTok and Snap with their own windows. Spend errors are usually boundary errors: timezones and date ranges that don't match across platforms.

Customers come from the MMP, and this is where definitions live or die. A customer is the event you decided it is — purchase, a first purchase, purchase — pulled event-level (AppsFlyer Raw Data Pull API) and deduplicated per user. Counting event rows instead of unique users is the single most common CAC inflation; counting installs instead of customers is the most common deflation of its meaning.

The pipeline, step by step

1. Resolve the period once — last full ISO week, in the report's timezone — and use it for every source
2. Pull spend per platform with pinned settings; convert currencies from one FX source
3. Pull the customer event from the MMP, deduplicate per user, count per channel/day
4. Map MMP media sources to your channel taxonomy (the "is restricted Meta?" question, answered once)
5. Compute channel CAC and blended CAC in the same pass
6. Compare against per-channel targets; flag breaches
7. Append to the report; post the summary with the flags

Why blended and channel must come from one pass

Blended CAC computed separately from channel CAC drifts — different pulls, different boundaries — and a flattering blend can hide a channel that's off. One pass, one period, one denominator definition.

Example output

The blend is fine; Snap isn't. That's exactly the readout a blended-only report hides.

What breaks in manual versions

Mixed timezones between platforms; platform-claimed conversions used as the denominator (double-counted across channels); the customer event silently swapped for installs in a busy week; FX applied inconsistently on multi-currency accounts; and last week's CAC never revisited after Meta and Google restate.

QA checklist

• ✓ One denominator definition, written down, with the dedup rule explicit
• ✓ Spend tied out against each platform's UI for the same range
• ✓ One channel's CAC recomputed by hand from raw inputs
• ✓ Trailing re-pull confirmed (last week's CAC should move slightly mid-week, then settle)

When not to automate

While the customer definition is still contested, or while channel taxonomy changes monthly. Lock those, then schedule it.

How Opera runs it

CAC reporting automation is this pipeline as a scheduled run: pinned pulls, deduplicated denominators, channel and blended in one pass, threshold flags in the summary.

"Refresh blended and channel-level CAC for last week and flag anything above target."