Session 09: Digital Customer Acquisition

1. The Digital Insurance Customer

The digital insurance customer is not a demographic — it is a behaviour. Age, income, and location matter, but the most important characteristic is the preference for digital self-service. A wealthy 55-year-old who uses a smartphone to manage investments and order groceries is more likely to buy insurance online than a 25-year-old who still calls an agent for every financial decision. Understanding who the digital customer is — and is not — is the foundation of any InsurTech customer acquisition strategy.

1.1 Who Buys Insurance Online?

1.2 The Mobile-First Reality

In India, over 70% of insurance-related online activity — searches, quote requests, policy comparisons — starts on a mobile device. However, only about 30% of purchases are completed on mobile, with the rest shifting to desktop or agent assistance for the final transaction. This "mobile-first research, desktop/agent-first purchase" gap represents the single biggest UX opportunity in digital insurance. The purchase flow — especially the form-filling, payment, and document verification steps — must be redesigned for mobile, not simply shrunk from desktop. InsurTechs that solve the "mobile purchase gap" will have a structural conversion advantage.

2. The Digital Acquisition Funnel

The digital insurance acquisition funnel describes the stages a customer passes through from first awareness to policy purchase — and beyond, to renewal. Understanding conversion rates at each stage — and where drop-off is highest — is the most important analysis a digital insurance business can perform.

2.1 The Six-Stage Funnel

┌─────────────────────────────────────────────────────────────────────┐
│              THE DIGITAL INSURANCE ACQUISITION FUNNEL               │
│                                                                     │
│   1. AWARENESS  ──→  2. CONSIDERATION  ──→  3. QUOTE              │
│   (Sees an ad    │   (Visits website/   │   (Fills form,           │
│    or search      │    reads content,     │    gets a price)         │
│    result)        │    explores options)  │                          │
│         │         │         │             │          │              │
│         ▼         │         ▼             │          ▼              │
│  Conv: 20-40%     │  Conv: 15-25%        │  Conv: 30-50%           │
│                                                                     │
│         ┌─────────┤                   ┌──┤                          │
│         ▼         │                   ▼  │                          │
│   4. PURCHASE     │          5. ONBOARDING  │   6. RENEWAL          │
│   (Makes payment, │          (Activates   │   (Pays next year      │
│    policy issued) │           policy, DL,  │    premium, stays      │
│         │         │           welcome kit) │    as customer)        │
│         ▼         │         ▼              │          ▼              │
│  Conv: 30-50%     │  Conv: 70-90%        │  Conv: 60-85%           │
│  (of quotes →     │  (of purchases →      │  (of policies due      │
│   purchases)      │   successful activate) │   for renewal)          │
└─────────────────────────────────────────────────────────────────────┘

Note: "Conv" = Conversion rate from the PREVIOUS stage.
E.g., 20-40% of people who see awareness content click through to consideration.
The overall conversion from awareness to purchase = 0.30 × 0.20 × 0.40 = 2.4%

2.2 Where the Funnel Leaks

Industry data from Indian digital insurance channels shows a consistent pattern:

• Consideration → Quote is the biggest leak (70–85% drop-off). A customer visits a website or app, starts a quote process, and abandons it. The reasons: the form takes too long (more than 3 minutes = 40%+ abandonment), asks for too much information upfront, requires documents the customer does not have readily available, or the customer was not ready to commit and just wanted a "ballpark" price.
• Quote → Purchase converts reasonably (30–50%). If a customer completes the quote process, they have revealed purchase intent. The drop-off at this stage is mostly price sensitivity — they went to the competition for a better quote — or comparison paralysis (checking too many insurers).
• First-year renewal is the silent killer. A digital carrier might spend ₹2,000 to acquire a first-year customer who pays ₹10,000 premium. If the customer does not renew, the carrier has likely lost money on that customer. Renewal rate is the single most important metric that separates sustainable InsurTechs from burning-cash ones.

3. Customer Acquisition Cost (CAC)

Customer Acquisition Cost is the total marketing and sales cost required to acquire one new paying customer. It is the single most important input to the unit economics calculation — and the metric where digital insurance differs most dramatically from traditional insurance.

3.1 Calculating CAC

# CAC by Channel — Framework
# Total marketing spend for a channel in a period
# Divided by the number of NEW paying customers acquired through that channel

