Sequence Activity by Contact

[MJohnson59]

In our internal analytics process we extract Hubspot Emails (via API) that are to one of our inside sales reps from a Contact or from one of our inside sales reps to a Contact, that are not the result of a Sequence.

For any emails that are directly from a sequence we just want a rolled up aggregate of how many were sent, how many were opened, etc for each hubspot contact.

I can get pretty close by doing a Single Object Report focused on Sequence Enrollments, however we are limited to the Contact properties we can extract, it only includes email. 

I then tried the Custom Report Builder, but any combination of Contacts or Sequences as primary heavily filters the results to only some subset of data (e.g. only clicks, only bounces, etc). 

Is there a way to create a report that would have the following output:

Conctat Record ID	Sequence Name	Contact Property_XYZ	Email Sent	Email Open	Email Click...etc
123	Email Sequence	Data Data Data	1	0	0

[RubenBurdin · Accepted solution]

I'm out of ideas at the moment, but do try to play with the field order in the table, as it usually works from the biggest to the smallest number. For example, if your first column is a user ID, this might bring in the entire user sequence activity if you don't filter it in any way. In the same way, if you use the sequence name as the first column and the user ID on the second, in theory it should break that sequence information down to the individual contact level. However, I'm not 100% sure as I haven't tested all these scenarios.

[RubenBurdin]

If I understood your requirement, this should be doable by building a custom report with Contacts and Sequences as data sources.

Set the report as a table and then map in the columns the data points that you want.

Make sure to add a filter that the Sequence Name is known (so you don't list contacts that weren't enrolled in sequences), as well as any other data point you need to make sure you have in that report.

I got a report showing the contact name and ID, the job title, and all those sequence metrics.

Does this help?

[MJohnson59]

@RubenBurdin Much appreciated on the feedback. 

I set this up using Contacts as the Primary and Sequences as the secondary. The problem is that the Sequence related aggregations are for the entire sequence, not the specific contact. Example screenshot attached. 

[RubenBurdin]

I think you need to add the sequence name for each row, as otherwise, yes, you get the full sequence data.

[MJohnson59]

Took a look at that option, unfortunately didn't work. 

I got slightly closer though. When choosing properties from Sequences you have an option to select particular sequence event types (this is both helpful and problematic). If I choose sequence step executions the aggregate corresponds to the number of emails sent via that sequence (in this case because we know each step is an email). 

However, any sequence email related metrics (Open, Clicks, etc) are still the entire sequence as a whole, not per contact. I even added in the timestap of the sequence step in the hopes that it might work as you thought if I broke it down one step further, but no luck.