Customer Transaction Analysis

Build a fintech analytics pipeline from Excel to MySQL to Power BI.

Introduction

30 Second Summary

Every time you tap your phone to pay for coffee or transfer money to a friend, a transaction record is created. Behind the scenes, data analysts use those records to answer critical business questions about customer behaviour and revenue trends.

In this project, you will build an end-to-end analytics pipeline for a fictional fintech company called NovaPay. You'll clean raw transaction data in Excel, load it into a MySQL database, segment customers using SQL, and visualise the results in an interactive Power BI dashboard.

What You'll Build

You will build a two-page Power BI dashboard connected to a live MySQL database, powered by RFM customer segmentation queries and revenue analysis.

By the end of this project, you'll have:

  • A cleaned Excel workbook with formatted transaction data and pivot table summaries showing revenue trends by month and channel.
  • A MySQL database with analytical SQL queries that segment customers using RFM (Recency, Frequency, Monetary) analysis.
  • An interactive Power BI dashboard with KPI cards, trend charts, and customer segment filters connected live to your database.
  • Secret Mission: Add Month-over-Month growth rate calculations with conditional formatting to highlight declining metrics.

Are there any prerequisites?

You need MySQL installed and running locally on your machine, along with basic familiarity with writing MySQL queries (SELECT, CREATE TABLE, INSERT). You should also have Power BI Desktop and Microsoft Excel (2013 or Microsoft 365 Online) installed.

Set Up Your Environment

Power BI Desktop cannot connect to a MySQL database on its own. It needs a driver installed on your machine that translates between the two tools.

In this step, you will install the MySQL Connector/NET driver and verify that Power BI Desktop recognises it. Once confirmed, Power BI will be ready to pull data directly from your local MySQL database.

In this step, get ready to:
  • Install MySQL Connector/NET 9.7.0 using the Windows MSI installer.
  • Verify the connector is registered using PowerShell.
  • Confirm Power BI Desktop can access the MySQL database connector without errors.
Download and install MySQL Connector/NET

MySQL Connector/NET is the driver that registers MySQL as a data provider on your Windows machine. Power BI Desktop looks for this provider when you try to connect to a MySQL database.

  • Open the MySQL Connector/NET download page in your browser:
  • https://dev.mysql.com/downloads/connector/net/
  • In the Select Operating System dropdown, select Microsoft Windows.
  • Under Select OS Version, keep Windows (x86, 32-bit) selected.
  • Click Download next to the MSI Installer row showing version 9.7.0 (file name mysql-connector-net-9.7.0.msi).
  • On the next page, click No thanks, just start my download to skip the Oracle account login.

Once the download finishes, run the installer.

  • Double-click mysql-connector-net-9.7.0.msi in your Downloads folder.
  • Click Next to start the installation.
  • Select Typical as the installation type, then click Next.
  • Click Install to begin copying files.
  • When the installer finishes, click Finish.

What does this installer do?

The MSI registers MySQL Connector/NET in the Windows Global Assembly Cache (GAC). This makes the MySQL Data Provider available to any application on your machine, including Power BI Desktop.

Verify the connector in PowerShell

After installing the connector, you need to confirm that Windows has registered the MySQL Data Provider. A quick PowerShell command shows all registered database providers on your machine.

  • Press the Windows key to open your search bar.
  • Type PowerShell and press Enter to open it.
  • Check that the MySQL Data Provider is registered by running this command:
[System.Data.Common.DbProviderFactories]::GetFactoryClasses()|ogv

What does this command do?

This command queries the .NET framework for all registered database providers and displays them in a grid view window. If the install succeeded, you will see a row called MySQL Data Provider in the list.

✔️ I see MySQL Data Provider in the list

The connector installed correctly. You are ready to move on to the Power BI verification below.

ⓧ I don't see MySQL Data Provider

  • Close PowerShell completely and reopen it. The provider registration may not be visible in a session that was open before installation.
  • Re-run the mysql-connector-net-9.7.0.msi installer and choose Repair if the option appears. This re-registers the provider in the GAC.
  • Make sure you downloaded the correct file. The filename should be exactly mysql-connector-net-9.7.0.msi.

Still stuck? Help me troubleshoot the MySQL Connector/NET installation on Windows

ⓧ I see an error when running the command

If PowerShell shows a red error message, your .NET Framework version may be outdated.

  • Open Windows Update and install any pending updates. The command requires .NET Framework 4.7.2 or later.
  • After updating, restart your computer and try the command again.

Still stuck? Help me fix the PowerShell error when checking for MySQL Data Provider

Confirm Power BI recognises the connector

The final check is to open Power BI Desktop and navigate to the MySQL database connector. If the connector installed correctly, you will see the connection dialog without any error about missing components.

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and press Enter to open it.
  • In the ribbon, click Home.
  • Click Get Data.
  • In the Get Data dialog, select Database from the left panel.
  • Select MySQL database from the list on the right.
  • Click Connect.

✔️ I see the connection dialog without errors

