Predictive Values View

Optimove's Single Customer View provides a set of predictive values continuously updated by machine learning models. By querying VW_CUSTOMER_PROFILES_REAL_FIELD_NAMES, you can establish a view of these predictive attributes, track them over time, and set up monitoring and QA systems for model performance.

This guide explains how to retrieve five key predictive insights, each offering a specific customer behavior probability useful for business analysis.

1. Risk of Churn

The Risk of Churn model predicts the likelihood that a customer will churn within the next 28 days to two months. This is indicated by a transition from any active lifecycle stage (LCS) to a churn LCS.

Churn Prediction Attributes

REAL_FIELD_NAME	ALIAS_FIELD_NAME	DESCRIPTION
`IF_CHURN_PREDICTION`	Churn Probability Score	Probability of churn in the next 28 days to 2 months
`IF_CHURN_PREDICTION_PERCENTILE`	Rank in Churn Probability	Percentile for churn probability
`IF_CHURN_PREDICTION_PERCENTILE_LCS`	Rank in LCS - Churn Probability	Percentile for churn probability within LCS

SQL Query: Churn Prediction

-- Churn prediction view for customers in active lifecycle stages
SELECT 
    PROFILE_DATE,
    CLIENT_CUSTOMER_ID,
    LIFECYCLESTAGE,
    IF_CHURN_PREDICTION,
    IF_CHURN_PREDICTION_PERCENTILE,
    IF_CHURN_PREDICTION_PERCENTILE_LCS
FROM 
    VW_CUSTOMER_PROFILES_REAL_FIELD_NAMES
WHERE 
    LIFECYCLESTAGE IN ('Active', 'New') 
    AND PROFILE_DATE > (CURRENT_DATE - 2)
ORDER BY 
    PROFILE_DATE DESC, IF_CHURN_PREDICTION DESC
LIMIT 1000;

SQL Query: Weekly Churn Prediction Summary

This query provides a weekly summary of minimum and maximum churn prediction scores and percentiles for each customer, giving insight into churn trends over time.

SELECT 
    WEEK(PROFILE_DATE),
    CLIENT_CUSTOMER_ID,
    LIFECYCLESTAGE,
    MIN(IF_CHURN_PREDICTION) AS Min_Churn_Prediction,
    MAX(IF_CHURN_PREDICTION) AS Max_Churn_Prediction,
    MIN(IF_CHURN_PREDICTION_PERCENTILE) AS Min_Churn_Prediction_Percentile,
    MAX(IF_CHURN_PREDICTION_PERCENTILE) AS Max_Churn_Prediction_Percentile,
    MIN(IF_CHURN_PREDICTION_PERCENTILE_LCS) AS Min_Churn_Prediction_Percentile_LCS,
    MAX(IF_CHURN_PREDICTION_PERCENTILE_LCS) AS Max_Churn_Prediction_Percentile_LCS
FROM 
    VW_CUSTOMER_PROFILES_REAL_FIELD_NAMES 
WHERE 
    LIFECYCLESTAGE IN ('Active', 'New', 'Churn') 
    AND PROFILE_DATE > '2024-01-01'
GROUP BY 
    WEEK(PROFILE_DATE), CLIENT_CUSTOMER_ID, LIFECYCLESTAGE
ORDER BY 
    WEEK(PROFILE_DATE) DESC
LIMIT 1000;

2. Conversion Rate

The Conversion Rate model forecasts the probability of a customer transitioning from non-paying to paying status, indicated by a move from "Registered Only" or "Non-Depositor" LCS to New LCS.

Conversion Prediction Attributes

REAL_FIELD_NAME	ALIAS_FIELD_NAME	DESCRIPTION
`IF_CONVERSION_PREDICTION`	Conversion Probability Score	Probability of converting to a paying customer
`IF_CONVERSION_PREDICTION_PERCENTILE`	Rank in Conversion Probability	Percentile for conversion likelihood

SQL Query: Conversion Prediction

-- Conversion prediction view for customers in "Registered Only" lifecycle stage
SELECT 
    PROFILE_DATE,
    CLIENT_CUSTOMER_ID,
    LIFECYCLESTAGE,
    IF_CONVERSION_PREDICTION,
    IF_CONVERSION_PREDICTION_PERCENTILE
FROM 
    VW_CUSTOMER_PROFILES_REAL_FIELD_NAMES
WHERE 
    LIFECYCLESTAGE = 'Registered Only' 
    AND PROFILE_DATE >= (CURRENT_DATE - 2)
ORDER BY 
    PROFILE_DATE DESC, IF_CONVERSION_PREDICTION DESC
