Last active
July 7, 2026 11:41
-
-
Save yalisassoon/c3b78e89d69c1ed74f9da2dc009cef01 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: SNOWPLOW_UNIFIED_VIEWS | |
| description: | | |
| Snowplow Unified Digital (Web) is a dbt package that converts raw Snowplow behavioral events into three analytics-ready entities: views, sessions, and users. It standardizes and enriches event data (e.g., content, device, geo, traffic attribution) and computes engagement metrics such as engaged time and scroll depth, producing a reliable canonical layer for product analytics, funnel/journey reporting, experimentation, and ML/LLM use. | |
| The core tables relate hierarchically: | |
| `snowplow_unified_views` has one row per content view (page/screen), capturing view-level context and engagement; | |
| `snowplow_unified_sessions` aggregates all activity within a visit into one row per session, rolling up view and event behaviors into session metrics; | |
| `snowplow_unified_users` aggregates sessions into one row per user, providing user traits and lifetime/rolling metrics, including optional identity stitching (e.g., `stitched_user_id`) across devices and login states. | |
| Together, these tables replace repeated event-level aggregation with consistent, governed definitions and cost-efficient incremental models, making behavioral analysis and AI consumption simpler and more trustworthy. | |
| Value notes: | |
| - Snowplow-generated identifiers (pageview id, domain user id, session id) are UUIDs - examples: 00112233-4455-6677-8899-aabbccddeeff, 00000000-1111-2222-3333-444455556666 | |
| - Company's internal userid might be anything the company defines. | |
| - Timestamp columns follow ISO-8601-style datetime format: yyyy-MM-dd'T'HH:mm:ss.SSSZ - examples: 2025-01-01T10:10:10.010+0000, 2025-01-01T20:32:45.276+0000 | |
| {{YOUR_BUSINESS_DESCRIPTION_HERE}} | |
| Example Co is a company established in 2000. Key industry is A. Key products are A, B, C. | |
| Company uses Snowplow to track web behaviour on these company's websites: | |
| - your_app -- https://example.com -- Example Co's main website. Example Co is a company that sells A, B, C and uses this website as an e-commerce platform. | |
| - your_app_support -- https://support.example.com -- Example Co's support website. Example Co uses Support website to help customers use their products. | |
| tables: | |
| - name: SNOWPLOW_UNIFIED_SESSIONS | |
| description: This table stores information about user sessions, including session metadata, user information, device and browser details, geographic location, marketing campaign data, and engagement metrics, providing a comprehensive view of user behavior and interactions with a website or application. | |
| base_table: | |
| database: {{YOUR_DATABASE}} | |
| schema: DERIVED # update this if you use non-default schema | |
| table: SNOWPLOW_UNIFIED_SESSIONS | |
| dimensions: | |
| - name: APP_ID | |
| description: The application or system that initiated the Snowplow session. | |
| expr: APP_ID | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - website | |
| - docs | |
| - name: DEFAULT_CHANNEL_GROUP | |
| description: The channel through which the user initially entered the website, categorized into groups such as paid advertising, email campaigns, or unassigned sources. | |
| expr: DEFAULT_CHANNEL_GROUP | |
| data_type: VARCHAR(25) | |
| sample_values: | |
| - Unassigned | |
| - Paid Search | |
| - name: DEVICE_CATEGORY | |
| description: The type of device used by the user during the session, categorized as Phone, Tablet, or Desktop. | |
| expr: DEVICE_CATEGORY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Phone | |
| - Tablet | |
| - Desktop | |
| - name: DEVICE_IDENTIFIER | |
| description: Unique identifier for the device used to access the application or website, allowing for tracking of user behavior across sessions on the same device. | |
| expr: DEVICE_IDENTIFIER | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - 00112233-4455-6677-8899-aabbccddeeff | |
| - 00000000-1111-2222-3333-444455556666 | |
| - name: DEVICE_SESSION_INDEX | |
| description: A unique identifier for a user's device session, allowing for the tracking of individual user interactions across multiple sessions on the same device. | |
| expr: DEVICE_SESSION_INDEX | |
| data_type: NUMBER(38,0) | |
| sample_values: | |
| - '2' | |
| - '10' | |
| - '42' | |
| - name: ENGAGED_TIME_IN_S | |
| description: The total amount of time (in seconds) the user was actively engaged with the website or application during the session. | |
| expr: ENGAGED_TIME_IN_S | |
| data_type: NUMBER(22,0) | |
| sample_values: | |
| - '40' | |
| - '60' | |
| - '70' | |
| - name: EVENT_COUNTS | |
| description: This column stores the count of different types of events that occurred during a user's session, including page pings, application errors, link clicks, and page views, providing insight into user behavior and application performance. | |
| expr: EVENT_COUNTS | |
| data_type: VARIANT | |
| sample_values: | |
| - |- | |
| { | |
| "page_ping": 9 | |
| } | |
| - |- | |
| { | |
| "event": 2, | |
| "page_ping": 12, | |
| "page_view": 2 | |
| } | |
| - |- | |
| { | |
| "event": 3, | |
| "link_click": 1, | |
| "page_ping": 3, | |
| "page_view": 3 | |
| } | |
| - name: FIRST_EVENT_NAME | |
| description: The type of event that triggered the start of the session. | |
| expr: FIRST_EVENT_NAME | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - page_ping | |
| - page_view | |
| - name: FIRST_GEO_CITY | |
| description: The city where the user's session originated from, based on their IP address. | |
| expr: FIRST_GEO_CITY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - London | |
| - New-York | |
| - name: FIRST_GEO_COUNTRY | |
| description: The country where the user's session originated, based on geolocation data. | |
| expr: FIRST_GEO_COUNTRY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - GB | |
| - US | |
| - name: FIRST_GEO_COUNTRY_NAME | |
| description: The country of origin of the user's first geolocation event during the session. | |
| expr: FIRST_GEO_COUNTRY_NAME | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - United Kingdom of Great Britain and Northern Ireland | |
| - Canada | |
| - name: FIRST_PAGE_TITLE | |
| description: The title of the first page visited by a user during a session, providing insight into the entry point of the user's journey on the website. | |
| expr: FIRST_PAGE_TITLE | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Example Page | |
| - Blog Example Title | |
| - name: FIRST_PAGE_URL | |
| description: The URL of the first page visited by a user during a session. | |
| expr: FIRST_PAGE_URL | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - https://example.com | |
| - https://example.com/a/b/c | |
| - https://blog.example.com/categories | |
| - name: FIRST_PAGE_URLHOST | |
| description: The domain of the first page visited by the user during the session. | |
| expr: FIRST_PAGE_URLHOST | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - example.com | |
| - blog.example.com | |
| - name: FIRST_PAGE_URLPATH | |
| description: The URL path of the first page visited by the user during the session. | |
| expr: FIRST_PAGE_URLPATH | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - / | |
| - /a/b/c | |
| - /categories | |
| - name: FIRST_PAGE_URLQUERY | |
| description: The URL query string of the first page visited by a user during a session, which may contain parameters such as campaign IDs, keywords, and source information. | |
| expr: FIRST_PAGE_URLQUERY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - utm_campaign=website&utm_medium=email | |
| - hidden=false&rows=10&search= | |
| - name: IS_ENGAGED | |
| description: 'Indicates whether the user was actively engaged with the application during the session, meaning they performed at least one of the following actions: scroll, click, or key press.' | |
| expr: IS_ENGAGED | |
| data_type: BOOLEAN | |
| sample_values: | |
| - 'TRUE' | |
| - 'FALSE' | |
| - name: LAST_EVENT_NAME | |
| description: The name of the last event that occurred during a user's session. | |
| expr: LAST_EVENT_NAME | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - page_view | |
| - name: LAST_GEO_CITY | |
| description: The city where the user was located when the session was recorded, as determined by geolocation data. | |
| expr: LAST_GEO_CITY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - London | |
| - New-York | |
| - name: LAST_GEO_COUNTRY | |
| description: The country from which the user accessed the application, based on their geolocation. | |
| expr: LAST_GEO_COUNTRY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - US | |
| - GB | |
| - name: LAST_GEO_COUNTRY_NAME | |
| description: The country of origin of the user's geolocation, as determined by their IP address. | |
| expr: LAST_GEO_COUNTRY_NAME | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Singapore | |
| - United States of America | |
| - name: LAST_PAGE_TITLE | |
| description: The title of the last page visited by a user during a session. | |
| expr: LAST_PAGE_TITLE | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Example Title | |
| - Blog Post Example Title | |
| - name: LAST_PAGE_URL | |
| description: The URL of the last page visited by the user during the session. | |
| expr: LAST_PAGE_URL | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - https://example.com | |
| - https://example.com/a/b/c | |
| - https://blog.example.com/categories | |
| - name: LAST_PAGE_URLHOST | |
| description: The URL host of the last page visited by the user during the session. | |
| expr: LAST_PAGE_URLHOST | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - example.com | |
| - blog.example.com | |
| - name: LAST_PAGE_URLPATH | |
| description: The URL path of the last page visited by the user during the session. | |
| expr: LAST_PAGE_URLPATH | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - / | |
| - /a/b/c | |
| - /categories | |
| - name: LAST_PAGE_URLQUERY | |
| description: The URL query parameters of the last page visited by the user during a session, including UTM tracking codes and Google Ads click IDs. | |
| expr: LAST_PAGE_URLQUERY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - utm_campaign=website&utm_medium=email | |
| - hidden=false&rows=10&search= | |
| - name: MKT_CAMPAIGN | |
| description: The marketing campaign that drove the user to the website. | |
| expr: MKT_CAMPAIGN | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - example_campaign | |
| - utm_campaign_example_2025 | |
| - name: MKT_CONTENT | |
| description: Marketing content identifier, a unique code associated with a specific marketing content or campaign. | |
| expr: MKT_CONTENT | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - '123' | |
| - 'blog-article-123' | |
| - name: MKT_MEDIUM | |
| description: The marketing medium through which a user accessed the website, such as a specific advertising campaign, social media platform, or email newsletter. | |
| expr: MKT_MEDIUM | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - name: MKT_NETWORK | |
| description: The marketing network through which the user accessed the website or application, such as Google or Microsoft. | |
| expr: MKT_NETWORK | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Microsoft | |
| - name: MKT_SOURCE | |
| description: The marketing source that initiated the user's session, such as a Pardot campaign, email, or GitHub referral. | |
| expr: MKT_SOURCE | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - github | |
| - name: MKT_SOURCE_PLATFORM | |
| description: The platform through which a marketing campaign was sourced, such as Facebook, Google, or Email. | |
| expr: MKT_SOURCE_PLATFORM | |
| data_type: VARCHAR(16777216) | |
| - name: MKT_TERM | |
| description: The marketing term or keyword that triggered the user's session, indicating the search query or campaign that led to the user's visit. | |
| expr: MKT_TERM | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - example tools | |
| - best example website | |
| - name: OS_TIMEZONE | |
| description: The time zone of the user's operating system at the time of the session. | |
| expr: OS_TIMEZONE | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - America/New_York | |
| - Europe/London | |
| - name: OS_TYPE | |
| description: The type of operating system used by the device that initiated the session. | |
| expr: OS_TYPE | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Unknown | |
| - Mac OS X | |
| - Android | |
| - name: OS_VERSION | |
| description: The version of the operating system used by the device that initiated the Snowplow session. | |
| expr: OS_VERSION | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - '??' | |
| - '10.14' | |
| - '>=10.15.7' | |
| - name: PAGE_REFERRER | |
| description: The URL of the webpage that referred the user to the current page, indicating where the user came from before arriving at the current page. | |
| expr: PAGE_REFERRER | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - https://example.com | |
| - https://example.com/a/b/c | |
| - https://blog.example.com/categories | |
| - name: PLATFORM | |
| description: The platform on which the Snowplow event was tracked, e.g. web, mobile app, server-side, etc. | |
| expr: PLATFORM | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - web | |
| - name: SCREEN_RESOLUTION | |
| description: The screen resolution of the device used during the session, represented as the width and height in pixels (e.g., 1920x1080). | |
| expr: SCREEN_RESOLUTION | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - 412x924 | |
| - 1280x720 | |
| - 3840x2160 | |
| - name: SESSION_IDENTIFIER | |
| description: Unique identifier for a user's session, used to track and analyze user behavior across multiple interactions. | |
| expr: SESSION_IDENTIFIER | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - 00112233-4455-6677-8899-aabbccddeeff | |
| - 00000000-1111-2222-3333-444455556666 | |
| - name: STITCHED_USER_ID | |
| description: Unique identifier for a user across multiple devices and platforms, used to stitch together user behavior and activity across different sessions and devices. | |
| expr: STITCHED_USER_ID | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - 00112233-4455-6677-8899-aabbccddeeff | |
| - 00000000-1111-2222-3333-444455556666 | |
| - name: TOTAL_EVENTS | |
| description: The total number of events triggered during a user's session. | |
| expr: TOTAL_EVENTS | |
| data_type: NUMBER(18,0) | |
| sample_values: | |
| - '6' | |
| - '3' | |
| - '2' | |
| - name: USER_ID | |
| description: Unique identifier for the user who initiated the session. | |
| expr: USER_ID | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - 00112233-4455-6677-8899-aabbccddeeff | |
| - 00000000-1111-2222-3333-444455556666 | |
| - name: USER_IDENTIFIER | |
| description: Unique identifier for the user, used to track individual users across sessions. | |
| expr: USER_IDENTIFIER | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - 00112233-4455-6677-8899-aabbccddeeff | |
| - 00000000-1111-2222-3333-444455556666 | |
| - name: VIEWS | |
| description: The number of times a user viewed a page or screen during a session. | |
| expr: VIEWS | |
| data_type: NUMBER(18,0) | |
| sample_values: | |
| - '2' | |
| - '36' | |
| time_dimensions: | |
| - name: END_TSTAMP | |
| description: The timestamp when the user's session ended. | |
| expr: END_TSTAMP | |
| data_type: TIMESTAMP_NTZ(9) | |
| sample_values: | |
| - 2025-01-01T10:10:10.010+0000 | |
| - 2025-01-01T20:32:45.276+0000 | |
| - name: START_TSTAMP | |
| description: The timestamp when the user's session started. | |
| expr: START_TSTAMP | |
| data_type: TIMESTAMP_NTZ(9) | |
| sample_values: | |
| - 2025-01-01T10:10:10.010+0000 | |
| - 2025-01-01T20:32:45.276+0000 | |
| metrics: | |
| - name: NUMBER_OF_SESSIONS | |
| synonyms: | |
| - count_of_sessions | |
| - session_count | |
| - session_number | |
| - session_quantity | |
| - sessions_count | |
| - sessions_total | |
| - total_session_count | |
| - total_sessions | |
| description: number of sessions per some dimensions | |
| expr: count(distinct SESSION_IDENTIFIER) | |
| access_modifier: public_access | |
| primary_key: | |
| columns: | |
| - SESSION_IDENTIFIER | |
| - name: SNOWPLOW_UNIFIED_USERS | |
| description: This table stores unified user data, providing a comprehensive view of user behavior and attributes across various platforms and devices. It captures user identification, session and engagement metrics, geographic information, browser and device details, and marketing attribution data, allowing for analysis of user behavior, demographics, and campaign effectiveness. | |
| base_table: | |
| database: {{YOUR_DATABASE}} | |
| schema: DERIVED # update this if you use non-default schema | |
| table: SNOWPLOW_UNIFIED_USERS | |
| dimensions: | |
| - name: ACTIVE_DAYS | |
| description: The number of days a user has been active within the Snowplow unified user model. | |
| expr: ACTIVE_DAYS | |
| data_type: NUMBER(18,0) | |
| sample_values: | |
| - '2' | |
| - '6' | |
| - '98' | |
| - name: DEFAULT_CHANNEL_GROUP | |
| description: The channel group through which the user was acquired, such as Organic Social, Unassigned, or Email, indicating the primary source of the user's initial interaction with the product or service. | |
| expr: DEFAULT_CHANNEL_GROUP | |
| data_type: VARCHAR(25) | |
| sample_values: | |
| - Organic Social | |
| - Unassigned | |
| - name: ENGAGED_TIME_IN_S | |
| description: The total time in seconds that the user was actively engaged with the application, such as interacting with the page, scrolling, or clicking, during the session. | |
| expr: ENGAGED_TIME_IN_S | |
| data_type: NUMBER(34,0) | |
| sample_values: | |
| - '0' | |
| - '110' | |
| - '730' | |
| - name: FIRST_GEO_CITY | |
| description: The city where the user is physically located when they interact with the Snowplow application. | |
| expr: FIRST_GEO_CITY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - London | |
| - New-York | |
| - name: FIRST_GEO_CONTINENT | |
| description: The continent where the user is located, based on their geolocation. | |
| expr: FIRST_GEO_CONTINENT | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Africa | |
| - Americas | |
| - Asia | |
| - name: FIRST_GEO_COUNTRY | |
| description: The country where the user is located, based on their IP address. | |
| expr: FIRST_GEO_COUNTRY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - DE | |
| - US | |
| - FR | |
| - name: FIRST_GEO_COUNTRY_NAME | |
| description: The country of origin of the user, based on their geolocation. | |
| expr: FIRST_GEO_COUNTRY_NAME | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Singapore | |
| - United States of America | |
| - name: FIRST_PAGE_TITLE | |
| description: The title of the first page a user visited on the website. | |
| expr: FIRST_PAGE_TITLE | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Example Title | |
| - Blog Post Abc | |
| - name: FIRST_PAGE_URL | |
| description: The URL of the first page visited by a user during a session. | |
| expr: FIRST_PAGE_URL | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - https://example.com | |
| - https://example.com/a/b/c | |
| - https://blog.example.com/categories | |
| - name: FIRST_PAGE_URLHOST | |
| description: The domain of the first page visited by the user. | |
| expr: FIRST_PAGE_URLHOST | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - example.com | |
| - blog.example.com | |
| - name: FIRST_PAGE_URLPATH | |
| description: The URL path of the first page a user visited on the website. | |
| expr: FIRST_PAGE_URLPATH | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - / | |
| - /a/b/c | |
| - /categories | |
| - name: FIRST_PAGE_URLQUERY | |
| description: The URL query parameters of the first page visited by the user, including UTM tracking codes and Google Analytics client ID. | |
| expr: FIRST_PAGE_URLQUERY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - utm_campaign=website&utm_medium=email | |
| - hidden=false&rows=10&search= | |
| - name: FIRST_PLATFORM | |
| description: The platform where the user first interacted with the application. | |
| expr: FIRST_PLATFORM | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - web | |
| - name: LAST_GEO_CITY | |
| description: The city where the user was last seen, based on their IP address geolocation. | |
| expr: LAST_GEO_CITY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - London | |
| - Boston | |
| - name: LAST_GEO_COUNTRY | |
| description: The country from which the user's last known geolocation was reported. | |
| expr: LAST_GEO_COUNTRY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - DE | |
| - AT | |
| - US | |
| - name: LAST_GEO_COUNTRY_NAME | |
| description: The country of origin for the user's last known geolocation. | |
| expr: LAST_GEO_COUNTRY_NAME | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Canada | |
| - United States of America | |
| - name: LAST_OS_TYPE | |
| description: The type of operating system last used by the user to access the application. | |
| expr: LAST_OS_TYPE | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Linux | |
| - Unknown | |
| - Chrome OS | |
| - name: LAST_OS_VERSION | |
| description: The version of the operating system (OS) running on the user's device, with values indicating the minimum OS version, specifically Mac OS X versions 10.15.7 and later, and versions 10 and later. | |
| expr: LAST_OS_VERSION | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - '??' | |
| - '>=10.15.7' | |
| - '>=10' | |
| - name: LAST_PAGE_TITLE | |
| description: The title of the last webpage visited by the user before the event was tracked. | |
| expr: LAST_PAGE_TITLE | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Example Title | |
| - Blog Post about Example Topic | |
| - name: LAST_PAGE_URL | |
| description: The URL of the last page visited by the user before the current event. | |
| expr: LAST_PAGE_URL | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - https://example.com | |
| - https://example.com/a/b/c | |
| - https://blog.example.com/categories | |
| - name: LAST_PAGE_URLHOST | |
| description: The URL host of the last page visited by the user. | |
| expr: LAST_PAGE_URLHOST | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - example.com | |
| - blog.example.com | |
| - name: LAST_PAGE_URLPATH | |
| description: The URL path of the last webpage visited by a user before leaving the site or completing a session. | |
| expr: LAST_PAGE_URLPATH | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - / | |
| - com/a/b/c | |
| - com/categories | |
| - name: LAST_PAGE_URLQUERY | |
| description: The URL query parameters of the last page visited by a user, including campaign, medium, and source information, typically used for tracking and analyzing the effectiveness of marketing campaigns and external referrals. | |
| expr: LAST_PAGE_URLQUERY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - utm_campaign=website&utm_medium=email | |
| - hidden=false&rows=10&search= | |
| - name: LAST_PLATFORM | |
| description: The platform from which the user last interacted with the application. | |
| expr: LAST_PLATFORM | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - web | |
| - name: LAST_SCREEN_RESOLUTION | |
| description: The resolution of the user's last screen, represented as the number of pixels in width and height (e.g. 2560x1440). | |
| expr: LAST_SCREEN_RESOLUTION | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - 2560x1440 | |
| - 1440x900 | |
| - 1920x1080 | |
| - name: MKT_CAMPAIGN | |
| description: Marketing campaign name, indicating the type of marketing effort, geographic region, and target audience. | |
| expr: MKT_CAMPAIGN | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - brand-search-campaign | |
| - example-nurture-campaign | |
| - name: MKT_CONTENT | |
| description: Unique identifier for a piece of marketing content, such as a blog post, social media post, or email campaign, used to track user interactions and engagement. | |
| expr: MKT_CONTENT | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - '20331501829' | |
| - later-33922986 | |
| - name: MKT_MEDIUM | |
| description: The marketing medium through which the user was acquired, such as pay-per-click advertising (ppc), display advertising (display), or social media post (post). | |
| expr: MKT_MEDIUM | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - ppc | |
| - display | |
| - post | |
| - name: MKT_NETWORK | |
| description: The network or platform where the marketing campaign was served, such as a social media platform or search engine. | |
| expr: MKT_NETWORK | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Microsoft | |
| - name: MKT_SOURCE | |
| description: The source through which the user was acquired or referred to the platform, such as a website or marketing campaign. | |
| expr: MKT_SOURCE | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - github | |
| - name: MKT_SOURCE_PLATFORM | |
| description: The platform through which the user was acquired, such as Facebook, Google, or Email. | |
| expr: MKT_SOURCE_PLATFORM | |
| data_type: VARCHAR(16777216) | |
| - name: MKT_TERM | |
| description: The marketing term or phrase associated with the user, such as a specific marketing campaign or product feature that drove the user to the site or application. | |
| expr: MKT_TERM | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - example topics | |
| - best tools for example | |
| - name: ON_MOBILE | |
| description: Indicates whether the user was accessing the application on a mobile device at the time of the event. | |
| expr: ON_MOBILE | |
| data_type: BOOLEAN | |
| sample_values: | |
| - 'FALSE' | |
| - name: ON_WEB | |
| description: Indicates whether the user was on the website at the time of the event. | |
| expr: ON_WEB | |
| data_type: BOOLEAN | |
| sample_values: | |
| - 'TRUE' | |
| - name: PAGE_REFERRER | |
| description: The URL of the webpage that referred the user to the current page, indicating the source of the user's visit. | |
| expr: PAGE_REFERRER | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - https://google.com/ | |
| - https://chatgpt.com/ | |
| - https://example.com/ | |
| - name: SESSIONS | |
| description: The total number of sessions initiated by a user across all platforms and devices. | |
| expr: SESSIONS | |
| data_type: NUMBER(18,0) | |
| sample_values: | |
| - '4' | |
| - '12' | |
| - name: STITCHED_USER_ID | |
| description: Unique identifier for a user across multiple platforms and devices, created by stitching together various user IDs to provide a single, unified view of the user. | |
| expr: STITCHED_USER_ID | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - 00112233-4455-6677-8899-aabbccddeeff | |
| - 00000000-1111-2222-3333-444455556666 | |
| - name: USER_ID | |
| description: Unique identifier for the user who triggered the event. | |
| expr: USER_ID | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - abc1 | |
| - user1 | |
| - name: USER_IDENTIFIER | |
| description: Unique identifier for a user in the Snowplow unified data model, used to track user behavior and activity across multiple platforms and devices. | |
| expr: USER_IDENTIFIER | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - 00112233-4455-6677-8899-aabbccddeeff | |
| - 00000000-1111-2222-3333-444455556666 | |
| - name: VIEWS | |
| description: The number of times a user has viewed a page or content. | |
| expr: VIEWS | |
| data_type: NUMBER(30,0) | |
| sample_values: | |
| - '1' | |
| - '42' | |
| time_dimensions: | |
| - name: END_TSTAMP | |
| description: The timestamp when the user's session ended. | |
| expr: END_TSTAMP | |
| data_type: TIMESTAMP_NTZ(9) | |
| sample_values: | |
| - 2025-01-01T10:10:10.010+0000 | |
| - 2025-01-01T20:32:45.276+0000 | |
| - name: START_TSTAMP | |
| description: The timestamp when the user's session started. | |
| expr: START_TSTAMP | |
| data_type: TIMESTAMP_NTZ(9) | |
| sample_values: | |
| - 2025-01-01T10:10:10.010+0000 | |
| - 2025-01-01T20:32:45.276+0000 | |
| primary_key: | |
| columns: | |
| - USER_IDENTIFIER | |
| - name: SNOWPLOW_UNIFIED_VIEWS | |
| description: This table stores unified view data from various sources, capturing user interactions and device information, including session details, user demographics, device characteristics, geographic location, and marketing campaign data, to provide a comprehensive understanding of user behavior and engagement. | |
| base_table: | |
| database: {{YOUR_DATABASE}} | |
| schema: DERIVED # update this if you use non-default schema | |
| table: SNOWPLOW_UNIFIED_VIEWS | |
| dimensions: | |
| - name: APP_ID | |
| description: The application identifier for the Snowplow event, indicating the source of the event data, such as the website or an app. | |
| expr: APP_ID | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - website | |
| - docs | |
| - name: CONTENT_GROUP | |
| description: The category of content the user was interacting with, such as the homepage, checkout process, or other specific groups of content. | |
| expr: CONTENT_GROUP | |
| data_type: VARCHAR(8) | |
| sample_values: | |
| - other | |
| - checkout | |
| - homepage | |
| - name: DEFAULT_CHANNEL_GROUP | |
| description: The channel group assigned to the event, indicating the marketing channel through which the event was triggered, such as paid advertising, display advertising, or unassigned if no channel is specified. | |
| expr: DEFAULT_CHANNEL_GROUP | |
| data_type: VARCHAR(25) | |
| sample_values: | |
| - Paid Other | |
| - Display | |
| - Unassigned | |
| - name: DEVICE_CATEGORY | |
| description: The type of device used to access the website or application, categorized as Desktop, Tablet, or Phone. | |
| expr: DEVICE_CATEGORY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Desktop | |
| - Tablet | |
| - Phone | |
| - name: DEVICE_IDENTIFIER | |
| description: Unique identifier for the device that generated the event, such as a mobile device or browser. | |
| expr: DEVICE_IDENTIFIER | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - 00112233-4455-6677-8899-aabbccddeeff | |
| - 00000000-1111-2222-3333-444455556666 | |
| - name: DEVICE_SESSION_INDEX | |
| description: A unique identifier for a user's device session, allowing for the tracking of individual user interactions across multiple events and sessions. | |
| expr: DEVICE_SESSION_INDEX | |
| data_type: NUMBER(38,0) | |
| sample_values: | |
| - '4' | |
| - '99' | |
| - name: ENGAGED_TIME_IN_S | |
| description: The amount of time a user spent actively engaged with the application, in seconds. | |
| expr: ENGAGED_TIME_IN_S | |
| data_type: NUMBER(22,0) | |
| sample_values: | |
| - '10' | |
| - '20' | |
| - name: GEO_CITY | |
| description: The city from which the user accessed the website or application. | |
| expr: GEO_CITY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - London | |
| - Paris | |
| - name: GEO_COUNTRY | |
| description: The country where the event occurred, based on the user's IP address. | |
| expr: GEO_COUNTRY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - US | |
| - GB | |
| - name: GEO_REGION | |
| description: Geographic region where the snowplow event occurred, with possible values being British Columbia (BCP), Lower Downtown Seattle (LDS), and California (CA). | |
| expr: GEO_REGION | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - CA | |
| - name: GEO_TIMEZONE | |
| description: The time zone associated with the geographic location of the user, represented in the IANA time zone format (e.g. Europe/Zurich). | |
| expr: GEO_TIMEZONE | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Europe/Zurich | |
| - America/Vancouver | |
| - name: HORIZONTAL_PIXELS_SCROLLED | |
| description: The total number of horizontal pixels scrolled by the user on the webpage. | |
| expr: HORIZONTAL_PIXELS_SCROLLED | |
| data_type: NUMBER(38,0) | |
| sample_values: | |
| - '246' | |
| - '0' | |
| - name: MKT_CAMPAIGN | |
| description: Marketing campaign name, describing the specific advertising campaign, including the business division (BD), marketing channel (Search), campaign type (Generic or Brand), and target region (UK/IE). | |
| expr: MKT_CAMPAIGN | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - example brand campaign | |
| - search terms keywords | |
| - name: MKT_CLICKID | |
| description: Unique identifier for a marketing click event, used to track the source and performance of online advertisements. | |
| expr: MKT_CLICKID | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - EAIaIQo..... | |
| - Cj0KCQj123-.... | |
| - name: MKT_CONTENT | |
| description: Unique identifier for a specific piece of marketing content. | |
| expr: MKT_CONTENT | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - '20331501838' | |
| - '79321121' | |
| - name: MKT_MEDIUM | |
| description: The marketing medium through which the user arrived at the website, such as email or pay-per-click advertising. | |
| expr: MKT_MEDIUM | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - ppc | |
| - name: MKT_SOURCE | |
| description: The source of the marketing campaign or channel that triggered the event, such as an email service provider or a sales outreach. | |
| expr: MKT_SOURCE | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - sales | |
| - blog | |
| - name: MKT_TERM | |
| description: Marketing term or category associated with the event or activity being tracked, such as a specific product or service offering. | |
| expr: MKT_TERM | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - example services | |
| - example results | |
| - name: OS_TIMEZONE | |
| description: The time zone of the user's operating system. | |
| expr: OS_TIMEZONE | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - America/Chicago | |
| - America/New_York | |
| - name: OS_TYPE | |
| description: The type of operating system used by the device that accessed the website or application. | |
| expr: OS_TYPE | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Windows NT | |
| - Mac OS | |
| - Android | |
| - name: OS_VERSION | |
| description: The version of the operating system (OS) running on the device, with possible values including specific version numbers (e.g. 10.15.7), unknown/missing values (??), and a range of versions greater than or equal to 10.15.7. | |
| expr: OS_VERSION | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - 10.15.7 | |
| - '??' | |
| - '>=10.15.7' | |
| - name: PAGE_REFERRER | |
| description: The URL of the webpage that referred the user to the current page. | |
| expr: PAGE_REFERRER | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - https://example.com | |
| - https://example.com/a/b/c | |
| - https://blog.example.com/categories | |
| - name: PAGE_TITLE | |
| description: The title of the webpage that was visited. | |
| expr: PAGE_TITLE | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - Components & applications | Snowplow Documentation | |
| - Opt-outs and anonymous tracking | Snowplow Documentation | |
| - Snowplow BDP | |
| - name: PAGE_URL | |
| description: The URL of the webpage that triggered the event. | |
| expr: PAGE_URL | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - https://example.com | |
| - https://example.com/a/b/c | |
| - https://blog.example.com/categories | |
| - name: PAGE_URLHOST | |
| description: The domain of the webpage where the event occurred, e.g. the website or subdomain that the user was on when the event was tracked. | |
| expr: PAGE_URLHOST | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - example.com | |
| - blog.example.com | |
| - name: PAGE_URLPATH | |
| description: The URL path of the webpage visited by the user. | |
| expr: PAGE_URLPATH | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - /a/b/c | |
| - /categories | |
| - name: PAGE_URLQUERY | |
| description: The URL query string parameters passed in the page request, including any search queries, tracking codes, or other data appended to the URL. | |
| expr: PAGE_URLQUERY | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - lang=en_US | |
| - gclid=abc | |
| - name: PLATFORM | |
| description: The platform on which the event occurred, such as a website or mobile application. | |
| expr: PLATFORM | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - web | |
| - name: SCREEN_RESOLUTION | |
| description: The screen resolution of the device used to access the website or application, represented as the number of pixels in width and height (e.g. 1920x1200). | |
| expr: SCREEN_RESOLUTION | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - 1792x1120 | |
| - 385x854 | |
| - 1920x1200 | |
| - name: SESSION_IDENTIFIER | |
| description: Unique identifier for a user's session, used to track and analyze user behavior across multiple events and interactions within a single session. | |
| expr: SESSION_IDENTIFIER | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - 00112233-4455-6677-8899-aabbccddeeff | |
| - 00000000-1111-2222-3333-444455556666 | |
| - name: STITCHED_USER_ID | |
| description: A unique identifier for a user that is consistent across multiple platforms and devices, created by stitching together user IDs from different sources. | |
| expr: STITCHED_USER_ID | |
| data_type: VARCHAR(16777216) | |
| - name: USER_ID | |
| description: Unique identifier for the user who triggered the event. | |
| expr: USER_ID | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - 00112233-4455-6677-8899-aabbccddeeff | |
| - 00000000-1111-2222-3333-444455556666 | |
| - name: USER_IDENTIFIER | |
| description: Unique identifier for the user who triggered the event. | |
| expr: USER_IDENTIFIER | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - 00112233-4455-6677-8899-aabbccddeeff | |
| - 00000000-1111-2222-3333-444455556666 | |
| - name: VERTICAL_PIXELS_SCROLLED | |
| description: The total number of vertical pixels scrolled by the user on the webpage. | |
| expr: VERTICAL_PIXELS_SCROLLED | |
| data_type: NUMBER(38,0) | |
| - name: VIEW_ID | |
| description: Unique identifier for the view in which the event occurred. | |
| expr: VIEW_ID | |
| data_type: VARCHAR(16777216) | |
| sample_values: | |
| - 00112233-4455-6677-8899-aabbccddeeff | |
| - 00000000-1111-2222-3333-444455556666 | |
| - name: VIEW_IN_SESSION_INDEX | |
| description: The index of the view within the session, representing the order in which the view was displayed to the user during their session. | |
| expr: VIEW_IN_SESSION_INDEX | |
| data_type: NUMBER(18,0) | |
| - name: VIEWS_IN_SESSION | |
| description: The number of times a user viewed a page or screen during a single session. | |
| expr: VIEWS_IN_SESSION | |
| data_type: NUMBER(18,0) | |
| time_dimensions: | |
| - name: END_TSTAMP | |
| description: The timestamp when the event or session ended. | |
| expr: END_TSTAMP | |
| data_type: TIMESTAMP_NTZ(9) | |
| sample_values: | |
| - 2025-01-01T10:10:10.010+0000 | |
| - 2025-01-01T20:32:45.276+0000 | |
| - name: START_TSTAMP | |
| description: The timestamp when the event or session started. | |
| expr: START_TSTAMP | |
| data_type: TIMESTAMP_NTZ(9) | |
| sample_values: | |
| - 2025-01-01T10:10:10.010+0000 | |
| - 2025-01-01T20:32:45.276+0000 | |
| facts: | |
| - name: ABSOLUTE_TIME_IN_S | |
| description: The total time in seconds spent by the user on the page or event, from the moment the page or event started loading until the moment the event was sent to the tracker. | |
| expr: ABSOLUTE_TIME_IN_S | |
| data_type: FLOAT | |
| access_modifier: public_access | |
| - name: HORIZONTAL_PERCENTAGE_SCROLLED | |
| description: The percentage of the webpage that was scrolled horizontally by the user. | |
| expr: HORIZONTAL_PERCENTAGE_SCROLLED | |
| data_type: FLOAT | |
| access_modifier: public_access | |
| sample_values: | |
| - '0' | |
| - '55' | |
| - '100' | |
| - name: VERTICAL_PERCENTAGE_SCROLLED | |
| description: The percentage of the webpage that was scrolled vertically by the user. | |
| expr: VERTICAL_PERCENTAGE_SCROLLED | |
| data_type: FLOAT | |
| access_modifier: public_access | |
| sample_values: | |
| - '10' | |
| - '20' | |
| - '100' | |
| relationships: | |
| - name: SNOWPLOW_UNIFIED_SESSIONS_TO_SNOWPLOW_UNIFIED_USERS | |
| left_table: SNOWPLOW_UNIFIED_SESSIONS | |
| right_table: SNOWPLOW_UNIFIED_USERS | |
| relationship_columns: | |
| - left_column: USER_IDENTIFIER | |
| right_column: USER_IDENTIFIER | |
| relationship_type: many_to_one | |
| join_type: inner | |
| - name: SNOWPLOW_UNIFIED_VIEWS_TO_SNOWPLOW_UNIFIED_SESSIONS | |
| left_table: SNOWPLOW_UNIFIED_VIEWS | |
| right_table: SNOWPLOW_UNIFIED_SESSIONS | |
| relationship_columns: | |
| - left_column: SESSION_IDENTIFIER | |
| right_column: SESSION_IDENTIFIER | |
| relationship_type: many_to_one | |
| join_type: inner | |
| - name: SNOWPLOW_UNIFIED_VIEWS_TO_SNOWPLOW_UNIFIED_USERS | |
| left_table: SNOWPLOW_UNIFIED_VIEWS | |
| right_table: SNOWPLOW_UNIFIED_USERS | |
| relationship_columns: | |
| - left_column: USER_IDENTIFIER | |
| right_column: USER_IDENTIFIER | |
| relationship_type: many_to_one | |
| join_type: inner | |
| verified_queries: | |
| - name: '"What are the top marketing channels by user engagement levels and power user activity in the last 90 days?"' | |
| sql: | | |
| select | |
| mkt_medium, | |
| approx_percentile(sessions, 0.9) as p90_lifetime_sessions, | |
| approx_percentile(views, 0.9) as p90_lifetime_views, | |
| count(*) as users_in_window | |
| from snowplow_unified_users | |
| where end_tstamp >= dateadd(day, -90, current_timestamp()) | |
| group by 1 | |
| order by p90_lifetime_sessions desc | |
| question: What are the top marketing channels by user engagement levels and power user activity in the last 90 days? | |
| verified_at: 1764885180 | |
| verified_by: Semantic Model Generator | |
| - name: '"What is the 7-day retention rate for users who first visited in the last 60 days?"' | |
| sql: | | |
| with | |
| cohorts as ( | |
| select | |
| start_tstamp::date as cohort_date, | |
| user_identifier, | |
| from snowplow_unified_users | |
| where start_tstamp >= dateadd(day, -60, current_timestamp()) | |
| ), | |
| returns7d as ( | |
| select | |
| c.cohort_date, | |
| c.user_identifier, | |
| from cohorts as c | |
| inner join snowplow_unified_views as v on c.user_identifier = v.user_identifier | |
| and dateadd(day, 7, c.cohort_date) = v.start_tstamp::date | |
| group by all | |
| ) | |
| select | |
| c.cohort_date, | |
| count(distinct c.user_identifier) as cohort_size, | |
| count(distinct r.user_identifier) as retained_users_day_7, | |
| retained_users_day_7 / nullifzero(cohort_size) as retention_rate_day_7 | |
| from cohorts as c | |
| left join returns7d as r on c.user_identifier = r.user_identifier | |
| group by 1 | |
| order by 1 | |
| question: What is the 7-day retention rate for users who first visited in the last 60 days? | |
| verified_at: 1764885180 | |
| verified_by: Semantic Model Generator | |
| - name: '"What is the daily breakdown of new versus returning visitors over the last 30 days?"' | |
| sql: | | |
| select | |
| s.start_tstamp::date as date, | |
| count(distinct iff(u.start_tstamp::date = s.start_tstamp::date, s.user_identifier, null)) as new_visitors, | |
| count(distinct iff(u.start_tstamp::date < s.start_tstamp::date, s.user_identifier, null)) as returning_visitors | |
| from snowplow_unified_sessions as s | |
| inner join snowplow_unified_users as u on s.user_identifier = u.user_identifier | |
| where s.start_tstamp >= dateadd(day, -30, current_timestamp()) | |
| group by 1 | |
| order by 1 | |
| question: What is the daily breakdown of new versus returning visitors over the last 30 days? | |
| verified_at: 1764885180 | |
| verified_by: Semantic Model Generator | |
| - name: '"What are the top traffic sources by sessions and visitors over the last 30 days?"' | |
| sql: | | |
| select | |
| s.mkt_source, | |
| s.mkt_medium, | |
| count(distinct s.session_identifier) as sessions, | |
| count(distinct s.user_identifier) as visitors | |
| from snowplow_unified_sessions as s | |
| where s.start_tstamp >= dateadd(day, -30, current_timestamp()) | |
| group by 1, 2 | |
| order by sessions desc | |
| limit 20 | |
| question: What are the top traffic sources by sessions and visitors over the last 30 days? | |
| verified_at: 1764885180 | |
| verified_by: Semantic Model Generator | |
| - name: '"What is the bounce rate by application over the last 30 days?"' | |
| sql: | | |
| with | |
| session_views as ( | |
| select | |
| v.app_id, | |
| v.session_identifier, | |
| count(*) as views_in_session | |
| from snowplow_unified_views as v | |
| where v.start_tstamp >= dateadd(day, -30, current_timestamp()) | |
| group by 1, 2 | |
| ) | |
| select | |
| app_id, | |
| count_if(views_in_session = 1) / cast(nullif(count(*), 0) as float) as bounce_rate | |
| from session_views | |
| group by 1 | |
| order by bounce_rate desc | |
| question: What is the bounce rate by application over the last 30 days? | |
| verified_at: 1764885180 | |
| verified_by: Semantic Model Generator | |
| - name: '"What are the top 20 pages by views and engagement time in the last 7 days?"' | |
| sql: | |
| select | |
| v.page_url, | |
| count(*) as view_count, | |
| sum(v.engaged_time_in_s) as total_engaged_time_seconds, | |
| avg(v.engaged_time_in_s) as avg_engaged_time_seconds | |
| from snowplow_unified_views as v | |
| where v.start_tstamp >= dateadd(day, -7, current_timestamp()) | |
| group by 1 | |
| order by view_count desc | |
| limit 20 | |
| question: What are the top 20 pages by views and engagement time in the last 7 days? | |
| verified_at: 1764885180 | |
| verified_by: Semantic Model Generator | |
| - name: '"What is the average session duration and engaged time per day over the last 30 days?"' | |
| sql: | | |
| select | |
| start_tstamp::date as date, | |
| avg(datediff('second', start_tstamp, end_tstamp)) as avg_session_duration_seconds, | |
| avg(engaged_time_in_s) as avg_engaged_time_seconds | |
| from snowplow_unified_sessions | |
| where start_tstamp >= dateadd(day, -30, current_timestamp()) | |
| group by 1 | |
| order by 1 | |
| question: What is the average session duration and engaged time per day over the last 30 days? | |
| verified_at: 1764885180 | |
| verified_by: Semantic Model Generator | |
| - name: '"How many unique visitors, sessions, and views did we have daily over the last 30 days?"' | |
| sql: | | |
| select | |
| start_tstamp::date as date, | |
| count(distinct user_identifier) as unique_visitors, | |
| count(distinct session_identifier) as sessions, | |
| count(distinct view_id) as views | |
| from snowplow_unified_views | |
| where start_tstamp >= dateadd(day, -30, current_timestamp()) | |
| group by 1 | |
| order by 1 | |
| question: How many unique visitors, sessions, and views did we have daily over the last 30 days? | |
| verified_at: 1764885180 | |
| verified_by: Semantic Model Generator | |
| module_custom_instructions: | |
| sql_generation: |- | |
| - Use Snowflake SQL | |
| - Use lowercase SQL | |
| - For time-related questions over views/sessions/users - use start_tstamp/end_tstamp timestamp columns | |
| - Use user_identifier to join users to sessions and views | |
| - Use session_identifier to join sessions to views | |
| Possible APP_IDs: | |
| - your_app -- https://example.com -- Example Co's main website. Example Co is a company that sells A, B, C and uses this website as an e-commerce platform. | |
| - your_app_support -- https://support.example.com -- Example Co's support website. Example Co uses Support website to help customers use their products. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment