Maven Churn Challenge

Lauren Rosenthal Avatar

Lauren Rosenthal

Maven Analytics

Tableau Dashboard

About this project

Getting Started

Introduction

In this project, I acted as a Business Intelligence Consultant for Maven Communications, a California-based Telecommunications company.

Business Goal

My primary goal was to help the company improve retention by identifying high value customers and churn risks.

Recommendations based on analysis

Look into why customers from San Diego are leaving at a higher rate. Internet type may play a role as most of the customers leaving utilized fiber optics.
Assess the competition; most churned customers stated that they were leaving due to better devices or better offers from competitors.
1. Reassess your offers. Most customers had not accepted an offer over the given time period. The churned customers who did accept an offer recently mostly fell into Offer E but soon left with the most cited reason as being a competitor.
Consider trialing special offers for customers who choose a one- or two-year contract. Of churned customers, 1655 of them (89%) were on month-to-month contracts.
1. Look at customer service within the first months, as almost 23% of customers who churned within the first year actually churned within the first month.
Consider incentivizing referrals. The majority of customers (54%) have not given any referrals. Within churned customers, 507 had done one referral, while 579 of current customers had also done one referral. Were churned customers expecting something that they didn’t get and then choose to not refer more?

Exploration

The Data

This dataset contains 2 tables, in CSV format:

The Customer Churn table contains information on all 7,043 customers from a Telecommunications company in California in Q2 2022
- Each record represents one customer, and contains details about their demographics, location, tenure, subscription services, status for the quarter (joined, stayed, or churned), and more!
The Zip Code Population table contains complimentary information on the estimated populations for the California zip codes in the Customer Churn table

source: Maven Churn Challenge

The Plan

Thinking like the business owner

What are the goals and objectives?

improve retention
identify high value customers
identify churn risks

Who are the key stakeholders and how will this help them?

CMO, managers
help them retain high-value customers and make changes to decrease churn risks

How does my work fit into the overall business strategy?

may impact scheduling for customer service
- consider an incentivizing strategy for customer service agents
increase retention rate —> overall revenue increase

Thinking about the stakeholders:

What is the primary stakeholder most interested in?

customer retention, customer satisfaction, churn rate

What information can I provide?

percentage of churn rate, percentage of churn categories
trends about high value customers

Defining the KPIs:

number/percentage of customers retained
customer lifetime value
total revenue
- revenue by product/service
- average revenue per account (ARPA)
churn rate

Analysis

Cleaning the data:

I downloaded the CSV files and opened in Excel to get familiar with the structure of the data. Along with the two datasets, a “data dictionary” was also provided to give insight into what you could expect to find in each field.
1. After becoming familiar with the data, I decided to edit the column names to adhere to SQL column naming conventions. For example, I changed ‘Phone Service’ to ‘phone_service.’
2. I ensured that there were no blank cells within the table that would change or skew the data.
  1. I left blanks that related to a different column (i.e. if customer did not have internet service, other services related to internet may be blank.)
3. I ensured that there were no duplicate entries using Count Distinct in Excel.
I imported the datasets into MySQL to begin some exploratory analysis.

Getting to know the data:

1. How many customers are in the database?

Total count of customers

7043

Count of current customers

5174

Percentage of current customers

73.46%

Count and percentage of churned customers with example query:

SET @totalqty := (
SELECT 
     COUNT(customer_status) 
FROM telecom_customer_churn 
WHERE customer_status IN ('joined', 'stayed', 'churned'));

SELECT
     customer_status
     , COUNT(customer_status) AS churn_counted
     , ROUND((COUNT(customer_status))/@totalqty * 100, 2) AS percent
FROM telecom_customer_churn
WHERE customer_status = 'churned'
GROUP BY customer_status;

2. What are the age and gender breakdowns of the customers?

Total gender breakdown

Female: 3488
Male: 3555

Current gender count

Female: 2549
Male: 2625

