topic Re: Snowflake Data Share ERD in 💬 RevOps Discussions

Snowflake Data Share ERD

lanza_apollo — Wed, 27 Dec 2023 22:37:06 GMT

I'm looking for some kind of ERD to help make sense of the relationships between the tables available to me in the Snowflake DataShare that is set up. Specifically trying to understand how to view email campaign performance. I see within the various events_hs_scheduled_email_v2 and other events_ email tables there is a property_hs_email_campaign_id field. Ideally I would like to view deliveries, opens, etc. on a campaign basis. Unfortunately I cannot figure out how to tie this to any of the campaign tables that I think it could be

with campaign_ids as ( select distinct property_hs_email_campaign_id as campaign_id from crm_platform_data_from_hubspot.v2_daily.events_opened_email_v2 order by 1 asc ) , associations as ( select * from crm_platform_data_from_hubspot.v2_daily.associations ) select * from associations inner join campaign_ids on associations.toobjectid::varchar = campaign_ids.campaign_id::varchar limit 100

The sample sql code should return any association that I can join the property_hs_email_campaign_id to. Am I missing some kind of mapping table? Ultimately I'm trying to go from objects_campaigns ->emails_delivered, emails_opened, etc.

Re: Snowflake Data Share ERD

Jaycee_Lewis — Thu, 28 Dec 2023 22:57:49 GMT

Hi, @lanza_apollo 👋 Welcome to our community! I'd like to invite a few of our community members to the conversation — hey @johnelmer @Shadab_Khan @ChristinaKay, do you have any experience with the type of question @lanza_apollo is asking?

Thank you for taking a look! — Jaycee

Re: Snowflake Data Share ERD

lanza_apollo — Tue, 02 Jan 2024 15:02:12 GMT

Hi @Jaycee_Lewis thank you for passing this along. To further elaborate, I'm really looking for any documentation/direction on how to associate emails delivered, opened, etc. with campaigns. An ERD would be great but really any documentation will do, especially sample SQL code.

-Thank you!

Re: Snowflake Data Share ERD

robertainslie — Wed, 03 Jan 2024 04:31:12 GMT

Hey @lanza_apollo ,

Rob A. from HubSpot's Solution Architecture team here.

This is a great question. It definitely takes some data exploration and knowledge of how HubSpot structures data to answer your query.

To my knowledge there is not a full ERD published that visualizes every relationship of data made available in the Snowflake data share. However, here are a few tips:

If you haven't already seen it, this is the best piece of documentation that shows how to make many types of queries
The Snowflake Data Share is essentially a replication of data that powers the HubSpot Custom Report Builder. As such, you can use the Report Builder to understand what data likely relates to other data. The UI shows what data can be related to other data. Additionally, there's a section that shows how the data join query will return records
For your specific question, you likely are interested in data from Marketing Emails, Marketing Email Events, and Campaigns (you may also be interested in joining to Contacts object if you want to know which Contacts opened the email).
I dug into this query, and I haven't done the aggregations, but I think this query will get you close to what you want (the tables are aliased to my portal, so you'll have to alter the table and view names). I think you will need to group by the Campaign Names and aggregate count by the unique email events

select HUB_203693_DB.V2_DAILY.OBJECTS_MARKETING_EMAILS.OBJECTID as "Marketing Email ID", HUB_203693_DB.V2_DAILY.OBJECTS_MARKETING_EMAILS.PROPERTY_HS_NAME as "Internal Name of Marketing Email", HUB_203693_DB.V2_DAILY.OBJECTS_MARKETING_EMAILS.PROPERTY_HS_ORIGIN_ASSET_ID as "Content ID of Marketing Email", HUB_203693_DB.V2_DAILY.OBJECTS_MARKETING_EMAILS.PROPERTY_HS_CAMPAIGN_NAME as "Internal ID of Campaign Associated to Marketing Email", HUB_203693_DB.V2_DAILY.EVENTS_OPENED_EMAIL_V2.ID as "Email Open Event ID", HUB_203693_DB.V2_DAILY.OBJECT_WITH_OBJECT_PROPERTIES.PROPERTIES['hs_origin_asset_id'] as "HubSpot Campaign ID", HUB_203693_DB.V2_DAILY.OBJECT_WITH_OBJECT_PROPERTIES.PROPERTIES['hs_name'] as "HubSpot Campaign Name" from HUB_203693_DB.V2_DAILY.OBJECTS_MARKETING_EMAILS join HUB_203693_DB.V2_DAILY.EVENTS_OPENED_EMAIL_V2 ON HUB_203693_DB.V2_DAILY.OBJECTS_MARKETING_EMAILS.PROPERTY_HS_ORIGIN_ASSET_ID = HUB_203693_DB.V2_DAILY.EVENTS_OPENED_EMAIL_V2.PROPERTY_HS_EMAIL_META_CONTENT_ID join HUB_203693_DB.V2_DAILY.OBJECT_WITH_OBJECT_PROPERTIES ON HUB_203693_DB.V2_DAILY.OBJECTS_MARKETING_EMAILS.PROPERTY_HS_CAMPAIGN_NAME = TO_VARCHAR(HUB_203693_DB.V2_DAILY.OBJECT_WITH_OBJECT_PROPERTIES.PROPERTIES['hs_origin_asset_id'])

I think the most important things to highlight:

The Object Ids of Campaigns and Marketing Emails are different from the 'content' or 'asset' IDs that are actually used by the HubSpot product (there's some legacy software reasons for this)
Both the Campaign and Open/Click email events use these alternative IDs for their joins NOT the object IDs of those records (you can see this in the SQL I shared)
The Content ID of the Marketing Email is what you would add to a HubSpot URL to actually see the email in reference: https://app.hubspot.com/email/{Hub ID}/details/{Email content ID}/performance
The Campaign object data provides a nested JSON object with details about the campaign, which I had to extract into separate columns to get the HubSpot Campaign ID
Also very critical to know: when you're working with the email event data (Opens, Clicks, etc), there is a column that is called "PROPERTY_HS_EMAIL_CAMPAIGN_ID" - this is a DIFFERENT "campaign ID" to the campaign ID we are referencing above. One of these is an ID used to track unique instances of sending an email (for things like A/B sends, Batch sends, Automated sends) and the other is what you see when you navigate to the "Campaigns" tool in HubSpot.

Here's another helpful query: showing pulling the Campaign data. The "OBJECTTYPEID" can be used to only query data about the Campaign

select *, HUB_203693_DB.V2_DAILY.OBJECT_WITH_OBJECT_PROPERTIES.PROPERTIES['hs_origin_asset_id'] from HUB_203693_DB.V2_DAILY.OBJECT_WITH_OBJECT_PROPERTIES where OBJECTTYPEID = '0-35'

Hope this is helpful - let me know how you get on with this!

Re: Snowflake Data Share ERD

lanza_apollo — Wed, 03 Jan 2024 18:37:32 GMT

Thank you so much @robertainslie . After playing with this I was able to answer my inital question and more. While not entirely intuitive, the relationships make sense to me and this unblocks me for now. Have a great week

Re: Snowflake Data Share ERD

tuttopanino — Tue, 23 Sep 2025 15:29:28 GMT

hey @lanza_apollo Sorry this is a bit late but you might want to check out Secoda if you're still running into issues. It'll automatically generate an ERD across Snowflake and the rest of your data stack. It will also let you chat with your data in natural language.