Snowplow Web Models

caution

This page is auto-generated from our dbt packages, some information may be incomplete

Snowplow Web

Snowplow Web Base Events This Run

models/base/scratch/<adaptor>/snowplow_web_base_events_this_run.sql

Description

For any given run, this table contains all required events to be consumed by subsequent nodes in the Snowplow dbt web package. This is a cleaned, deduped dataset, containing all columns from the raw events table as well as having the page_view_id joined in from the page view context.

Note: This table should be used as the input to any custom modules that require event level data, rather than selecting straight from atomic.events

File Paths

bigquery
databricks
default
snowflake

models/base/scratch/bigquery/snowplow_web_base_events_this_run.sql

models/base/scratch/databricks/snowplow_web_base_events_this_run.sql

models/base/scratch/default/snowplow_web_base_events_this_run.sql

models/base/scratch/snowflake/snowplow_web_base_events_this_run.sql

Details

Columns

note

Base event this run table column lists may be incomplete and is missing contexts/unstructs, please check your warehouse for a more accurate column list.

Column Name	Description
app_id	Application ID e.g. ‘angry-birds’ is used to distinguish different applications that are being tracked by the same Snowplow stack, e.g. production versus dev.
platform	Platform e.g. ‘web’
etl_tstamp	Timestamp event began ETL e.g. ‘2017-01-26 00:01:25.292’
collector_tstamp	Time stamp for the event recorded by the collector e.g. ‘2013-11-26 00:02:05’
dvce_created_tstamp	Timestamp event was recorded on the client device e.g. ‘2013-11-26 00:03:57.885’
event	The type of event recorded e.g. ‘page_view’
event_id	A UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’
txn_id	Transaction ID set client-side, used to de-dupe records e.g. 421828
name_tracker	Tracker namespace e.g. ‘sp1’
v_tracker	Tracker version e.g. ‘js-3.0.0’
v_collector	Collector version e.g. ‘ssc-2.1.0-kinesis’
v_etl	ETL version e.g. ‘snowplow-micro-1.1.0-common-1.4.2’
user_id	Unique ID set by business e.g. ‘jon.doe@email.com’
user_ipaddress	User IP address e.g. ‘92.231.54.234’
user_fingerprint	A user fingerprint generated by looking at the individual browser features e.g. 2161814971
domain_userid	User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’
domain_sessionidx	A visit / session index e.g. 3
network_userid	User ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’
geo_country	ISO 3166-1 code for the country the visitor is located in e.g. ‘GB’, ‘US’
geo_region	ISO-3166-2 code for country region the visitor is in e.g. ‘I9’, ‘TX’
geo_city	City the visitor is in e.g. ‘New York’, ‘London’
geo_zipcode	Postcode the visitor is in e.g. ‘94109’
geo_latitude	Visitor location latitude e.g. 37.443604
geo_longitude	Visitor location longitude e.g. -122.4124
geo_region_name	Visitor region name e.g. ‘Florida’
ip_isp	Visitor’s ISP e.g. ‘FDN Communications’
ip_organization	Organization associated with the visitor’s IP address – defaults to ISP name if none is found e.g. ‘Bouygues Telecom’
ip_domain	Second level domain name associated with the visitor’s IP address e.g. ‘nuvox.net’
ip_netspeed	Visitor’s connection type e.g. ‘Cable/DSL’
page_url	The page URL e.g. ‘http://www.example.com’
page_title	Web page title e.g. ‘Snowplow Docs – Understanding the structure of Snowplow data’
page_referrer	URL of the referrer e.g. ‘http://www.referrer.com’
page_urlscheme	Scheme aka protocol e.g. ‘https’
page_urlhost	Host aka domain e.g. ‘“www.snowplow.io’
page_urlport	Port if specified, 80 if not 80
page_urlpath	Path to page e.g. ‘/product/index.html’
page_urlquery	Querystring e.g. ‘id=GTM-DLRG’
page_urlfragment	Fragment aka anchor e.g. ‘4-conclusion’
refr_urlscheme	Referer scheme e.g. ‘http’
refr_urlhost	Referer host e.g. ‘www.bing.com’
refr_urlport	Referer port e.g. 80
refr_urlpath	Referer page path e.g. ‘/images/search’
refr_urlquery	Referer URL querystring e.g. ‘q=psychic+oracle+cards’
refr_urlfragment	Referer URL fragment
refr_medium	Type of referer e.g. ‘search’, ‘internal’
refr_source	Name of referer if recognised e.g. ‘Bing images’
refr_term	Keywords if source is a search engine e.g. ‘psychic oracle cards’
mkt_medium	Type of traffic source e.g. ‘cpc’, ‘affiliate’, ‘organic’, ‘social’
mkt_source	The company / website where the traffic came from e.g. ‘Google’, ‘Facebook’
mkt_term	Any keywords associated with the referrer e.g. ‘new age tarot decks’
mkt_content	The content of the ad. (Or an ID so that it can be looked up.) e.g. 13894723
mkt_campaign	The campaign ID e.g. ‘diageo-123’
se_category	Category of event e.g. ‘ecomm’, ‘video’
se_action	Action performed / event name e.g. ‘add-to-basket’, ‘play-video’
se_label	The object of the action e.g. the ID of the video played or SKU of the product added-to-basket e.g. ‘pbz00123’
se_property	A property associated with the object of the action e.g. ‘HD’, ‘large’
se_value	A value associated with the event / action e.g. the value of goods added-to-basket e.g. 9.99
tr_orderid	Order ID e.g. ‘#134’
tr_affiliation	Transaction affiliation (e.g. store where sale took place) e.g. ‘web’
tr_total	Total transaction value e.g. 12.99
tr_tax	Total tax included in transaction value e.g. 3.00
tr_shipping	Delivery cost charged e.g. 0.00
tr_city	Delivery address, city e.g. ‘London’
tr_state	Delivery address, state e.g. ‘Washington’
tr_country	Delivery address, country e.g. ‘France’
ti_orderid	Order ID e.g. ‘#134’
ti_sku	Product SKU e.g. ‘pbz00123’
ti_name	Product name e.g. ‘Cone pendulum’
ti_category	Product category e.g. ‘New Age’
ti_price	Product unit price e.g. 9.99
ti_quantity	Number of product in transaction e.g. 2
pp_xoffset_min	Minimum page x offset seen in the last ping period e.g. 0
pp_xoffset_max	Maximum page x offset seen in the last ping period e.g. 100
pp_yoffset_min	Minimum page y offset seen in the last ping period e.g. 0
pp_yoffset_max	Maximum page y offset seen in the last ping period e.g. 200
useragent	Raw useragent
br_name	Browser name e.g. ‘Firefox 12’
br_family	Browser family e.g. ‘Firefox’
br_version	Browser version e.g. ‘12.0’
br_type	Browser type e.g. ‘Browser’
br_renderengine	Browser rendering engine e.g. ‘GECKO’
br_lang	Language the browser is set to e.g. ‘en-GB’
br_features_pdf	Whether the browser recognizes PDFs e.g. True
br_features_flash	Whether Flash is installed e.g. True
br_features_java	Whether Java is installed e.g. True
br_features_director	Whether Adobe Shockwave is installed e.g. True
br_features_quicktime	Whether QuickTime is installed e.g. True
br_features_realplayer	Whether RealPlayer is installed e.g. True
br_features_windowsmedia	Whether mplayer2 is installed e.g. True
br_features_gears	Whether Google Gears is installed e.g. True
br_features_silverlight	Whether Microsoft Silverlight is installed e.g. True
br_cookies	Whether cookies are enabled e.g. True
br_colordepth	Bit depth of the browser color palette e.g. 24
br_viewwidth	Viewport width e.g. 1000
br_viewheight	Viewport height e.g. 1000
os_name	Name of operating system e.g. ‘Android’
os_family	Operating system family e.g. ‘Linux’
os_manufacturer	Company responsible for OS e.g. ‘Apple’
os_timezone	Client operating system timezone e.g. ‘Europe/London’
dvce_type	Type of device e.g. ‘Computer’
dvce_ismobile	Is the device mobile? e.g. True
dvce_screenwidth	Screen width in pixels e.g. 1900
dvce_screenheight	Screen height in pixels e.g. 1024
doc_charset	The page’s character encoding e.g. , ‘UTF-8’
doc_width	The page’s width in pixels e.g. 1024
doc_height	The page’s height in pixels e.g. 3000
tr_currency	Currency e.g. ‘USD’
tr_total_base	Total in base currency e.g. 12.99
tr_tax_base	Total tax in base currency e.g. 3.00
tr_shipping_base	decimal Delivery cost in base currency e.g. 0.00
ti_currency	Currency e.g. ‘EUR’
ti_price_base	decimal Price in base currency e.g. 9.99
base_currency	Reporting currency e.g. ‘GBP’
geo_timezone	Visitor timezone name e.g. ‘Europe/London’
mkt_clickid	The click ID e.g. ‘ac3d8e459’
mkt_network	The ad network to which the click ID belongs e.g. ‘DoubleClick’
etl_tags	JSON of tags for this ETL run e.g. “[‘prod’]”
dvce_sent_tstamp	When the event was sent by the client device e.g. ‘2013-11-26 00:03:58.032’
refr_domain_userid	The Snowplow domain_userid of the referring website e.g. ‘bc2e92ec6c204a14’
refr_dvce_tstamp	The time of attaching the domain_userid to the inbound link e.g. ‘2013-11-26 00:02:05’
domain_sessionid	A visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’
derived_tstamp	Timestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’
event_vendor	Who defined the event e.g. ‘com.acme’
event_name	Event name e.g. ‘link_click’
event_format	Format for event e.g. ‘jsonschema’
event_version	Version of event schema e.g. ‘1-0-2’
event_fingerprint	Hash client-set event fields e.g. AADCE520E20C2899F4CED228A79A3083
true_tstamp	User-set “true timestamp” for the event e.g. ‘2013-11-26 00:02:04’
page_view_id

Code

bigquery
databricks
default
snowflake

Source

{{
  config(
    tags=["this_run"]
  )
}}

{%- set lower_limit, upper_limit = snowplow_utils.return_limits_from_model(ref('snowplow_web_base_sessions_this_run'),
                                                                          'start_tstamp',
                                                                          'end_tstamp') %}

-- without downstream joins, it's safe to dedupe by picking the first event_id found.
select
  array_agg(e order by e.collector_tstamp limit 1)[offset(0)].*

from (

  select
    a.contexts_com_snowplowanalytics_snowplow_web_page_1_0_0[safe_offset(0)].id as page_view_id,
    b.domain_userid, -- take domain_userid from manifest. This ensures only 1 domain_userid per session.
    a.* except(contexts_com_snowplowanalytics_snowplow_web_page_1_0_0, domain_userid)

  from {{ var('snowplow__events') }} as a
  inner join {{ ref('snowplow_web_base_sessions_this_run') }} as b
  on a.domain_sessionid = b.session_id

  where a.collector_tstamp <= {{ snowplow_utils.timestamp_add('day', var("snowplow__max_session_days", 3), 'b.start_tstamp') }}
  and a.dvce_sent_tstamp <= {{ snowplow_utils.timestamp_add('day', var("snowplow__days_late_allowed", 3), 'a.dvce_created_tstamp') }}
  and a.collector_tstamp >= {{ lower_limit }}
  and a.collector_tstamp <= {{ upper_limit }}
  {% if var('snowplow__derived_tstamp_partitioned', true) and target.type == 'bigquery' | as_bool() %}
    and a.derived_tstamp >= {{ snowplow_utils.timestamp_add('hour', -1, lower_limit) }}
    and a.derived_tstamp <= {{ upper_limit }}
  {% endif %}
  and {{ snowplow_utils.app_id_filter(var("snowplow__app_id",[])) }}

) e
group by e.event_id

Source

{{
  config(
    tags=["this_run"]
  )
}}

{%- set lower_limit, upper_limit = snowplow_utils.return_limits_from_model(ref('snowplow_web_base_sessions_this_run'),
                                                                          'start_tstamp',
                                                                          'end_tstamp') %}
select
    a.contexts_com_snowplowanalytics_snowplow_web_page_1[0].id as page_view_id,
    b.domain_userid, -- take domain_userid from manifest. This ensures only 1 domain_userid per session.
    a.* except(contexts_com_snowplowanalytics_snowplow_web_page_1, a.domain_userid)

from {{ var('snowplow__events') }} as a
inner join {{ ref('snowplow_web_base_sessions_this_run') }} as b
on a.domain_sessionid = b.session_id

where a.collector_tstamp <= {{ snowplow_utils.timestamp_add('day', var("snowplow__max_session_days", 3), 'b.start_tstamp') }}
and a.dvce_sent_tstamp <= {{ snowplow_utils.timestamp_add('day', var("snowplow__days_late_allowed", 3), 'a.dvce_created_tstamp') }}
and a.collector_tstamp >= {{ lower_limit }}
and a.collector_tstamp <= {{ upper_limit }}
and {{ snowplow_utils.app_id_filter(var("snowplow__app_id",[])) }}

qualify row_number() over (partition by a.event_id order by a.collector_tstamp, a.etl_tstamp) = 1

Source

{{
  config(
    sort='collector_tstamp',
    dist='event_id',
    tags=["this_run"]
  )
}}

{%- set lower_limit, upper_limit = snowplow_utils.return_limits_from_model(ref('snowplow_web_base_sessions_this_run'),
                                                                          'start_tstamp',
                                                                          'end_tstamp') %}

/* Dedupe logic: Per dupe event_id keep earliest row ordered by collector_tstamp.
   If multiple earliest rows, take arbitrary one using row_number(). */

with events_this_run AS (
  select
    a.app_id,
    a.platform,
    a.etl_tstamp,
    a.collector_tstamp,
    a.dvce_created_tstamp,
    a.event,
    a.event_id,
    a.txn_id,
    a.name_tracker,
    a.v_tracker,
    a.v_collector,
    a.v_etl,
    a.user_id,
    a.user_ipaddress,
    a.user_fingerprint,
    b.domain_userid, -- take domain_userid from manifest. This ensures only 1 domain_userid per session.
    a.domain_sessionidx,
    a.network_userid,
    a.geo_country,
    a.geo_region,
    a.geo_city,
    a.geo_zipcode,
    a.geo_latitude,
    a.geo_longitude,
    a.geo_region_name,
    a.ip_isp,
    a.ip_organization,
    a.ip_domain,
    a.ip_netspeed,
    a.page_url,
    a.page_title,
    a.page_referrer,
    a.page_urlscheme,
    a.page_urlhost,
    a.page_urlport,
    a.page_urlpath,
    a.page_urlquery,
    a.page_urlfragment,
    a.refr_urlscheme,
    a.refr_urlhost,
    a.refr_urlport,
    a.refr_urlpath,
    a.refr_urlquery,
    a.refr_urlfragment,
    a.refr_medium,
    a.refr_source,
    a.refr_term,
    a.mkt_medium,
    a.mkt_source,
    a.mkt_term,
    a.mkt_content,
    a.mkt_campaign,
    a.se_category,
    a.se_action,
    a.se_label,
    a.se_property,
    a.se_value,
    a.tr_orderid,
    a.tr_affiliation,
    a.tr_total,
    a.tr_tax,
    a.tr_shipping,
    a.tr_city,
    a.tr_state,
    a.tr_country,
    a.ti_orderid,
    a.ti_sku,
    a.ti_name,
    a.ti_category,
    a.ti_price,
    a.ti_quantity,
    a.pp_xoffset_min,
    a.pp_xoffset_max,
    a.pp_yoffset_min,
    a.pp_yoffset_max,
    a.useragent,
    a.br_name,
    a.br_family,
    a.br_version,
    a.br_type,
    a.br_renderengine,
    a.br_lang,
    a.br_features_pdf,
    a.br_features_flash,
    a.br_features_java,
    a.br_features_director,
    a.br_features_quicktime,
    a.br_features_realplayer,
    a.br_features_windowsmedia,
    a.br_features_gears,
    a.br_features_silverlight,
    a.br_cookies,
    a.br_colordepth,
    a.br_viewwidth,
    a.br_viewheight,
    a.os_name,
    a.os_family,
    a.os_manufacturer,
    a.os_timezone,
    a.dvce_type,
    a.dvce_ismobile,
    a.dvce_screenwidth,
    a.dvce_screenheight,
    a.doc_charset,
    a.doc_width,
    a.doc_height,
    a.tr_currency,
    a.tr_total_base,
    a.tr_tax_base,
    a.tr_shipping_base,
    a.ti_currency,
    a.ti_price_base,
    a.base_currency,
    a.geo_timezone,
    a.mkt_clickid,
    a.mkt_network,
    a.etl_tags,
    a.dvce_sent_tstamp,
    a.refr_domain_userid,
    a.refr_dvce_tstamp,
    a.domain_sessionid,
    a.derived_tstamp,
    a.event_vendor,
    a.event_name,
    a.event_format,
    a.event_version,
    a.event_fingerprint,
    a.true_tstamp,
    {% if var('snowplow__enable_load_tstamp', true) %}
      a.load_tstamp,
    {% endif %}
    row_number() over (partition by a.event_id order by a.collector_tstamp) as event_id_dedupe_index

  from {{ var('snowplow__events') }} as a
  inner join {{ ref('snowplow_web_base_sessions_this_run') }} as b
  on a.domain_sessionid = b.session_id

  where a.collector_tstamp <= {{ snowplow_utils.timestamp_add('day', var("snowplow__max_session_days", 3), 'b.start_tstamp') }}
  and a.dvce_sent_tstamp <= {{ snowplow_utils.timestamp_add('day', var("snowplow__days_late_allowed", 3), 'a.dvce_created_tstamp') }}
  and a.collector_tstamp >= {{ lower_limit }}
  and a.collector_tstamp <= {{ upper_limit }}
  and {{ snowplow_utils.app_id_filter(var("snowplow__app_id",[])) }}
)

, page_context as (
  select
    root_id,
    root_tstamp,
    id as page_view_id,
    row_number() over (partition by root_id order by root_tstamp) as page_context_dedupe_index

  from {{ var('snowplow__page_view_context') }}
  where
    root_tstamp >= {{ lower_limit }}
    and root_tstamp <= {{ upper_limit }}
)

, page_context_dedupe as (
  select
   *

  from page_context
  where page_context_dedupe_index = 1
)

select
  e.*,
  pc.page_view_id

from events_this_run as e
left join page_context_dedupe as pc
on e.event_id = pc.root_id
and e.collector_tstamp = pc.root_tstamp

where e.event_id_dedupe_index = 1

Source

{{
  config(
    tags=["this_run"],
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
  )
}}

{%- set lower_limit, upper_limit = snowplow_utils.return_limits_from_model(ref('snowplow_web_base_sessions_this_run'),
                                                                          'start_tstamp',
                                                                          'end_tstamp') %}

select
  a.contexts_com_snowplowanalytics_snowplow_web_page_1[0]:id::varchar as page_view_id,
  b.domain_userid, -- take domain_userid from manifest. This ensures only 1 domain_userid per session.
  a.* exclude(contexts_com_snowplowanalytics_snowplow_web_page_1, domain_userid)

from {{ var('snowplow__events') }} as a
inner join {{ ref('snowplow_web_base_sessions_this_run') }} as b
on a.domain_sessionid = b.session_id

where a.collector_tstamp <= {{ snowplow_utils.timestamp_add('day', var("snowplow__max_session_days", 3), 'b.start_tstamp') }}
and a.dvce_sent_tstamp <= {{ snowplow_utils.timestamp_add('day', var("snowplow__days_late_allowed", 3), 'a.dvce_created_tstamp') }}
and a.collector_tstamp >= {{ lower_limit }}
and a.collector_tstamp <= {{ upper_limit }}
and {{ snowplow_utils.app_id_filter(var("snowplow__app_id",[])) }}

qualify row_number() over (partition by a.event_id order by a.collector_tstamp) = 1

Depends On

Models
Macros

model.snowplow_web.snowplow_web_base_sessions_this_run

Referenced By

Models

Snowplow Web Base New Event Limits

models/base/scratch/snowplow_web_base_new_event_limits.sql

Description

This table contains the lower and upper timestamp limits for the given run of the web model. These limits are used to select new events from the events table.

Type: Table

Details

Columns

Column Name	Description	Type
lower_limit	The lower `collector_tstamp` limit for the run	timestamp_ntz
upper_limit	The upper `collector_tstamp` limit for the run	timestamp_ntz

Code

default

Source

{{ config(
   post_hook=["{{snowplow_utils.print_run_limits(this)}}"],
   sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
   )
}}


{%- set models_in_run = snowplow_utils.get_enabled_snowplow_models('snowplow_web') -%}

{% set min_last_success,
         max_last_success,
         models_matched_from_manifest,
         has_matched_all_models = snowplow_utils.get_incremental_manifest_status(ref('snowplow_web_incremental_manifest'),
                                                                                 models_in_run) -%}


{% set run_limits_query = snowplow_utils.get_run_limits(min_last_success,
                                                          max_last_success,
                                                          models_matched_from_manifest,
                                                          has_matched_all_models,
                                                          var("snowplow__start_date","2020-01-01")) -%}


{{ run_limits_query }}

Depends On

Models
Macros

model.snowplow_web.snowplow_web_incremental_manifest

Referenced By

Models

Snowplow Web Base Quarantined Sessions

models/base/manifest/snowplow_web_base_quarantined_sessions.sql

Description

This table contains any sessions that have been quarantined. Sessions are quarantined once they exceed the maximum allowed session length, defined by snowplow__max_session_days. Once quarantined, no further events from these sessions will be processed. Events up until the point of quarantine remain in your derived tables. The reason for removing long sessions is to reduce table scans on both the events table and all derived tables. This improves performance greatly.

Type: Table

Details

Columns

Column Name	Description	Type
session_id	The `session_id` of the quarantined session	text

Code

default

Source

{{
  config(
    materialized='incremental',
    full_refresh=snowplow_web.allow_refresh(),
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt')),
    tblproperties={
      'delta.autoOptimize.optimizeWrite' : 'true',
      'delta.autoOptimize.autoCompact' : 'true'
    }
  )
}}

/*
Boilerplate to generate table.
Table updated as part of post-hook on sessions_this_run
Any sessions exceeding max_session_days are quarantined
Once quarantined, any subsequent events from the session will not be processed.
This significantly reduces table scans
*/

with prep as (
  select
    cast(null as {{ type_string() }}) session_id
)

select *

from prep
where false

Depends On

Macros

Referenced By

Models

Snowplow Web Base Sessions Lifecycle Manifest

models/base/manifest/snowplow_web_base_sessions_lifecycle_manifest.sql

Description

This incremental table is a manifest of all sessions that have been processed by the Snowplow dbt web model. For each session, the start and end timestamp is recorded.

By knowing the lifecycle of a session the model is able to able to determine which sessions and thus events to process for a given timeframe, as well as the complete date range required to reprocess all events of each session.

Type: Table

Details

Columns

Column Name	Description	Type
session_id	A visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
domain_userid	User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’	text
start_tstamp	The `collector_tstamp` when the session began	timestamp_ntz
end_tstamp	The `collector_tstamp` when the session ended	timestamp_ntz

Code

default

Source

{{
  config(
    materialized='incremental',
    unique_key='session_id',
    upsert_date_key='start_tstamp',
    sort='start_tstamp',
    dist='session_id',
    partition_by = snowplow_utils.get_value_by_target_type(bigquery_val={
      "field": "start_tstamp",
      "data_type": "timestamp"
    }, databricks_val='start_tstamp_date'),
    cluster_by=snowplow_web.web_cluster_by_fields_sessions_lifecycle(),
    full_refresh=snowplow_web.allow_refresh(),
    tags=["manifest"],
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt')),
    tblproperties={
      'delta.autoOptimize.optimizeWrite' : 'true',
      'delta.autoOptimize.autoCompact' : 'true'
    },
    snowplow_optimize = true
  )
}}

