Skip to content

Sample queries

This partner page offers some sample queries of possible use cases to reference when setting up your queries.

A common query might be to filter events by time.

You can filter them by the time of occurrence. Event tables are clustered by time which makes filtering by time optimal:

You can also filter events by the time at which they were persisted in the Snowflake data warehouse by using sf_created_at. sf_created_at and time are not the same but are usually close, so this query should have similar performance characteristics:

Campaign names and Canvas names are not present in the events themselves. Instead, they are published in a changelog table.

You can see campaign names for events related to a campaign by joining with the campaign changelog table using a query like:

Some important things to note include:

  • The Snowflake’s window functions are used here.
  • The left join will ensure that events unrelated to a campaign will also be included.
  • If you see events with campaign_ids but no campaign names then there is a possibility that the campaign was created with a name before Data Sharing existed as a product.
  • You can see Canvas names using a similar query, joining with the CHANGELOGS_CANVAS_SHARED table instead.

If you want to see both campaign and Canvas names, you may have to use the following sub-query:

You can use this push funnel query to aggregate push sends raw event data, through to deliveries raw event data, through to opens raw event data. This query shows how all the tables should be joined since each raw event typically has a separate table:

You can use this daily email messaging cadence query to analyze the time between emails that a user receives.

For example, if a user received two emails in one day, they would fall under 0 "days since last received". If they received one email on Monday and one on Tuesday, they would fall into the 1 "days since last received" cohort.

You can use this unique email clicks query to analyze the unique email click in a given time window. The algorithm to calculate this is as follows:

  1. Partition the events by the key (app_group_id, message_variation_id, dispatch_id, email_address).
  2. In each partition, order the events by time, and the first event is always a unique event.
  3. For every subsequent event, if it occurred more than seven days after its predecessor, is considered a unique event.

We can use Snowflake’s windowing functions to help us achieve this. The following query gives us all email clicks in the last 365 days and indicates which events are unique in the is_unique column:

If you just want to see the unique events, use the QUALIFY clause:

To further see unique event counts grouped by email address:

HOW HELPFUL WAS THIS PAGE?
New Stuff!