As a sales or marketing leader, understanding how many sales touchpoints it takes to get a deal closed is a key information.

Getting that information directly from Hubspot is not straightforward. Fortunately, you can use Actiondesk to transform the raw data you get from Hubspot and feed that data to a Google Sheet for example.

Let's see how to build an automation that will:
Get all fresh engagements data coming from Hubspot
Modify the structure of the table so that each row is a 1:1 relationship between engagement and contact
Get for each contact the stage of the associated deal (assuming there is only one deal per contact) and the id of the hubspot owner
Compute the average number of engagements per contact where dealstage = closed won / lost

Here's what to do step by step:
Import Engagements from Hubspot
Unnest the column contactIds. You now have a 1:1 relationship between engagement and contact (Learn more about the Unnest operation)
Import Deals with the property "Dealstage"
Import Contacts with the property "Hubspot_owner_id"
In Deals, unnest the column "AssociateVid" (Hubspot nomenclature can be confused, vid is the same thing as contactId)
Go back to the table Engagements, do a vlookup to get the dealstage, so with the following parameters: contactIds, associatedVids[Deals], dealstage
Filter your engagements to take only the ones where the associated contact is in the dealstage Closed Won or Lost. Add a filter with the following expression: OR(dealstage_deals="closedwon",dealstage_deals="closedlost")
Add Pivot table to count the number of engagements for each contact, so with the following parameters: contactIds, COUNT
Add a vlookup to get the hubspot_owner_id from contacts, so with the following parameters: contactsIds, vid[contacts], hubspot_owner_id
Then add a second table to get the average number of touch points per contact for each owner, so with the following parameters: hubspot_owner_id_contacts, AVERAGE, COUNT_of_contactIds
Finally, let's the program the push to Google sheets. Click on Program Actions
Choose the "Create or Update" Action, the Google Sheet you want to use, and the relevant tab (this tab should have two headers: Owner_id and Average_touch_points)
Choose your matching keys, when actiondesk updates the data, this is how it will recognize which row to update. In this instance, Googlesheets key should be Owner_id, and Actiondesk key should be hubspot_owner_id_contacts
Add a column match: here the Googlesheets key should be Average_touch_points and the Actiondesk key should be AVERAGE_of_COUNT_of_contactIds

And you're done, you can just click on run once to see it in action. Depending on your number of engagements, it might take a few minutes.

Last but not least, schedule and publish your workflow so that it runs a chosen frequency.
Was this article helpful?
Thank you!