Marketing Analyst
The signature question
Paste this into your AI tool:
Using reports.channel_daily, compute blended CAC and ROAS by channel for the last 90 days available. Break down:
- Paid Search - sum cost_usd and conversions, then
cac = cost_usd / conversions,roas = attributed_revenue / cost_usd - Email - sum attributed_revenue (cost is $0); show revenue-per-thousand-recipients as a soft proxy for efficiency
- Organic - sum sessions, conversions, attributed_revenue (cost is $0); show conversion rate
Then produce a blended CAC for the period: total cost_usd across all channels / total conversions across all channels - and call out that this only counts conversions captured in reports.channel_daily, so it understates true acquisition if email or organic conversion tracking is partial.
Default to reports.channel_daily. Only drop into staging.* or raw.* if the user is asking for a column that hasn't been surfaced upstream (e.g. campaign-level breakouts), and tell me when you do.
Show me the SQL first. State the attribution caveats in plain English. Only execute once I approve the plan.