Calculating the time spent on each deal stage

[mdrnd]

Hi all,

Hope all is well for everyone. I have a question regarding calculating the time spent on each deal stage.

The problem I have is that most of the times we're moving deal stages all at once for deals preventing me to calculate the actual the time spent each deal stage. I know that there is a handy dafault report for this but it doesn't work for me.

So what I did was I create a specific "Create Date" for each deal stage and made it required so anyone, anytime moves a deal from one stage to another needs to enter the Create Date for each deal stage. So I have multiple different Create Date fields for each deal stage.

- Create Data Stage 1
- Create Date Stage 2
- Carete Date Stage 3

How can I create a custom report based on my custom create dates so I can have an accurate time frame spent on each deal stage?

Thank you,

[mdrnd]

Hi Karsten,

As always thanks for being so prompt and helpful. My ideal report would contain 5-6 stages all at the same time in a single report regardless of how many deals each stage holdds.

If you could give a little further guidence on the report design, that would be great.

So for instance:

Stage 1 has 20 deals and we need a report that shows the avarage time spent on this specific deal stage.

Stage 2 has 12 deals and we need a report that shows the avarage time spent on this specific deal stage.
.... etc.

Would be so good to have this in a single report. If not I can create a sepereate report for each stage and put them under the same dashboard.

Thansk very much.

[karstenkoehler]

Hi @mdrnd,

I found a way to display this within one report. First, you would create the calculation properties, as described above.

Then, navigate to Menu > Reports > Reports > Create custom report > Single object and set the Filters and Visualization as below. My calculation properties are called "Time between 1 and 2" and "Time between 2 and 3".

Let me know if you have additional questions!

[Rachel_HWG]

I'm trying to understand why the default fields do not work before I create all these calculation fields. 

I am looking for the time spent in one particular stage of a deal. I'm trying to confirm in this thread if there is something very speficid beyond the default that is being solved for that I do not need. 

Is there a known issue that the default report will not peform these caluclations.  I am using the default report and adding the filter to the one deal stage I need.  I am not getting any results although I know data exist. 

I just wanted to confirm before I build this out. 

[mdrnd]

Hi Karsten,

Just a quick heads up. Built the report but the numbers are wierd. It only shows "24 hours" for avarage, total, maximum and minumum.

When I click on that "24 hours" time spent data, it only shows one deal but there are many deals between two stages that moved from Stage 1 to Stage 2.

Any idea why this isn't populating correctly?

Thanks,

[karstenkoehler]

Hi @mdrnd,

Currently the report requires that deals have been in all stages (see the "is known" Filters). You could try removing them, I'm not sure how the report would behave them then, however.

Best regards!

[mdrnd]

Perfect! Thank you. One last question:

Since we have multiple Create Dates such as:

Stage 1 Create Date (First Deal Stage)
Stage 2 Create Date
Stage 3 Create Date
Stage 4 Create Date (Last Deal Stage)

Does that mean we need to create multiple calculation properties since I need the time between Stage 1 and Stage 2, Stage 2 and Stage 3, Stage 3 and Stage 4?

Also can you walk me through the calculation properties process?

Thanks again.

[karstenkoehler · Accepted solution]

Correct, @mdrnd. 4 stages mean that you need 3 calculation properties.

The initial steps for calculation properties are explained here: https://knowledge.hubspot.com/crm-setup/create-calculation-properties

In the last step (after naming and choosing the property type), select "Time between" from the "Calculated property type" dropdown. You can then enter the dates of the two stages you want to calculate the time in between.

Best regards!

[mdrnd]

Very helpful. Thanks very much.

[karstenkoehler]

Hi @mdrnd,

You can use calculation properties to calculate the time between the create dates. The Time between option calculates the time between two selected date picker properties. You can select your Start date and End date properties.

So in addition to your existing properties, you can create a time between 1 and 2 property, a time between 2 and 3 property and so on.

You cannot report on all of these time between properties in one report. You would create a single number tile, showing the average for the desired time frame.

Let me know if you need further assistance with this!

[RMenezes]

Hi @karstenkoehler are you sure about not being able to visualize them in one report? I created a table and threw them all in as columns and threw 'Original Source' as the first column. Now I can see how much time leads from each source spend in each stage.

[karstenkoehler]

@RMenezes the new properties you're referring to were just introduced a few days ago, my post above is more than two years old 😉 Thanks for pointing this out, I'll update my post shortly.

[RMenezes]

Ahh yes, I missed that. Yeah, I see now. The properties make life so much easier.