How to Churn Analysis in Microsoft Power BI

Churn analysis is an important aspect of customer relationship management that every organization must pay attention to. It helps to identify and understand why customers stop doing business with a company, which in turn enables organizations to take the necessary steps to improve customer retention.

Churn analysis in Power BI typically involves analyzing customer data such as transaction history, frequency of purchases, and communication with the business. By analyzing this data, businesses can identify patterns and trends that can provide valuable insights into customer behaviour.

For example, Power BI can be used to analyze the purchasing habits of customers who have stopped doing business with a company. By examining factors such as the frequency of purchases and the types of products purchased, businesses can identify patterns and trends that can help them understand why these customers churned.

Another area where Power BI can be useful is in analyzing customer feedback. By examining customer feedback data, businesses can identify areas of weakness in their products or services that may have contributed to customer churn. This can help businesses take the necessary steps to address these issues and improve customer satisfaction.

Power BI can also be used to analyze customer engagement. By analyzing data on how customers interact with the business, such as website visits, social media interactions, and email communications, businesses can identify areas where they can improve engagement and prevent churn.

How then do we do this in PowerBI?

Let’s go into PowerBI. The model used is the model you are familiar with

First, we need to have our base measures set.

1. Total Sales

2. Total Customers

Now, for our churn measures. Let’s think of the logic together. Say you are managing a business and you need to identify customers that are no longer buying from you. What would you do? How would you go about it manually? Think about this before moving downwards.

Well, your guess is as good as mine. To do this manually, you will need to follow the process as thus:

1. Get a list of all customers that have purchased from you

2. Get a list of customers that have purchased from you recently. Recently is relative i.e. it could be a month, two months, three months etc.

3. Compare both lists to identify who is on number 1 and not on number 2

Similar logic is followed for the DAX measure. The Churned/Lost customers can be calculated with the DAX below

The Customers_last_365_days variable creates a virtual table containing a list of customers who purchased a product between 365 days ago to 60 days ago.

The Customers_last_60_day variable creates a virtual table containing a list of customers who purchased a product between 60 days ago to the current day context provided by the calendar table.

The COUNTROWS function counts the number of customers who made purchases in the previous period (Customers_last_365_days) but did not make any purchases in the current period (Customers_last_60_day).

If we are losing customers, what that implies is that we are losing potential revenue. The DAX below calculates the potential revenue we are losing out on.

Note that the revenue doesn’t necessarily mean that the exact amount will be made if the customer is not churned. It is best interpreted as you are losing a certain percentage of that lost revenue with the customer being churned. For example, If I say Nigeria is a $500 billion economy. It doesn’t mean every company will generate that amount as revenue but companies operating in Nigeria will generate a certain percentage as revenue. That percentage could be as low as 0.000000000000001% and as high as it could get.

Now that we have gotten that out of the way. Identifying lost customers is not just sufficient. My Former Boss (Joshua) used to say something. So what? We’ve lost 300 customers, so what? What do we do with that? How does it help us take the next step?

The so what can be gotten from understanding what those lost customers bought, where they brought from, their feedback on both the product and service rendered to them etc?

Let’s take a quick walkthrough of this together

Overall, churn analysis in Power BI can provide organizations with valuable insights that can help them improve customer retention and increase revenue. By identifying patterns and trends in customer behaviour, organizations can take proactive steps to address issues and improve the overall customer experience. If you’re looking to improve customer retention in your business, churn analysis in Power BI is worth exploring