# Example: Paid Search (Google Ads) for Motor Insurance
spend = {
    'ad_spend': 1500000,      # ₹15 Lakhs on Google Ads in the month
    'agency_fees': 150000,     # Agency management fees
    'landing_page_cost': 50000, # Content/landing page creation (amortized)
    'tracking_tools': 30000,   # Analytics and attribution tools
}
total_cost = sum(spend.values())  # ₹17,30,000

new_customers = 450  # Policies issued through this channel

cac_paid_search = total_cost / new_customers  # ₹3,844 per customer

3.2 CAC by Channel — Typical Indian InsurTech Benchmarks

4. Customer Lifetime Value (LTV)

Customer Lifetime Value is the total profit a customer generates over their entire relationship with the insurer. In insurance, LTV is particularly sensitive to three factors: the retention rate (what proportion of customers renew), the cross-sell rate (how many additional products a customer buys), and the claims experience (a customer who files a large claim has a very different LTV from one who never claims).

4.1 The Insurance LTV Formula

LTV = (Average Annual Premium × Profit Margin × Average Customer Tenure) + Cross-Sell Value

Where:
  Average Premium        = ₹8,000/year (motor insurance example)
  Profit Margin          = 25% (1 − Loss Ratio − Variable Expenses)
  Average Customer Tenure = 1 / (1 − Retention Rate)
                           = 1 / (1 − 0.75) = 4 years (if 75% annual retention)

Base LTV = ₹8,000 × 25% × 4 = ₹8,000

  Cross-Sell Value        = ₹3,000 (additional products purchased over tenure)

  Total LTV               = ₹11,000

  LTV/CAC (at ₹2,500 CAC) = 11,000 / 2,500 = 4.4x  (Healthy!)

Notice that LTV is incredibly sensitive to the retention rate:

An improvement from 70% to 80% retention doubles the LTV. This is why insurance companies obsess over retention — a 1-percentage-point improvement in retention can increase enterprise value by 5–10%. Every rupee spent on retention (service improvements, loyalty programmes, renewal incentives) must be evaluated against this LTV leverage.

4.2 Calculating LTV in Python

def calculate_ltv(annual_premium, loss_ratio, expense_ratio, retention_rate,
                  discount_rate=0.10, years=10):
    """
    Calculate Customer Lifetime Value for an insurance policy.

    Parameters:
    -----------
    annual_premium : float — Premium paid per year
    loss_ratio : float — Proportion of premium paid as claims (0.0–1.0)
    expense_ratio : float — Proportion of premium spent on expenses (0.0–1.0)
    retention_rate : float — Probability customer renews each year (0.0–1.0)
    discount_rate : float — Cost of capital for NPV calculation (default 10%)
    years : int — Maximum projection horizon (default 10)

    Returns:
    --------
    dict — LTV metrics
    """
    profit_margin = 1 - loss_ratio - expense_ratio
    annual_profit = annual_premium * profit_margin

    ltv = 0
    retention_prob = 1.0

    for year in range(1, years + 1):
        if year > 1:
            retention_prob *= retention_rate
        discounted_profit = (annual_profit * retention_prob) / ((1 + discount_rate) ** (year - 1))
        ltv += discounted_profit

    avg_tenure = 1 / (1 - retention_rate) if retention_rate < 1 else years

    return {
        'annual_profit': annual_profit,
        'profit_margin': profit_margin,
        'avg_tenure_years': round(avg_tenure, 1),
        'ltv': round(ltv, 0),
        'cac_breakeven_at': round(annual_profit / (1 - retention_rate / (1 + discount_rate)), 0) if retention_rate > 0 else float('inf')
    }

# Example: Motor Insurance
motor_ltv = calculate_ltv(
    annual_premium=8000,
    loss_ratio=0.68,
    expense_ratio=0.12,
    retention_rate=0.75
)
print(f"Motor Insurance LTV Analysis:")
for k, v in motor_ltv.items():
    if k == 'profit_margin':
        print(f"  {k}: {v*100:.1f}%")
    elif k == 'ltv':
        print(f"  {k}: ₹{v:,.0f}")
    else:
        print(f"  {k}: {v}")

