Case study · DTC e-commerce
Meta + TikTok ads kill list
For a DTC e-commerce group (2 brands, EU + US). A BigQuery + dbt pipeline that tells a media buying team every morning, in Slack, which Meta and TikTok ads to switch off, based on their own spend and ROAS rules.
Windsor.aiBigQuerydbtn8nSlackLooker StudioOct 2026 → ongoing
0:00 / 0:46
What the animation shows, scene by scene:
- Losing ads: Five ad cards on fire. About 3.9k a day was going into ads that should already be off, across 5 ad accounts, 2 brands, EU and US.
- Old setup: A Google Sheets report is crossed out. It timed out so data went stale, purchases were counted twice, and ROAS looked better than it was.
- Pipeline: Meta and TikTok flow through Windsor.ai into BigQuery, then through dbt layers: staging, intermediate, marts. Daily load, 6-month backfill, 14-day refresh for late conversions.
- Rules: A kill_rules.csv file with three rules: ROAS below target over 7 days and the last 2, no purchase in 7 days, ROAS below target over 30 days. Changing a threshold means editing one line.
- 9:00 Slack: A Slack message every morning at 9:00 lists the ads to switch off, about 9 alerts a day. Before every run a freshness check: if the data is fresh the list is sent, if not I get an email instead of the team.
- Result: First 13 days live: 106 losing ads flagged, about half less spend on them (estimate), about 6 hours a week of manual checks replaced (estimate).
106 losing ads flagged in the first 13 days
Spend on flagged ads dropped by about half (estimate)
Stale data triggers an alert instead of a wrong list
The problem
- Two brands on Meta and TikTok, with EU and US ad accounts. The team decided by hand which ads were burning money.
- The first version ran on Google Sheets. As the accounts grew it timed out silently: data went stale and nobody noticed. A kill list built on old data is worse than none.
- A purchase double count inflated ROAS, so losing ads looked profitable.
What I delivered
- Ingestion: Windsor.ai pulls Meta and TikTok daily into BigQuery, with a 6-month backfill and a 14-day rolling refresh to catch late conversions.
- Modelling: a layered dbt project (staging → intermediate → marts). Daily marts store raw spend and revenue and compute ROAS after summing, which avoids averaging ratios. The latest name and status per ad ID, because statuses change over time.
- Business rules as data: the kill thresholds live in one
kill_rules.csvseed (platform, brand, funnel stage). The client changes a threshold by editing a CSV, and their account manager can review it. Rules check 2-, 7- and 30-day windows; ads passing the 7-day ROAS rule skip the 30-day rules. - Delivery: n8n posts the kill list to Slack each morning. Looker Studio sits on the same marts.
- Monitoring: n8n checks data freshness before every run. If either platform is stale, or a workflow crashes, I get an email instead of the team getting a wrong list.
Results
First 13 days live (Oct 16–28, 2026), from the Slack message log:
- 114 kill alerts on 106 different ads, about 9 a day, across 5 ad accounts (Meta 101 ads, TikTok 5).
- What triggered them: ROAS below target over 7 + 2 days (56), ROAS below target over 30 days (55), no purchase in 7 days (37), no purchase in 30 days (1). A third of the alerts broke two or more rules at once.
- 8 ads were flagged a second time days later: still spending after the first alert. That’s why a daily check matters.
- Money going into losing ads: in the 7 days before their first alert, the flagged ads spent ~27.5k, about 3.9k a day.
- Spend on them dropped by about half (estimate): at the pre-alert rate they would have spent ~25.5k up to Oct 27; they actually spent 13.9k, about 45% less. On Meta, spend per flagged ad per day fell from ~36 to ~18, so the team acted on the alerts. On TikTok there was no drop yet.
- Manual work replaced (estimate): checking 5 accounts by hand across three windows takes about 10 minutes per account a day, roughly 6 hours a week of media-buyer time. Now it’s a Slack message at 9:00.
- Silent failures became alerts: stale data can no longer reach the team unnoticed.
How the estimate works: pre-alert daily spend per ad × ad-days after the alert, compared with actual spend. It assumes the ads would have kept spending at their pre-alert rate. The accounts mix EUR, USD and CHF, summed as-is.
How I work on a project like this
- I verify tables with queries, not tool status. A backfill reported “OK” but was missing two weeks of TikTok data; I found the gap, reloaded it and matched it to Ads Manager.
- Rules are written down and confirmed with the client. Any assumption I add is logged as a decision.