Report on historic average of property

[LPrivett]

I have created a Health Score for all of our companies. I can see the history health scores of any individual company. I can report on the *changes* to the health scores of each company. I want to report the *average* health score of all companies, in any given time period. E.g. produce a simple line graph showing the average health score, filter-able by other company properties (e.g owner or country). I can't seem to work out how to do that - any ideas?

[JessicaBaskey]

Hi Louise, 

May I ask how you report on the changes to the health score of each individual company? Do you have historic values of it saved in any way?

I assume the health score is a custom property?

I'd be happy to help think of a solution. 

Best regards, 

Jess

Baskey Digitak

[LPrivett]

Hi Jessica, 

It's a custom property yes, a Hubspot-calculated score. I can see the history of it on an individual company by finding the property on the company and clicking 'details' to see its history (or alternatively, 'view property history' on the left sidebar on the record, and find 'health score'.

I've also set up a custom event for the score changing, as I saw a Hubspot influencer post saying this could be used to report on history. So I can pull a report of every time the score has *changed*, by company.  https://www.linkedin.com/posts/meetshadab_hubspot-revops-hubspottipsandtricks-activity-7228701759634...

The data is clearly in HS to show the average health, filterable by any company property I want (territory / size etc), but I just can't figure out how to display it without exporting and doing some finnicky excel work that my laptop will not like in a few months' time when the dataset is too big.

Thanks,

Louise

[JessicaBaskey · Accepted solution]

Hi Louise,

Thanks for the additional information.
From what I know, you can not report on historical property values in HubSpot, only the current value of the property. So if a contact’s health score is 50 now and was 25 last month, we can only report on the 50 and can’t calculate an average between 50 and 25 UNLESS that 25 is stored in a separate property.

I think to make this happen you need to create a custom number field for each month of the year called health score Jan, Health score Feb, Health score March, Health score April and so on.
You would then need to export the health score property history and import it into these fields.
Then you could create a calculated property that finds the average of all these fields.

Best regards,
Jess