Analysing customer churn

ShobhitAnalytic — Tue, 28 Feb 2023 12:24:32 GMT

Business Request:
A telecom service provider requests an analysis of why customers are churning, or in other words, cancelling their mobile subscriptions.

The offered solution provides a Power BI report with:

An overview page which summarizes the critical churn statistics and churn reasons.
A detailed report providing a deeper analysis of churn across customer demographics and segments

Errors and data transformation:

A review of the dataset reveals data entry errors in demographic columns classifying customers as “Under 30” or “Senior”. 4.144 out of 6.687 customers have been left unclassified.

Customer is offered a solution which corrects this error and provides a more granular demographic classification. Customers are classified as “Youth” if age is less than 20, as “Young Adult” if age is between 20-30, as “Middle Aged” if age is between 30-60, and “Senior” if age is above 60.

DAX (Data Analysis Expressions) calculations:

The following measure and columns are created using DAX to facilitate the analysis:

SERIAL # DAX DESCRIPTION

1.	Number of Customers = count(‘Databel – Data'[Customer ID])	A count of the number of Customer IDs listed in dataset.
2.	Number of Unique Customers = DISTINCTCOUNT(‘Databel – Data'[Customer ID])	A count of the Unique Customer IDs. This is compared with the previous measure (#2) to check the data for duplicates.
3.	Churned = IF(‘Databel – Data'[Churn Label]=”Yes”,1,0)	IF statement converting the column ‘Churn Label’ from text (yes/no) entries to a binomial column (1/0) to allow calculations.
4.	Number of Churned Customers = sum(‘Databel – Data'[Churned])	Uses previously created column (#3) to calculate total churned customers.
5.	Churn Rate = [Number of Churned Customers]/[Number of Customers]	Calculates churn rate and is formatted as to display a percentage.
6	DemographicClassification = If(‘Databel – Data'[Age]<=20, “Youth”, IF(‘Databel – Data'[Age]<30, “Young Adult”, IF(‘Databel – Data'[Age]<60, “Middle Aged”,”Senior”)))	Nested IF statement to correct error mentioned in the data transformation section and to provide a classification of customers.
7.	Contract Category = SWITCH(‘Databel – Data'[Contract Type], “One Year”, “Yearly”, “Two Year”, “Yearly”, “Monthly”)	SWITCH statement to classify different contract types as yearly or monthly contracts.
8.	Grouped Consumption = IF(‘Databel – Data'[Avg Monthly GB Download]<5,”Less than 5 GB”, IF(‘Databel – Data'[Avg Monthly GB Download]<10,”Between 5 and 10 GB”,”10 or more GB”))	Nested IF statement to classify customers monthly data usage into three categories.

eyJrIjoiMDQ3ODVkMjgtMjkwOC00YjU2LWFhZWEtMDYwNTg2NDNhNDY5IiwidCI6Ijk5N2FhN2QwLTQ1NDYtNDQ4ZS1iNDY0LTk0ZDM0MTc3ODczNyIsImMiOjl9&embedImagePlaceholder=true

topic Analysing customer churn in Data Stories Gallery

Analysing customer churn