Customer Lifetime Value Analysis Using BigQuery and GA4
Customer LTV Analysis with BigQuery and GA4
📌Introduction
A subscription ecommerce brand needed to calculate true customer lifetime value segmented by acquisition source, product category, and customer demographics to inform acquisition spending and retention investment decisions.
❗The Problem
The brand was making acquisition spending decisions based on first-purchase profitability and 30-day cohort data. They had no view of 12-month or 24-month LTV by channel, meaning they were under-investing in channels that drove high-LTV customers at seemingly high CPAs.
🔍Identifying the Causes
GA4's native LTV reports were limited to 90 days and could not be segmented by acquisition channel at the necessary granularity. The subscription data in Recurly was completely disconnected from GA4 session and acquisition data, making true LTV-by-channel analysis impossible within any single platform.
⚠️Consequences for the Business
The brand was over-investing in paid search (low CPA, low LTV) and under-investing in influencer partnerships (high CPA, very high LTV). One channel appeared 3x more efficient on 30-day data but 40% less efficient on 24-month LTV — a completely opposite strategic conclusion.
✅Solution
Built a BigQuery pipeline joining GA4 user-level data (with UTM source preserved as user property), Recurly subscription event data (charges, renewals, cancellations), and demographics from a customer survey. Created LTV calculation models at 30, 90, 180, 365, and 730 day horizons, segmented by acquisition channel, first product, and customer age bracket.
📈Results
Influencer partnership channel showed 24-month LTV of $487 vs. paid search at $198. Despite 2x higher first-purchase CPA for influencer, the LTV justified 2.4x higher acceptable CPA. Budget reallocation toward influencer partnerships increased 24-month portfolio LTV by 28% over 4 months of scaling.
🏁Conclusion
LTV analysis that reveals the multi-year value of customers by acquisition channel often completely overturns short-term ROAS-based budget allocation. The investment in building this infrastructure consistently delivers outsized strategic value.
💡Key Takeaways
Always preserve UTM source as a GA4 user property (not just session-scoped) to enable lifetime attribution. Join subscription/purchase data at the user level using hashed email as the key. Build LTV models at multiple time horizons — 30-day and 24-month conclusions are often opposite.