Build an RFM Customer Segmentation Model

You are a customer analytics expert specializing in segmentation and lifecycle marketing. Help me build a complete RFM (Recency, Frequency, Monetary) segmentation model for my business. **Business Context:** - Industry: [INDUSTRY, e.g., e-commerce, SaaS, retail] - Analysis period: [TIME_PERIOD, e.g., last 12 months] - Number of customers: [CUSTOMER_COUNT, e.g., 50,000] - Average transaction frequency: [AVG_FREQUENCY, e.g., 2.3 purchases per year] - Available data fields: [DATA_FIELDS, e.g., customer_id, purchase_date, order_total, product_category] - Business goal: [GOAL, e.g., reduce churn, increase repeat purchases, optimize marketing spend] **Please provide the following deliverables:** 1. **Data Preparation Steps:** Outline the SQL or Python code to calculate each RFM component: - Recency: Days since last purchase as of [ANALYSIS_DATE] - Frequency: Total number of transactions in the analysis period - Monetary: Total or average spend in the analysis period Include handling for edge cases (single-purchase customers, refunds, outliers). 2. **Scoring Methodology:** Define the scoring approach (quintile-based 1-5 scoring vs. custom thresholds). Provide the logic for assigning scores and explain trade-offs. Include the code to compute RFM scores. 3. **Segment Definitions:** Create a segment mapping table with at least 8 distinct customer segments, including: - Segment name (e.g., "Champions," "At Risk," "Hibernating") - RFM score ranges for each segment - Estimated percentage of customer base - Behavioral description of each segment 4. **Segment Profiles:** For each segment, describe: - Key characteristics and typical customer behavior - Customer lifetime value (CLV) implications - Churn risk level (low/medium/high) 5. **Marketing Action Plan:** For each segment, recommend 2-3 specific marketing strategies, including channel recommendations (email, SMS, retargeting), messaging tone, offer types, and campaign frequency. 6. **Implementation Code:** Provide complete Python (pandas) or SQL code to: - Calculate RFM scores from raw transaction data - Assign segments based on the scoring rules - Generate a summary statistics table per segment 7. **Monitoring Dashboard Specs:** Recommend 5-7 KPIs to track segment migration over time and suggest a quarterly review cadence. Format the output with clear sections, include code blocks, and provide a visual segment grid (text-based) mapping R and F scores.