-- Known edge cases:
-- 1: Rare case with multiple domain_userid per session.

{% set lower_limit, upper_limit, _ = snowplow_utils.return_base_new_event_limits(ref('snowplow_web_base_new_event_limits')) %}
{% set session_lookback_limit = snowplow_utils.get_session_lookback_limit(lower_limit) %}
{% set is_run_with_new_events = snowplow_utils.is_run_with_new_events('snowplow_web') %}

with new_events_session_ids as (
  select
    e.domain_sessionid as session_id,
    max(e.domain_userid) as domain_userid, -- Edge case 1: Arbitary selection to avoid window function like first_value.
    min(e.collector_tstamp) as start_tstamp,
    max(e.collector_tstamp) as end_tstamp

  from {{ var('snowplow__events') }} e

  where
    e.domain_sessionid is not null
    and not exists (select 1 from {{ ref('snowplow_web_base_quarantined_sessions') }} as a where a.session_id = e.domain_sessionid) -- don't continue processing v.long sessions
    and e.dvce_sent_tstamp <= {{ snowplow_utils.timestamp_add('day', var("snowplow__days_late_allowed", 3), 'dvce_created_tstamp') }} -- don't process data that's too late
    and e.collector_tstamp >= {{ lower_limit }}
    and e.collector_tstamp <= {{ upper_limit }}
    and {{ snowplow_utils.app_id_filter(var("snowplow__app_id",[])) }}
    and {{ is_run_with_new_events }} --don't reprocess sessions that have already been processed.
    {% if var('snowplow__derived_tstamp_partitioned', true) and target.type == 'bigquery' | as_bool() %} -- BQ only
      and e.derived_tstamp >= {{ lower_limit }}
      and e.derived_tstamp <= {{ upper_limit }}
    {% endif %}

  group by 1
  )

{% if is_incremental() %}

, previous_sessions as (
  select *

  from {{ this }}

  where start_tstamp >= {{ session_lookback_limit }}
  and {{ is_run_with_new_events }} --don't reprocess sessions that have already been processed.
)

, session_lifecycle as (
  select
    ns.session_id,
    coalesce(self.domain_userid, ns.domain_userid) as domain_userid, -- Edge case 1: Take previous value to keep domain_userid consistent. Not deterministic but performant
    least(ns.start_tstamp, coalesce(self.start_tstamp, ns.start_tstamp)) as start_tstamp,
    greatest(ns.end_tstamp, coalesce(self.end_tstamp, ns.end_tstamp)) as end_tstamp -- BQ 1 NULL will return null hence coalesce

  from new_events_session_ids ns
  left join previous_sessions as self
    on ns.session_id = self.session_id

  where
    self.session_id is null -- process all new sessions
    or self.end_tstamp < {{ snowplow_utils.timestamp_add('day', var("snowplow__max_session_days", 3), 'self.start_tstamp') }} --stop updating sessions exceeding 3 days
  )

{% else %}

, session_lifecycle as (

  select * from new_events_session_ids

)

{% endif %}

select
  sl.session_id,
  sl.domain_userid,
  sl.start_tstamp,
  least({{ snowplow_utils.timestamp_add('day', var("snowplow__max_session_days", 3), 'sl.start_tstamp') }}, sl.end_tstamp) as end_tstamp -- limit session length to max_session_days
  {% if target.type in ['databricks', 'spark'] -%}
  , DATE(start_tstamp) as start_tstamp_date
  {%- endif %}

from session_lifecycle sl

Depends On

Models
Macros

Referenced By

Models

model.snowplow_web.snowplow_web_base_sessions_this_run

Snowplow Web Base Sessions This Run

models/base/scratch/snowplow_web_base_sessions_this_run.sql

Description

For any given run, this table contains all the required sessions.

Type: Table

Details

Columns

Column Name	Description	Type
session_id	A visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
domain_userid	User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’	text
start_tstamp	The `collector_tstamp` when the session began	timestamp_ntz
end_tstamp	The `collector_tstamp` when the session ended	timestamp_ntz

Code

default

Source

{{
  config(
    tags=["this_run"],
    post_hook=[
    "{{ snowplow_utils.quarantine_sessions('snowplow_web', var('snowplow__max_session_days')) }}"
    ],
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
  )
}}

{%- set lower_limit,
        upper_limit,
        session_start_limit = snowplow_utils.return_base_new_event_limits(ref('snowplow_web_base_new_event_limits')) %}

select
  s.session_id,
  s.domain_userid,
  s.start_tstamp,
  -- end_tstamp used in next step to limit events. When backfilling, set end_tstamp to upper_limit if end_tstamp > upper_limit.
  -- This ensures we don't accidentally process events after upper_limit
  case when s.end_tstamp > {{ upper_limit }} then {{ upper_limit }} else s.end_tstamp end as end_tstamp

from {{ ref('snowplow_web_base_sessions_lifecycle_manifest')}} s

where
-- General window of start_tstamps to limit table scans. Logic complicated by backfills.
-- To be within the run, session start_tstamp must be >= lower_limit - max_session_days as we limit end_tstamp in manifest to start_tstamp + max_session_days
s.start_tstamp >= {{ session_start_limit }}
and s.start_tstamp <= {{ upper_limit }}
-- Select sessions within window that either; start or finish between lower & upper limit, start and finish outside of lower and upper limits
and not (s.start_tstamp > {{ upper_limit }} or s.end_tstamp < {{ lower_limit }})

Depends On

Models
Macros

Referenced By

Models

Snowplow Web Consent Cmp Stats

models/optional_modules/consent/snowplow_web_consent_cmp_stats.sql

Description

Used for modeling cmp_visible events and related metrics

Type: Table

Details

Columns

Column Name	Description	Type
event_id	A UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
domain_userid	The optional userid of a user	text
page_view_id	A UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
domain_sessionid	A visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
cmp_load_time	The time taken for the consent box to be shown to the screen	float
cmp_tstamp	The timestamp of the cmp_visible event	timestamp_ntz
first_consent_event_tstamp	The timestamp of the first consent event after a cmp_visible event	timestamp_ntz
first_consent_event_type	The event type of the first consent event after a cmp_visible event	text
cmp_interaction_time	The time it takes for the user to make a consent choice after the cmp_visible event is fired	number

Code

default

Source

{{
  config(
    materialized='table',
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
  )
}}

{%- if target.type in ('postgres') -%}

with events as (

  select
    event_id,
    domain_userid,
    page_view_id,
    domain_sessionid,
    derived_tstamp,
    event_name,
    event_type,
    cmp_load_time,
    -- postgres does not allow the IGNORE NULL clause within last_value(), below workaround should do the same: removing NULLS using array_remove then using the COUNT window function (which counts the number of non-null items and count is bounded up to the current row) to access the array using that as its index position
    (array_remove(array_agg(case when event_name = 'cmp_visible' then event_id else null end) over (partition by domain_userid order by derived_tstamp), null))[count(case when event_name = 'cmp_visible' then event_id else null end) over (partition by domain_userid order by derived_tstamp rows between unbounded preceding and current row)] as cmp_id

  from {{ ref('snowplow_web_consent_log') }}

  where event_type <> 'pending' or event_type is null

)

{%- elif target.type in ('databricks', 'spark') -%}

with events as (

   select
    event_id,
    domain_userid,
    page_view_id,
    domain_sessionid,
    derived_tstamp,
    event_name,
    event_type,
    cmp_load_time,
    last_value(case when event_name = 'cmp_visible' then event_id else null end, TRUE)
    over (partition by domain_userid order by derived_tstamp
    rows between unbounded preceding and current row) as cmp_id

  from {{ ref('snowplow_web_consent_log') }}

  where event_type <> 'pending' or event_type is null

)

{%- else -%}

with events as (

  select
    event_id,
    domain_userid,
    page_view_id,
    domain_sessionid,
    derived_tstamp,
    event_name,
    event_type,
    cmp_load_time,
    last_value(case when event_name = 'cmp_visible' then event_id else null end ignore nulls)
    over (partition by domain_userid order by derived_tstamp
    rows between unbounded preceding and current row) as cmp_id

  from {{ ref('snowplow_web_consent_log') }}

  where event_type <> 'pending' or event_type is null

)

{%- endif -%}

, event_orders as (

  select
    event_id,
    event_type,
    cmp_id,
    derived_tstamp,
    row_number() over(partition by cmp_id order by derived_tstamp) as row_num

  from events

)

, first_consent_events as (

  select
    event_id,
    cmp_id,
    event_type,
    derived_tstamp as first_consent_event_tstamp

  from event_orders

  where row_num = 2

)

, cmp_events as (

  select distinct
    event_id,
    domain_userid,
    page_view_id,
    domain_sessionid,
    cmp_load_time,
    derived_tstamp as cmp_tstamp

  from events

  where event_name = 'cmp_visible'

)

select
  e.event_id,
  e.domain_userid,
  e.page_view_id,
  e.domain_sessionid,
  e.cmp_load_time,
  e.cmp_tstamp,
  f.first_consent_event_tstamp,
  f.event_type as first_consent_event_type,
  {{ datediff('e.cmp_tstamp', 'f.first_consent_event_tstamp', 'second') }} as cmp_interaction_time

from cmp_events e

left join first_consent_events f
on e.event_id = f.cmp_id

Depends On

Models
Macros

model.snowplow_web.snowplow_web_consent_log

Snowplow Web Consent Events This Run

models/optional_modules/consent/scratch/<adaptor>/snowplow_web_consent_events_this_run.sql

Description

This model does not currently have a description.

Type: Table

File Paths

bigquery
databricks
default
snowflake

models/optional_modules/consent/scratch/bigquery/snowplow_web_consent_events_this_run.sql

models/optional_modules/consent/scratch/databricks/snowplow_web_consent_events_this_run.sql

models/optional_modules/consent/scratch/default/snowplow_web_consent_events_this_run.sql

models/optional_modules/consent/scratch/snowflake/snowplow_web_consent_events_this_run.sql

Details

Columns

Column Name	Description	Type
event_id		text
domain_userid		text
user_id		text
geo_country		text
page_view_id		text
domain_sessionid		text
derived_tstamp		timestamp_ntz
load_tstamp		timestamp_ntz
event_name		text
event_type		text
basis_for_processing		text
consent_url		text
consent_version		text
consent_scopes		text
domains_applied		text
gdpr_applies		boolean
cmp_load_time		float

Code

bigquery
databricks
default
snowflake

Source

{{
  config(
    tags=["this_run"]
  )
}}

with prep as (

  select
    e.event_id,
    e.domain_userid,
    e.user_id,
    e.geo_country,
    e.page_view_id,
    e.domain_sessionid,
    e.derived_tstamp,
    e.load_tstamp,
    e.event_name,
    {{ snowplow_utils.get_optional_fields(
        enabled= true,
        fields=consent_fields(),
        col_prefix='unstruct_event_com_snowplowanalytics_snowplow_consent_preferences_1',
        relation=ref('snowplow_web_base_events_this_run'),
        relation_alias='e') }},
    {{ snowplow_utils.get_optional_fields(
        enabled= true,
        fields=[{'field': 'elapsed_time', 'dtype': 'string'}],
        col_prefix='unstruct_event_com_snowplowanalytics_snowplow_cmp_visible_1',
        relation=ref('snowplow_web_base_events_this_run'),
        relation_alias='e') }}

    from {{ ref("snowplow_web_base_events_this_run") }} as e

    where e.event_name in ('cmp_visible', 'consent_preferences')

    and {{ snowplow_utils.is_run_with_new_events('snowplow_web') }} --returns false if run doesn't contain new events.

)

select
  p.event_id,
  p.domain_userid,
  p.user_id,
  p.geo_country,
  p.page_view_id,
  p.domain_sessionid,
  p.derived_tstamp,
  p.load_tstamp,
  p.event_name,
  p.event_type,
  p.basis_for_processing,
  p.consent_url,
  p.consent_version,
  {{ snowplow_utils.get_array_to_string('consent_scopes', 'p', ', ') }} as consent_scopes,
  {{ snowplow_utils.get_array_to_string('domains_applied', 'p', ', ') }} as domains_applied,
  coalesce(safe_cast(p.gdpr_applies as boolean), false) gdpr_applies,
  cast(p.elapsed_time as {{ dbt.type_float() }}) as cmp_load_time

  from prep p

Source

{{
  config(
    tags=["this_run"]
  )
}}

with prep as (

  select
    e.event_id,
    e.domain_userid,
    e.user_id,
    e.geo_country,
    e.page_view_id,
    e.domain_sessionid,
    e.derived_tstamp,
    e.load_tstamp,
    e.event_name,
    e.unstruct_event_com_snowplowanalytics_snowplow_consent_preferences_1.event_type::STRING as event_type,
    e.unstruct_event_com_snowplowanalytics_snowplow_consent_preferences_1.basis_for_processing::STRING as basis_for_processing,
    e.unstruct_event_com_snowplowanalytics_snowplow_consent_preferences_1.consent_url::STRING as consent_url,
    e.unstruct_event_com_snowplowanalytics_snowplow_consent_preferences_1.consent_version::STRING as consent_version,
    e.unstruct_event_com_snowplowanalytics_snowplow_consent_preferences_1.consent_scopes::ARRAY<STRING> as consent_scopes,
    e.unstruct_event_com_snowplowanalytics_snowplow_consent_preferences_1.domains_applied::ARRAY<STRING> as domains_applied,
    e.unstruct_event_com_snowplowanalytics_snowplow_consent_preferences_1.gdpr_applies::boolean as gdpr_applies,
    e.unstruct_event_com_snowplowanalytics_snowplow_cmp_visible_1.elapsed_time::float as cmp_load_time

  from {{ ref("snowplow_web_base_events_this_run") }} as e

  where event_name in ('cmp_visible', 'consent_preferences')

  and {{ snowplow_utils.is_run_with_new_events('snowplow_web') }} --returns false if run doesn't contain new events.

)

select
  p.event_id,
  p.domain_userid,
  p.user_id,
  p.geo_country,
  p.page_view_id,
  p.domain_sessionid,
  p.derived_tstamp,
  p.load_tstamp,
  p.event_name,
  p.event_type,
  p.basis_for_processing,
  p.consent_url,
  p.consent_version,
  {{ snowplow_utils.get_array_to_string('consent_scopes', 'p', ', ') }} as consent_scopes,
  {{ snowplow_utils.get_array_to_string('domains_applied', 'p', ', ') }} as domains_applied,
  coalesce(p.gdpr_applies, false) as gdpr_applies,
  p.cmp_load_time

from prep p

Source

{{
  config(
    tags=["this_run"]
  )
}}

{%- set lower_limit, upper_limit = snowplow_utils.return_limits_from_model(ref('snowplow_web_base_sessions_this_run'),
                                                                          'start_tstamp',
                                                                          'end_tstamp') %}

with consent_pref as (

  select
    root_id,
    root_tstamp,
    event_type,
    basis_for_processing,
    consent_url,
    consent_version,
    consent_scopes,
    domains_applied,
    gdpr_applies,
    row_number() over (partition by root_id order by root_tstamp) dedupe_index

  from {{ var('snowplow__consent_preferences') }}

  where root_tstamp >= {{ lower_limit }}
  and root_tstamp <= {{ upper_limit }}

)

, cmp_visible  as (

  select
    root_id,
    root_tstamp,
    elapsed_time,
    row_number() over (partition by root_id order by root_tstamp) dedupe_index

  from {{ var('snowplow__consent_cmp_visible') }}

  where root_tstamp >= {{ lower_limit }}
  and root_tstamp <= {{ upper_limit }}

)

  select
    e.event_id,
    e.domain_userid,
    e.user_id,
    e.geo_country,
    e.page_view_id,
    e.domain_sessionid,
    e.derived_tstamp,
    e.load_tstamp,
    e.event_name,
    p.event_type,
    p.basis_for_processing,
    p.consent_url,
    p.consent_version,
    replace(translate(p.consent_scopes, '"[]', ''), ',', ', ') as consent_scopes,
    replace(translate(p.domains_applied, '"[]', ''), ',', ', ') as domains_applied,
    coalesce(p.gdpr_applies, false) as gdpr_applies,
    v.elapsed_time as cmp_load_time

  from {{ ref("snowplow_web_base_events_this_run") }} as e

  left join consent_pref p
    on e.event_id = p.root_id
    and e.collector_tstamp = p.root_tstamp
    and p.dedupe_index = 1

  left join cmp_visible v
    on e.event_id = v.root_id
    and e.collector_tstamp = v.root_tstamp
    and v.dedupe_index = 1

  where event_name in ('cmp_visible', 'consent_preferences')

  and {{ snowplow_utils.is_run_with_new_events('snowplow_web') }} --returns false if run doesn't contain new events.

Source

{{
  config(
    tags=["this_run"],
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
  )
}}

with prep as (

  select
    e.event_id,
    e.domain_userid,
    e.user_id,
    e.geo_country,
    e.page_view_id,
    e.domain_sessionid,
    e.derived_tstamp,
    e.load_tstamp,
    e.event_name,
    e.unstruct_event_com_snowplowanalytics_snowplow_consent_preferences_1:eventType::varchar as event_type,
    e.unstruct_event_com_snowplowanalytics_snowplow_consent_preferences_1:basisForProcessing::varchar as basis_for_processing,
    e.unstruct_event_com_snowplowanalytics_snowplow_consent_preferences_1:consentUrl::varchar as consent_url,
    e.unstruct_event_com_snowplowanalytics_snowplow_consent_preferences_1:consentVersion::varchar as consent_version,
    e.unstruct_event_com_snowplowanalytics_snowplow_consent_preferences_1:consentScopes::array as consent_scopes,
    e.unstruct_event_com_snowplowanalytics_snowplow_consent_preferences_1:domainsApplied::array as domains_applied,
    e.unstruct_event_com_snowplowanalytics_snowplow_consent_preferences_1:gdprApplies::boolean as gdpr_applies,
    e.unstruct_event_com_snowplowanalytics_snowplow_cmp_visible_1:elapsedTime::float as cmp_load_time

  from {{ ref("snowplow_web_base_events_this_run") }} as e

  where event_name in ('cmp_visible', 'consent_preferences')

  and {{ snowplow_utils.is_run_with_new_events('snowplow_web') }} --returns false if run doesn't contain new events.

)

select
  p.event_id,
  p.domain_userid,
  p.user_id,
  p.geo_country,
  p.page_view_id,
  p.domain_sessionid,
  p.derived_tstamp,
  p.load_tstamp,
  p.event_name,
  p.event_type,
  p.basis_for_processing,
  p.consent_url,
  p.consent_version,
  {{ snowplow_utils.get_array_to_string('consent_scopes', 'p', ', ') }} as consent_scopes,
  {{ snowplow_utils.get_array_to_string('domains_applied', 'p', ', ') }} as domains_applied,
  coalesce(p.gdpr_applies, false) as gdpr_applies,
  p.cmp_load_time

from prep p

Depends On

Models
Macros

Referenced By

Models

model.snowplow_web.snowplow_web_consent_log

Snowplow Web Consent Log

models/optional_modules/consent/snowplow_web_consent_log.sql

Description

Incremental table showing the audit trail of consent and Consent Management Platform (cmp) events

Type: Table

Details

Columns

Column Name	Description	Type
event_id	A UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
domain_userid	User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’	text
user_id	Unique ID set by business e.g. ‘jon.doe@email.com’	text
geo_country	ISO 3166-1 code for the country the visitor is located in e.g. ‘GB’, ‘US’	text
page_view_id	A UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
domain_sessionid	A visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
derived_tstamp	Timestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’	timestamp_ntz
load_tstamp	The timestamp of the event landing the data warehouse.	timestamp_ntz
event_name	Event name e.g. ‘link_click’	text
event_type	The action for the consent preferences of a user E.g allow_all	text
basis_for_processing	GDPR lawful basis for data collection & processing	text
consent_url	URI of the privacy policy related document	text
consent_version	Version of the privacy policy related document	text
consent_scopes	The scopes allowed after the user finalized his selection of consent preferences Eg ['analytics', 'functional', 'advertisement']	text
domains_applied	The domains for which this consent allows these preferences to persist to	text
gdpr_applies	A boolean which determines if GDPR applies based on the user's geo-location	boolean
cmp_load_time	The time taken for the consent box to be shown to the screen	float

Code

default

Source

{{
  config(
    materialized= 'incremental',
    unique_key='event_id',
    upsert_date_key='derived_tstamp',
    sort='derived_tstamp',
    dist='event_id',
    tags=["derived"],
    partition_by = snowplow_utils.get_value_by_target_type(bigquery_val = {
      "field": "derived_tstamp",
      "data_type": "timestamp"
    }, databricks_val = 'derived_tstamp_date'),
    cluster_by=snowplow_web.web_cluster_by_fields_consent(),
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt')),
    tblproperties={
      'delta.autoOptimize.optimizeWrite' : 'true',
      'delta.autoOptimize.autoCompact' : 'true'
    },
    snowplow_optimize= true
  )
}}

select
  *
  {% if target.type in ['databricks', 'spark'] -%}
  , DATE(derived_tstamp) as derived_tstamp_date
  {%- endif %}

from {{ ref('snowplow_web_consent_events_this_run') }}

where {{ snowplow_utils.is_run_with_new_events('snowplow_web') }} --returns false if run doesn't contain new events.

Depends On

Models
Macros

Referenced By

Models

Snowplow Web Consent Scope Status

models/optional_modules/consent/snowplow_web_consent_scope_status.sql

Description

Aggregate of current number of users consented to each consent scope

Type: Table

Details

Columns

Column Name	Description	Type
scope	Consent scope	text
total_consent	The number of consent events corresponding to a scope	number

Code

default

Source

{{
  config(
    materialized='table',
  )
}}

with arrays as (

  select
    u.domain_userid,
    {{ snowplow_utils.get_split_to_array('last_consent_scopes', 'u', ', ') }} as scope_array

  from {{ ref('snowplow_web_consent_users') }} u

  where is_latest_version

  )

  , unnesting as (

    {{ snowplow_utils.unnest('domain_userid', 'scope_array', 'consent_scope', 'arrays') }}

  )

select
  replace(replace(replace(cast(consent_scope as {{type_string() }}), '"', ''), '[', ''), ']', '') as scope,
  count(*) as total_consent

from unnesting

group by 1

Depends On

Models
Macros

model.snowplow_web.snowplow_web_consent_users

Snowplow Web Consent Totals

models/optional_modules/consent/snowplow_web_consent_totals.sql

Description

Summary of the latest consent status as per consent version

Type: Table

Details

Columns

Column Name	Description	Type
consent_version	Version of the privacy policy related document	text
version_start_tstamp	The first allow_all consent event belonging to a consent version	timestamp_ntz
consent_scopes	The scopes allowed after the user finalized his selection of consent preferences Eg ['analytics', 'functional', 'advertisement']	text
consent_url	URI of the privacy policy related document	text
domains_applied	The domains for which this consent allows these preferences to persist to	text
is_latest_version	A boolean to filter whether the last consent or cmp visible event is sent after the latest privacy policy version goes live	boolean
last_allow_all_event	The timestamp of the last allow_all consent event generated by the latest consent version	timestamp_ntz
total_visitors	The number of visitors who have visited since the last consent version is live	number
allow_all	Total number of users whose last consent event sent from the latest consent version has type allow_all	number
allow_selected	Total number of users whose last consent event sent from the latest consent version has type allow_selected	number
allow	Total number of users whose last consent event sent from the latest consent version has type allow	number
pending	Total number of users whose last consent event sent from the latest consent version has type pending	number
denied	Total number of users whose last consent event sent from the latest consent version has type denied	number
expired	Total number of users whose last consent event sent from the latest consent version has type expired	number
withdrawn	Total number of users whose last consent event sent from the latest consent version has type withdrawn	number
implicit_consent		number
expires_in_six_months	The total number of users whose consent expires in six months (only the offical version is taken into account)	number

