CPG Analytics, Deep Dive, Retail Analytics

Expediting Customer Lifetime Value (CLTV) Performance Analytics with AI

Tracking Customer Lifetime Value

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. Tellius simplifies this process and helps firms identify, track, and predict high CLTV customers to maximize brand potential and boost profitability/retention.


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


  • 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

Tellius Solution

Let’s dive into how a customer analytics team can go from connecting multiple complex data sources to delivering actionable insights to the business.

Data Preparation

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. 

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

Exploratory Analysis

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?

Automated Insights

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?


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.

Predictive Analytics

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.


Leave reply

Read Similar Posts

  • Deep Dive

    Customer Retention Analysis in Tellius

    Customer retention analysis is business critical but time consuming. In this post, we showcase how to perform customer retention analysis in Tellius — the leading AI-Powered Insights & Analytics platform — using customer data, usage data, and product plan details.

  • Deep Dive

    Customer Segmentation via RFM and AI-Powered Insights in Tellius

    Customer segmentation is the process of dividing existing and potential customers into sub-groups (also known

  • Deep Dive

    The Missing Insights and AI-powered Analytics Layer of the Modern Data Analytics Stack

    AI-powered Insights & Analytics is the missing layer of the Modern Data Analytics Stack that enables analysts to control a lot more of the data value chain and increase their impact on organizations.