[Analytic Block] - Customer Lifetime Value - Simple Approach


(Marcus O'hanlon) #1

Customer Lifetime Value is a metric that every business loves to measure but it is notoriously trick to flexibly and easily track. What is the average value I expect every customer to deliver to my business and how do I expect them to cumulatively deliver that value over time. I have previously written about a way of tracking customer lifetime value and retention (Customer Retention and Lifetime Value), however this approach can be a bit daunting, this article seeks to provide a more simplistic approach to measuring customer lifetime value.

Expected Output
The output of this analysis should provide us with a method to quickly look at the lifetime value for customers and cohort it by anything your analytical heart desires, like customer traffic source as shown below:

Try it Yourself - How it’s Done

The below code block assumes we have two tables:
User table - a user table that contain user specific information such as registration date and demographic information.
Orders/Event Table - a orders/events table that tracks orders/events at user level.

Dimensions
Days since signup: for every order/event measure the users relative days since signup.

Measures
Distinct Users: in your orders/events table create a measure to count distinct all users.
Avg Sale Price: in your orders/events table create a measure to calculate the average sale price.

Table Calcs
Most of the math takes places in the table calc, we add a table calc to calculate the percentage of customers retained after x days, and then multiply that value by the average customer spend at day x perform a running total on this and we have our customer lifetime value curve:

Retention

${order_items.distinct_users} / offset(${order_items.distinct_users}, 1- row())

CLV

running_total(${retention} * ${order_items.avg_value})