Modeling Ecommerce CLV in BigQuery

Estimating Customer Lifetime Value (CLV) allows ecommerce businesses to grasp the real worth of their customers beyond immediate revenue. The traditional method of calculating CLV by simply adding up all transactions can be misleading, as it compares customers at various stages of their journey without accounting for differences in their lifecycle.

To address this problem, we will use predictive CLV modeling and Google BigQuery, where we can store our estimates for easy and efficient access.

By uploading CLV data into BigQuery, we enable deep analysis and make it easy to generate ad hoc insights, accurately segment customers, and optimize marketing strategies. We also lay the foundation for creating Looker Studio dashboards and chatting with business data using large language models (LLMs).

Here's what we'll cover in this tutorial:

• Using CLV to drive business value
• Estimating a CLV model with Shopify data using Python
• Calculating CLV-based Key Metrics
• Modeling CLV in BigQuery

Driving Business Value with CLV

Understanding Customer Lifetime Value (CLV) is essential for ecommerce businesses. It helps in three ways: optimizing paid traffic, improving email marketing campaigns, and gaining insights from customer data.

Optimizing Paid Traffic: Knowing the CLV helps you spend your advertising budget wisely. You can see which channels bring in the most valuable customers and spend more money there. This leads to better results for the same or even lower costs.

Improving Email Marketing Campaigns: Knowing the CLV allows you to tailor your email campaigns more precisely by segmenting your customers based on their value. High-value customers could receive exclusive offers, motivating them to make additional purchases. For those who spend less, you can design email campaigns to engage them more deeply and gradually boost their spending.

Another key reason for estimating CLV is to enable comparisons between your customers' values. Many ecommerce businesses rely on realized revenue, summing up all transaction values to determine the customer's value. This approach, however, leads to flawed comparisons, as you're essentially comparing customers at different stages of their lifecycle with your business.

The challenge arises because customers come on board at various times, each with a different potential duration of interaction with your business. For instance, a customer who joined three months ago has had more opportunities to purchase than one who joined today. To make fair comparisons, it's essential to standardize the timeframe over which we evaluate CLV.

Predictive CLV modeling addresses this issue. For example, if you compare customers over a one-year horizon and you have a customer who was acquired three months ago, you would calculate their CLV by adding the revenue from their first three months to the forecasted revenue for the next nine months. This method produces a CLV that combines both realized and predicted revenue, offering a more accurate and equitable way to assess the value of different customers.

Data & Colab Notebook

We'll use the following Shopify order data for the CLV modeling