Snowflake recently announced the General Availability of the Semantic Views. In this post we will cover the key aspects of a Semantic View and use a real-world Salesforce Marketing Cloud (SFMC) dataset to demonstrate the power of Semantic Views. If you are not familiar with Salesforce Marketing Cloud, you can checkout the following documentation:
The three Objects (tables) that we will be using from Salesforce Marketing Cloud are Send, OpenEvent and ClickEvent. The OpenEvent is event when the Marketing email recipient opens the email and ClickEvent is the event captured when the recipient Clicks on one of the links in the Marketing email.
Let’s start by re-creating a production dataset. The email addresses have been masked for Privacy reasons.
insert into Send values (1, '2025-01-01', 'Prada Paradoxe Coupon');
insert into Send values (2, '2025-01-01', 'Juliette Has A Gun Coupon');
insert into Send values (3, '2025-01-01', 'Gucci Flora Coupon');
insert into Send values (4, '2025-01-01', 'Miss Dior Coupon');
insert into Send values (5, '2025-01-01', 'Delina Exclusif Coupon');
insert into Send values (6, '2025-01-01', 'Mojave Ghost Coupon');
insert into Send values (7, '2025-01-01', 'Club De Nuit Coupon');
insert into OpenEvent values (1, 1, 'maryam@email.tld', current_timestamp);
insert into OpenEvent values (2, 1, 'fatima@email.tld', current_timestamp);
insert into OpenEvent values (3, 1, 'uroosa@email.tld', current_timestamp);
insert into OpenEvent values (4, 1, 'hannah@email.tld', current_timestamp);
insert into OpenEvent values (5, 1, 'zainab@email.tld', current_timestamp);
insert into OpenEvent values (6, 1, 'aliya@email.tld', current_timestamp);
insert into OpenEvent values (7, 2, 'aliya@email.tld', current_timestamp);
insert into OpenEvent values (8, 4, 'hannah@email.tld', current_timestamp);
insert into OpenEvent values (9, 1, 'hannah@email.tld', current_timestamp);
insert into ClickEvent values (1, 1, 'maryam@email.tld', current_timestamp);
insert into ClickEvent values (2, 1, 'fatima@email.tld', current_timestamp);
insert into ClickEvent values (3, 1, 'uroosa@email.tld', current_timestamp);
insert into ClickEvent values (4, 1, 'hannah@email.tld', current_timestamp);
insert into ClickEvent values (5, 2, 'aliya@email.tld', current_timestamp);
Now let’s create a Semantic View that captures the relationships with the Send and the ClickEvent and the OpenEvent
create or replace semantic view sfmc_insights
TABLES(
Send as send PRIMARY KEY (id)
, OpenEvent as OpenEvent PRIMARY KEY (id)
, ClickEvent as ClickEvent PRIMARY KEY (id)
)
RELATIONSHIPS (
OpenEvent (SendID) references Send (Id)
, ClickEvent (SendID) references Send (Id)
)
DIMENSIONS (
Send.id as id
, Send.Subject as Subject
, ClickEvent.SubscriberKey as SubscriberKey
, OpenEvent.SubscriberKey as SubscriberKey
)
METRICS (
ClickEvent.total_clicks as count(id)
, OpenEvent.total_opens as count(id)
)
;
Now let’s SELECT from the Semantic View. Let’s say we want to see how each Marketing email performed, i.e. the Number of Open and the Number of Clicks. We can use the following SELECT for that:
SELECT * FROM SEMANTIC_VIEW(
sfmc_insights
DIMENSIONS
Subject
METRICS
total_opens
, total_clicks
)
Now let’s say we interested in the individual recipients and how they reacted to each Marketing Email i.e. how many times they “Opened” the email. We can use the following SELECT:
SELECT * FROM SEMANTIC_VIEW(
sfmc_insights
DIMENSIONS
OpenEvent.SubscriberKey
, Subject
METRICS
total_opens
)
order by 1 desc
You can do the same for number of clicks by each receipt by the Marketing email:
SELECT * FROM SEMANTIC_VIEW(
sfmc_insights
DIMENSIONS
ClickEvent.SubscriberKey
, Subject
METRICS
total_clicks
)
order by 1 desc;
One thing to note is that in a Semantic View you have to define the relationship. In this dataset there is no relationship between the OpenEvent and ClickEvent.
SELECT * FROM SEMANTIC_VIEW(
sfmc_insights
DIMENSIONS
OpenEvent.SubscriberKey
, ClickEvent.SubscriberKey
METRICS
total_clicks
, total_opens
)
The above query will result in an error:
Invalid dimension specified: The dimension entity 'OPENEVENT' must be related to and have an equal or lower level of granularity compared to the base metric or dimension entity 'CLICKEVENT'.