# Example: Term Life Insurance
life_ltv = calculate_ltv(
    annual_premium=12000,
    loss_ratio=0.45,
    expense_ratio=0.25,
    retention_rate=0.85
)
print(f"\nTerm Life LTV Analysis:")
for k, v in life_ltv.items():
    if k == 'profit_margin':
        print(f"  {k}: {v*100:.1f}%")
    elif k == 'ltv':
        print(f"  {k}: ₹{v:,.0f}")
    else:
        print(f"  {k}: {v}")

5. Funnel Analytics in Python

Building a funnel analytics pipeline allows an InsurTech to track conversion rates, identify bottlenecks, and measure the impact of optimisation experiments. The analysis requires data from the company's analytics platform (Google Analytics, Mixpanel, or a custom event tracking system), structured as a sequence of events per customer session.

5.1 Loading and Preparing Funnel Data

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# Load session-level funnel data
# Each row represents a user session with columns for each stage
funnel_data = pd.read_csv('data/funnel_data.csv')

# Columns: session_id, stage_awareness (1/0), stage_consideration,
#          stage_quote, stage_purchase, stage_onboarding, channel, product

print(f"Total sessions tracked: {len(funnel_data):,}")
print(f"\nColumns: {funnel_data.columns.tolist()}")
print(f"\nChannels: {funnel_data['channel'].value_counts().to_dict()}")

# Calculate stage-wise conversion
funnel_stages = ['stage_awareness', 'stage_consideration',
                 'stage_quote', 'stage_purchase', 'stage_onboarding']

stage_counts = {}
for stage in funnel_stages:
    stage_counts[stage] = funnel_data[stage].sum()

print("\nStage-wise user counts:")
for stage, count in stage_counts.items():
    print(f"  {stage:25s}: {count:6,.0f}")

5.2 Calculating Conversion Rates

▶ Prerequisite: run the §5.1 block first in the same notebook — stage_counts is defined there. This block will not run standalone.

# Overall conversion (top-of-funnel to end)
overall_conv = stage_counts['stage_onboarding'] / stage_counts['stage_awareness']
print(f"Overall conversion (Awareness → Onboarding): {overall_conv*100:.1f}%")

# Stage-by-stage conversion
stages_list = list(funnel_stages)
conversion_rates = {}
for i in range(len(stages_list) - 1):
    from_stage = stages_list[i]
    to_stage = stages_list[i + 1]
    rate = stage_counts[to_stage] / stage_counts[from_stage] if stage_counts[from_stage] > 0 else 0
    conversion_rates[f"{from_stage} → {to_stage}"] = rate
    print(f"  {from_stage:25s} → {to_stage:20s}: {rate*100:.1f}%")

# Conversion by channel
print("\n\nPurchase conversion rate by channel:")
channel_conv = funnel_data.groupby('channel').agg(
    total_awareness=('stage_awareness', 'sum'),
    total_purchase=('stage_purchase', 'sum')
).reset_index()
channel_conv['conversion'] = channel_conv['total_purchase'] / channel_conv['total_awareness']
print(channel_conv.sort_values('conversion', ascending=False).to_string(index=False))

5.3 Funnel Visualization

▶ Prerequisite: continues from §5.1 (§5.2 is optional here) — stage_counts and the funnel_stages list must be defined. This block will not run standalone.

# Funnel bar chart — classic insurance funnel visualization
stage_names = ['Awareness', 'Consideration', 'Quote', 'Purchase', 'Onboarding']
counts = [stage_counts[s] for s in funnel_stages]

fig, ax = plt.subplots(figsize=(10, 6))
colors = ['#74b9ff', '#a29bfe', '#6c5ce7', '#00b894', '#00d2d3']

bars = ax.barh(stage_names, counts, color=colors, edgecolor='white', height=0.6)

# Add data labels
for bar, count in zip(bars, counts):
    ax.annotate(f'{count:,} ({count/counts[0]*100:.1f}%)',
                xy=(count, bar.get_y() + bar.get_height()/2),
                ha='left', va='center', fontsize=11, fontweight='bold',
                xytext=(5, 0), textcoords='offset points')

# Add conversion arrows between bars
for i in range(len(counts) - 1):
    conv_pct = counts[i+1] / counts[i] * 100
    ax.annotate(f'  ↓ {conv_pct:.0f}%',
                xy=(counts[i]/2, i - 0.3), ha='center', fontsize=9,
                color='#e17055', fontweight='bold')

ax.invert_yaxis()  # Awareness at top
ax.set_title('Digital Insurance Acquisition Funnel', fontsize=14, fontweight='bold')
ax.set_xlabel('Number of Users')
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)
plt.tight_layout()
plt.show()

