Use Case

Analyzing and Predicting Customer Lifetime Value (CLTV)

Customer Lifetime Value (CLTV) is a critical KPI in any industry for making decisions related to sales, marketing, product development, and customer support. But tracking CLTV can be unwieldy and time consuming. In this use case, you’ll see how Tellius simplifies the process to calculate and understand in order to help organizations identify, track, and predict high CLTV customers to maximize brand potential and boost profitability/retention.

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.

  • Retail data 
  • Customer Demographics


  • 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 *
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"
) 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.


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.

See Tellius in Action

Get a Demo

Learn More

  • Segment 125
    Use Case

    Customer Segmentation Using Machine Learning and Clustering

    Identify high value segments of customers using automated machine learning and clustering techniques on customer data

    Read Now
  • Modern analytics & data intelligence for modern data stack

    Modern Analytics and Decision Intelligence for the Modern Data Stack

    Data-driven organizations should not be content with the same old approaches to data consumption. In the Modern Data Stack, each layer plays a key role in your organization’s goals to get better insights from vast amounts of data and to proactively uncover new opportunities for growth.

    Read Now
  • Augmented Analytics: How AI is Transforming BI

    Augmented Analytics: How AI is Transforming BI

    Supercharge your data insights initiatives with next generation business intelligence and analytics

    Read E-Book