You should see the MySQL database connection dialog asking for a server name and database name. No error message about missing components should appear.

  • Click Cancel to close the dialog. You will connect to your database in a later step.

ⓧ I see an error about missing components

If Power BI shows a message saying additional components are required, the connector may not have registered correctly.

  • Close Power BI Desktop completely. Re-run the mysql-connector-net-9.7.0.msi installer and choose Repair. Then reopen Power BI Desktop and try again.
  • Ensure you did not skip the Typical installation type. Custom installs that skip core components can cause this.
  • Restart your computer to allow Windows to fully register the new provider, then retry.

Still stuck? Help me fix the missing component error in Power BI for MySQL

Your environment is ready. Next up, you will download raw transaction data and prepare it in Excel for analysis.

Prepare Transaction Data in Excel

Your environment is set up and Power BI can talk to MySQL. Now it's time to get your hands on the actual data.

Raw transaction data is rarely ready for analysis. You'll clean NovaPay's transaction CSV in Excel, then create pivot tables to spot early patterns before diving into SQL.

In this step, get ready to:
  • Clean the raw transaction CSV and save it as an Excel workbook.
  • Create pivot tables to explore revenue by channel and monthly trends.
  • Export the cleaned data as a CSV file for MySQL import.
Open and clean the raw transaction CSV

The raw CSV contains every transaction NovaPay processed, including failed and pending ones that would skew your analysis. You need to strip those out and standardise the date format before any calculations make sense.

  • Press the Windows key to open your search bar.
  • Type Excel and press Enter to open it.
  • Click File in the top menu bar.
  • Click Open and navigate to the folder where you downloaded the NovaPay transactions CSV.
  • Select the CSV file and click Open.

You should see columns for transaction_id, customer_id, transaction_date, amount, transaction_type, channel, merchant_category, and status.

Now remove the rows you don't need for analysis.

  • Click the dropdown arrow on the status column header.
  • Uncheck failed and pending, leaving only completed checked.
  • Click OK to apply the filter.
  • Select all visible (filtered) rows by pressing Ctrl+A.
  • Copy them with Ctrl+C.
  • Open a new blank worksheet by clicking the + tab at the bottom of the workbook.
  • Paste with Ctrl+V to get a clean copy with only completed transactions.
  • Delete the original sheet (right-click the tab, select Delete).

Why remove failed and pending transactions?

Failed transactions never moved money. Pending ones haven't settled yet. Including them would inflate your transaction counts and distort revenue figures.

Next, standardise the date column so MySQL can parse it correctly during import.

  • Select the entire transaction_date column by clicking its column letter.
  • Right-click and select Format Cells.
  • Under Category, select Custom.
  • In the Type field, enter YYYY-MM-DD and click OK.
  • Scan the spreadsheet for any blank rows. If you find any, select the entire row, right-click, and choose Delete.
  • Check the amount column for negative values. Remove any rows with negative amounts.
  • Since all remaining rows are completed, you can delete the status column entirely. Right-click its column letter and select Delete.

Now save your cleaned data as an Excel workbook.

  • Click File > Save As.
  • Choose your Desktop or project folder as the location.
  • Set the filename to novapay_transactions_cleaned.
  • Change the Save as type dropdown to Excel Workbook (*.xlsx).
  • Click Save.

Dates still showing the wrong format?

If your dates appear as numbers (like 45678) after formatting, the column may be stored as text. Select the column, go to Data > Text to Columns, choose Delimited, click Finish, then re-apply the custom date format.

help me fix my date formatting in Excel

Create a cross-tabulation pivot table

A Pivot Table lets you summarise thousands of rows into a compact grid. Your first one will show how revenue flows across different transaction types and channels.

  • Click any cell inside your cleaned data.
  • Go to the Insert tab in the ribbon.
  • Click PivotTable.
  • In the dialog, select New Worksheet and click OK.

Excel opens a new sheet with an empty pivot table and a field list panel on the right.

  • Drag transaction_type to the Rows area.
  • Drag channel to the Columns area.
  • Drag amount to the Values area. It should default to Sum of amount.

What does this pivot table show?

Each cell shows the total revenue for one combination of transaction type and channel. You can quickly see which channel carries the most money for each transaction type.

Look for the largest value in the table. Note which transaction type and channel combination generates the most revenue. You'll compare this to your Power BI dashboard later.

Values showing Count instead of Sum?

If the Values area shows Count of amount instead of Sum of amount, click the dropdown arrow on the field in the Values area, select Value Field Settings, and change it to Sum. This happens when Excel detects text values in the amount column.

help me fix my pivot table values

Create a monthly trend pivot table and export cleaned data

