Customer Retention Analysis

Insights:

• The customer acquisition trend shows a significant increase in the number of new customers acquired each month, particularly from mid-2017 to early 2018. This indicates that Olist experienced rapid growth during this period, likely due to successful marketing campaigns, expansion of product offerings, or increased brand awareness.
• The retention rates show a general decline over time, with the highest retention observed in the first month after acquisition (month 1 retention) and a significant drop in retention by month 3 and month 6. This suggests that while Olist was successful in acquiring new customers, retaining them over the long term has been a challenge. The sharp decline in retention rates after the first month indicates that many customers may not have had a positive experience or found enough value to continue shopping with Olist.
• The retention rates also show some variability across different cohorts, with certain months (e.g., 2017-08-01 and 2017-09-01) exhibiting higher retention rates compared to others. This could be due to various factors such as seasonal promotions, changes in product offerings, or improvements in customer service during those periods. Analyzing the specific actions taken during these months could provide insights into strategies that may have contributed to better retention.

Pareto Principle Analysis for Customer Base

Solution: Pareto Principle, also known as the 80/20 rule, suggests that a small percentage of customers (20%) often contribute to a large percentage of revenue (80%). We can check whether this principle holds true for our case by applying the following steps:

1. First, we will calculate the total lifetime spend for each unique customer by summing up their payment values across all their orders.
2. Next, we will rank the customers based on their total spend and calculate the cumulative revenue contribution for each customer.
3. Then, we will use the percent_rank window function to determine which customers fall into the top 20% of spenders. This function will assign a percentile rank to each customer based on their total spend. This process is done by ordering the customers in descending order of their total spend and then dividing them into percentiles. Customers with a percentile rank of 0.20 or less will be classified as the top 20% of spenders.
4. Besides the ranking, we will also calculate the cumulative revenue contribution for each customer to see how much of the total revenue is contributed by the top 20% of customers. This will allow us to determine if the top 20% of customers indeed contribute to around 80% of the total revenue, as suggested by the Pareto Principle.
5. Finally, we will calculate the total revenue contributed by the top 20% of customers and compare it to the total revenue to see if it adheres to the 80/20 rule.

If the pareto principle holds true, it means our revenue comes predominantly from a small group of high-value customers. Understanding the profile of these top-tier customers can help us tailor our marketing strategies, improve customer retention, and focus on providing exceptional service to this valuable segment of our customer base.

with customer_revenue as (
    -- step 1: calculate total lifetime spend per unique customer
    select 
        c.customer_unique_id,
        sum(p.payment_value) as total_spend
    from 
        orders o
    inner join 
        customers c on o.customer_id = c.customer_id
    inner join 
        payments p on o.order_id = p.order_id
    where 
        o.order_status not in ('canceled', 'unavailable')
    group by 
        c.customer_unique_id
),

revenue_ranking as (
    -- step 2: rank customers by spend and calculate cumulative totals
    select 
        customer_unique_id,
        total_spend,
        -- using percent_rank to determine the top 20% percentile
        percent_rank() over (order by total_spend desc) as percentile_rank,
        -- running total of revenue
        sum(total_spend) over (order by total_spend desc) as cumulative_revenue,
        -- total revenue across all customers for percentage calculation
        sum(total_spend) over () as grand_total_revenue
    from 
        customer_revenue
),

pareto_summary as (
    -- step 3: identify the contribution of the top 20%
    select 
        case 
            when percentile_rank <= 0.20 then 'Top 20% (VIPs)'
            else 'Bottom 80% (Long Tail)'
        end as customer_tier,
        count(customer_unique_id) as customer_count,
        sum(total_spend) as tier_revenue,
        max(grand_total_revenue) as total_revenue
    from 
        revenue_ranking
    group by 
        case 
            when percentile_rank <= 0.20 then 'Top 20% (VIPs)'
            else 'Bottom 80% (Long Tail)'
        end
)

-- final output: calculating the percentage contribution per tier
select 
    customer_tier,
    customer_count,
    cast(tier_revenue as decimal(15,2)) as revenue_contribution,
    cast(100.0 * tier_revenue / total_revenue as decimal(5,2)) as revenue_pct
from 
    pareto_summary
order by 
    revenue_pct desc;

Results:

Insights:

• The analysis revealed that the top 20% of customers contribute to approximately 53.58% of the total revenue. In reality, there is not a perfect adherence to the 80/20 rule, as the top 20% of customers do not contribute to 80% of the revenue always. However, considering that more than half of the revenue of our company comes from only 20% of all customers, it is still a pareto principle type of distribution, where a small percentage of customers contribute to a large percentage of revenue.
• The top 20% of customers, often referred to as VIPs, are a critical segment for Olist. They contribute significantly to the company’s revenue and likely have higher engagement and loyalty. Understanding the characteristics of these VIP customers, such as their purchasing behavior, preferences, and demographics, can help Olist tailor its marketing strategies and customer service efforts to better meet their needs and enhance their experience.

Purchase Frequency and Inactivity Analysis

4. For the segment of customers with multiple purchases, what is the average time interval (in days) between consecutive orders? How does this ‘purchase latency’ vary across different product categories?

*Solution: To analyze the purchase frequency and inactivity patterns among customers with multiple purchases, we will follow these steps:

1. First, we will identify customers who have made multiple purchases by counting the number of orders for each unique customer. We will focus on those customers who have made at least two purchases to analyze the time interval between their consecutive orders.
2. Next, we will calculate the time interval (in days) between consecutive orders for each customer. This can be done using the lag window function to access the previous order’s purchase timestamp and then calculating the difference in days between the current order and the previous order.
3. We will also join the relevant tables to include product category information for each order, allowing us to analyze how purchase latency varies across different product categories.
4. Finally, we will calculate the average purchase latency for each product category and identify any significant variations in purchase frequency across categories. This analysis will help us understand customer behavior and identify potential opportunities for targeted marketing or promotions based on purchase patterns in different product categories.

with purchase_sequences as (
    -- step 1: identify orders for repeat customers and find the previous order date
    -- we use lag() to get the prior purchase timestamp for each unique user
    select 
        c.customer_unique_id,
        o.order_purchase_timestamp,
        p.product_category_name,
        lag(o.order_purchase_timestamp) over (
            partition by c.customer_unique_id 
            order by o.order_purchase_timestamp
        ) as previous_order_timestamp
    from 
        orders o
    inner join 
        customers c on o.customer_id = c.customer_id
    inner join 
        order_items oi on o.order_id = oi.order_id
    inner join 
        products p on oi.product_id = p.product_id
    where 
        o.order_status not in ('canceled', 'unavailable')
),

latency_calculations as (
    -- step 2: calculate days between orders and filter only for the second+ purchases
    select 
        customer_unique_id,
        product_category_name,
        datediff(day, previous_order_timestamp, order_purchase_timestamp) as days_to_next_order
    from 
        purchase_sequences
    where 
        previous_order_timestamp is not null -- ensures we only look at repeat purchase events
)

-- final output: average latency per category to identify replenishment cycles
select 
    lc.product_category_name,
    pcnt.product_category_name_english,
    count(*) as repeat_purchase_events,
    avg(days_to_next_order) as avg_days_between_orders,
    min(days_to_next_order) as min_days,
    max(days_to_next_order) as max_days
from 
    latency_calculations lc
left join product_category_name_translation pcnt 
	on pcnt.product_category_name  = lc.product_category_name
group by 
    lc.product_category_name,
    pcnt.product_category_name_english 
having 
    count(*) >= 5 -- filtering for statistical significance in categories
order by 
	repeat_purchase_events desc,
    avg_days_between_orders asc;

Results:

Insights:

• Analyzing the avg_days_between_orders is not sufficient to make important conclusions about purchase frequency, since the more important metric is how many times the products are purchased repeatedly. Therefore, we should consider the repeat_purchase_events column as well, which shows us how many times the products in each category were purchased repeatedly. For example, the ‘moveis_decoracao’ (furniture_decor) category has the highest number of repeat purchase events (2266) and an average of 7 days between orders, indicating that customers frequently repurchase items in this category within a short time frame. This could suggest that customers are buying multiple items for home decoration or that they are replacing items frequently.
• The following categories with high repeat purchase events include ‘cama_mesa_banho’ (bed_bath_table) and ‘informatica_acessorios’ (computers_accessories), which also show relatively short average days between orders (14 and 12 days, respectively). This indicates that customers in these categories also have a high purchase frequency, possibly due to the nature of the products, which may require regular replacement or upgrading.
• On the other hand, categories like ‘artigos_de_festas’ (party_supplies) and ‘musica’ (music) have fewer repeat purchase events and longer average days between orders, suggesting that customers may not need to repurchase these items as frequently. This could be due to the fact that party supplies are often used for specific events and music may be purchased for long-term enjoyment, leading to less frequent repurchases.
• Overall, the analysis of purchase latency across different product categories provides valuable insights into customer behavior and can help Olist identify which categories have higher purchase frequencies. This information can be used to optimize inventory management, tailor marketing strategies, and improve customer retention by targeting customers with personalized offers based on their purchasing patterns in specific categories.

