Report of dates and times spent in deal stage

[ShireenSc]

I woul dlike to evaluate how long a deal stays in one stage for example how long it took our sales person to go from contatcting a cutsomer to giving them a quotation? If i go into a deal, the information is there. It shows:

Deal XYZ moved to S2 - Quotation December 7th at 2:43 pm

How do I view this information in a report for all deals lodaded? 

[erijohns · Accepted solution]

---

@maypascaud wrote:

Hi @atramer, you are correct - the Time in deal Stage report does not display the time that deals have spent in their current stages; it only displays the number of days spent in a deal stage once the deal has moved onto its next stage. This is so that the report can provide an absolute value of the number of days a deal has spent in a given stage (and not an ever-changing one based upon the current amount of days a deal is in its stage). 

---

This explains so many of my data questions -- thank you!

[MayPascaud · Accepted solution]

Thanks very much for linking to that developers forum post @erijohns - indeed, at this time, getting the version history of the properties through the Deals API would be the best way to extract this information from HubSpot. That being said, if you would like to have the ability to export this information from your portal / visualize it more effectively, please upvote and comment on this idea on our Ideas Forum with your use case!

[erijohns · Accepted solution]

Hi everyone!

I'll post this on a couple channels, because I figured out a Google Sheets workaround to this question and A) it's solving our need and B) it's a beastly formula and I'm proud of myself. 

Our use case, in case yours differs: we wanted to know how long it was taking our Deals to move from Signed Contract to Live, inclusive of Deals that hadn't yet gone live. We wanted the total count. Maybe we're really good at signing clients, and maybe we're really good at getting 20% of our signatures live in two weeks, but if 80% of our clients are languishing in implementation limbo, we really want to know. 

Hubspot counts Time in Deal Stage as a static number -- it tracks the time in deal stage once a deal has moved on to the next stage, so we couldn't get the dynamic number ("Deal X has been stuck in Signed Contract for 200 days") without a couple workarounds. 

Here's what I did: 

1. I filtered my Deals view appropriately, including the correct pipeline and these three fields: Signed Contract Date, Option A Go Live, and Option B Go Live (there are two ways to take our product live). 

2. I emailed the view to myself and downloaded the .csv file. 

3. I uploaded the .csv to Google Sheets and scrubbed it of unecessary data (you could do this in Excel too, of course). 

4. And bam, I had a list of all clients with a Signed Contract date and either a Go Live date or no Go Live date, which meant they were stuck. 

5. I went on a 10-day spirit quest to learn how to use the DATEDIF formula in a nested IF statement. If you didn't major in English like I did you can probably do this faster. 

