Creating Dynamic Time Buckets (Rolling Dates) for Count of Deals

[mdrnd]

Hi everyone,

Trying to create time buckets for count of deals. Specifically I'm interested in creating a report to see how many deals created in the last 30 days, 60 days, 90 days, etc. But here is the problem  that I'm having:

I'd like to create dynamic time buckets (rolling dates) such as 0-30 days, 31-90 days, 91-120 days etc.

When I use the rolling date it only gives me last 30 days, last 60 days, last 90 days, etc. The problem is that Last 60 days also covers the last 30 days. And Last 90 days also covers Last 60 days and Last 30 days. But I want to see only the number of the deals created in the last 31-60 days for instance. This is a dynamic range not a specific time between two dates.

To further clarify on this: I'd like to create a report to see the number of deals that are 30 days older, 60 days older, 90 days older, etc...

I also tried to find the more than X days filter but I couldn't locate.

Can anyone assist on this?

Thank you,

[karstenkoehler]

Hi @mdrnd,

There might be an easy solution if you're okay with adjusting your starting requirements a bit. Since you're buckets are monthly, a custom report that shows deals by Create date with a monthly frequency. To specify, on the y-axis you would see the Count of deals, on the x-axis you'd see the Create date by month. Let me know if you need assistance with this report. (A lot of variations already exist under Reports > Reports > Report library, simply tick the Deals box in the left sidebar.)

Alternatively, I'd recommend exploring Reports > Analytics Tools > Sales Analytics, as there are also reports for Deals created in here.

If you want to assign deals a bucket, as per your original question, you would have to set up an elaborate system of workflows, tagging deals with a custom property value and moving them between buckets. It would be unnecessarily complex to set up and probably a pain to maintain.

Best regards!

[mdrnd]

Hi Karsten,

Thanks for the answer. Could you please explain the method further you'd use to create the time buckets? This is an important report for me so I'd rather prefer to cretate a custom report other than relying on the templates. I've checked most of the pre-created reports and none of them works for the time buckets the way I explained above.

Thank you

[karstenkoehler · Accepted solution]

Hi @mdrnd,

You would have to set up a custom property with options for each bucket. You would then create deal-based workflows that enroll deals based on their Create date for each bucket: is less than 31 days ago, is more than 30 days ago but less than 61 days ago, is more than 60 days ago but less than 91 days ago etc. You would then set the property value of the custom property from above to bucket 1, 2, or 3. I haven't tested this and there might be enrollment issues but that's how I'd approach it.

(If I were you, I'd go with a custom report that shows the number of deals per month. Less prone to error, proven to be stable.)

Best regards!

[mdrnd]

Hey everyone,

Any suggestion on this?

Thank you

[Josh]

Hi @mdrnd,

This is definitely tough in the report builder and a challenge I've been presented with before. The only workaround I've found within HubSpot is to create a custom property that changes using a workflow. For example, delay for 30 days and change a property to show 30 days out, delay for 60 days change the property to 60 days out. You can then use this property in correlation with other filters in your reporting.

I hope this helps! 

Thank you for the ping @TiphaineCuisset 

Josh

[mdrnd]

Thansk Josh. I tried this already not a really good work-around.

Does anyone have any other suggestions?

Thank you

[TiphaineCuisset]

Hi @mdrnd 

Thank you for reaching out.

I want to tag some of our experts on this - @danmoyle @Josh @Dan1 do you have any thoughts for @mdrnd on this? 

Thank you!

Best

Tiphaine