Managing money through MPESA creates a surprisingly detailed financial record. Every payment, transfer, withdrawal, deposit, and transaction fee leaves behind a transaction trail.
But having the data is one thing. Understanding where the money goes, where it comes from, and how those patterns change over time is another.
Project Objectives
I wanted the analysis to answer four main questions:
- Who are the people I send money to and receive money from most frequently?
- Where does most of my money go?
- How much money comes into and leaves my MPESA account each month?
- How does the proportion of money coming in versus going out change from year to year?
These questions required more than simply summing the Paid In and Withdrawn columns. So let's dive into the 'simplicity' of the project.
Getting this data is fairly easy; you tell Safaricom to give you the data in the range of time you wanted (e.g., 1 month, 3 months, a year). I chose to go for 2.5years, and I ended up with close to 6500 rows of data to work with.
Profiling the Data
The MPESA statement is actually clean because you never get duplicate transactions. That was one win so far. However, I still needed to profile and know what I was looking into and how to categorise later.
SELECT
receipt_no,
TRIM(details) AS details,
UPPER(TRIM(transaction_status)) AS transaction_status,
paid_in::numeric AS paid_in,
withdrawn::numeric AS withdrawn,
CASE
WHEN paid_in > 0 THEN 'IN'
WHEN withdrawn > 0 THEN 'OUT'
ELSE 'UNKNOWN' -- you never get unknown bytheway
END AS direction,
CASE
WHEN paid_in > 0 THEN paid_in
WHEN withdrawn > 0 THEN withdrawn
ELSE 0
END AS amount
FROM mpesa.mpesa_dirty;
The important principle here was not to overwrite the raw data. If a transformation turned out to be wrong, I could always go back to the original dataset.
Creating Transaction Categories
The details column turned out to be the most useful field in the statement. Rather than manually labelling thousands of transactions, I used patterns in the descriptions to classify them into categories such as Pochi, Paybill, Merchant Payment, P2P Transfer, Data/Bundles, Airtime, Fuliza Borrowing, Fuliza Repayment, Transaction Fee, Cash Withdrawal, Business Income, and Funds Received.
CASE
WHEN details ILIKE '%Micro SME Business%'
OR details ILIKE '%Small Business%' THEN 'Small Business / Pochi'
WHEN details ILIKE '%Pay Bill%' THEN 'Paybill'
WHEN details ILIKE '%Merchant Payment%' THEN 'Merchant Payment'
WHEN details ILIKE '%Transfer%' THEN 'P2P Transfer'
WHEN details ILIKE '%Fuliza%'
AND details ILIKE '%Repayment%' THEN 'Fuliza Repayment'
WHEN details ILIKE '%Fuliza%' THEN 'Fuliza Borrowing'
WHEN details ILIKE '%Airtime%' THEN 'Airtime'
WHEN details ILIKE '%Data%'
OR details ILIKE '%Bundle%' THEN 'Data / Bundles'
ELSE 'Other'
END AS transaction_category
This is probably the most interesting part of the project because you have to really understand your data. For example, Micro SME Business transactions were grouped under Pochi because, in this context, they were the same thing.
Analysis
After the cleaning, standardisation, classification and validation, we get to the analysis. I needed to answer my questions and we shall do it together.
Top 5 persons who sent me money
SELECT
counterparty_name,
COUNT(*) AS transaction_count,
SUM(amount) AS total_received
FROM mpesa.mpesa_clean
WHERE
direction = 'IN'
AND counterparty_name IS NOT NULL
AND TRIM(counterparty_name) <> ''
GROUP BY counterparty_name
ORDER BY total_received DESC
LIMIT 5;
I did a bank-to-mobile transfer that totalled 212k, followed by Jane Kanyi, who sent me 85k.
Top 5 people I send money to
SELECT
counterparty_name,
COUNT(*) AS transaction_count,
SUM(amount) AS total_sent
FROM mpesa.mpesa_clean
WHERE
direction = 'OUT'
AND counterparty_name IS NOT NULL
AND TRIM(counterparty_name) <> ''
GROUP BY
counterparty_name
ORDER BY total_sent DESC
LIMIT 5;
Sorry guys, apparently I don't send money using mobile money
Expenditure breakdown
This is a breakdown of where the money going out went.
SELECT
transaction_category,
COUNT(*) AS transaction_count,
SUM(amount) AS total_spent,
ROUND(100.0 * SUM(amount) / NULLIF(SUM(SUM(amount)) OVER (), 0),
2) AS percentage_of_expenditure
FROM mpesa.mpesa_clean
WHERE direction = 'OUT'
GROUP BY transaction_category
ORDER BY total_spent DESC;
My transactions are heavily on paybill, with 46% of the transactions. To be noted also, my biggest category by number of transactions is to Pochi wallets.
Monthly money in vs money out
SELECT
month_name || '' || transaction_year AS month,
SUM(
CASE
WHEN direction = 'IN' THEN amount
ELSE 0
END
) AS total_paid_in,
SUM(
CASE
WHEN direction = 'OUT' THEN amount
ELSE 0
END
) AS total_paid_out
FROM mpesa.mpesa_clean
GROUP BY transaction_year, transaction_month, month_name
ORDER BY transaction_year, transaction_month;
This one is just something else, how am I spending 70k on one month and 9k on the next.
Yearly money in vs money out
Shows the total money coming into and leaving the account for each year.
SELECT
transaction_year,
SUM(
CASE
WHEN direction = 'IN' THEN amount
ELSE 0
END
) AS total_paid_in,
SUM(
CASE
WHEN direction = 'OUT' THEN amount
ELSE 0
END
) AS total_paid_out
FROM mpesa.mpesa_clean
GROUP BY transaction_year
ORDER BY transaction_year;
I learnt that I am averaging 300k usage per year on MPESA.
The insights are not limited to what we have above. You can go crazy with your data. You might notice your terrible money behaviours along the way.
If you do try this project, please share so I can also learn from you. I promise to add visuals; I am getting around Google Data Studio.
Meanwhile, I am going to bask in this growth. Happy coding, folks. As always, I am rooting for you. Here’s to growth.
The project was inspired by Grace Musungu, follow her on TikTok too. She has amazing content
Top comments (1)
Thanks for the analysis. I was anticipating you could have built us a power BI dashboard or basic frontend (since you are a full stack developer ) to render the data. The sql window with results was also good but you only gave us a sneak peak of what you were doing. The queries are well formatted and broken down. Keep up with the good work.