Strategic life-cycle analysis for 1,200 global customers using Excel
Global Customer Analytics & RFM Segmentation
Sofia Angelina
Bootcamp Data Analyst with Excel
Executive Project Summary
Business Problem
Analytical Situation
Data Used
Broad marketing campaigns were leading to inefficient ad spend and missed retention opportunities. The business lacked a structured way to identify at-risk high-value customers.
Developed a 4-pillar RFM (Recency, Frequency, Monetary) segmentation model to prioritize marketing resources and recover potential churn risk revenue.
Global E-Commerce Customer Analytics 2024 by Anas Riaz.
Source: https://www.kaggle.com/datasets/anasriaz/global-e-commerce-customer-analytics-2024
Dataset Dimensions & Scope
1,200
25
4
3yr
TOTAL CUSTOMERS
DATA ATTRIBUTES
GLOBAL REGIONS
HISTORICAL SPAN
Recency, Frequency, Monetary Value (RFM)
days_since_last_purchase
total_orders
total_spent_usd
RECENCY
How recent a customer made a purchase, or how active customers are more likely to buy again.
MONETARY
How much money a customer spent on their purchases. High-spending customers yield higher lifetime value.
FREQUENCY
How often a customer make a purchase. Repeat buyers are more engaged and loyal.
The 4 Strategic Customer Pillars
Quartile Scoring with PERCENTILE.INC to dynamically score customers from 1-4.
Core Loyalists
High R-F-M scores. The highest value segment driving brand advocacy.
Mid-Tier & Developing
Recent buyers with low frequency. Nurture for loyalty conversion.
High-Value At-Risk
Historically valuable but low recency. Priority win-back group.
Low-Value/Dormant
Low value, long-term inactivity. Minimal budget allocation group.
Customer Base Composition
Observation:
Highly attractive healthy-growth distribution. 62% total customer case consists of active buyers (Developing & Core Loyalists), providing strong foundation for immediate upselling and customer advocacy programs.
Revenue Contribution Analysis
Observation:
Core Loyalists, despite only being in the 25% of total customer generates the highest revenue.
Performance by Referral Source
Observation:
Social Media, Email Campaigns, and Organic Search serve as our primary acquisition pillars, collectively securing over 373 million USD in customer lifetime spend.
Return vs Satisfaction
Observation:
Automotive represents a critical pain point, showing a major dip in customer satisfaction down to a 3.1 out of 5.0 average. This strongly indicates fulfillment errors, inaccurate online descriptions, or product quality mismatches.
Interactive Dashboard
Interactive dashboard allows stakeholder to filter segments by region, loyalty tier, and income bracket using dynamic slicers.