Skip to main content

V1 vs V2 spend reconciliation — why totals differ and which side to trust

Reference for anyone comparing legacy V1 (Laravel "Scale Winning Ads" at ocdmg-launch) against V2 (creative-ingest at launch.lynkr.cc) and seeing different spend totals for the same client and date window. Written after the 2026-08-26 side-by-side and platform-truth audit that settled ticket PL-A3's Top Speed gap. Read this BEFORE declaring either side wrong: most deltas are scope or coverage differences with a known mechanism, and each mechanism has a different fix.

The two data paths​

V1 serves reports from its own MySQL insights table (grain: one row per client_id + ad_id + date_start, no breakdown segments), populated by V1's own Python sync from Meta/Google. Its UI aggregates several platform ad_ids into one creative/subtask row where names match.

V2 serves reports from ClickHouse marketing_paid_acquisition (ReplacingMergeTree), read through v_paid_acquisition_latest, which:

  • keeps the latest snapshot per grain (argMax over synced_at), so a day re-fetched by a later sync supersedes earlier partial snapshots;
  • carries two provenances: data_source='platform_api' (native V2 sync) and the V1 cutover archive (sentinel synced_at = 2199-12-31, imported history);
  • applies migration 062 archive retirement: on any account-day where a native row exists, all archive rows for that account-day are suppressed so stale V1 snapshots can never beat native truth;
  • exposes breakdown segments (age, gender, device, country, day_type…) alongside a segment_key='all' row. Certified reads filter segment_key='all'; forgetting that filter inflates totals ~4x.

One row per platform ad_id per day; no cross-ad aggregation at storage level.

The difference mechanisms, ranked by how often they bite​

1. Native sync gap days (V2 too low — THE Top Speed / PL-A3 cause)​

If the native sync fetches a day once early (a few minutes of spend) and never re-fetches it, the latest-snapshot read serves that tiny partial forever. The 2026-08-26 audit found 2026-08-14 was a fleet-wide gap day: Top Speed's Meta account held $0.93 for the day vs $15,637.09 actual; Base Camp, Bigly, Credit Secrets, iPhone Freedom and Pup Labs all showed the same signature (day spend < 10% of the account's median). Top Speed and Pup Labs Google also carried a suppressed Aug 1–12 tail from the earlier quota-blocked Google heal.

  • Signature: a specific account-day is near zero in v_paid_acquisition_latest while neighbours are normal; V1 (or the platform UI) shows real spend that day. Probe:

    -- days below 10% of the account's 30d median = suspected gap days
    WITH daily AS (
      SELECT client_id, platform, account_id, date, sum(spend) AS d
      FROM v_paid_acquisition_latest
      WHERE segment_key='all' AND data_source='platform_api'
        AND date >= today()-30 AND date < today()-1
      GROUP BY ALL)
    SELECT * FROM daily d
    JOIN (SELECT client_id, platform, account_id, medianExact(d) m
          FROM daily GROUP BY ALL HAVING m > 200) me USING (client_id, platform, account_id)
    WHERE d.d < me.m * 0.10 ORDER BY client_id, date

  • Fix: re-dispatch the sync with a lookback covering the gap (npm run sync:marketing -- --days N, or scripts/trigger-marketing-sync.ts). Idempotent — newer snapshots supersede; nothing is deleted.

2. Historical migration-062 segment-blind suppression (V2 too low, archive-covered dates only)​

062's native-coverage join key is (client_id, platform, account_id, date) — no segment_key (see the acct_native subquery in clickhouse/migrations/062_paid_acquisition_archive_retirement.sql). A native breakdown row alone therefore suppresses the archive segment_key='all' row even when no native 'all' row exists, and since certified reads filter 'all', the ad-day vanishes. Measured 2026-08-17: $2.76M of archive 'all' spend suppressed with no native replacement over 90d. The simulated surgical fix was to add segment_key to the join key. Source migration 063_paid_acquisition_archive_segment_grain.sql implements that correction. Confirm migration 063 in the target ledger before treating it as deployed.

  • Only applies to dates the archive covers (pre-cutover history). It does NOT explain gaps in windows with no archive rows — the Top Speed Aug 14–20 gap was initially misattributed to this mechanism until a probe showed zero archive rows in the window.
  • Signature: ad-day has native breakdown rows, zero native 'all', and an archive 'all' row that the view hides.

3. Account-universe differences (either side)​

The two systems may have different ad accounts bound for the same client. In the Aug 14–20 UpWellness comparison, V1's fourth account was UW:01B (GR+2) while V2's was UW:16 (LTV) — a 1.6% total delta traceable to binding differences, not data quality. Always compare the account lists (V1 expanded account rows vs V2's account grain) before comparing totals.

4. Row-grain: V1 aggregates ads, V2 lists them​

V1 groups multiple platform ad_ids under one creative/subtask row (e.g. 4 V2 ad rows shared subtask 1012486). Per-row spend/CPC/CTR therefore differ by construction even when both sides are internally consistent. Compare at account or campaign grain, or sum V2's rows per subtask, before judging per-ad numbers.

5. V1 UI confounds (operator error, not data)​

  • The "Active Only" toolbar button is a status dropdown whose default state applies NO status restriction, despite the label.
  • The top "Breakdown Options" menu only edits regex patterns; real breakdowns are the per-row icon-tag strip (DOW, TOD, AT…).
  • The date-preset combobox can override raw Start/End inputs; confirm the window from the network payload (start_date/end_date), not the inputs.
  • TOTALS (Managed Accounts) ≠ TOTALS (All Accounts).
  • V1's "Last 30 days" style presets can be inclusive of 31 dates (see the 2026-08-17 audit); prefer explicit custom ranges when comparing.

Which platform is "correct"? Ask the source.​

Neither V1 nor V2 is the arbiter — the ad platforms are. Use scripts/audit-platform-direct.ts (Meta Graph insights + Google GAQL, V1 never consulted, four-state cell discipline, dry-run by default):

node --env-file=.env.production --import tsx scripts/audit-platform-direct.ts \
  --since=2026-08-14 --until=2026-08-20 --out=/tmp/a3-truth \
  --legs=meta,google --clients=top-speed-ts --include-paused --execute

Case study: PL-A3, Top Speed, 2026-08-14 → 2026-08-20​

SourceSpend
V1 UI (all accounts)$160,703.40
Platform truth (Meta + Google direct)$160,686.76
V2 v_paid_acquisition_latest ('all', latest)$136,645.99
V2 base table, naive sum across snapshots$153,515.82

Platform truth matched V1 within $17 (0.01%). V2's $24,040.76 shortfall was entirely mechanism 1: the Aug 14 gap day (Meta $0.93 recorded vs $15,637.09 actual; Google $489.00 vs $8,893.26) plus small Google restatements elsewhere (cents). Every other account-day matched to rounding. Note the naive base-table sum is also wrong (it double-counts snapshots) — only latest-per-grain reads are meaningful.

Verdict: V1 correct, V2 under-reporting from a sync coverage gap. Heal = fleet resync with lookback past the gap (dispatched 2026-08-26), then re-run the audit; the 062 fix (mechanism 2) remains separately worthwhile for archive-covered history.

Reconciliation checklist​

  1. Pin the window explicitly on both sides; verify via network payloads.
  2. Compare account lists first (mechanism 3).
  3. Probe V2 for gap days (mechanism 1 query above).
  4. If the window predates cutover, check 062 suppression (mechanism 2).
  5. Only compare per-ad rows after resolving grain (mechanism 4).
  6. Disagreement survives all of the above → run audit-platform-direct.ts --execute and let the platform rule.