Your second pivot table shows how transaction volume and revenue change month to month. This gives you a quick feel for trends before building the full dashboard.

  • Switch back to the sheet containing your cleaned data.
  • Click any cell inside the data.
  • Go to Insert > PivotTable and select New Worksheet.
  • Click OK.
  • Drag transaction_date to the Rows area.
  • Excel may automatically group dates by month. If it doesn't, right-click any date value in the pivot table, select Group, choose Months, and click OK.
  • Drag transaction_id to the Values area. It should show as Count of transaction_id.
  • Drag amount to the Values area. Confirm it shows as Sum of amount.

What does this pivot table reveal?

Each row represents one month. The two value columns let you compare transaction volume (count) against revenue (sum). If volume grows but revenue shrinks, average transaction size is dropping.

Finally, export a CSV copy of your cleaned data. You'll need this file format for importing into MySQL in the next step.

  • Switch back to the sheet containing your cleaned transaction data.
  • Click File > Save As.
  • Set the filename to novapay_transactions_cleaned.
  • Change the Save as type dropdown to CSV (Comma delimited) (*.csv).
  • Click Save. If Excel warns about losing features, click Yes to confirm.
  • Save the workbook again as novapay_transactions_cleaned.xlsx (File > Save As > Excel Workbook) to keep your pivot tables intact.

You should now have two files in your folder: novapay_transactions_cleaned.xlsx (your workbook with pivot tables) and novapay_transactions_cleaned.csv (the flat file for MySQL import).

Don't see both files?

Make sure you saved twice: once as CSV and once as XLSX. If you only saved as CSV, the pivot tables are lost because CSV doesn't support multiple sheets. Reopen the CSV, recreate the pivot tables, and save as XLSX first next time.

help me figure out my Excel save issue

Your data is clean, explored, and ready for serious analysis. Next up, you'll load it into MySQL and build a proper database schema that supports fast analytical queries.

Load Data into MySQL and Build the Schema

Your cleaned transaction data is ready in a CSV file. Now you need a structured home for it where you can run powerful queries.

In this step, you will create a MySQL database, define a table schema that matches your cleaned data, import the CSV, and build a summary table that pre-aggregates customer-level metrics for faster dashboard queries later.

In this step, get ready to:
  • Create the novapay_analytics database and transactions table.
  • Import cleaned CSV data into MySQL.
  • Build a customers summary table using aggregation.
Create the database and transactions table

Before you can import data, MySQL needs a dedicated database and a table with columns that match your CSV structure.

  • Open a MySQL client session with local infile enabled by running this command in your terminal:
mysql --local-infile=1 -u root -p

Why the --local-infile flag?

By default, MySQL disables loading files from the client machine for security reasons. The --local-infile=1 flag enables this capability for your session so you can import the CSV file in the next substep.

  • Enter your MySQL root password when prompted.
  • Enable local infile on the server side and create the database by running:
-- Enable local file loading on the server
SET GLOBAL local_infile = 1;

-- Create the project database
CREATE DATABASE novapay_analytics;

-- Switch to the new database
USE novapay_analytics;

What does this code do?

SET GLOBAL local_infile = 1 tells the MySQL server to accept file uploads from the client. Without this, the data import command in the next substep will fail.

CREATE DATABASE creates a new empty database called novapay_analytics. USE switches your session into that database so all subsequent commands target it.

  • Create the transactions table by running:
CREATE TABLE transactions (
    transaction_id VARCHAR(50) PRIMARY KEY,
    customer_id VARCHAR(50),
    transaction_date DATE,
    amount DECIMAL(10,2),
    transaction_type VARCHAR(50),
    channel VARCHAR(50),
    merchant_category VARCHAR(100)
);

What does this table structure do?

Each column maps directly to a column in your cleaned CSV. transaction_id is the primary key, which means every row must have a unique ID.

DECIMAL(10,2) stores monetary amounts with up to 10 digits total and 2 decimal places. This avoids floating-point rounding errors that can creep into financial calculations.

  • Verify the table was created by running:
DESCRIBE transactions;

You should see all seven columns listed with their types matching what you just defined.

Getting an access denied error?

Make sure you are logged in as a user with CREATE privileges. If you installed MySQL with the default setup, the root user has all privileges.

If SET GLOBAL local_infile = 1 fails with a permissions error, try reconnecting with mysql --local-infile=1 -u root -p and ensure you are using the root account.

Help me troubleshoot my MySQL connection

Import the cleaned CSV data

Your novapay_transactions_cleaned.csv file from the previous step contains all the completed transactions ready for import.

  • Load the CSV data into the transactions table by running:
LOAD DATA LOCAL INFILE [[CSVPATH="C:/Users/YourName/Desktop/novapay_transactions_cleaned.csv"]]
INTO TABLE transactions
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(transaction_id, customer_id, transaction_date, amount, transaction_type, channel, merchant_category);

What does this command do?

  • LOAD DATA LOCAL INFILE reads a file from your local machine and sends it to the MySQL server.
  • FIELDS TERMINATED BY ',' tells MySQL that columns are separated by commas (CSV format).
  • ENCLOSED BY '"' handles fields wrapped in double quotes.
  • LINES TERMINATED BY '\r\n' matches Windows line endings (use '\n' if you saved the CSV on macOS or Linux).
  • IGNORE 1 ROWS skips the header row so column names are not inserted as data.

Find your file path

Use forward slashes in the file path even on Windows (e.g. C:/Users/YourName/Desktop/novapay_transactions_cleaned.csv). MySQL accepts forward slashes on all platforms.

  • Verify the data loaded successfully by running:
SELECT COUNT(*) FROM transactions;

The result should match the number of data rows in your cleaned CSV (excluding the header row).

  • Check a few sample rows to confirm the data looks correct:
SELECT * FROM transactions LIMIT 5;

You should see five rows with properly formatted dates (YYYY-MM-DD), decimal amounts, and clean category values.

Getting a "LOCAL capability is disabled" error?

This means either the server or your client session does not have local infile enabled. Run SHOW VARIABLES LIKE 'local_infile'; to check the server setting. If it shows OFF, run SET GLOBAL local_infile = 1; again.

You also need to exit MySQL and reconnect with the --local-infile=1 flag on the client side. Both server and client must allow local loading.

Help me fix my LOAD DATA LOCAL INFILE error

Create a customers summary table

Power BI queries will be faster if customer-level aggregations are pre-computed. This summary table stores each customer's first and last transaction dates, total transaction count, and total spend.

  • Create the customers table structure by running:
-- Create a summary table for customer-level metrics
CREATE TABLE customers (
    customer_id VARCHAR(50) PRIMARY KEY,
    first_transaction_date DATE,
    last_transaction_date DATE,
    total_transactions INT,
    total_amount DECIMAL(12,2)
);
  • Populate the table by aggregating data from transactions:
-- Aggregate transaction data per customer
INSERT INTO customers (customer_id, first_transaction_date, last_transaction_date, total_transactions, total_amount)
SELECT
    customer_id,
    MIN(transaction_date) AS first_transaction_date,
    MAX(transaction_date) AS last_transaction_date,
    COUNT(*) AS total_transactions,
    SUM(amount) AS total_amount
FROM transactions
GROUP BY customer_id;

What does this query do?

The INSERT INTO ... SELECT pattern creates new rows from a query result. For each unique customer_id, it calculates their earliest transaction date, latest transaction date, how many transactions they made, and their total spend.

This denormalised table speeds up Power BI queries later because it avoids recalculating these aggregations every time the dashboard refreshes.

  • Verify the customers table is populated by running:
SELECT COUNT(DISTINCT customer_id) FROM transactions;
  • Compare that number with the row count in the customers table:
SELECT COUNT(*) FROM customers;

Both numbers should match. This confirms every unique customer in the transactions table has a corresponding row in the customers summary table.

  • Spot-check the data by running:
SELECT * FROM customers LIMIT 5;

You should see five rows with a customer ID, date range, transaction count, and total amount.

Seeing zero rows in the customers table?

The INSERT INTO ... SELECT query depends on data existing in the transactions table. Run SELECT COUNT(*) FROM transactions; first. If it returns 0, your CSV import in the previous substep did not succeed. Go back and resolve the LOAD DATA step before retrying.

Help me figure out why my customers table is empty

✔️ Awesome, I've got everything!

Great. Make sure you have both the transactions and customers tables populated before moving on.

ⓧ I'd like to double check the full code

Here is the complete set of SQL statements you should have run in this step:

-- Enable local file loading
SET GLOBAL local_infile = 1;

-- Create the project database
CREATE DATABASE novapay_analytics;
USE novapay_analytics;

-- Create the transactions table
CREATE TABLE transactions (
    transaction_id VARCHAR(50) PRIMARY KEY,
    customer_id VARCHAR(50),
    transaction_date DATE,
    amount DECIMAL(10,2),
    transaction_type VARCHAR(50),
    channel VARCHAR(50),
    merchant_category VARCHAR(100)
);

-- Import the cleaned CSV data
LOAD DATA LOCAL INFILE 'C:/Users/YourName/Desktop/novapay_transactions_cleaned.csv'
INTO TABLE transactions
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(transaction_id, customer_id, transaction_date, amount, transaction_type, channel, merchant_category);

-- Create the customers summary table
CREATE TABLE customers (
    customer_id VARCHAR(50) PRIMARY KEY,
    first_transaction_date DATE,
    last_transaction_date DATE,
    total_transactions INT,
    total_amount DECIMAL(12,2)
);

-- Populate with aggregated customer data
INSERT INTO customers (customer_id, first_transaction_date, last_transaction_date, total_transactions, total_amount)
SELECT
    customer_id,
    MIN(transaction_date) AS first_transaction_date,
    MAX(transaction_date) AS last_transaction_date,
    COUNT(*) AS total_transactions,
    SUM(amount) AS total_amount
FROM transactions
GROUP BY customer_id;

Your MySQL database is loaded and structured. Next up, you will write analytical SQL queries that segment these customers using RFM analysis and answer NovaPay's key business questions.

Write Analytical SQL Queries

