Data Storytelling with SQL: Churn Insights Across Customer Segments in the Telecom Business

Image generation by ChatGPT

The purpose of this customer segment analysis is to identify which segments have the highest churn rates and also to predict potential high-risk customer groups based on historical patterns. In this analysis, revenue is excluded.

The analysis distinguishes between churn risk and churn impact on the business. Let’s drill down into the insights. A small segment might have the highest churn rate but affect relatively few customers, while a large segment with a moderate churn rate could cause a much greater loss of customers and revenue. The magnitude of the impacts on business is more significant.

For the analysis, three rankings: churn rate rank, total customers rank, and churned customers rank, are applied to compare customer churn in the proportion and volume of customers. Telecom stakeholders can use all three rankings for data-driven decisions to prioritize targeted retention programs, contract incentives, onboarding improvements, and customer engagement strategies.

Dataset Background & Overview

The telco customer churn dataset contains fictional company data that provides home phone and internet service to customers in an urban area of California in Q2 2022. The original dataset consists of 5 CSV files in demographics, location, population, services, and status data. It provides valuable multifaceted customer data, including their demographics, geographical location, adoption of multiple services, payment method and churn status.

For this project, the telecom data is cleaned, validated, and transformed to ensure data integrity, and then divided and created into 6 SQL VIEWS (virtual tables in the SQL database) to perform analysis using SQLite & Python.

👉Data source from Kaggle, originated from the IBM TechXchange Community blog

Analysis Process with SQLite

This SQLite query compares churn across four customer segmentation dimensions, including age group, tenure band, contract type, and customer lifecycle tier, to identify which segments have the highest churn risk and the greatest number of churned customers.

Business Purpose:

Based on the customer segment analysis, compare the churn rate with the total number of churned customers to measure the magnitude of the impact on business.

👉Note: The following queries are in sequence order in one code block to return the output.

Step 1: Build a customer-level dataset.

The query joined three analytical views using customer_id, which is a primary key.

This creates one consolidated dataset for segment-level churn analysis. It is structured as a pipeline of Common Table Expressions (CTEs) to build a customer-level dataset, aggregate churn across four dimensions, calculate an overall benchmark, add window-function rankings for the segments, and execute the final result. Each CTE has one primary responsibility.

The WITH clause defines four CTEs. A CTE is a named, temporary result set available only while the current SQL statement is running.

👉Here is the SQLite query to build a customer-level dataset named base:

WITH base AS (   
   SELECT
       c.customer_id,
       c.age_group,
       s.tenure_band,
       s.contract,
       s.customer_lifecycle_tier,
       ch.is_churned
   FROM vw_customer c
   JOIN vw_subscription s
       ON c.customer_id = s.customer_id
   JOIN vw_churn ch
       ON c.customer_id = ch.customer_id
),

Step 2: Calculate churn metrics for each segment.

The segment_dimension and segment_value separately evaluate customer groups.

For every value within the four dimensions, the query calculates:

• Total customers
• Churned customers
• Retained customers
• Churn rate

The UNION ALL statements combine these separate analyses into one standardized results table. It appends the result without removing duplicate rows. It is appropriate because each query represents a different segmentation dimension.

👉Here is the SQLite query to calculate churn metrics for each segment:

segment_analysis AS (   
   SELECT
       'Age Group' AS segment_dimension,
       age_group AS segment_value,
       COUNT(*) AS total_customers,
       SUM(is_churned) AS churned_customers, 
       COUNT(*) - SUM(is_churned) AS retained_customers,
       ROUND(100.0 * SUM(is_churned) / COUNT(*), 2) AS churn_rate_pct
   FROM base
   WHERE age_group IS NOT NULL
   GROUP BY age_group


   UNION ALL


   SELECT
       'Tenure Band' AS segment_dimension,
       tenure_band AS segment_value,
       COUNT(*) AS total_customers,
       SUM(is_churned) AS churned_customers,
       COUNT(*) - SUM(is_churned) AS retained_customers,
       ROUND(100.0 * SUM(is_churned) / COUNT(*), 2) AS churn_rate_pct
   FROM base
   WHERE tenure_band IS NOT NULL
   GROUP BY tenure_band


   UNION ALL


   SELECT
       'Contract Type' AS segment_dimension,
       contract AS segment_value,
       COUNT(*) AS total_customers,
       SUM(is_churned) AS churned_customers,
       COUNT(*) - SUM(is_churned) AS retained_customers,
       ROUND(100.0 * SUM(is_churned) / COUNT(*), 2) AS churn_rate_pct
   FROM base
   WHERE contract IS NOT NULL
   GROUP BY contract


   UNION ALL


   SELECT
       'Customer Lifecycle Tier' AS segment_dimension,
       customer_lifecycle_tier AS segment_value,
       COUNT(*) AS total_customers,
       SUM(is_churned) AS churned_customers,
       COUNT(*) - SUM(is_churned) AS retained_customers,
       ROUND(100.0 * SUM(is_churned) / COUNT(*), 2) AS churn_rate_pct
   FROM base
   WHERE customer_lifecycle_tier IS NOT NULL
   GROUP BY customer_lifecycle_tier
),

The `is_churned` contains a binary indicator: 1 (churned), 0 (retained). So, adding the values of the `is_churned` produces the number of churned customers.

