How Telecom Companies Use Digital Marketing to Grow Faster

# telecom_campaign_analysis.py — what a telecom growth marketer does weekly
# Compares channels on cost per activated subscriber — not just cost per lead.

import pandas as pd

# Weekly export from ad platforms + CRM (simplified)
data = {
    "channel": [
        "Google Search", "Google Search",
        "Meta", "Meta",
        "YouTube", "YouTube",
        "WhatsApp", "Affiliate",
    ],
    "campaign": [
        "New SIM - Delhi", "New SIM - Noida",
        "Data Pack - Gurgaon", "Recharge - Pan India",
        "Plan Comparison", "Network Quality",
        "Activation Nudge", "Plan Discovery",
    ],
    "spend": [420000, 310000, 380000, 540000, 260000, 190000, 70000, 130000],
    "leads": [4200, 3100, 5100, 7200, 2600, 1900, 3500, 2600],
    "activations": [1680, 1148, 1530, 2160, 520, 380, 2450, 520],
    "first_month_arpu": [210, 205, 230, 190, 200, 195, 215, 180],
    "churn_30d_pct": [0.22, 0.21, 0.25, 0.18, 0.30, 0.32, 0.12, 0.28],
}

df = pd.DataFrame(data)

# Core metrics
df["cost_per_lead"] = (df["spend"] / df["leads"]).round(2)
df["cost_per_activation"] = (df["spend"] / df["activations"]).round(2)
df["activation_rate_pct"] = (df["activations"] / df["leads"] * 100).round(1)

# Simple LTV estimate: ARPU × expected months (assume churn is constant)
def expected_months(churn: float) -> float:
    return 1 / churn if churn > 0 else 0

df["expected_months"] = df["churn_30d_pct"].apply(expected_months).round(1)
df["ltv"] = (df["first_month_arpu"] * df["expected_months"]).round(0)
df["ltv_cac"] = (df["ltv"] / df["cost_per_activation"]).round(2)

# Sort by LTV:CAC — the channel with the best ratio wins, not the cheapest
result = df.sort_values("ltv_cac", ascending=False)
print(result[[
    "channel", "campaign", "cost_per_activation",
    "activation_rate_pct", "ltv", "ltv_cac"
]].to_string(index=False))

# What a telecom growth marketer does with this:
# - WhatsApp Activation Nudge has the lowest CPA and the highest LTV:CAC —
#   scale it and study why activation is so high there.
# - YouTube Plan Comparison has a terrible LTV:CAC — it may be worth keeping
#   for brand but should be capped, not scaled.
# - Meta Recharge Pan India has moderate LTV:CAC but high volume — a good
#   candidate for incremental testing.
#
# The mistake beginners make: ranking channels by cost per lead alone.
# That ignores activation, churn, and ARPU. A channel can look cheap on
# leads and be expensive on lifetime value.

// ga4-telecom-funnel.js — the events a telecom marketing analyst sets up
// Without these, you cannot measure activation or ARPU — only pageviews.

// 1. User requests a new SIM on the website
gtag("event", "sim_request", {
  plan_type: "prepaid",       // prepaid | postpaid
  circle: "delhi_ncr",
  source: "google_search",
});

// 2. KYC is completed (usually in the app)
gtag("event", "kyc_complete", {
  plan_type: "prepaid",
  circle: "delhi_ncr",
  verification_time_min: 4,
});

// 3. SIM is activated and the subscriber starts using the network
gtag("event", "sim_activated", {
  plan_type: "prepaid",
  circle: "delhi_ncr",
  days_since_request: 2,
});

// 4. First recharge completed — the true "activation" event
gtag("event", "first_recharge", {
  value: 239,                 // amount in INR
  currency: "INR",
  plan_type: "prepaid",
  pack: "1.5GB_per_day_28days",
});

// 5. Plan upgrade (ARPU growth)
gtag("event", "plan_upgrade", {
  value: 599,
  currency: "INR",
  previous_pack: "1.5GB_per_day_28days",
  new_pack: "2GB_per_day_84days",
});

// Why this matters:
// - Pageviews tell you nothing about telecom growth.
// - These five events let you build the full funnel:
//     sim_request → kyc_complete → sim_activated → first_recharge → plan_upgrade
// - Without them, "cost per activation" is a guess, not a number.
// - A telecom marketer who understands this is far more valuable than
//   one who only knows ad platforms.
//
// Note: in real telecom companies, these events flow through a CRM or
// the mobile app, not only the website. The principle is the same —
// instrument the funnel at every step.

-- telecom-cohort-analysis.sql
-- Cohort retention and ARPU — the metrics a telecom growth team lives by.

-- 1. Monthly retention and ARPU by activation cohort
WITH base AS (
  SELECT
    subscriber_id,
    DATE_TRUNC(activated_at, MONTH) AS cohort_month,
    DATE_TRUNC(recharge_date, MONTH) AS activity_month,
    SUM(recharge_amount) AS revenue
  FROM subscriber_recharges
  WHERE activated_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
  GROUP BY 1, 2, 3
)
SELECT
  cohort_month,
  activity_month,
  DATE_DIFF(activity_month, cohort_month, MONTH) AS months_since_activation,
  COUNT(DISTINCT subscriber_id) AS active_subscribers,
  ROUND(SUM(revenue) / COUNT(DISTINCT subscriber_id), 2) AS arpu_inr
FROM base
GROUP BY 1, 2, 3
ORDER BY cohort_month, activity_month;

-- 2. Churn rate by acquisition channel
SELECT
  acquisition_channel,
  COUNT(*) AS total_subscribers,
  SUM(IF(churned_at IS NOT NULL, 1, 0)) AS churned,
  ROUND(SUM(IF(churned_at IS NOT NULL, 1, 0)) * 100.0 / COUNT(*), 2) AS churn_pct,
  ROUND(AVG(first_month_arpu), 2) AS avg_first_month_arpu
FROM subscribers
WHERE activated_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH)
GROUP BY acquisition_channel
ORDER BY churn_pct;

-- 3. Which channels produce subscribers who stay and grow?
SELECT
  s.acquisition_channel,
  COUNT(DISTINCT s.subscriber_id) AS subscribers,
  ROUND(AVG(s.first_month_arpu), 2) AS first_month_arpu,
  ROUND(AVG(r.total_revenue), 2) AS avg_12m_revenue,
  ROUND(AVG(DATEDIFF(s.churned_at, s.activated_at)), 1) AS avg_lifetime_days
FROM subscribers s
LEFT JOIN (
  SELECT subscriber_id, SUM(recharge_amount) AS total_revenue
  FROM subscriber_recharges
  WHERE recharge_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
  GROUP BY 1
) r ON r.subscriber_id = s.subscriber_id
WHERE s.activated_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
GROUP BY s.acquisition_channel
ORDER BY avg_12m_revenue DESC;

-- What a telecom growth team does with this:
-- - Identifies channels that bring high-ARPU, low-churn subscribers,
--   even if their cost per activation is higher.
-- - Tests lifecycle campaigns on cohorts with high early churn.
-- - Reallocates budget toward channels that produce long-term revenue,
--   not just cheap leads.
--
-- This is why SQL is one of the highest-leverage skills in telecom
-- digital marketing. Ad platforms tell you what happened at the top
-- of the funnel; SQL tells you what happened to revenue over time.