Why CLTV is Important
In the marketing world, customer lifetime value (CLTV) is the most important aspect of the business. CLTV tells marketers how much revenue they can expect from one customer over the course of a business relationship. In this post, we showcase how Tellius can be leveraged to perform a performance deep dive using Retail data combined with transactions and customer demographics. Our goal is to understand the different personas among the customers — the first step to effective targeting and personalization.
The Data Inputs for CLTV
In this use case, we will analyze data coming from different sources.
Sources:
- Retail data
- Customer Demographics
Schema:
- Weekly purchase activity at customer level
- Performance metrics: Sales, Quantity, Frequency, Average Order Value (AOV)
- Sales Activity
- Customer Demographics
- Store details
Let’s dive into how a customer analytics team can go from connecting multiple complex data sources to delivering actionable insights to the business.
First, we start by loading data from multiple sources, including internal and external sources.
Once we have the data loaded, Tellius helps us evaluate the quality of the data by inferring the data types of each column and automatically detecting any inconsistencies in the data.
Calculating CLTV
Before analyzing the data, we need to perform feature engineering to extract more information about the customers. Tellius’s no-code/low-code data prep capabilities enables users to leverage point-and-click data transformations or even leverage existing SQL/Python scripts to create new variables directly in the platform to reduce back and forth between tools.
We start by creating shopper compositions categorized by the number of products purchased by each customer (Ultra Light, Light, Medium, Heavy, Ultra Heavy users).
Tellius’s preparation capabilities provides full flexibility for advanced users to incorporate the required business logic by manipulating the data in any way or creating Calculated Calculations, as shown below.
Step 1: Count the unique Products purchased by each customer Using SQL
SQL Script:
SELECT a.*, b.Total_Products_Purchased from Transaction a left join
(select household_id, count(distinct(PRODUCT_ID)) as Total_Products_Purchased
from Transaction group by household_id) b
on a.household_id = b.household_idHCP_ID, TRxs_total, NTILE (10) OVER (ORDER BY TRxs_total DESC) Decile FROM market_deciles
Step 2: Creating shopper composition using SQL
SQL Script:
select *
(case
when Total_Products_Purchased = 1 then "Ultra Light"
when Total_Products_Purchased between 2 and 5 then "Light"
when Total_Products_Purchased between 6 and 15 then "Medium"
when Total_Products_Purchased between 16 and 20 then "Heavy"
when Total_Products_Purchased > 20 then "Ultra Heavy"
end
) as Shopper_Composition from Transaction
We are concerned with identifying Customer Lifetime Value, which is a function of average order value (AOV), purchase frequency (Frequency), and customer lifespan (Customer_Relationship_Years), as per this logic, embodied in the Calculated Columns below.
Customer Lifetime Value = Average order value * Frequency * Lifespan
→ Average order value = Total Revenue / Total Orders
→ Frequency = Total Orders / Total Unique Customers
→ Lifespan = Number of years customer associated to purchase
Exploring CLTV Metrics
Now that we have CLTV calculated and the data is ready for analysis, let’s begin answering ad-hoc business questions using Tellius’s powerful Search interface which understands your question, calculates an answer in seconds, and returns the best fit visualization based on the variables used in your question.
Business Question: What is our monthly trend in Sales? Was there market recovery post-COVID?
SEARCH –> what are monthly sales in 2020?
When we look at the Sales trend, it does appear the market is starting to recover post-COVID. Let’s now look at the trend by shopper composition.
Business Question: What is the monthly trend in Sales by shopper composition?
SEARCH –> what are monthly sales in 2020 by shopper composition?
Generating Insights on CLTV
It appears that Medium composition shoppers are driving sales. What is causing this? Let’s see if Tellius Insights can detect why.
Tellius examined millions of data points in a matter of minutes and discovered Medium shoppers tend to use their credit card, purchase makeup, and shop at high value stores more frequently. Unlike the present method, which is manual and hypothesis-driven, Tellius provides us with critical information, ensuring that we do not miss any possibilities. Tellius automatically analyses every potential combination of why volume changed and identifies the primary factors for this trend break, removing bias from the study. This helps the company to restructure its approach and reprioritizes its field force based on these new insights.
Let’s now configure the Tellius Feed, so Tellius provides proactive insights into the leading indicators of performance. This provides tremendous value and helps us automate large portions of our performance deep dives.
How? As our data streams in, Tellius continuously monitors all our leading indicators and automatically alerts us with actionable insights if there have been any statistically significant changes.
Business Question: How is CLTV is performing across different Shopper compositions?
SEARCH –> how is CLTV performing across different Shopper compositions?
Insight –> what drives CLTV for Medium vs Heavy shoppers?
Sharing CLTV Metrics and Analysis
Now we can create a vizpad to track our various segment’s customer lifetime, CLTV and other KPIs — to inform our marketing strategy related to customer acquisition, cross sell/upsell, and retention.
Predicting CLTV
We have identified existing high/medium/low CLTV customers, now let’s leverage Tellius’s AutoML with a single click to predict future behavior and identify high CLTV customers.
Summary
As showcased above, Tellius provides teams with a unified analytics experience across data sources to enable faster answers to what happened with a Google-like interface, explanations for business metric changes, and how to improve business outcomes by understanding the drivers of those behaviors.
Take Tellius for a free 14 day spin (no credit card necessary). Tellius is useful for a variety of other eCommerce & Retail applications or download our Guide to AI-Driven Analytics for eCommerce.