Current vs. total customer age breakdown with example query:

    SELECT
     'Current_Cust' AS Type
     , COUNT(CASE WHEN age BETWEEN 19 AND 24 THEN 1 END) AS early20s
     , COUNT(CASE WHEN age BETWEEN 25 AND 29 THEN 1 END) AS late20s
     , COUNT(CASE WHEN age BETWEEN 30 AND 34 THEN 1 END) AS early30s
     , COUNT(CASE WHEN age BETWEEN 35 AND 39 THEN 1 END) AS late30s
     , COUNT(CASE WHEN age BETWEEN 40 AND 44 THEN 1 END) AS early40s
     , COUNT(CASE WHEN age BETWEEN 45 AND 49 THEN 1 END) AS late40s
     , COUNT(CASE WHEN age BETWEEN 50 AND 54 THEN 1 END) AS early50s
     , COUNT(CASE WHEN age BETWEEN 55 AND 59 THEN 1 END) AS late50s
     , COUNT(CASE WHEN age BETWEEN 60 AND 64 THEN 1 END) AS early60s
     , COUNT(CASE WHEN age BETWEEN 65 AND 69 THEN 1 END) AS late60s
     , COUNT(CASE WHEN age BETWEEN 70 AND 74 THEN 1 END) AS early70s
     , COUNT(CASE WHEN age > 74 THEN 1 END) AS late70s
FROM telecom_customer_churn
WHERE customer_status IN ('stayed', 'joined')

UNION ALL

SELECT
     'Total_Cust'
     , COUNT(CASE WHEN age BETWEEN 19 AND 24 THEN 1 END) AS early20s
     , COUNT(CASE WHEN age BETWEEN 25 AND 29 THEN 1 END) AS late20s
     , COUNT(CASE WHEN age BETWEEN 30 AND 34 THEN 1 END) AS early30s
     , COUNT(CASE WHEN age BETWEEN 35 AND 39 THEN 1 END) AS late30s
     , COUNT(CASE WHEN age BETWEEN 40 AND 44 THEN 1 END) AS early40s
     , COUNT(CASE WHEN age BETWEEN 45 AND 49 THEN 1 END) AS late40s
     , COUNT(CASE WHEN age BETWEEN 50 AND 54 THEN 1 END) AS early50s
      , COUNT(CASE WHEN age BETWEEN 55 AND 59 THEN 1 END) AS late50s
     , COUNT(CASE WHEN age BETWEEN 60 AND 64 THEN 1 END) AS early60s
     , COUNT(CASE WHEN age BETWEEN 65 AND 69 THEN 1 END) AS late60s
     , COUNT(CASE WHEN age BETWEEN 70 AND 74 THEN 1 END) AS early70s
     , COUNT(CASE WHEN age > 74 THEN 1 END) AS late70s
FROM telecom_customer_churn;

Type	Early 20s	Late 20s	Early 30s	Late 30s	Early 40s	Late 40s	Early 50s	Late 50s	Early 60s	Late 60s	Early 70s	Late 70s
Current Customers	621	476	470	487	511	513	461	484	485	212	211	243
Total Customers	779	622	626	640	677	665	621	631	640	364	358	420

3. What is the minimum, maximum, and average monthly charge? Total revenue?

Monthly minimum, maximum, and average charges

Minimum: -$10
Maximum: $118.75
Average: $63.60

Quarterly minimum, maximum, and average charges

Minimum: $18.8
Maximum: $8684.8
Average: $2280.38

Total minimum, maximum, and average charges

Minimum: $21.36
Maximum: $ 11979.34
Average: $3034.38

4. How many customers use the phone service? Internet service? Additional services?

Current customer services

Phone: 4664
Internet: 3761
Online security: 1724
Online backup: 1906
Protection plan: 1877
Premium tech support: 1734

5. What is the breakdown of churn categories (i.e. how many customers fall under each category of reason for leaving)?

Number of churn categories and percentage with example query:

SET @totalqty := (
     SELECT 
          COUNT(churn_cat) 
     FROM telecom_customer_churn
     WHERE churn_cat IN ('attitude', 'price', 'dissatisfaction', 'competitor', 'other'));
SELECT 
     churn_cat
     , COUNT(churn_cat) AS number_per_category
     , ROUND((COUNT(churn_cat))/@totalqty * 100, 2) AS percent
FROM telecom_customer_churn
WHERE churn_cat IN ('attitude', 'price', 'dissatisfaction', 'competitor', 'other')
GROUP BY churn_cat;

Churn Category	Number/Category	Percent
Competitor	841	45%
Dissatisfaction	321	17.17%
Other	182	9.74%
Price	211	11.29%
Attitude	314	16.8%

6. What is the breakdown of tenure at the company for current and past customers?

Tenure of churned customers

First year: 1037
Second year: 294
Third year: 180
Fourth year: 145
Fifth year: 120
More than 5 years: 93

Interpretation: an overwhelming number of churned customers were with Maven Communications for less than 1 year

