Calculating Time Between Properties

[cooperelias]

Hey everyone - wanted to share something I set up tapping into a new feature that's still in beta I believe: the ability to calculate the difference between two datetime properties.

My goal is to monitor the average amount of time between owner assigned datetime and first contacted datetime so we can see which teams are doing the best at reaching out to new leads in a timely manner.

Here's how I accomplished this:

1. Create custom property for "First contacted" as a datetime property through the API. Set that with a workflow that enrolls when "last contacted is known" and then copies the value from Last contacted to First contacted. The idea now is that we won't update First contacted again, while Last contacted will continue to change.
2. Create custom property for "Time between owner assignment and first contacted" as a calculated property type using the "Time Between" calculation.
   This is a beta feature right now, so you may not see that option yet.
3. Create a custom number property for "Time Before First Contact" and set that with a workflow that enrolls when "Time between owner assignment and first contacted is known" and then copies the value from that calculated property to our new number property.
   Doing this ensures that your property for "Time Before First Contact" stays static, because the calculated value will change if the contact gets reassigned.
   
4. By default, the time between calculation displays in milliseconds (something they are working on changing I think). To make it more useful in reporting right now, I created two additional calculated properties that converted "Time Before First Contact" from milliseconds to minutes (divide by 60,000) and hours (divide by 3,600,000).
5. Create a custom report that displays HubSpot team by Time Before First Contact (average). In my case I used the minutes version, and I also added columns for minimum and maximum values. Here's a screenshot of what that looks like on a dashboard.
   

As you can see, there are a few things to still look into like the min value of 0.1 minutes, but overall I think this will open up a lot of options for creating properties to use for reporting and workflow enrollment criteria.

Notes about filters: I am only including contacts with owner assigned in the past 7 days, but I'll expand that as this continues to run and store the value. I also am only showing our leads with an online original source, not for offline or self-generated leads.

[scottfox]

Love this!!! Thanks so much for sharing!

I've been calculating the time in status on tickets using a spreadsheet and this will be an absolute godsend.

I've got workflows to set the date whenever a ticket enters a particular stage in the pipeline. I can now calculate the time in between the dates. 💥

[cooperelias]

@scottfox - Funny you mention that because I was recently digging into all the default properties for Tickets and noticed that HubSpot automatically calculates "Time in [Status]" on Tickets. It should be something that you can use directly in building custom reports, but it looks like by default they are hidden properties which means they aren't visible on ticket records and can't be used in workflows.

However, I also tried using the API to change a Hidden property to be visible, and it worked! So you might be able to just update those default properties to be visible and then use them in workflows, lists, etc.

Here's one of those properties from my portal:

{
"updatedAt": "2020-07-08T19:28:43.919Z",
"createdAt": "2019-09-10T01:51:47.070Z",
"name": "hs_time_in_912300",
"label": "Time in 'Ready to Process (Membership Opt-Out)'",
"type": "number",
"fieldType": "calculation_read_time",
"description": "The total time in seconds spent by the ticket in the 'Ready to Process' stage, 'Membership Opt-Out' pipeline",
"groupName": "ticketinformation",
"createdUserId": "5339358",
"updatedUserId": "5339358",
"displayOrder": -1,
"calculated": false,
"externalOptions": false,
"archived": false,
"hasUniqueValue": false,
"hidden": true,
"modificationMetadata": {
"archivable": true,
"readOnlyDefinition": true,
"readOnlyValue": true
},
"formField": false
}

And here would be the API request to update the property to no longer be hidden:

curl --request PATCH \
  --url 'https://api.hubapi.com/crm/v3/properties/ticket/hs_time_in_912300?hapikey=YOUR_HUBSPOT_API_KEY' \
  --header 'content-type: application/json' \
  --data '{
  "hidden": false
}'

These properties also exist on Deal records, so the same could be done to trigger things based on amount of time in a certain Deal stage.

[scottfox]

Thanks @cooperelias that's so awesome! This will simplify my life and workflows heaps if we can make this work.

I've made a bunch of these fields visible now, but no tickets seem to have any data in those fields. Did you happen to have data in them when you tried this?

Cheers!

[cooperelias]

@scottfox I noticed that the values weren’t showing when I went to view all properties, but when I clicked details, a value was there.

I don’t have Service Hub so I couldn’t test with tickets. But I did do this for one of my deal stages and I was able to use it for a workflow enrollment trigger.

I’m also not sure why those properties are hidden by default. Maybe someone from the HubSpot team can chime in, but it seems like they would be very useful for enrollment triggers.

Hope it works for you!

[HadarS]

@scottfox @cooperelias @JonathanD - FYI The ability to calculate the time between properties is live (no longer in Beta). Check out how it works here: https://knowledge.hubspot.com/crm-setup/create-calculation-properties. Enjoy!

[Eabhan]

This is so slick, wanted to ensure some of our users saw it whom I thought would be interested based on their intros! @ernopyykko @GWELTD @rachma 

[JonathanD]

Thanks for this, its very nifty and will help with a new sales process I'm implementing for our business growth division.

Having sight of this as a metric does open up areas for improvement and I'm sure a comparison to conversions per team might also be a nice thing to consider, on our side at least.

[HadarS]

@cooperelias  This is so cool!