# How to Build Customer Lifetime Value Prediction Models
Predicting Customer Lifetime Value (CLV) is the holy grail of marketing analytics. Instead of treating every customer the same, CLV allows Australian small business owners to identify who their high-value 'VIPs' are before they've even finished their second purchase, allowing you to allocate your marketing budget where it will generate the highest return.
By understanding the long-term value of a customer, you can move away from 'cost-per-acquisition' thinking and start focusing on 'profit-per-customer' growth.
Prerequisites
Before you begin, ensure you have the following:- Transaction History: At least 12–24 months of sales data (CSV export from Shopify, Xero, or your POS).
- Unique Identifiers: A way to link transactions to specific people (Email address or Customer ID).
- Tools: Microsoft Excel or Google Sheets (for basic models) or Python/R (for advanced predictive models).
- Clear Definitions: A decision on whether you are measuring 'Revenue CLV' or 'Profit CLV'.
---
Step 1: Define Your Objective and Timeframe
Before touching any data, decide what you are trying to predict. Are you looking at the 'Historical CLV' (what they have already spent) or 'Predictive CLV' (what they are likely to spend in the next 12 months)? For most Brisbane businesses, a 12-month or 3-year window is the most practical for planning marketing budgets.Step 2: Clean and Consolidate Your Data
Export your sales data into a single spreadsheet. You need four primary columns:- Customer ID (Email is best)
- Order Date
- Order Value (Excluding GST is usually best for accuracy)
- Order ID
Step 3: Calculate the 'Big Three' Metrics (RFM)
To build a predictive model, you first need to calculate three variables for every customer:- Recency: How many days since their last purchase?
- Frequency: How many times have they purchased in total?
- Monetary Value: What is the total or average value of their purchases?
In Excel, you can use a Pivot Table to group your data by Customer ID and calculate the MAX of Date (Recency), COUNT of Order ID (Frequency), and SUM of Order Value (Monetary).
Step 4: Determine Your Average Purchase Value (APV)
Calculate the average amount a customer spends per transaction.Formula: Total Revenue / Total Number of Orders.
Example: If your boutique earned $100,000 from 1,000 orders, your APV is $100.
Step 5: Calculate Average Purchase Frequency Rate (APFR)
Determine how often the average customer buys from you within your chosen timeframe.Formula: Total Number of Purchases / Total Number of Unique Customers.
Step 6: Calculate Customer Value (CV)
Multiply your APV by your APFR. This gives you the average value a customer brings to your business over the period you are measuring.Step 7: Calculate Average Customer Lifespan (ACL)
This is often the hardest part for Australian small businesses. How long does a customer stay active before they 'churn' (stop buying)? If you don't have years of data, a common industry benchmark is 1-3 years. Alternatively, calculate it as:1 / Churn Rate.
Step 8: The Simple Predictive CLV Formula
Now, combine these to get your baseline prediction:CLV = Customer Value x Average Customer Lifespan
This gives you a 'flat' average. However, to make it truly predictive, we need to segment.
Step 9: Segment Customers into Tiers
Not all customers are average. Create 'Buckets' based on your RFM scores from Step 3:- Platinum: High Frequency, High Spend, Recent (Protect these!)
- Gold: High Spend, but haven't visited in a while (Re-engagement targets)
- Silver: New customers with high initial spend (High potential)
- Bronze: One-time low-value shoppers.
Step 10: Apply a Retention Discount Rate
In finance, $100 today is worth more than $100 in three years. To be professional, apply a 'Discount Rate' (usually between 8-15%) to your future predicted earnings to account for inflation and risk. This ensures your CLV model isn't overly optimistic.Step 11: Build the Predictive Model (The 'Buy 'Til You Die' approach)
For advanced users, use the BG/NBD Model (Beta-Geometric/Negative Binomial Distribution). This statistical model predicts the probability of a customer being 'alive' (still a customer) and how many transactions they will make in the future. You can find free templates for this in Google Sheets or use Python libraries likeLifetimes.
Step 12: Validate Your Model
Take a slice of your data from a year ago. Use your model to 'predict' what those customers would spend over the following 12 months. Then, compare your prediction to what actually happened. If you are within 10-15% accuracy, your model is ready for use.Step 13: Integrate CLV into Your Marketing Strategy
Now that you have your predictions, change your bidding strategy. If you know a 'Platinum' lead is worth $2,000 over their life, you can afford to spend $200 on Google Ads to acquire them, even if their first purchase is only $150.Pro Tip: Use your CLV data to create 'Lookalike' audiences on Meta (Facebook/Instagram). Upload your list of high-CLV customers so the algorithm finds more people exactly like your best spenders.
Common Mistakes to Avoid
- Including GST: Always calculate CLV on net revenue. GST is not your money; including it inflates your perceived value.
- Ignoring Churn: If you assume a customer will stay forever, your CLV will be infinite. Be realistic about when a customer is 'lost'.
- Data Silos: Ensure your Shopify data and your in-store POS data are merged. If 'John Smith' buys online and in-store but appears as two different people, your frequency data will be wrong.
Troubleshooting
- "My CLV seems too high": Check if you have a few 'outlier' customers (like a B2B wholesaler) who are skewing the average. It's often best to remove these outliers from a general B2C model.
- "I don't have enough data": If you have fewer than 100 customers, focus on 'Historical CLV' first. Predictive models require a larger sample size to be statistically significant.
- "Dates are messy": Ensure your CSV export didn't flip Australian (DD/MM) and US (MM/DD) date formats, as this will break your Recency calculations.
Next Steps
Now that you have a working CLV model, you should:- Automate the report: Link your database to a tool like Looker Studio.
- Set up 'At-Risk' Triggers: Email customers automatically when they exceed their predicted 'Recency' gap.
- Refine your CPA: Adjust your Google Ads targets based on these new insights.
Need help extracting your data or building a custom dashboard for your Brisbane business? Contact the team at Local Marketing Group for a technical audit.