PROJECT SUMMARY
Summary
OCA Indonesia, a CPaaS company, provides multi-channel customer engagement channels including SMS, Email, WhatsApp, and Call (IVR). As transaction volume grows, the company requires better analytical visibility into customer behavior patterns, channel preferences, and engagement consistency across its client base to drive structured growth and improve account prioritization.
Goals
The project aims to develop a unified customer segmentation framework to clarify service usage patterns, identify high-value and at-risk clients, and uncover operational or commercial opportunities for retention, cross-selling, and revenue growth. Additionally, it supports infrastructure capacity planning by evaluating platform traffic concentrations.
Process
The process involved problem understanding, multi-channel transaction data preparation, and data cleaning for analytics. Business metrics were engineered, including Recency, Usage Frequency, Monetary, and Tenure (RFMT), followed by evaluating and implementing an RFMT segmentation model using Python and SQL rather than K-Means clustering to maximize business interpretability.
Output
Analysis revealed that all 20 active customers are recently active, yet revenue contribution is heavily concentrated among a small group of Champion customers who drive the majority of transactions. It is recommended to protect Champions to secure up to 70% of platform revenue and convert Potential Loyalists, as a 20-30% usage increase yields substantial incremental revenue.
SCOPE OF WORK / ACHIEVEMENTS
- Engineered an RFMT segmentation framework using SQL and Python to categorize 20 active multi-channel customer accounts based on behavioral characteristics.
- Analyzed transactional datasets spanning January 1 to March 31, 2025, to identify revenue concentration risks and platform utilization levels.
- Formulated strategic playbooks targeting Potential Loyalists to drive a 20-30% increase in platform usage for incremental revenue growth.
- Designed customer retention strategies protecting high-value Champion accounts to secure up to 70% of the platform’s total revenue contribution.
TOOLS & METHODS
Tools
- Python
- Google Colab
- SQL
- Google BigQuery
- PowerBI Dashboard
Methods
- Data Preparation & Cleaning
- Metric Engineering
- Behavioral Deep Dive
- RFMT Segmentation (Recency, Frequency, Monetary, Tenure)
- Evaluation Criteria Analysis
