Job #2 Customer analysis – How can we target right

Analytics with dashboards

The story begins

The customer-centric journey continues with another business challenge – the customer retention. After hearing my analysis for the call center, the Retention Manager, perhaps a bit impressed, called a meeting with his engagement partner at PwC to ask for the support. The meeting minutes contain his concerns below. As a business intelligence analyst, I need to explore the issues with customers and suggest a better approach for the team and the company to better drive their profitability in the near future:

This story reminds me of an experience years ago, when I worked as a Finance Business Partner in an insurance company. They have a similar problem with Excel reports: very challenging to look at the patterns or any insights. Even worse, the report completion time is 2 months, which delayed their reaction time to any changes in the customer trend. Therefore, I created a Power BI dashboard that helps them manage the persistency ratio of the customer (The ratio of customers, in his/her Xth year of policy (contract) tenure, to continue the policy with the company in this current year). It seems a confusing idea, but I made it at the end and shorten the reporting time from 2 months to just 2 weeks.

Let’s look at the given file to see where I can start!

Using Power Query, I (transform) load the Excel file to see the dataset’s characteristics. This dataset is highly structured and standardized, but there are rooms for further exploration to have the analysis efficient

1. Each row represents a unique customer, with the Churn (Leave) decision (Yes/No)
2. There are columns with binary values (Yes / No). The column names suggest that those are services that the customers have already subscribed to or not during their time with this telecom company
3. There are columns with categorical values (for instance, different type of Bank transfer – Payment, Credit card, or others)
4. There are quantitative variables, including monthly charge, total historical charges, contract tenure, number of technical tickets sent

With the dataset characteristics, I think of how to manage the story by dividing it into different parts!

Analysis journey – how I utilize what the dataset possesses

The most important part of the requirement is how the team can proactively support the customer and reduce the termination (or churn) rate. Therefore, the dashboard, in my opinion, must empower the retention team to have their best ever experiment in finding the targeted segments, who are more likely to cancel their company’s services. Imagine the team is standing in the controlling chamber of the USS Enterprise in Star Trek, equipped with a lot of dashboards and reports.

So, have a deeper look at what I am given in the dataset. There are so many categorical variables, especially in the services subscription column (with two status Yes/No). Again, I envisioned them as the button Yes/No for each category, and I can try to modify my focused segment by choosing the options as filters.

One of the noticeable values among the category is “Internet Service”. The reason is, besides Yes/No answer, there is an option “No Internet Service”. My instinct said that there might be differences between the customers who choose not to subscribe to an add-on service and those who initially choose not to have the internet service with the company.

Coming to the quantitative variables, I was thinking about different ideas. Even if it is a discrete value (let’s say, the numbers of tickets sent by a customer), or a continuous value (monthly charged fee with decimal number), it is neither comfortable nor natural to any person to click every single value and figure out the focused segment. Perhaps other techniques, such as correlation analysis or values by range, should make more sense.

However, the most critical metric in this analysis is the Churn ratio, which is how the team can measure the churn probability of every customer profile. Although it is easy to manage using a ratio, managing the metrics through different filter selection requires an extra step to ensure it works. Let’s see how I can set up the measure through Power BI.

Prepare for the quantitative variables.

The churning ratio and its visualization, which I need, has to contain several characteristics:

1. Must align with any filters selected by the users
2. Form as a percentage ratio
3. Position in a card visualization or any chart that can change color, or any type of formatting that can be compared to a benchmark.

The first instinct is to create a ratio that counts every customer terminating their contract/or not terminating, over the total number of customers. However, the idea creates a static measure, which is tightly knitted to the condition inside the DAX syntax and not to the visualization filter.

% Churn_alldata = 
DIVIDE(
    COUNTROWS(
        FILTER(
            '01 Churn-Dataset',
            '01 Churn-Dataset'[Churn] = "Yes"
        )
    ),
    DISTINCTCOUNT(
        '01 Churn-Dataset'[customerID]
        ),
    "0"
)

Thus, this should be used as the benchmark showcasing the average of the churn ratio among the dataset. Any customer segment with a higher ratio than the average will have a higher possibility of terminating their contract soon.

So, after trying many techniques, I was inspired by the idea of using CTE (Common Table Expressions) from SQL to calculate this metric. The idea is to create a table with a set condition (containing all the customers who terminated their contracts – “Yes” in the churn column). By connecting it with the original table, it will be influenced by the filter of any categorical and numeric variables, and makes the churn metric dynamic. If you are interested in the original idea of CTE in SQL, please take a look here.

Below is the DAX I used to create this table. I used SUMMARIZECOLUMNS (similar to the GROUP BY function of SQL), not to forget the condition “Yes” to select only the customer who terminated the contract.

Churn_table = 
SUMMARIZECOLUMNS(
    '01 Churn-Dataset'[customerID],
    '01 Churn-Dataset'[Churn],
    FILTER(
        ALL('01 Churn-Dataset'),
        '01 Churn-Dataset'[Churn] = "Yes")
)

Then I applied a usual DIVIDE and COUNT syntax to calculate the dynamic churn ratio. Be aware that the “churn” customer I count is in a different table from the total number of customers.

% Churn = 
DIVIDE(
    COUNT(
        'Churn_table'[customerID]),
    DISTINCTCOUNTNOBLANK('01 Churn-Dataset'[customerID]),
    "0"
)

The charges are the continuous values: they contain real decimal numbers. The options for choosing each value are unlimited. Therefore, a better idea must be to visualize the fee with the churn possibility. Furthermore, it must be actionable: there will be a customer with a very high charge fee, and the others fall into the low fee range, perhaps. I came up with the idea to transform them into a range of charge fees. But how can I use tools to label the price within each static range?

