Overview
Beautyplaza is a retail company specializing in beauty products. This case study aims to analyze sales data from 2021, 2022, and 2023 to gain insights into customer retention. The focus will be on retention rate within 6 months, time between purchases, and differences in purchase behavior across the first, second, and third purchases. The analysis will consider time, drop-off rates, brands, and purchase amounts.
Click here to access the dataset
Dataset Description
The dataset used in this analysis is provided on the Datamantra website and can be accessed through the link attached. The key columns in the dataset include:
- Customer_ID: Unique identifier for each customer.
- Order_Id: Unique identifier for each order.
- Order_Date: Date of each purchase.
- List of Brands: Brands included in each purchase, in a specific order.
- First_Order: Indicates whether this purchase is the first order (Y/N).
- Sum_of_Sales: Total amount spent in each transaction.
Objectives
Create a simple yet intuitive dashboard using Tableau or Power BI to visualize the insights gained from the analysis. The dashboard should include the following key visualizations:
- Retention Rate Visualization: Show the retention rates for customers who made their first purchase in 2021, 2022, and 2023.
- Time Between Purchases Visualization: Display the average time intervals between the first and second purchases and between the second and third purchases.
- Purchase Behavior Visualization: Compare the average purchase amounts and the most frequently bought brands for the first, second, and third purchases.
- Drop-off Rates Visualization: Highlight the drop-off rates between the first and second purchases, and between the second and third purchases.
Methodology
1. Data Cleaning and Preparation
- Date Parsing: Ensure that all dates are in a consistent format for accurate time-based calculations.
- Customer Segmentation: Identify unique customers and their purchase histories.
- Handling Missing Data: Address any missing values in critical columns such as Purchase Date and Purchase Amount.
2. Retention Rate Calculation
- Define Cohorts: Group customers based on their initial purchase dates.
- Calculate Retention: Determine the percentage of customers who made a repeat purchase within 6 months of their first purchase.
3. Time Between Purchases
- Time Interval Calculation: Compute the time intervals between consecutive purchases for each customer.
- Average Time Analysis: Analyze the average time between the first and second, and between the second and third purchases.
4. Purchase Behavior Analysis
- First Purchase Analysis: Examine the average purchase amount and the brands most frequently bought during the first purchase.
- Second Purchase Analysis: Similar analysis for the second purchase, noting any changes in behavior.
- Third Purchase Analysis: Analyze the third purchase to identify patterns or deviations from earlier purchases.
- Drop-off Rates: Calculate the drop-off rates between the first and second purchases, and between the second and third purchases.
Tools Required
- SQL
- Python
- Excel
- Tableau or Power BI
Questions for Analysis
1. Retention Rate Analysis
- What is the retention rate within 6 months for customers who made their first purchase in 2021?
- How does the retention rate within 6 months compare across the years 2021, 2022, and 2023?
2. Time Between Purchases
- What is the average time between the first and second purchase for customers who made their first purchase in 2021?
- How does the average time between purchases change from the second to the third purchase?
3. Purchase Behavior Analysis
- What is the average purchase amount for the first, second, and third purchases?
- Which brands are most commonly purchased during the first, second, and third purchases?
- What are the drop-off rates between the first and second purchase, and between the second and third purchase?
- How does the average purchase amount differ between customers who make a second purchase within 6 months and those who do not?
Actionable Insights and Conclusions
Based on the analysis of the above questions, provide actionable insights and conclusions to improve customer retention and purchasing behaviors. Consider strategies such as targeted marketing campaigns, loyalty programs, personalized offers, and improving customer engagement to reduce drop-off rates and increase repeat purchases.
This case study provides a framework for analyzing customer retention and purchasing behaviors using the provided dataset. By focusing on retention rates, time between purchases, and purchase behaviors, Beautyplaza can gain valuable insights to enhance customer loyalty and drive business growth. Detailed analysis and actionable insights can inform strategic decisions and improve customer engagement. The dataset is available for download on the DataMantra website, providing an excellent resource for further exploration and analysis.
