Calculate deals count over a given period of time

[ArnaudA]

Hi Community !

I am currently trying to create a property that calculate the deals count over a specific period of time. 

What I am trying to achieve : 

- Calculate deal count for the first quarter of the year

- Calculate deal count for the second quarter of the year

- Identify contact (create a list) that have a lower deal count for the second period of time (second quarter < first quarter)

I have a custom property on deal which is "Order date" that I would like to use as a condition for a calculation property, but it does not seems possible.

I don't see any ways to achieve this (I am able to do it on report, but there is not way to create a list after).

Do you guys already face the same needs, and how have you been able to achieve it?

Thanks in advance!

Regards

[karstenkoehler · Accepted solution]

Hi @ArnaudA,

This can indeed be achieved with calculation properties: https://knowledge.hubspot.com/properties/create-calculation-properties

I've explained how to solve the first two of your bullet points here: https://community.hubspot.com/t5/CRM/Create-property-that-sums-deals-over-specific-date-range/m-p/10...

Your third point would be another additional custom calculation property of type boolean, for example, using an IF function to check whether one property is greater than the other and then returning true, if not return false.

Give it a try and let me know if you're getting stuck and if so where!

[karstenkoehler · Accepted solution]

@ArnaudA those steps are correct, yes 🙂

To your first question, you would need Operations Hub Professional and daily scheduled workflows if you want a dynamic date ranges (e.g. last 90 days vs previous date range). These daily workflows would check every day which bracket a date / record falls into.

To your second question, this would be an additional calculation property, see my previous reply.

using an IF function to check whether one property is greater than the other and then returning true, if not return false.

You would select custom equation as calculation type. Give it a try and let me know if you're getting stuck. https://knowledge.hubspot.com/properties/create-calculation-properties#functions

[karstenkoehler · Accepted solution]

Hi @ArnaudA,

This can indeed be achieved with calculation properties: https://knowledge.hubspot.com/properties/create-calculation-properties

I've explained how to solve the first two of your bullet points here: https://community.hubspot.com/t5/CRM/Create-property-that-sums-deals-over-specific-date-range/m-p/10...

Your third point would be another additional custom calculation property of type boolean, for example, using an IF function to check whether one property is greater than the other and then returning true, if not return false.

Give it a try and let me know if you're getting stuck and if so where!

[ArnaudA]

Hi @karstenkoehler,

Thanks a lot for this detailed answer!

If I got it correctly, I will have to: 

1. Create a multiple checkboxes property to select the desired time range.
2. Setup a workflow that will assign the correct time range to the property created at step 1
3. Create a specific custom calculation property (for me, at contact level) for each time range I want to calculate the deal count (using property created at step 1 to setup an additional condition).
4. Create the final custom calculation property that will determine if deal count is decresing over time.

I then got few questions about this flow:

1. What if I want to "automatize" the time range that will be taken in account? For exemple, if I want to identify contact that have a lower deal count in the previous 6 months than in the 6 months before those 6 months.
2. How is HubSpot able to verify that a select option of a property is greater that another select option of the same property?

Thank you once more on the really helpfull insights!

[karstenkoehler · Accepted solution]

@ArnaudA those steps are correct, yes 🙂

To your first question, you would need Operations Hub Professional and daily scheduled workflows if you want a dynamic date ranges (e.g. last 90 days vs previous date range). These daily workflows would check every day which bracket a date / record falls into.

To your second question, this would be an additional calculation property, see my previous reply.

using an IF function to check whether one property is greater than the other and then returning true, if not return false.

You would select custom equation as calculation type. Give it a try and let me know if you're getting stuck. https://knowledge.hubspot.com/properties/create-calculation-properties#functions

[ArnaudA · Accepted solution]

Hey @karstenkoehler , once again, this is bringing a nice solution! What I'm going to do : 

1. Create a boolean property at deal level : Purchased between the last 12 and 6 months
2. Create a boolean property at deal level : Purchased in the last 6 months
3. Setup a daily workflow that will verify time range deal has been created
4. Create custom calculation property (for me, at contact level) for each Time range I want to calculate the count.
5. Create the final custom calculation property (comparing previous 2 calculation property) that will determine if deal count is decresing over time for a contact.

Solution is complete for me, thanks a lot.

[kennedyp]

Hi @ArnaudA! Welcome to the Community-- happy to have you here 😊

My initial thought would be a report too, but I can see the disconnect when our end goal is a list. @karstenkoehler & @JBeatty provide some great comments in this thread explaining how you can create a calculation property based on the total number of deals associated with another object. That may be a good place to start and then see what options you have for filtering by that order date 

Best, 
Kennedy

[ArnaudA]

Hey @kennedyp , thanks a lot for the welcome and pointing this topic out!

It's really helpful on how to setup a custom count property.

However, I'm currently struggling on how to add some date parameter as additionnal condition. This last point is blocking my process and expected results. 

@karstenkoehler put me on a good track, which I need to finalize.

Thank you once more,

Arnaud