I conducted customer purchase behaviour analysis for 2Market, a global retailer, delivering actionable insights across demographics, advertising channels, and product preferences.
The analysis supported key business objectives:
- increasing profitability
- improving market position in different markets
- enhancing customer experience by targeted marketing strategies
The analysis of 2Market's customer data revealed significant opportunities to increase revenue and market share through targeted strategies. Spain currently dominates revenue (49%), while child-free customers and social media channels demonstrate exceptional value.
Key Recommendations:
- Expand beyond Spain to diversify market presence
- Target high-value child-free segments through Instagram
- Prioritize social media over traditional marketing (4x more effective)
- Enhance mid-life customer basket size and in-store experience
- Diversify beyond liquor and meat product categories
| ⏱️ Duration | 🏆 Grade | 🛠️ Tools |
|---|---|---|
| 6 Weeks | 96% (High Distinction) |
|
| Process | Highlights |
|---|---|
| Data Import | Imported raw marketing data into Excel as structured table |
| Data Cleaning | • Converted data types for proper analysis • Checked data integrity (duplicates, missing values) • Identified and addressed outliers using min/max/average • Standardized categorical data (marital status) with VLOOKUP • Normalized date fields using TEXTSPLIT, IF, and DATE functions |
| Initial Analysis | • Created demographic distributions with box plots and line graphs • Analyzed relationships between customer features (age, income, marital status, family composition) |
| Process | Highlights |
|---|---|
| Data Loading | Loaded cleaned Excel data into SQL data-table |
| Query Development | Wrote complex SQL queries using Joins, Subqueries and Case Statements for detailed analysis. |
| Analysis Focus | • Identified best-selling products by country • Determined most effective advertising methods by country • Calculated marketing revenue attribution • Compared social media effectiveness across channels |
| Component | Highlights |
|---|---|
| Dashboard Suite | Developed three interconnected dashboards: • Customer Demographics • Marketing Effectiveness • Product Sales & Purchase Patterns |
| Technical Elements | • Created 41 calculated fields for dynamic analysis • Implemented data source filters for outliers |
| Visualization Techniques | • Utilized Dynamic zone visibility for detailed exploration of different marketing channels • Treemap for country-wise Revenue and Customer analysis • Interactive KPIs based on filtered segments • Cross-referenced visualizations |
Customer Demographics
- Identified Spain, South Africa, and Canada as top markets by revenue and customer count
- Discovered Midlife segment (45-54 years) constitutes the largest customer group across countries
Important
Value Discovery: Child-free customers demonstrate significantly higher value, generating:
- $695,384 in revenue from just 630 customers
- 2-5× higher spending across all product categories
- Higher average income across all age groups
Dashboard showing revenue distribution across countries and spending patterns by demographic segments
Marketing Effectiveness
- Quantified marketing attribution at 8% of overall revenue from Q3 2012 to Q2 2014
Important
Value Discovery: Determined social media channels (Facebook, Twitter, Instagram) were 4x more effective than traditional channels
- Highlighted Instagram as the highest-value channel (highest basket value at $126, lowest deal usage at 1.1)
- Measured recent campaign success rates: Instagram (56%), Facebook (55%), Twitter (38%)
Dashboard comparing Marketing Channels and their effectiveness across customer demographies and product patterns
Product & Purchase Analysis
- Identified Liquor and Meat products as dominant sales categories (75% of overall revenue)
- Discovered child-free customers outspend those with children by 2-5x across all categories
- Recognized global preference for walk-in shopping despite improvements in online ratios
Important
Tracked significant improvements in online purchase-to-web-visits ratio in Q1 2014
Dashboard showing product category performance and purchasing behavior across demographic segments
Based on the comprehensive data analysis, the following strategic recommendations were presented to enhance 2Market's business performance:
Business Recommendations
- Mitigate risk by expanding customer base outside Spain.
- Increase basket size for mid-life customers.
- Focus on high-revenue customer segments, particularly child-free customers.
- Prioritize Social media marketing, Instagram and Twitter
- Instagram for child-free high value customers
- Twitter for mid-life customers with children.
- Diversify product offerings beyond liquor and meat.
- Enhance in-store shopping experience for customers with children.
Technical Recommendations
- Conduct further analysis at regional levels to tailor marketing and product strategies more effectively.
- Gather more granular product, invoicing and marketing data to refine sales and marketing strategies.