LTV (Lifetime Value) gives you an idea of how much revenue you can expect from a customer throughout their entire relationship with your business. In this post, we’ll explore how to calculate LTV by cohorts in BigQuery.
First off, a cohort is a group of users who started interacting with your product during the same time frame. For example, all the customers who made their first session in January would fall into the same cohort. To calculate LTV for each cohort, we need to look at how many users made a purchase and the revenue they generated.
In our approach, we first identify each user’s first session date. Then, for each cohort, we tally up the total number of buyers and the total revenue. This allows us to compute LTV as the ratio of revenue to the number of customers.
This method will help you gain deeper insights into customer behavior and make informed decisions to optimize your marketing strategies.
Check out the attached image to see the steps involved in calculating LTV in BigQuery.
If you work with GA4 to BigQuery exports, be sure to check out my SQL cheat sheet.
