Hola! ¡Tenemos nuestra Comunidad en Español!

Report of dates and times spent in deal stage

SOLVE
Occasional Contributor

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? 

 

1 Accepted solution

Accepted Solutions
Highlighted
Top Contributor

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. 

34 Replies
Community Manager

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: 

 

Screen Shot 2017-04-05 at 5.46.24 PM.png

 

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

Reply
0 Upvotes
Occasional Contributor

Thanks May. What would the subscription fee be to view this add on? It is very difficult to find pricing on the website.

Reply
0 Upvotes
Community Manager

Hi @ShireenSc, I'm sorry, I should have specified! The Reporting Add-On is priced at $200/month. You can see all of HubSpot's pricing here: https://www.hubspot.com/pricing/marketing#?currency=USD

 

 

Reply
0 Upvotes
Occasional Contributor

Thanks before we decide to take the full package - is it possible to trial the add ons to see if it's worth our while? I did slect trial fpr 10 days but I still don't have access to the add ons?

Reply
0 Upvotes
Community Manager

Hi @ShireenSc, I took a look at your portal and you actually already tried out the Reporting free trial from November-December 2016. Since this free trial is only available once, it would not be possible for you to sign up for it again.

Reply
0 Upvotes
Regular Contributor

I am having a little trouble getting this wiget to work the way I would like it to. 

 

I am trying to display only few specific deals with the amount of time it has spent with in each stage. So I switch it to total instead of average and created a view to pull in only the deals I would like to focus on.  But it seems that it is not giving accurate times in the stage. Most of the time it is displaying a deal in its current stage as 0 days in stage when I know it has been in a certain stage for well over a week. 

 

Thank you

 

A

Reply
0 Upvotes
Community Manager

@atramer Are you trying to visualize this with a report or a view? COuld you provide screenshots of what you have built, and point out examples of incorrect data within that screenshot?

Reply
0 Upvotes
Regular Contributor

@moonwebstrategy,

Yes, I am trying to visualize this in a bar chart.  Below is how it looks in the dashboard report editor, and the other is how long the deal has been in a certain stage without any data showing up in the chart. 

 

 Cap1.PNGIn the Visualization Editor.Cap 2.PNG

Reply
0 Upvotes
Regular Contributor

From what I have looked at, it is counting the days spent in the previous stage only after it has moved on to the next stage.  This is not a current stage tracking report is it. 

 

am I wrong?

Reply
0 Upvotes
Community Manager

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). 

New Contributor

Hey @moonwebstrategy - is there a way to set up a report to show the ever-changing number of days a deal has been sitting in a given stage? I have been playing around with the reports and can't seem to find a way to show this - I would like to be able to see deals that have been stuck in a stage for an extended period of time as those are the ones that we need to focus on moving on to the next stage (or closing out). 

I'm sure I am not the only one who is looking for something like this, and knowing how long the deal was in the previous stage once it is moved on is only partially useful. 

 

Community Manager

Hi @Erik989, at this time the "Time in deal stage" report calculates the amount of time a deal has spent in a stage once a deal moves to the next stage. In order to calculate the number of days a deal has spent in the current stage, the system would need to be constantly reculculating the number of days in which the deal has been in its stage each time the report loads, which isn't technically feasible at the moment.

 

This is something that the team is aware of, however, and that we would like to revisit in the future. There is an idea posted on the ideas forum on this topic - I would encourage you to upvote/comment on it in order to increase its visibility. Our developers take customer feedback very seriously, and use the forum to prioritize their work on certain areas of the product.

New Contributor

Is there a way to include the timestamp from when a deal enters a stage in the Deals export file? I believe this is a datapoint HubSpot collects (see picture) but it does not seem to be available to export. 

If we knew the time it entered a given stage, we could export that information and run a calculation for how long the deal has been in the current stage - granted this would have to be done manually by the user (for example, this would be something I would pull weekly). This would at least give some insight as to how long a deal has been in a stage that would be helpful for our weekly meetings (I'm sure other users would find this datapoint useful as well). 

 

Is this something that can be exported and I'm just missing it? 

 

 

Deal Moved.JPG

 

Occasional Contributor

@Erik989 wrote:

Is there a way to include the timestamp from when a deal enters a stage in the Deals export file? I believe this is a datapoint HubSpot collects (see picture) but it does not seem to be available to export. 

If we knew the time it entered a given stage, we could export that information and run a calculation for how long the deal has been in the current stage - granted this would have to be done manually by the user (for example, this would be something I would pull weekly). This would at least give some insight as to how long a deal has been in a stage that would be helpful for our weekly meetings (I'm sure other users would find this datapoint useful as well). 

 

Is this something that can be exported and I'm just missing it? 

 

 

Deal Moved.JPG

 


 

 

 

 

 

 

Hi Erik! I'm on the same search as you, sounds like -- this is the closest thing to what you request that I've found so far. But man, I'd love if this was an exported value like you suggest. 

Community Manager

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!

Occasional Contributor

@maypascaud wrote:

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!


Boom bam @moonwebstrategy! I found that yesterday and posted a beautiful and enticing use case. 

Community Manager

@erijohns I saw that, thank you very much Robot Very Happy

Reply
0 Upvotes
Occasional Contributor

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!

Top Contributor

OMG, @erijohns, column G! 

 

You have saved me a ton of time. I majored in physical theatre, so it probably would have taken me just as long to come up with that one. 

 

A million kudos. 

Occasional Contributor

Haha AWESOME! I'm so glad you're loving it. 

 

I've been using this for weekly snapshots for a couple months now, and I've noticed two cases where my formula breaks down (both are rare):

 

1. If the client went live before the contract was signed, Days Since Signed Contract will implode, rather than give a negative number.

 

2. If the client went live in both options on the same day, the formula posts an "ERROR" text like I told it to. I could add another IF() statement to account for this, but so far it's just one client. 

 

Otherwise it works like a dream!

Reply
0 Upvotes
CCS
Regular Contributor

Pipedrive shows current time in deals stage and colours it red when over the intended timeframe.  Is it possible Hubspot can do the same?  This will really help tracking deals sales has to follow up.

Pipedrive time in deal.JPG

 

Occasional Contributor

@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!

CCS
Regular Contributor

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,

Reply
0 Upvotes
CCS
Regular Contributor

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

Reply
0 Upvotes
New Contributor

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

Reply
0 Upvotes
Highlighted
Top Contributor

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. 

New Contributor

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?

Reply
0 Upvotes
Top Contributor

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. 

Reply
0 Upvotes
New Contributor

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

Reply
0 Upvotes