Code

default

Source

{{
  config(
    materialized='table',
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
  )
}}

with totals as (

  select
    last_consent_version,
    count(distinct domain_userid) as total_visitors,
    count(case when last_consent_event_type ='allow_all' then 1 end) as allow_all,
    count(case when last_consent_event_type ='allow_selected' then 1 end) as allow_selected,
    count(case when last_consent_event_type IN ('allow_all', 'allow_selected') then 1 end) as allow,
    count(case when last_consent_event_type = 'pending' then 1 end) as pending,
    count(case when last_consent_event_type = 'deny_all'  then 1 end) as denied,
    count(case when last_consent_event_type = 'expired'  then 1 end) as expired,
    count(case when last_consent_event_type = 'withdrawn'  then 1 end) as withdrawn,
    count(case when last_consent_event_type = 'implicit_consent'  then 1 end) as implicit_consent,
    count(case when {{ dateadd('year', '1', 'last_consent_event_tstamp') }} <= {{ dateadd('month', '6', 'current_date') }}
          and last_consent_event_type <> 'expired'
          and {{ dateadd('year', '1', 'last_consent_event_tstamp') }} > current_date then 1 end) as expires_in_six_months

  from {{ ref('snowplow_web_consent_users') }}

  where last_consent_event_type is not null

  group by 1

)

select
  v.*,
  t.total_visitors,
  t.allow_all,
  t.allow_selected,
  t.allow,
  t.pending,
  t.denied,
  t.expired,
  t.withdrawn,
  t.implicit_consent,
  t.expires_in_six_months

from {{ ref('snowplow_web_consent_versions') }} v

left join totals t
on t.last_consent_version = v.consent_version

order by v.version_start_tstamp desc

Depends On

Models
Macros

Snowplow Web Consent Users

models/optional_modules/consent/snowplow_web_consent_users.sql

Description

By user consent stats

Type: Table

Details

Columns

Column Name	Description	Type
domain_userid	User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’	text
user_id	Unique ID set by business e.g. ‘jon.doe@email.com’	text
geo_country	ISO 3166-1 code for the country the visitor is located in e.g. ‘GB’, ‘US’	text
cmp_events	The number of cmp_visible events the user has generated	number
consent_events	The number of cosent events the user has generated	number
last_cmp_event_tstamp	The timestamp of the last cmp_visible event	timestamp_ntz
last_consent_event_tstamp	The timestamp of the last consent event after the cmp_visible event happened	timestamp_ntz
last_consent_event_type	The type of the last consent event after the cmp_visible event happened	text
last_consent_scopes	The list of consent scopes in connection with the last consent event	text
last_consent_version	The privacy policy version in connection with the last consent event	text
last_consent_url	The privacy policy url in connection with the last consent event	text
last_domains_applied	The domains for which the last consent event applies	text
last_processed_event	The timestamp of the last processed event needed for the incremental logic	timestamp_ntz
is_latest_version	A boolean to filter whether the last consent or cmp visible event is sent after the latest privacy policy version goes live	boolean

Code

default

Source

{{
  config(
    materialized='incremental',
    unique_key='domain_userid',
    sort = 'last_consent_event_tstamp',
    dist = 'domain_userid',
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
  )
}}


{% if is_incremental() %}
{%- set lower_limit, upper_limit = snowplow_utils.return_limits_from_model(this,
                                                                          'last_processed_event',
                                                                          'last_processed_event') %}
{% endif %}

with base as (

  select
    domain_userid,
    user_id,
    geo_country,
    max(load_tstamp) as last_processed_event,
    count(case when event_name = 'cmp_visible' then 1 end) as cmp_events,
    count(case when event_name = 'consent_preferences' then 1 end) as consent_events,
    max(case when event_name = 'cmp_visible' then derived_tstamp end) as last_cmp_event_tstamp,
    row_number() over(partition by domain_userid order by max(load_tstamp) desc) as latest_event_by_user_rank

  from {{ ref('snowplow_web_consent_log') }}

  {% if is_incremental() %} -- and it has not been processed yet
  where load_tstamp > {{ upper_limit }}
  {% endif %}

  group by 1,2,3

)

, latest_consents as (

  select
    domain_userid,
    derived_tstamp as last_consent_event_tstamp,
    event_type as last_consent_event_type,
    consent_scopes as last_consent_scopes,
    consent_version as last_consent_version,
    consent_url as last_consent_url,
    domains_applied as last_domains_applied,
    row_number() over(partition by domain_userid order by load_tstamp desc) as latest_consent_event_by_user_rank

  from {{ ref('snowplow_web_consent_log') }}

  where event_name = 'consent_preferences'

  {% if is_incremental() %} -- and it has not been processed yet
  and load_tstamp > {{ upper_limit }}
  {% endif %}

)

{% if is_incremental() %}

select
  b.domain_userid,
  b.user_id,
  b.geo_country,
  coalesce(b.cmp_events, 0) + coalesce(t.cmp_events, 0) as cmp_events,
  coalesce(b.consent_events, 0) + coalesce(t.consent_events, 0) as consent_events,
  b.last_cmp_event_tstamp,
  l.last_consent_event_tstamp,
  l.last_consent_event_type,
  l.last_consent_scopes,
  l.last_consent_version,
  l.last_consent_url,
  l.last_domains_applied,
  b.last_processed_event,
  case when v.is_latest_version then True else False end as is_latest_version

from base b

left join latest_consents l
on b.domain_userid = l.domain_userid

left join {{ ref('snowplow_web_consent_versions')}} v
on v.consent_version = l.last_consent_version

left join {{ this }} t
on t.domain_userid = b.domain_userid

where (l.latest_consent_event_by_user_rank = 1 or l.domain_userid is null)
and b.latest_event_by_user_rank = 1

{% else %}

select
  b.domain_userid,
  b.user_id,
  b.geo_country,
  b.cmp_events,
  b.consent_events,
  b.last_cmp_event_tstamp,
  l.last_consent_event_tstamp,
  l.last_consent_event_type,
  l.last_consent_scopes,
  l.last_consent_version,
  l.last_consent_url,
  l.last_domains_applied,
  b.last_processed_event,
  case when v.is_latest_version then True else False end as is_latest_version

from base b

left join latest_consents l
on b.domain_userid = l.domain_userid

left join {{ ref('snowplow_web_consent_versions') }} v
on v.consent_version = l.last_consent_version

where (l.latest_consent_event_by_user_rank = 1 or l.domain_userid is null)
and b.latest_event_by_user_rank = 1

{% endif %}

Depends On

Models
Macros

Referenced By

Models

Snowplow Web Consent Versions

models/optional_modules/consent/snowplow_web_consent_versions.sql

Description

Used to keep track of each consent version and its validity

Type: Table

Details

Columns

Column Name	Description	Type
consent_version	Version of the privacy policy related document	text
version_start_tstamp	The time_stamp of the first allow_all event related to a consent version	timestamp_ntz
consent_scopes	The scopes allowed after the user finalized his selection of consent preferences Eg ['analytics', 'functional', 'advertisement']	text
consent_url	URI of the privacy policy related document	text
domains_applied	The domains for which this consent allows these preferences to persist to	text
is_latest_version	A boolean to filter whether the last consent or cmp visible event is sent after the latest privacy policy version goes live	boolean
last_allow_all_event	The timestamp of the last allow_all event used for the incremental update	timestamp_ntz

Code

default

Source

{{
  config(
    materialized='incremental',
    unique_key='consent_version',
    sort = 'version_start_tstamp',
    dist = 'consent_version',
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
  )
}}


{% if is_incremental() %}
{%- set lower_limit, upper_limit = snowplow_utils.return_limits_from_model(this,
                                                                          'last_allow_all_event',
                                                                          'last_allow_all_event') %}
{% endif %}

with consent_versions as (

  select
    consent_version,
    consent_scopes,
    consent_url,
    domains_applied,
    min(derived_tstamp) as version_start_tstamp,
    max(load_tstamp) as last_allow_all_event

  from {{ ref('snowplow_web_consent_log') }}

  where event_name <> 'cmp_visible' and event_type = 'allow_all'

  {% if is_incremental() %} -- and it has not been processed yet
  and load_tstamp > {{ upper_limit }}
  {% endif %}

  group by 1,2,3,4
)

, latest_version as (

  select
    consent_version,
    version_start_tstamp

  from consent_versions

  order by 2 desc limit 1
)

{% if is_incremental() %}

select
  v.consent_version,
  least(v.version_start_tstamp, t.version_start_tstamp) as version_start_tstamp,
  v.consent_scopes,
  v.consent_url,
  v.domains_applied,
  case when l.consent_version is not null then True else False end is_latest_version,
  v.last_allow_all_event

from consent_versions v

left join latest_version l

on v.consent_version = l.consent_version

left join {{ this }} t
on t.consent_version = v.consent_version

{% else %}

select
  v.consent_version,
  v.version_start_tstamp,
  v.consent_scopes,
  v.consent_url,
  v.domains_applied,
  case when l.consent_version is not null then True else False end is_latest_version,
  v.last_allow_all_event

from consent_versions v

left join latest_version l

on v.consent_version = l.consent_version

{% endif %}

Depends On

Models
Macros

model.snowplow_web.snowplow_web_consent_log

Referenced By

Models

Snowplow Web Incremental Manifest

models/base/manifest/snowplow_web_incremental_manifest.sql

Description

This incremental table is a manifest of the timestamp of the latest event consumed per model within the snowplow-web package as well as any models leveraging the incremental framework provided by the package. The latest event's timestamp is based off collector_tstamp. This table is used to determine what events should be processed in the next run of the model.

Type: Table

Details

Columns

Column Name	Description	Type
model	The name of the model.	text
last_success	The latest event consumed by the model, based on `collector_tstamp`	timestamp_ntz

Code

default

Source

{{
  config(
    materialized='incremental',
    full_refresh=snowplow_web.allow_refresh(),
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt')),
    tblproperties={
      'delta.autoOptimize.optimizeWrite' : 'true',
      'delta.autoOptimize.autoCompact' : 'true'
    }
  )
}}

-- Boilerplate to generate table.
-- Table updated as part of end-run hook

with prep as (
  select
    cast(null as {{ snowplow_utils.type_max_string() }}) model,
    cast('1970-01-01' as {{ type_timestamp() }}) as last_success
)

select *

from prep
where false

Depends On

Macros

Referenced By

Models

Snowplow Web Page View Events

models/page_views/scratch/<adaptor>/snowplow_web_page_view_events.sql

Description

This is a staging table containing all the page view events for a given run of the Web model. It is the first step in the page views module and therefore does not contain metrics such as engaged time and scroll depth which are calculated in subsequent models. It is also where the de-duping of page_view_id's occurs

File Paths

default

models/page_views/scratch/default/snowplow_web_page_view_events.sql

Details

Columns

Column Name	Description
page_view_id	A UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’

Code

default

Source

{{
  config(
    sort='start_tstamp',
    dist='page_view_id'
  )
}}

with page_view_events as (
  select
    ev.page_view_id,
    ev.event_id,

    ev.app_id,

    -- user fields
    ev.user_id,
    ev.domain_userid,
    ev.network_userid,

    -- session fields
    ev.domain_sessionid,
    ev.domain_sessionidx,

    -- timestamp fields
    ev.dvce_created_tstamp,
    ev.collector_tstamp,
    ev.derived_tstamp,
    ev.derived_tstamp as start_tstamp,

    ev.doc_width,
    ev.doc_height,

    ev.page_title,
    ev.page_url,
    ev.page_urlscheme,
    ev.page_urlhost,
    ev.page_urlpath,
    ev.page_urlquery,
    ev.page_urlfragment,

    ev.mkt_medium,
    ev.mkt_source,
    ev.mkt_term,
    ev.mkt_content,
    ev.mkt_campaign,
    ev.mkt_clickid,
    ev.mkt_network,

    ev.page_referrer,
    ev.refr_urlscheme ,
    ev.refr_urlhost,
    ev.refr_urlpath,
    ev.refr_urlquery,
    ev.refr_urlfragment,
    ev.refr_medium,
    ev.refr_source,
    ev.refr_term,

    ev.geo_country,
    ev.geo_region,
    ev.geo_region_name,
    ev.geo_city,
    ev.geo_zipcode,
    ev.geo_latitude,
    ev.geo_longitude,
    ev.geo_timezone ,

    ev.user_ipaddress,

    ev.useragent,

    ev.br_lang,
    ev.br_viewwidth,
    ev.br_viewheight,
    ev.br_colordepth,
    ev.br_renderengine,
    ev.os_timezone,

    row_number() over (partition by ev.page_view_id order by ev.derived_tstamp, ev.dvce_created_tstamp) as page_view_id_dedupe_index

  from {{ ref('snowplow_web_base_events_this_run') }} as ev

  where ev.event_name = 'page_view'
  and ev.page_view_id is not null

  {% if var("snowplow__ua_bot_filter", true) %}
    {{ filter_bots('ev') }}
  {% endif %}
)

select
  pv.page_view_id,
  pv.event_id,

  pv.app_id,

  -- user fields
  pv.user_id,
  pv.domain_userid,
  pv.network_userid,

  -- session fields
  pv.domain_sessionid,
  pv.domain_sessionidx,

  -- timestamp fields
  pv.dvce_created_tstamp,
  pv.collector_tstamp,
  pv.derived_tstamp,
  pv.start_tstamp,

  pv.doc_width,
  pv.doc_height,

  pv.page_title,
  pv.page_url,
  pv.page_urlscheme,
  pv.page_urlhost,
  pv.page_urlpath,
  pv.page_urlquery,
  pv.page_urlfragment,

  pv.mkt_medium,
  pv.mkt_source,
  pv.mkt_term,
  pv.mkt_content,
  pv.mkt_campaign,
  pv.mkt_clickid,
  pv.mkt_network,

  pv.page_referrer,
  pv.refr_urlscheme ,
  pv.refr_urlhost,
  pv.refr_urlpath,
  pv.refr_urlquery,
  pv.refr_urlfragment,
  pv.refr_medium,
  pv.refr_source,
  pv.refr_term,

  pv.geo_country,
  pv.geo_region,
  pv.geo_region_name,
  pv.geo_city,
  pv.geo_zipcode,
  pv.geo_latitude,
  pv.geo_longitude,
  pv.geo_timezone ,

  pv.user_ipaddress,

  pv.useragent,

  pv.br_lang,
  pv.br_viewwidth,
  pv.br_viewheight,
  pv.br_colordepth,
  pv.br_renderengine,
  pv.os_timezone,

  row_number() over (partition by pv.domain_sessionid order by pv.derived_tstamp, pv.dvce_created_tstamp) as page_view_in_session_index --Moved to post dedupe, unlike V1 web model.

from page_view_events as pv

where page_view_id_dedupe_index = 1

Depends On

Macros

macro.snowplow_web.filter_bots

Snowplow Web Page Views

models/page_views/snowplow_web_page_views.sql

Description

This derived incremental table contains all historic page views and should be the end point for any analysis or BI tools.

Type: Table

Details

Columns

Column Name	Description	Type
page_view_id	A UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
event_id	A UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
app_id	Application ID e.g. ‘angry-birds’ is used to distinguish different applications that are being tracked by the same Snowplow stack, e.g. production versus dev.	text
user_id	Unique ID set by business e.g. ‘jon.doe@email.com’	text
domain_userid	User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’	text
network_userid	User ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’	text
domain_sessionid	A visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
domain_sessionidx	A visit / session index e.g. 3	number
page_view_in_session_index	A page view index within a single session	number
page_views_in_session	Distinct count of `page_view_id` within a session	number
dvce_created_tstamp	Timestamp event was recorded on the client device e.g. ‘2013-11-26 00:03:57.885’	timestamp_ntz
collector_tstamp	Time stamp for the event recorded by the collector e.g. ‘2013-11-26 00:02:05’	timestamp_ntz
derived_tstamp	Timestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’	timestamp_ntz
start_tstamp	Timestamp for the start of the page view, based on `derived_tstamp`	timestamp_ntz
end_tstamp	Timestamp for the end of the page view, based on `derived_tstamp`	timestamp_ntz
model_tstamp	The current timestamp when the model processed this row.	timestamp_ntz
engaged_time_in_s	Time spent by the user on the page calculated using page pings.	number
absolute_time_in_s	The time in seconds between the `start_tstamp` and `end_tstamp`	number
horizontal_pixels_scrolled	Distance the user scrolled horizontally in pixels	number
vertical_pixels_scrolled	Distance the user scrolled vertically in pixels	number
horizontal_percentage_scrolled	Percentage of page scrolled horizontally	float
vertical_percentage_scrolled	Percentage of page scrolled vertically	float
doc_width	The page’s width in pixels e.g. 1024	number
doc_height	The page’s height in pixels e.g. 3000	number
page_title	Web page title e.g. ‘Snowplow Docs – Understanding the structure of Snowplow data’	text
page_url	The page URL e.g. ‘http://www.example.com’	text
page_urlscheme	Scheme aka protocol e.g. ‘https’	text
page_urlhost	Host aka domain e.g. ‘“www.snowplow.io’	text
page_urlpath	Path to page e.g. ‘/product/index.html’	text
page_urlquery	Querystring e.g. ‘id=GTM-DLRG’	text
page_urlfragment	Fragment aka anchor e.g. ‘4-conclusion’	text
mkt_medium	Type of traffic source e.g. ‘cpc’, ‘affiliate’, ‘organic’, ‘social’	text
mkt_source	The company / website where the traffic came from e.g. ‘Google’, ‘Facebook’	text
mkt_term	Any keywords associated with the referrer e.g. ‘new age tarot decks’	text
mkt_content	The content of the ad. (Or an ID so that it can be looked up.) e.g. 13894723	text
mkt_campaign	The campaign ID e.g. ‘diageo-123’	text
mkt_clickid	The click ID e.g. ‘ac3d8e459’	text
mkt_network	The ad network to which the click ID belongs e.g. ‘DoubleClick’	text
page_referrer	URL of the referrer e.g. ‘http://www.referrer.com’	text
refr_urlscheme	Referer scheme e.g. ‘http’	text
refr_urlhost	Referer host e.g. ‘www.bing.com’	text
refr_urlpath	Referer page path e.g. ‘/images/search’	text
refr_urlquery	Referer URL querystring e.g. ‘q=psychic+oracle+cards’	text
refr_urlfragment	Referer URL fragment	text
refr_medium	Type of referer e.g. ‘search’, ‘internal’	text
refr_source	Name of referer if recognised e.g. ‘Bing images’	text
refr_term	Keywords if source is a search engine e.g. ‘psychic oracle cards’	text
geo_country	ISO 3166-1 code for the country the visitor is located in e.g. ‘GB’, ‘US’	text
geo_region	ISO-3166-2 code for country region the visitor is in e.g. ‘I9’, ‘TX’	text
geo_region_name	Visitor region name e.g. ‘Florida’	text
geo_city	City the visitor is in e.g. ‘New York’, ‘London’	text
geo_zipcode	Postcode the visitor is in e.g. ‘94109’	text
geo_latitude	Visitor location latitude e.g. 37.443604	float
geo_longitude	Visitor location longitude e.g. -122.4124	float
geo_timezone	Visitor timezone name e.g. ‘Europe/London’	text
user_ipaddress	User IP address e.g. ‘92.231.54.234’	text
useragent	Raw useragent	text
br_lang	Language the browser is set to e.g. ‘en-GB’	text
br_viewwidth	Viewport width e.g. 1000	number
br_viewheight	Viewport height e.g. 1000	number
br_colordepth	Bit depth of the browser color palette e.g. 24	text
br_renderengine	Browser rendering engine e.g. ‘GECKO’	text
os_timezone	Client operating system timezone e.g. ‘Europe/London’	text
category	Category based on activity if the IP/UA is a spider or robot, BROWSER otherwise	text
primary_impact	Whether the spider or robot would affect page impression measurement, ad impression measurement, both or none	text
reason	Type of failed check if the IP/UA is a spider or robot, PASSED_ALL otherwise	text
spider_or_robot	True if the IP address or user agent checked against the list is a spider or robot, false otherwise	boolean
useragent_family	Useragent family (browser) name	text
useragent_major	Useragent major version	text
useragent_minor	Useragent minor version	text
useragent_patch	Useragent patch version	text
useragent_version	Full version of the useragent	text
os_family	Operating system family e.g. ‘Linux’	text
os_major	Operation system major version	text
os_minor	Operation system minor version	text
os_patch	Operation system patch version	text
os_patch_minor	Operation system patch minor version	text
os_version	Operation system full version	text
device_family	Device type	text
device_class	Class of device e.g. phone	text
agent_class	Class of agent e.g. browser	text
agent_name	Name of agent e.g. Chrome	text
agent_name_version	Name and version of agent e.g. Chrome 53.0.2785.124	text
agent_name_version_major	Name and major version of agent e.g. Chrome 53	text
agent_version	Version of agent e.g. 53.0.2785.124	text
agent_version_major	Major version of agent e.g. 53	text
device_brand	Brand of device e.g. Google	text
device_name	Name of device e.g. Google Nexus 6	text
device_version	Version of device e.g. 6.0	text
layout_engine_class	Class of layout engine e.g. Browser	text
layout_engine_name	Name of layout engine e.g. Blink	text
layout_engine_name_version	Name and version of layout engine e.g. Blink 53.0	text
layout_engine_name_version_major	Name and major version of layout engine e.g. Blink 53	text
layout_engine_version	Version of layout engine e.g. 53.0	text
layout_engine_version_major	Major version of layout engine e.g. 53	text
operating_system_class	Class of the OS e.g. Mobile	text
operating_system_name	Name of the OS e.g. Android	text
operating_system_name_version	Name and version of the OS e.g. Android 7.0	text
operating_system_version	Version of the OS e.g. 7.0	text

Code

default

Source

{{
  config(
    materialized='incremental',
    unique_key='page_view_id',
    upsert_date_key='start_tstamp',
    sort='start_tstamp',
    dist='page_view_id',
    partition_by = snowplow_utils.get_value_by_target_type(bigquery_val = {
      "field": "start_tstamp",
      "data_type": "timestamp"
    }, databricks_val='start_tstamp_date'),
    cluster_by=snowplow_web.web_cluster_by_fields_page_views(),
    tags=["derived"],
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt')),
    tblproperties={
      'delta.autoOptimize.optimizeWrite' : 'true',
      'delta.autoOptimize.autoCompact' : 'true'
    },
    snowplow_optimize = true
  )
}}


select *
  {% if target.type in ['databricks', 'spark'] -%}
  , DATE(start_tstamp) as start_tstamp_date
  {%- endif %}
from {{ ref('snowplow_web_page_views_this_run') }}
where {{ snowplow_utils.is_run_with_new_events('snowplow_web') }} --returns false if run doesn't contain new events.

Depends On

Models
Macros

Snowplow Web Page Views This Run

models/page_views/scratch/<adaptor>/snowplow_web_page_views_this_run.sql

Description

This staging table contains all the page views for the given run of the Web model. It possess all the same columns as snowplow_web_page_views. If building a custom module that requires page view events, this is the table you should reference.

Type: Table

File Paths

bigquery
databricks
default
snowflake

models/page_views/scratch/bigquery/snowplow_web_page_views_this_run.sql

models/page_views/scratch/databricks/snowplow_web_page_views_this_run.sql

models/page_views/scratch/default/snowplow_web_page_views_this_run.sql

models/page_views/scratch/snowflake/snowplow_web_page_views_this_run.sql

Details

Columns

Column Name	Description	Type
page_view_id	A UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
event_id	A UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
app_id	Application ID e.g. ‘angry-birds’ is used to distinguish different applications that are being tracked by the same Snowplow stack, e.g. production versus dev.	text
user_id	Unique ID set by business e.g. ‘jon.doe@email.com’	text
domain_userid	User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’	text
network_userid	User ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’	text
domain_sessionid	A visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
domain_sessionidx	A visit / session index e.g. 3	number
page_view_in_session_index	A page view index within a single session	number
page_views_in_session	Distinct count of `page_view_id` within a session	number
dvce_created_tstamp	Timestamp event was recorded on the client device e.g. ‘2013-11-26 00:03:57.885’	timestamp_ntz
collector_tstamp	Time stamp for the event recorded by the collector e.g. ‘2013-11-26 00:02:05’	timestamp_ntz
derived_tstamp	Timestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’	timestamp_ntz
start_tstamp	Timestamp for the start of the page view, based on `derived_tstamp`	timestamp_ntz
end_tstamp	Timestamp for the end of the page view, based on `derived_tstamp`	timestamp_ntz
model_tstamp	The current timestamp when the model processed this row.	timestamp_ntz
engaged_time_in_s	Time spent by the user on the page calculated using page pings.	number
absolute_time_in_s	The time in seconds between the `start_tstamp` and `end_tstamp`	number
horizontal_pixels_scrolled	Distance the user scrolled horizontally in pixels	number
vertical_pixels_scrolled	Distance the user scrolled vertically in pixels	number
horizontal_percentage_scrolled	Percentage of page scrolled horizontally	float
vertical_percentage_scrolled	Percentage of page scrolled vertically	float
doc_width	The page’s width in pixels e.g. 1024	number
doc_height	The page’s height in pixels e.g. 3000	number
page_title	Web page title e.g. ‘Snowplow Docs – Understanding the structure of Snowplow data’	text
page_url	The page URL e.g. ‘http://www.example.com’	text
page_urlscheme	Scheme aka protocol e.g. ‘https’	text
page_urlhost	Host aka domain e.g. ‘“www.snowplow.io’	text
page_urlpath	Path to page e.g. ‘/product/index.html’	text
page_urlquery	Querystring e.g. ‘id=GTM-DLRG’	text
page_urlfragment	Fragment aka anchor e.g. ‘4-conclusion’	text
mkt_medium	Type of traffic source e.g. ‘cpc’, ‘affiliate’, ‘organic’, ‘social’	text
mkt_source	The company / website where the traffic came from e.g. ‘Google’, ‘Facebook’	text
mkt_term	Any keywords associated with the referrer e.g. ‘new age tarot decks’	text
mkt_content	The content of the ad. (Or an ID so that it can be looked up.) e.g. 13894723	text
mkt_campaign	The campaign ID e.g. ‘diageo-123’	text
mkt_clickid	The click ID e.g. ‘ac3d8e459’	text
mkt_network	The ad network to which the click ID belongs e.g. ‘DoubleClick’	text
page_referrer	URL of the referrer e.g. ‘http://www.referrer.com’	text
refr_urlscheme	Referer scheme e.g. ‘http’	text
refr_urlhost	Referer host e.g. ‘www.bing.com’	text
refr_urlpath	Referer page path e.g. ‘/images/search’	text
refr_urlquery	Referer URL querystring e.g. ‘q=psychic+oracle+cards’	text
refr_urlfragment	Referer URL fragment	text
refr_medium	Type of referer e.g. ‘search’, ‘internal’	text
refr_source	Name of referer if recognised e.g. ‘Bing images’	text
refr_term	Keywords if source is a search engine e.g. ‘psychic oracle cards’	text
geo_country	ISO 3166-1 code for the country the visitor is located in e.g. ‘GB’, ‘US’	text
geo_region	ISO-3166-2 code for country region the visitor is in e.g. ‘I9’, ‘TX’	text
geo_region_name	Visitor region name e.g. ‘Florida’	text
geo_city	City the visitor is in e.g. ‘New York’, ‘London’	text
geo_zipcode	Postcode the visitor is in e.g. ‘94109’	text
geo_latitude	Visitor location latitude e.g. 37.443604	float
geo_longitude	Visitor location longitude e.g. -122.4124	float
geo_timezone	Visitor timezone name e.g. ‘Europe/London’	text
user_ipaddress	User IP address e.g. ‘92.231.54.234’	text
useragent	Raw useragent	text
br_lang	Language the browser is set to e.g. ‘en-GB’	text
br_viewwidth	Viewport width e.g. 1000	number
br_viewheight	Viewport height e.g. 1000	number
br_colordepth	Bit depth of the browser color palette e.g. 24	text
br_renderengine	Browser rendering engine e.g. ‘GECKO’	text
os_timezone	Client operating system timezone e.g. ‘Europe/London’	text
category	Category based on activity if the IP/UA is a spider or robot, BROWSER otherwise	text
primary_impact	Whether the spider or robot would affect page impression measurement, ad impression measurement, both or none	text
reason	Type of failed check if the IP/UA is a spider or robot, PASSED_ALL otherwise	text
spider_or_robot	True if the IP address or user agent checked against the list is a spider or robot, false otherwise	boolean
useragent_family	Useragent family (browser) name	text
useragent_major	Useragent major version	text
useragent_minor	Useragent minor version	text
useragent_patch	Useragent patch version	text
useragent_version	Full version of the useragent	text
os_family	Operating system family e.g. ‘Linux’	text
os_major	Operation system major version	text
os_minor	Operation system minor version	text
os_patch	Operation system patch version	text
os_patch_minor	Operation system patch minor version	text
os_version	Operation system full version	text
device_family	Device type	text
device_class	Class of device e.g. phone	text
agent_class	Class of agent e.g. browser	text
agent_name	Name of agent e.g. Chrome	text
agent_name_version	Name and version of agent e.g. Chrome 53.0.2785.124	text
agent_name_version_major	Name and major version of agent e.g. Chrome 53	text
agent_version	Version of agent e.g. 53.0.2785.124	text
agent_version_major	Major version of agent e.g. 53	text
device_brand	Brand of device e.g. Google	text
device_name	Name of device e.g. Google Nexus 6	text
device_version	Version of device e.g. 6.0	text
layout_engine_class	Class of layout engine e.g. Browser	text
layout_engine_name	Name of layout engine e.g. Blink	text
layout_engine_name_version	Name and version of layout engine e.g. Blink 53.0	text
layout_engine_name_version_major	Name and major version of layout engine e.g. Blink 53	text
layout_engine_version	Version of layout engine e.g. 53.0	text
layout_engine_version_major	Major version of layout engine e.g. 53	text
operating_system_class	Class of the OS e.g. Mobile	text
operating_system_name	Name of the OS e.g. Android	text
operating_system_name_version	Name and version of the OS e.g. Android 7.0	text
operating_system_version	Version of the OS e.g. 7.0	text

Code

bigquery
databricks
default
snowflake

Source

{{
  config(
    tags=["this_run"]
  )
}}

with page_view_events as (
select
  ev.page_view_id,
  ev.event_id,

  ev.app_id,

  -- user fields
  ev.user_id,
  ev.domain_userid,
  ev.network_userid,

  -- session fields
  ev.domain_sessionid,
  ev.domain_sessionidx,

  -- timestamp fields
  ev.dvce_created_tstamp,
  ev.collector_tstamp,
  ev.derived_tstamp,
  ev.derived_tstamp as start_tstamp,

  ev.doc_width,
  ev.doc_height,

  ev.page_title,
  ev.page_url,
  ev.page_urlscheme,
  ev.page_urlhost,
  ev.page_urlpath,
  ev.page_urlquery,
  ev.page_urlfragment,

  ev.mkt_medium,
  ev.mkt_source,
  ev.mkt_term,
  ev.mkt_content,
  ev.mkt_campaign,
  ev.mkt_clickid,
  ev.mkt_network,

  ev.page_referrer,
  ev.refr_urlscheme,
  ev.refr_urlhost,
  ev.refr_urlpath,
  ev.refr_urlquery,
  ev.refr_urlfragment,
  ev.refr_medium,
  ev.refr_source,
  ev.refr_term,

  ev.geo_country,
  ev.geo_region,
  ev.geo_region_name,
  ev.geo_city,
  ev.geo_zipcode,
  ev.geo_latitude,
  ev.geo_longitude,
  ev.geo_timezone ,

  ev.user_ipaddress,

  ev.useragent,

  ev.br_lang,
  ev.br_viewwidth,
  ev.br_viewheight,
  ev.br_colordepth,
  ev.br_renderengine,
  ev.os_timezone,

  row_number() over (partition by ev.domain_sessionid order by ev.derived_tstamp, ev.dvce_created_tstamp) AS page_view_in_session_index,

  -- optional fields, only populated if enabled.

  -- iab enrichment fields: set iab variable to true to enable
  {{ snowplow_utils.get_optional_fields(
        enabled=var('snowplow__enable_iab', false),
        fields=iab_fields(),
        col_prefix='contexts_com_iab_snowplow_spiders_and_robots_1',
        relation=ref('snowplow_web_base_events_this_run'),
        relation_alias='ev') }},

  -- ua parser enrichment fields: set ua_parser variable to true to enable
  {{ snowplow_utils.get_optional_fields(
        enabled=var('snowplow__enable_ua', false),
        fields=ua_fields(),
        col_prefix='contexts_com_snowplowanalytics_snowplow_ua_parser_context_1',
        relation=ref('snowplow_web_base_events_this_run'),
        relation_alias='ev') }},

  -- yauaa enrichment fields: set yauaa variable to true to enable
  {{ snowplow_utils.get_optional_fields(
        enabled=var('snowplow__enable_yauaa', false),
        fields=yauaa_fields(),
        col_prefix='contexts_nl_basjes_yauaa_context_1',
        relation=ref('snowplow_web_base_events_this_run'),
        relation_alias='ev') }}

from (
  select
    array_agg(e order by e.derived_tstamp, e.dvce_created_tstamp limit 1)[offset(0)] as ev
    -- order by matters here since derived_tstamp determines parts of model logic

  from {{ ref('snowplow_web_base_events_this_run') }} as e
  where e.event_name = 'page_view'
  and e.page_view_id is not null

  group by e.page_view_id
)
where 1 = 1

{% if var("snowplow__ua_bot_filter", true) %}
 {{ filter_bots('ev') }}
{% endif %}
)


select
  ev.page_view_id,
  ev.event_id,

  ev.app_id,

  -- user fields
  ev.user_id,
  ev.domain_userid,
  ev.network_userid,

  -- session fields
  ev.domain_sessionid,
  ev.domain_sessionidx,

  ev.page_view_in_session_index,
  max(ev.page_view_in_session_index) over (partition by ev.domain_sessionid) as page_views_in_session,

  -- timestamp fields
  ev.dvce_created_tstamp,
  ev.collector_tstamp,
  ev.derived_tstamp,
  ev.start_tstamp,
  coalesce(t.end_tstamp, ev.derived_tstamp) as end_tstamp, -- only page views with pings will have a row in table t
  {{ snowplow_utils.current_timestamp_in_utc() }} as model_tstamp,

  coalesce(t.engaged_time_in_s, 0) as engaged_time_in_s, -- where there are no pings, engaged time is 0.
  timestamp_diff(coalesce(t.end_tstamp, ev.derived_tstamp), ev.derived_tstamp, second)  as absolute_time_in_s,

  sd.hmax as horizontal_pixels_scrolled,
  sd.vmax as vertical_pixels_scrolled,

  sd.relative_hmax as horizontal_percentage_scrolled,
  sd.relative_vmax as vertical_percentage_scrolled,

  ev.doc_width,
  ev.doc_height,

  ev.page_title,
  ev.page_url,
  ev.page_urlscheme,
  ev.page_urlhost,
  ev.page_urlpath,
  ev.page_urlquery,
  ev.page_urlfragment,

  ev.mkt_medium,
  ev.mkt_source,
  ev.mkt_term,
  ev.mkt_content,
  ev.mkt_campaign,
  ev.mkt_clickid,
  ev.mkt_network,

  ev.page_referrer,
  ev.refr_urlscheme,
  ev.refr_urlhost,
  ev.refr_urlpath,
  ev.refr_urlquery,
  ev.refr_urlfragment,
  ev.refr_medium,
  ev.refr_source,
  ev.refr_term,

  ev.geo_country,
  ev.geo_region,
  ev.geo_region_name,
  ev.geo_city,
  ev.geo_zipcode,
  ev.geo_latitude,
  ev.geo_longitude,
  ev.geo_timezone,

  ev.user_ipaddress,

  ev.useragent,

  ev.br_lang,
  ev.br_viewwidth,
  ev.br_viewheight,
  ev.br_colordepth,
  ev.br_renderengine,

  ev.os_timezone,

  ev.category,
  ev.primary_impact,
  ev.reason,
  ev.spider_or_robot,

  ev.useragent_family,
  ev.useragent_major,
  ev.useragent_minor,
  ev.useragent_patch,
  ev.useragent_version,
  ev.os_family,
  ev.os_major,
  ev.os_minor,
  ev.os_patch,
  ev.os_patch_minor,
  ev.os_version,
  ev.device_family,

  ev.device_class,
  ev.agent_class,
  ev.agent_name,
  ev.agent_name_version,
  ev.agent_name_version_major,
  ev.agent_version,
  ev.agent_version_major,
  ev.device_brand,
  ev.device_name,
  ev.device_version,
  ev.layout_engine_class,
  ev.layout_engine_name,
  ev.layout_engine_name_version,
  ev.layout_engine_name_version_major,
  ev.layout_engine_version,
  ev.layout_engine_version_major,
  ev.operating_system_class,
  ev.operating_system_name,
  ev.operating_system_name_version,
  ev.operating_system_version

from page_view_events ev

left join {{ ref('snowplow_web_pv_engaged_time') }} t
on ev.page_view_id = t.page_view_id {% if var('snowplow__limit_page_views_to_session', true) %} and ev.domain_sessionid = t.domain_sessionid {% endif %}

left join {{ ref('snowplow_web_pv_scroll_depth') }} sd
on ev.page_view_id = sd.page_view_id {% if var('snowplow__limit_page_views_to_session', true) %} and ev.domain_sessionid = sd.domain_sessionid {% endif %}

Source

{{
  config(
    tags=["this_run"]
  )
}}

with prep as (
select
  ev.page_view_id,
  ev.event_id,

  ev.app_id,

  -- user fields
  ev.user_id,
  ev.domain_userid,
  ev.network_userid,

  -- session fields
  ev.domain_sessionid,
  ev.domain_sessionidx,

  -- timestamp fields
  ev.dvce_created_tstamp,
  ev.collector_tstamp,
  ev.derived_tstamp,
  ev.derived_tstamp as start_tstamp,

  ev.doc_width,
  ev.doc_height,

  ev.page_title,
  ev.page_url,
  ev.page_urlscheme,
  ev.page_urlhost,
  ev.page_urlpath,
  ev.page_urlquery,
  ev.page_urlfragment,

  ev.mkt_medium,
  ev.mkt_source,
  ev.mkt_term,
  ev.mkt_content,
  ev.mkt_campaign,
  ev.mkt_clickid,
  ev.mkt_network,

  ev.page_referrer,
  ev.refr_urlscheme,
  ev.refr_urlhost,
  ev.refr_urlpath,
  ev.refr_urlquery,
  ev.refr_urlfragment,
  ev.refr_medium,
  ev.refr_source,
  ev.refr_term,

  ev.geo_country,
  ev.geo_region,
  ev.geo_region_name,
  ev.geo_city,
  ev.geo_zipcode,
  ev.geo_latitude,
  ev.geo_longitude,
  ev.geo_timezone ,

  ev.user_ipaddress,

  ev.useragent,

  ev.br_lang,
  ev.br_viewwidth,
  ev.br_viewheight,
  ev.br_colordepth,
  ev.br_renderengine,
  ev.os_timezone,

  -- optional fields, only populated if enabled.

  -- iab enrichment fields: set iab variable to true to enable
  {{snowplow_web.get_iab_context_fields()}},

  -- ua parser enrichment fields
  {{snowplow_web.get_ua_context_fields()}},

  -- yauaa enrichment fields
  {{snowplow_web.get_yauaa_context_fields()}}

  from {{ ref('snowplow_web_base_events_this_run') }} as ev

  where ev.event_name = 'page_view'
  and ev.page_view_id is not null

  {% if var("snowplow__ua_bot_filter", true) %}
     {{ filter_bots('ev') }}
  {% endif %}

  qualify row_number() over (partition by ev.page_view_id order by ev.derived_tstamp, ev.dvce_created_tstamp) = 1
)

, page_view_events as (
  select
    p.page_view_id,
    p.event_id,

    p.app_id,

    -- user fields
    p.user_id,
    p.domain_userid,
    p.network_userid,

    -- session fields
    p.domain_sessionid,
    p.domain_sessionidx,

    row_number() over (partition by p.domain_sessionid order by p.derived_tstamp, p.dvce_created_tstamp) AS page_view_in_session_index,

    -- timestamp fields
    p.dvce_created_tstamp,
    p.collector_tstamp,
    p.derived_tstamp,
    p.start_tstamp,
    coalesce(t.end_tstamp, p.derived_tstamp) as end_tstamp, -- only page views with pings will have a row in table t
    {{ snowplow_utils.current_timestamp_in_utc() }} as model_tstamp,

    coalesce(t.engaged_time_in_s, 0) as engaged_time_in_s, -- where there are no pings, engaged time is 0.
    datediff(second, p.derived_tstamp, coalesce(t.end_tstamp, p.derived_tstamp))  as absolute_time_in_s,

    sd.hmax as horizontal_pixels_scrolled,
    sd.vmax as vertical_pixels_scrolled,

    sd.relative_hmax as horizontal_percentage_scrolled,
    sd.relative_vmax as vertical_percentage_scrolled,

    p.doc_width,
    p.doc_height,

    p.page_title,
    p.page_url,
    p.page_urlscheme,
    p.page_urlhost,
    p.page_urlpath,
    p.page_urlquery,
    p.page_urlfragment,

    p.mkt_medium,
    p.mkt_source,
    p.mkt_term,
    p.mkt_content,
    p.mkt_campaign,
    p.mkt_clickid,
    p.mkt_network,

    p.page_referrer,
    p.refr_urlscheme,
    p.refr_urlhost,
    p.refr_urlpath,
    p.refr_urlquery,
    p.refr_urlfragment,
    p.refr_medium,
    p.refr_source,
    p.refr_term,

    p.geo_country,
    p.geo_region,
    p.geo_region_name,
    p.geo_city,
    p.geo_zipcode,
    p.geo_latitude,
    p.geo_longitude,
    p.geo_timezone,

    p.user_ipaddress,

    p.useragent,

    p.br_lang,
    p.br_viewwidth,
    p.br_viewheight,
    p.br_colordepth,
    p.br_renderengine,

    p.os_timezone,

    p.category,
    p.primary_impact,
    p.reason,
    p.spider_or_robot,

    p.useragent_family,
    p.useragent_major,
    p.useragent_minor,
    p.useragent_patch,
    p.useragent_version,
    p.os_family,
    p.os_major,
    p.os_minor,
    p.os_patch,
    p.os_patch_minor,
    p.os_version,
    p.device_family,

    p.device_class,
    p.agent_class,
    p.agent_name,
    p.agent_name_version,
    p.agent_name_version_major,
    p.agent_version,
    p.agent_version_major,
    p.device_brand,
    p.device_name,
    p.device_version,
    p.layout_engine_class,
    p.layout_engine_name,
    p.layout_engine_name_version,
    p.layout_engine_name_version_major,
    p.layout_engine_version,
    p.layout_engine_version_major,
    p.operating_system_class,
    p.operating_system_name,
    p.operating_system_name_version,
    p.operating_system_version

  from prep p

  left join {{ ref('snowplow_web_pv_engaged_time') }} t
  on p.page_view_id = t.page_view_id {% if var('snowplow__limit_page_views_to_session', true) %} and p.domain_sessionid = t.domain_sessionid {% endif %}

  left join {{ ref('snowplow_web_pv_scroll_depth') }} sd
  on p.page_view_id = sd.page_view_id {% if var('snowplow__limit_page_views_to_session', true) %} and p.domain_sessionid = sd.domain_sessionid {% endif %}
)

select
  pve.page_view_id,
  pve.event_id,

  pve.app_id,

  -- user fields
  pve.user_id,
  pve.domain_userid,
  pve.network_userid,

  -- session fields
  pve.domain_sessionid,
  pve.domain_sessionidx,

  pve.page_view_in_session_index,
  max(pve.page_view_in_session_index) over (partition by pve.domain_sessionid) as page_views_in_session,

  -- timestamp fields
  pve.dvce_created_tstamp,
  pve.collector_tstamp,
  pve.derived_tstamp,
  pve.start_tstamp,
  pve.end_tstamp,
  pve.model_tstamp,

  pve.engaged_time_in_s,
  pve.absolute_time_in_s,

  pve.horizontal_pixels_scrolled,
  pve.vertical_pixels_scrolled,

  pve.horizontal_percentage_scrolled,
  pve.vertical_percentage_scrolled,

  pve.doc_width,
  pve.doc_height,

  pve.page_title,
  pve.page_url,
  pve.page_urlscheme,
  pve.page_urlhost,
  pve.page_urlpath,
  pve.page_urlquery,
  pve.page_urlfragment,

  pve.mkt_medium,
  pve.mkt_source,
  pve.mkt_term,
  pve.mkt_content,
  pve.mkt_campaign,
  pve.mkt_clickid,
  pve.mkt_network,

  pve.page_referrer,
  pve.refr_urlscheme,
  pve.refr_urlhost,
  pve.refr_urlpath,
  pve.refr_urlquery,
  pve.refr_urlfragment,
  pve.refr_medium,
  pve.refr_source,
  pve.refr_term,

  pve.geo_country,
  pve.geo_region,
  pve.geo_region_name,
  pve.geo_city,
  pve.geo_zipcode,
  pve.geo_latitude,
  pve.geo_longitude,
  pve.geo_timezone,

  pve.user_ipaddress,

  pve.useragent,

  pve.br_lang,
  pve.br_viewwidth,
  pve.br_viewheight,
  pve.br_colordepth,
  pve.br_renderengine,

  pve.os_timezone,

  pve.category,
  pve.primary_impact,
  pve.reason,
  pve.spider_or_robot,

  pve.useragent_family,
  pve.useragent_major,
  pve.useragent_minor,
  pve.useragent_patch,
  pve.useragent_version,
  pve.os_family,
  pve.os_major,
  pve.os_minor,
  pve.os_patch,
  pve.os_patch_minor,
  pve.os_version,
  pve.device_family,

  pve.device_class,
  pve.agent_class,
  pve.agent_name,
  pve.agent_name_version,
  pve.agent_name_version_major,
  pve.agent_version,
  pve.agent_version_major,
  pve.device_brand,
  pve.device_name,
  pve.device_version,
  pve.layout_engine_class,
  pve.layout_engine_name,
  pve.layout_engine_name_version,
  pve.layout_engine_name_version_major,
  pve.layout_engine_version,
  pve.layout_engine_version_major,
  pve.operating_system_class,
  pve.operating_system_name,
  pve.operating_system_name_version,
  pve.operating_system_version

from page_view_events pve

Source

{{
  config(
    tags=["this_run"]
  )
}}