High Risk of Churn Detection

5. Which customers have exceeded the average purchase cycle by more than 2 standard deviations without placing a new order? Can we flag these users as ‘High Risk of Churn’ based on their deviation from the typical repurchase behavior?

Solution: To identify customers who are at high risk of churn based on their purchase behavior, we will follow these steps:

1. First, we will calculate the average purchase cycle (the average time interval between consecutive orders) and the standard deviation of the purchase cycle for customers with multiple purchases. This will help us establish a benchmark for typical repurchase behavior.
2. Next, we will calculate the recency of the last purchase for each customer by finding the difference in days between their last order date and the reference date (the maximum order purchase timestamp in the dataset).
3. We will then compare the recency of each customer’s last purchase to the average purchase cycle plus two standard deviations. Customers whose recency exceeds this threshold will be flagged as ‘High Risk of Churn’ because they have gone significantly longer than the typical repurchase cycle without making a new purchase.
4. Finally, we will count the number of customers who fall into the ‘High Risk of Churn’ category to understand the potential size of this segment and to inform retention strategies.

with dataset_max_date as (
    -- finding the last order date in the dataset to act as "today" for recency/churn calculation
    select max(order_purchase_timestamp) as max_date from orders
),
purchase_intervals as (
    -- step 1: use lag() to calculate the day-gap between consecutive orders for the same unique user
    select 
        c.customer_unique_id,
        o.order_purchase_timestamp,
        days_since_prior = datediff(day, lag(o.order_purchase_timestamp) over (
            partition by c.customer_unique_id 
            order by o.order_purchase_timestamp
        ), o.order_purchase_timestamp)
    from 
        orders o
    inner join 
        customers c on o.customer_id = c.customer_id
    where 
        o.order_status not in ('canceled', 'unavailable')
),

cycle_stats as (
    -- step 2: calculate global benchmarks for repurchase behavior
    -- stdev() helps define what is "statistically normal" vs "anomalous"
    select 
        avg_cycle = avg(days_since_prior * 1.0),
        std_dev_cycle = stdev(days_since_prior)
    from 
        purchase_intervals
    where 
        days_since_prior is not null -- only includes repeat purchase events
),

customer_last_purchase as (
    -- step 3: find the recency of the absolute last order for every customer
    select 
        c.customer_unique_id,
        max(o.order_purchase_timestamp) as last_order_date,
        days_since_last_order = datediff(day, max(o.order_purchase_timestamp), d.max_date)
    from 
        orders o
    inner join 
        customers c on o.customer_id = c.customer_id
    cross join 
        dataset_max_date d
    where 
        o.order_status not in ('canceled', 'unavailable')
    group by 
        c.customer_unique_id, d.max_date
)

-- final output: count only the users exceeding the churn threshold
select 
    count(distinct lp.customer_unique_id) as high_risk_churn_customer_count
from 
    customer_last_purchase lp
cross join 
    cycle_stats stats
where 
    lp.days_since_last_order > (stats.avg_cycle + (2 * stats.std_dev_cycle));

Results:

Insights:

• The analysis identified 42,051 customers who have exceeded the average purchase cycle by more than 2 standard deviations without placing a new order. These customers can be flagged as ‘High Risk of Churn’ based on their significant deviation from typical repurchase behavior.

Conclusion

Through the analysis of customer retention, purchase frequency, and churn risk, we have gained valuable insights into the behavior of Olist’s customer base. The segmentation of customers using RFM analysis has allowed us to identify key segments such as ‘Champions’, ‘Loyal Customers’, ‘At Risk’, and ‘Hibernating’, each with distinct characteristics and opportunities for targeted marketing strategies. The cohort analysis revealed trends in customer acquisition and retention rates over time, highlighting the challenges of retaining customers after their initial purchase. The Pareto Principle analysis showed that a small percentage of customers contribute to a significant portion of the revenue, emphasizing the importance of understanding and catering to these high-value customers. Finally, the purchase frequency analysis across product categories provided insights into customer behavior and potential opportunities for optimizing inventory and marketing strategies. The identification of customers at high risk of churn allows Olist to implement targeted retention efforts to re-engage these customers and prevent them from becoming inactive. Overall, these analyses provide a comprehensive understanding of customer behavior and retention patterns, enabling Olist to make informed decisions to enhance customer loyalty and drive revenue growth.