[Analytic Block] - Customer Lifetime Value - Simple Approach

  • 28 September 2017
  • 0 replies
  • 825 views

Userlevel 4

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})




0 replies

Be the first to reply!

Reply