HubSpot Data Share - Email Events

[LRoy6]

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

[robertainslie · Accepted solution]

@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!

[NOlah]

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