topic Re: HubSpot Data Share - Email Events in APIs & Integrations

HubSpot Data Share - Email Events

LRoy6 — Fri, 23 Aug 2024 15:06:47 GMT

I am currently in the process of transitioning my company's ETL framework from using the APIs to using the Snowflake data share.

Is there a specific dataset in the data share that can mimic what the Email Events API extracts?: https://legacydocs.hubspot.com/docs/methods/email/email_events_overview

Re: HubSpot Data Share - Email Events

NOlah — Mon, 26 Aug 2024 15:29:59 GMT

Hello @LRoy6,
Thank you for your question! I understand you want to transition your email framework to the Snowflake Data Share mentioned here, is that correct? I also recommend reviewing our documentation on querying HubSpot data in Snowflake, specifically the "Events" data set.

I have also found a similar thread existing in our Community here, where @robertainslie offered some examples of using Marketing Email data in Snowflake. Robert, do you have any suggestions for @LRoy6?

Additionally, let's consult our experts to see if they have any suggestions for you.

Hello @louischausse and @Bryantworks, do you happen to have any ideas that could help @LRoy6 with their goal? Thank you all for contributing.

Best regards,

Noemi

Re: HubSpot Data Share - Email Events

robertainslie — Mon, 26 Aug 2024 19:05:59 GMT

@LRoy6 - the message linked to by @NOlah has a pretty good explanation, check that out. Also, if you haven't already seen it, this is the best piece of documentation that shows how to make many types of queries

Maybe one other clarifying item:
In your Snowflake query/schema the HubSpot marketing email event data is split into individual tables for each event type and you'll have to query them separately or join multiple event types together in order to get a similar output to the the Email Events API.

Example query/join getting the "Click" events for marketing email:

Select contacts.objectid,contacts.property_email,email_clicks.OCCURREDAT,email_clicks.PROPERTY_HS_CLICK_ORIGINAL_URL, email_clicks.PROPERTY_HS_EMAIL_CAMPAIGN_ID from HUB_203693_DB.V2_DAILY.OBJECTS_CONTACTS as contacts join HUB_203693_DB.V2_DAILY.EVENTS_CLICKED_LINK_IN_EMAIL_V2 as email_clicks ON contacts.OBJECTID=email_clicks.objectid

The event types correspond to what you see in the HubSpot Custom Report builder when using the "Marketing Email Activity" data type

These correspond to the following event types, roughly in the 'order' that the events happen for a single send:

EVENTS_HS_SCHEDULED_EMAIL_V2
EVENTS_MTA_BOUNCED_EMAIL_V2
EVENTS_MTA_DELIVERED_EMAIL_V2
EVENTS_OPENED_EMAIL_V2
EVENTS_CLICKED_LINK_IN_EMAIL_V2
EVENTS_REPLIED_EMAIL_V2
EVENTS_REPORTED_SPAM_EMAIL_V2
EVENTS_UPDATED_EMAIL_SUBSCRIPTION_STATUS_V2

Note: these events are for marketing email sends. There are also email send events for Sequence (1:1 automated emails sent by sales reps)

Hope this helps!