Power BI for Better Data Visualization: Chart Types, Customer Retention Rates, and Drawing Conclusions
“Through quantity, you’ll come to create quality “
I just wanted to share how I use Power BI to create a good data visualizations. This is my first time using power BI and creating a dashboard (previously I used Tableau and Python) and I wanted to make an article about it.
Power BI is a tools provided by Microsoft to make data more insightful, meaningful, and brightful or many people called it as data visualization. In modern technology era, data has become a necessity for every company even in every second there will be more and more data production, so that every decision-making needs to be data-based, therefore data processing requires a lot of involvement including people and tools in it. In addition, a data practitioner believes that someone who has expertise in data processing will get a job in any position easily, not only in engineering division.
Many tools provide an ease tools for users in processing data, such as Excel, Tableau, R, Python, Google Analytics, Google Data Studio and there are many platforms competing to create their best tools. Here I want to share a sample data I got. Things I prepare to process data and design insights:
1. Data Cleaning and Transformation
This stage spend more than 50% of the visualization process. Things that usually happen are useless data variables, duplicate data, outlier data, inappropriate data formats, and missing values. We also have to look at data distribution and data correlation between the variable so that the data is ready to be processed.
2. Data Visualization
In starting the visualization, I usually choose a chart type, and determine what visuals can be obtained by making a list of the needed variables and the added variables. Re-filter the data column to be used, perform general data aggregates (such as count, distinct count, min, max, mean, mode), logical if functions, pivot functions, and other necessary functions. On the other hand, I usually create new measures and new columns to create the calculation column I want to use or usually called as quick query, to calculate 2 or more columns from 1 or more different tables by using Power BI because the queries it offers are quite flexible and efficient with tools that look similar to Excel. This section I got design below:
The chart type that I used:
- Multi Row chart:
This is one of the chart shape I love, because the data is displayed directly at the required number of points. Pretty easy to understand, right? This chart only need 1 field so it will automatically calculate the variable data we need.
- Gauge and Slicer Chart
Gauge is made to measure a certain dimensional parameter. However, I thought about using it to calculate the average number (5.86) of users using the app from the maximum number (159) of users. In addition, I added a slicer as a time marker that we can move according to the time range we want to know. I combined these two charts because they look suitable for the appearance of the charts, it will add visuals to be more attractive and pleasing to the eye, right?
- Card and Table Chart
Next, I use Card chart to count the number of each service category that canceled the order and that successfully completed the order (you also can make adjustment in Filter Pane to collapse or expand functional data to determine how reader see the report). With a table of calculation of total payments, both cancelled and completed. This visual makes me quite proud because it combines different types of graphs to provide information that is easy to understand at a glance when the reader sees it.
- Multi Row Chart and Area Chart
I thought Multi Row is a popular chart that I often used, which will show the top days and the top services (you also can make adjustment in Filter Pane). It will automatically show the values when there is a change to the Data Control Panel. Besides that, the Area chart shows the total value of the service order price in daily movement. The gap seems too far where Oli services seem many times more than other transactions. That’s why we need Control panel.
- Slicer Chart
So I created a Data Control Panel to show something more specific so that overlaps in the previous Area Chart can be explained more clearly using this control panel. In contrast to Date Slicers in the title Gauge and Slicer before, for categorical data, the slicer options consist only of Lists or Dropdowns. The data control panel I’m currently using is in the form of a List.
- Matrix Chart
The retention rate method is a must visual to show in the dashboard report for every service includes customer transactions. Customer retention is to show repeat buyers and prevent them from switching to competitors. I used a monthly parameter, it means the percentage of customers who come the following month with the same customer. First of all, we need the user_id or Unique Key variables of the customer. To find this out I used 3 new variables that I calculated, there are new column Value variables to create a matrix in a year (12 months), new column First Order in Month variables to calculate the same customer transactions in next months, and new measure Customer Retention Rate variables to calculate the percentage of each customer that has been defined in before. Column values can be inserted in columns, First order in Month can be inserted in rows, and Value can be inserted into values.
For this formula I tried to use a calculation like this:
Value:
First Order in Month:
Customer Rate %:
The thing I usually get an error is when I want to create a new variable. Power BI provides New Measure and New Column that you can use for new Variables you want to create. There are several things that distinguish the two so when you want to create a new variable make sure whether it goes to Column or Measure.
3. Conclusion
After the dashboard report is done, let’s draw a conclusion. In making conclusions, make sure we know the audience, to which division we will present the information so that the conclusions can be understood by stakeholders.
From my current case, the conclusion I will draw are in term of the digital marketing division and focus on Know Your Customer and for growth on increasing customer transactions:
The most users after the first used came in July and August, but continued to decline in the following month.
Users who have used the application since they registered is 21%, and 78% of users have not even made a transaction since the first registered app.
User activity to use application services after registering and joining the application is on average 5 days, with a minimum of service usage on the same day after creating the account, and a maximum of 159 days after application registration or about 5 months after joining customer.
The more concern is when user orders the service, the total price for the cancelled user is more than the total number of users who have successfully and finished using the service. This can be of more concern to find out further whether it is an internal factor of the application or external both the partnership and or the users.
Of the several category services offered in the application, 4 of them are services that are in great demand.
— The 4 categories are Ban with the fourth rank with a total price of 3 million successfully ordered and Friday is the highest day for Ban service users.
— Tune up is ranked third with a total price of 4 million successfully ordered and Thursday is the highest day for Tuneup service users.
— Cuci with the second rank with a total price of 61 million successfully ordered and Saturday is the highest day for Cuci service users.
— Oli with the first rank with a total price of 94 million successfully ordered and Friday is the highest day for Oli service users.
From the movement and behavior of these customers, the digital marketing team can develop applications and services came out from marketing perspective.
Ah one more thing, I used the template from here.
Thanks and hope it can help you to create a better visual dashboard. You also can contact me.