# Identify biggest drop-off point
drop_offs = []
for i in range(len(counts) - 1):
    drop = (counts[i] - counts[i+1]) / counts[i] * 100
    drop_offs.append({'from': stage_names[i], 'to': stage_names[i+1], 'drop_pct': drop})

biggest_drop = max(drop_offs, key=lambda x: x['drop_pct'])
print(f"\nBiggest drop-off: {biggest_drop['from']} → {biggest_drop['to']}")
print(f"  {biggest_drop['drop_pct']:.1f}% of users drop off at this stage")
print(f"  Focus optimization efforts here.")

6. CAC and LTV Calculation in Python

Combining CAC and LTV analysis reveals the economic health of each acquisition channel. The goal is to identify which channels have the best LTV/CAC ratio and allocate more budget there — while either fixing or shutting down channels with poor economics.

6.1 Channel-Level CAC and LTV Analysis

# Channel cost and acquisition data
channel_data = pd.DataFrame({
    'channel': ['organic_search', 'paid_search', 'social_media',
                'aggregator', 'referral', 'embedded_partner'],
    'monthly_spend': [200000, 1500000, 1200000, 800000, 50000, 300000],
    'new_customers': [400, 450, 350, 600, 200, 800],
    'avg_premium': [8500, 7800, 7200, 7100, 8800, 6800],
    'loss_ratio': [0.62, 0.68, 0.72, 0.70, 0.58, 0.74],
    'retention_rate': [0.80, 0.72, 0.68, 0.65, 0.85, 0.60],
    'expense_ratio': [0.10, 0.12, 0.14, 0.08, 0.10, 0.15]  # Channel-specific
})

# Calculate CAC
channel_data['cac'] = channel_data['monthly_spend'] / channel_data['new_customers']

# Calculate profit margin and annual profit per customer
channel_data['profit_margin'] = (1 - channel_data['loss_ratio']
                                  - channel_data['expense_ratio'])
channel_data['annual_profit'] = (channel_data['avg_premium']
                                  * channel_data['profit_margin'])

# Calculate LTV (simplified: annual profit × avg tenure)
channel_data['avg_tenure'] = 1 / (1 - channel_data['retention_rate'])
channel_data['ltv'] = channel_data['annual_profit'] * channel_data['avg_tenure']

# Calculate LTV/CAC ratio
channel_data['ltv_cac_ratio'] = channel_data['ltv'] / channel_data['cac']

# Sort by LTV/CAC descending
channel_data = channel_data.sort_values('ltv_cac_ratio', ascending=False)

print("=" * 100)
print(f"{'Channel':20s} {'CAC (₹)':>10s} {'LTV (₹)':>10s} {'LTV/CAC':>8s} {'Volume':>8s} {'Rating'}")
print("=" * 100)

for _, row in channel_data.iterrows():
    rating = 'EXCELLENT' if row['ltv_cac_ratio'] > 5 \
        else 'GOOD' if row['ltv_cac_ratio'] > 3 \
        else 'MARGINAL' if row['ltv_cac_ratio'] > 1.5 \
        else 'POOR (FIX OR KILL)'
    print(f"{row['channel']:20s} ₹{row['cac']:>7,.0f}  ₹{row['ltv']:>8,.0f}  {row['ltv_cac_ratio']:>5.1f}x  {row['new_customers']:>5d}  {rating}")

print("\n" + "=" * 100)
print("RECOMMENDED BUDGET ALLOCATION (based on LTV/CAC):")
print("=" * 100)

total_ltv = channel_data['ltv'].sum()
channel_data['budget_share'] = channel_data['ltv_cac_ratio'] / channel_data['ltv_cac_ratio'].sum()

for _, row in channel_data.iterrows():
    recommended = row['budget_share'] / channel_data['budget_share'].sum()
    # Show investment recommendation
    if row['ltv_cac_ratio'] > 3:
        action = 'INCREASE INVESTMENT'
    elif row['ltv_cac_ratio'] > 1.5:
        action = 'MAINTAIN'
    else:
        action = 'REDUCE OR RESTRUCTURE'
    print(f"  {row['channel']:20s}: {action} (LTV/CAC = {row['ltv_cac_ratio']:.1f}x)")

6.2 Visualizing Channel Economics

