Hi, it's Casey. 👋
Over the weekend I listened back to Joshua Oakes and Madelyn Donovan's Reporting and Dashboards office hours from last Wednesday, and they got into process dashboards versus outcome dashboards.
I wasn't super familiar with those terms. That's one of my favorite things about office hours: people in this community have ideas you've never thought of, or at least never had words for.
I've built outcome dashboards my whole career: How many deals closed this quarter… how many leads became MQLs, then SQLs. Every one of them comes with caveats. Oh, this month this happened. Oh, this rep never marks anyone as SQL, so we always see a jump from MQL to opportunity. This exception, that exception.
Those caveats are what a process dashboard shows. An outcome dashboard tells you what happened. A process dashboard can tell you why. Maybe your win rate looks great because you cleaned out a bunch of old deals. Maybe a rep is skipping a step. It's hard to see both side by side, which is why my reports always came with footnotes.
Madelyn put it in words I knew better: "getting something quantitative out of qualitative data." A step isn't good or bad. It either happened or it didn't, and that's what lets you measure it.
Then Joshua built one live. I don't want to be dramatic and say it changed my life. Okay, I'll be a little dramatic. It kind of did.
Coming off Halloween weekend, I'll admit it, data sets would be part of my HubSpot haunted house. I've opened them a couple of times, seen what looks like a giant spreadsheet, thought "what am I supposed to do with this," and closed the tab. (I hope I'm not the only one.) When Joshua opened his last Wednesday, he told anyone who'd never seen one, "welcome to the first day of the rest of your life."
Turn Any Step Into a 1 or a 0
The session summary is open to everyone and the full replay is for signed-in subscribers.
Quick tier note: Data sets need Data Hub Professional or Enterprise, plus Data Studio permissions. HubSpot's help page also mentions a beta opt-in for Super Admins, so if you don't see Data Studio, check with your admin.
I thought data sets were all about joining data, like connecting companies to deals, or an outside source to HubSpot. You can do that. But your first data set can be simpler: creating the data you need out of data you already have.🪄
Joshua pulled deals into a data set, including the "date entered" fields for the stages in his demo pipeline. A deal that went through Approval has a date there. A deal that skipped it is blank.
Then he added a formula column, and he didn't write the formula himself. He opened Create new column using AI and asked Breeze, in plain words:
If the deal has a date in the date entered approval stage, return one, otherwise return zero.Here's what it wrote:
IF(
(
IS_KNOWN([DEAL.hs_v2_date_entered_1012022864])
OR IS_KNOWN([DEAL.hs_v2_date_entered_1015280806])
),
1,
0
)You probably don’t care about this exact formula. That's fine. All you have to do is tell Breeze what you need.
Then check what it gives you. Breeze checked two stage properties here, not one. On his next formula, it grabbed the wrong field, and Joshua had to catch it. Madelyn said it probably comes down to how well you name your properties, and Joshua pointed out that stages you create yourself get numeric internal names, which makes it harder.
A column of 1s and 0s doesn't look like much. But once a step is a number, you can calculate with it. What percent of closed-won deals ever entered that stage? Filter to closed-won deals and average the 1-or-0 column. You can get close on Pro, and in theory this one works there, but I've tried for years and never gotten it to work reliably. If you have Data Hub, data sets win.
Your version might be different: Leads created this month that became MQLs… Deals that got a statement-of-work review… Whatever you've been footnoting.
Why Your Rate Might Equal 2
Then Joshua built a rate: deals that went through approval divided by deals that won. He dropped it into a report as a single number and got 2. That's not a rate.
Formulas in a data set run one row at a time, so each deal gets its own ratio. Add up the rows and you get a count, not a percentage.
Joshua caught it right there on the call. He switched the roll-up to average, which gave him the right number for his formula: 5.71% on his sandbox sample data. (I talked to him this morning, and that's how he fixed it.) So if a rate ever comes back as a whole number, check how the report adds it up before you touch the formula.
Then make sure the name matches the question. Joshua's column says approval to won, but the math answers it the other way around: of the deals that won, how many went through approval. Both are useful. Name it for what it answers.
While prepping this newsletter, Joshua also told me something I didn't know, the same formula can behave a little differently in a data set than in the custom report builder's formula tool, and in his experience data sets behave better. Another point for data sets if you have them. If you don't, try it as a formula field in a custom report first. Custom reports need a Professional or Enterprise subscription, but you may be able to find out without Data Hub.
The Funnel That Wouldn't Add Up
This summer I was building a client's full funnel: audience to lead, lead to MQL, MQL to SAL, SAL to SQL, with a conversion rate for each step.
The way their funnel report was set up, it only tracked people who entered the funnel in the time frame we were looking at. My client had a bunch of contacts created last year that we were nurturing and moving through the stages this year, so the numbers kept coming back wrong. Each rate divides two different groups, people who hit one stage and people who hit the next, and those don't always happen in the same year. No single row can hold that. Distinct counts finally made it reliable.
So instead of a 1 or a 0 on each row, I use DISTINCT_COUNT across the whole data set. For Joshua's example, that's the count of deals that entered approval and won, divided by the count of deals that entered approval. HubSpot's "Create and use summary functions in datasets" page has a nearly identical conversion-rate example you can start from.
Breeze can write this one too. Here's the prompt I'd give it:
Divide the distinct count of deals that have a date entered approval and where Is closed won is true by the distinct count of deals that have a date entered approval.Every row will show the same number. When I did this, I was sure it was broken. It's not. HubSpot's page says it directly: summary measures repeat the result on every row. That's the total, so when the report averages the rows, it gives you the exact answer.
My prompt asks the question most people mean: of the deals that hit approval, how many won. That's the opposite direction from Joshua's column, so pick whichever one you actually need.
Six Steps to a Rate You Can Trust
Click More if you see it, then go to Data Management, then Data Studio. Click Create dataset, then Bring in data, and start from Deals.
Add columns for deal stage, the "date entered" property for the stage you want to check, and Is closed won.
Add a column with Create new column using AI, give Breeze Joshua's prompt, and name it Count of approved. Check which stage properties it used, then open a deal you know went through that stage and make sure it shows a 1.
Add a second formula column with the distinct count prompt above. Every row should show the same number.
Use the data set in a report, show the rate as a KPI with median, and format it as a percent.
Count a few deals by hand before anyone else sees the number.
Retire One Spreadsheet This Week
Find one rate your team still calculates by hand every month. I had a client where we did exactly that, because there was no other way.
Write it as a sentence: a count of this, divided by a count of that.
Build it with the steps above, and give Breeze your sentence with distinct counts.
Check the result against last month's spreadsheet.
If it matches, that's one monthly task gone. If it doesn't, bring it to office hours and we'll figure out which records the two counts disagree about.
Stop Building Properties for One Report
If a custom formula just isn't working, give data sets a try. They're less intimidating than they look. You do need Data Hub for data sets. (I still think basic math like this shouldn't need Data Hub, but here we are.)
Madelyn made a point I agree with completely: one-off properties junk up a portal and confuse the team. So many portals I walk into have properties built for a single report. Joshua added the AI piece: AI tools, and anything with API access, will find those properties and treat them like they mean something. A data set keeps that math out of everyone's way.
If data sets intimidate you, I've been there. I was right there until a couple of months ago. After July I kind of blocked them out of my mind, because I was so relieved to be done. Then I listened to Joshua and Madelyn and thought, I've done this, it works, why am I not using it more?
You have a friend here. Bring your data set, or the formula that won't work, to any office hours. I'm on Tuesdays, and Joshua and Madelyn are on Wednesdays.
- Casey
Updates from last week worth your attention
Custom Skills in Breeze Assistant | All hubs and tiers | Live
Custom skills let you give Breeze Assistant your own instructions for understanding your organization and running business-specific tasks. You can create, import and manage them in the Breeze Assistant side panel, then call one by name in any conversation. It is live, across every tier.
What to do: pick one task you keep re-explaining to Breeze, capture it once as a skill, and call it by name next time.
Filter by records without associations in segments | All hubs and tiers | Public beta
Segments can now filter for records that don't have an association, or don't have a specific association label. For example, companies with no associated deal. Before this, getting there took an extra segment, report or calculated property. It is live, every tier.
What to do: find a segment you built only to exclude records, like companies with no deal, and see whether one filter does it now.
A new setting: Create new contacts from logged emails | All hubs and tiers | Live
Contact email logging rules has a new option. It automatically creates a new contact in your CRM from incoming emails to connected personal accounts. Before, those accounts could only log emails from contacts you already had. It is live, every tier.
What to do: open your Contact email logging rules, decide which connected accounts should create contacts, and list the workflows that fire on a new contact before you turn the option on. If most of what lands in your inbox is noise, leave it off.
These came off the daily morning show, Another Orange Morning, where Chris Carolan, Casey Hawkins, and friends go through all of the updates live.
This week's Office Hours, noon Eastern (11 AM Central)
Join us at Office Hours. Bring the thing you're stuck on.
Office Hours are free through November. All you need is a free Hubsessed subscription.
Send questions between sessions to community@hubsessed.io
New: Sign in at hubsessed.io/sign-in and your member page is waiting: Office Hours replays and transcripts, this newsletter, and your Academy courses if you're a member.
