How to build a Customer Lifetime Value model

[LookerExperts]

This content, written by Tom Yeager and Tim Ramsell, was initially posted in Looker Blog on Mar 27, 2020. The content is subject to limited support.

We love our customers at Looker. They’ve been essential to helping us improve our product and services, from fixing unexpected bugs to adding new features to better serve their needs. This is also why our amazing support team, also known as the Department of Customer Love, has put customer feedback and relationships first since day one of Looker.

And because we’re so obsessed with our customers, we often dive into the quantitative side of our customers’ successes to better serve them, while also helping us to find and support new customers.

What characteristics do our most successful customers share?
Which marketing programs are the most efficient at bringing in new customers?
How can customer success strategies reduce churn and lengthen expected tenure?
And how do we measure all of this to understand how what we’re doing is working?

The answers to all of these questions can be different, which ultimately requires us to agree on how best to measure the value of a customer. To that end, we began to put together our own customer lifetime value (CLV) model, also known as an LTV model, into Looker.

Why build a Customer Lifetime Value model?

While there are different ways to calculate CLV, they all are aimed at creating a roadmap of net income over the expected life of a customer. The inputs of your CLV formula have a direct application from your current and predicted performance, and help to generate buy-in from strategic decision makers and employees working towards increasing profitability.

LTV customer acquisition costs (CAC) benchmarks

When compared to your customer acquisition costs (CAC), you can evaluate today’s spending decisions on new customers and balance that against the level of sales and marketing investment needed to sell your product. You can also compare this as a ratio of Quarter-over-Quarter to Year-over-Year to show the direction of the value of your investments directly against industry trends in any lifecycle of a company.

Why move to Looker?

This type of data and insight often lives in spreadsheets that are usually saved onto someone's desktop and buried underneath layers of complex formulas. This makes sharing this complicated analysis difficult, and limits one’s ability to find deeper answers on their own. It also may be subject to a phenomenon called Simpson’s Paradox, where high-level analysis can mute a root cause observation.

To combat data silos and buried business-drivers, we created a scalable ‘Looker-way’ to bring light to these real business questions. Thankfully for us, our data platform makes creating, automating, and sharing insights commonplace and easy to digest!

Metrics for calculating CLV

Since there are different ways and metrics to calculate and analyze CLV, we’ve tailored our formula for a simple and annualized understanding of our own SaaS business. At Looker, we’ve incorporated dollar-based net retention ($BNR), which is a year-over-year (YoY) comparison of annual recurring revenue (ARR) within a cohort, based on our typical renewal cycle. $BNR lumps customer expansion, upsells, and churn dollars all together. We also use a standard account churn rate to derive tenure.

Dollar-cased net retention calculation (expansion)

To calculate $BNR, we utilize three measures of ARR:

• Current ARR: This is the total ARR we recognize on a given day. Since we are creating a dynamic model, it’s imperative to track this historically so we can see how the ARR (and therefore CLV) changes over time.

• Previous year ARR: This is the total ARR exactly one year prior to the selected current ARR date.

• Pre-term ARR: This refers to the ARR from customers that did not exist one year prior to the selected current ARR date.

$BNR % = (Current ARR - Pre-term ARR) / Previous year ARR

With these three measures, we’re able to calculate the percent change from the previous year to the selected current date. Even though ours is an annualized example, you could make this logic daily, monthly, quarterly, or in any timeframe that makes the most sense for your organization.

Churn calculation

At Looker, calculating customer churn rates drives our tenure estimates. While $BNR factors in churned ARR dollars, in this case, customer churn only counts churned customers as a % of the total base who stop service entirely.

To do this, we take the date the customer cancelled their subscription and derive the following measures:

• Daily churn count: count of all lost accounts with a given cancellation date

• 30-Day rolling churn count: rolling sum of the daily churn count and the churn counts of the previous 30 days

• Active account count: total number of active accounts on a given day

We compare the 30-day rolling churn count to the active account count on a given day to get the daily 30-day churn rate. We’ve run the same calculation over a 90-day window, and found a smoother rate without such intense day-to-day fluctuations.

Tenure calculation

Calculating the account tenure can be as simple as taking the inverse of the 30-day churn rate (or in our case 90-day). This gives an estimate for the average number of months (or quarters if using the 90-day rate) we can expect a new customer to stay with us given our current churn rates.

At Looker, we decided to go with a more conservative approach to tenure and adjusted the formula slightly. Instead of doing a simple inverse we used the following formula:

- ln(0.5)/ln(1-churn rate)

This decreases the slope of the exponential curve, which is especially important at very low churn rates.

CLV calculation: putting it all together

Now that you have your $BNR and tenure measures, you can calculate expected CLV. We also discount our ARR by our corporate gross margin percent upfront, to generally account for our fixed and direct costs of doing business. To demonstrate our calculation using an annualized methodology and holding our $BNR % flat throughout the lifetime. We’ll assume an estimated tenure of three years.

Year 1: (Current ARR) * (Gross Margin %)
Year 2: (Year 1) * $BNR %
Year 3: (Year 2) * $BNR %

From here, simply sum the values for each of the years and you have your three-year estimated CLV! If your tenure changes, just change the number of years you include the in sum to get your new CLV. You can also adjust $BNR % to differing levels each year, depending on the nature of your business.

How to build a customer lifetime value model

When creating an effective, dynamic customer lifetime value model, the most important feature to include is date tracking. Having accurate start dates, churn dates, and the dates of any contract change are critical to being able to see LTV in a meaningful way. This is also where the biggest improvements begin when moving your data from a spreadsheet into a tool such as Looker.

Being able to store large amounts of data in a database and then easily explore the data with Looker enables countless new ways to analyze your data. Instead of being tied to a single extract for a point-in-time, we are now able to see what LTV looks like today as well as what it looked like yesterday, or last month, or even on this day last year.

We can even see how our CLV, $BNR, and tenure have changed over time and whether the programs we’ve implemented have succeeded. This also gives us the ability to include additional information, like account details from Salesforce, to further enhance our analytic capabilities.

By creating a CLV model like what we have discussed here, you no longer have to guess about how much value your customers will provide to your organization. With a customer lifetime value model in place, you can prioritize, customize, and track accounts, contracts, and programs at a much more granular level, opening your company up to new avenues of efficiency and profitability.

More on customer lifetime value

Now that we have our customer lifetime value model, we’ll be publishing another blog on practical use cases for our CLV model in the coming weeks.

In the meantime, check out our Looker Blocks that help you build a CLV model and find cohorts faster: