Customer Segmentation Insights:
Prompt for AI

Directions:

Copy and paste the prompt below into your company's authorized Gen AI tool, ensuring that you input the required CSV files and adjust parameters as needed for your specific business context. This will help generate accurate customer segmentation insights, optimize lead prioritization, and improve marketing effectiveness. 

Prompt
# RFM (Recency, Frequency, Monetary) Analysis and Customer Segmentation Prompt

## Input Data Requirements
Please ensure you have the following CSV files with their required fields:

1. transactions.csv
   - transaction_id
   - customer_id
   - transaction_date
   - total_amount
   - payment_status

2. customers.csv
   - customer_id
   - company_name
   - industry
   - business_type
   - account_created
   - account_status

3. transaction_items.csv
   - transaction_id
   - product_id
   - product_category
   - quantity
   - unit_price
   - line_total

## Analysis Instructions

Using the provided B2B distributor data files (customers.csv, transactions.csv, and transaction_items.csv), please:

1. Calculate the following RFM metrics for each customer:
   - Recency: Number of days since their last purchase
   - Frequency: Total number of orders in the past 365 days
   - Monetary: Total spending amount in the past 365 days

2. Create customer segments based on the following RFM score criteria:
   - Champions (R >= 4, F >= 4, M >= 4)
   - Loyal Customers (R >= 3, F >= 3, M >= 3)
   - Potential Loyalists (R >= 3, F >= 2)
   - At Risk (R <= 2, F >= 3)
   - Lost Customers (all others)

3. Generate a dashboard visualization that includes:
   - Customer segment distribution bar chart
   - Key metrics for the Champions segment
   - Average values for each RFM component
   - Segment-specific insights

4. Identify the Ideal Customer Profile by analyzing the Champions segment:
   - Average order frequency
   - Average order value
   - Total annual spend
   - Most common product categories
   - Industry distribution
   - Business type distribution

## Desired Output

1. A visual dashboard showing:
   - Segment distribution
   - Key performance metrics
   - RFM score distributions
   - Time-based trends

2. Ideal Customer Profile summary including:
   - Behavioral metrics (order frequency, value, timing)
   - Business characteristics (industry, size, type)
   - Product preferences
   - Payment patterns

3. Strategic recommendations for:
   - Customer retention strategies per segment
   - Growth opportunities
   - Risk mitigation for At Risk customers
   - Reactivation approaches for Lost Customers

## Additional Parameters
- Analysis timeframe: Past 365 days
- Minimum transaction count: 1
- Currency: USD
- Score calculation method: Quintile-based (1-5 scale)
- Exclude inactive accounts
- Consider only paid transactions

## Example Usage

```python
# Sample code structure for analysis
import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime, timedelta

# Load data
customers_df = pd.read_csv('customers.csv')
transactions_df = pd.read_csv('transactions.csv')
items_df = pd.read_csv('transaction_items.csv')

# Calculate RFM scores
def calculate_rfm_scores(transactions_df, customers_df):
    current_date = datetime.now()
    rfm_data = transactions_df.groupby('customer_id').agg({
        'transaction_date': lambda x: (current_date - pd.to_datetime(x.max())).days,
        'transaction_id': 'count',
        'total_amount': 'sum'
    }).reset_index()
    
    # Rename columns
    rfm_data.columns = ['customer_id', 'recency', 'frequency', 'monetary']
    
    # Calculate quintiles and scores
    rfm_data['r_score'] = pd.qcut(rfm_data['recency'], q=5, labels=[5,4,3,2,1])
    rfm_data['f_score'] = pd.qcut(rfm_data['frequency'], q=5, labels=[1,2,3,4,5])
    rfm_data['m_score'] = pd.qcut(rfm_data['monetary'], q=5, labels=[1,2,3,4,5])
    
    return rfm_data

# Generate visualization
def create_rfm_dashboard(rfm_data, segment_data):
    # Dashboard code here
    pass
```

## Notes
- Ensure all dates are in YYYY-MM-DD format
- Handle missing values appropriately
- Validate customer IDs across all files
- Consider seasonal patterns in recency calculations
- Adjust segment thresholds based on industry norms
- Account for business type in monetary calculations