Sign In
Get a demo
Get a demo
menu Menu close Close
Sign In
keyboard_backspace Back to Blog

Build a churn prevention campaign for Braze using AI

Ellen Perfect
Ellen Perfect
4 December 2024

Keeping customers engaged is key to preventing churn and keeping net dollar retention high. But identifying at-risk customers and intervening at the right moment is a major challenge. In this guide, we’ll walk through how to use Census’s AI Columns to create data points—like an engagement score and trend—that you can use to identify and win back your most valuable customers.

This step-by-step process will help you leverage these insights to power re-engagement campaigns in platforms like Braze.

Check out the video walkthrough below, or read on for a step by step:

 

Overview

In order to build a reengagement campaign, we'll need to first identify our high-value customers. We use a loyalty status where platinum, gold and silver customers are considered high value.

 

Next, we'll need to understand which customers are at risk of disengagement. To do this, we created two new columns in Braze: one for engagement score, and one for engagement score trend. This allows us to identify how our users' engagement is changing over time.

Screenshot 2024-12-05 at 8.52.33 AM

 

Step 2: Define your dataset and engagement events

In Census, everything begins with a dataset. If you’re comfortable with SQL, you can create this dataset yourself. If not, Census provides a sample SQL query you can share with your data team:

WITH last_60_day_event AS (
    SELECT 
        behavior.user_id, 
        behavior.event_id, 
        behavior.session_id, 
        behavior.event_name, 
        behavior.event_type, 
        behavior.event_timestamp, 
        behavior.device_type, 
        behavior.browser_type, 
        behavior.referral_source, 
        behavior.event_properties, 
        users.loyalty_status, 
        users.email
    FROM 
        census_on_census.production.user_behavior AS behavior
    LEFT JOIN 
        census_on_census.production.users AS users 
    ON 
        behavior.user_id = users.user_id
    WHERE 
        behavior.event_timestamp >= DATEADD(DAY, -60, CURRENT_DATE)
),
previous_60_day_event AS (
    SELECT 
        behavior.user_id, 
        behavior.event_id, 
        behavior.session_id, 
        behavior.event_name, 
        behavior.event_type, 
        behavior.event_timestamp, 
        behavior.device_type, 
        behavior.browser_type, 
        behavior.referral_source, 
        behavior.event_properties, 
        users.loyalty_status, 
        users.email
    FROM 
        census_on_census.production.user_behavior AS behavior
    LEFT JOIN 
        census_on_census.production.users AS users 
    ON 
        behavior.user_id = users.user_id
    WHERE 
        behavior.event_timestamp >= DATEADD(DAY, -120, CURRENT_DATE) 
        AND behavior.event_timestamp < DATEADD(DAY, -60, CURRENT_DATE)
),
last_60_day_aggregated AS (
    SELECT 
        user_id, 
        loyalty_status, 
        email, 
        ARRAY_AGG(
            OBJECT_CONSTRUCT(
                'event_name', event_name, 
                'event_type', event_type, 
                'event_timestamp', event_timestamp
            )
        ) AS event_detail_json_last_60_days
    FROM 
        last_60_day_event
    GROUP BY 
        user_id, loyalty_status, email
),
previous_60_day_aggregated AS (
    SELECT 
        user_id, 
        loyalty_status, 
        email, 
        ARRAY_AGG(
            OBJECT_CONSTRUCT(
                'event_name', event_name, 
                'event_type', event_type, 
                'event_timestamp', event_timestamp
            )
        ) AS event_detail_json_previous_60_days
    FROM 
        previous_60_day_event
    GROUP BY 
        user_id, loyalty_status, email
),
final AS (
    SELECT 
        COALESCE(l.user_id, p.user_id) AS user_id,
        COALESCE(l.email, p.email) AS email,
        COALESCE(l.loyalty_status, p.loyalty_status) AS loyalty_status,
        l.event_detail_json_last_60_days,
        p.event_detail_json_previous_60_days
    FROM 
        last_60_day_aggregated AS l
    FULL OUTER JOIN 
        previous_60_day_aggregated AS p 
    ON 
        l.user_id = p.user_id
)
SELECT 
    * 
FROM 
    final;
Copy

This code will help you pull in trackable events that indicate a user is engaged.

Step 3: Set up your AI Column

Once we have our dataset defined, we'll use a prompt to analyze the results and identify at-risk users. Screenshot 2024-12-05 at 8.58.39 AM

You can find the full text of the prompt below:

Here's your updated prompt with minor adjustments for clarity and flow: Objective You are analyzing e-commerce customer engagement data to evaluate user behavior patterns and calculate engagement metrics. Your task is to process two time periods of event data, calculate engagement scores, and identify changes or trends.

Input Data 

• EVENT_DETAIL_JSON_LAST_60_DAYS: Event data from the most recent 60 days. 
• EVENT_DETAIL_JSON_PREVIOUS_60_DAYS: Event data from the 60 days prior to that. Each event includes: 
• user_id: Unique identifier for the user. 
• event_type: The type of event (e.g., purchase, login). 
• event_value (optional): The value associated with the event (e.g., cart value). 
• event_timestamp: Timestamp of the event. 

Goals 

1. Calculate Engagement Scores (1-10) 

• Determine a score for each period based on event activity and patterns. 
• Use event categories and weights to calculate scores, applying bonuses or penalties as needed. 
• Normalize the final scores to the range of 1-10. 

2. Analyze Changes and Trends 

• Compare engagement scores between periods to calculate: 
• Numeric Change: current_score - previous_score. 
• Trend Classification: increasing, decreasing, or stable. 

Processing Rules 

1. Event Categories and Weights 

• Critical Conversion Events (50% weight): 
• purchase: 10 points 
• checkout: 8 points 
• add_to_cart: 5 points 
• Interest Indicators (30% weight): 
• product_view: 3 points 
• wishlist_add: 4 points 
• search: 2 points 
• General Events (20% weight): 
• signup: 3 points 
• login: 1 point 
• logout: -1 point 

2. Score Adjustments 

• Bonus (+1): Multiple purchase events. 
• Bonus (+1): add_to_cart or purchase events with event_value > 500. 
• Penalty (-1): More than 10 logout events. 

3. Normalization 

• Scale total points to fit a range of 1-10. Ensure scores fall within this range. 

4. Trend Analysis 

• Calculate numeric_change = current_score - previous_score. 
• Classify trends as: 
• Increasing: numeric_change > 0. 
• Decreasing: numeric_change < 0. 
• Stable: numeric_change == 0. 

5. Data Validation 

• Verify each event includes user_id, event_type, and event_timestamp. 
• Ignore duplicate events (same user_id, event_type, and timestamp within 5 minutes). 
• Skip invalid or incomplete records. 

6. Sequence Analysis 

• Ideal user path: product_view → add_to_cart → checkout → purchase. 
• Flag abandoned carts: Multiple add_to_cart events without a checkout. 

7. Error Handling 

• Return -1: Invalid data format. 
• Return -2: Missing required events (e.g., no valid user activity). 
• Return -3: Empty period (e.g., no events in the given time frame). Expected Output Your output must be in JSON format and include: { "current_score": <integer 1-10>, "previous_score": <integer 1-10>, "numeric_change": <integer>, "trend": "<increasing|decreasing|stable>" } Execution Notes 
• Ensure the output is automation-friendly and suitable for direct integration with marketing and CRM systems. 
• All error codes must be explicitly handled. 
• Validate the final output: 
• Scores must be integers between 1 and 10. 
• trend must align with numeric_change. This version keeps the original structure but refines word choice and phrasing for slight improvements in clarity while maintaining your intent.
Copy

The prompt has a few key sections:

  • It identifies your defined events as critical, indicating interest, or general - this will tell the AI how to weight these events in its consideration of the engagement score
  • It calculates an engagement score based on your defined event criteria
  • It calculates an engagement trend to identify whether a user is becoming more or less interested in your products
  • It requests the output in a JSON format that is directly compatible with a CRM, to make it easy to route this data in Braze or Hubspot

Step 4: Sync to Braze

With the engagement score and trend ready, sync these data points to Braze (or your preferred marketing platform). Use them to build segments of high-value customers whose engagement is decreasing.

For example, you could create a segment to identify gold, platinum, and silver loyalty members with a decreasing trend score.