topic Re: How to view dates when deals move to a new stage in a report in Dashboards & Reporting

Report of dates and times spent in deal stage

ShireenSc — Thu, 06 Apr 2017 20:56:57 GMT

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?

Re: How to view dates when deals move to a new stage in a report

MayPascaud — Wed, 05 Apr 2017 21:50:07 GMT

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-reporting-add-on

Best,

May

May. What would the Re: How to view dates when deals move to a new stage in a report

ShireenSc — Mon, 10 Apr 2017 12:19:48 GMT

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

Re: May. What would the Re: How to view dates when deals move to a new stage in a report

MayPascaud — Tue, 18 Apr 2017 18:02:47 GMT

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

Re: May. What would the Re: How to view dates when deals move to a new stage in a report

ShireenSc — Mon, 08 May 2017 08:30:21 GMT

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?

Re: May. What would the Re: How to view dates when deals move to a new stage in a report

MayPascaud — Thu, 11 May 2017 22:07:02 GMT

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.

Re: How to view dates when deals move to a new stage in a report

atramer — Wed, 31 May 2017 19:47:02 GMT

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

Re: How to view dates when deals move to a new stage in a report

MayPascaud — Wed, 31 May 2017 21:02:30 GMT

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

Re: How to view dates when deals move to a new stage in a report

atramer — Thu, 01 Jun 2017 18:44:46 GMT

@MayPascaud,

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.

In the Visualization Editor.

Re: How to view dates when deals move to a new stage in a report

atramer — Thu, 01 Jun 2017 19:00:59 GMT

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?

Re: How to view dates when deals move to a new stage in a report

MayPascaud — Mon, 05 Jun 2017 12:40:24 GMT

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

Re: How to view dates when deals move to a new stage in a report

Erik989 — Tue, 13 Jun 2017 20:46:32 GMT

Hey @MayPascaud - 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.

Re: How to view dates when deals move to a new stage in a report

MayPascaud — Mon, 19 Jun 2017 17:09:57 GMT

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.

Re: How to view dates when deals move to a new stage in a report

erijohns — Tue, 20 Jun 2017 16:59:26 GMT

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

Re: How to view dates when deals move to a new stage in a report

Erik989 — Tue, 20 Jun 2017 17:53:03 GMT

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?

Re: How to view dates when deals move to a new stage in a report

erijohns — Tue, 20 Jun 2017 19:44:11 GMT

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

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.

Re: How to view dates when deals move to a new stage in a report

MayPascaud — Wed, 21 Jun 2017 15:48:03 GMT

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!

Re: How to view dates when deals move to a new stage in a report

erijohns — Wed, 21 Jun 2017 20:30:59 GMT

@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 @MayPascaud! I found that yesterday and posted a beautiful and enticing use case.

Re: How to view dates when deals move to a new stage in a report

MayPascaud — Wed, 21 Jun 2017 20:33:37 GMT

@erijohns I saw that, thank you very much

Re: How to view dates when deals move to a new stage in a report

erijohns — Thu, 29 Jun 2017 20:01:13 GMT

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!