We were going through our own sales reporting dashboards when we noticed something. They were only built to show snapshots.
How we noticed was more interesting.
We would go to check on a particular opportunity in the pipeline and discover that we weren’t seeing it anymore. Naturally, having known there was a deal we were looking for that couldn’t be found, this would lead to a manual lookup. Typically we would at that point discover that the deal had been marked as closed lost. But after building countless report dashboards for ourselves and for our clients, we knew this wouldn’t cut it. We needed something better than a snapshot. Something that captured all the changes that happen over time.
See, the reports we had were good. Good if you only wanted to see the current state of things as they are. You could see the number of new opportunities created this month, how many deals are in each stage, what’s been won, lost, etc., etc.. It’s the standard baseline, and for the most part, it works. Until something disappears.
So now, the limitations were obvious. No clear record of when deals were moved, how quickly they moved, or whether stage transitions are trending in a healthy direction. Anyone who’s built their dashboards like this puts their team at risk of only being able to notice problems in hindsight, like when deals stall or when forecasts miss. Clearly, after the fact is not the time to be made aware of an issue.
Without visibility into change, it's difficult to ask the right questions. And without those questions, pipeline management becomes reactive at best.
A More Dynamic Reporting Approach
As we were thinking about what to do, the exact challenge was addressed in a HubSpot Admins Show & Tell session. Perfect timing. Rickey Payne, a HubSpot Systems Architect, demonstrated a set of dashboards designed to show day-by-day pipeline changes, namely, deal stage changes. It was exactly what we needed. That was our aha moment.
By tracking movement, not just totals, we could get the context to interpret trends, assess deal velocity, and flag issues earlier. So we set off to make our own report to capture those change events.
How It Works
The dashboard focuses on opportunity stage changes over the past 14 days (intentionally excluding initial deal creation events to avoid noise). It’s built using a pivot table format in HubSpot’s custom report builder.
Our key configurations include:
- Filters to only show records with a stage change in the last 14 days
- Groupings by pipeline and opportunity stage
- Exclusions to filter out record creation events
Making Pivot Tables Interactive
There was one common frustration that we had to tackle. With HubSpot pivot tables, record ID fields are not clickable by default. Unlike single-object reports, pivot tables don’t let users click through from the ID column. Boo.
But there’s a workaround.
To enable linking:
- Add the record name or ID as a row field to display the deal name
- Then add the same field again as an aggregation value, using a count or distinct count function
- This second instance generates a clickable link
- Rename the resulting column from “Distinct Count of Record ID” to something more user-friendly, like “Deal Record Link”
Now, our report is fully interactive and users can click directly into specific opportunities from the dashboard view.
What to Track Next
We were happy. And we couldn't keep from dreaming of what to do next. Here are some next steps we (and you) can consider.
- Segmenting stage change reports by rep or region
- Tracking average time spent in each stage
- Highlighting reversals (deals moving backward) or sudden drops (deals jumping straight to closed-lost)
- Pairing change reports with lead source, company size, or deal type filters to identify patterns
We found this new report to significantly improve our team's visibility into what is happening everyday in our sales pipeline. We encourage you to give this type of report a try yourself! Let us know if it makes a difference for you and your team or not. We'd love to hear from you.
