Calculated company property total revenue last 18 months

[kelseyingram392]

In order to properly segment our data, we need to know total revenue over the past 18 months on both the contact and the company record. Giving levels are important metrics for nonprofits to report on and treat donors accordingly. However it appears that you can't select date fields as a criteria for a calculated property, how is that possible??

[AM8]

@kelseyingram392 : I hear you! So using Salesforce is not an option? 

@Josh @Phil_Vallender  do you have a work around for this? 

[Josh · Accepted solution]

Hi @kelseyingram392,

The fact that you can't filter based on date does make it tough. Here's the only thing I can think of as a workaround.

When a donation is given, have a closed won stage that is something like: Recent Donation Given. Donations/deals will only live in that stage for 18 months. After 18 months it is moved to a stage like Old Donation Given. This is for closed won beyond 18 months. You can use workflows to move these around.

Once that is accomplished you should be able to do a sum type of calculated property and use a filter of: Deal Stage == Recent Donation Given.

I hope that helps or at least points you in the right direction.

Thanks for the ping @AM8!

[kelseyingram392]

Hi @AM8 , 

The fields are currently needed at the Contact level (individual donors), but would be needed at the Company level (corporate donors) in the future as well.  The fields would likely be the same. 

This data needs to be dynamically calculating at least daily.  

We're trying to avoid using an external system for reporting, since the results of the calculated properties would then be used to drive segmentation (e.g. once a donor reaches a new giving level threshold this year, send a special thank you email). 

These calculations are extremely easy to do in Salesforce with rollup summary fields, but the fact that HubSpot seems to 1) only allow one condition, 2) not allow date fields as a filter, and 3) doesn't allow relative dates is a shocking barrier!!

[AM8]

@kelseyingram392 
Can you please share more details about the calculated fields you are using on the contact and the company level. Are they are same or different?

Is this a one time report that you need or do you need this data monthly ?

Are you using only Hubspot for your reporting or can you connect to another BI tool via API or Zapier? 

[kelseyingram392]

Hi @JessicaH @Bryantworks @mikedupuy @AM8 , 

Any chance you've gotten a chance to think about this? I tried using a workaround Deal workflow to update the Contact record to try to create the rollup but you can't do cross-object workflows. I'm running out of ideas and would appreciate any suggestions!

[JessicaH]

Hi @kelseyingram392,

Thanks for reaching out.
I want to tag in some thought leaders to see if they can assist with this.
Hi @Bryantworks @mikedupuy @AM8, would you be able to share your thoughts?

Thanks,
Jess