▶ Prerequisite: continues from §6.1 — channel_data (with its calculated columns) must be defined. This block will not run standalone.

# Bubble chart: CAC vs. LTV with bubble size = customer volume
fig, ax = plt.subplots(figsize=(12, 8))

# Color by LTV/CAC ratio
colors = channel_data['ltv_cac_ratio']
normalized_size = channel_data['new_customers'] / channel_data['new_customers'].max() * 800

scatter = ax.scatter(
    channel_data['cac'],
    channel_data['ltv'],
    s=normalized_size,      # Bubble size = volume
    c=colors,               # Color = LTV/CAC
    cmap='RdYlGn',
    alpha=0.7,
    edgecolors='black',
    linewidth=0.5
)

# Add channel labels
for _, row in channel_data.iterrows():
    label = row['channel'].replace('_', ' ').title()
    ax.annotate(label,
                (row['cac'], row['ltv']),
                fontsize=9, ha='center', va='bottom',
                xytext=(0, 8), textcoords='offset points')

# Reference lines for LTV/CAC thresholds
max_val = max(channel_data['cac'].max(), channel_data['ltv'].max()) * 1.2
x = np.linspace(0, max_val)
ax.plot(x, x * 3, 'g--', alpha=0.5, label='LTV/CAC = 3x (Target)')
ax.plot(x, x * 1, 'r--', alpha=0.5, label='LTV/CAC = 1x (Break-even)')

ax.set_xlabel('CAC (₹)')
ax.set_ylabel('LTV (₹)')
ax.set_title('Channel Economics: CAC vs. LTV (Bubble Size = Volume)', fontweight='bold')
ax.legend()
ax.grid(True, alpha=0.3)
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)

# Colorbar for LTV/CAC
cbar = plt.colorbar(scatter)
cbar.set_label('LTV / CAC Ratio', fontsize=10)

plt.tight_layout()
plt.show()

print("\nChannels above the green line (LTV/CAC > 3) are healthy growth candidates.")
print("Channels between red and green lines need monitoring and optimization.")
print("Channels below the red line (LTV/CAC < 1) destroy value — fix or restructure.")

# ============================================================================
# Session 09 — Digital Customer Acquisition (COMPLETE RUNNABLE SCRIPT)
# InsurTech & Digital Risk Solutions (MBA) — Woxsen University
#
# HOW TO RUN:
#   venv/bin/python session_09_digital_acquisition.py
#   (or run top-to-bottom in one Jupyter notebook — the page's §5.1–§6.2
#    blocks are merged here in order so nothing is left undefined)
#
# DATA FILE: data/funnel_data.csv (provided) — the script also looks for
#            funnel_data.csv in the current folder as a fallback.
#
# OUTPUTS:   console tables + chapter9_funnel.png + channel_economics_bubble.png
# ============================================================================
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# ----------------------------------------------------------------------------
# SECTION 5.1 — Loading and Preparing Funnel Data
# Each row represents a user session with 1/0 flags for each funnel stage.
# ----------------------------------------------------------------------------
try:
    funnel_data = pd.read_csv('data/funnel_data.csv')
except FileNotFoundError:
    funnel_data = pd.read_csv('funnel_data.csv')

print(f"Total sessions tracked: {len(funnel_data):,}")
print(f"\nColumns: {funnel_data.columns.tolist()}")
print(f"\nChannels: {funnel_data['channel'].value_counts().to_dict()}")

# Calculate stage-wise conversion
funnel_stages = ['stage_awareness', 'stage_consideration',
                 'stage_quote', 'stage_purchase', 'stage_onboarding']

stage_counts = {}
for stage in funnel_stages:
    stage_counts[stage] = funnel_data[stage].sum()

print("\nStage-wise user counts:")
for stage, count in stage_counts.items():
    print(f"  {stage:25s}: {count:6,.0f}")

# ----------------------------------------------------------------------------
# SECTION 5.2 — Calculating Conversion Rates
# ----------------------------------------------------------------------------
# Overall conversion (top-of-funnel to end)
overall_conv = stage_counts['stage_onboarding'] / stage_counts['stage_awareness']
print(f"Overall conversion (Awareness → Onboarding): {overall_conv*100:.1f}%")

