Created
June 9, 2026 12:42
-
-
Save yalisassoon/195f7a5bb60891e543cd5de8567ea991 to your computer and use it in GitHub Desktop.
genie_instructions_pt_2 example_metrics_views
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
| -- Update these to point to your schemas, then run the whole script. | |
| DECLARE OR REPLACE VARIABLE derived_schema STRING DEFAULT 'your_catalog.derived'; -- schema with the snowplow_unified dbt output | |
| DECLARE OR REPLACE VARIABLE semantics_schema STRING DEFAULT 'your_catalog.semantics'; -- schema for the new metric views, can be the same | |
| EXECUTE IMMEDIATE 'CREATE SCHEMA IF NOT EXISTS ' || semantics_schema; | |
| -- ----------------------------------------------------------------------------- | |
| -- snowplow_unified_views_metric_view (views grain -- joins up to sessions and users) | |
| -- ----------------------------------------------------------------------------- | |
| EXECUTE IMMEDIATE 'DROP VIEW IF EXISTS ' || semantics_schema || '.snowplow_unified_views_metric_view'; | |
| EXECUTE IMMEDIATE 'CREATE VIEW ' || semantics_schema || '.snowplow_unified_views_metric_view | |
| WITH METRICS | |
| LANGUAGE YAML | |
| AS $$ | |
| version: 1.1 | |
| source: ' || derived_schema || '.snowplow_unified_views | |
| comment: > | |
| Snowplow Unified VIEWS. One row per content view (page/screen) with view-level | |
| context and engagement (engaged time, scroll depth). Most granular grain, joining | |
| up to sessions via session_identifier and to users via user_identifier so view | |
| activity can be sliced by session- and user-level attributes. | |
| joins: | |
| - name: sessions | |
| source: ' || derived_schema || '.snowplow_unified_sessions | |
| on: source.session_identifier = sessions.session_identifier | |
| rely: | |
| at_most_one_match: true | |
| - name: users | |
| source: ' || derived_schema || '.snowplow_unified_users | |
| on: source.user_identifier = users.user_identifier | |
| rely: | |
| at_most_one_match: true | |
| dimensions: | |
| # --- identity / grain keys (kept as dimensions so counts work in measures) --- | |
| - name: view_id | |
| expr: source.view_id | |
| display_name: View ID | |
| comment: Unique identifier (UUID) for the page/screen view. | |
| - name: session_identifier | |
| expr: source.session_identifier | |
| display_name: Session ID | |
| synonyms: ["session id", "session identifier"] | |
| - name: user_identifier | |
| expr: source.user_identifier | |
| display_name: User ID | |
| synonyms: ["visitor id", "user identifier", "domain user id"] | |
| - name: user_id | |
| expr: source.user_id | |
| display_name: Business User ID | |
| comment: Company-defined user id (login id), may be null if not used. | |
| - name: stitched_user_id | |
| expr: source.stitched_user_id | |
| display_name: Stitched User ID | |
| synonyms: ["cross-device user id", "identity stitched id"] | |
| # --- in-session ordering --- | |
| - name: view_in_session_index | |
| expr: source.view_in_session_index | |
| display_name: View Index In Session | |
| - name: views_in_session | |
| expr: source.views_in_session | |
| display_name: Views In Session | |
| - name: device_session_index | |
| expr: source.device_session_index | |
| display_name: Device Session Index | |
| # --- app / content / page --- | |
| - name: app_id | |
| expr: source.app_id | |
| display_name: App | |
| synonyms: ["application", "site", "property", "app id"] | |
| comment: Application/property that produced the event (e.g. website, docs). | |
| - name: content_group | |
| expr: source.content_group | |
| display_name: Content Group | |
| synonyms: ["content category", "page group"] | |
| - name: page_url | |
| expr: source.page_url | |
| display_name: Page URL | |
| synonyms: ["url", "page", "page address"] | |
| - name: page_urlhost | |
| expr: source.page_urlhost | |
| display_name: Page Host | |
| synonyms: ["domain", "host", "hostname"] | |
| - name: page_urlpath | |
| expr: source.page_urlpath | |
| display_name: Page Path | |
| synonyms: ["path", "url path"] | |
| - name: page_urlquery | |
| expr: source.page_urlquery | |
| display_name: Page Query String | |
| synonyms: ["url query", "query string"] | |
| - name: page_title | |
| expr: source.page_title | |
| display_name: Page Title | |
| synonyms: ["title"] | |
| - name: page_referrer | |
| expr: source.page_referrer | |
| display_name: Page Referrer | |
| synonyms: ["referrer", "referring url"] | |
| # --- acquisition / marketing --- | |
| - name: default_channel_group | |
| expr: source.default_channel_group | |
| display_name: Channel Group | |
| synonyms: ["channel", "marketing channel", "default channel"] | |
| - name: mkt_source | |
| expr: source.mkt_source | |
| display_name: Marketing Source | |
| synonyms: ["utm source", "source"] | |
| - name: mkt_medium | |
| expr: source.mkt_medium | |
| display_name: Marketing Medium | |
| synonyms: ["utm medium", "medium"] | |
| - name: mkt_campaign | |
| expr: source.mkt_campaign | |
| display_name: Marketing Campaign | |
| synonyms: ["utm campaign", "campaign"] | |
| - name: mkt_term | |
| expr: source.mkt_term | |
| display_name: Marketing Term | |
| synonyms: ["utm term", "keyword"] | |
| - name: mkt_content | |
| expr: source.mkt_content | |
| display_name: Marketing Content | |
| synonyms: ["utm content", "ad content"] | |
| - name: mkt_clickid | |
| expr: source.mkt_clickid | |
| display_name: Click ID | |
| synonyms: ["gclid", "click identifier"] | |
| # --- device / geo --- | |
| - name: device_identifier | |
| expr: source.device_identifier | |
| display_name: Device ID | |
| synonyms: ["device identifier", "domain device id"] | |
| - name: device_category | |
| expr: source.device_category | |
| display_name: Device Category | |
| synonyms: ["device type", "device"] | |
| - name: os_type | |
| expr: source.os_type | |
| display_name: OS Type | |
| synonyms: ["operating system"] | |
| - name: os_version | |
| expr: source.os_version | |
| display_name: OS Version | |
| - name: os_timezone | |
| expr: source.os_timezone | |
| display_name: OS Timezone | |
| - name: platform | |
| expr: source.platform | |
| display_name: Platform | |
| - name: geo_country | |
| expr: source.geo_country | |
| display_name: Country | |
| synonyms: ["country code"] | |
| - name: geo_city | |
| expr: source.geo_city | |
| display_name: City | |
| - name: geo_region | |
| expr: source.geo_region | |
| display_name: Region | |
| - name: geo_timezone | |
| expr: source.geo_timezone | |
| display_name: Geo Timezone | |
| - name: screen_resolution | |
| expr: source.screen_resolution | |
| display_name: Screen Resolution | |
| # --- unaggregated numeric facts (analogue of Snowflake `facts`) --- | |
| # Numeric fields can be aggregated at query time (SUM/AVG/...) by Genie/SQL. | |
| - name: engaged_time_in_s | |
| expr: source.engaged_time_in_s | |
| display_name: Engaged Time (s) | |
| synonyms: ["engagement time", "time engaged"] | |
| - name: absolute_time_in_s | |
| expr: source.absolute_time_in_s | |
| display_name: Absolute Time (s) | |
| synonyms: ["time on page", "dwell time"] | |
| - name: vertical_percentage_scrolled | |
| expr: source.vertical_percentage_scrolled | |
| display_name: Vertical Scroll % | |
| synonyms: ["scroll depth", "vertical scroll percentage"] | |
| - name: horizontal_percentage_scrolled | |
| expr: source.horizontal_percentage_scrolled | |
| display_name: Horizontal Scroll % | |
| - name: vertical_pixels_scrolled | |
| expr: source.vertical_pixels_scrolled | |
| display_name: Vertical Pixels Scrolled | |
| - name: horizontal_pixels_scrolled | |
| expr: source.horizontal_pixels_scrolled | |
| display_name: Horizontal Pixels Scrolled | |
| # --- time --- | |
| - name: start_tstamp | |
| expr: source.start_tstamp | |
| display_name: View Start | |
| synonyms: ["start time", "view start timestamp", "date", "time"] | |
| comment: Timestamp when the view started. Use for time filtering/grouping. | |
| - name: end_tstamp | |
| expr: source.end_tstamp | |
| display_name: View End | |
| synonyms: ["end time", "view end timestamp"] | |
| # --- session attributes (joined, many views -> one session) --- | |
| - name: session_default_channel_group | |
| expr: sessions.default_channel_group | |
| display_name: Session Channel Group | |
| synonyms: ["session channel", "visit channel"] | |
| comment: Channel attributed to the parent session (session-level, not view-level). | |
| - name: session_landing_page | |
| expr: sessions.first_page_url | |
| display_name: Session Landing Page | |
| synonyms: ["entry page", "session entry page"] | |
| - name: session_first_event | |
| expr: sessions.first_event_name | |
| display_name: Session First Event | |
| - name: session_last_event | |
| expr: sessions.last_event_name | |
| display_name: Session Last Event | |
| - name: session_is_engaged | |
| expr: sessions.is_engaged | |
| display_name: Session Is Engaged | |
| comment: TRUE if the parent session had a scroll, click or key press. | |
| - name: session_views | |
| expr: sessions.views | |
| display_name: Views In Parent Session | |
| - name: session_total_events | |
| expr: sessions.total_events | |
| display_name: Events In Parent Session | |
| - name: session_start | |
| expr: sessions.start_tstamp | |
| display_name: Session Start | |
| - name: session_end | |
| expr: sessions.end_tstamp | |
| display_name: Session End | |
| # --- user attributes (joined, many views -> one user) --- | |
| - name: user_acquisition_channel | |
| expr: users.default_channel_group | |
| display_name: User Acquisition Channel | |
| synonyms: ["acquisition channel", "first channel"] | |
| comment: First-touch channel of the user (user lifetime attribute). | |
| - name: user_first_geo_country | |
| expr: users.first_geo_country | |
| display_name: User First Country | |
| - name: user_on_web | |
| expr: users.on_web | |
| display_name: User On Web | |
| - name: user_on_mobile | |
| expr: users.on_mobile | |
| display_name: User On Mobile | |
| - name: user_lifetime_sessions | |
| expr: users.sessions | |
| display_name: User Lifetime Sessions | |
| synonyms: ["sessions per user", "lifetime sessions"] | |
| - name: user_lifetime_views | |
| expr: users.views | |
| display_name: User Lifetime Views | |
| - name: user_active_days | |
| expr: users.active_days | |
| display_name: User Active Days | |
| - name: user_first_seen | |
| expr: users.start_tstamp | |
| display_name: User First Seen | |
| synonyms: ["acquisition date", "first visit", "cohort date"] | |
| comment: When the user was first seen. Use with start_tstamp for new-vs-returning. | |
| - name: user_last_seen | |
| expr: users.end_tstamp | |
| display_name: User Last Seen | |
| measures: | |
| - name: view_count | |
| expr: COUNT(1) | |
| display_name: Views | |
| synonyms: ["number of views", "page views", "view count", "pageviews"] | |
| comment: Total page/screen views. | |
| - name: unique_views | |
| expr: COUNT(DISTINCT source.view_id) | |
| display_name: Unique Views | |
| - name: session_count | |
| expr: COUNT(DISTINCT source.session_identifier) | |
| display_name: Sessions | |
| synonyms: ["number of sessions", "session count", "sessions", "visits"] | |
| - name: visitors | |
| expr: COUNT(DISTINCT source.user_identifier) | |
| display_name: Visitors | |
| synonyms: ["unique visitors", "users", "unique users"] | |
| - name: total_engaged_time_s | |
| expr: SUM(source.engaged_time_in_s) | |
| display_name: Total Engaged Time (s) | |
| - name: avg_engaged_time_s | |
| expr: AVG(source.engaged_time_in_s) | |
| display_name: Avg Engaged Time (s) | |
| synonyms: ["average engagement time"] | |
| - name: total_absolute_time_s | |
| expr: SUM(source.absolute_time_in_s) | |
| display_name: Total Time On Page (s) | |
| - name: avg_vertical_scroll_pct | |
| expr: AVG(source.vertical_percentage_scrolled) | |
| display_name: Avg Vertical Scroll % | |
| synonyms: ["average scroll depth"] | |
| - name: avg_horizontal_scroll_pct | |
| expr: AVG(source.horizontal_percentage_scrolled) | |
| display_name: Avg Horizontal Scroll % | |
| $$'; | |
| -- ----------------------------------------------------------------------------- | |
| -- snowplow_unified_sessions_metric_view (sessions grain -- joins up to users) | |
| -- ----------------------------------------------------------------------------- | |
| EXECUTE IMMEDIATE 'DROP VIEW IF EXISTS ' || semantics_schema || '.snowplow_unified_sessions_metric_view'; | |
| EXECUTE IMMEDIATE 'CREATE VIEW ' || semantics_schema || '.snowplow_unified_sessions_metric_view | |
| WITH METRICS | |
| LANGUAGE YAML | |
| AS $$ | |
| version: 1.1 | |
| source: ' || derived_schema || '.snowplow_unified_sessions | |
| comment: > | |
| Snowplow Unified SESSIONS. One row per session (a visit), rolling up view/event | |
| behaviour into session metrics. Joins up to users via user_identifier so session | |
| activity can be sliced by user lifetime attributes (e.g. new vs returning). | |
| joins: | |
| - name: users | |
| source: ' || derived_schema || '.snowplow_unified_users | |
| on: source.user_identifier = users.user_identifier | |
| rely: | |
| at_most_one_match: true | |
| dimensions: | |
| # --- identity / grain keys --- | |
| - name: session_identifier | |
| expr: source.session_identifier | |
| display_name: Session ID | |
| synonyms: ["session id", "session identifier", "visit id"] | |
| - name: user_identifier | |
| expr: source.user_identifier | |
| display_name: User ID | |
| synonyms: ["visitor id", "user identifier"] | |
| - name: user_id | |
| expr: source.user_id | |
| display_name: Business User ID | |
| comment: Company-defined user id (login id), may be null if not used. | |
| - name: stitched_user_id | |
| expr: source.stitched_user_id | |
| display_name: Stitched User ID | |
| - name: device_identifier | |
| expr: source.device_identifier | |
| display_name: Device ID | |
| synonyms: ["device identifier", "domain device id"] | |
| - name: device_session_index | |
| expr: source.device_session_index | |
| display_name: Device Session Index | |
| # --- app / entry & exit pages --- | |
| - name: app_id | |
| expr: source.app_id | |
| display_name: App | |
| synonyms: ["application", "site", "property"] | |
| - name: first_page_url | |
| expr: source.first_page_url | |
| display_name: Landing Page | |
| synonyms: ["entry page", "first page", "landing url"] | |
| - name: first_page_urlhost | |
| expr: source.first_page_urlhost | |
| display_name: Landing Host | |
| synonyms: ["entry domain"] | |
| - name: first_page_urlpath | |
| expr: source.first_page_urlpath | |
| display_name: Landing Path | |
| - name: first_page_urlquery | |
| expr: source.first_page_urlquery | |
| display_name: Landing Query String | |
| - name: first_page_title | |
| expr: source.first_page_title | |
| display_name: Landing Page Title | |
| - name: last_page_url | |
| expr: source.last_page_url | |
| display_name: Exit Page | |
| synonyms: ["last page", "exit url"] | |
| - name: last_page_urlhost | |
| expr: source.last_page_urlhost | |
| display_name: Exit Host | |
| - name: last_page_urlpath | |
| expr: source.last_page_urlpath | |
| display_name: Exit Path | |
| - name: last_page_urlquery | |
| expr: source.last_page_urlquery | |
| display_name: Exit Query String | |
| - name: last_page_title | |
| expr: source.last_page_title | |
| display_name: Exit Page Title | |
| - name: page_referrer | |
| expr: source.page_referrer | |
| display_name: Referrer | |
| synonyms: ["referring url"] | |
| # --- acquisition / marketing --- | |
| - name: default_channel_group | |
| expr: source.default_channel_group | |
| display_name: Channel Group | |
| synonyms: ["channel", "marketing channel"] | |
| - name: mkt_source | |
| expr: source.mkt_source | |
| display_name: Marketing Source | |
| synonyms: ["utm source", "source", "traffic source"] | |
| - name: mkt_medium | |
| expr: source.mkt_medium | |
| display_name: Marketing Medium | |
| synonyms: ["utm medium", "medium"] | |
| - name: mkt_campaign | |
| expr: source.mkt_campaign | |
| display_name: Marketing Campaign | |
| synonyms: ["utm campaign", "campaign"] | |
| - name: mkt_network | |
| expr: source.mkt_network | |
| display_name: Marketing Network | |
| - name: mkt_source_platform | |
| expr: source.mkt_source_platform | |
| display_name: Source Platform | |
| - name: mkt_term | |
| expr: source.mkt_term | |
| display_name: Marketing Term | |
| synonyms: ["keyword"] | |
| - name: mkt_content | |
| expr: source.mkt_content | |
| display_name: Marketing Content | |
| # --- device / geo --- | |
| - name: device_category | |
| expr: source.device_category | |
| display_name: Device Category | |
| synonyms: ["device type", "device"] | |
| - name: os_type | |
| expr: source.os_type | |
| display_name: OS Type | |
| - name: os_version | |
| expr: source.os_version | |
| display_name: OS Version | |
| - name: os_timezone | |
| expr: source.os_timezone | |
| display_name: OS Timezone | |
| - name: platform | |
| expr: source.platform | |
| display_name: Platform | |
| - name: screen_resolution | |
| expr: source.screen_resolution | |
| display_name: Screen Resolution | |
| - name: first_geo_country | |
| expr: source.first_geo_country | |
| display_name: Country | |
| synonyms: ["country code", "geo country"] | |
| - name: first_geo_country_name | |
| expr: source.first_geo_country_name | |
| display_name: Country Name | |
| - name: first_geo_city | |
| expr: source.first_geo_city | |
| display_name: City | |
| - name: last_geo_country | |
| expr: source.last_geo_country | |
| display_name: Last Country | |
| - name: last_geo_country_name | |
| expr: source.last_geo_country_name | |
| display_name: Last Country Name | |
| - name: last_geo_city | |
| expr: source.last_geo_city | |
| display_name: Last City | |
| # --- session behaviour flags / events --- | |
| - name: is_engaged | |
| expr: source.is_engaged | |
| display_name: Is Engaged | |
| synonyms: ["engaged session flag"] | |
| comment: TRUE if the user scrolled, clicked or pressed a key during the session. | |
| - name: first_event_name | |
| expr: source.first_event_name | |
| display_name: First Event | |
| - name: last_event_name | |
| expr: source.last_event_name | |
| display_name: Last Event | |
| # --- unaggregated numeric facts --- | |
| - name: views | |
| expr: source.views | |
| display_name: Views In Session | |
| synonyms: ["page views in session"] | |
| - name: events_in_session | |
| expr: source.total_events | |
| display_name: Events In Session | |
| - name: engaged_time_in_s | |
| expr: source.engaged_time_in_s | |
| display_name: Engaged Time (s) | |
| # --- time --- | |
| - name: start_tstamp | |
| expr: source.start_tstamp | |
| display_name: Session Start | |
| synonyms: ["start time", "session start timestamp", "date", "time"] | |
| comment: Timestamp when the session started. Use for time filtering/grouping. | |
| - name: end_tstamp | |
| expr: source.end_tstamp | |
| display_name: Session End | |
| synonyms: ["end time"] | |
| # --- user attributes (joined, many sessions -> one user) --- | |
| - name: user_acquisition_channel | |
| expr: users.default_channel_group | |
| display_name: User Acquisition Channel | |
| synonyms: ["acquisition channel", "first channel"] | |
| comment: First-touch channel of the user (user lifetime attribute). | |
| - name: user_first_geo_country | |
| expr: users.first_geo_country | |
| display_name: User First Country | |
| - name: user_on_web | |
| expr: users.on_web | |
| display_name: User On Web | |
| - name: user_on_mobile | |
| expr: users.on_mobile | |
| display_name: User On Mobile | |
| - name: user_lifetime_sessions | |
| expr: users.sessions | |
| display_name: User Lifetime Sessions | |
| synonyms: ["sessions per user", "lifetime sessions"] | |
| - name: user_lifetime_views | |
| expr: users.views | |
| display_name: User Lifetime Views | |
| - name: user_active_days | |
| expr: users.active_days | |
| display_name: User Active Days | |
| - name: user_first_seen | |
| expr: users.start_tstamp | |
| display_name: User First Seen | |
| synonyms: ["acquisition date", "first visit", "cohort date"] | |
| comment: When the user was first seen. Compare to start_tstamp for new-vs-returning. | |
| - name: user_last_seen | |
| expr: users.end_tstamp | |
| display_name: User Last Seen | |
| measures: | |
| - name: session_count | |
| expr: COUNT(DISTINCT source.session_identifier) | |
| display_name: Sessions | |
| synonyms: ["number of sessions", "session count", "sessions total", "visits"] | |
| comment: Distinct sessions. | |
| - name: visitors | |
| expr: COUNT(DISTINCT source.user_identifier) | |
| display_name: Visitors | |
| synonyms: ["unique visitors", "users", "unique users"] | |
| - name: total_views | |
| expr: SUM(source.views) | |
| display_name: Total Views | |
| synonyms: ["page views"] | |
| - name: avg_views_per_session | |
| expr: AVG(source.views) | |
| display_name: Avg Views Per Session | |
| synonyms: ["pages per session", "pages per visit"] | |
| - name: bounced_sessions | |
| expr: COUNT_IF(source.views = 1) | |
| display_name: Bounced Sessions | |
| comment: Sessions with exactly one view. | |
| - name: bounce_rate | |
| expr: COUNT_IF(source.views = 1) / CAST(COUNT(1) AS DOUBLE) | |
| display_name: Bounce Rate | |
| synonyms: ["bounce %", "bounce ratio"] | |
| comment: Share of sessions with a single view. Returns a 0-1 fraction. | |
| - name: engaged_sessions | |
| expr: COUNT_IF(source.is_engaged) | |
| display_name: Engaged Sessions | |
| - name: engagement_rate | |
| expr: COUNT_IF(source.is_engaged) / CAST(COUNT(1) AS DOUBLE) | |
| display_name: Engagement Rate | |
| synonyms: ["engaged session rate"] | |
| comment: Share of engaged sessions. Returns a 0-1 fraction. | |
| - name: avg_engaged_time_s | |
| expr: AVG(source.engaged_time_in_s) | |
| display_name: Avg Engaged Time (s) | |
| synonyms: ["average engagement time"] | |
| - name: avg_session_duration_s | |
| expr: AVG(TIMESTAMPDIFF(SECOND, source.start_tstamp, source.end_tstamp)) | |
| display_name: Avg Session Duration (s) | |
| synonyms: ["average session length", "session duration"] | |
| - name: total_events | |
| expr: SUM(source.total_events) | |
| display_name: Total Events | |
| $$'; | |
| -- ----------------------------------------------------------------------------- | |
| -- snowplow_unified_users_metric_view (users grain) | |
| -- ----------------------------------------------------------------------------- | |
| EXECUTE IMMEDIATE 'DROP VIEW IF EXISTS ' || semantics_schema || '.snowplow_unified_users_metric_view'; | |
| EXECUTE IMMEDIATE 'CREATE VIEW ' || semantics_schema || '.snowplow_unified_users_metric_view | |
| WITH METRICS | |
| LANGUAGE YAML | |
| AS $$ | |
| version: 1.1 | |
| source: ' || derived_schema || '.snowplow_unified_users | |
| comment: > | |
| Snowplow Unified USERS. One row per user with traits and lifetime/rolling | |
| metrics, including optional identity stitching across devices. start_tstamp = | |
| first seen, end_tstamp = last seen. | |
| dimensions: | |
| # --- identity --- | |
| - name: user_identifier | |
| expr: user_identifier | |
| display_name: User ID | |
| synonyms: ["visitor id", "user identifier"] | |
| - name: user_id | |
| expr: user_id | |
| display_name: Business User ID | |
| comment: Company-defined user id (login id), may be null if not used. | |
| - name: stitched_user_id | |
| expr: stitched_user_id | |
| display_name: Stitched User ID | |
| synonyms: ["cross-device user id"] | |
| # --- acquisition / marketing (first touch) --- | |
| - name: default_channel_group | |
| expr: default_channel_group | |
| display_name: Acquisition Channel | |
| synonyms: ["channel", "acquisition channel group", "first channel"] | |
| - name: mkt_source | |
| expr: mkt_source | |
| display_name: Acquisition Source | |
| synonyms: ["utm source", "source"] | |
| - name: mkt_medium | |
| expr: mkt_medium | |
| display_name: Acquisition Medium | |
| synonyms: ["utm medium", "medium"] | |
| - name: mkt_campaign | |
| expr: mkt_campaign | |
| display_name: Acquisition Campaign | |
| synonyms: ["utm campaign", "campaign"] | |
| - name: mkt_network | |
| expr: mkt_network | |
| display_name: Acquisition Network | |
| - name: mkt_source_platform | |
| expr: mkt_source_platform | |
| display_name: Source Platform | |
| - name: mkt_term | |
| expr: mkt_term | |
| display_name: Acquisition Term | |
| - name: mkt_content | |
| expr: mkt_content | |
| display_name: Acquisition Content | |
| # --- entry / referrer --- | |
| - name: first_page_url | |
| expr: first_page_url | |
| display_name: First Page | |
| synonyms: ["acquisition page", "first landing page"] | |
| - name: first_page_urlhost | |
| expr: first_page_urlhost | |
| display_name: First Page Host | |
| - name: first_page_urlpath | |
| expr: first_page_urlpath | |
| display_name: First Page Path | |
| - name: first_page_urlquery | |
| expr: first_page_urlquery | |
| display_name: First Page Query String | |
| - name: first_page_title | |
| expr: first_page_title | |
| display_name: First Page Title | |
| - name: last_page_url | |
| expr: last_page_url | |
| display_name: Last Page | |
| - name: last_page_urlhost | |
| expr: last_page_urlhost | |
| display_name: Last Page Host | |
| - name: last_page_urlpath | |
| expr: last_page_urlpath | |
| display_name: Last Page Path | |
| - name: last_page_urlquery | |
| expr: last_page_urlquery | |
| display_name: Last Page Query String | |
| - name: last_page_title | |
| expr: last_page_title | |
| display_name: Last Page Title | |
| - name: page_referrer | |
| expr: page_referrer | |
| display_name: First Referrer | |
| synonyms: ["acquisition referrer"] | |
| # --- platform / device / geo --- | |
| - name: first_platform | |
| expr: first_platform | |
| display_name: First Platform | |
| - name: last_platform | |
| expr: last_platform | |
| display_name: Last Platform | |
| - name: last_os_type | |
| expr: last_os_type | |
| display_name: Last OS Type | |
| - name: last_os_version | |
| expr: last_os_version | |
| display_name: Last OS Version | |
| - name: on_web | |
| expr: on_web | |
| display_name: On Web | |
| comment: TRUE if the user was seen on web. | |
| - name: on_mobile | |
| expr: on_mobile | |
| display_name: On Mobile | |
| comment: TRUE if the user was seen on mobile. | |
| - name: last_screen_resolution | |
| expr: last_screen_resolution | |
| display_name: Last Screen Resolution | |
| - name: first_geo_continent | |
| expr: first_geo_continent | |
| display_name: Continent | |
| - name: first_geo_country | |
| expr: first_geo_country | |
| display_name: Country | |
| synonyms: ["country code", "geo country"] | |
| - name: first_geo_country_name | |
| expr: first_geo_country_name | |
| display_name: Country Name | |
| - name: first_geo_city | |
| expr: first_geo_city | |
| display_name: City | |
| - name: last_geo_country | |
| expr: last_geo_country | |
| display_name: Last Country | |
| - name: last_geo_country_name | |
| expr: last_geo_country_name | |
| display_name: Last Country Name | |
| - name: last_geo_city | |
| expr: last_geo_city | |
| display_name: Last City | |
| # --- unaggregated numeric facts (lifetime) --- | |
| - name: sessions | |
| expr: sessions | |
| display_name: Lifetime Sessions | |
| synonyms: ["sessions per user", "total sessions per user"] | |
| - name: views | |
| expr: views | |
| display_name: Lifetime Views | |
| synonyms: ["views per user"] | |
| - name: active_days | |
| expr: active_days | |
| display_name: Active Days | |
| - name: engaged_time_in_s | |
| expr: engaged_time_in_s | |
| display_name: Lifetime Engaged Time (s) | |
| # --- time --- | |
| - name: start_tstamp | |
| expr: start_tstamp | |
| display_name: First Seen | |
| synonyms: ["first seen", "acquisition date", "first visit", "date", "cohort date"] | |
| comment: Timestamp the user was first seen. Use for cohorting/time grouping. | |
| - name: end_tstamp | |
| expr: end_tstamp | |
| display_name: Last Seen | |
| synonyms: ["last seen", "last visit"] | |
| measures: | |
| - name: user_count | |
| expr: COUNT(DISTINCT user_identifier) | |
| display_name: Users | |
| synonyms: ["number of users", "unique users", "visitors", "unique visitors"] | |
| comment: Distinct users. | |
| - name: total_sessions | |
| expr: SUM(sessions) | |
| display_name: Total Sessions | |
| - name: total_views | |
| expr: SUM(views) | |
| display_name: Total Views | |
| - name: avg_sessions_per_user | |
| expr: AVG(sessions) | |
| display_name: Avg Sessions Per User | |
| synonyms: ["sessions per user", "visit frequency"] | |
| - name: avg_views_per_user | |
| expr: AVG(views) | |
| display_name: Avg Views Per User | |
| - name: avg_active_days | |
| expr: AVG(active_days) | |
| display_name: Avg Active Days | |
| - name: total_engaged_time_s | |
| expr: SUM(engaged_time_in_s) | |
| display_name: Total Engaged Time (s) | |
| - name: p90_lifetime_sessions | |
| expr: APPROX_PERCENTILE(sessions, 0.9) | |
| display_name: P90 Lifetime Sessions | |
| synonyms: ["90th percentile sessions", "power user sessions"] | |
| comment: 90th percentile of lifetime sessions per user (power-user threshold). | |
| - name: p90_lifetime_views | |
| expr: APPROX_PERCENTILE(views, 0.9) | |
| display_name: P90 Lifetime Views | |
| synonyms: ["90th percentile views"] | |
| $$'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment