Triple Whale & BigQuery — five findings

For Kiran (data + engineering) · prepared by Sebastian, 2026-08-26 · window 2026-07-26 → 2026-08-24 (30 days) · all figures USD

Triple Whale is now the CEO-facing source of truth for both business and Meta numbers. Reconciling it against Shopify and the Meta Marketing API to make that safe turned up five pipeline issues. Two of them silently corrupt numbers that are already being quoted.

The good news first, because it matters: TW's revenue agrees with Shopify to within 2.7% (US$162,839 vs US$158,479) and its Meta spend agrees with the Marketing API to within 1.5% once AUD is converted at ~0.72. On the week ending 2026-08-21 the two agree on attributed purchases exactly (20 and 20). The core of the pipeline is sound. What follows are specific, fixable defects around it.

Context: paid spend is not mostly Meta

This came out of the reconciliation and reframes everything downstream. Google Ads is 63% of paid spend — more than twice Meta. It is easy to miss because it is the one channel that does not live in a *Spend column: it is ga_adCost.

The five findings

Reproducing this

On rounding: the daily table rounds each day to the dollar, so its column sums land within ~$2 of the unrounded aggregates (US$63,246 total paid, US$39,977 Google). Query the warehouse directly for exact figures.

Every figure above comes from lucky-nature-498502-h2.raw.triple_whale_summary unless stated otherwise, deduped on latest loaded_at. Run these to check any of it.

Channel spend — note Google is `ga_adCost`, not a `*Spend` column

SELECT ROUND(SUM(ga_adCost),0)   AS google,
       ROUND(SUM(fb_ads_spend),0) AS meta,
       ROUND(SUM(tiktok_spend),0) AS tiktok,
       ROUND(SUM(adsSpend),0)     AS ads_spend_field,
       ROUND(SUM(blendedAds),0)   AS blended_ads
FROM `lucky-nature-498502-h2.raw.triple_whale_summary`
WHERE DATE_DIFF(DATE(date_end), DATE(date_start), DAY) = 0
  AND DATE(date_start) BETWEEN '2026-07-26' AND '2026-08-24';

Empty attribution tables

SELECT table_id, row_count,
       TIMESTAMP_MILLIS(last_modified_time) AS last_modified
FROM `lucky-nature-498502-h2.raw.__TABLES__`
WHERE table_id IN ('shopify_order_attribution','quiz_events_utm');

Duplicated + restated days

SELECT DATE(date_start) AS day, COUNT(*) AS n,
       STRING_AGG(CAST(ROUND(totalSales,0) AS STRING), ' | ') AS variants
FROM `lucky-nature-498502-h2.raw.triple_whale_summary`
WHERE DATE_DIFF(DATE(date_end), DATE(date_start), DAY) = 0
GROUP BY day HAVING COUNT(*) > 1;

What I am asking for

In priority order: (1) fix or derive adsSpend — it is breaking metrics already on screen. (2) confirm the UTM situation on ad URLs, which I believe is the root cause of findings 3 and 4. (3) define blendedSales. (4) dedup and backfill the daily series.

Happy to take any of this to the Triple Whale side rather than yours if that is the faster path — I mainly need to know which numbers I can safely put in front of Gabe in the meantime.

Generated 2026-08-26 by database/tw_data_quality_report.py. Timestamps Australia/Sydney; BigQuery stores UTC.