Tenure of current customers with example query:

SELECT
     CASE
          WHEN tenure_months <= 12 THEN 'first year'
          WHEN tenure_months BETWEEN 13 AND 24 THEN 'second year'
          WHEN tenure_months BETWEEN 25 AND 36 THEN 'third year'
          WHEN tenure_months BETWEEN 37 AND 48 THEN 'fourth year'
          WHEN tenure_months BETWEEN 49 AND 60 THEN 'fifth year'
          ELSE 'more than 5 years'
     END AS monthly_tenure,
     COUNT(*) AS number_of_customers
FROM telecom_customer_churn
WHERE customer_status IN ('stayed', 'joined')
GROUP BY monthly_tenure
ORDER BY number_of_customers DESC;

First year: 1149
Second year: 730
Third year: 652
Fourth year: 617
Fifth year: 712
More than 5 years: 1314

Interpretation: Contrasting churned customers, the majority of current customers have been with Maven Communications for more than 5 years.

7. How many customers use streaming services?

Count of current customers with streaming services

TV: 1893
Movies: 1914
Music 1760

Count of churned customers with streaming services

TV: 814
Movies: 818
Music 728

8. Where are customers located?

Total customer locations (top 5 cities)

Los Angeles: 293
San Diego: 285
San Jose: 112
Sacramento: 108
San Francisco: 104

Current customer locations (top 5 cities)

Los Angeles: 215
San Diego: 100
San Jose: 83
Sacramento: 82
San Francisco: 73

Interpretation: San Diego's customer-base has decreased significantly.

Churned Customer Locations (top 5 cities)

San Diego: 185
Los Angeles: 78
San Francisco: 31
San Jose: 29
Fallbrook: 26

Interpretation: As expected, almost 2/3 of San Diego's customers have churned.

9. What type of contract is used most frequently?

Month-to-month: 1955
One-year: 1384
Two-year: 1835

Diving into the business problem:

As noted in the exploratory analysis, 26.54% of customers in the database have churned. The majority of customers were in the San Diego area, as over half of the San Diego customer base churned (185 out of 285). The most frequent reason customers gave for churning was “competitor” at 45% with “dissatisfaction” (17.17%) and “attitude” (16.8%) coming in 2nd and 3rd, respectively. To determine in more detail why customers might churn, I analyzed the following:

1. What are the demographics of customers who have churned?

Average age and gender breakdown

Average age: 49.74
Female count: 939
Male count: 930

Analysis of churned customer age with example query:

`SELECT
     'Past_Cust' AS Type
     , COUNT(CASE WHEN age BETWEEN 19 AND 24 THEN 1 END) AS early20s
     , COUNT(CASE WHEN age BETWEEN 25 AND 29 THEN 1 END) AS late20s
     , COUNT(CASE WHEN age BETWEEN 30 AND 34 THEN 1 END) AS early30s
     , COUNT(CASE WHEN age BETWEEN 35 AND 39 THEN 1 END) AS late30s
     , COUNT(CASE WHEN age BETWEEN 40 AND 44 THEN 1 END) AS early40s
     , COUNT(CASE WHEN age BETWEEN 45 AND 49 THEN 1 END) AS late40s
     , COUNT(CASE WHEN age BETWEEN 50 AND 54 THEN 1 END) AS early50s
     , COUNT(CASE WHEN age BETWEEN 55 AND 59 THEN 1 END) AS late50s
     , COUNT(CASE WHEN age BETWEEN 60 AND 64 THEN 1 END) AS early60s
     , COUNT(CASE WHEN age BETWEEN 65 AND 69 THEN 1 END) AS late60s
     , COUNT(CASE WHEN age BETWEEN 70 AND 74 THEN 1 END) AS early70s
     , COUNT(CASE WHEN age > 74 THEN 1 END) AS late70s
FROM telecom_customer_churn
WHERE customer_status IN ('churned')

UNION ALL

SELECT
     'Total_Cust'
     , COUNT(CASE WHEN age BETWEEN 19 AND 24 THEN 1 END) AS early20s
     , COUNT(CASE WHEN age BETWEEN 25 AND 29 THEN 1 END) AS late20s
     , COUNT(CASE WHEN age BETWEEN 30 AND 34 THEN 1 END) AS early30s
     , COUNT(CASE WHEN age BETWEEN 35 AND 39 THEN 1 END) AS late30s
     , COUNT(CASE WHEN age BETWEEN 40 AND 44 THEN 1 END) AS early40s
     , COUNT(CASE WHEN age BETWEEN 45 AND 49 THEN 1 END) AS late40s
     , COUNT(CASE WHEN age BETWEEN 50 AND 54 THEN 1 END) AS early50s
     , COUNT(CASE WHEN age BETWEEN 55 AND 59 THEN 1 END) AS late50s
     , COUNT(CASE WHEN age BETWEEN 60 AND 64 THEN 1 END) AS early60s
     , COUNT(CASE WHEN age BETWEEN 65 AND 69 THEN 1 END) AS late60s
     , COUNT(CASE WHEN age BETWEEN 70 AND 74 THEN 1 END) AS early70s
     , COUNT(CASE WHEN age > 74 THEN 1 END) AS late70s
FROM telecom_customer_churn;

Type	Early 20s	Late 20s	Early 30s	Late 30s	Early 40s	Late 40s	Early 50s	Late 50s	Early 60s	Late 60s	Early 70s	Late 70s
Churned	158	146	156	153	166	152	160	147	155	152	147	177
Total	779	622	626	640	677	665	621	631	640	364	358	420
Percentage	20.28%	23.47%	24.92%	23.91%	24.52%	22.86%	25.76%	23.3%	24.22%	41.76%	41.06%	42.14%

Interpretation: Churned customers who are in their late 60s and above took out 40% of that age's customer base.

Percentage of married churned customers

Married: 35.79%

Churned customers with dependents

Number of dependents	Number of churned customers
0	1763
1	38
2	33
3	30
4	1
5	2
6	1
7	1

Interpretation: The vast majority of churned customers reported no dependents. This could be a potential reason for churning.

2. How many customers who have churned recently accepted offers from Maven Communications?

Customer offer acceptance

Customer Status	Offer A	Offer B	Offer C	Offer D	Offer E	None
Stayed	485	723	320	441	204	2547
Churned	35	101	95	161	426	1051
Joined	0	0	0	0	175	279

Interpretation: There is a discrepancy between churned customers vs. stayed customers and offer acceptance. Churned customers most often utilized Offer E.

Looking into offer E and example query:

`SELECT
     churn_cat,
     COUNT(*) AS number_customers
FROM telecom_customer_churn
WHERE offer = 'offer E'
GROUP BY churn_cat
ORDER BY churn_cat DESC;

SELECT
     churn_reason,
     COUNT(churn_reason) AS reason
FROM telecom_customer_churn
WHERE customer_status = 'churned' and offer = 'offer e'
GROUP BY churn_reason
ORDER BY reason DESC
LIMIT 10;

Interpretation: The majority of customers who accepted Offer E later left Maven for a competitor (201 customers) with leading reasons being "competitor made better offer" (78 customers) and "competitor had better devices" (77 customers).

3. What services were people utilizing prior to churning?

Phone services

Had phone services: 1699
Did not have phone services: 170

Internet services:

Had internet services: 1756
Did not have internet services: 113

Type of internet services

Fiber optics: 1236
DSL: 307
Cable: 213

Interpretation: The majority of churned customers utilized fiber optics.

Unlimited data

Yes: 1502
No: 254

Streaming services

Movies: 808
TV: 814
Music: 728
ALL: 450

4. What was the average monthly, quarterly, and total revenue charge of churned customers?

WITH churned_customers AS (SELECT * FROM telecom_customer_churn
WHERE customer_status = 'churned')

SELECT
     'minimum' AS Type,
     MIN(total_revenue) AS total_revenue,
     MIN(total_charges) AS quarterly,
     MIN(monthly_charge) AS monthly
FROM churned_customers

UNION ALL

SELECT
     'maximum',
     MAX(total_revenue) AS total_revenue,
     MAX(total_charges) AS quarterly,
     MAX(monthly_charge) AS monthly
FROM churned_customers

UNION ALL

SELECT
     'average',
     ROUND(AVG(total_revenue),2) AS total_revenue,
     ROUND(AVG(total_charges),2) AS quarterly,
     ROUND(AVG(monthly_charge),2) AS monthly
FROM churned_customers;

Type	Total Revenue	Quarterly	Monthly
Minimum	26.61	18.85	-10
Maximum	11195.44	8684.8	118.35
Average	1971.35	1531.8	73.35

5. What type of contract was most frequent for churned customers and how quickly did they churn?

Contract type for churned customers