# Stage-by-stage conversion
stages_list = list(funnel_stages)
conversion_rates = {}
for i in range(len(stages_list) - 1):
    from_stage = stages_list[i]
    to_stage = stages_list[i + 1]
    rate = stage_counts[to_stage] / stage_counts[from_stage] if stage_counts[from_stage] > 0 else 0
    conversion_rates[f"{from_stage} → {to_stage}"] = rate
    print(f"  {from_stage:25s} → {to_stage:20s}: {rate*100:.1f}%")

# Conversion by channel
print("\n\nPurchase conversion rate by channel:")
channel_conv = funnel_data.groupby('channel').agg(
    total_awareness=('stage_awareness', 'sum'),
    total_purchase=('stage_purchase', 'sum')
).reset_index()
channel_conv['conversion'] = channel_conv['total_purchase'] / channel_conv['total_awareness']
print(channel_conv.sort_values('conversion', ascending=False).to_string(index=False))

# ----------------------------------------------------------------------------
# SECTION 5.3 — Funnel Visualization
# ----------------------------------------------------------------------------
# Funnel bar chart — classic insurance funnel visualization
stage_names = ['Awareness', 'Consideration', 'Quote', 'Purchase', 'Onboarding']
counts = [stage_counts[s] for s in funnel_stages]

fig, ax = plt.subplots(figsize=(10, 6))
colors = ['#74b9ff', '#a29bfe', '#6c5ce7', '#00b894', '#00d2d3']

bars = ax.barh(stage_names, counts, color=colors, edgecolor='white', height=0.6)

# Add data labels
for bar, count in zip(bars, counts):
    ax.annotate(f'{count:,} ({count/counts[0]*100:.1f}%)',
                xy=(count, bar.get_y() + bar.get_height()/2),
                ha='left', va='center', fontsize=11, fontweight='bold',
                xytext=(5, 0), textcoords='offset points')

# Add conversion arrows between bars
for i in range(len(counts) - 1):
    conv_pct = counts[i+1] / counts[i] * 100
    ax.annotate(f'  ↓ {conv_pct:.0f}%',
                xy=(counts[i]/2, i - 0.3), ha='center', fontsize=9,
                color='#e17055', fontweight='bold')

ax.invert_yaxis()  # Awareness at top
ax.set_title('Digital Insurance Acquisition Funnel', fontsize=14, fontweight='bold')
ax.set_xlabel('Number of Users')
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)
plt.tight_layout()
plt.savefig('chapter9_funnel.png', dpi=120)  # artifact for submission
plt.show()

# Identify biggest drop-off point
drop_offs = []
for i in range(len(counts) - 1):
    drop = (counts[i] - counts[i+1]) / counts[i] * 100
    drop_offs.append({'from': stage_names[i], 'to': stage_names[i+1], 'drop_pct': drop})

biggest_drop = max(drop_offs, key=lambda x: x['drop_pct'])
print(f"\nBiggest drop-off: {biggest_drop['from']} → {biggest_drop['to']}")
print(f"  {biggest_drop['drop_pct']:.1f}% of users drop off at this stage")
print(f"  Focus optimization efforts here.")

# ----------------------------------------------------------------------------
# SECTION 6.1 — Channel-Level CAC and LTV Analysis
# ----------------------------------------------------------------------------
# Channel cost and acquisition data
channel_data = pd.DataFrame({
    'channel': ['organic_search', 'paid_search', 'social_media',
                'aggregator', 'referral', 'embedded_partner'],
    'monthly_spend': [200000, 1500000, 1200000, 800000, 50000, 300000],
    'new_customers': [400, 450, 350, 600, 200, 800],
    'avg_premium': [8500, 7800, 7200, 7100, 8800, 6800],
    'loss_ratio': [0.62, 0.68, 0.72, 0.70, 0.58, 0.74],
    'retention_rate': [0.80, 0.72, 0.68, 0.65, 0.85, 0.60],
    'expense_ratio': [0.10, 0.12, 0.14, 0.08, 0.10, 0.15]  # Channel-specific
})

# Calculate CAC
channel_data['cac'] = channel_data['monthly_spend'] / channel_data['new_customers']

# Calculate profit margin and annual profit per customer
channel_data['profit_margin'] = (1 - channel_data['loss_ratio']
                                  - channel_data['expense_ratio'])
channel_data['annual_profit'] = (channel_data['avg_premium']
                                  * channel_data['profit_margin'])

