Real-world recipes · Marketing analytics
Customer acquisition cost (CAC) by channel
CAC = marketing spend on a channel ÷ customers that channel brought in. The spend lives in campaigns; the customers live in customers. Total each one per channel first, then join the totals together.
Try itEdit the query, then Run
| channel | total_spend | customers | cac |
|---|---|---|---|
| paid_search | 27020 | 84 | 321.67 |
| social | 16780 | 74 | 226.76 |
| partner | 10300 | 66 | 156.06 |
| referral | 3940 | 58 | 67.93 |
| organic_search | empty | 138 | empty |
5 rows
organic_search has no spend row, so the LEFT JOIN keeps it with an empty CAC instead of dropping it. That's the "keep the #N/A rows" join from the VLOOKUP lesson doing real work.
Your turn
Total marketing spend per month, as spend, in month order.
GROUP BY month on the campaigns table.
This page on one card
