Customer Lifetime Value (CLV) Analysis

Introduction to Customer Lifetime Value Analysis

Customer Lifetime Value (CLV) is a metric that quantifies the total value a customer contributes to your business over their entire lifecycle. Understanding CLV allows for targeted customer segmentation, personalized marketing, and efficient allocation of resources.

In the following example, we draw from a Customer Lifetime Value Dataset on Kaggle.

For this analysis, we will name the dataset marketing_customer_value, which encapsulates key features like the number of policies, monthly premium auto, vehicle size, and vehicle class.

You can download the raw dataset for this analysis here.

SQL-inf Query for CLV Prediction

SELECT * marketing_customer_value FROM PREDICT(Customer_Lifetime_Value, ignore=Customer)

This SQL query is designed to predict the Customer Lifetime Value (clv) while disregarding the customer_id, which has no bearing on CLV predictions.

Key Findings

Our analysis yielded the following:

Model Performance: The model achieved an R2 score of 65%, which is quite promising for predicting Customer Lifetime Value.
Feature Importance: The top four features contribute to 80% of the predictive power. These are: Number of Policies, Monthly Premium Auto, Vehicle Size, and Vehicle Class.
Highlights:
1. Number of Policies: A noticeable increase in CLV is observed when a customer holds 2 policies, although this value stabilizes for customers with 3 or more policies.
2. Monthly Premium Auto: Customers seem to gravitate towards moderate premium rates, showing signs of price sensitivity as premium costs escalate.
3. Vehicle Size: Medium-sized vehicles generate the highest median CLV, followed by small and large vehicles.
4. Vehicle Class: Luxury Cars and Luxury SUVs have remarkably high median CLVs compared to other classes, possibly due to the higher premiums they attract.

Strategic Conclusions and Recommendations

Policy Bundling: Customers with two policies exhibit significantly higher CLVs. Strategies like bundled packages should be developed to encourage customers to take up more than one policy.
Pricing Sensitivity: The data indicates that customers are sensitive to high premium rates. Consider offering tiered pricing or discounts to attract and retain this customer segment.
Vehicle-Based Personalization: Tailoring insurance packages based on vehicle size and class could be beneficial, as these variables substantially influence CLV. Special packages for luxury car owners could be a high-revenue stream.
Re-evaluation of Discounts: The fact that CLV decreases and stabilizes for customers with 3 or more policies may warrant a re-evaluation of the discounts or incentives offered for multiple policies. It may be possible to optimize this to increase profitability.

By incorporating these targeted strategies based on the key contributing factors to CLV, businesses can enhance customer value, increase profitability, and gain a competitive edge.

Advanced View Creation for Customer Lifetime Value

Here we will give examples of views that could be used for predicting customer lifetime value.

Basic Demographics and Spend

SELECT 
  c.customer_id,
  c.age,
  c.gender,
  CASE 
    WHEN DATE_ADD('MONTH', 6, c.join_date) <= CURRENT_DATE THEN SUM(t.amount_spent)
    ELSE NULL
  END AS cltv_at_6_months
FROM 
  customers c
LEFT JOIN 
  transactions t ON c.customer_id = t.customer_id AND t.purchase_date BETWEEN c.join_date AND DATE_ADD('MONTH', 6, c.join_date)
GROUP BY 
  c.customer_id;

Product Categories

SELECT 
  c.customer_id,
  p.product_category,
  AVG(t.amount_spent) as avg_spend_per_category,
  CASE 
    WHEN DATE_ADD('MONTH', 6, c.join_date) <= CURRENT_DATE THEN SUM(t.amount_spent)
    ELSE NULL
  END AS cltv_at_6_months
FROM 
  customers c
LEFT JOIN 
  transactions t ON c.customer_id = t.customer_id AND t.purchase_date BETWEEN c.join_date AND DATE_ADD('MONTH', 6, c.join_date)
LEFT JOIN 
  products p ON t.product_id = p.product_id
GROUP BY 
  c.customer_id, p.product_category;

Geographic Location

SELECT 
  c.customer_id,
  l.country,
  l.city,
  CASE 
    WHEN DATE_ADD('MONTH', 6, c.join_date) <= CURRENT_DATE THEN SUM(t.amount_spent)
    ELSE NULL
  END AS cltv_at_6_months
FROM 
  customers c
LEFT JOIN 
  transactions t ON c.customer_id = t.customer_id AND t.purchase_date BETWEEN c.join_date AND DATE_ADD('MONTH', 6, c.join_date)
LEFT JOIN 
  locations l ON c.location_id = l.location_id
GROUP BY 
  c.customer_id;

Time of Purchase

SELECT 
  c.customer_id,
  EXTRACT(HOUR FROM t.purchase_date) as purchase_hour,
  COUNT(*) as num_purchases,
  CASE 
    WHEN DATE_ADD('MONTH', 6, c.join_date) <= CURRENT_DATE THEN SUM(t.amount_spent)
    ELSE NULL
  END AS cltv_at_6_months
FROM 
  customers c
LEFT JOIN 
  transactions t ON c.customer_id = t.customer_id AND t.purchase_date BETWEEN c.join_date AND DATE_ADD('MONTH', 6, c.join_date)
GROUP BY 
  c.customer_id, purchase_hour;

Payment Method

SELECT 
  c.customer_id,
  pm.payment_type,
  COUNT(*) as num_transactions,
  CASE 
    WHEN DATE_ADD('MONTH', 6, c.join_date) <= CURRENT_DATE THEN SUM(t.amount_spent)
    ELSE NULL
  END AS cltv_at_6_months
FROM 
  customers c
LEFT JOIN 
  transactions t ON c.customer_id = t.customer_id AND t.purchase_date BETWEEN c.join_date AND DATE_ADD('MONTH', 6, c.join_date)
LEFT JOIN 
  payment_methods pm ON t.payment_method_id = pm.payment_method_id
GROUP BY 
  c.customer_id, pm.payment_type;