# Calculate LTV (simplified: annual profit × avg tenure)
channel_data['avg_tenure'] = 1 / (1 - channel_data['retention_rate'])
channel_data['ltv'] = channel_data['annual_profit'] * channel_data['avg_tenure']

# Calculate LTV/CAC ratio
channel_data['ltv_cac_ratio'] = channel_data['ltv'] / channel_data['cac']

# Sort by LTV/CAC descending
channel_data = channel_data.sort_values('ltv_cac_ratio', ascending=False)

print("=" * 100)
print(f"{'Channel':20s} {'CAC (₹)':>10s} {'LTV (₹)':>10s} {'LTV/CAC':>8s} {'Volume':>8s} {'Rating'}")
print("=" * 100)

for _, row in channel_data.iterrows():
    rating = 'EXCELLENT' if row['ltv_cac_ratio'] > 5 \
        else 'GOOD' if row['ltv_cac_ratio'] > 3 \
        else 'MARGINAL' if row['ltv_cac_ratio'] > 1.5 \
        else 'POOR (FIX OR KILL)'
    print(f"{row['channel']:20s} ₹{row['cac']:>7,.0f}  ₹{row['ltv']:>8,.0f}  {row['ltv_cac_ratio']:>5.1f}x  {row['new_customers']:>5d}  {rating}")

print("\n" + "=" * 100)
print("RECOMMENDED BUDGET ALLOCATION (based on LTV/CAC):")
print("=" * 100)

total_ltv = channel_data['ltv'].sum()
channel_data['budget_share'] = channel_data['ltv_cac_ratio'] / channel_data['ltv_cac_ratio'].sum()

for _, row in channel_data.iterrows():
    recommended = row['budget_share'] / channel_data['budget_share'].sum()
    # Show investment recommendation
    if row['ltv_cac_ratio'] > 3:
        action = 'INCREASE INVESTMENT'
    elif row['ltv_cac_ratio'] > 1.5:
        action = 'MAINTAIN'
    else:
        action = 'REDUCE OR RESTRUCTURE'
    print(f"  {row['channel']:20s}: {action} (LTV/CAC = {row['ltv_cac_ratio']:.1f}x)")

# ----------------------------------------------------------------------------
# SECTION 6.2 — Visualizing Channel Economics
# Bubble chart: CAC vs. LTV with bubble size = customer volume
# ----------------------------------------------------------------------------
fig, ax = plt.subplots(figsize=(12, 8))

# Color by LTV/CAC ratio
colors = channel_data['ltv_cac_ratio']
normalized_size = channel_data['new_customers'] / channel_data['new_customers'].max() * 800

scatter = ax.scatter(
    channel_data['cac'],
    channel_data['ltv'],
    s=normalized_size,      # Bubble size = volume
    c=colors,               # Color = LTV/CAC
    cmap='RdYlGn',
    alpha=0.7,
    edgecolors='black',
    linewidth=0.5
)

# Add channel labels
for _, row in channel_data.iterrows():
    label = row['channel'].replace('_', ' ').title()
    ax.annotate(label,
                (row['cac'], row['ltv']),
                fontsize=9, ha='center', va='bottom',
                xytext=(0, 8), textcoords='offset points')

# Reference lines for LTV/CAC thresholds
max_val = max(channel_data['cac'].max(), channel_data['ltv'].max()) * 1.2
x = np.linspace(0, max_val)
ax.plot(x, x * 3, 'g--', alpha=0.5, label='LTV/CAC = 3x (Target)')
ax.plot(x, x * 1, 'r--', alpha=0.5, label='LTV/CAC = 1x (Break-even)')

ax.set_xlabel('CAC (₹)')
ax.set_ylabel('LTV (₹)')
ax.set_title('Channel Economics: CAC vs. LTV (Bubble Size = Volume)', fontweight='bold')
ax.legend()
ax.grid(True, alpha=0.3)
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)

# Colorbar for LTV/CAC
cbar = plt.colorbar(scatter)
cbar.set_label('LTV / CAC Ratio', fontsize=10)

plt.tight_layout()
plt.savefig('channel_economics_bubble.png', dpi=120)  # artifact for submission
plt.show()

print("\nChannels above the green line (LTV/CAC > 3) are healthy growth candidates.")
print("Channels between red and green lines need monitoring and optimization.")
print("Channels below the red line (LTV/CAC < 1) destroy value — fix or restructure.")