select
  ev.page_view_id,
  ev.event_id,

  ev.app_id,

  -- user fields
  ev.user_id,
  ev.domain_userid,
  ev.network_userid,

  -- session fields
  ev.domain_sessionid,
  ev.domain_sessionidx,

  ev.page_view_in_session_index,
  max(ev.page_view_in_session_index) over (partition by ev.domain_sessionid) as page_views_in_session,

  -- timestamp fields
  ev.dvce_created_tstamp,
  ev.collector_tstamp,
  ev.derived_tstamp,
  ev.start_tstamp,
  coalesce(t.end_tstamp, ev.derived_tstamp) as end_tstamp, -- only page views with pings will have a row in table t
  {{ snowplow_utils.current_timestamp_in_utc() }} as model_tstamp,

  coalesce(t.engaged_time_in_s, 0) as engaged_time_in_s, -- where there are no pings, engaged time is 0.
  {{ datediff('ev.derived_tstamp', 'coalesce(t.end_tstamp, ev.derived_tstamp)', 'second') }} as absolute_time_in_s,

  sd.hmax as horizontal_pixels_scrolled,
  sd.vmax as vertical_pixels_scrolled,

  sd.relative_hmax as horizontal_percentage_scrolled,
  sd.relative_vmax as vertical_percentage_scrolled,

  ev.doc_width,
  ev.doc_height,

  ev.page_title,
  ev.page_url,
  ev.page_urlscheme,
  ev.page_urlhost,
  ev.page_urlpath,
  ev.page_urlquery,
  ev.page_urlfragment,

  ev.mkt_medium,
  ev.mkt_source,
  ev.mkt_term,
  ev.mkt_content,
  ev.mkt_campaign,
  ev.mkt_clickid,
  ev.mkt_network,

  ev.page_referrer,
  ev.refr_urlscheme,
  ev.refr_urlhost,
  ev.refr_urlpath,
  ev.refr_urlquery,
  ev.refr_urlfragment,
  ev.refr_medium,
  ev.refr_source,
  ev.refr_term,

  ev.geo_country,
  ev.geo_region,
  ev.geo_region_name,
  ev.geo_city,
  ev.geo_zipcode,
  ev.geo_latitude,
  ev.geo_longitude,
  ev.geo_timezone,

  ev.user_ipaddress,

  ev.useragent,

  ev.br_lang,
  ev.br_viewwidth,
  ev.br_viewheight,
  ev.br_colordepth,
  ev.br_renderengine,

  ev.os_timezone,

  -- optional fields, only populated if enabled.

  -- iab enrichment fields: set iab variable to true to enable
  {{snowplow_web.get_iab_context_fields('iab')}},

  -- ua parser enrichment fields
  {{snowplow_web.get_ua_context_fields('ua')}},

  -- yauaa enrichment fields
  {{snowplow_web.get_yauaa_context_fields('ya')}}

from {{ ref('snowplow_web_page_view_events') }} ev

left join {{ ref('snowplow_web_pv_engaged_time') }} t
on ev.page_view_id = t.page_view_id {% if var('snowplow__limit_page_views_to_session', true) %} and ev.domain_sessionid = t.domain_sessionid {% endif %}

left join {{ ref('snowplow_web_pv_scroll_depth') }} sd
on ev.page_view_id = sd.page_view_id {% if var('snowplow__limit_page_views_to_session', true) %} and ev.domain_sessionid = sd.domain_sessionid {% endif %}

{% if var('snowplow__enable_iab', false) -%}

  left join {{ ref('snowplow_web_pv_iab') }} iab
  on ev.page_view_id = iab.page_view_id

{% endif -%}

{% if var('snowplow__enable_ua', false) -%}

  left join {{ ref('snowplow_web_pv_ua_parser') }} ua
  on ev.page_view_id = ua.page_view_id

{% endif -%}

{% if var('snowplow__enable_yauaa', false) -%}

  left join {{ ref('snowplow_web_pv_yauaa') }} ya
  on ev.page_view_id = ya.page_view_id

{%- endif -%}

Source

{{
  config(
    tags=["this_run"],
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
  )
}}

with prep as (
select
  ev.page_view_id,
  ev.event_id,

  ev.app_id,

  -- user fields
  ev.user_id,
  ev.domain_userid,
  ev.network_userid,

  -- session fields
  ev.domain_sessionid,
  ev.domain_sessionidx,

  -- timestamp fields
  ev.dvce_created_tstamp,
  ev.collector_tstamp,
  ev.derived_tstamp,
  ev.derived_tstamp as start_tstamp,

  ev.doc_width,
  ev.doc_height,

  ev.page_title,
  ev.page_url,
  ev.page_urlscheme,
  ev.page_urlhost,
  ev.page_urlpath,
  ev.page_urlquery,
  ev.page_urlfragment,

  ev.mkt_medium,
  ev.mkt_source,
  ev.mkt_term,
  ev.mkt_content,
  ev.mkt_campaign,
  ev.mkt_clickid,
  ev.mkt_network,

  ev.page_referrer,
  ev.refr_urlscheme,
  ev.refr_urlhost,
  ev.refr_urlpath,
  ev.refr_urlquery,
  ev.refr_urlfragment,
  ev.refr_medium,
  ev.refr_source,
  ev.refr_term,

  ev.geo_country,
  ev.geo_region,
  ev.geo_region_name,
  ev.geo_city,
  ev.geo_zipcode,
  ev.geo_latitude,
  ev.geo_longitude,
  ev.geo_timezone ,

  ev.user_ipaddress,

  ev.useragent,

  ev.br_lang,
  ev.br_viewwidth,
  ev.br_viewheight,
  ev.br_colordepth,
  ev.br_renderengine,
  ev.os_timezone,

  -- optional fields, only populated if enabled.

  -- iab enrichment fields: set iab variable to true to enable
  {{snowplow_web.get_iab_context_fields()}},

  -- ua parser enrichment fields
  {{snowplow_web.get_ua_context_fields()}},

  -- yauaa enrichment fields
  {{snowplow_web.get_yauaa_context_fields()}}

  from {{ ref('snowplow_web_base_events_this_run') }} as ev

  where ev.event_name = 'page_view'
  and ev.page_view_id is not null

  {% if var("snowplow__ua_bot_filter", true) %}
     {{ filter_bots('ev') }}
  {% endif %}

  qualify row_number() over (partition by ev.page_view_id order by ev.derived_tstamp, ev.dvce_created_tstamp) = 1
)

, page_view_events as (
  select
    p.page_view_id,
    p.event_id,

    p.app_id,

    -- user fields
    p.user_id,
    p.domain_userid,
    p.network_userid,

    -- session fields
    p.domain_sessionid,
    p.domain_sessionidx,

    row_number() over (partition by p.domain_sessionid order by p.derived_tstamp, p.dvce_created_tstamp) AS page_view_in_session_index,

    -- timestamp fields
    p.dvce_created_tstamp,
    p.collector_tstamp,
    p.derived_tstamp,
    p.start_tstamp,
    coalesce(t.end_tstamp, p.derived_tstamp) as end_tstamp, -- only page views with pings will have a row in table t
    {{ snowplow_utils.current_timestamp_in_utc() }} as model_tstamp,

    coalesce(t.engaged_time_in_s, 0) as engaged_time_in_s, -- where there are no pings, engaged time is 0.
    timediff(second, p.derived_tstamp, coalesce(t.end_tstamp, p.derived_tstamp))  as absolute_time_in_s,

    sd.hmax as horizontal_pixels_scrolled,
    sd.vmax as vertical_pixels_scrolled,

    sd.relative_hmax as horizontal_percentage_scrolled,
    sd.relative_vmax as vertical_percentage_scrolled,

    p.doc_width,
    p.doc_height,

    p.page_title,
    p.page_url,
    p.page_urlscheme,
    p.page_urlhost,
    p.page_urlpath,
    p.page_urlquery,
    p.page_urlfragment,

    p.mkt_medium,
    p.mkt_source,
    p.mkt_term,
    p.mkt_content,
    p.mkt_campaign,
    p.mkt_clickid,
    p.mkt_network,

    p.page_referrer,
    p.refr_urlscheme,
    p.refr_urlhost,
    p.refr_urlpath,
    p.refr_urlquery,
    p.refr_urlfragment,
    p.refr_medium,
    p.refr_source,
    p.refr_term,

    p.geo_country,
    p.geo_region,
    p.geo_region_name,
    p.geo_city,
    p.geo_zipcode,
    p.geo_latitude,
    p.geo_longitude,
    p.geo_timezone,

    p.user_ipaddress,

    p.useragent,

    p.br_lang,
    p.br_viewwidth,
    p.br_viewheight,
    p.br_colordepth,
    p.br_renderengine,

    p.os_timezone,

    p.category,
    p.primary_impact,
    p.reason,
    p.spider_or_robot,

    p.useragent_family,
    p.useragent_major,
    p.useragent_minor,
    p.useragent_patch,
    p.useragent_version,
    p.os_family,
    p.os_major,
    p.os_minor,
    p.os_patch,
    p.os_patch_minor,
    p.os_version,
    p.device_family,

    p.device_class,
    p.agent_class,
    p.agent_name,
    p.agent_name_version,
    p.agent_name_version_major,
    p.agent_version,
    p.agent_version_major,
    p.device_brand,
    p.device_name,
    p.device_version,
    p.layout_engine_class,
    p.layout_engine_name,
    p.layout_engine_name_version,
    p.layout_engine_name_version_major,
    p.layout_engine_version,
    p.layout_engine_version_major,
    p.operating_system_class,
    p.operating_system_name,
    p.operating_system_name_version,
    p.operating_system_version

  from prep p

  left join {{ ref('snowplow_web_pv_engaged_time') }} t
  on p.page_view_id = t.page_view_id {% if var('snowplow__limit_page_views_to_session', true) %} and p.domain_sessionid = t.domain_sessionid {% endif %}

  left join {{ ref('snowplow_web_pv_scroll_depth') }} sd
  on p.page_view_id = sd.page_view_id {% if var('snowplow__limit_page_views_to_session', true) %} and p.domain_sessionid = sd.domain_sessionid {% endif %}
)

select
  pve.page_view_id,
  pve.event_id,

  pve.app_id,

  -- user fields
  pve.user_id,
  pve.domain_userid,
  pve.network_userid,

  -- session fields
  pve.domain_sessionid,
  pve.domain_sessionidx,

  pve.page_view_in_session_index,
  max(pve.page_view_in_session_index) over (partition by pve.domain_sessionid) as page_views_in_session,

  -- timestamp fields
  pve.dvce_created_tstamp,
  pve.collector_tstamp,
  pve.derived_tstamp,
  pve.start_tstamp,
  pve.end_tstamp,
  pve.model_tstamp,

  pve.engaged_time_in_s,
  pve.absolute_time_in_s,

  pve.horizontal_pixels_scrolled,
  pve.vertical_pixels_scrolled,

  pve.horizontal_percentage_scrolled,
  pve.vertical_percentage_scrolled,

  pve.doc_width,
  pve.doc_height,

  pve.page_title,
  pve.page_url,
  pve.page_urlscheme,
  pve.page_urlhost,
  pve.page_urlpath,
  pve.page_urlquery,
  pve.page_urlfragment,

  pve.mkt_medium,
  pve.mkt_source,
  pve.mkt_term,
  pve.mkt_content,
  pve.mkt_campaign,
  pve.mkt_clickid,
  pve.mkt_network,

  pve.page_referrer,
  pve.refr_urlscheme,
  pve.refr_urlhost,
  pve.refr_urlpath,
  pve.refr_urlquery,
  pve.refr_urlfragment,
  pve.refr_medium,
  pve.refr_source,
  pve.refr_term,

  pve.geo_country,
  pve.geo_region,
  pve.geo_region_name,
  pve.geo_city,
  pve.geo_zipcode,
  pve.geo_latitude,
  pve.geo_longitude,
  pve.geo_timezone,

  pve.user_ipaddress,

  pve.useragent,

  pve.br_lang,
  pve.br_viewwidth,
  pve.br_viewheight,
  pve.br_colordepth,
  pve.br_renderengine,

  pve.os_timezone,

  pve.category,
  pve.primary_impact,
  pve.reason,
  pve.spider_or_robot,

  pve.useragent_family,
  pve.useragent_major,
  pve.useragent_minor,
  pve.useragent_patch,
  pve.useragent_version,
  pve.os_family,
  pve.os_major,
  pve.os_minor,
  pve.os_patch,
  pve.os_patch_minor,
  pve.os_version,
  pve.device_family,

  pve.device_class,
  pve.agent_class,
  pve.agent_name,
  pve.agent_name_version,
  pve.agent_name_version_major,
  pve.agent_version,
  pve.agent_version_major,
  pve.device_brand,
  pve.device_name,
  pve.device_version,
  pve.layout_engine_class,
  pve.layout_engine_name,
  pve.layout_engine_name_version,
  pve.layout_engine_name_version_major,
  pve.layout_engine_version,
  pve.layout_engine_version_major,
  pve.operating_system_class,
  pve.operating_system_name,
  pve.operating_system_name_version,
  pve.operating_system_version

from page_view_events pve

Depends On

Models
Macros

Referenced By

Models

model.snowplow_web.snowplow_web_page_views

Snowplow Web Pv Engaged Time

models/page_views/scratch/snowplow_web_pv_engaged_time.sql

Description

This model calculates the time a visitor spent engaged on a given page view. This is calculated using the number of page ping events received for that page view.

Type: Table

Details

Columns

Column Name	Description	Type
page_view_id	A UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
domain_sessionid		text
end_tstamp		timestamp_ntz
engaged_time_in_s		number

Code

default

Source

{{
  config(
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
  )
}}

select
  ev.page_view_id,
  {% if var('snowplow__limit_page_views_to_session', true) %}
  ev.domain_sessionid,
  {% endif %}
  max(ev.derived_tstamp) as end_tstamp,

  -- aggregate pings:
    -- divides epoch tstamps by snowplow__heartbeat to get distinct intervals
    -- floor rounds to nearest integer - duplicates all evaluate to the same number
    -- count(distinct) counts duplicates only once
    -- adding snowplow__min_visit_length accounts for the page view event itself.

  {{ var("snowplow__heartbeat", 10) }} * (count(distinct(floor({{ snowplow_utils.to_unixtstamp('ev.dvce_created_tstamp') }}/{{ var("snowplow__heartbeat", 10) }}))) - 1) + {{ var("snowplow__min_visit_length", 5) }} as engaged_time_in_s

from {{ ref('snowplow_web_base_events_this_run') }} as ev

where ev.event_name = 'page_ping'
and ev.page_view_id is not null

group by 1 {% if var('snowplow__limit_page_views_to_session', true) %}, 2 {% endif %}

Depends On

Models
Macros

model.snowplow_web.snowplow_web_base_events_this_run

Referenced By

Models

model.snowplow_web.snowplow_web_page_views_this_run

Snowplow Web Pv Iab

models/page_views/scratch/<adaptor>/snowplow_web_pv_iab.sql

Description

Redshift and Postgres only. This is a staging table containing context data generated by the IAB enrichment for the events in the given run. The model is disable by default. Refer to the docs to enable.

The IAB Spiders & Robots enrichment uses the IAB/ABC International Spiders and Bots List to determine whether an event was produced by a user or a robot/spider based on its’ IP address and user agent.

File Paths

default

models/page_views/scratch/default/snowplow_web_pv_iab.sql

Details

Columns

Column Name	Description
page_view_id	A UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’

Code

default

Source

{{
  config(
    enabled=(var('snowplow__enable_iab', false) and target.type in ['redshift', 'postgres'] | as_bool())
  )
}}

with base as (
  select
    pv.page_view_id,

    iab.category,
    iab.primary_impact,
    iab.reason,
    iab.spider_or_robot,
    row_number() over (partition by pv.page_view_id order by pv.collector_tstamp) as dedupe_index

  from {{ var('snowplow__iab_context') }} iab

  inner join {{ ref('snowplow_web_page_view_events') }} pv
  on iab.root_id = pv.event_id
  and iab.root_tstamp = pv.collector_tstamp

  where iab.root_tstamp >= (select lower_limit from {{ ref('snowplow_web_pv_limits') }})
    and iab.root_tstamp <= (select upper_limit from {{ ref('snowplow_web_pv_limits') }})

)

select *

from base

where dedupe_index = 1

Snowplow Web Pv Limits

models/page_views/scratch/<adaptor>/snowplow_web_pv_limits.sql

Description

This model calculates the lower and upper limit for the page views events in the given run. This is based taking the min and max collector_tstamp across all page views. It is used to improve performance when selected rows from the various context tables such as the UA parser table.

File Paths

default

models/page_views/scratch/default/snowplow_web_pv_limits.sql

Details

Code

default

Source

select
  min(collector_tstamp) as lower_limit,
  max(collector_tstamp) as upper_limit

from {{ ref('snowplow_web_base_events_this_run') }}

where page_view_id is not null

Snowplow Web Pv Scroll Depth

models/page_views/scratch/snowplow_web_pv_scroll_depth.sql

Description

This model calculates the horizontal and vertical scroll depth of the visitor on a given page view. Such metrics are useful when assessing engagement on a page view.

Type: Table

Details

Columns

Column Name	Description	Type
page_view_id	A UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
domain_sessionid		text
doc_width		number
doc_height		number
br_viewwidth		number
br_viewheight		number
hmin		number
hmax		number
vmin		number
vmax		number
relative_hmin		float
relative_hmax		float
relative_vmin		float
relative_vmax		float

Code

default

Source

{{
  config(
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
  )
}}

with prep as (
  select
    ev.page_view_id,
    {% if var('snowplow__limit_page_views_to_session', true) %}
    ev.domain_sessionid,
    {% endif %}

    max(ev.doc_width) as doc_width,
    max(ev.doc_height) as doc_height,

    max(ev.br_viewwidth) as br_viewwidth,
    max(ev.br_viewheight) as br_viewheight,

    -- coalesce replaces null with 0 (because the page view event does send an offset)
    -- greatest prevents outliers (negative offsets)
    -- least also prevents outliers (offsets greater than the docwidth or docheight)

    least(greatest(min(coalesce(ev.pp_xoffset_min, 0)), 0), max(ev.doc_width)) as hmin, -- should be zero
    least(greatest(max(coalesce(ev.pp_xoffset_max, 0)), 0), max(ev.doc_width)) as hmax,

    least(greatest(min(coalesce(ev.pp_yoffset_min, 0)), 0), max(ev.doc_height)) as vmin, -- should be zero (edge case: not zero because the pv event is missing)
    least(greatest(max(coalesce(ev.pp_yoffset_max, 0)), 0), max(ev.doc_height)) as vmax

  from {{ ref('snowplow_web_base_events_this_run') }} as ev

  where ev.event_name in ('page_view', 'page_ping')
    and ev.page_view_id is not null
    and ev.doc_height > 0 -- exclude problematic (but rare) edge case
    and ev.doc_width > 0 -- exclude problematic (but rare) edge case

  group by 1 {% if var('snowplow__limit_page_views_to_session', true) %}, 2 {% endif %}
)

select
  page_view_id,
  {% if var('snowplow__limit_page_views_to_session', true) %}
  domain_sessionid,
  {% endif %}

  doc_width,
  doc_height,

  br_viewwidth,
  br_viewheight,

  hmin,
  hmax,
  vmin,
  vmax,

  cast(round(100*(greatest(hmin, 0)/cast(doc_width as {{ type_float() }}))) as {{ type_float() }}) as relative_hmin, -- brackets matter: because hmin is of type int, we need to divide before we multiply by 100 or we risk an overflow
  cast(round(100*(least(hmax + br_viewwidth, doc_width)/cast(doc_width as {{ type_float() }}))) as {{ type_float() }}) as relative_hmax,
  cast(round(100*(greatest(vmin, 0)/cast(doc_height as {{ type_float() }}))) as {{ type_float() }}) as relative_vmin,
  cast(round(100*(least(vmax + br_viewheight, doc_height)/cast(doc_height as {{ type_float() }}))) as {{ type_float() }}) as relative_vmax -- not zero when a user hasn't scrolled because it includes the non-zero viewheight

from prep

Depends On

Models
Macros

model.snowplow_web.snowplow_web_base_events_this_run

Referenced By

Models

model.snowplow_web.snowplow_web_page_views_this_run

Snowplow Web Pv Ua Parser

models/page_views/scratch/<adaptor>/snowplow_web_pv_ua_parser.sql

Description

Redshift and Postgres only. This is a staging table containing context data generated by the UA parser enrichment for the events in the given run. The model is disable by default. Refer to the docs to enable.

File Paths

default

models/page_views/scratch/default/snowplow_web_pv_ua_parser.sql

Details

Columns

Column Name	Description
page_view_id	A UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’

Code

default

Source

{{
  config(
    enabled=(var('snowplow__enable_ua', false) and target.type in ['redshift', 'postgres'] | as_bool())
  )
}}

with base as (
  select
    pv.page_view_id,

    ua.useragent_family,
    ua.useragent_major,
    ua.useragent_minor,
    ua.useragent_patch,
    ua.useragent_version,
    ua.os_family,
    ua.os_major,
    ua.os_minor,
    ua.os_patch,
    ua.os_patch_minor,
    ua.os_version,
    ua.device_family,
    row_number() over (partition by pv.page_view_id order by pv.collector_tstamp) as dedupe_index

  from {{ var('snowplow__ua_parser_context') }} as ua

  inner join {{ ref('snowplow_web_page_view_events') }} pv
  on ua.root_id = pv.event_id
  and ua.root_tstamp = pv.collector_tstamp

  where ua.root_tstamp >= (select lower_limit from {{ ref('snowplow_web_pv_limits') }})
    and ua.root_tstamp <= (select upper_limit from {{ ref('snowplow_web_pv_limits') }})

)

select *

from base

where dedupe_index = 1

Snowplow Web Pv Yauaa

models/page_views/scratch/<adaptor>/snowplow_web_pv_yauaa.sql

Description

Redshift and Postgres only. This is a staging table containing context data generated by the YAUAA enrichment. The model is disable by default. Refer to the docs to enable.

File Paths

default

models/page_views/scratch/default/snowplow_web_pv_yauaa.sql

Details

Columns

Column Name	Description
page_view_id	A UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’

Code

default

Source

{{
  config(
    enabled=(var('snowplow__enable_yauaa', false) and target.type in ['redshift', 'postgres'] | as_bool())
  )
}}


with base as (
  select
    pv.page_view_id,

    ya.device_class,
    ya.agent_class,
    ya.agent_name,
    ya.agent_name_version,
    ya.agent_name_version_major,
    ya.agent_version,
    ya.agent_version_major,
    ya.device_brand,
    ya.device_name,
    ya.device_version,
    ya.layout_engine_class,
    ya.layout_engine_name,
    ya.layout_engine_name_version,
    ya.layout_engine_name_version_major,
    ya.layout_engine_version,
    ya.layout_engine_version_major,
    ya.operating_system_class,
    ya.operating_system_name,
    ya.operating_system_name_version,
    ya.operating_system_version,
    row_number() over (partition by pv.page_view_id order by pv.collector_tstamp) as dedupe_index

  from {{ var('snowplow__yauaa_context') }} ya

  inner join {{ ref('snowplow_web_page_view_events') }} pv
  on ya.root_id = pv.event_id
  and ya.root_tstamp = pv.collector_tstamp

  where ya.root_tstamp >= (select lower_limit from {{ ref('snowplow_web_pv_limits') }})
    and ya.root_tstamp <= (select upper_limit from {{ ref('snowplow_web_pv_limits') }})

)

select *

from base

where dedupe_index = 1

Snowplow Web Sessions

models/sessions/snowplow_web_sessions.sql

Description

This derived incremental table contains all historic sessions and should be the end point for any analysis or BI tools.

Type: Table

Details

Columns