Your novapay_analytics database is loaded and ready to answer questions. Now it is time to turn raw data into business insights.

The NovaPay business team wants to know where revenue is trending, which channels are growing, and who their most valuable customers are. In this step, you will write SQL queries that answer those questions and then build a customer segmentation model using RFM analysis.

In this step, get ready to:
  • Write revenue trend, channel breakdown, and top customer queries.
  • Build an RFM scoring query that ranks customers by Recency, Frequency, and Monetary value.
  • Create a reusable MySQL view that segments customers into business-meaningful groups.
Write revenue and volume queries

These three queries answer the business team's most common questions about revenue performance.

  • Make sure you are using the correct database by running this command in your MySQL client:
USE novapay_analytics;
  • Query the monthly revenue trend by running:
-- Monthly revenue and transaction volume trend
SELECT
    DATE_FORMAT(transaction_date, '%Y-%m') AS month,
    SUM(amount) AS total_revenue,
    COUNT(*) AS total_transactions
FROM transactions
GROUP BY month
ORDER BY month;

What does this query do?

DATE_FORMAT(transaction_date, '%Y-%m') extracts the year and month from each transaction date, grouping all transactions within the same month together.

The SUM(amount) gives you total revenue per month, while COUNT(*) shows how many transactions occurred. Together, they reveal whether NovaPay is growing or declining month over month.

  • You should see one row per month showing the total revenue and transaction count for that month, ordered chronologically.

Getting zero rows or an error?

  • Check that you ran USE novapay_analytics; first. Without it, MySQL may be querying a different database.
  • Confirm your transactions table has data by running SELECT COUNT(*) FROM transactions; first.

Help me debug my monthly revenue query

  • Query the revenue breakdown by channel by running:
-- Revenue and volume by transaction channel
SELECT
    channel,
    SUM(amount) AS total_revenue,
    COUNT(*) AS total_transactions
FROM transactions
GROUP BY channel
ORDER BY total_revenue DESC;

What does this query do?

This groups all transactions by their channel (mobile_app, web, ATM, branch) and ranks them by revenue. The result tells the business team which channel drives the most income.

  • You should see one row per channel with its total revenue and transaction count, sorted from highest to lowest revenue.
  • Query the top 10 customers by total spend by running:
-- Top 10 highest-spending customers
SELECT
    customer_id,
    SUM(amount) AS total_spent,
    COUNT(*) AS total_transactions
FROM transactions
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 10;

What does this query do?

This ranks every customer by their total lifetime spend and returns only the top 10. These are NovaPay's most valuable individual customers by monetary value alone.

  • You should see 10 rows showing customer IDs alongside their total spend and transaction count, with the highest spender at the top.

Results look unexpected?

  • If you see fewer than 10 rows, your dataset may have fewer than 10 unique customers. That is fine for testing.
  • If amounts look wrong, verify that you only loaded completed transactions (no failed or pending rows) during the data prep step.

Help me debug my top customers query

Build the RFM analysis

Simple aggregations tell you what happened, but not which customers matter most. RFM analysis scores every customer on three dimensions to identify your champions and your at-risk accounts.

What is RFM analysis?

RFM stands for Recency, Frequency, and Monetary. It measures how recently a customer transacted (R), how often they transact (F), and how much they spend (M).

By scoring each dimension into quartiles (1 to 4), you can quickly identify high-value segments. A customer who transacted yesterday, buys often, and spends heavily is a Champion. One who has not transacted in months and only bought once is Lost.

  • Calculate raw RFM values and score them into quartiles by running:
-- RFM calculation with quartile scoring
SELECT
    customer_id,
    -- Raw RFM values
    DATEDIFF(CURDATE(), MAX(transaction_date)) AS recency,
    COUNT(*) AS frequency,
    SUM(amount) AS monetary,
    -- Quartile scores (1-4)
    NTILE(4) OVER (ORDER BY DATEDIFF(CURDATE(), MAX(transaction_date)) ASC) AS recency_score,
    NTILE(4) OVER (ORDER BY COUNT(*) ASC) AS frequency_score,
    NTILE(4) OVER (ORDER BY SUM(amount) ASC) AS monetary_score
FROM transactions
GROUP BY customer_id;

How does the scoring work?

DATEDIFF(CURDATE(), MAX(transaction_date)) calculates how many days since the customer's last transaction. A lower number means they transacted more recently.

NTILE(4) divides all customers into 4 equal-sized groups (quartiles) based on the ORDER BY value. For recency, score 1 means most recent (best). For frequency and monetary, score 4 means highest (best).

  • You should see one row per customer showing their raw recency, frequency, and monetary values alongside their quartile scores (integers 1 through 4).

Getting an error about window functions?

  • NTILE is a window function available in MySQL 8.0 and later. Run SELECT VERSION(); to confirm you are on version 8.0 or higher.
  • Make sure there is no semicolon inside the query before the final line. The entire SELECT statement must execute as one command.

Help me fix my RFM query

