M-pesa Statement Analysis Project

This is the second time attempting this project. Okay, let’s not beat around the bush. The first attempt? Well, let’s just say it was more of a “learning experience” than a roaring success. Who am I kidding? It was an epic fail! But hey, in the world of data, failure is just another step toward greatness, right? 😅

In my first attempt, I used Excel and Tableau. I picked those tools (or should I say stack lol. No?) since I normally use Python, SQL, and a visualization tool. I was challenging myself, or setting myself. I don’t know. I made a dashboard.

Well, let’s just say it was nowhere near the mind-blowing, eye-popping dashboards I drool over online. My mental picture collided head-on with the reality of my creation. Brace yourselves, folks, here’s the masterpiece from my first attempt:

I am not going to lie, I was high-key very proud of myself haha. I even went ahead and wrote a whole blog post about it, only to take one look at my creation and feel a sudden urge to hide it away forever.

Months later I came back to the same dataset and did the work all over again. This time I went to Canva and designed a rough idea of what I wanted my dashboard to look like. I had Ptsd (Post-traumatic styling disorder). I was on a mission to get it right without a template. Here is what I came up with.

I successfully moved from the first dashboard to this! I was even more proud of myself.

I know right? thanks. I had attained data accuracy, the numbers were matching. Here is how I went about it.

Process

For context, M-PESA is a mobile phone-based money transfer service, payments, and micro-financing service. You can request your statement to see your cash flow. It comes in a PDF so I had to get a third-party app to convert it to an Excel file.

I used Google Sheets(Excel) and Looker Studio previously known as Data Studio for this project. Which is totally free.

Although Safaricom gives a breakdown of its own, it is not visualized and it is general. I wanted to get to the specifics. This is the breakdown they give plus your dataset.

Converting the PDF to an Excel file

The file comes in PDF format. Once I received the file, I removed the password and converted it to an Excel file to start the analysis. You will notice that there are watermarks that you can remove too.

Merging the different sheets

Once I was past this, I uploaded the dataset to Google Sheets. I noticed the data was in different sheets and I needed to merge all sheets into one for analysis. Here is the formula that I used.

Type the following formula:
=QUERY({Sheet1!A1:Z; Sheet2!A1:Z}, "SELECT * WHERE Col1 IS NOT NULL")

Replace "Sheet1" and "Sheet2" with the names of the sheets you want to merge, and "A1:Z" with the range of cells that contains the data you want to merge.


Then press enter

After cleaning the data, I saved the file on my computer and uploaded it to Tableau for visualization.

Here are the insights I was after

1. Top 5 people sending money

This was the main reason why this project kicked off. Here I was filtering by the Top 5 sources of income. The initials and name have been used for the purpose of concealing. However, the recipient got the dataset with full names.

2. Expenditure breakdown

I went further to understand where the money was being used. If you are using Looker Studio, create a filter, If you are using Tableau calculated fields are your best friend.

3. Monthly Paid in trend

Here I was getting the monthly distribution of money received (paid in)

4. Monthly withdrawing trend

I was getting the monthly distribution of money withdrawn-leaving the account. I wanted to see the month with the highest number and the least.

5. Percentage of money coming in and money leaving the account for both years

This was to offer a comparison between 2022 and 2023

Definitely, the insights are not limited to just what is stated above. You can go on and on, but know when to stop querying your data.

I am proud of both dashboards. They both played an important role. One got me started and the other one has shown me what happens if you keep showing up. If you think this is my best work yet then you got another think coming haha. I am now building interactive dashboards, which I will share in days to come.

This is to remind you that every expert was once a beginner and you might not like what you are seeing at the moment, but as long as you don’t quit, you will get where you want to be. And appreciate your growth.

If you do try this project, please share so I can also learn from you.

Meanwhile, I am going to bask in this growth. And also attach that dashboard one more time. Just in case you forgot what it looks like haha. I’m currently googling ‘how to set a dashboard as an alarm’

Happy coding folks. As always, I am rooting for you. Here’s to growth.

And scene!