Month-to-month: 1655
One-year: 166
Two-year: 48

Interpretation: A large majority of churned customers were under month-to-month contracts. A secondary query revealed that 22.96% of churned customers left within the first month of their contract.

Reasons for leaving within 1st month

Competitor: 163 customers
Attitude: 72 customers
Price: 47 customers
Other: 33 customers
Dissatisfaction: 65 customers

6. What percent of total revenue do churned customers make?

Percent of total revenue from churned customers

$3.68M
17.24% of total revenue

7. What are the top reasons why customers churned?

Top 10 churn reasons with example query:

SET @totalchurned := (SELECT COUNT(churn_reason) FROM telecom_customer_churn WHERE customer_status = 'churned');
SELECT
     churn_reason,
     COUNT(churn_reason) AS num_per_reason,
     ROUND(COUNT(churn_reason) * 100 / @totalchurned,2) AS percent_per_reason
FROM telecom_customer_churn
WHERE customer_status = 'churned'
GROUP BY churn_reason
ORDER BY num_per_reason DESC
LIMIT 5;

Churn reason	Number/reason	Percent/reason
Competitor: better devices	313	16.75%
Competitor: better offer	311	16.64%
Attitude: support person	220	11.77%
Don't know	130	6.96%
Competitor: more data	117	6.26%

Revenue from top 3 churned reason

$2.17M
10.17% of total revenue

High value customer data:

Before beginning my analysis, I qualified metrics on what qualities we would measure “high value” by. A high value customer is one whose business will significantly impact the bottom line. Total revenue, usage of multiple services, average revenue per account (APRA), tenure, and number of referrals are all ways we could gain insight into high level customers.

1. How many customers utilize multiple services (phone, internet, streaming, unlimited data) and what percentage of those have churned?

Number of churned customers with multiple services

463 customers
Makes up 30.56% of all customers who had multiple services

2. Who are the customers who generate the top third of total revenue? How many of those customers have churned?

Percent of churned top revenue generators with example query:

`WITH cte AS (
SELECT 
     customerID
     , total_revenue
     , customer_status
FROM    (
     SELECT 
          telecom_customer_churn.*
          , @counter := @counter +1 AS counter
     FROM (select @counter:=0) AS initvar, 
     telecom_customer_churn
     ORDER BY total_revenue DESC
) AS X
WHERE counter <= (33/100 * @counter)
ORDER BY total_revenue DESC)`

SELECT
     COUNT(customerID) AS num_churned
     , ROUND(COUNT(customerID) * 100 / (SELECT COUNT(*) FROM cte),2) AS percent_churned
     , ROUND(SUM(total_revenue)) AS revenue_lost
FROM cte
WHERE customer_status = 'churned';

365 of the top revenue generators were churned customers
15.71% of top generators
$2.29M revenue lost due to churn of top customers

3. What is the average revenue per account (APRA)?

ARPA

$3034.38 overall

Churned customers above ARPA with example query:

SET @arpa := (
     SELECT 
          ROUND(SUM(total_revenue) / COUNT(customerID),2) AS ARPA
     FROM telecom_customer_churn);

SELECT 
     COUNT(customerID) AS num_cust
     , ROUND(AVG(total_revenue),2) AS avg_total_rev
     , ROUND(AVG(total_revenue) * 100 / @arpa) AS percent_of_arpa
FROM telecom_customer_churn
WHERE total_revenue > @arpa AND customer_status ='churned';

Interpretation: 446 churned customers with total revenue above ARPA, with an average revenue $5749.21, making it 189% of the ARPA.

Key Takeaways

Look into why customers from San Diego are leaving at a higher rate. Internet type may play a role as most of the customers leaving utilized fiber optics.
Assess the competition; most churned customers stated that they were leaving due to better devices or better offers from competitors.
1. Reassess your offers. Most customers had not accepted an offer over the given time period. The churned customers who did accept an offer recently mostly fell into Offer E but soon left with the most cited reason as being a competitor.
Consider trialing special offers for customers who choose a one- or two-year contract. Of churned customers, 1655 of them (89%) were on month-to-month contracts.
1. Look at customer service within the first months, as almost 23% of customers who churned within the first year actually churned within the first month.
Consider incentivizing referrals. The majority of customers (54%) have not given any referrals. Within churned customers, 507 had done one referral, while 579 of current customers had also done one referral. Were churned customers expecting something that they didn’t get and then choose to not refer more?