This is how I transformed a chaotic spreadsheet into a clean, automated BI system that tracks revenue, conversion rates, and reps performance in real time.
The client was tracking their entire sales pipeline manually in Google Sheets with three different sheets: leads, deals, payments. Nothing was connected and no one had a real-time view of how the funnel was performing.
Yet the company was growing. And they needed a way to track performance without changing how the team worked.
My role was to build a complete reporting system that:
- Kept the current input flow (Google Sheets)
- Cleaned and structured the data model underneath
- Provided clear, automated visibility into revenue, conversion, and rep efficiency
The original spreadsheet had a structure like this:
- Leads_Raw: lead ID, assigned rep, contact source, manual status column, multiple follow-up dates
- Deals_Raw: lead ID reference, deal amount, closing date, deal stage (open/won/lost)
- Payments_Raw: lead ID reference, payment date, amount, status (paid/failed)
This setup created multiple issues:
- Manual and inconsistent status updates
- Repeated columns for actions instead of one status field
- Disconnected tables with no relational integrity
- No timeline tracking, no funnel metrics, no performance goals
The company had data — but no visibility.
If you want to practice some of the techniques used in this article with a demo dataset I suggest you ti start from here: Finding The Best Dataset For Data Analytics Practice And Portfolio.
Contents
Reorganizing the Input Data
I cleaned and restructured the original sheets into three clean fact tables:
Leads Table
From the Leads_Raw tab
- Lead_ID (primary key)
- Assigned_Rep, Source, Created_Date
- Flags like Is_Won, Is_Paying, Has_Deal (calculated in Power BI)
Deals Table
From Deals_Raw
- Lead_ID, Deal_Amount, Close_Date, Status
- Flag logic for conversion tracking and deal qualification
Payments Table
From Payments_Raw
- Lead_ID, Payment_Amount, Payment_Date, Status
- Aggregated for revenue KPIs, success/fail rate, and LTV
I also created:
- A complete Date Dimension using Power Query
- A Targets table, defining monthly goals for:
- Revenue, Leads, Paying Customers, LTV
- CR Lead→Deal, CR Lead→Paying
- A sales reps table used for all the measures neede to calculate reps KPIs
Dimensional Model in Power BI
Once cleaned, I connected everything using a star schema:
- Fact tables: Fact_Leads, Fact_Deals, Fact_Payments
- Dimensions: Dim_Date, Dim_SalesReps, Targets
Key relationships:
- Lead_ID is used as the primary bridge across fact tables
- All facts are linked to Dim_Date via activity date fields (Created_At, Close_Date, Payment_Date)
- Targets is a disconnected table accessed via lookup logic
💡 This model lets users slice KPIs by rep, source, and time with full flexibility.
To understand more about data modeling in Power BI check this two articles where I explain in details the theory and the practice of creating a responsive and scalable data model: Data Modeling Guide and How to Create a Power BI Data Model: Step-by-Step Guide with IBM HR Dataset.
The Dashboards
With the model in place, I built a report with 5 strategic views.
Executive Overview
This is the main decision-maker view. It includes:
- Total Revenue, Leads, Customers, LTV
- Status labels (with color coding)
- Performance commentary (e.g., “$7.3K below target, ▼ -16%”)
- Revenue trend over time
- Leads by Status breakdown
This page uses a lot of DAX language, if you want to have an understanding of how to create simple KPIs measure check my article: Mastering DAX for Advanced HR Analytics in Power BI.
The DAX used for creating the text under the KPIs is explained in the next sections of this article.
Month – Over – Month
This page compares the current month to the previous one across key KPIs:
- Revenue, Leads, Deals, CR
- Comparison trend charts
- Variations and arrows (↑ or ↓) for each metric
- Breakdown by sales rep
This page is built mainly with DAX measures.
Using as example Revenue, firstly I calculate the revenue per month:
Revenue per Month =
CALCULATE(
SUM(Fact_Payments[Amount]),
DATESMTD(Dim_Date[Date]))
After I calculated the revenue of the previous month:
Prev Month Revenue =
CALCULATE(
[Revenue per Month],
PARALLELPERIOD(Dim_Date[Date], -1, MONTH))
At this point I was able to create the variation between the current month selected and the previous month:
Revenue MoM % =
DIVIDE([Revenue per Month] – [Prev Month Revenue], [Prev Month Revenue])
I decided to create the card in a way that can give a clear understanding of the situation rapidly, showing the variation over month with a growing or decreasing sign and with a dynamic text that explain the variation in details.
For the label with the percentage of variation:
Revenue MoM Label =
VAR Diff = [Revenue MoM %]
RETURN
IF(
ISBLANK(Diff),
“–”,
IF(
Diff > 0,
“▲ ” & FORMAT(Diff, “0%”),
IF(
Diff < 0,
“▼ ” & FORMAT(ABS(Diff), “0%”),
“–” )))
For the text under:
Revenue MoM Detail =
VAR Current1 = [Revenue per Month]
VAR Previous = [Prev Month Revenue]
VAR Diff = Current1 – Previous
VAR Sign = IF(Diff > 0, “+”, IF(Diff < 0, “-“, “”))
VAR MonthNow = FORMAT(MAX(Dim_Date[Date]), “mmm”)
VAR MonthPrev = FORMAT(EDATE(MAX(Dim_Date[Date]), -1), “mmm”)
RETURN
MonthPrev & “: $” & FORMAT(Previous, “#,0″) & ” | ” &
MonthNow & “: $” & FORMAT(Current1, “#,0”) &
” | Var: ” & Sign & “$” & FORMAT(ABS(Diff), “#,0”)
Funnel View
Here I visualized the full journey from Lead → Deal → Paying Customer with:
- A clean horizontal funnel
- Conversion rate gauges with live targets
- Label summaries showing “+7% above target”
- Conversion breakdown by source and rep
- All visuals are clickable and act as filters
Also this page is built using different DAX formulas, I calculated the conversion rate for source, reps and for different stages of the funnel, using the function DIVIDE, for example:
Conversion Rate by Source (%) = DIVIDE([Paying Customers per Source], [Leads per Source])
For the cards with the gauge graphs I used the same logic as for the MoM cards, developing a text that showcases the situation in details.
Sales Reps Details
A modern, card-style page with images and clean KPIs per rep:
- Leads handled
- Deals closed
- Conversion rate
- Revenue generated
Sales Reps Insights
Compare reps on performance averages:
- Dynamic bar charts by lead, deal, CR, revenue
- Possibility to change the view with a table
Final Result
This once chaotic Google Sheet is now:
- A fully automated sales dashboard
- With KPIs, benchmarks, and clear commentary
- Connected to a model that supports any custom time range
- Providing daily insight without changing the original input process
