Performance Marketing: True ROAS and Customer LTV Across Facebook, TikTok and Google

Objective

Every performance marketer runs the same broken loop: Facebook Ads Manager says the prospecting campaign returned 4x, TikTok claims credit for conversions Google also claims, and the finance team's revenue number matches none of them. The platforms grade their own homework, they only see their own clicks, and they all report on day-0 revenue — so the campaign that buys discount-hunters who never order again looks identical to the one that buys customers worth $1,450.

This recipe puts all three sides in one database: platform spend (Facebook, TikTok, Google), your own browser clickstream with UTMs and click IDs, identity resolution, conversions, and the order history that reveals lifetime value. Then it computes what the platforms can't: site-side click truth, last-touch attribution across platforms, CAC, day-0 ROAS, 90-day LTV, LTV:CAC and contribution-margin ROAS — and shows the ranking flip that decides where next month's budget goes.

Step 1: Create the marketing warehouse

CREATE TABLE IF NOT EXISTS recipe_pm_ad_spend (
  row_id          INTEGER PRIMARY KEY,
  platform        TEXT,
  campaign_id     TEXT,
  campaign_name   TEXT,
  objective       TEXT,
  day             TIMESTAMP,
  spend           DOUBLE,
  impressions     INTEGER,
  clicks_reported INTEGER
);

CREATE TABLE IF NOT EXISTS recipe_pm_creative (
  creative_id INTEGER PRIMARY KEY,
  campaign_id TEXT,
  headline    TEXT,
  body_copy   TEXT,
  angle       VECTOR(384)
);

CREATE TABLE IF NOT EXISTS recipe_pm_click_events (
  event_id     INTEGER PRIMARY KEY,
  anonymous_id TEXT,
  occurred_at  TIMESTAMP,
  landing_path TEXT,
  utm_source   TEXT,
  utm_medium   TEXT,
  utm_campaign TEXT,
  click_id     TEXT,
  device       TEXT
);