Column Name	Description	Type
app_id	Application ID e.g. ‘angry-birds’ is used to distinguish different applications that are being tracked by the same Snowplow stack, e.g. production versus dev.	text
domain_sessionid	A visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
domain_sessionidx	A visit / session index e.g. 3	number
start_tstamp	Timestamp for the start of the session, based on `derived_tstamp`	timestamp_ntz
end_tstamp	Timestamp for the end of the session, based on `derived_tstamp`	timestamp_ntz
model_tstamp	The current timestamp when the model processed this row.	timestamp_ntz
user_id	Unique ID set by business e.g. ‘jon.doe@email.com’	text
domain_userid	User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’	text
stitched_user_id		text
network_userid	User ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’	text
page_views	The number of distinct page views within a session	number
engaged_time_in_s	The total time engaged by a user within a session	number
absolute_time_in_s	The time in seconds between the `start_tstamp` and `end_tstamp`	number
first_page_title	The title of the first page visited within the session	text
first_page_url	The url of the first page visited within the session	text
first_page_urlscheme	The urlscheme of the first page visited within the session	text
first_page_urlhost	The urlhost of the first page visited within the session	text
first_page_urlpath	The urlpath of the first page visited within the session	text
first_page_urlquery	The urlquery of the first page visited within the session	text
first_page_urlfragment	The urlfragment of the first page visited within the session	text
last_page_title	The title of the last page visited within the session	text
last_page_url	The url of the last page visited within the session	text
last_page_urlscheme	The urlscheme of the last page visited within the session	text
last_page_urlhost	The urlhost of the last page visited within the session	text
last_page_urlpath	The urlpath of the last page visited within the session	text
last_page_urlquery	The urlquery of the last page visited within the session	text
last_page_urlfragment	The urlfragment of the last page visited within the session	text
referrer	The referrer associated with the first page view of the session	text
refr_urlscheme	Referer scheme e.g. ‘http’	text
refr_urlhost	Referer host e.g. ‘www.bing.com’	text
refr_urlpath	Referer page path e.g. ‘/images/search’	text
refr_urlquery	Referer URL querystring e.g. ‘q=psychic+oracle+cards’	text
refr_urlfragment	Referer URL fragment	text
refr_medium	Type of referer e.g. ‘search’, ‘internal’	text
refr_source	Name of referer if recognised e.g. ‘Bing images’	text
refr_term	Keywords if source is a search engine e.g. ‘psychic oracle cards’	text
mkt_medium	Type of traffic source e.g. ‘cpc’, ‘affiliate’, ‘organic’, ‘social’	text
mkt_source	The company / website where the traffic came from e.g. ‘Google’, ‘Facebook’	text
mkt_term	Any keywords associated with the referrer e.g. ‘new age tarot decks’	text
mkt_content	The content of the ad. (Or an ID so that it can be looked up.) e.g. 13894723	text
mkt_campaign	The campaign ID e.g. ‘diageo-123’	text
mkt_clickid	The click ID e.g. ‘ac3d8e459’	text
mkt_network	The ad network to which the click ID belongs e.g. ‘DoubleClick’	text
geo_country	ISO 3166-1 code for the country the visitor is located in e.g. ‘GB’, ‘US’	text
geo_region	ISO-3166-2 code for country region the visitor is in e.g. ‘I9’, ‘TX’	text
geo_region_name	Visitor region name e.g. ‘Florida’	text
geo_city	City the visitor is in e.g. ‘New York’, ‘London’	text
geo_zipcode	Postcode the visitor is in e.g. ‘94109’	text
geo_latitude	Visitor location latitude e.g. 37.443604	float
geo_longitude	Visitor location longitude e.g. -122.4124	float
geo_timezone	Visitor timezone name e.g. ‘Europe/London’	text
user_ipaddress	User IP address e.g. ‘92.231.54.234’	text
useragent	Raw useragent	text
br_renderengine	Browser rendering engine e.g. ‘GECKO’	text
br_lang	Language the browser is set to e.g. ‘en-GB’	text
os_timezone	Client operating system timezone e.g. ‘Europe/London’	text
category	Category based on activity if the IP/UA is a spider or robot, BROWSER otherwise	text
primary_impact	Whether the spider or robot would affect page impression measurement, ad impression measurement, both or none	text
reason	Type of failed check if the IP/UA is a spider or robot, PASSED_ALL otherwise	text
spider_or_robot	True if the IP address or user agent checked against the list is a spider or robot, false otherwise	boolean
useragent_family	Useragent family (browser) name	text
useragent_major	Useragent major version	text
useragent_minor	Useragent minor version	text
useragent_patch	Useragent patch version	text
useragent_version	Full version of the useragent	text
os_family	Operating system family e.g. ‘Linux’	text
os_major	Operation system major version	text
os_minor	Operation system minor version	text
os_patch	Operation system patch version	text
os_patch_minor	Operation system patch minor version	text
os_version	Operation system full version	text
device_family	Device type	text
device_class	Class of device e.g. phone	text
agent_class	Class of agent e.g. browser	text
agent_name	Name of agent e.g. Chrome	text
agent_name_version	Name and version of agent e.g. Chrome 53.0.2785.124	text
agent_name_version_major	Name and major version of agent e.g. Chrome 53	text
agent_version	Version of agent e.g. 53.0.2785.124	text
agent_version_major	Major version of agent e.g. 53	text
device_brand	Brand of device e.g. Google	text
device_name	Name of device e.g. Google Nexus 6	text
device_version	Version of device e.g. 6.0	text
layout_engine_class	Class of layout engine e.g. Browser	text
layout_engine_name	Name of layout engine e.g. Blink	text
layout_engine_name_version	Name and version of layout engine e.g. Blink 53.0	text
layout_engine_name_version_major	Name and major version of layout engine e.g. Blink 53	text
layout_engine_version	Version of layout engine e.g. 53.0	text
layout_engine_version_major	Major version of layout engine e.g. 53	text
operating_system_class	Class of the OS e.g. Mobile	text
operating_system_name	Name of the OS e.g. Android	text
operating_system_name_version	Name and version of the OS e.g. Android 7.0	text
operating_system_version	Version of the OS e.g. 7.0	text

Code

default

Source

{{
  config(
    materialized='incremental',
    unique_key='domain_sessionid',
    upsert_date_key='start_tstamp',
    sort='start_tstamp',
    dist='domain_sessionid',
    partition_by = snowplow_utils.get_value_by_target_type(bigquery_val={
      "field": "start_tstamp",
      "data_type": "timestamp"
    }, databricks_val='start_tstamp_date'),
    cluster_by=snowplow_web.web_cluster_by_fields_sessions(),
    tags=["derived"],
    post_hook="{{ snowplow_web.stitch_user_identifiers(
      enabled=var('snowplow__session_stitching')
      ) }}",
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt')),
    tblproperties={
      'delta.autoOptimize.optimizeWrite' : 'true',
      'delta.autoOptimize.autoCompact' : 'true'
    },
    snowplow_optimize = true
  )
}}


select *
  {% if target.type in ['databricks', 'spark'] -%}
  , DATE(start_tstamp) as start_tstamp_date
  {%- endif %}
from {{ ref('snowplow_web_sessions_this_run') }}
where {{ snowplow_utils.is_run_with_new_events('snowplow_web') }} --returns false if run doesn't contain new events.

Depends On

Models
Macros

Referenced By

Models

model.snowplow_web.snowplow_web_users_sessions_this_run

Snowplow Web Sessions This Run

models/sessions/scratch/<adaptor>/snowplow_web_sessions_this_run.sql

Description

This staging table contains all the sessions for the given run of the Web model. It possess all the same columns as snowplow_web_sessions. If building a custom module that requires session level data, this is the table you should reference.

Type: Table

File Paths

bigquery
databricks
default
snowflake

models/sessions/scratch/bigquery/snowplow_web_sessions_this_run.sql

models/sessions/scratch/databricks/snowplow_web_sessions_this_run.sql

models/sessions/scratch/default/snowplow_web_sessions_this_run.sql

models/sessions/scratch/snowflake/snowplow_web_sessions_this_run.sql

Details

Columns

Column Name	Description	Type
app_id	Application ID e.g. ‘angry-birds’ is used to distinguish different applications that are being tracked by the same Snowplow stack, e.g. production versus dev.	text
domain_sessionid	A visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
domain_sessionidx	A visit / session index e.g. 3	number
start_tstamp	Timestamp for the start of the session, based on `derived_tstamp`	timestamp_ntz
end_tstamp	Timestamp for the end of the session, based on `derived_tstamp`	timestamp_ntz
model_tstamp	The current timestamp when the model processed this row.	timestamp_ntz
user_id	Unique ID set by business e.g. ‘jon.doe@email.com’	text
domain_userid	User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’	text
stitched_user_id		text
network_userid	User ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’	text
page_views	The number of distinct page views within a session	number
engaged_time_in_s	The total time engaged by a user within a session	number
absolute_time_in_s	The time in seconds between the `start_tstamp` and `end_tstamp`	number
first_page_title	The title of the first page visited within the session	text
first_page_url	The url of the first page visited within the session	text
first_page_urlscheme	The urlscheme of the first page visited within the session	text
first_page_urlhost	The urlhost of the first page visited within the session	text
first_page_urlpath	The urlpath of the first page visited within the session	text
first_page_urlquery	The urlquery of the first page visited within the session	text
first_page_urlfragment	The urlfragment of the first page visited within the session	text
last_page_title	The title of the last page visited within the session	text
last_page_url	The url of the last page visited within the session	text
last_page_urlscheme	The urlscheme of the last page visited within the session	text
last_page_urlhost	The urlhost of the last page visited within the session	text
last_page_urlpath	The urlpath of the last page visited within the session	text
last_page_urlquery	The urlquery of the last page visited within the session	text
last_page_urlfragment	The urlfragment of the last page visited within the session	text
referrer	The referrer associated with the first page view of the session	text
refr_urlscheme	Referer scheme e.g. ‘http’	text
refr_urlhost	Referer host e.g. ‘www.bing.com’	text
refr_urlpath	Referer page path e.g. ‘/images/search’	text
refr_urlquery	Referer URL querystring e.g. ‘q=psychic+oracle+cards’	text
refr_urlfragment	Referer URL fragment	text
refr_medium	Type of referer e.g. ‘search’, ‘internal’	text
refr_source	Name of referer if recognised e.g. ‘Bing images’	text
refr_term	Keywords if source is a search engine e.g. ‘psychic oracle cards’	text
mkt_medium	Type of traffic source e.g. ‘cpc’, ‘affiliate’, ‘organic’, ‘social’	text
mkt_source	The company / website where the traffic came from e.g. ‘Google’, ‘Facebook’	text
mkt_term	Any keywords associated with the referrer e.g. ‘new age tarot decks’	text
mkt_content	The content of the ad. (Or an ID so that it can be looked up.) e.g. 13894723	text
mkt_campaign	The campaign ID e.g. ‘diageo-123’	text
mkt_clickid	The click ID e.g. ‘ac3d8e459’	text
mkt_network	The ad network to which the click ID belongs e.g. ‘DoubleClick’	text
geo_country	ISO 3166-1 code for the country the visitor is located in e.g. ‘GB’, ‘US’	text
geo_region	ISO-3166-2 code for country region the visitor is in e.g. ‘I9’, ‘TX’	text
geo_region_name	Visitor region name e.g. ‘Florida’	text
geo_city	City the visitor is in e.g. ‘New York’, ‘London’	text
geo_zipcode	Postcode the visitor is in e.g. ‘94109’	text
geo_latitude	Visitor location latitude e.g. 37.443604	float
geo_longitude	Visitor location longitude e.g. -122.4124	float
geo_timezone	Visitor timezone name e.g. ‘Europe/London’	text
user_ipaddress	User IP address e.g. ‘92.231.54.234’	text
useragent	Raw useragent	text
br_renderengine	Browser rendering engine e.g. ‘GECKO’	text
br_lang	Language the browser is set to e.g. ‘en-GB’	text
os_timezone	Client operating system timezone e.g. ‘Europe/London’	text
category	Category based on activity if the IP/UA is a spider or robot, BROWSER otherwise	text
primary_impact	Whether the spider or robot would affect page impression measurement, ad impression measurement, both or none	text
reason	Type of failed check if the IP/UA is a spider or robot, PASSED_ALL otherwise	text
spider_or_robot	True if the IP address or user agent checked against the list is a spider or robot, false otherwise	boolean
useragent_family	Useragent family (browser) name	text
useragent_major	Useragent major version	text
useragent_minor	Useragent minor version	text
useragent_patch	Useragent patch version	text
useragent_version	Full version of the useragent	text
os_family	Operating system family e.g. ‘Linux’	text
os_major	Operation system major version	text
os_minor	Operation system minor version	text
os_patch	Operation system patch version	text
os_patch_minor	Operation system patch minor version	text
os_version	Operation system full version	text
device_family	Device type	text
device_class	Class of device e.g. phone	text
agent_class	Class of agent e.g. browser	text
agent_name	Name of agent e.g. Chrome	text
agent_name_version	Name and version of agent e.g. Chrome 53.0.2785.124	text
agent_name_version_major	Name and major version of agent e.g. Chrome 53	text
agent_version	Version of agent e.g. 53.0.2785.124	text
agent_version_major	Major version of agent e.g. 53	text
device_brand	Brand of device e.g. Google	text
device_name	Name of device e.g. Google Nexus 6	text
device_version	Version of device e.g. 6.0	text
layout_engine_class	Class of layout engine e.g. Browser	text
layout_engine_name	Name of layout engine e.g. Blink	text
layout_engine_name_version	Name and version of layout engine e.g. Blink 53.0	text
layout_engine_name_version_major	Name and major version of layout engine e.g. Blink 53	text
layout_engine_version	Version of layout engine e.g. 53.0	text
layout_engine_version_major	Major version of layout engine e.g. 53	text
operating_system_class	Class of the OS e.g. Mobile	text
operating_system_name	Name of the OS e.g. Android	text
operating_system_name_version	Name and version of the OS e.g. Android 7.0	text
operating_system_version	Version of the OS e.g. 7.0	text

Code

bigquery
databricks
default
snowflake

Source

{{
    config(
        tags=["this_run"]
    )
}}

with session_firsts as (
    select
        -- app id
        app_id as app_id,

        -- session fields
        domain_sessionid,
        domain_sessionidx,

        {{ snowplow_utils.current_timestamp_in_utc() }} as model_tstamp,

        -- user fields
        user_id,
        domain_userid,
        {% if var('snowplow__session_stitching') %}
            -- updated with mapping as part of post hook on derived sessions table
            cast(domain_userid as {{ type_string() }}) as stitched_user_id,
        {% else %}
            cast(null as {{ type_string() }}) as stitched_user_id,
        {% endif %}
        network_userid as network_userid,

        -- first page fields
        page_title as first_page_title,
        page_url as first_page_url,
        page_urlscheme as first_page_urlscheme,
        page_urlhost as first_page_urlhost,
        page_urlpath as first_page_urlpath,
        page_urlquery as first_page_urlquery,
        page_urlfragment as first_page_urlfragment,

        -- referrer fields
        page_referrer as referrer,
        refr_urlscheme as refr_urlscheme,
        refr_urlhost as refr_urlhost,
        refr_urlpath as refr_urlpath,
        refr_urlquery as refr_urlquery,
        refr_urlfragment as refr_urlfragment,
        refr_medium as refr_medium,
        refr_source as refr_source,
        refr_term as refr_term,

        -- marketing fields
        mkt_medium as mkt_medium,
        mkt_source as mkt_source,
        mkt_term as mkt_term,
        mkt_content as mkt_content,
        mkt_campaign as mkt_campaign,
        mkt_clickid as mkt_clickid,
        mkt_network as mkt_network,

        -- Most if not all the following fields should be the same across all events in a session, but this ensures they are
        -- geo fields
        geo_country as geo_country,
        geo_region as geo_region,
        geo_region_name as geo_region_name,
        geo_city as geo_city,
        geo_zipcode as geo_zipcode,
        geo_latitude as geo_latitude,
        geo_longitude as geo_longitude,
        geo_timezone as geo_timezone,

        -- ip address
        user_ipaddress as user_ipaddress,

        -- user agent
        useragent as useragent,

        br_renderengine as br_renderengine,
        br_lang as br_lang,
        os_timezone as os_timezone,

        -- optional fields, only populated if enabled.
        -- iab enrichment fields: set iab variable to true to enable
        {{ snowplow_utils.get_optional_fields(
                enabled=var('snowplow__enable_iab', false),
                fields=iab_fields(),
                col_prefix='contexts_com_iab_snowplow_spiders_and_robots_1',
                relation=ref('snowplow_web_base_events_this_run'),
                relation_alias='ev') }},

        -- ua parser enrichment fields: set ua_parser variable to true to enable
        {{ snowplow_utils.get_optional_fields(
                enabled=var('snowplow__enable_ua', false),
                fields=ua_fields(),
                col_prefix='contexts_com_snowplowanalytics_snowplow_ua_parser_context_1',
                relation=ref('snowplow_web_base_events_this_run'),
                relation_alias='ev') }},

        -- yauaa enrichment fields: set yauaa variable to true to enable
        {{ snowplow_utils.get_optional_fields(
                enabled=var('snowplow__enable_yauaa', false),
                fields=yauaa_fields(),
                col_prefix='contexts_nl_basjes_yauaa_context_1',
                relation=ref('snowplow_web_base_events_this_run'),
                relation_alias='ev') }},

        row_number() over (partition by ev.domain_sessionid order by ev.derived_tstamp, ev.dvce_created_tstamp) AS page_event_in_session_index,
        event_name
    from {{ ref('snowplow_web_base_events_this_run') }} ev
    where
        event_name in ('page_ping', 'page_view')
        and page_view_id is not null
        {% if var("snowplow__ua_bot_filter", true) %}
            {{ filter_bots('ev') }}
        {% endif %}
),

session_lasts as (
    select
        domain_sessionid,
        page_title as last_page_title,
        page_url as last_page_url,
        page_urlscheme as last_page_urlscheme,
        page_urlhost as last_page_urlhost,
        page_urlpath as last_page_urlpath,
        page_urlquery as last_page_urlquery,
        page_urlfragment as last_page_urlfragment,
        row_number() over (partition by domain_sessionid order by derived_tstamp desc, dvce_created_tstamp) AS page_event_in_session_index
    from {{ ref('snowplow_web_base_events_this_run') }}
    where
        event_name in ('page_view')
        and page_view_id is not null
        {% if var("snowplow__ua_bot_filter", true) %}
            {{ filter_bots() }}
        {% endif %}
),