Create customer segments from RFM scores

Quartile scores are useful, but labels like "Champions" or "At Risk" are what the business team actually needs. You will now wrap the RFM logic into a persistent MySQL view so Power BI can access the segments directly.

  • Create the vw_customer_segments view by running:
-- Create a view that assigns each customer to a segment
CREATE VIEW vw_customer_segments AS
WITH rfm_calc AS (
    -- Calculate raw RFM values per customer
    SELECT
        customer_id,
        DATEDIFF(CURDATE(), MAX(transaction_date)) AS recency,
        COUNT(*) AS frequency,
        SUM(amount) AS monetary
    FROM transactions
    GROUP BY customer_id
),
rfm_scores AS (
    -- Score each dimension into quartiles
    SELECT
        customer_id,
        NTILE(4) OVER (ORDER BY recency ASC) AS recency_score,
        NTILE(4) OVER (ORDER BY frequency ASC) AS frequency_score,
        NTILE(4) OVER (ORDER BY monetary ASC) AS monetary_score
    FROM rfm_calc
)
-- Map scores to business-meaningful segment labels
SELECT
    customer_id,
    recency_score,
    frequency_score,
    monetary_score,
    CASE
        WHEN recency_score = 1 AND frequency_score = 4 AND monetary_score = 4 THEN 'Champions'
        WHEN frequency_score >= 3 AND monetary_score >= 3 THEN 'Loyal Customers'
        WHEN recency_score >= 3 AND frequency_score >= 2 THEN 'At Risk'
        WHEN recency_score = 4 AND frequency_score = 1 THEN 'Lost'
        ELSE 'Other'
    END AS segment
FROM rfm_scores;

What does this view do?

The first CTE (rfm_calc) calculates raw recency, frequency, and monetary values for each customer. The second CTE (rfm_scores) converts those raw values into quartile scores using NTILE(4).

The final SELECT maps score combinations to segment labels. The CASE conditions are evaluated in order, so a customer matching "Champions" will not also be labelled "Loyal Customers" even if they qualify for both.

Because this is a view (not a table), it recalculates every time you query it. Your segments will always reflect the latest data.

What do the segment labels mean?

  • Champions (R=1, F=4, M=4): Transacted very recently, buy often, and spend the most.
  • Loyal Customers (F>=3, M>=3): Consistently frequent buyers with high spend.
  • At Risk (R>=3, F>=2): Have not transacted recently but were previously active.
  • Lost (R=4, F=1): Have not transacted in a long time and only bought once.
  • Verify the view was created successfully by checking the segment distribution:
-- Check the distribution of customers across segments
SELECT segment, COUNT(*) AS customer_count
FROM vw_customer_segments
GROUP BY segment;
  • You should see a result set with one row per segment (Champions, Loyal Customers, At Risk, Lost, and possibly Other) along with the count of customers in each.

View creation failed?

  • If you see "view already exists", run DROP VIEW IF EXISTS vw_customer_segments; first, then re-run the CREATE VIEW statement.
  • If you see a syntax error near WITH, confirm your MySQL version is 8.0 or higher. Common Table Expressions (CTEs) require MySQL 8.0+.
  • If all customers show as "Other", double-check that your NTILE scoring order is correct. Score 1 for recency should be the most recent customers, and score 4 for frequency and monetary should be the highest values.

Help me debug my view creation

✔️ Awesome, I've got everything!

Great. Your view is ready and Power BI will be able to query it directly in the next step.

ⓧ I'd like to double check the full view

Here is the complete vw_customer_segments view definition for comparison:

CREATE VIEW vw_customer_segments AS
WITH rfm_calc AS (
    SELECT
        customer_id,
        DATEDIFF(CURDATE(), MAX(transaction_date)) AS recency,
        COUNT(*) AS frequency,
        SUM(amount) AS monetary
    FROM transactions
    GROUP BY customer_id
),
rfm_scores AS (
    SELECT
        customer_id,
        NTILE(4) OVER (ORDER BY recency ASC) AS recency_score,
        NTILE(4) OVER (ORDER BY frequency ASC) AS frequency_score,
        NTILE(4) OVER (ORDER BY monetary ASC) AS monetary_score
    FROM rfm_calc
)
SELECT
    customer_id,
    recency_score,
    frequency_score,
    monetary_score,
    CASE
        WHEN recency_score = 1 AND frequency_score = 4 AND monetary_score = 4 THEN 'Champions'
        WHEN frequency_score >= 3 AND monetary_score >= 3 THEN 'Loyal Customers'
        WHEN recency_score >= 3 AND frequency_score >= 2 THEN 'At Risk'
        WHEN recency_score = 4 AND frequency_score = 1 THEN 'Lost'
        ELSE 'Other'
    END AS segment
FROM rfm_scores;

Your analytical queries are complete and the customer segmentation view is live in MySQL. Next up, you will connect Power BI directly to this database and turn these query results into an interactive dashboard.

Build the Power BI Dashboard