It is called “Static Segmentation”. You can learn more from it here.

Step 1: Create the table with the defined range for the needed values. We have the monthly charge and the total charges to transform. There are many ways to define the range, including based on the variable’s max and min values, or the range’s frequency (like using the histogram chart). To simplify this case, I divided those values into segments based on the min and max ones.

Step 2: Create the foreign key in the original table to connect with the table of defined range. I break through small steps.

1. When scanning each charge record in the column Monthly Charges/Total Charges, it needs to be compared with the Min Charge/Max Charge column in the new range table.
2. The charge must be >= Min charge and <= Max charge.
3. Output the final list of labels, where the filter from the range in step 2 will choose the only suitable title for that range value. Be careful with the ‘=’ to avoid confusion when labelling a particular charge.

MonthlyRangeKey = 
VAR CURRENT_MONTHLY = '01 Churn-Dataset'[MonthlyCharges]#An alias name for charge variable 

#Create the filter comparing the scanned value of charge to the range in the new range table 
VAR FILTERSEGMENT =
    FILTER(
        'MonthlyChargeRange',
        AND(
            MonthlyChargeRange[Min Charge] < CURRENT_MONTHLY,
            MonthlyChargeRange[Max Charge] >= CURRENT_MONTHLY
        )
    )
#Output is a list where there is only one label, filtered by the condition in step 2
VAR RESULT =
    CALCULATE(
        DISTINCT('MonthlyChargeRange'[Range Key]),
        FILTERSEGMENT
    )
RETURN
    RESULT

Finally, we can connect the range tables to the original fact table. Now I can use the label in the range table to filter the charge value of each customer falling into the selected range.

So we are ready for the dashboard now. Let’s build each dashboard for the qualitative and quantitative variables.

Build analytic dashboards

With the idea about a controlling chamber for the team to manage the churn analysis through their customer, I created a dashboard that can be used for many times and can be adjusted easily in the future. I made a filter tableau, including different categorical values.

Let’s say how we can utilize this tableau. We try every filter to see how the selected option increases or decreases the churning ratio compared to the benchmark, which is the churn ratio of the whole dataset.

Of course, we can always try with many filters, even combining the services and the demographic to see which segments have the higher rate of churn compared to the whole population’s ratio, like this one above

With discrete values, such as numbers of tech tickets sent and the contract tenure, we can apply two different approaches: correlation analysis to recognize the trend, and churn ratio by each value to see where the significant point is to focus on the right customers

After approaching the money value by dividing into different ranges, we can have a bigger picture about the fee charged and the churn ratio. The monthly charge is a good way to start, before the team can look closely at the price of each service, to see if the current listing prices are making sense to the customers

Let’s have an overall view for the quantitative dashboard

Conclusion

Now, the team has an easy yet powerful tool to segment the customers who need their most concentrated effort to avoid contract termination. As a business intelligence analyst, I emailed them to summarize the findings with several recommendations, hoping the team can utilize these actionable insights to help them efficiently drive the customer retention ratio.

Hi Anna, Call Centre Manager,

To the related responsible people,

After carefully examining the customer data, I found some interesting insights that need proactive actions:

For categorical information, such as Service-related (Contract type, Internet Service, and other service subscription in Yes/No), and Demographic (Dependants, Senior, Partners), by selecting the decisions through filters in the dashboards, we can see that:

1. Customers with month-to-month contracts and/or fiber optic service would have a double churn rate compared to the average churn rate in the data set. ===> We should convince Month-to-Month customers to subscribe to an annual package (reducing over 75% possibility of churning) 
2. Except for some services such as Paperless Bill and Streaming movies, customers who had as many as services with us would have a lower churn rate. ===> Therefore, we should invest resources to convince customers to enjoy different mini-services besides our main products. 
3. For payment (payment method and paperless bill), customers with electronic checks and who choose to have online bills have a high churn rate. ===> Thus, we should either (1) examine the online payment process to avoid any barriers customers may encounter that dissatisfy them and (2) have a marketing campaign to educate customers about the advantages of subscribing to many of our mini-services to reduce the churn rate of this particular customer segment. 
4. For Demographic attributes, customers with a lonely life (without dependants, without a partner) and seniors had a high churn rate (approx. 50%). ===> Thus, when acquiring new customers, we should segment this type of customer to have special treatment during their time with our products (frequent service calls, frequent maintenance programs, loyalty promotion programs), or sometimes, just a good morning call will brighten their day. 

For quantitative information, some notes are worth your notice:

1. Customers with at least one tech ticket had a skyrocket chance of churning (from 20% to over 60%). This means there is a change in customers’ behavior to new providers if they are annoyed with technical problems. Thus, we must ensure that our maintenance & repair services improve their performances or upgrade the technical aspects of our products. 
2. The more significant the customer tenure, the lower the churn rate. The breakpoint for the churn rate that is lower than the average churn rate is 18. This supports the idea that we should convince our customers to have a more long-term package with us. 
3. The monthly charge and total charge (by range) seem opposite, while they demonstrate different ideas. The total charge can be understood in terms of how long they subscribe to our services. The monthly charge can be understood from the financial aspect of our customer perspective. High and Very high range of monthly charges (from $60 and above) will have a higher risk of leading to churning decisions. Therefore, we should have a unique customer program, such as premium customers with high monthly charges, to satisfy their concerns and encourage them to be more loyal to us. 

In short, 

1. Convince customers to have annual or 2-year contract subscriptions with us.
2. Convince customers to have more mini-services with us.
3. Track and improve our maintenance & repair services
4. Have unique treatments for specific segments (seniors with lonely lives, premium customers with high monthly charges). 

If you have any concerns, please ask me; I would love to explain more.

Best wishes, 

Nguyen.