How ad_spend works in product_sales_summary_by_transaction
The short version
We pull ad spend from Northbeam, map it to products, then spread it across the rows in our final table based on how many orders each product/plan/gender combo had that day.
Where does spend come from?
It starts in channel_spend_summary, which combines:
- Northbeam data (Google Ads, Meta, TikTok, etc.)
- Offline channel data (but we filter most of this out for the main
ad_spendcolumn)
We exclude offline affiliate channels (Vibe, MNTN, The Offer, Katalys, Influencers) from ad_spend — those show up in their own columns like offer_affiliate_spend instead.
How does spend get assigned to products?
Most products match directly by name. But a few need special handling:
| Spend comes in as | Gets split to |
|---|---|
Medkit | All Med-Kit 1, Med-Kit 2, etc. rows that exist that day |
HRT | All HRT kit variants that day |
Sermorelin | All Sermorelin variants that day |
GLP-1 Generic | 50% to Tirzepatide, 50% to Injectable Sema |
If there are no matching product rows for a given day (e.g., no Med-Kit orders), the spend stays on the original name so it doesn’t vanish.
How does spend get split across rows?
Our final table has rows for each (date, product, membership_plan, gender) combo. But spend only comes in at the (date, product) level.
So we allocate it by order share:
row's ad_spend = product's total spend × (row's orders / product's total orders that day)
If a product has spend but zero orders, we keep the spend on a single row rather than losing it.
Quick reference: key CTEs
| CTE | What it does |
|---|---|
campaign_spend_pre | Raw spend by date + product from channel_spend_summary |
product_spend_aggregated | Handles the Medkit/HRT/Sermorelin/GLP-1 splits |
final_metrics_pre_ad_allocated_by_plan | Allocates spend to plan/gender rows by order share |
final_metrics | Final output (also keeps “Uncategorized” spend separate) |
Watch out for
- Spend can drop if there’s a mismatch between product names in spend data vs. order data. The join is on
(date, standardized_product_name). - Affiliate spend is separate — it’s in columns like
offer_affiliate_spend, not inad_spend.