In Step 4 you wrote SQL queries that segment customers and reveal revenue patterns. Those queries answer the business team's questions, but raw query results aren't easy for stakeholders to interpret at a glance.

Now you will connect Power BI Desktop directly to your MySQL database and build an interactive two-page dashboard. Stakeholders will be able to explore revenue trends, channel performance, and customer segments without writing a single query.

In this step, get ready to:
  • Connect Power BI Desktop to your local MySQL database.
  • Build a revenue overview page with KPI cards and trend charts.
  • Create a customer segmentation page with interactive filtering.
Connect Power BI to your MySQL database

Power BI needs a live connection to your novapay_analytics database so it can pull data directly from your tables and views.

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and press Enter to open it.
  • In the ribbon, click Home > Get Data > Database > MySQL database.
  • In the Server field, enter localhost.
  • In the Database field, enter novapay_analytics.
  • Click OK.
  • When prompted for credentials, select Database in the left panel (not Windows).
  • Enter your MySQL username and password, then click Connect.
  • In the Navigator window, check the box next to the transactions table.
  • Also check the box next to vw_customer_segments if it appears in the list.
  • Click Load.

Don't see vw_customer_segments in Navigator?

MySQL views may not appear in the Navigator list. To load the view manually, go back to Home > Get Data > MySQL database again. This time, expand the Advanced options section in the connection dialog and enter a SQL statement:

SELECT * FROM novapay_analytics.vw_customer_segments

Click OK and then Load to bring the view into your data model.

help me load a MySQL view into Power BI when it doesn't appear in Navigator

What is the Database authentication type?

Power BI offers Windows and Database authentication when connecting to MySQL. You must choose Database because MySQL uses its own credential system, not Windows accounts.

Your MySQL username is typically root unless you created a different user. The password is the one you set during MySQL installation.

After loading, you should see transactions and vw_customer_segments listed in the Data pane on the right side of the report canvas.

  • Save your file as novapay_dashboard.pbix by pressing Ctrl+S and choosing a location on your Desktop.
Build the revenue overview visuals

Page 1 of your dashboard gives stakeholders a high-level view of NovaPay's financial performance through KPI cards and trend charts.

Add KPI cards
  • Double-click the page tab at the bottom of the canvas and rename it to Revenue Overview.
  • In the Visualizations pane, select the Card visual.
  • From the Data pane, drag amount into the Fields well. Power BI automatically sums it.
  • This card shows Total Revenue. Position it at the top-left of the canvas.
  • Click an empty area of the canvas to deselect, then add a second Card visual.
  • Drag transaction_id into the Fields well. Click the dropdown arrow on the field and change the aggregation to Count.
  • This card shows Total Transactions. Position it to the right of the first card.
  • Add a third Card visual. Drag customer_id into the Fields well and change the aggregation to Count (Distinct).
  • This card shows Unique Customers. Position it next in the row.
  • Add a fourth Card visual. Drag amount into the Fields well and change the aggregation to Average.
  • This card shows Average Transaction Value. Position it at the end of the row.

What are KPI cards?

KPI stands for Key Performance Indicator. A Card visual in Power BI displays a single headline number. Placing four cards in a row across the top of your dashboard gives stakeholders the most important metrics at a glance.

Each card uses a different aggregation on your data: SUM for revenue, COUNT for transactions, DISTINCTCOUNT for unique customers, and AVERAGE for the typical transaction size.

Add trend and breakdown charts
  • Click an empty area of the canvas, then select the Line Chart visual from the Visualizations pane.
  • Drag transaction_date to the X-axis well.
  • Drag amount to the Y-axis well (it defaults to SUM).
  • If Power BI groups dates into a hierarchy (Year/Quarter/Month), click the double-arrow icon on the X-axis header to drill to the monthly level.
  • Resize this chart to fill the left half of the canvas below the KPI cards. It shows the monthly revenue trend.
  • Click an empty area, then select the Donut Chart visual.
  • Drag channel to the Legend well.
  • Drag amount to the Values well.
  • Position this donut chart in the top-right area below the KPI cards. It shows revenue split by channel.
  • Click an empty area, then select the Stacked Bar Chart visual.
  • Drag transaction_type to the Y-axis well.
  • Drag transaction_id to the X-axis well and set aggregation to Count.
  • Position this chart in the bottom-right area. It shows transaction volume by type.

Save your file with Ctrl+S. Your Page 1 should now display four KPI cards across the top, a line chart on the left, a donut chart on the top-right, and a stacked bar chart on the bottom-right.

Charts showing a single data point instead of monthly breakdown?

Power BI may have grouped your transaction_date into a year-level hierarchy. Click the double-down arrow icon at the top of the Line Chart visual to expand to the Month level.

If dates appear as text instead of a proper date hierarchy, go to the Data pane, select the transaction_date column, and confirm its data type is set to Date in the ribbon.

help me fix my Power BI line chart so it shows monthly data instead of a single point

Add the customer segmentation page

