SQL Case Study: Analyzing Customer Churn for an E-Commerce Business

Introduction

If you're a beginner in SQL and data analysis, chances are you've run into datasets but struggled with what to analyze and how to approach the problem. So, let's dive into a real-world case study and break it down step by step.

We're going to analyze customer churn for an e-commerce business. The goal? Figure out why customers are leaving and provide insights to improve retention.

Customer Churn: SQL Case Study

Understanding the Problem Statement

Business Scenario

An e-commerce company noticed a decline in returning customers and suspects that many are churning (i.e., leaving the platform after their initial purchases). As a data analyst, your job is to identify the key factors behind customer churn using SQL and help the business make data-driven decisions.

Dataset Overview

We have the following tables in our PostgreSQL database:

customers (Customer details)

customer_id	name	signup_date	country
101	Alice	2023-01-15	USA
102	Bob	2023-02-20	Canada
103	Charlie	2023-03-10	India

orders (Customer order details)

order_id	customer_id	order_date	total_amount	status
201	101	2023-02-01	150.00	Completed
202	102	2023-03-05	200.00	Completed
203	103	2023-03-20	80.00	Cancelled

Step 1: Defining Churn

Before writing SQL queries, we must first define churn in a way that makes sense for the business. Here’s a common definition:

A customer is considered churned if they have not placed an order in the last 6 months.

We will write an SQL query to identify customers who haven’t made a purchase in the last 6 months from today’s date.

SQL Query to Identify Churned Customers

SELECT c.customer_id, c.name, c.signup_date,
       MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.signup_date
HAVING MAX(o.order_date) < NOW() - INTERVAL '6 months' OR MAX(o.order_date) IS NULL;

Explanation

• We join customers with orders on customer_id.

• Use MAX(order_date) to find the last order date of each customer.

• Use HAVING to filter customers whose last purchase was more than 6 months ago (or who never made a purchase).

• LEFT JOIN ensures we don’t lose customers who signed up but never ordered.

Step 2: Identifying Patterns in Churned Customers

Now that we have the list of churned customers, let’s dig deeper to find patterns:

1. Do churned customers have smaller average order values?

SELECT
    CASE
        WHEN o.customer_id IS NULL THEN 'No Orders'
        ELSE 'Churned Customers'
    END AS customer_type,
    ROUND(AVG(o.total_amount),2) AS avg_order_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY customer_type;

Insights:

• If churned customers have lower average order values, we might need better discounts or incentives.

2. Are there specific months where churn is higher?

SELECT
    DATE_TRUNC('month', signup_date) AS signup_month,
    COUNT(DISTINCT customer_id) AS churned_customers
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE (MAX(o.order_date) < NOW() - INTERVAL '6 months' OR MAX(o.order_date) IS NULL)
GROUP BY signup_month
ORDER BY signup_month;

Insights:

• If more customers churn in specific months, there might be seasonal trends.

3. Does churn vary by country?

SELECT c.country, COUNT(DISTINCT c.customer_id) AS churned_customers
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE (MAX(o.order_date) < NOW() - INTERVAL '6 months' OR MAX(o.order_date) IS NULL)
GROUP BY c.country
ORDER BY churned_customers DESC;

Insights:

• If churn is higher in a particular country, we may need better localized marketing.

Step 3: Recommendations for the Business

After analyzing the data, here are some actionable recommendations:

1. Introduce Loyalty Programs: If we find that churned customers generally spend less, a loyalty program might incentivize repeat purchases.

2. Retargeting Campaigns: If a specific country has high churn, we should launch a targeted campaign.

3. Seasonal Promotions: If churn is seasonal, we can prepare discounts or email reminders before high-churn months.

4. Personalized Offers: If customers with a specific order history are churning, personalized discount emails might help retain them.

Conclusion

SQL is an incredibly powerful tool for understanding customer behavior. By analyzing churn, we identified insights that could reduce customer loss and increase retention. The key takeaway? Data tells a story—you just need the right SQL queries to uncover it! 

If you found this useful, let me know! What other SQL case studies would you like me to break down?