🧩 Project: How Important Are Multiple Purchases in the First Month of a Customer’s Life?

🎯 Problem Statement

This project explored whether customers who make a second purchase within 30 days of their first order perform better in terms of overall sales than those who do not. For example, if a customer made their first purchase on February 15 and their second on March 1, they were classified as part of the multiple-purchase-in-first-month group. Customers who did not make a second purchase within 30 days formed the control group.

💡 Key Business Question

Should the company invest more marketing resources to encourage customers to make their second purchase within the first month?

WITH help_a AS (
    SELECT customer_key, MIN(order_date) AS first_order_date
    FROM sales
    GROUP BY customer_key
),
help_b AS (
    SELECT ha.customer_key,
           DATEDIFF(s.order_date, ha.first_order_date) AS date_diff,
           CASE WHEN DATEDIFF(s.order_date, ha.first_order_date) BETWEEN 0 AND 30 THEN 1 ELSE 0 END AS super_number
    FROM help_a ha
    LEFT JOIN sales s ON ha.customer_key = s.customer_key
),
help_c AS (
    SELECT customer_key,
           CASE WHEN SUM(super_number) > 1 THEN 'multiple_purch_first_month'
                ELSE 'control_group' END AS customer_group
    FROM help_b
    GROUP BY customer_key
)
SELECT AVG(a.sales_amount) AS avg_sales,
       YEAR(a.order_date) AS year,
       MONTH(a.order_date) AS month,
       c.customer_group
FROM help_c c
INNER JOIN sales a ON c.customer_key = a.customer_key
GROUP BY YEAR(a.order_date), MONTH(a.order_date), c.customer_group
ORDER BY customer_group, YEAR(order_date), MONTH(order_date) DESC;


📊 Results

• The multiple_purch_first_month group did not show significantly higher average sales than the control group.

• Therefore, investing additional marketing efforts to push for a second purchase within the first month may not be cost-effective.