6. I figured out how to use one formula to calculate either the time between Signed Contract and Live or the Signed Contract and Today. (hint; there's a TODAY() formula as well). 

That gave me my magic. Now I weekly go through those steps (I created a template to make it easier) so that we can track our averages over time. We just have a weekly snapshot of where we sit. 

If you want to see or steal the formula I've created a public Google Sheet here with some data in it. 

Solving this puzzle has been extremely rewarding to me -- it's unlocked some insights that we never had as a company. I hope this helps someone else. Enjoy!

[kcooper · Accepted solution]

Dude...YES!

Now that you can enroll deals in workflows, this is so much easier.

Create a Date Picker deal property. I called mine "Date entered current deal stage" (super exciting name, right...). 

Write a Deal-based workflow that looks like this:

Enrollment criteria = Deal stage - has been updated in the last 1 day AND Deal stage - is any of (enter one deal stage name here - and only one...you will see why later).

Set a property value - Set a date stamp for Date entered current deal stage (or whatever you named your field).

Then clone it and make a workflow for every deal stage you have. 

Why not just make a workflow that is only based off of Deal stage - has been updated in the last 1 day - you ask? Because right now there is no re-enrollment of deals into a workflow. Sooooo the first time it runs through that workflow will be its last. This is why we make workflows for each stage.

Note: This isn't foolproof. Because there is no re-enrollment, if you have a rep who likes to move things in and out of the same deal stage, this date is going to reflect the last date they moved the deal into a stage for the first time. So say Elliott moves a deal from A to B to A - the date is going to reflect the date they moved the deal into B. If they then move it into C, the date will be updated to reflect that date...but if they move it back into B it will still reflect the C date. Get it? (Got it!) Good. 

THIS IS NOW AN EXPORTABLE FILED! No more messing with APIs and trying to convert unix timestamps. So you can export a list of deals from HubSpot and then use sheets or excel to do a quick formula displaying the time between the Date entered current deal stage and the current date. 

This is still a manual process at the end of the day, and it isn't foolproof because you can't re-enroll deals, but hot **bleep** is it better than pulling the Date entered date from the API...especially for us non-super-techie folk. 

[PDuque]

I suggest using GoTellMe Calendar Toolkit https://gotellme.com/which-functions-does-calendar-toolkit-have

https://hubspot.gotellme.com/CalendarToolkit/.

With it you can populate new properties calculated with values from other properties, and then used them on a report or any where else you need it.

[arcbright]

One of the free add on tools from Methoddata.com has a pipeline velocity feature. There some other good add ons as well. I think everything is free at the moment.

[grahamfcr · Accepted solution]

Create new properties for each deal stage call them {"timestamp - [deal stage name]}. Set the properties as date properties.

Create a workflow that timestmaps the new property when a stage is moved.

Example,
If deal stage is opp
yes,
then set "timestamp - opp" to date of step
no,
if deal stage reached out 
yes,
then set "timestamp - opp" to date of step etc..

You can then create rules around how long sales deals are lagging in stages.

[gkeshian]

Our app, Sales Rep Scorecards, calculates the amount of time that deals are spending in each stage automatically.  The app is available through the HubSpot Connect directory.

You can visualize this in Gantt charts like the screenshot below, either overall, or by sales rep.  You can also export your deal data to see the dates each deal moved into every stage.

Tracking the amount of time HubSpot deals spend in each deal stage

[CCS]

We just feel we pay so much for Hubspot why can't they have it as part of their CRM like Pipedrive?  Why pay for another app??

[CCS]

Will this work if we do not have sales reps but a contact centre all working on deals and stages?  We just need to see how long each deal is in a stage as per the Pipedrive example.

Will your software be able to show us deal time in stage?

[snwalker]

Just saw this post. I am the CEO of MethodData. We can absolutely help you with this. I will send you a message offline.

[cassiopagnoncel]

Trying to extract these timestamps using API. It's quite hard. How have you guys solved this?

[kcooper · Accepted solution]

Dude...YES!

Now that you can enroll deals in workflows, this is so much easier.

Create a Date Picker deal property. I called mine "Date entered current deal stage" (super exciting name, right...). 

Write a Deal-based workflow that looks like this:

Enrollment criteria = Deal stage - has been updated in the last 1 day AND Deal stage - is any of (enter one deal stage name here - and only one...you will see why later).

Set a property value - Set a date stamp for Date entered current deal stage (or whatever you named your field).

Then clone it and make a workflow for every deal stage you have. 

Why not just make a workflow that is only based off of Deal stage - has been updated in the last 1 day - you ask? Because right now there is no re-enrollment of deals into a workflow. Sooooo the first time it runs through that workflow will be its last. This is why we make workflows for each stage.

Note: This isn't foolproof. Because there is no re-enrollment, if you have a rep who likes to move things in and out of the same deal stage, this date is going to reflect the last date they moved the deal into a stage for the first time. So say Elliott moves a deal from A to B to A - the date is going to reflect the date they moved the deal into B. If they then move it into C, the date will be updated to reflect that date...but if they move it back into B it will still reflect the C date. Get it? (Got it!) Good. 

THIS IS NOW AN EXPORTABLE FILED! No more messing with APIs and trying to convert unix timestamps. So you can export a list of deals from HubSpot and then use sheets or excel to do a quick formula displaying the time between the Date entered current deal stage and the current date. 

This is still a manual process at the end of the day, and it isn't foolproof because you can't re-enroll deals, but hot **bleep** is it better than pulling the Date entered date from the API...especially for us non-super-techie folk. 

[cskelly]

What's the proper way to export the list? Having a bit of trouble with it.

[mranney]

Great stuff, super helpful - so thanks!

Quick question - how are you triggering the first date property to set off the first enrollment criteria set for each stage in the pipeline?

[cassiopagnoncel]

Almost there mate, I'm looking to have the datetime for _every_ deal stage, so

- qualification_datetime

- presentation_datetime

- negotiation_datetime

- closed_datetime

Something like this. Any idea?

[LaurenMca]

Hi All

Sorry I'm very new to hubspot and am self taught on all of this. Where do I go to set the date stamp property? I can't find the option when I'm setting up the workflow.

many thanks

[HubSpotMaster]

@LaurenMca Your post was a long time ago but you would need to create these date stamp properties individually for each stage

[kcooper]

Yeah - you can make a Date Picker deal property for every deal stage you have, and in the workflows you can have it set the deal property for that stage instead of setting and resetting a field. 

[cassiopagnoncel]

Alright will try this. Have you guys been successful retrieving this via API?

[kcooper]

Yeah, using the steps that one of the posters (erijohn) put up - It is a PITA....but more accurate, since using deal workflows to set date fields only works the first time the deal enters that stage. Once we are able to re-enroll deals in workflows then this way will be just as accurate, and infinitely more accessible. 

HubSpot does a good job of reporting Time in Stage (after the deal has moved out of the stage) in it's own reporting system. The only reason I needed to pull from the API was to find time in CURRENT deal stage. 

[MayPascaud]

Hi @ShireenSc, thanks for reaching out!

So, you wouldn't have the ability to build such a report under your current free subscription.

However, there is a report within our Reporting Add-On that accomplishes this goal: it is called "Time in deal stage" and allows you to view the average time during which a deal stays in each stage. Here is what it looks like in the reporting add-on dashboard: 

You can learn more about our reporting add-on here: https://knowledge.hubspot.com/articles/kcs_article/reports/what-widgets-are-available-with-the-repor...

Best,

May

[CCS]

What is the best date to use to see average time spend when deals are not closed yet - create date?

[CCS]

We use the average deal in stage report, but need to see each deal's time in stage as you can in Pipedrive.  Is this spossible? If not, can the date from when deal entered a stage be exported?

Thanks,