Why do teams track LTV and CAC separately?
Marketing owns CAC. Product owns LTV. They live in different dashboards, different meetings, different OKRs. I've sat in rooms where both numbers were discussed in the same hour and no one connected them.
What does the disconnect cost you?
A channel with a low CAC looks efficient until you realize it attracts users who never come back. A channel with a high CAC looks expensive until you realize those users stick around for years. Without LTV:CAC by channel, budget decisions are basically guesses with extra steps.
How do you build it in BigQuery?
The logic has three parts:
- Aggregate all revenue per user from GA4 purchase events
- Assign each user their first-touch channel using user_first_touch_timestamp
- Join with ad spend data by channel and calculate LTV:CAC per group
It's not a quick query, but it's a one-time build.
What do you actually do with this number?
A ratio below 1 means you're spending more to acquire a user than you'll ever make back. I've seen entire channels running at 0.6 LTV:CAC for months because no one had connected the two datasets. This analysis ends those conversations fast.
Want to get all my top Linkedin content? I regularly upload it to one Notion doc.
Go here to download it for FREE