session_aggs as (
    select
        domain_sessionid,
        min(derived_tstamp) as start_tstamp,
        max(derived_tstamp) as end_tstamp,
        -- engagement fields
        count(distinct page_view_id) as page_views,
        -- (hb * (#page pings - # distinct page view ids ON page pings)) + (# distinct page view ids ON page pings * min visit length)
        ({{ var("snowplow__heartbeat", 10) }} * (
                -- number of (unqiue in heartbeat increment) pages pings following a page ping (gap of heartbeat)
                count(distinct case
                        when event_name = 'page_ping' then
                        -- need to get a unique list of floored time PER page view, so create a dummy surrogate key...
                            {{ dbt.concat(['page_view_id', "cast(floor("~snowplow_utils.to_unixtstamp('dvce_created_tstamp')~"/"~var('snowplow__heartbeat', 10)~") as "~dbt.type_string()~")" ]) }}
                        else
                            null end) -
                    count(distinct case when event_name = 'page_ping' then page_view_id else null end)
                ))  +
            -- number of page pings following a page view (or no event) (gap of min visit length)
            (count(distinct case when event_name = 'page_ping' then page_view_id else null end) * {{ var("snowplow__min_visit_length", 5) }}) as engaged_time_in_s,
        {{ snowplow_utils.timestamp_diff('min(derived_tstamp)', 'max(derived_tstamp)', 'second') }} as absolute_time_in_s,
        -- number of page view ids with only page pings...
    from {{ ref('snowplow_web_base_events_this_run') }}
    where
        event_name in ('page_ping', 'page_view')
        and page_view_id is not null
        {% if var("snowplow__ua_bot_filter", true) %}
            {{ filter_bots() }}
        {% endif %}
    group by
        domain_sessionid
)

select
    -- app id
    a.app_id,

    -- session fields
    a.domain_sessionid,
    a.domain_sessionidx,

    -- when the session starts with a ping we need to add the min visit length to get when the session actually started
    case when a.event_name = 'page_ping' then
        {{ snowplow_utils.timestamp_add(datepart="second", interval=-var("snowplow__min_visit_length", 5), tstamp="c.start_tstamp") }}
    else c.start_tstamp end as start_tstamp,
    c.end_tstamp,
    a.model_tstamp,

    -- user fields
    a.user_id,
    a.domain_userid,
    a.stitched_user_id,
    a.network_userid,

    -- engagement fields
    c.page_views,
    c.engaged_time_in_s,
    -- when the session starts with a ping we need to add the min visit length to get when the session actually started
    c.absolute_time_in_s + case when a.event_name = 'page_ping' then {{ var("snowplow__min_visit_length", 5) }} else 0 end as absolute_time_in_s,

    -- first page fields
    a.first_page_title,
    a.first_page_url,
    a.first_page_urlscheme,
    a.first_page_urlhost,
    a.first_page_urlpath,
    a.first_page_urlquery,
    a.first_page_urlfragment,

    -- only take the first value when the last is genuinely missing (base on url as has to always be populated)
    case when b.last_page_url is null then coalesce(b.last_page_title, a.first_page_title) else b.last_page_title end as last_page_title,
    case when b.last_page_url is null then coalesce(b.last_page_url, a.first_page_url) else b.last_page_url end as last_page_url,
    case when b.last_page_url is null then coalesce(b.last_page_urlscheme, a.first_page_urlscheme) else b.last_page_urlscheme end as last_page_urlscheme,
    case when b.last_page_url is null then coalesce(b.last_page_urlhost, a.first_page_urlhost) else b.last_page_urlhost end as last_page_urlhost,
    case when b.last_page_url is null then coalesce(b.last_page_urlpath, a.first_page_urlpath) else b.last_page_urlpath end as last_page_urlpath,
    case when b.last_page_url is null then coalesce(b.last_page_urlquery, a.first_page_urlquery) else b.last_page_urlquery end as last_page_urlquery,
    case when b.last_page_url is null then coalesce(b.last_page_urlfragment, a.first_page_urlfragment) else b.last_page_urlfragment end as last_page_urlfragment,

    -- referrer fields
    a.referrer,
    a.refr_urlscheme,
    a.refr_urlhost,
    a.refr_urlpath,
    a.refr_urlquery,
    a.refr_urlfragment,
    a.refr_medium,
    a.refr_source,
    a.refr_term,

    -- marketing fields
    a.mkt_medium,
    a.mkt_source,
    a.mkt_term,
    a.mkt_content,
    a.mkt_campaign,
    a.mkt_clickid,
    a.mkt_network,

    -- geo fields
    a.geo_country,
    a.geo_region,
    a.geo_region_name,
    a.geo_city,
    a.geo_zipcode,
    a.geo_latitude,
    a.geo_longitude,
    a.geo_timezone,

    -- ip address
    a.user_ipaddress,

    -- user agent
    a.useragent,

    a.br_renderengine,
    a.br_lang,

    a.os_timezone,

    -- optional fields, only populated if enabled.
    -- iab enrichment fields
    a.category,
    a.primary_impact,
    a.reason,
    a.spider_or_robot,

    -- ua parser enrichment fields
    a.useragent_family,
    a.useragent_major,
    a.useragent_minor,
    a.useragent_patch,
    a.useragent_version,
    a.os_family,
    a.os_major,
    a.os_minor,
    a.os_patch,
    a.os_patch_minor,
    a.os_version,
    a.device_family,

    -- yauaa enrichment fields
    a.device_class,
    a.agent_class,
    a.agent_name,
    a.agent_name_version,
    a.agent_name_version_major,
    a.agent_version,
    a.agent_version_major,
    a.device_brand,
    a.device_name,
    a.device_version,
    a.layout_engine_class,
    a.layout_engine_name,
    a.layout_engine_name_version,
    a.layout_engine_name_version_major,
    a.layout_engine_version,
    a.layout_engine_version_major,
    a.operating_system_class,
    a.operating_system_name,
    a.operating_system_name_version,
    a.operating_system_version
from
    session_firsts a
left join
    session_lasts b on a.domain_sessionid = b.domain_sessionid and b.page_event_in_session_index = 1
left join
    session_aggs c on a.domain_sessionid = c.domain_sessionid
where
    a.page_event_in_session_index = 1

Source

{{
    config(
        tags=["this_run"]
    )
}}

with session_firsts as (
    select
        -- app id
        app_id as app_id,

        -- session fields
        domain_sessionid,
        domain_sessionidx,

        {{ snowplow_utils.current_timestamp_in_utc() }} as model_tstamp,

        -- user fields
        user_id,
        domain_userid,
        {% if var('snowplow__session_stitching') %}
            -- updated with mapping as part of post hook on derived sessions table
            cast(domain_userid as {{ type_string() }}) as stitched_user_id,
        {% else %}
            cast(null as {{ type_string() }}) as stitched_user_id,
        {% endif %}
        network_userid as network_userid,

        -- first page fields
        page_title as first_page_title,
        page_url as first_page_url,
        page_urlscheme as first_page_urlscheme,
        page_urlhost as first_page_urlhost,
        page_urlpath as first_page_urlpath,
        page_urlquery as first_page_urlquery,
        page_urlfragment as first_page_urlfragment,

        -- referrer fields
        page_referrer as referrer,
        refr_urlscheme as refr_urlscheme,
        refr_urlhost as refr_urlhost,
        refr_urlpath as refr_urlpath,
        refr_urlquery as refr_urlquery,
        refr_urlfragment as refr_urlfragment,
        refr_medium as refr_medium,
        refr_source as refr_source,
        refr_term as refr_term,

        -- marketing fields
        mkt_medium as mkt_medium,
        mkt_source as mkt_source,
        mkt_term as mkt_term,
        mkt_content as mkt_content,
        mkt_campaign as mkt_campaign,
        mkt_clickid as mkt_clickid,
        mkt_network as mkt_network,

        -- Most if not all the following fields should be the same across all events in a session, but this ensures they are
        -- geo fields
        geo_country as geo_country,
        geo_region as geo_region,
        geo_region_name as geo_region_name,
        geo_city as geo_city,
        geo_zipcode as geo_zipcode,
        geo_latitude as geo_latitude,
        geo_longitude as geo_longitude,
        geo_timezone as geo_timezone,

        -- ip address
        user_ipaddress as user_ipaddress,

        -- user agent
        useragent as useragent,

        br_renderengine as br_renderengine,
        br_lang as br_lang,
        os_timezone as os_timezone,

        -- optional fields, only populated if enabled.
        -- iab enrichment fields
        {{snowplow_web.get_iab_context_fields()}},

        -- ua parser enrichment fields
        {{snowplow_web.get_ua_context_fields()}},

        -- yauaa enrichment fields
        {{snowplow_web.get_yauaa_context_fields()}},

        event_name
    from {{ ref('snowplow_web_base_events_this_run') }}
    where
        event_name in ('page_ping', 'page_view')
        and page_view_id is not null
        {% if var("snowplow__ua_bot_filter", true) %}
            {{ filter_bots() }}
        {% endif %}
    qualify row_number() over (partition by domain_sessionid order by derived_tstamp, dvce_created_tstamp) = 1
),

session_lasts as (
    select
        domain_sessionid,
        page_title as last_page_title,
        page_url as last_page_url,
        page_urlscheme as last_page_urlscheme,
        page_urlhost as last_page_urlhost,
        page_urlpath as last_page_urlpath,
        page_urlquery as last_page_urlquery,
        page_urlfragment as last_page_urlfragment
    from {{ ref('snowplow_web_base_events_this_run') }}
    where
        event_name in ('page_view')
        and page_view_id is not null
        {% if var("snowplow__ua_bot_filter", true) %}
            {{ filter_bots() }}
        {% endif %}
    qualify row_number() over (partition by domain_sessionid order by derived_tstamp desc, dvce_created_tstamp) = 1
),

session_aggs as (
    select
        domain_sessionid,
        min(derived_tstamp) as start_tstamp,
        max(derived_tstamp) as end_tstamp,
        -- engagement fields
        count(distinct page_view_id) as page_views,
        -- (hb * (#page pings - # distinct page view ids ON page pings)) + (# distinct page view ids ON page pings * min visit length)
        ({{ var("snowplow__heartbeat", 10) }} * (
                -- number of (unqiue in heartbeat increment) pages pings following a page ping (gap of heartbeat)
                count(distinct case
                        when event_name = 'page_ping' then
                        -- need to get a unique list of floored time PER page view, so create a dummy surrogate key...
                            {{ dbt.concat(['page_view_id', "cast(floor("~snowplow_utils.to_unixtstamp('dvce_created_tstamp')~"/"~var('snowplow__heartbeat', 10)~") as "~dbt.type_string()~")" ]) }}
                        else
                            null end) -
                    count(distinct case when event_name = 'page_ping' then page_view_id else null end)
                ))  +
            -- number of page pings following a page view (or no event) (gap of min visit length)
            (count(distinct case when event_name = 'page_ping' then page_view_id else null end) * {{ var("snowplow__min_visit_length", 5) }}) as engaged_time_in_s,
        {{ snowplow_utils.timestamp_diff('min(derived_tstamp)', 'max(derived_tstamp)', 'second') }} as absolute_time_in_s
    from {{ ref('snowplow_web_base_events_this_run') }}
    where
        event_name in ('page_ping', 'page_view')
        and page_view_id is not null
        {% if var("snowplow__ua_bot_filter", true) %}
            {{ filter_bots() }}
        {% endif %}
    group by
        domain_sessionid
)

select
    -- app id
    a.app_id,

    -- session fields
    a.domain_sessionid,
    a.domain_sessionidx,

    -- when the session starts with a ping we need to add the min visit length to get when the session actually started
    case when a.event_name = 'page_ping' then
        {{ snowplow_utils.timestamp_add(datepart="second", interval=-var("snowplow__min_visit_length", 5), tstamp="c.start_tstamp") }}
    else c.start_tstamp end as start_tstamp,
    c.end_tstamp,
    a.model_tstamp,

    -- user fields
    a.user_id,
    a.domain_userid,
    a.stitched_user_id,
    a.network_userid,

    -- engagement fields
    c.page_views,
    c.engaged_time_in_s,
    -- when the session starts with a ping we need to add the min visit length to get when the session actually started
    c.absolute_time_in_s + case when a.event_name = 'page_ping' then {{ var("snowplow__min_visit_length", 5) }} else 0 end as absolute_time_in_s,

    -- first page fields
    a.first_page_title,
    a.first_page_url,
    a.first_page_urlscheme,
    a.first_page_urlhost,
    a.first_page_urlpath,
    a.first_page_urlquery,
    a.first_page_urlfragment,

    -- only take the first value when the last is genuinely missing (base on url as has to always be populated)
    case when b.last_page_url is null then coalesce(b.last_page_title, a.first_page_title) else b.last_page_title end as last_page_title,
    case when b.last_page_url is null then coalesce(b.last_page_url, a.first_page_url) else b.last_page_url end as last_page_url,
    case when b.last_page_url is null then coalesce(b.last_page_urlscheme, a.first_page_urlscheme) else b.last_page_urlscheme end as last_page_urlscheme,
    case when b.last_page_url is null then coalesce(b.last_page_urlhost, a.first_page_urlhost) else b.last_page_urlhost end as last_page_urlhost,
    case when b.last_page_url is null then coalesce(b.last_page_urlpath, a.first_page_urlpath) else b.last_page_urlpath end as last_page_urlpath,
    case when b.last_page_url is null then coalesce(b.last_page_urlquery, a.first_page_urlquery) else b.last_page_urlquery end as last_page_urlquery,
    case when b.last_page_url is null then coalesce(b.last_page_urlfragment, a.first_page_urlfragment) else b.last_page_urlfragment end as last_page_urlfragment,

    -- referrer fields
    a.referrer,
    a.refr_urlscheme,
    a.refr_urlhost,
    a.refr_urlpath,
    a.refr_urlquery,
    a.refr_urlfragment,
    a.refr_medium,
    a.refr_source,
    a.refr_term,

    -- marketing fields
    a.mkt_medium,
    a.mkt_source,
    a.mkt_term,
    a.mkt_content,
    a.mkt_campaign,
    a.mkt_clickid,
    a.mkt_network,

    -- geo fields
    a.geo_country,
    a.geo_region,
    a.geo_region_name,
    a.geo_city,
    a.geo_zipcode,
    a.geo_latitude,
    a.geo_longitude,
    a.geo_timezone,

    -- ip address
    a.user_ipaddress,

    -- user agent
    a.useragent,

    a.br_renderengine,
    a.br_lang,

    a.os_timezone,

    -- optional fields, only populated if enabled.
    -- iab enrichment fields
    a.category,
    a.primary_impact,
    a.reason,
    a.spider_or_robot,

    -- ua parser enrichment fields
    a.useragent_family,
    a.useragent_major,
    a.useragent_minor,
    a.useragent_patch,
    a.useragent_version,
    a.os_family,
    a.os_major,
    a.os_minor,
    a.os_patch,
    a.os_patch_minor,
    a.os_version,
    a.device_family,

    -- yauaa enrichment fields
    a.device_class,
    a.agent_class,
    a.agent_name,
    a.agent_name_version,
    a.agent_name_version_major,
    a.agent_version,
    a.agent_version_major,
    a.device_brand,
    a.device_name,
    a.device_version,
    a.layout_engine_class,
    a.layout_engine_name,
    a.layout_engine_name_version,
    a.layout_engine_name_version_major,
    a.layout_engine_version,
    a.layout_engine_version_major,
    a.operating_system_class,
    a.operating_system_name,
    a.operating_system_name_version,
    a.operating_system_version
from
    session_firsts a
left join
    session_lasts b on a.domain_sessionid = b.domain_sessionid
left join
    session_aggs c on a.domain_sessionid = c.domain_sessionid

Source

{{
    config(
        tags=["this_run"]
    )
}}

with session_firsts as (
    select
        -- app id
        app_id as app_id,

        -- session fields
        domain_sessionid,
        domain_sessionidx,

        {{ snowplow_utils.current_timestamp_in_utc() }} as model_tstamp,

        -- user fields
        user_id,
        domain_userid,
        {% if var('snowplow__session_stitching') %}
            -- updated with mapping as part of post hook on derived sessions table
            cast(domain_userid as {{ type_string() }}) as stitched_user_id,
        {% else %}
            cast(null as {{ type_string() }}) as stitched_user_id,
        {% endif %}
        network_userid as network_userid,

        -- first page fields
        page_title as first_page_title,
        page_url as first_page_url,
        page_urlscheme as first_page_urlscheme,
        page_urlhost as first_page_urlhost,
        page_urlpath as first_page_urlpath,
        page_urlquery as first_page_urlquery,
        page_urlfragment as first_page_urlfragment,

        -- referrer fields
        page_referrer as referrer,
        refr_urlscheme as refr_urlscheme,
        refr_urlhost as refr_urlhost,
        refr_urlpath as refr_urlpath,
        refr_urlquery as refr_urlquery,
        refr_urlfragment as refr_urlfragment,
        refr_medium as refr_medium,
        refr_source as refr_source,
        refr_term as refr_term,

        -- marketing fields
        mkt_medium as mkt_medium,
        mkt_source as mkt_source,
        mkt_term as mkt_term,
        mkt_content as mkt_content,
        mkt_campaign as mkt_campaign,
        mkt_clickid as mkt_clickid,
        mkt_network as mkt_network,

        -- Most if not all the following fields should be the same across all events in a session, but this ensures they are
        -- geo fields
        geo_country as geo_country,
        geo_region as geo_region,
        geo_region_name as geo_region_name,
        geo_city as geo_city,
        geo_zipcode as geo_zipcode,
        geo_latitude as geo_latitude,
        geo_longitude as geo_longitude,
        geo_timezone as geo_timezone,

        -- ip address
        user_ipaddress as user_ipaddress,

        -- user agent
        useragent as useragent,

        br_renderengine as br_renderengine,
        br_lang as br_lang,
        os_timezone as os_timezone,

        -- optional fields, only populated if enabled.
        -- iab enrichment fields: set iab variable to true to enable
        {{snowplow_web.get_iab_context_fields('iab')}},

        -- ua parser enrichment fields
        {{snowplow_web.get_ua_context_fields('ua')}},

        -- yauaa enrichment fields
        {{snowplow_web.get_yauaa_context_fields('ya')}},

        -- the joined tables should all be unique on page_view_id anyway, but this has the benefit of de-duping just in case
        row_number() over (partition by ev.domain_sessionid order by ev.derived_tstamp, ev.dvce_created_tstamp) AS page_event_in_session_index,
        event_name
    from {{ ref('snowplow_web_base_events_this_run') }} ev

    {% if var('snowplow__enable_iab', false) -%}
        left join {{ ref('snowplow_web_pv_iab') }} iab
        on ev.page_view_id = iab.page_view_id
    {% endif -%}

    {% if var('snowplow__enable_ua', false) -%}
        left join {{ ref('snowplow_web_pv_ua_parser') }} ua
        on ev.page_view_id = ua.page_view_id
    {% endif -%}

    {% if var('snowplow__enable_yauaa', false) -%}
        left join {{ ref('snowplow_web_pv_yauaa') }} ya
        on ev.page_view_id = ya.page_view_id
    {% endif -%}

    where
        ev.event_name in ('page_ping', 'page_view')
        and ev.page_view_id is not null
        {% if var("snowplow__ua_bot_filter", true) %}
            {{ filter_bots('ev') }}
        {% endif %}
),

session_lasts as (
    select
        domain_sessionid,
        page_title as last_page_title,
        page_url as last_page_url,
        page_urlscheme as last_page_urlscheme,
        page_urlhost as last_page_urlhost,
        page_urlpath as last_page_urlpath,
        page_urlquery as last_page_urlquery,
        page_urlfragment as last_page_urlfragment,
        row_number() over (partition by ev.domain_sessionid order by ev.derived_tstamp desc, ev.dvce_created_tstamp) AS page_event_in_session_index
    from {{ ref('snowplow_web_base_events_this_run') }} ev
    where
        event_name in ('page_view')
        and page_view_id is not null
        {% if var("snowplow__ua_bot_filter", true) %}
            {{ filter_bots() }}
        {% endif %}
),

session_aggs as (
    select
        domain_sessionid,
        min(derived_tstamp) as start_tstamp,
        max(derived_tstamp) as end_tstamp,
        -- engagement fields
        count(distinct page_view_id) as page_views,
        -- (hb * (#page pings - # distinct page view ids ON page pings)) + (# distinct page view ids ON page pings * min visit length)
        ({{ var("snowplow__heartbeat", 10) }} * (
                -- number of (unqiue in heartbeat increment) pages pings following a page ping (gap of heartbeat)
                count(distinct case
                        when event_name = 'page_ping' then
                        -- need to get a unique list of floored time PER page view, so create a dummy surrogate key...
                            {{ dbt.concat(['page_view_id', "cast(floor("~snowplow_utils.to_unixtstamp('dvce_created_tstamp')~"/"~var('snowplow__heartbeat', 10)~") as "~dbt.type_string()~")" ]) }}
                        else
                            null end) -
                    count(distinct case when event_name = 'page_ping' then page_view_id else null end)
                ))  +
            -- number of page pings following a page view (or no event) (gap of min visit length)
            (count(distinct case when event_name = 'page_ping' then page_view_id else null end) * {{ var("snowplow__min_visit_length", 5) }}) as engaged_time_in_s,
        {{ snowplow_utils.timestamp_diff('min(derived_tstamp)', 'max(derived_tstamp)', 'second') }} as absolute_time_in_s
    from {{ ref('snowplow_web_base_events_this_run') }}
    where
        event_name in ('page_ping', 'page_view')
        and page_view_id is not null
        {% if var("snowplow__ua_bot_filter", true) %}
            {{ filter_bots() }}
        {% endif %}
    group by
        domain_sessionid
)

select
    -- app id
    a.app_id,

    -- session fields
    a.domain_sessionid,
    a.domain_sessionidx,

    -- when the session starts with a ping we need to add the min visit length to get when the session actually started
    case when a.event_name = 'page_ping' then
        {{ snowplow_utils.timestamp_add(datepart="second", interval=-var("snowplow__min_visit_length", 5), tstamp="c.start_tstamp") }}
    else c.start_tstamp end as start_tstamp,
    c.end_tstamp,
    a.model_tstamp,

    -- user fields
    a.user_id,
    a.domain_userid,
    a.stitched_user_id,
    a.network_userid,

    -- engagement fields
    c.page_views,
    c.engaged_time_in_s,
    -- when the session starts with a ping we need to add the min visit length to get when the session actually started
    c.absolute_time_in_s + case when a.event_name = 'page_ping' then {{ var("snowplow__min_visit_length", 5) }} else 0 end as absolute_time_in_s,

    -- first page fields
    a.first_page_title,
    a.first_page_url,
    a.first_page_urlscheme,
    a.first_page_urlhost,
    a.first_page_urlpath,
    a.first_page_urlquery,
    a.first_page_urlfragment,

    -- only take the first value when the last is genuinely missing (base on url as has to always be populated)
    case when b.last_page_url is null then coalesce(b.last_page_title, a.first_page_title) else b.last_page_title end as last_page_title,
    case when b.last_page_url is null then coalesce(b.last_page_url, a.first_page_url) else b.last_page_url end as last_page_url,
    case when b.last_page_url is null then coalesce(b.last_page_urlscheme, a.first_page_urlscheme) else b.last_page_urlscheme end as last_page_urlscheme,
    case when b.last_page_url is null then coalesce(b.last_page_urlhost, a.first_page_urlhost) else b.last_page_urlhost end as last_page_urlhost,
    case when b.last_page_url is null then coalesce(b.last_page_urlpath, a.first_page_urlpath) else b.last_page_urlpath end as last_page_urlpath,
    case when b.last_page_url is null then coalesce(b.last_page_urlquery, a.first_page_urlquery) else b.last_page_urlquery end as last_page_urlquery,
    case when b.last_page_url is null then coalesce(b.last_page_urlfragment, a.first_page_urlfragment) else b.last_page_urlfragment end as last_page_urlfragment,

    -- referrer fields
    a.referrer,
    a.refr_urlscheme,
    a.refr_urlhost,
    a.refr_urlpath,
    a.refr_urlquery,
    a.refr_urlfragment,
    a.refr_medium,
    a.refr_source,
    a.refr_term,

    -- marketing fields
    a.mkt_medium,
    a.mkt_source,
    a.mkt_term,
    a.mkt_content,
    a.mkt_campaign,
    a.mkt_clickid,
    a.mkt_network,

    -- geo fields
    a.geo_country,
    a.geo_region,
    a.geo_region_name,
    a.geo_city,
    a.geo_zipcode,
    a.geo_latitude,
    a.geo_longitude,
    a.geo_timezone,

    -- ip address
    a.user_ipaddress,

    -- user agent
    a.useragent,

    a.br_renderengine,
    a.br_lang,

    a.os_timezone,

    -- optional fields, only populated if enabled.
    -- iab enrichment fields
    a.category,
    a.primary_impact,
    a.reason,
    a.spider_or_robot,

    -- ua parser enrichment fields
    a.useragent_family,
    a.useragent_major,
    a.useragent_minor,
    a.useragent_patch,
    a.useragent_version,
    a.os_family,
    a.os_major,
    a.os_minor,
    a.os_patch,
    a.os_patch_minor,
    a.os_version,
    a.device_family,

    -- yauaa enrichment fields
    a.device_class,
    a.agent_class,
    a.agent_name,
    a.agent_name_version,
    a.agent_name_version_major,
    a.agent_version,
    a.agent_version_major,
    a.device_brand,
    a.device_name,
    a.device_version,
    a.layout_engine_class,
    a.layout_engine_name,
    a.layout_engine_name_version,
    a.layout_engine_name_version_major,
    a.layout_engine_version,
    a.layout_engine_version_major,
    a.operating_system_class,
    a.operating_system_name,
    a.operating_system_name_version,
    a.operating_system_version
from
    session_firsts a
left join
    session_lasts b on a.domain_sessionid = b.domain_sessionid and b.page_event_in_session_index = 1
left join
    session_aggs c on a.domain_sessionid = c.domain_sessionid
where
    a.page_event_in_session_index = 1

Source

{{
    config(
        tags=["this_run"],
        sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
    )
}}

with session_firsts as (
    select
        -- app id
        app_id as app_id,

        -- session fields
        domain_sessionid,
        domain_sessionidx,

        {{ snowplow_utils.current_timestamp_in_utc() }} as model_tstamp,

        -- user fields
        user_id,
        domain_userid,
        {% if var('snowplow__session_stitching') %}
            -- updated with mapping as part of post hook on derived sessions table
            cast(domain_userid as {{ type_string() }}) as stitched_user_id,
        {% else %}
            cast(null as {{ type_string() }}) as stitched_user_id,
        {% endif %}
        network_userid as network_userid,

        -- first page fields
        page_title as first_page_title,
        page_url as first_page_url,
        page_urlscheme as first_page_urlscheme,
        page_urlhost as first_page_urlhost,
        page_urlpath as first_page_urlpath,
        page_urlquery as first_page_urlquery,
        page_urlfragment as first_page_urlfragment,

        -- referrer fields
        page_referrer as referrer,

        refr_urlscheme as refr_urlscheme,
        refr_urlhost as refr_urlhost,
        refr_urlpath as refr_urlpath,
        refr_urlquery as refr_urlquery,
        refr_urlfragment as refr_urlfragment,
        refr_medium as refr_medium,
        refr_source as refr_source,
        refr_term as refr_term,

        -- marketing fields
        mkt_medium as mkt_medium,
        mkt_source as mkt_source,
        mkt_term as mkt_term,
        mkt_content as mkt_content,
        mkt_campaign as mkt_campaign,
        mkt_clickid as mkt_clickid,
        mkt_network as mkt_network,

        -- Most if not all the following fields should be the same across all events in a session, but this ensures they are
        -- geo fields
        geo_country as geo_country,
        geo_region as geo_region,
        geo_region_name as geo_region_name,
        geo_city as geo_city,
        geo_zipcode as geo_zipcode,
        geo_latitude as geo_latitude,
        geo_longitude as geo_longitude,
        geo_timezone as geo_timezone,

        -- ip address
        user_ipaddress as user_ipaddress,

        -- user agent
        useragent as useragent,

        br_renderengine as br_renderengine,
        br_lang as br_lang,
        os_timezone as os_timezone,

        -- optional fields, only populated if enabled.

        -- iab enrichment fields
        {{snowplow_web.get_iab_context_fields()}},

        -- ua parser enrichment fields
        {{snowplow_web.get_ua_context_fields()}},

        -- yauaa enrichment fields
        {{snowplow_web.get_yauaa_context_fields()}},

        -- event name for use later
        event_name
    from {{ ref('snowplow_web_base_events_this_run') }}
    where
        event_name in ('page_ping', 'page_view')
        and page_view_id is not null
        {% if var("snowplow__ua_bot_filter", true) %}
            {{ filter_bots() }}
        {% endif %}
    qualify row_number() over (partition by domain_sessionid order by derived_tstamp, dvce_created_tstamp) = 1
),

session_lasts as (
    select
        domain_sessionid,
        page_title as last_page_title,
        page_url as last_page_url,
        page_urlscheme as last_page_urlscheme,
        page_urlhost as last_page_urlhost,
        page_urlpath as last_page_urlpath,
        page_urlquery as last_page_urlquery,
        page_urlfragment as last_page_urlfragment
    from {{ ref('snowplow_web_base_events_this_run') }}
    where
        event_name = 'page_view'
        and page_view_id is not null
        {% if var("snowplow__ua_bot_filter", true) %}
            {{ filter_bots() }}
        {% endif %}
    qualify row_number() over (partition by domain_sessionid order by derived_tstamp desc, dvce_created_tstamp) = 1
),

session_aggs as (
    select
        domain_sessionid,
        min(derived_tstamp) as start_tstamp,
        max(derived_tstamp) as end_tstamp,
        -- engagement fields
        count(distinct page_view_id) as page_views,
        -- (hb * (#page pings - # distinct page view ids ON page pings)) + (# distinct page view ids ON page pings * min visit length)
        ({{ var("snowplow__heartbeat", 10) }} * (
                -- number of (unqiue in heartbeat increment) pages pings following a page ping (gap of heartbeat)
                count(distinct case
                        when event_name = 'page_ping' then
                        -- need to get a unique list of floored time PER page view, so create a dummy surrogate key...
                            {{ dbt.concat(['page_view_id', "cast(floor("~snowplow_utils.to_unixtstamp('dvce_created_tstamp')~"/"~var('snowplow__heartbeat', 10)~") as "~dbt.type_string()~")" ]) }}
                        else
                            null end) -
                    count(distinct case when event_name = 'page_ping' then page_view_id else null end)
                ))  +
            -- number of page pings following a page view (or no event) (gap of min visit length)
            (count(distinct case when event_name = 'page_ping' then page_view_id else null end) * {{ var("snowplow__min_visit_length", 5) }}) as engaged_time_in_s,
        {{ snowplow_utils.timestamp_diff('min(derived_tstamp)', 'max(derived_tstamp)', 'second') }} as absolute_time_in_s
    from {{ ref('snowplow_web_base_events_this_run') }}
    where
        event_name in ('page_ping', 'page_view')
        and page_view_id is not null
        {% if var("snowplow__ua_bot_filter", true) %}
            {{ filter_bots() }}
        {% endif %}
    group by
        domain_sessionid
)

select
    -- app id
    a.app_id,

    -- session fields
    a.domain_sessionid,
    a.domain_sessionidx,

    -- when the session starts with a ping we need to add the min visit length to get when the session actually started
    case when a.event_name = 'page_ping' then
        {{ snowplow_utils.timestamp_add(datepart="second", interval=-var("snowplow__min_visit_length", 5), tstamp="c.start_tstamp") }}
    else c.start_tstamp end as start_tstamp,
    c.end_tstamp,
    a.model_tstamp,

    -- user fields
    a.user_id,
    a.domain_userid,
    a.stitched_user_id,
    a.network_userid,

    -- engagement fields
    c.page_views,
    c.engaged_time_in_s,
    -- when the session starts with a ping we need to add the min visit length to get when the session actually started
    c.absolute_time_in_s + case when a.event_name = 'page_ping' then {{ var("snowplow__min_visit_length", 5) }} else 0 end as absolute_time_in_s,

    -- first page fields
    a.first_page_title,
    a.first_page_url,
    a.first_page_urlscheme,
    a.first_page_urlhost,
    a.first_page_urlpath,
    a.first_page_urlquery,
    a.first_page_urlfragment,

    -- only take the first value when the last is genuinely missing (base on url as has to always be populated)
    case when b.last_page_url is null then coalesce(b.last_page_title, a.first_page_title) else b.last_page_title end as last_page_title,
    case when b.last_page_url is null then coalesce(b.last_page_url, a.first_page_url) else b.last_page_url end as last_page_url,
    case when b.last_page_url is null then coalesce(b.last_page_urlscheme, a.first_page_urlscheme) else b.last_page_urlscheme end as last_page_urlscheme,
    case when b.last_page_url is null then coalesce(b.last_page_urlhost, a.first_page_urlhost) else b.last_page_urlhost end as last_page_urlhost,
    case when b.last_page_url is null then coalesce(b.last_page_urlpath, a.first_page_urlpath) else b.last_page_urlpath end as last_page_urlpath,
    case when b.last_page_url is null then coalesce(b.last_page_urlquery, a.first_page_urlquery) else b.last_page_urlquery end as last_page_urlquery,
    case when b.last_page_url is null then coalesce(b.last_page_urlfragment, a.first_page_urlfragment) else b.last_page_urlfragment end as last_page_urlfragment,

    -- referrer fields
    a.referrer,
    a.refr_urlscheme,
    a.refr_urlhost,
    a.refr_urlpath,
    a.refr_urlquery,
    a.refr_urlfragment,
    a.refr_medium,
    a.refr_source,
    a.refr_term,

    -- marketing fields
    a.mkt_medium,
    a.mkt_source,
    a.mkt_term,
    a.mkt_content,
    a.mkt_campaign,
    a.mkt_clickid,
    a.mkt_network,

    -- geo fields
    a.geo_country,
    a.geo_region,
    a.geo_region_name,
    a.geo_city,
    a.geo_zipcode,
    a.geo_latitude,
    a.geo_longitude,
    a.geo_timezone,

    -- ip address
    a.user_ipaddress,

    -- user agent
    a.useragent,

    a.br_renderengine,
    a.br_lang,

    a.os_timezone,

    -- optional fields, only populated if enabled.
    -- iab enrichment fields
    a.category,
    a.primary_impact,
    a.reason,
    a.spider_or_robot,

    -- ua parser enrichment fields
    a.useragent_family,
    a.useragent_major,
    a.useragent_minor,
    a.useragent_patch,
    a.useragent_version,
    a.os_family,
    a.os_major,
    a.os_minor,
    a.os_patch,
    a.os_patch_minor,
    a.os_version,
    a.device_family,

    -- yauaa enrichment fields
    a.device_class,
    a.agent_class,
    a.agent_name,
    a.agent_name_version,
    a.agent_name_version_major,
    a.agent_version,
    a.agent_version_major,
    a.device_brand,
    a.device_name,
    a.device_version,
    a.layout_engine_class,
    a.layout_engine_name,
    a.layout_engine_name_version,
    a.layout_engine_name_version_major,
    a.layout_engine_version,
    a.layout_engine_version_major,
    a.operating_system_class,
    a.operating_system_name,
    a.operating_system_name_version,
    a.operating_system_version
from
    session_firsts a
left join
    session_lasts b on a.domain_sessionid = b.domain_sessionid
left join
    session_aggs c on a.domain_sessionid = c.domain_sessionid

Depends On

Models
Macros

model.snowplow_web.snowplow_web_base_events_this_run

Referenced By

Models

model.snowplow_web.snowplow_web_sessions

Snowplow Web User Mapping

models/user_mapping/snowplow_web_user_mapping.sql

Description

A mapping table between domain_userid and user_id.

Type: Table

Details

Columns

Column Name	Description	Type
domain_userid	User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’	text
user_id	Unique ID set by business e.g. ‘jon.doe@email.com’	text
end_tstamp	The `collector_tstamp` when the user was last active	timestamp_ntz

Code

default

Source

{{
  config(
    materialized='incremental',
    unique_key='domain_userid',
    sort='end_tstamp',
    dist='domain_userid',
    partition_by = snowplow_utils.get_value_by_target_type(bigquery_val={
      "field": "end_tstamp",
      "data_type": "timestamp"
    }),
    tags=["derived"],
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
  )
}}


select distinct
  domain_userid,
  last_value(user_id) over(
    partition by domain_userid
    order by collector_tstamp
    rows between unbounded preceding and unbounded following
  ) as user_id,
  max(collector_tstamp) over (partition by domain_userid) as end_tstamp

from {{ ref('snowplow_web_base_events_this_run') }}

where {{ snowplow_utils.is_run_with_new_events('snowplow_web') }} --returns false if run doesn't contain new events.
and user_id is not null
and domain_userid is not null

Depends On

Models
Macros

Referenced By

Models

model.snowplow_web.snowplow_web_sessions

Snowplow Web Users

models/users/snowplow_web_users.sql

Description

This derived incremental table contains all historic users data and should be the end point for any analysis or BI tools.

Type: Table

Details

Columns

Column Name	Description	Type
user_id	Unique ID set by business e.g. ‘jon.doe@email.com’	text
domain_userid	User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’	text
network_userid	User ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’	text
start_tstamp	Timestamp for the start of the users lifecycle, based on `derived_tstamp`	timestamp_ntz
end_tstamp	Timestamp for the last time the user was seen, based on `derived_tstamp`	timestamp_ntz
model_tstamp	The current timestamp when the model processed this row.	timestamp_ntz
page_views	The total page views by the user	number
sessions	The total sessions by the user	number
engaged_time_in_s	The total engaged time in seconds by the user	number
first_page_title	The title of the first page visited by the user	text
first_page_url	The url of the first page visited by the user	text
first_page_urlscheme	The urlscheme of the first page visited by the user	text
first_page_urlhost	The urlhost of the first page visited by the user	text
first_page_urlpath	The urlpath of the first page visited by the user	text
first_page_urlquery	The urlquery of the first page visited by the user	text
first_page_urlfragment	The urlfragment of the first page visited by the user	text
last_page_title	The title of the last page visited by the user	text
last_page_url	The url of the last page visited by the user	text
last_page_urlscheme	The urlscheme of the last page visited by the user	text
last_page_urlhost	The urlhost of the last page visited by the user	text
last_page_urlpath	The urlpath of the last page visited by the user	text
last_page_urlquery	The urlquery of the last page visited by the user	text
last_page_urlfragment	The urlfragment of the last page visited by the user	text
referrer	The referrer associated with the first page view of the user	text
refr_urlscheme	Referer scheme e.g. ‘http’	text
refr_urlhost	Referer host e.g. ‘www.bing.com’	text
refr_urlpath	Referer page path e.g. ‘/images/search’	text
refr_urlquery	Referer URL querystring e.g. ‘q=psychic+oracle+cards’	text
refr_urlfragment	Referer URL fragment	text
refr_medium	Type of referer e.g. ‘search’, ‘internal’	text
refr_source	Name of referer if recognised e.g. ‘Bing images’	text
refr_term	Keywords if source is a search engine e.g. ‘psychic oracle cards’	text
mkt_medium	Type of traffic source e.g. ‘cpc’, ‘affiliate’, ‘organic’, ‘social’	text
mkt_source	The company / website where the traffic came from e.g. ‘Google’, ‘Facebook’	text
mkt_term	Any keywords associated with the referrer e.g. ‘new age tarot decks’	text
mkt_content	The content of the ad. (Or an ID so that it can be looked up.) e.g. 13894723	text
mkt_campaign	The campaign ID e.g. ‘diageo-123’	text
mkt_clickid	The click ID e.g. ‘ac3d8e459’	text
mkt_network	The ad network to which the click ID belongs e.g. ‘DoubleClick’	text

Code

default

Source

{{
  config(
    materialized='incremental',
    unique_key='domain_userid',
    upsert_date_key='start_tstamp',
    disable_upsert_lookback=true,
    sort='start_tstamp',
    dist='domain_userid',
    partition_by = snowplow_utils.get_value_by_target_type(bigquery_val={
      "field": "start_tstamp",
      "data_type": "timestamp"
    }, databricks_val='start_tstamp_date'),
    cluster_by=snowplow_web.web_cluster_by_fields_users(),
    tags=["derived"],
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt')),
    tblproperties={
      'delta.autoOptimize.optimizeWrite' : 'true',
      'delta.autoOptimize.autoCompact' : 'true'
    },
    snowplow_optimize = true
  )
}}

select *
  {% if target.type in ['databricks', 'spark'] -%}
  , DATE(start_tstamp) as start_tstamp_date
  {%- endif %}
from {{ ref('snowplow_web_users_this_run') }}
where {{ snowplow_utils.is_run_with_new_events('snowplow_web') }} --returns false if run doesn't contain new events.

Depends On

Models
Macros

Snowplow Web Users Aggs

models/users/scratch/snowplow_web_users_aggs.sql

Description

This model aggregates various metrics derived from sessions to a users level.

Type: Table

Details

Columns

Column Name	Description	Type
domain_userid	User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’	text
start_tstamp		timestamp_ntz
end_tstamp		timestamp_ntz
first_domain_sessionid		text
last_domain_sessionid		text
page_views		number
sessions		number
engaged_time_in_s		number

Code

default

Source

{{
  config(
    partition_by = snowplow_utils.get_value_by_target_type(bigquery_val={
      "field": "start_tstamp",
      "data_type": "timestamp"
    }),
    cluster_by=snowplow_utils.get_value_by_target_type(bigquery_val=["domain_userid"]),
    sort='domain_userid',
    dist='domain_userid',
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
  )
}}

select
  domain_userid,
  -- time
  user_start_tstamp as start_tstamp,
  user_end_tstamp as end_tstamp,
  -- first/last session. Max to resolve edge case with multiple sessions with the same start/end tstamp
  max(case when start_tstamp = user_start_tstamp then domain_sessionid end) as first_domain_sessionid,
  max(case when end_tstamp = user_end_tstamp then domain_sessionid end) as last_domain_sessionid,
  -- engagement
  sum(page_views) as page_views,
  count(distinct domain_sessionid) as sessions,
  sum(engaged_time_in_s) as engaged_time_in_s

from {{ ref('snowplow_web_users_sessions_this_run') }}

group by 1,2,3

Depends On

Models
Macros

model.snowplow_web.snowplow_web_users_sessions_this_run

Referenced By

Models

Snowplow Web Users Lasts

models/users/scratch/snowplow_web_users_lasts.sql

Description

This model identifies the last page view for a user and returns various dimensions associated with that page view.

Type: Table

Details

Columns

Column Name	Description	Type
domain_userid	User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’	text
last_page_title		text
last_page_url		text
last_page_urlscheme		text
last_page_urlhost		text
last_page_urlpath		text
last_page_urlquery		text
last_page_urlfragment		text

Code

default

Source

{{
  config(
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
  )
}}


select
  a.domain_userid,
  a.last_page_title,

  a.last_page_url,

  a.last_page_urlscheme,
  a.last_page_urlhost,
  a.last_page_urlpath,
  a.last_page_urlquery,
  a.last_page_urlfragment

from {{ ref('snowplow_web_users_sessions_this_run') }} a

inner join {{ ref('snowplow_web_users_aggs') }} b
on a.domain_sessionid = b.last_domain_sessionid

Depends On

Models
Macros

Referenced By

Models

model.snowplow_web.snowplow_web_users_this_run

Snowplow Web Users Sessions This Run

models/users/scratch/snowplow_web_users_sessions_this_run.sql

Description

This model contains all sessions data related to users contained in the given run of the Web model

Type: Table

Details

Columns

Column Name	Description	Type
app_id		text
domain_sessionid	A visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’	text
domain_sessionidx		number
start_tstamp		timestamp_ntz
end_tstamp		timestamp_ntz
model_tstamp		timestamp_ntz
user_id		text
domain_userid		text
stitched_user_id		text
network_userid		text
page_views		number
engaged_time_in_s		number
absolute_time_in_s		number
first_page_title		text
first_page_url		text
first_page_urlscheme		text
first_page_urlhost		text
first_page_urlpath		text
first_page_urlquery		text
first_page_urlfragment		text
last_page_title		text
last_page_url		text
last_page_urlscheme		text
last_page_urlhost		text
last_page_urlpath		text
last_page_urlquery		text
last_page_urlfragment		text
referrer		text
refr_urlscheme		text
refr_urlhost		text
refr_urlpath		text
refr_urlquery		text
refr_urlfragment		text
refr_medium		text
refr_source		text
refr_term		text
mkt_medium		text
mkt_source		text
mkt_term		text
mkt_content		text
mkt_campaign		text
mkt_clickid		text
mkt_network		text
geo_country		text
geo_region		text
geo_region_name		text
geo_city		text
geo_zipcode		text
geo_latitude		float
geo_longitude		float
geo_timezone		text
user_ipaddress		text
useragent		text
br_renderengine		text
br_lang		text
os_timezone		text
category		text
primary_impact		text
reason		text
spider_or_robot		boolean
useragent_family		text
useragent_major		text
useragent_minor		text
useragent_patch		text
useragent_version		text
os_family		text
os_major		text
os_minor		text
os_patch		text
os_patch_minor		text
os_version		text
device_family		text
device_class		text
agent_class		text
agent_name		text
agent_name_version		text
agent_name_version_major		text
agent_version		text
agent_version_major		text
device_brand		text
device_name		text
device_version		text
layout_engine_class		text
layout_engine_name		text
layout_engine_name_version		text
layout_engine_name_version_major		text
layout_engine_version		text
layout_engine_version_major		text
operating_system_class		text
operating_system_name		text
operating_system_name_version		text
operating_system_version		text
user_start_tstamp		timestamp_ntz
user_end_tstamp		timestamp_ntz

Code

default

Source

{{
  config(
    tags=["this_run"],
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
  )
}}

with user_ids_this_run as (
  select
      distinct domain_userid

  from {{ ref('snowplow_web_base_sessions_this_run') }}
  where domain_userid is not null
)

select
  a.*,
  min(a.start_tstamp) over(partition by a.domain_userid) as user_start_tstamp,
  max(a.end_tstamp) over(partition by a.domain_userid) as user_end_tstamp

from {{ var('snowplow__sessions_table') }} a
inner join user_ids_this_run b
on a.domain_userid = b.domain_userid

Depends On

Models
Macros

Referenced By

Models

Snowplow Web Users This Run

models/users/scratch/snowplow_web_users_this_run.sql

Description

This staging table contains all the users for the given run of the Web model. It possess all the same columns as snowplow_web_users. If building a custom module that requires session level data, this is the table you should reference.

Type: Table

Details

Columns

Column Name	Description	Type
user_id	Unique ID set by business e.g. ‘jon.doe@email.com’	text
domain_userid	User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’	text
network_userid	User ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’	text
start_tstamp	Timestamp for the start of the users lifecycle, based on `derived_tstamp`	timestamp_ntz
end_tstamp	Timestamp for the last time the user was seen, based on `derived_tstamp`	timestamp_ntz
model_tstamp	The current timestamp when the model processed this row.	timestamp_ntz
page_views	The total page views by the user	number
sessions	The total sessions by the user	number
engaged_time_in_s	The total engaged time in seconds by the user	number
first_page_title	The title of the first page visited by the user	text
first_page_url	The url of the first page visited by the user	text
first_page_urlscheme	The urlscheme of the first page visited by the user	text
first_page_urlhost	The urlhost of the first page visited by the user	text
first_page_urlpath	The urlpath of the first page visited by the user	text
first_page_urlquery	The urlquery of the first page visited by the user	text
first_page_urlfragment	The urlfragment of the first page visited by the user	text
last_page_title	The title of the last page visited by the user	text
last_page_url	The url of the last page visited by the user	text
last_page_urlscheme	The urlscheme of the last page visited by the user	text
last_page_urlhost	The urlhost of the last page visited by the user	text
last_page_urlpath	The urlpath of the last page visited by the user	text
last_page_urlquery	The urlquery of the last page visited by the user	text
last_page_urlfragment	The urlfragment of the last page visited by the user	text
referrer	The referrer associated with the first page view of the user	text
refr_urlscheme	Referer scheme e.g. ‘http’	text
refr_urlhost	Referer host e.g. ‘www.bing.com’	text
refr_urlpath	Referer page path e.g. ‘/images/search’	text
refr_urlquery	Referer URL querystring e.g. ‘q=psychic+oracle+cards’	text
refr_urlfragment	Referer URL fragment	text
refr_medium	Type of referer e.g. ‘search’, ‘internal’	text
refr_source	Name of referer if recognised e.g. ‘Bing images’	text
refr_term	Keywords if source is a search engine e.g. ‘psychic oracle cards’	text
mkt_medium	Type of traffic source e.g. ‘cpc’, ‘affiliate’, ‘organic’, ‘social’	text
mkt_source	The company / website where the traffic came from e.g. ‘Google’, ‘Facebook’	text
mkt_term	Any keywords associated with the referrer e.g. ‘new age tarot decks’	text
mkt_content	The content of the ad. (Or an ID so that it can be looked up.) e.g. 13894723	text
mkt_campaign	The campaign ID e.g. ‘diageo-123’	text
mkt_clickid	The click ID e.g. ‘ac3d8e459’	text
mkt_network	The ad network to which the click ID belongs e.g. ‘DoubleClick’	text

Code

default

Source

{{
  config(
    tags=["this_run"],
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
  )
}}

select
  -- user fields
  a.user_id,
  a.domain_userid,
  a.network_userid,

  b.start_tstamp,
  b.end_tstamp,
  {{ snowplow_utils.current_timestamp_in_utc() }} as model_tstamp,

  -- engagement fields
  b.page_views,
  b.sessions,

  b.engaged_time_in_s,

  -- first page fields
  a.first_page_title,

  a.first_page_url,

  a.first_page_urlscheme,
  a.first_page_urlhost,
  a.first_page_urlpath,
  a.first_page_urlquery,
  a.first_page_urlfragment,

  c.last_page_title,

  c.last_page_url,

  c.last_page_urlscheme,
  c.last_page_urlhost,
  c.last_page_urlpath,
  c.last_page_urlquery,
  c.last_page_urlfragment,

  -- referrer fields
  a.referrer,

  a.refr_urlscheme,
  a.refr_urlhost,
  a.refr_urlpath,
  a.refr_urlquery,
  a.refr_urlfragment,

  a.refr_medium,
  a.refr_source,
  a.refr_term,

  -- marketing fields
  a.mkt_medium,
  a.mkt_source,
  a.mkt_term,
  a.mkt_content,
  a.mkt_campaign,
  a.mkt_clickid,
  a.mkt_network

from {{ ref('snowplow_web_users_aggs') }} as b

inner join {{ ref('snowplow_web_users_sessions_this_run') }} as a
on a.domain_sessionid = b.first_domain_sessionid

inner join {{ ref('snowplow_web_users_lasts') }} c
on b.domain_userid = c.domain_userid

Depends On

Models
Macros

Referenced By

Models

model.snowplow_web.snowplow_web_users

Snowplow Web​

Snowplow Web Base Events This Run​

Description

File Paths

Details

Depends On

Referenced By

Snowplow Web Base New Event Limits​

Description

Details

Depends On

Referenced By

Snowplow Web Base Quarantined Sessions​

Description

Details

Depends On

Referenced By

Snowplow Web Base Sessions Lifecycle Manifest​

Description

Details

Depends On

Referenced By

Snowplow Web Base Sessions This Run​

Description

Details

Depends On

Referenced By

Snowplow Web Consent Cmp Stats​

Description

Details

Depends On

Snowplow Web Consent Events This Run​

Description

File Paths

Details

Depends On

Referenced By

Snowplow Web Consent Log​

Description

Details

Depends On

Referenced By

Snowplow Web Consent Scope Status​

Description

Details

Depends On

Snowplow Web Consent Totals​

Description

Details

Depends On

Snowplow Web Consent Users​

Description

Details

Depends On

Referenced By

Snowplow Web Consent Versions​

Description

Details

Depends On

Referenced By

Snowplow Web Incremental Manifest​

Description

Details

Depends On

Referenced By

Snowplow Web Page View Events​

Description

File Paths

Details

Depends On

Snowplow Web Page Views​

Description

Details

Depends On

Snowplow Web Page Views This Run​

Description

File Paths

Details

Depends On

Referenced By

Snowplow Web

Snowplow Web Base Events This Run

Snowplow Web Base New Event Limits

Snowplow Web Base Quarantined Sessions

Snowplow Web Base Sessions Lifecycle Manifest

Snowplow Web Base Sessions This Run

Snowplow Web Consent Cmp Stats

Snowplow Web Consent Events This Run

Snowplow Web Consent Log

Snowplow Web Consent Scope Status

Snowplow Web Consent Totals

Snowplow Web Consent Users

Snowplow Web Consent Versions

Snowplow Web Incremental Manifest

Snowplow Web Page View Events

Snowplow Web Page Views

Snowplow Web Page Views This Run

Snowplow Web Pv Engaged Time

Snowplow Web Pv Iab

Snowplow Web Pv Limits

Snowplow Web Pv Scroll Depth

Snowplow Web Pv Ua Parser

Snowplow Web Pv Yauaa

Snowplow Web Sessions

Snowplow Web Sessions This Run

Snowplow Web User Mapping

Snowplow Web Users

Snowplow Web Users Aggs

Snowplow Web Users Lasts

Snowplow Web Users Sessions This Run

Snowplow Web Users This Run