Each segment query is filtered in the WHERE statement to remove customers whose segment classification is unavailable. Without this filter, SQLite would create a separate NULL group. Also, GROUP BY is used in each segment query to change the data from the customer-level grain to the segment-level grain. After grouping, each output row represents one segment value rather than one customer.

Step 3: Calculate the company-wide churn rate

The query calculates the overall percentage of customers who churned. This becomes a benchmark for evaluating whether an individual segment performs better or worse than the total customer base.

Get WonHee Lee’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

This CTE applies the same churn formula to the entire base dataset. Therefore, it returns one row containing one company-wide churn rate. The `overall_churn_rate_pct` is calculated separately because the overall rate includes all rows in the base, while individual segment queries exclude null values for their respective dimensions.

Here is the SQLite query to calculate the company-wide churn rate:

overall_churn AS (   
   SELECT
       ROUND(100.0 * SUM(is_churned) / COUNT(*), 2) AS overall_churn_rate_pct
   FROM base
),

A positive `churn_rate_gap_pct` means the segment’s churn rate is above the company average and may require greater attention. A negative value means the segment is retaining customers more effectively than average.

Step 4: Compare and rank the segments

Each customer segment is compared with the overall churn rate and assigned three rankings:

• Churn rate gap in percentage: `churn_rate_pct` — `overall_churn_rate_pct`
• Churn rate rank
• Customer volume rank
• Churned customer rank

The query used the CROSS JOIN because the overall_churn returns one row; the CROSS JOIN attaches its single benchmark value to every row from the segment_analysis. It also requires no join key.

Here is the SQLite query to compare and rank the segments:

ranked_segments AS (   
   SELECT
       sa.segment_dimension,
       sa.segment_value,
       sa.total_customers,
       sa.churned_customers,
       sa.retained_customers,
       sa.churn_rate_pct,
       oc.overall_churn_rate_pct,


       ROUND(sa.churn_rate_pct - oc.overall_churn_rate_pct, 2) AS churn_rate_gap_pct, 


       RANK() OVER (ORDER BY sa.churn_rate_pct DESC) AS churn_rate_rank,


       RANK() OVER (ORDER BY sa.total_customers DESC) AS customer_volume_rank,


       RANK() OVER (ORDER BY sa.churned_customers DESC) AS churned_customer_rank


   FROM segment_analysis sa
   CROSS JOIN overall_churn oc
)

Window-function rankings calculate values across related rows without collapsing the result set. Each segment remains a separate row while receiving a rank. RANK() assigns equal ranks to tied values and leaves gaps afterward. For example, a ranking could be 1, 2, 2, 4.

Step 5: Display the results

The final output lists the highest-churn segments first. When two segments have the same churn rate, the query prioritizes the segment with more customers and then the one with more churned customers.

Here is the SQLite query to make the output:

SELECT
   segment_dimension,
   segment_value,
   total_customers,
   churned_customers,
   retained_customers,
   churn_rate_pct,
   overall_churn_rate_pct,
   churn_rate_gap_pct,
   churn_rate_rank,
   customer_volume_rank,
   churned_customer_rank
FROM ranked_segments
ORDER BY
   churn_rate_pct DESC,
   total_customers DESC,
   churned_customers DESC;

SQLite applies the sorting rules from left to right: 1. highest churn rate first, 2. if churn rates are equal, largest segment first, 3. if both are equal, highest churned-customer count first. The ranking functions calculate analytical positions, while the final ORDER BY controls only how the rows are displayed.

The Output from the Queries:

Output from customer segment analysis

Insights of the output

• Early-life churn is the most urgent customer-experience problem. Customers in their first 0–6 months have the highest churn rate at 53.12%, double the overall rate of 26.56%. This signals potential weaknesses in onboarding, service activation, or early support. Improving the first-six-month experience is critical to recovering acquisition costs and increasing customer lifetime value.
• Month-to-month contracts create the greatest churn exposure. This group combines a high churn rate of 45.79% with the largest customer base and the highest churn volume — 1,625 lost customers. The business should prioritize targeted migration to longer-term contracts through loyalty pricing, service bundles, and value-based incentives while addressing the reasons customers prefer flexibility.
• Retention strengthens dramatically with tenure and contractual commitment. Churn declines to 9.66% after 49 months and only 2.59% under two-year contracts. This shows that moving customers beyond the early lifecycle and building stronger commitment can substantially stabilize the customer base.

Strategic Recommendations

• Strengthen early-lifecycle retention: Customer Success and Operations teams should introduce a structured 90–180-day onboarding program with activation support, service-quality checks, and proactive outreach. Prioritize new month-to-month customers and track 0–6-month churn, early complaints, and service adoption.
• Convert high-risk customers to longer-term contracts: Marketing and Retention teams should offer targeted loyalty discounts, value-added bundles, or rate guarantees to suitable month-to-month customers, especially those showing churn-risk signals. Measure conversion rates, incremental retention, churn reduction, and the financial return of incentives.

Caveats and Assumptions

• The Stakeholders referenced in the strategic recommendations are hypothetical and were assigned based on a simulated telecom company scenario. They do not represent confirmed stakeholders from an actual telecom company.

Tech Stack Used

• Data Preparation: Excel & Power Query Editor, SQLite, VS Code
• AI-assisted: ChatGPT, Copilot, Gemini

👩‍💻

👉 This is a part of my data analytics project, and I’d love to hear your constructive feedback, ideas, or suggestions for improvement. 🧐