Time spent in deal stage: percentile or buckets

[ketewan]

Hi there!

I know that there is a template to calculate average (or max/min/total) time deals spent in a specific stage. 

I wonder if there is a possibility to create a distribution or percentile based on this data. For example, I want to know how many deals fall under <1 days, 1-3 days, >3 days buckets etc, or maybe even median instead of average would also help.

[danmoyle · Accepted solution]

@ketewan here's some info I found and put together that may help. 

HubSpot does provide the capability to create distributions or percentiles based on the data of how deals spend time in a specific stage, but it needs to be done with the custom reporting features.

1. Create a custom report: Go to your HubSpot account, navigate to the "Reports" section, and create a new custom report. Choose the appropriate primary data source, Deals.

2. Set up the time duration property: Ensure that you have a property in HubSpot that tracks the duration of time spent in a specific stage for each deal. If you don't have this property, you may need to create a custom property to store this information. [This would be a custom property where the filed type is calculation. You should be able to calculate time between create date and either last modified date, or a custom property like "date moved in stage."]

3. Define the time buckets: Determine the time buckets you want to analyze, such as "< 1 day," "1-3 days," "> 3 days," and so on. Assign these buckets to the respective time ranges.

4. Build the report: Configure the report to group the deals based on the time duration property you created earlier. Use the time buckets as the grouping criteria.

5. Apply filters (optional): If you want to further refine the report, you can apply filters based on specific criteria like deal stage, deal size, or any other relevant properties.

To calculate percentiles or the median, you may need to export the data from HubSpot and perform the calculations using external tools like spreadsheets or data analysis software. Or you might be able to use a pivot table functionalitry. By sorting the time durations in ascending order, you can determine the median (50th percentile) or other percentiles based on your desired criteria.

Note: HubSpot's reporting capabilities may evolve over time, so it's always a good idea to explore their latest features info and consult their documentation or support resources for more detailed instructions.

Thanks for the tag @kvlschaefer 😊

[ketewan]

> If you don't have this property, you may need to create a custom property to store this information. 
This won't help me with analytics for already existing deals 😞
There is already "Time in stage" calculated property in Hubspot by default, but for some reason, I can only use it in deals-based report. Maybe it's possible to extract it another way?

[danmoyle]

@ketewan you could, by hand, go into the property history and extract that data, yes. It's incredibly tedious and won't help with reports unfortunately. 

I can't envision another way to make this work for you, I'm sorry! 

[Franci]

Hello @ketewan,

This is what I would do:

1. To obtain data on the time spent on each deal in a specific stage from HubSpot, retrieve the necessary data.
2. Next, calculate the duration by taking the difference between the start and end timestamps or dates.
3. Categorize these durations by grouping them into time buckets based on desired intervals, such as "<1 day," "1-3 days," or ">3 days."
4. Assign each duration to its corresponding bucket. To evaluate the distribution, tally the number of deals in each bucket.
5. To better comprehend the distribution, visualize the data using a histogram or bar chart, representing the number or percentage of deals in each time bucket. 

Thank you @kvlschaefer for tagging me!

[ketewan]

> Next, calculate the duration by taking the difference between the start and end timestamps or dates.

How can it be done in reports? I see "Time spent in property", which is only available in the deals report, but already not available in joined Contact vs Deal report or in the usual search. Is deal property history available?
I can also create separate reports based on bucket restriction (like one report for <1, one for 1-3, and one for >3)), but wasn't yet successful in combining them into one report. Maybe I am using the wrong tools, and it can't be done in reports?

[kvlschaefer]

Hi @ketewan,

Thanks for reaching out to the Community!

I wanted to invite our subject matter experts to see if they have advice.

Hi @Franci, @danmoyle, @StjepanGrcic - Do you have any sales reporting tips for @ketewan?

Thank you! 

Best,

Kristen