LIMIT 1000;

3. Reactivation Rate

The Reactivation Rate model predicts the likelihood of a churned customer re-engaging, seen as a shift from Churn LCS to Reactivated LCS.

Reactivation Prediction Attributes

REAL_FIELD_NAME	ALIAS_FIELD_NAME	DESCRIPTION
`IF_REACTIVATION_PREDICTION`	Reactivation Probability Score	Probability of reactivation
`IF_REACTIVATION_PREDICTION_PERCENTILE`	Rank in Reactivation Probability	Percentile for reactivation likelihood

SQL Query: Reactivation Prediction

-- Reactivation prediction view for churned customers
SELECT 
    PROFILE_DATE,
    CLIENT_CUSTOMER_ID,
    LIFECYCLESTAGE,
    IF_REACTIVATION_PREDICTION,
    IF_REACTIVATION_PREDICTION_PERCENTILE
FROM 
    VW_CUSTOMER_PROFILES_REAL_FIELD_NAMES
WHERE 
    LIFECYCLESTAGE = 'Churn' 
    AND PROFILE_DATE >= (CURRENT_DATE - 2)
ORDER BY 
    PROFILE_DATE DESC, IF_REACTIVATION_PREDICTION DESC
LIMIT 1000;

4. Becoming Top Spender (Beta)

This beta model predicts the likelihood of an active customer reaching the top 5% of customers by lifetime spend over the next 3-6 months.

Top Spender Prediction Attributes

REAL_FIELD_NAME	ALIAS_FIELD_NAME	DESCRIPTION
`IF_IS_TOP_SPENDER`	Is Top Spender?	Indicates if a customer is a top spender
`IF_PROBABILITY_TO_BECOME_TOP_SPENDER`	Becoming Top Spender Score	Probability of becoming a top spender in the next 3-6 months
`IF_TOP_SPENDER_PREDICTION_PERCENTILE`	Rank in Becoming Top Spender	Percentile for likelihood of becoming a top spender
`IF_TOP_SPENDER_PREDICTION_PERCENTILE_LCS`	Rank by LCS Becoming Top Spender	Percentile within lifecycle stage

SQL Query: Top Spender Prediction

-- View for predicting top spender potential for active customers
SELECT 
    PROFILE_DATE,
    CLIENT_CUSTOMER_ID,
    LIFECYCLESTAGE,
    IF_IS_TOP_SPENDER,
    IF_PROBABILITY_TO_BECOME_TOP_SPENDER,
    IF_TOP_SPENDER_PREDICTION_PERCENTILE,
    IF_TOP_SPENDER_PREDICTION_PERCENTILE_LCS
FROM 
    VW_CUSTOMER_PROFILES_REAL_FIELD_NAMES
WHERE 
    LIFECYCLESTAGE IN ('Active', 'New') 
    AND PROFILE_DATE >= (CURRENT_DATE - 2) 
    AND IF_IS_TOP_SPENDER = 'NO'
ORDER BY 
    PROFILE_DATE DESC, IF_PROBABILITY_TO_BECOME_TOP_SPENDER DESC;

5. Future Value (Beta)

The Future Value model estimates a customer’s projected value over the next 12 months. This metric is particularly valuable for strategic planning and customer prioritization.

Future Value Prediction Attributes

REAL_FIELD_NAME	ALIAS_FIELD_NAME	DESCRIPTION
`IF_FUTURE_VALUE_PREDICTION`	Future Value Prediction	Predicted value of the customer over the next 12 months
`IF_FUTURE_VALUE_PREDICTION_PERCENTILE`	Rank in Future Value Prediction	Percentile for future value prediction
`IF_FUTURE_VALUE_PREDICTION_PERCENTILE_LCS`	Rank by LCS Future Value Prediction	Percentile within lifecycle stage

SQL Query: Future Value Prediction

-- View for future value prediction for customers
SELECT 
    PROFILE_DATE,
    CLIENT_CUSTOMER_ID,
    LIFECYCLESTAGE,
    IF_FUTURE_VALUE_PREDICTION,
    IF_FUTURE_VALUE_PREDICTION_PERCENTILE,
    IF_FUTURE_VALUE_PREDICTION_PERCENTILE_LCS
FROM 
    VW_CUSTOMER_PROFILES_REAL_FIELD_NAMES 
WHERE 
    PROFILE_DATE >= (CURRENT_DATE - 2)
ORDER BY 
    PROFILE_DATE DESC
LIMIT 1000;

Additional Resources

Refer to the Data Shares Glossary for more detail on field definitions and how they relate to Optimove’s data properties.