Page 2 lets stakeholders explore how customers are distributed across the RFM segments you created in Step 4.

  • At the bottom of the canvas, click the + icon to add a new report page.
  • Double-click the new page tab and rename it to Customer Segments.
  • Select the Donut Chart visual from the Visualizations pane.
  • In the Data pane, expand vw_customer_segments.
  • Drag segment to the Legend well.
  • Drag customer_id to the Values well and set aggregation to Count.
  • Position the donut chart on the left side of the canvas. It shows customer count by segment.
  • Click an empty area, then select the Table visual from the Visualizations pane.
  • Drag these fields from vw_customer_segments into the Columns well: segment, customer_id (set to Count), monetary_score (set to Average), and frequency_score (set to Average).
  • Position the table on the right side of the canvas.
  • Click an empty area, then select the Slicer visual from the Visualizations pane.
  • Drag segment from vw_customer_segments into the slicer's field well.
  • Position the slicer at the top of the page so it is clearly visible.

How does the slicer work?

A slicer is an on-canvas filter that lets users click a value to instantly filter every other visual on the page. When a stakeholder clicks Champions in the slicer, the donut chart and table update to show only Champion customers.

By default, a slicer filters all other visuals on the same report page. No extra configuration is needed for this behaviour.

  • Apply a consistent colour theme to both pages. Click an empty area of the canvas, then go to View > Themes in the ribbon and select a professional theme that suits a fintech dashboard.
  • Add clear titles to each visual by selecting a chart, opening the Format pane, expanding Title, and entering descriptive text (e.g. "Monthly Revenue Trend", "Revenue by Channel").
  • Save your file with Ctrl+S.

Test interactivity by clicking a segment name in your slicer. All visuals on Page 2 should update to reflect that segment only.

Slicer not filtering the other visuals?

Make sure all visuals on Page 2 are using fields from the same vw_customer_segments table or a related table. Visuals that use unrelated tables won't respond to the slicer.

Also confirm you haven't accidentally set interaction to None. Select the slicer, go to Format > Edit interactions in the ribbon, and ensure the filter icon is active on each visual.

help me fix my Power BI slicer so it filters the other visuals on the page

Secret mission

Add Month-over-Month Growth Metrics

Your dashboard shows what happened, but can it show what's changing? In this secret mission, you'll create DAX measures that calculate Month-over-Month revenue growth and apply conditional formatting to instantly flag declining performance in red.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local machine, so there are no ongoing cloud costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • MySQL database novapay_analytics stored locally on your machine.
  • Power BI Desktop file novapay_dashboard.pbix saved locally.
  • Excel workbook novapay_transactions_cleaned.xlsx and CSV file novapay_transactions_cleaned.csv saved locally.
  • MySQL Connector/NET 9.7.0 driver installed on your system.

Keep everything running

No action needed. Choose this if you are still actively building or want to refine your dashboard further.

  • The MySQL database persists on your local machine at no cost. You can continue querying and adding data.
  • Your Power BI file stays on disk and can be reopened any time.
  • The MySQL Connector/NET driver remains available for any future Power BI-to-MySQL connections.

Pause - I'll come back to this later

Stop the MySQL service to free up system resources while keeping all your data intact.

  • Press the Windows key, type Services, and press Enter.
  • Find the MySQL service in the list (it may be named MySQL80 or similar depending on your version).
  • Right-click the service and select Stop.
  • When you want to resume, return to Services, right-click your MySQL service, and select Start.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each step below.

  • Drop the MySQL database. Open your MySQL command line or MySQL Workbench and run:
DROP DATABASE novapay_analytics;
  • Delete your local project files. Open PowerShell and run:
del "$HOME\Desktop\novapay_dashboard.pbix"
del "$HOME\Desktop\novapay_transactions_cleaned.xlsx"
del "$HOME\Desktop\novapay_transactions_cleaned.csv"

Adjust the file paths

If you saved your files in a different location than your Desktop, update the paths in the command above to match where you stored them.

  • Uninstall the MySQL Connector/NET driver. Press the Windows key, type Add or remove programs, and press Enter.
  • Search for MySQL Connector Net in the app list.
  • Click the three-dot menu next to it and select Uninstall.

Nice Work!

Nice Work!

You just built a complete data analytics pipeline for a fintech company. From raw CSV data to a polished, interactive Power BI dashboard, you proved you can take messy financial records and turn them into actionable business insights.

You've learned how to:

  • Clean and prepare raw transaction data in Excel using filtering, date formatting, and pivot tables, then load it into a MySQL database with a structured schema.
  • Write analytical SQL queries for revenue trends, channel breakdowns, and RFM customer segmentation that classifies customers by recency, frequency, and monetary value.
  • Build an interactive two-page Power BI dashboard connected live to MySQL with KPI cards, trend charts, and customer segment filters.
  • Secret Mission: Added Month-over-Month growth rate DAX measures with conditional formatting to highlight declining metrics.

Ready to quiz yourself?