Analyzing WhatsApp Group “Squad Tenaga” Chats with Power BI

A few days ago I just read an interesting article published by Egemolu Goodnews which explains how he analyzed the messages sent in his Whatsapp group application. From the article, I was finally interested in doing the same thing, namely analyzing the messages I got with my group named “Squad Tenaga”.

For the Egemolu Goodnews article that discusses this first, you can check here : https://medium.com/microsoft-power-bi/analyzing-whatsapp-group-chats-with-excel-and-power-bi-a1f8149f3093

Straight to the point, I will do an analysis using Power BI as a helper software here where I will analyze how many messages come in, how many people are involved in group messages, who sends the most messages, what words are sent the most and what month is the month most message activity occurs. For that we will enter the explanation stage.

• Collecting Data

At this stage you can retrieve any message data that has been sent in the group using the data export feature in the WhatsApp application. The trick is that you click the three dots on your group chat then select other and export chat. Then whatsapp will create the data and send it as you wish it can be to your friend’s whatsapp, email and others.

The file you get will be a txt file and contains all the contents of the messages that have occurred in the group.

This is the appearance or content of the txt file that you will get. You can see that the data obtained is still very messy and therefore we have to do data cleaning.

• Data Cleaning

In this process, I will do data cleaning and data formatting. It can be said that Egemolu Goodnews is right that this is a very long and tiring stage because the data we get is not structured at all and there are also many symbols used so it is a challenge to separate each data.

The first thing I did was to replace some symbols so that later they can be used as delimiters to separate the words into new columns. You can do it directly using notepad by using CRTL + H to find and replace. In this txt file, the visible delimiters are the symbols (-) and (:) so I will replace them with (;) so that the data that will be generated will change like :

16/10/23 20.23 — Dody 1KA25: ada yang pake indihome?

To

16/10/23 20.23 ; Dody 1KA25; ada yang pake indihome?

• Importing Data Into Power BI

In Power BI, we can utilize the Power Query feature to transform data, so I will first import data from the txt file that we have cleaned.

First, we get the data using text/csv

Then we set the delimiter to semicolon (;). It can be seen that the data that was originally 1 sentence can now be divided into 3 columns which I will later change into “Waktu Pesan”, “Nama Pengirim”, “Isi Pesan”. Keep in mind that even though we have done the cleaning at the beginning, there are still some data that goes into the wrong column that we shouldn’t process further.

After the data is uploaded, I will directly process it in the power query. Don’t forget to give each column a name first. Here I will process any data that goes into the column according to the column category. For the “Waktu Pesan” column, I will filter using Contains /. This means that only data that has the character “/” is shown. For the “Sender Name” and “Message Content” columns, the data entered is safe and correct (If there is empty data you can just delete it).

Next, I will split the column using demilitary space, later the Message Time column will be divided into 2. The purpose is to separate the Date and Time (Don’t forget to change the format to Date for the date and Time for the Time).

Because the time from the data I have has a different format, I will follow the English time format that uses (:). Therefore, I do replace values by replacing (.) with (:)

Next, I will duplicate the Whatsapp Chat table and rename it with “Word” then I delete all columns except the “Isi Pesan” column, I do this because I want to know what words are most used in my whatsapp group.

Once that’s done, we can go back to power bi to make some minor changes, namely creating a new column containing “Date, Month, and Year”.

Here I created some new columns by using DAX to find Day, Month and Year.

The end result will be like this, if you want to do the same thing below is the Dax code.

Day = day([Tanggal])

Month = month([Tanggal]) 

MonthName = FORMAT('Chat WhatsApp dengan Squad Tenaga (1)'[Month],"MMMM")

Year = year('Chat WhatsApp dengan Squad Tenaga (1)'[Tanggal])         

You can change ‘Chat Whatsapp dengan Squad Tenaga (1)’ with your table name that you use.

After all is successfully run, you can design and customize the dashboard display as you want and what charts you need to get conclusions.

Final Dashboard

Conclusion

from the project that I have done, several conclusions can be drawn including:

• The total active members in the power squad group are 16 people - Total messages in the group in the period October 2023 - February 2024 are 2824 messages.
• The most frequently used word is “ada”.
• The most active month in the group is the month of February 2024 with a total of 1328 messages.
• The most active member in the group is Fauzan with a total of 1524 messages.

Insights

Through this analysis, I was able to find out a lot of new insights or new information such as what month the power squad group sent the most messages to each other, which members sent the most messages, what words were used the most in the group.

This project that I did is just a fun project and also because of the inspiration from the article on this medium website. To fill or train this analysis skill is a very exciting project because I can share the results of this analysis to my group and everyone in my group can know this new information too.

Thank you for reading my project. Allow me to introduce myself. My name is Althea Daffa Putra Purwantoro and I graduated from Gunadarma University in 2023 with a major in Information Systems. I have an interest in the field of data, especially data analysts and this is my first project in analyzing data. It is not free from mistakes, of course the projects I have worked on must have errors or shortcomings, I am happy if there is input for me on the projects I have worked on. If you wish, you can write down what parts I have to develop better and what parts are wrong or need improvement.