CREATE TABLE IF NOT EXISTS recipe_pm_identity (
  anonymous_id  TEXT PRIMARY KEY,
  customer_id   INTEGER,
  identified_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS recipe_pm_conversions (
  conversion_id INTEGER PRIMARY KEY,
  anonymous_id  TEXT,
  occurred_at   TIMESTAMP,
  event_name    TEXT,
  revenue       DOUBLE
);

CREATE TABLE IF NOT EXISTS recipe_pm_orders (
  order_id     INTEGER PRIMARY KEY,
  customer_id  INTEGER,
  ordered_at   TIMESTAMP,
  revenue      DOUBLE,
  gross_margin DOUBLE
);

CREATE TABLE IF NOT EXISTS recipe_pm_attribution (
  conversion_id       INTEGER PRIMARY KEY,
  anonymous_id        TEXT,
  customer_id         INTEGER,
  campaign_id         TEXT,
  platform            TEXT,
  first_order_revenue DOUBLE,
  converted_at        TIMESTAMP
);

CREATE TABLE IF NOT EXISTS recipe_pm_campaign_scorecard (
  campaign_id    TEXT PRIMARY KEY,
  campaign_name  TEXT,
  platform       TEXT,
  spend          DOUBLE,
  new_customers  INTEGER,
  cac            DOUBLE,
  day0_roas      DOUBLE,
  ltv_90d        DOUBLE,
  ltv_roas       DOUBLE,
  margin_roas    DOUBLE
);

Step 2: Load platform spend (what the ad accounts report)

Three days of a small DTC brand's media buy across five campaigns. clicks_reported is what each platform claims — keep an eye on it.

INSERT INTO recipe_pm_ad_spend (row_id, platform, campaign_id, campaign_name, objective, day, spend, impressions, clicks_reported) VALUES
 (1,'facebook','fb-1001','FB_Prospecting_Broad_US','prospecting','2026-05-04',420.00,58200,6),
 (2,'facebook','fb-1001','FB_Prospecting_Broad_US','prospecting','2026-05-05',400.00,55400,5),
 (3,'facebook','fb-1001','FB_Prospecting_Broad_US','prospecting','2026-05-06',380.00,52100,4),
 (4,'facebook','fb-1002','FB_Retargeting_DPA','retargeting','2026-05-04',110.00,9400,2),
 (5,'facebook','fb-1002','FB_Retargeting_DPA','retargeting','2026-05-05',95.00,8100,2),
 (6,'facebook','fb-1002','FB_Retargeting_DPA','retargeting','2026-05-06',95.00,8300,2),
 (7,'tiktok','tt-2001','TT_Creator_UGC_Q2','prospecting','2026-05-04',280.00,141000,8),
 (8,'tiktok','tt-2001','TT_Creator_UGC_Q2','prospecting','2026-05-05',260.00,133500,7),
 (9,'tiktok','tt-2001','TT_Creator_UGC_Q2','prospecting','2026-05-06',260.00,129800,6),
 (10,'google','gg-3001','GG_Brand_Exact','brand','2026-05-04',140.00,2100,2),
 (11,'google','gg-3001','GG_Brand_Exact','brand','2026-05-05',130.00,1950,2),
 (12,'google','gg-3001','GG_Brand_Exact','brand','2026-05-06',130.00,2000,2),
 (13,'google','gg-3002','GG_NonBrand_Generic','prospecting','2026-05-04',340.00,18600,3),
 (14,'google','gg-3002','GG_NonBrand_Generic','prospecting','2026-05-05',330.00,17900,3),
 (15,'google','gg-3002','GG_NonBrand_Generic','prospecting','2026-05-06',330.00,18200,3);

Step 3: Load the browser clickstream (UTMs + click IDs)

This is your own first-party data — every landing hit with its UTM tags and the platform click ID (fbclid / ttclid / gclid). utm_campaign carries the platform's campaign ID, which is the join key back to spend.

Note event 46: user a_f01 clicks a retargeting ad on May 20, twelve days after they already converted. Attribution must ignore it.

INSERT INTO recipe_pm_click_events (event_id, anonymous_id, occurred_at, landing_path, utm_source, utm_medium, utm_campaign, click_id, device) VALUES
 (1,'a_f01','2026-05-04 10:12:00','/lp/summer-bundle','facebook','paid_social','fb-1001','fbclid_8Kq2','mobile'),
 (2,'a_x01','2026-05-04 11:40:00','/lp/summer-bundle','facebook','paid_social','fb-1001','fbclid_9Lm4','mobile'),
 (3,'a_x02','2026-05-04 15:05:00','/lp/summer-bundle','facebook','paid_social','fb-1001','fbclid_2Rt7','desktop'),
 (4,'a_x03','2026-05-04 18:22:00','/lp/summer-bundle','facebook','paid_social','fb-1001','fbclid_5Yh1','mobile'),
 (5,'a_x04','2026-05-05 08:30:00','/lp/summer-bundle','facebook','paid_social','fb-1001','fbclid_1Qa5','desktop'),
 (6,'a_f02','2026-05-05 14:03:00','/lp/summer-bundle','facebook','paid_social','fb-1001','fbclid_3Wp9','mobile'),
 (7,'a_f01','2026-05-05 19:48:00','/products/tote','facebook','paid_social','fb-1001','fbclid_7Nz3','mobile'),
 (8,'a_x05','2026-05-05 21:11:00','/lp/summer-bundle','facebook','paid_social','fb-1001','fbclid_6Bd8','mobile'),
 (9,'a_f03','2026-05-06 09:41:00','/lp/summer-bundle','facebook','paid_social','fb-1001','fbclid_4Cf2','mobile'),
 (10,'a_x06','2026-05-06 12:55:00','/lp/summer-bundle','facebook','paid_social','fb-1001','fbclid_8Gh6','mobile'),
 (11,'a_x07','2026-05-06 17:30:00','/lp/summer-bundle','facebook','paid_social','fb-1001','fbclid_0Jk9','desktop'),
 (12,'a_x08','2026-05-06 20:02:00','/lp/summer-bundle','facebook','paid_social','fb-1001','fbclid_2Ml4','mobile'),
 (13,'a_x09','2026-05-04 16:14:00','/products/tote','facebook','paid_social','fb-1002','fbclid_3Vb2','mobile'),
 (14,'a_f04','2026-05-05 11:00:00','/products/tote','facebook','paid_social','fb-1002','fbclid_5Pn7','mobile'),
 (15,'a_x10','2026-05-05 13:37:00','/cart','facebook','paid_social','fb-1002','fbclid_9Xz1','desktop'),
 (16,'a_f05','2026-05-06 16:20:00','/products/tote','facebook','paid_social','fb-1002','fbclid_1Dq8','mobile'),
 (17,'a_x11','2026-05-06 19:05:00','/cart','facebook','paid_social','fb-1002','fbclid_7Sw3','mobile'),
 (18,'a_g04','2026-05-04 18:00:00','/lp/creator-pick','tiktok','paid_social','tt-2001','ttclid_A1b2','mobile'),
 (19,'a_t01','2026-05-04 19:30:00','/lp/creator-pick','tiktok','paid_social','tt-2001','ttclid_C3d4','mobile'),
 (20,'a_x12','2026-05-04 20:15:00','/lp/creator-pick','tiktok','paid_social','tt-2001','ttclid_E5f6','mobile'),
 (21,'a_x13','2026-05-04 21:02:00','/lp/creator-pick','tiktok','paid_social','tt-2001','ttclid_G7h8','mobile'),
 (22,'a_x14','2026-05-04 22:40:00','/lp/creator-pick','tiktok','paid_social','tt-2001','ttclid_I9j0','mobile'),
 (23,'a_x15','2026-05-05 18:33:00','/lp/creator-pick','tiktok','paid_social','tt-2001','ttclid_K1l2','mobile'),
 (24,'a_x16','2026-05-05 19:55:00','/lp/creator-pick','tiktok','paid_social','tt-2001','ttclid_M3n4','mobile'),
 (25,'a_t02','2026-05-05 20:15:00','/lp/creator-pick','tiktok','paid_social','tt-2001','ttclid_O5p6','mobile'),
 (26,'a_x17','2026-05-05 21:47:00','/lp/creator-pick','tiktok','paid_social','tt-2001','ttclid_Q7r8','mobile'),
 (27,'a_x18','2026-05-05 23:10:00','/lp/creator-pick','tiktok','paid_social','tt-2001','ttclid_S9t0','mobile'),
 (28,'a_x19','2026-05-06 18:20:00','/lp/creator-pick','tiktok','paid_social','tt-2001','ttclid_U1v2','mobile'),
 (29,'a_x20','2026-05-06 19:41:00','/lp/creator-pick','tiktok','paid_social','tt-2001','ttclid_W3x4','mobile'),
 (30,'a_x21','2026-05-06 20:58:00','/lp/creator-pick','tiktok','paid_social','tt-2001','ttclid_Y5z6','mobile'),
 (31,'a_x22','2026-05-06 22:14:00','/lp/creator-pick','tiktok','paid_social','tt-2001','ttclid_A7b8','mobile'),
 (32,'a_g01','2026-05-04 08:30:00','/','google','cpc','gg-3001','gclid_Ee11','desktop'),
 (33,'a_x23','2026-05-04 13:12:00','/','google','cpc','gg-3001','gclid_Ff22','mobile'),
 (34,'a_g02','2026-05-05 12:45:00','/','google','cpc','gg-3001','gclid_Gg33','desktop'),
 (35,'a_x24','2026-05-05 15:28:00','/','google','cpc','gg-3001','gclid_Hh44','mobile'),
 (36,'a_x25','2026-05-06 10:44:00','/','google','cpc','gg-3001','gclid_Ii55','desktop'),
 (37,'a_g03','2026-05-06 15:10:00','/','google','cpc','gg-3001','gclid_Jj66','desktop'),
 (38,'a_x26','2026-05-04 09:20:00','/lp/compare','google','cpc','gg-3002','gclid_Kk77','desktop'),
 (39,'a_x27','2026-05-04 14:50:00','/lp/compare','google','cpc','gg-3002','gclid_Ll88','mobile'),
 (40,'a_x28','2026-05-05 09:05:00','/lp/compare','google','cpc','gg-3002','gclid_Mm99','desktop'),
 (41,'a_x29','2026-05-05 16:37:00','/lp/compare','google','cpc','gg-3002','gclid_Nn10','mobile'),
 (42,'a_g04','2026-05-06 10:05:00','/lp/compare','google','cpc','gg-3002','gclid_Oo11','desktop'),
 (43,'a_g05','2026-05-06 13:22:00','/lp/compare','google','cpc','gg-3002','gclid_Pp12','desktop'),
 (44,'a_x30','2026-05-06 17:55:00','/lp/compare','google','cpc','gg-3002','gclid_Qq13','mobile'),
 (45,'a_x31','2026-05-06 21:30:00','/lp/compare','google','cpc','gg-3002','gclid_Rr14','desktop'),
 (46,'a_f01','2026-05-20 12:00:00','/products/tote','facebook','paid_social','fb-1002','fbclid_LATE1','mobile');

Step 4: Load identity, conversions and the order history

recipe_pm_identity is the join that makes LTV possible — the moment an anonymous browser becomes a known customer. recipe_pm_orders carries every order since, including the repeats the ad platforms never see.

INSERT INTO recipe_pm_identity (anonymous_id, customer_id, identified_at) VALUES
 ('a_f01',501,'2026-05-08 09:00:00'),('a_f02',502,'2026-05-08 11:30:00'),
 ('a_f03',503,'2026-05-09 10:15:00'),('a_f04',504,'2026-05-07 12:00:00'),
 ('a_f05',505,'2026-05-08 17:45:00'),('a_t01',506,'2026-05-06 21:00:00'),
 ('a_t02',507,'2026-05-07 19:20:00'),('a_g01',508,'2026-05-05 09:10:00'),
 ('a_g02',509,'2026-05-06 13:00:00'),('a_g03',510,'2026-05-07 16:00:00'),
 ('a_g04',511,'2026-05-07 11:20:00'),('a_g05',512,'2026-05-08 14:50:00');

INSERT INTO recipe_pm_conversions (conversion_id, anonymous_id, occurred_at, event_name, revenue) VALUES
 (1,'a_f01','2026-05-08 09:00:00','first_purchase',95.00),
 (2,'a_f02','2026-05-08 11:30:00','first_purchase',95.00),
 (3,'a_f03','2026-05-09 10:15:00','first_purchase',95.00),
 (4,'a_f04','2026-05-07 12:00:00','first_purchase',110.00),
 (5,'a_f05','2026-05-08 17:45:00','first_purchase',110.00),
 (6,'a_t01','2026-05-06 21:00:00','first_purchase',42.00),
 (7,'a_t02','2026-05-07 19:20:00','first_purchase',42.00),
 (8,'a_g01','2026-05-05 09:10:00','first_purchase',130.00),
 (9,'a_g02','2026-05-06 13:00:00','first_purchase',130.00),
 (10,'a_g03','2026-05-07 16:00:00','first_purchase',130.00),
 (11,'a_g04','2026-05-07 11:20:00','first_purchase',145.00),
 (12,'a_g05','2026-05-08 14:50:00','first_purchase',145.00);

INSERT INTO recipe_pm_orders (order_id, customer_id, ordered_at, revenue, gross_margin) VALUES
 (1,501,'2026-05-08 09:00:00',95.00,57.00),(2,501,'2026-06-05 14:20:00',225.00,135.00),
 (3,501,'2026-07-11 10:05:00',200.00,120.00),(4,502,'2026-05-08 11:30:00',95.00,57.00),
 (5,502,'2026-06-12 16:40:00',225.00,135.00),(6,502,'2026-07-18 09:15:00',200.00,120.00),
 (7,503,'2026-05-09 10:15:00',95.00,57.00),(8,503,'2026-06-14 11:50:00',225.00,135.00),
 (9,503,'2026-07-21 15:30:00',200.00,120.00),(10,504,'2026-05-07 12:00:00',110.00,66.00),
 (11,504,'2026-06-20 13:25:00',220.00,132.00),(12,505,'2026-05-08 17:45:00',110.00,66.00),
 (13,505,'2026-06-25 18:10:00',220.00,132.00),(14,506,'2026-05-06 21:00:00',42.00,25.20),
 (15,506,'2026-05-29 20:35:00',18.00,10.80),(16,507,'2026-05-07 19:20:00',42.00,25.20),
 (17,507,'2026-06-02 19:45:00',18.00,10.80),(18,508,'2026-05-05 09:10:00',130.00,78.00),
 (19,508,'2026-06-08 10:30:00',210.00,126.00),(20,509,'2026-05-06 13:00:00',130.00,78.00),
 (21,509,'2026-06-16 14:05:00',210.00,126.00),(22,510,'2026-05-07 16:00:00',130.00,78.00),
 (23,510,'2026-06-22 12:40:00',210.00,126.00),(24,511,'2026-05-07 11:20:00',145.00,87.00),
 (25,511,'2026-06-10 09:55:00',620.00,372.00),(26,511,'2026-07-15 11:10:00',685.00,411.00),
 (27,512,'2026-05-08 14:50:00',145.00,87.00),(28,512,'2026-06-18 15:35:00',620.00,372.00),
 (29,512,'2026-07-24 16:20:00',685.00,411.00);

Step 5: Load ad creative and embed the messaging angle

The copy each campaign ran. EMBED turns it into a vector so Step 11 can ask which angle — not which campaign — buys which kind of customer.

INSERT INTO recipe_pm_creative (creative_id, campaign_id, headline, body_copy) VALUES
 (1,'fb-1001','Built for the long haul','Our summer bundle is made from full-grain leather and organic canvas. Designed to last for years, not seasons. Sixty day free returns and a lifetime repair warranty.'),
 (2,'fb-1002','Still thinking about the Tote?','The bag you were looking at is still in stock. Ships free today and we cover the return if it is not right for you.'),
 (3,'tt-2001','70% OFF TODAY ONLY','Everyone is grabbing these before the flash sale ends at midnight. Cheapest price of the year. Hurry, limited stock, link in bio.'),
 (4,'gg-3001','The official store','Shop the official collection direct from us. Authentic products, full manufacturer warranty, free shipping on every order.'),
 (5,'gg-3002','Compare the best everyday tote bags','An honest side by side comparison of materials, capacity, and warranty across the top everyday totes, with sizing guidance to help you choose.');

UPDATE recipe_pm_creative SET angle = EMBED(headline || ' ' || body_copy);

Step 6: Ground truth — the platforms are over-reporting clicks

Nobody audits this, and it silently corrupts every CPC and CVR number in the account. Compare what each platform claimed against what your own site actually recorded during the flight window.

WITH platform AS (
  SELECT campaign_id, SUM(spend) AS spend, SUM(impressions) AS impressions,
         SUM(clicks_reported) AS clicks_reported
  FROM recipe_pm_ad_spend
  GROUP BY campaign_id
),
site AS (
  SELECT utm_campaign AS campaign_id, COUNT(*) AS clicks_observed
  FROM recipe_pm_click_events
  WHERE occurred_at < '2026-05-07 00:00:00'
  GROUP BY utm_campaign
)
SELECT p.campaign_id, p.spend, p.clicks_reported, s.clicks_observed,
       ROUND(p.spend / s.clicks_observed, 2) AS true_cpc
FROM platform p
JOIN site s ON s.campaign_id = p.campaign_id
ORDER BY p.spend DESC;

Expected — TikTok reports 21 clicks against 14 real landings (50% inflation), so its true CPC is $57.14, not the $38 the platform implies. Google brand search is honest (6 vs 6):

Step 7: Attribute every conversion, last touch, 30-day lookback

One pass: join each conversion to that visitor's prior clicks, rank them by recency, keep the most recent, and resolve the anonymous ID to a customer. This is the join the ad platforms structurally cannot do — they only see their own clicks.

INSERT INTO recipe_pm_attribution (conversion_id, anonymous_id, customer_id, campaign_id, platform, first_order_revenue, converted_at)
WITH touches AS (
  SELECT v.conversion_id AS conversion_id, v.anonymous_id AS anonymous_id, v.revenue AS revenue,
         e.utm_campaign AS campaign_id, e.utm_source AS source,
         e.occurred_at AS click_at, v.occurred_at AS convert_at
  FROM recipe_pm_conversions v
  JOIN recipe_pm_click_events e ON e.anonymous_id = v.anonymous_id
  WHERE e.occurred_at <= v.occurred_at
    AND (julianday(v.occurred_at) - julianday(e.occurred_at)) <= 30
),
ranked AS (
  SELECT conversion_id, anonymous_id, revenue, campaign_id, source, click_at, convert_at,
         ROW_NUMBER() OVER (PARTITION BY conversion_id ORDER BY click_at DESC) AS rn
  FROM touches
),
won AS (
  SELECT conversion_id, anonymous_id, revenue, campaign_id, source, convert_at
  FROM ranked
  WHERE rn = 1
)
SELECT w.conversion_id, w.anonymous_id, i.customer_id, w.campaign_id, w.source, w.revenue, w.convert_at
FROM won w
JOIN recipe_pm_identity i ON i.anonymous_id = w.anonymous_id;

Step 8: Ground truth — check the attribution by hand

Two rows in the output prove the logic. Read them before trusting the rest.

SELECT conversion_id, anonymous_id, customer_id, campaign_id, platform, converted_at
FROM recipe_pm_attribution
ORDER BY conversion_id;

Expected — 12 rows, and specifically:

• Conversion 11 (a_g04) is credited to gg-3002, not tt-2001. That visitor saw the TikTok creator ad on May 4 and clicked Google non-brand on May 6 before converting May 7. TikTok would have claimed this conversion in its own dashboard; last-touch across all platforms gives it to Google.
• Conversion 1 (a_f01) is credited to fb-1001, not fb-1002. That visitor clicked a retargeting ad on May 20 — after converting on May 8. The e.occurred_at <= v.occurred_at guard excludes it. Without that guard, retargeting quietly steals credit for customers it did not acquire.

Step 9: The day-0 scorecard everyone reports on

Spend, new customers, CAC, and the ROAS the ad platforms show you.

WITH spend AS (
  SELECT campaign_id, platform, campaign_name, SUM(spend) AS spend
  FROM recipe_pm_ad_spend
  GROUP BY campaign_id, platform, campaign_name
),
cohort AS (
  SELECT campaign_id, COUNT(*) AS new_customers, SUM(first_order_revenue) AS day0_revenue
  FROM recipe_pm_attribution
  GROUP BY campaign_id
)
SELECT s.campaign_name, s.platform, s.spend, c.new_customers, c.day0_revenue,
       ROUND(s.spend / c.new_customers, 2) AS cac,
       ROUND(c.day0_revenue / s.spend, 3) AS day0_roas
FROM spend s
JOIN cohort c ON c.campaign_id = s.campaign_id
ORDER BY day0_roas DESC;

Expected — brand search wins, non-brand looks mediocre, and you are about to make the wrong decision:

Step 10: The 90-day view — LTV, LTV:CAC and contribution margin

Same cohorts, extended through every subsequent order. This is the number the ad platforms will never have, because the repeat orders never touch an ad.

INSERT INTO recipe_pm_campaign_scorecard (campaign_id, campaign_name, platform, spend, new_customers, cac, day0_roas, ltv_90d, ltv_roas, margin_roas)
WITH spend AS (
  SELECT campaign_id, platform, campaign_name, SUM(spend) AS spend
  FROM recipe_pm_ad_spend
  GROUP BY campaign_id, platform, campaign_name
),
cohort AS (
  SELECT campaign_id, COUNT(*) AS new_customers, SUM(first_order_revenue) AS day0_revenue
  FROM recipe_pm_attribution
  GROUP BY campaign_id
),
ltv AS (
  SELECT a.campaign_id AS campaign_id, SUM(o.revenue) AS ltv_revenue, SUM(o.gross_margin) AS ltv_margin
  FROM recipe_pm_attribution a
  JOIN recipe_pm_orders o ON o.customer_id = a.customer_id
  WHERE (julianday(o.ordered_at) - julianday(a.converted_at)) <= 90
  GROUP BY a.campaign_id
)
SELECT s.campaign_id, s.campaign_name, s.platform, s.spend, c.new_customers,
       ROUND(s.spend / c.new_customers, 2),
       ROUND(c.day0_revenue / s.spend, 3),
       ROUND(l.ltv_revenue / c.new_customers, 2),
       ROUND(l.ltv_revenue / s.spend, 2),
       ROUND(l.ltv_margin / s.spend, 2)
FROM spend s
JOIN cohort c ON c.campaign_id = s.campaign_id
JOIN ltv l ON l.campaign_id = s.campaign_id;

SELECT campaign_name, platform, spend, cac, day0_roas, ltv_90d, ltv_roas, margin_roas
FROM recipe_pm_campaign_scorecard
ORDER BY ltv_roas DESC;

Expected — the ranking flips. GG_NonBrand_Generic was third on day-0 ROAS and is first on 90-day LTV:CAC. TT_Creator_UGC_Q2 was merely last; on LTV it is a catastrophe — $60 lifetime value against a $400 CAC, and a contribution-margin ROAS of 0.09:

Only three of the five campaigns are contribution-positive (margin_roas > 1).

Step 11: Which creative angle buys low-LTV customers?

The campaign IDs tell you where the money went. The creative embeddings tell you why. Score every ad's copy against a discount-and-urgency angle, then put that score next to the LTV it produced.

WITH angle AS (
  SELECT campaign_id,
         ROUND(COSINE_SIMILARITY(angle, EMBED('limited time flash sale discount hurry cheapest price')), 4) AS discount_affinity
  FROM recipe_pm_creative
),
spend AS (
  SELECT campaign_id, campaign_name, SUM(spend) AS spend
  FROM recipe_pm_ad_spend
  GROUP BY campaign_id, campaign_name
),
cohort AS (
  SELECT campaign_id, COUNT(*) AS new_customers
  FROM recipe_pm_attribution
  GROUP BY campaign_id
),
ltv AS (
  SELECT a.campaign_id AS campaign_id, SUM(o.revenue) AS ltv_revenue
  FROM recipe_pm_attribution a
  JOIN recipe_pm_orders o ON o.customer_id = a.customer_id
  GROUP BY a.campaign_id
)
SELECT s.campaign_name, g.discount_affinity,
       ROUND(l.ltv_revenue / c.new_customers, 2) AS ltv_90d,
       ROUND(l.ltv_revenue / s.spend, 2) AS ltv_roas
FROM spend s
JOIN angle g ON g.campaign_id = s.campaign_id
JOIN cohort c ON c.campaign_id = s.campaign_id
JOIN ltv l ON l.campaign_id = s.campaign_id
ORDER BY g.discount_affinity DESC;

Expected — a near-monotonic inverse relationship: the more a creative leans on discount and urgency, the lower the lifetime value of the customers it buys. The education-led comparison ad scores negative on discount affinity and produced the highest LTV in the account:

The similarity values depend on the configured embedding model — the shipped 384-dim all-minilm default produces the figures above. The ordering is the assertion, not the fourth decimal.

Step 12: Ask the database what to do about it

Everything above is a report. This turns it into a decision, in one SQL call — no export, no notebook, no BI seat.

Requires a tool-capable LLM in [query.ai_service]. Steps 1-11 do not.

SELECT AGENT_RUN(
  'aidb-assistant',
  'You are a performance marketing analyst. Use execute_query to read every row of the table recipe_pm_campaign_scorecard. Columns: campaign_name, platform, spend, new_customers, cac, day0_roas, ltv_90d, ltv_roas, margin_roas. Then recommend how to reallocate next month budget. Name the one campaign to cut and the one campaign to scale, and justify each using the ltv_roas and margin_roas values you read. Under 150 words.',
  json_object('max_iterations', 4, 'timeout_ms', 240000)
) AS budget_recommendation;

The agent reads the scorecard with its own execute_query tool and returns a written recommendation. Wording varies by model; a correct answer cuts TT_Creator_UGC_Q2 (margin ROAS 0.09) and scales GG_NonBrand_Generic (LTV ROAS 2.90, margin ROAS 1.74) — not the campaign with the best day-0 ROAS.

Expected Outcomes

• Total media spend across the three platforms: $3,700; 12 attributed new customers; blended CAC $308.33.
• Click discrepancy: TikTok over-reports by 50% (21 claimed vs 14 observed); Google brand search reports exactly.
• Attribution: 12 of 12 conversions attributed last-touch; one cross-platform steal corrected (TikTok → Google non-brand), one post-conversion retargeting click correctly excluded.
• The flip: day-0 ROAS ranks GG_Brand_Exact first; 90-day LTV:CAC ranks GG_NonBrand_Generic first (2.90 vs 2.55).
• The kill decision: TT_Creator_UGC_Q2 returns $0.09 of gross margin per $1 spent.
• The mechanism: discount-affinity of ad copy runs inversely to customer LTV.

Cleanup (Optional)

DROP TABLE IF EXISTS recipe_pm_campaign_scorecard;
DROP TABLE IF EXISTS recipe_pm_attribution;
DROP TABLE IF EXISTS recipe_pm_creative;
DROP TABLE IF EXISTS recipe_pm_orders;
DROP TABLE IF EXISTS recipe_pm_conversions;
DROP TABLE IF EXISTS recipe_pm_identity;
DROP TABLE IF EXISTS recipe_pm_click_events;
DROP TABLE IF EXISTS recipe_pm_ad_spend;

Use it from your agent

• Ingestion: point your Facebook / TikTok / Google Ads connectors at recipe_pm_ad_spend (one row per campaign per day) and your analytics stream at recipe_pm_click_events. Both are ordinary tables — any SDK, any ETL tool.
• REST/SDK: run Step 7 and Step 10 on a nightly schedule and your scorecard is always current. POST /v1/query/execute with the block as-is.
• Durable agent (v1.9.0+): wrap Step 12 in CREATE AGENT budget_review PERSONA 'aidb-assistant' TASK '...' ON SCHEDULE '0 7 * * 1' and every Monday morning the recommendation is waiting, with the run recorded in _system_agent_runs.
• Why in-DB: attribution needs the ad spend, the clickstream, the identity graph and the full order history joined in one place. In a stitched stack that is four systems, three sync jobs and a reverse-ETL bill — and the LTV number is always a day stale.

Key Concepts Learned

• Day-0 ROAS and 90-day LTV:CAC rank campaigns differently, and only one of them is a budget decision. Optimising to the platform's number optimises to the platform's incentive.
• Attribution is a ROW_NUMBER over your own clickstream, not a vendor product. The two guards that matter are the lookback window and click_at <= converted_at — without the second, retargeting takes credit for customers it never acquired.
• Reconcile platform-reported clicks against your own landings. The discrepancy is free signal about traffic quality and it silently corrupts every CPC in the account.
• Contribution-margin ROAS is the honest cut line. Revenue ROAS above 1.0 can still lose money.
• Embeddings turn creative copy into a queryable variable, which is how you learn that the angle — not the channel — is what buys low-LTV customers.