The Challenge
A mid-sized retail chain wanted to understand their customer base better — who buys what, when, how often, and what drives repeat purchases. They had 12 months of transaction data sitting in a SQL database but no analytics resources internally.
They needed a professional report they could present to their management team and use to guide their marketing strategy for the upcoming year.
My Approach
Data Extraction & Cleaning
Extracted 45,000 transaction records via SQL. Cleaned duplicates, standardized customer IDs, and handled 3.2% missing product category data.
Descriptive Statistics
Calculated purchase frequency, average basket size, customer lifetime value, and retention rates using Python (pandas) and Jamovi.
Segmentation Analysis
Identified 4 distinct customer segments using RFM analysis (Recency, Frequency, Monetary value). Each segment received a profile and behavioral description.
Report Writing & Recommendations
Wrote a 20-page report with 14 charts, clear findings for each segment, and 7 specific recommendations with expected impact estimates.
Key Findings
- Top 20% of customers generated 68% of total revenue (strong Pareto effect)
- Average repurchase cycle was 34 days — shorter than management assumed
- Saturday afternoon was the highest conversion period by 2.3×
- Segment 3 ("Occasional Buyers") had the highest growth potential — identified 3 upsell levers
- Product category C had a 40% cart abandonment correlation with price sensitivity