Analyze Telco Customer Churn Dataset Using SQL

Customer churn or attrition is a crucial metric in the business world, especially in the telecommunication industry. It means losing customers or subscribers. In other words, it is a loss of monthly subscription fees and potential future purchases. If the churn rate is high, it can lead to financial instability and hinder the company’s growth.

Hence, a telco company needs to identify patterns in the churn dataset to save the company’s marketing costs in acquiring new customers. Churn analysis also helps in understanding customer sentiment and satisfaction levels. If issues like billing problems, network coverage, or customer service are consistently causing customers to leave, improving these areas can lead to higher customer satisfaction and loyalty.

Photo by Dustin Tramel on Unsplash

In this article, I will show you how I use SQL to answer hypothetical questions using the IBM telco customer churn dataset I obtained from Pranjali on GitHub. The dataset contains information about a fictional telco company that provided home phone and Internet services to 7,043 customers in California in Q3. Below is the entity-relationship diagram (ERD) that I created with MIRO.

Before diving into the exciting world of analytics, I performed data cleaning using Excel to get the dataset ready for the analysis adventure. Feel free to download my cleaned dataset from my Google Drive to check out how I used INDEX & MATCH functions to join tables and remove those necessary columns! To begin your analysis using MySQL workbench, you may choose to import this CSV file using Table Data Import Wizard or import this SQL file using SQL Data Export and Import Wizard.⚡

We’ve got more exciting SQL-related hypothetical questions and solutions lined up. Let’s explore the fascinating world of SQL together!🤓

Query 1: Considering the top 5 groups with the highest average monthly charges among churned customers, how can personalized offers be tailored based on age, gender, and contract type to potentially improve customer retention rates?

The query analyzes churned customers in the “telco_churn” dataset, grouping them by age category, contract type, gender, and customer status. It categorizes customers into 'Young Adults' (< 30 years), 'Middle-Aged Adults' (30-49 years), and 'Seniors' (50+ years). The results show the average tenure (in months) and average monthly charge for each group.

Based on the top 5 groups with the highest average monthly charges among churned customers, personalized offers can be tailored as follows to potentially improve customer retention rates:

Middle-Aged Adults (Two-Year Contract, Female):

• Offer exclusive loyalty rewards or discounts on premium services to retain long-term commitment.
• Provide personalized customer service experiences and access to priority support channels.

Middle-Aged Adults (Two-Year Contract, Male):

• Introduce special bundles or family plans that cater to the unique needs of male customers.
• Offer complimentary add-ons, such as streaming services or device upgrades, to enhance their overall experience.

Seniors (One-Year Contract, Female):

• Focus on simplicity and ease of use; provide clear billing and straightforward service packages.
• Offer personalized tutorials or tech assistance to address any concerns about technology usage.

Young Adults (One-Year Contract, Female):

• Emphasize flexible plans and options that adapt to changing needs and lifestyles.
• Provide incentives for referrals, encouraging them to bring in friends or family to the service.

Young Adults (One-Year Contract, Male):

• Offer incentives related to the latest gadgets, devices, or gaming subscriptions, catering to male interests.
• Provide engaging online content or community events to build a sense of belonging and customer loyalty.

These personalized approaches target the specific preferences and needs of each demographic group, aiming to enhance customer satisfaction and increase their likelihood of staying with the service. By understanding their unique demands, the telco can foster stronger customer relationships and improve retention rates effectively.

Query 2: What are the feedback or complaints from those churned customers?

The “Other” category does not convey much information. Let’s replace it with other useful values.

These UPDATE statements are used to clean the data:

• The first one categorizes specific churn reasons into more granular and descriptive categories (“Personal Issue”, “Unknown”, and “Dissatisfaction”). If none of the conditions match, it keeps the existing “Churn Category” value.
• The next two statements replace missing or blank values in the “Churn Reason” and “Churn Category” columns with "NA", ensuring consistency in the dataset and making the data more reliable for analysis.

Expected output for query 2

Upon completion of the process, we identified the top three categories cited by customers for churning:

1. Competitor: It is suggested that it conduct continuous and thorough analyses of competitors’ offerings, pricing structures, and customer feedback. Understanding competitors’ strengths and weaknesses helps businesses emphasize their unique selling points and identify areas where they can gain a competitive edge. It also can implement a customer loyalty rewards program to incentivize long-term relationships.
2. Dissatisfaction: Other than improving service quality, network coverage, and call/data quality, the telco company can conduct regular surveys to gauge customer satisfaction and identify areas for improvement. Provide personalized offers and discounts based on individual usage patterns to make customers feel valued. Engage dissatisfied customers with exclusive deals to win back their trust.
3. Attitude: Provide training to customer service representatives to handle customer issues with empathy and understanding.

By addressing these specific churn reasons, the telco company can tailor their retention strategies to meet the needs of different customer segments, ultimately improving customer satisfaction and loyalty. Regular feedback analysis and proactive communication are key to ensuring long-term customer retention.

Query 3: How does the payment method influence churn behavior?

The code utilizes Common Table Expressions (CTEs) “ChurnData” and “LoyalData” to isolate churned and loyal customers based on “Churn Label”. It then joins these CTEs using an INNER JOIN on “Payment Method”. The query selects “Payment Method”, “Churned”, and “Loyal”, calculates the total by adding churned and loyal counts, and employs SUM() OVER() to create a running total, ordered by “Payment Method”.

Expected output for query 3

This result showed that a significant number of churned customers use bank withdrawal as their payment method. The telco company could investigate if there were issues with bank withdrawals such as delays, errors, or high fees. It needs to address any discrepancies by improving the communication with the bank or exploring alternative banking partners with better services. Besides, it can diversify payment options by introducing convenient methods such as online payment gateways, mobile wallets, or digital banking apps. It can offer incentives or discounts for customers using these preferred methods.

By addressing the specific concerns related to bank withdrawals and providing tailored solutions, the telco company can mitigate churn among customers using this payment method, thereby improving overall customer retention rates.

Hope you enjoy reading this article. Happy learning. 😊

In summary, analyzing the customer churn dataset is essential for the telco company to maintain a thriving business. It provides insights into customer behavior, helps in retaining customers, and ultimately contributes to the company’s long-term success in the competitive telecommunications market.