Skip to main content

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

models/base/scratch/default/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 NameDescription
app_idApplication 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.
platformPlatform e.g. ‘web’
etl_tstampTimestamp event began ETL e.g. ‘2017-01-26 00:01:25.292’
collector_tstampTime stamp for the event recorded by the collector e.g. ‘2013-11-26 00:02:05’
dvce_created_tstampTimestamp event was recorded on the client device e.g. ‘2013-11-26 00:03:57.885’
eventThe type of event recorded e.g. ‘page_view’
event_idA UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’
txn_idTransaction ID set client-side, used to de-dupe records e.g. 421828
name_trackerTracker namespace e.g. ‘sp1’
v_trackerTracker version e.g. ‘js-3.0.0’
v_collectorCollector version e.g. ‘ssc-2.1.0-kinesis’
v_etlETL version e.g. ‘snowplow-micro-1.1.0-common-1.4.2’
user_idUnique ID set by business e.g. ‘jon.doe@email.com’
user_ipaddressUser IP address e.g. ‘92.231.54.234’
user_fingerprintA user fingerprint generated by looking at the individual browser features e.g. 2161814971
domain_useridUser identifier specified in your project variables. By default this is the true domain_userid, a user ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’
domain_sessionidxA visit / session index e.g. 3
network_useridUser ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’
geo_countryISO 3166-1 code for the country the visitor is located in e.g. ‘GB’, ‘US’
geo_regionISO-3166-2 code for country region the visitor is in e.g. ‘I9’, ‘TX’
geo_cityCity the visitor is in e.g. ‘New York’, ‘London’
geo_zipcodePostcode the visitor is in e.g. ‘94109’
geo_latitudeVisitor location latitude e.g. 37.443604
geo_longitudeVisitor location longitude e.g. -122.4124
geo_region_nameVisitor region name e.g. ‘Florida’
ip_ispVisitor’s ISP e.g. ‘FDN Communications’
ip_organizationOrganization associated with the visitor’s IP address – defaults to ISP name if none is found e.g. ‘Bouygues Telecom’
ip_domainSecond level domain name associated with the visitor’s IP address e.g. ‘nuvox.net’
ip_netspeedVisitor’s connection type e.g. ‘Cable/DSL’
page_urlThe page URL e.g. ‘http://www.example.com’
page_titleWeb page title e.g. ‘Snowplow Docs – Understanding the structure of Snowplow data’
page_referrerURL of the referrer e.g. ‘http://www.referrer.com’
page_urlschemeScheme aka protocol e.g. ‘https’
page_urlhostHost aka domain e.g. ‘“www.snowplow.io’
page_urlportPort if specified, 80 if not 80
page_urlpathPath to page e.g. ‘/product/index.html’
page_urlqueryQuerystring e.g. ‘id=GTM-DLRG’
page_urlfragmentFragment aka anchor e.g. ‘4-conclusion’
refr_urlschemeReferer scheme e.g. ‘http’
refr_urlhostReferer host e.g. ‘www.bing.com’
refr_urlportReferer port e.g. 80
refr_urlpathReferer page path e.g. ‘/images/search’
refr_urlqueryReferer URL querystring e.g. ‘q=psychic+oracle+cards’
refr_urlfragmentReferer URL fragment
refr_mediumType of referer e.g. ‘search’, ‘internal’
refr_sourceName of referer if recognised e.g. ‘Bing images’
refr_termKeywords if source is a search engine e.g. ‘psychic oracle cards’
mkt_mediumType of traffic source e.g. ‘cpc’, ‘affiliate’, ‘organic’, ‘social’
mkt_sourceThe company / website where the traffic came from e.g. ‘Google’, ‘Facebook’
mkt_termAny keywords associated with the referrer e.g. ‘new age tarot decks’
mkt_contentThe content of the ad. (Or an ID so that it can be looked up.) e.g. 13894723
mkt_campaignThe campaign ID e.g. ‘diageo-123’
se_categoryCategory of event e.g. ‘ecomm’, ‘video’
se_actionAction performed / event name e.g. ‘add-to-basket’, ‘play-video’
se_labelThe object of the action e.g. the ID of the video played or SKU of the product added-to-basket e.g. ‘pbz00123’
se_propertyA property associated with the object of the action e.g. ‘HD’, ‘large’
se_valueA value associated with the event / action e.g. the value of goods added-to-basket e.g. 9.99
tr_orderidOrder ID e.g. ‘#134’
tr_affiliationTransaction affiliation (e.g. store where sale took place) e.g. ‘web’
tr_totalTotal transaction value e.g. 12.99
tr_taxTotal tax included in transaction value e.g. 3.00
tr_shippingDelivery cost charged e.g. 0.00
tr_cityDelivery address, city e.g. ‘London’
tr_stateDelivery address, state e.g. ‘Washington’
tr_countryDelivery address, country e.g. ‘France’
ti_orderidOrder ID e.g. ‘#134’
ti_skuProduct SKU e.g. ‘pbz00123’
ti_nameProduct name e.g. ‘Cone pendulum’
ti_categoryProduct category e.g. ‘New Age’
ti_priceProduct unit price e.g. 9.99
ti_quantityNumber of product in transaction e.g. 2
pp_xoffset_minMinimum page x offset seen in the last ping period e.g. 0
pp_xoffset_maxMaximum page x offset seen in the last ping period e.g. 100
pp_yoffset_minMinimum page y offset seen in the last ping period e.g. 0
pp_yoffset_maxMaximum page y offset seen in the last ping period e.g. 200
useragentRaw useragent
br_nameBrowser name e.g. ‘Firefox 12’
br_familyBrowser family e.g. ‘Firefox’
br_versionBrowser version e.g. ‘12.0’
br_typeBrowser type e.g. ‘Browser’
br_renderengineBrowser rendering engine e.g. ‘GECKO’
br_langLanguage the browser is set to e.g. ‘en-GB’
br_features_pdfWhether the browser recognizes PDFs e.g. True
br_features_flashWhether Flash is installed e.g. True
br_features_javaWhether Java is installed e.g. True
br_features_directorWhether Adobe Shockwave is installed e.g. True
br_features_quicktimeWhether QuickTime is installed e.g. True
br_features_realplayerWhether RealPlayer is installed e.g. True
br_features_windowsmediaWhether mplayer2 is installed e.g. True
br_features_gearsWhether Google Gears is installed e.g. True
br_features_silverlightWhether Microsoft Silverlight is installed e.g. True
br_cookiesWhether cookies are enabled e.g. True
br_colordepthBit depth of the browser color palette e.g. 24
br_viewwidthViewport width e.g. 1000
br_viewheightViewport height e.g. 1000
os_nameName of operating system e.g. ‘Android’
os_familyOperating system family e.g. ‘Linux’
os_manufacturerCompany responsible for OS e.g. ‘Apple’
os_timezoneClient operating system timezone e.g. ‘Europe/London’
dvce_typeType of device e.g. ‘Computer’
dvce_ismobileIs the device mobile? e.g. True
dvce_screenwidthScreen width in pixels e.g. 1900
dvce_screenheightScreen height in pixels e.g. 1024
doc_charsetThe page’s character encoding e.g. , ‘UTF-8’
doc_widthThe page’s width in pixels e.g. 1024
doc_heightThe page’s height in pixels e.g. 3000
tr_currencyCurrency e.g. ‘USD’
tr_total_baseTotal in base currency e.g. 12.99
tr_tax_baseTotal tax in base currency e.g. 3.00
tr_shipping_basedecimal Delivery cost in base currency e.g. 0.00
ti_currencyCurrency e.g. ‘EUR’
ti_price_basedecimal Price in base currency e.g. 9.99
base_currencyReporting currency e.g. ‘GBP’
geo_timezoneVisitor timezone name e.g. ‘Europe/London’
mkt_clickidThe click ID e.g. ‘ac3d8e459’
mkt_networkThe ad network to which the click ID belongs e.g. ‘DoubleClick’
etl_tagsJSON of tags for this ETL run e.g. “[‘prod’]”
dvce_sent_tstampWhen the event was sent by the client device e.g. ‘2013-11-26 00:03:58.032’
refr_domain_useridThe Snowplow domain_userid of the referring website e.g. ‘bc2e92ec6c204a14’
refr_dvce_tstampThe time of attaching the domain_userid to the inbound link e.g. ‘2013-11-26 00:02:05’
domain_sessionidSession identifier specified in your project variables. By default this is the true domain_sessionid i.e. a visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’
derived_tstampTimestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’
event_vendorWho defined the event e.g. ‘com.acme’
event_nameEvent name e.g. ‘link_click’
event_formatFormat for event e.g. ‘jsonschema’
event_versionVersion of event schema e.g. ‘1-0-2’
event_fingerprintHash client-set event fields e.g. AADCE520E20C2899F4CED228A79A3083
true_tstampUser-set “true timestamp” for the event e.g. ‘2013-11-26 00:02:04’
page_view_idA UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’
original_domain_sessionidTrue domain_sessionid i.e. a visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’
original_domain_useridTrue User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’
Code
Source
{#
Copyright (c) 2020-present Snowplow Analytics Ltd. All rights reserved.
This program is licensed to you under the Snowplow Community License Version 1.0,
and you may not use this file except in compliance with the Snowplow Community License Version 1.0.
You may obtain a copy of the Snowplow Community License Version 1.0 at https://docs.snowplow.io/community-license-1.0
#}

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

{# dbt passed variables by reference so need to use copy to avoid altering the list multiple times #}
{% set contexts = var('snowplow__entities_or_sdes', []).copy() %}

{% do contexts.append({'name': var('snowplow__page_view_context'), 'prefix': 'page_view', 'single_entity': True}) %}

{% if var('snowplow__enable_iab', false) -%}
  {% do contexts.append({'name': var('snowplow__iab_context'), 'prefix': 'iab', 'single_entity': True}) %}
{% endif -%}

{% if var('snowplow__enable_ua', false) -%}
  {% do contexts.append({'name': var('snowplow__ua_parser_context'), 'prefix': 'ua', 'single_entity': True}) %}
{% endif -%}

{% if var('snowplow__enable_yauaa', false) -%}
  {% do contexts.append({'name': var('snowplow__yauaa_context'), 'prefix': 'yauaa', 'single_entity': True}) %}
{% endif -%}


{% if var('snowplow__enable_consent', false) -%}
  {% do contexts.append({'name': var('snowplow__consent_cmp_visible'), 'prefix': 'cmp_visible', 'single_entity': True}) %}
  {% do contexts.append({'name': var('snowplow__consent_preferences'), 'prefix': 'consent_pref', 'single_entity': True}) %}
{% endif -%}

{% if var('snowplow__enable_cwv', false) -%}
  {% do contexts.append({'name': var('snowplow__cwv_context'), 'prefix': 'cwv', 'single_entity': True}) %}
{% endif -%}

{% set base_events_query = snowplow_utils.base_create_snowplow_events_this_run(
    sessions_this_run_table='snowplow_web_base_sessions_this_run',
    session_identifiers=var('snowplow__session_identifiers', [{"schema" : "atomic", "field" : "domain_sessionid"}]),
    session_sql=var('snowplow__session_sql', none),
    session_timestamp=var('snowplow__session_timestamp', 'collector_tstamp'),
    derived_tstamp_partitioned=var('snowplow__derived_tstamp_partitioned', true),
    days_late_allowed=var('snowplow__days_late_allowed', 3),
    max_session_days=var('snowplow__max_session_days', 3),
    app_ids=var('snowplow__app_ids', []),
    snowplow_events_database=var('snowplow__database', target.database) if target.type not in ['databricks', 'spark'] else var('snowplow__databricks_catalog', 'hive_metastore') if target.type in ['databricks'] else var('snowplow__atomic_schema', 'atomic'),
    snowplow_events_schema=var('snowplow__atomic_schema', 'atomic'),
    snowplow_events_table=var('snowplow__events_table', 'events'),
    entities_or_sdes=contexts) %}


with base_query as (
  {{ base_events_query }}
)

{% set base_query_cols = get_column_schema_from_query( 'select * from (' + base_events_query +') a') %}

select
  {% for col in base_query_cols | map(attribute='name') | list -%}
    {% if col == 'session_identifier' -%}
      a.session_identifier as domain_sessionid
    {%- elif col == 'domain_sessionid' -%}
      a.domain_sessionid as original_domain_sessionid
    {%- elif col == 'user_identifier' -%}
      a.user_identifier as domain_userid
    {%- elif col == 'domain_userid' -%}
      a.domain_userid as original_domain_userid
    {%- else -%}
      a.{{col}}
    {%- endif -%}
    {%- if not loop.last -%},{%- endif %}
  {% endfor %}

from base_query a

Depends On

Referenced By

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 NameDescriptionType
lower_limitThe lower collector_tstamp limit for the runtimestamp_ntz
upper_limitThe upper collector_tstamp limit for the runtimestamp_ntz
Code
Source
{#
Copyright (c) 2020-present Snowplow Analytics Ltd. All rights reserved.
This program is licensed to you under the Snowplow Community License Version 1.0,
and you may not use this file except in compliance with the Snowplow Community License Version 1.0.
You may obtain a copy of the Snowplow Community License Version 1.0 at https://docs.snowplow.io/community-license-1.0
#}

{{ config(
   post_hook=["{{snowplow_utils.print_run_limits(this, 'snowplow_web')}}"],
   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

Referenced By

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 NameDescriptionType
session_identifierThe session_identifier of the quarantined sessiontext
Code
Source
{#
Copyright (c) 2020-present Snowplow Analytics Ltd. All rights reserved.
This program is licensed to you under the Snowplow Community License Version 1.0,
and you may not use this file except in compliance with the Snowplow Community License Version 1.0.
You may obtain a copy of the Snowplow Community License Version 1.0 at https://docs.snowplow.io/community-license-1.0
#}

{{
  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'
    }
  )
}}

{% set quarantined_query = snowplow_utils.base_create_snowplow_quarantined_sessions() %}

{{ quarantined_query }}

Depends On

Referenced By

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 NameDescriptionType
session_identifierThe session identifier as defined in your project variables. Default to domain_sessionid.text
user_identifierThe user identifier as defined in your project variables. Default to domain_userid.text
start_tstampThe collector_tstamp when the session begantimestamp_ntz
end_tstampThe collector_tstamp when the session endedtimestamp_ntz
Code
Source
{#
Copyright (c) 2020-present Snowplow Analytics Ltd. All rights reserved.
This program is licensed to you under the Snowplow Community License Version 1.0,
and you may not use this file except in compliance with the Snowplow Community License Version 1.0.
You may obtain a copy of the Snowplow Community License Version 1.0 at https://docs.snowplow.io/community-license-1.0
#}

{{
  config(
    materialized='incremental',
    unique_key='session_identifier',
    upsert_date_key='start_tstamp',
    sort='start_tstamp',
    dist='session_identifier',
    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
  )
}}

{% set sessions_lifecycle_manifest_query = snowplow_utils.base_create_snowplow_sessions_lifecycle_manifest(
    session_identifiers=var('snowplow__session_identifiers', [{"schema" : "atomic", "field" : "domain_sessionid"}]),
    session_sql=var('snowplow__session_sql', none),
    session_timestamp=var('snowplow__session_timestamp', 'collector_tstamp'),
    user_identifiers=var('snowplow__user_identifiers', [{"schema": "atomic", "field" : "domain_userid"}]),
    user_sql=var('snowplow__user_sql', none),
    quarantined_sessions='snowplow_web_base_quarantined_sessions',
    derived_tstamp_partitioned=var('snowplow__derived_tstamp_partitioned', true),
    days_late_allowed=var('snowplow__days_late_allowed', 3),
    max_session_days=var('snowplow__max_session_days', 3),
    app_ids=var('snowplow__app_ids', []),
    snowplow_events_database=var('snowplow__database', target.database) if target.type not in ['databricks', 'spark'] else var('snowplow__databricks_catalog', 'hive_metastore') if target.type in ['databricks'] else var('snowplow__atomic_schema', 'atomic'),
    snowplow_events_schema=var('snowplow__atomic_schema', 'atomic'),
    snowplow_events_table=var('snowplow__events_table', 'events'),
    event_limits_table='snowplow_web_base_new_event_limits',
    incremental_manifest_table='snowplow_web_incremental_manifest'
 ) %}

{{ sessions_lifecycle_manifest_query }}

Depends On

Referenced By

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 NameDescriptionType
session_identifierThe session identifier as defined in your project variables. Default to domain_sessionid.text
user_identifierThe user identifier as defined in your project variables. Default to domain_userid.text
start_tstampThe collector_tstamp when the session begantimestamp_ntz
end_tstampThe collector_tstamp when the session endedtimestamp_ntz
Code
Source
{#
Copyright (c) 2020-present Snowplow Analytics Ltd. All rights reserved.
This program is licensed to you under the Snowplow Community License Version 1.0,
and you may not use this file except in compliance with the Snowplow Community License Version 1.0.
You may obtain a copy of the Snowplow Community License Version 1.0 at https://docs.snowplow.io/community-license-1.0
#}

{{
    config(
      tags=["this_run"],
      post_hook=["{{ snowplow_utils.base_quarantine_sessions(var('snowplow__max_session_days', 3), var('snowplow__quarantined_sessions', 'snowplow_web_base_quarantined_sessions')) }}"],
      sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
    )
}}


{% set sessions_query = snowplow_utils.base_create_snowplow_sessions_this_run(
    lifecycle_manifest_table='snowplow_web_base_sessions_lifecycle_manifest',
    new_event_limits_table='snowplow_web_base_new_event_limits') %}

{{ sessions_query }}

Depends On

Referenced By

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 NameDescriptionType
event_idA UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_useridThe optional userid of a usertext
original_domain_useridtext
page_view_idA UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_sessionidSession identifier specified in your project variables. By default this is the true domain_sessionid i.e. a visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
original_domain_sessionidtext
cmp_load_timeThe time taken for the consent box to be shown to the screenfloat
cmp_tstampThe timestamp of the cmp_visible eventtimestamp_ntz
first_consent_event_tstampThe timestamp of the first consent event after a cmp_visible eventtimestamp_ntz
first_consent_event_typeThe event type of the first consent event after a cmp_visible eventtext
cmp_interaction_timeThe time it takes for the user to make a consent choice after the cmp_visible event is firednumber
Code
Source
{#
Copyright (c) 2020-present Snowplow Analytics Ltd. All rights reserved.
This program is licensed to you under the Snowplow Community License Version 1.0,
and you may not use this file except in compliance with the Snowplow Community License Version 1.0.
You may obtain a copy of the Snowplow Community License Version 1.0 at https://docs.snowplow.io/community-license-1.0
#}

{{
  config(
    materialized='table',
    enabled=var("snowplow__enable_consent", false),
    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,
    original_domain_userid,
    page_view_id,
    domain_sessionid,
    original_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,
    original_domain_userid,
    page_view_id,
    domain_sessionid,
    original_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,
    original_domain_userid,
    page_view_id,
    domain_sessionid,
    original_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,
    original_domain_userid,
    page_view_id,
    domain_sessionid,
    original_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.original_domain_userid,
  e.page_view_id,
  e.domain_sessionid,
  e.original_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/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

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

Details

Columns
Column NameDescriptionType
event_idtext
domain_useridtext
original_domain_useridtext
user_idtext
geo_countrytext
page_view_idtext
domain_sessionidtext
original_domain_sessionidtext
derived_tstamptimestamp_ntz
load_tstamptimestamp_ntz
event_nametext
event_typetext
basis_for_processingtext
consent_urltext
consent_versiontext
consent_scopestext
domains_appliedtext
gdpr_appliesboolean
cmp_load_timefloat
Code
Source
{#
Copyright (c) 2020-present Snowplow Analytics Ltd. All rights reserved.
This program is licensed to you under the Snowplow Community License Version 1.0,
and you may not use this file except in compliance with the Snowplow Community License Version 1.0.
You may obtain a copy of the Snowplow Community License Version 1.0 at https://docs.snowplow.io/community-license-1.0
#}

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

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

  select
    e.event_id,
    e.domain_userid,
    e.original_domain_userid,
    e.user_id,
    e.geo_country,
    e.page_view_id,
    e.domain_sessionid,
    e.original_domain_sessionid,
    e.derived_tstamp,
    e.load_tstamp,
    e.event_name,
    e.consent_pref_event_type as event_type,
    e.consent_pref_basis_for_processing as basis_for_processing,
    e.consent_pref_consent_url as consent_url,
    e.consent_pref_consent_version as consent_version,
    replace(translate(e.consent_pref_consent_scopes, '"[]', ''), ',', ', ') as consent_scopes,
    replace(translate(e.consent_pref_domains_applied, '"[]', ''), ',', ', ') as domains_applied,
    coalesce(e.consent_pref_gdpr_applies, false) as gdpr_applies,
    e.cmp_visible_elapsed_time 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.

  {% if var("snowplow__ua_bot_filter", false) %}
      {{ filter_bots() }}
  {% endif %}

Depends On

Referenced By

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 NameDescriptionType
event_idA UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_useridUser identifier specified in your project variables. By default this is the true domain_userid, a user ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
original_domain_useridTrue User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
user_idUnique ID set by business e.g. ‘jon.doe@email.com’text
geo_countryISO 3166-1 code for the country the visitor is located in e.g. ‘GB’, ‘US’text
page_view_idA UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_sessionidSession identifier specified in your project variables. By default this is the true domain_sessionid i.e. a visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
original_domain_sessionidTrue domain_sessionid i.e. a visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
derived_tstampTimestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’timestamp_ntz
load_tstampThe timestamp of the event landing the data warehouse.timestamp_ntz
event_nameEvent name e.g. ‘link_click’text
event_typeThe action for the consent preferences of a user E.g allow_alltext
basis_for_processingGDPR lawful basis for data collection & processingtext
consent_urlURI of the privacy policy related documenttext
consent_versionVersion of the privacy policy related documenttext
consent_scopesThe scopes allowed after the user finalized his selection of consent preferences Eg ['analytics', 'functional', 'advertisement']text
domains_appliedThe domains for which this consent allows these preferences to persist totext
gdpr_appliesA boolean which determines if GDPR applies based on the user's geo-locationboolean
cmp_load_timeThe time taken for the consent box to be shown to the screenfloat
Code
Source
{#
Copyright (c) 2020-present Snowplow Analytics Ltd. All rights reserved.
This program is licensed to you under the Snowplow Community License Version 1.0,
and you may not use this file except in compliance with the Snowplow Community License Version 1.0.
You may obtain a copy of the Snowplow Community License Version 1.0 at https://docs.snowplow.io/community-license-1.0
#}

{{
  config(
    materialized= 'incremental',
    enabled=var("snowplow__enable_consent", false),
    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

Referenced By

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 NameDescriptionType
scopeConsent scopetext
total_consentThe number of consent events corresponding to a scopenumber
Code
Source
{#
Copyright (c) 2020-present Snowplow Analytics Ltd. All rights reserved.
This program is licensed to you under the Snowplow Community License Version 1.0,
and you may not use this file except in compliance with the Snowplow Community License Version 1.0.
You may obtain a copy of the Snowplow Community License Version 1.0 at https://docs.snowplow.io/community-license-1.0
#}

{{
  config(
    materialized='table',
    enabled=var("snowplow__enable_consent", false)
  )
}}

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 {{ snowplow_utils.type_max_string() }}), '"', ''), '[', ''), ']', '') as scope,
  count(*) as total_consent

from unnesting

group by 1

Depends On

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 NameDescriptionType
consent_versionVersion of the privacy policy related documenttext
version_start_tstampThe first allow_all consent event belonging to a consent versiontimestamp_ntz
consent_scopesThe scopes allowed after the user finalized his selection of consent preferences Eg ['analytics', 'functional', 'advertisement']text
consent_urlURI of the privacy policy related documenttext
domains_appliedThe domains for which this consent allows these preferences to persist totext
is_latest_versionA boolean to filter whether the last consent or cmp visible event is sent after the latest privacy policy version goes liveboolean
last_allow_all_eventThe timestamp of the last allow_all consent event generated by the latest consent versiontimestamp_ntz
total_visitorsThe number of visitors who have visited since the last consent version is livenumber
allow_allTotal number of users whose last consent event sent from the latest consent version has type allow_allnumber
allow_selectedTotal number of users whose last consent event sent from the latest consent version has type allow_selectednumber
allowTotal number of users whose last consent event sent from the latest consent version has type allownumber
pendingTotal number of users whose last consent event sent from the latest consent version has type pendingnumber
deniedTotal number of users whose last consent event sent from the latest consent version has type deniednumber
expiredTotal number of users whose last consent event sent from the latest consent version has type expirednumber
withdrawnTotal number of users whose last consent event sent from the latest consent version has type withdrawnnumber
implicit_consentnumber
expires_in_six_monthsThe total number of users whose consent expires in six months (only the offical version is taken into account)number
Code
Source
{#
Copyright (c) 2020-present Snowplow Analytics Ltd. All rights reserved.
This program is licensed to you under the Snowplow Community License Version 1.0,
and you may not use this file except in compliance with the Snowplow Community License Version 1.0.
You may obtain a copy of the Snowplow Community License Version 1.0 at https://docs.snowplow.io/community-license-1.0
#}

{{
  config(
    materialized='table',
    enabled=var("snowplow__enable_consent", false),
    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/optional_modules/consent/snowplow_web_consent_users.sql

Description

By user consent stats

Type: Table

Details

Columns
Column NameDescriptionType
domain_useridUser identifier specified in your project variables. By default this is the true domain_userid, a user ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
user_idUnique ID set by business e.g. ‘jon.doe@email.com’text
geo_countryISO 3166-1 code for the country the visitor is located in e.g. ‘GB’, ‘US’text
cmp_eventsThe number of cmp_visible events the user has generatednumber
consent_eventsThe number of cosent events the user has generatednumber
last_cmp_event_tstampThe timestamp of the last cmp_visible eventtimestamp_ntz
last_consent_event_tstampThe timestamp of the last consent event after the cmp_visible event happenedtimestamp_ntz
last_consent_event_typeThe type of the last consent event after the cmp_visible event happenedtext
last_consent_scopesThe list of consent scopes in connection with the last consent eventtext
last_consent_versionThe privacy policy version in connection with the last consent eventtext
last_consent_urlThe privacy policy url in connection with the last consent eventtext
last_domains_appliedThe domains for which the last consent event appliestext
last_processed_eventThe timestamp of the last processed event needed for the incremental logictimestamp_ntz
is_latest_versionA boolean to filter whether the last consent or cmp visible event is sent after the latest privacy policy version goes liveboolean
Code
Source
{#
Copyright (c) 2020-present Snowplow Analytics Ltd. All rights reserved.
This program is licensed to you under the Snowplow Community License Version 1.0,
and you may not use this file except in compliance with the Snowplow Community License Version 1.0.
You may obtain a copy of the Snowplow Community License Version 1.0 at https://docs.snowplow.io/community-license-1.0
#}

{{
  config(
    materialized='incremental',
    enabled=var("snowplow__enable_consent", false),
    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

Referenced By

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 NameDescriptionType
consent_versionVersion of the privacy policy related documenttext
version_start_tstampThe time_stamp of the first allow_all event related to a consent versiontimestamp_ntz
consent_scopesThe scopes allowed after the user finalized his selection of consent preferences Eg ['analytics', 'functional', 'advertisement']text
consent_urlURI of the privacy policy related documenttext
domains_appliedThe domains for which this consent allows these preferences to persist totext
is_latest_versionA boolean to filter whether the last consent or cmp visible event is sent after the latest privacy policy version goes liveboolean
last_allow_all_eventThe timestamp of the last allow_all event used for the incremental updatetimestamp_ntz
Code
Source
{#
Copyright (c) 2020-present Snowplow Analytics Ltd. All rights reserved.
This program is licensed to you under the Snowplow Community License Version 1.0,
and you may not use this file except in compliance with the Snowplow Community License Version 1.0.
You may obtain a copy of the Snowplow Community License Version 1.0 at https://docs.snowplow.io/community-license-1.0
#}

{{
  config(
    materialized='incremental',
    enabled=var("snowplow__enable_consent", false),
    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

Referenced By

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 NameDescriptionType
modelThe name of the model.text
last_successThe latest event consumed by the model, based on collector_tstamptimestamp_ntz
Code
Source
{#
Copyright (c) 2020-present Snowplow Analytics Ltd. All rights reserved.
This program is licensed to you under the Snowplow Community License Version 1.0,
and you may not use this file except in compliance with the Snowplow Community License Version 1.0.
You may obtain a copy of the Snowplow Community License Version 1.0 at https://docs.snowplow.io/community-license-1.0
#}

{{
  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'
    }
  )
}}

{% set incremental_manifest_query = snowplow_utils.base_create_snowplow_incremental_manifest() %}

{{ incremental_manifest_query }}

Depends On

Referenced By

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

models/page_views/scratch/default/snowplow_web_page_view_events.sql

Details

Columns
Column NameDescription
page_view_idA UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’
Code
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

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 NameDescriptionType
page_view_idA UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
event_idA UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
app_idApplication 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
platformtext
user_idUnique ID set by business e.g. ‘jon.doe@email.com’text
domain_useridUser identifier specified in your project variables. By default this is the true domain_userid, a user ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
original_domain_useridTrue User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
stitched_user_idThe user_id (or domain_user_id if not found during user stitching) when the snowplow__session_stitching or snowplow__page_view_stitching variable is enabled otherwise NULL. The user_id field to be used for stitching can be overwritten by var('snowplow__user_stitching_id').text
network_useridUser ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’text
domain_sessionidSession identifier specified in your project variables. By default this is the true domain_sessionid i.e. a visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
original_domain_sessionidTrue domain_sessionid i.e. a visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_sessionidxA visit / session index e.g. 3number
page_view_in_session_indexA page view index within a single sessionnumber
page_views_in_sessionDistinct count of page_view_id within a sessionnumber
dvce_created_tstampTimestamp event was recorded on the client device e.g. ‘2013-11-26 00:03:57.885’timestamp_ntz
collector_tstampTime stamp for the event recorded by the collector e.g. ‘2013-11-26 00:02:05’timestamp_ntz
derived_tstampTimestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’timestamp_ntz
start_tstampTimestamp for the start of the page view, based on derived_tstamptimestamp_ntz
end_tstampTimestamp for the end of the page view, based on derived_tstamptimestamp_ntz
model_tstampThe current timestamp when the model processed this row.timestamp_ntz
engaged_time_in_sTime spent by the user on the page calculated using page pings.number
absolute_time_in_sThe time in seconds between the start_tstamp and end_tstampnumber
horizontal_pixels_scrolledDistance the user scrolled horizontally in pixelsnumber
vertical_pixels_scrolledDistance the user scrolled vertically in pixelsnumber
horizontal_percentage_scrolledPercentage of page scrolled horizontallyfloat
vertical_percentage_scrolledPercentage of page scrolled verticallyfloat
doc_widthThe page’s width in pixels e.g. 1024number
doc_heightThe page’s height in pixels e.g. 3000number
content_grouptext
page_titleWeb page title e.g. ‘Snowplow Docs – Understanding the structure of Snowplow data’text
page_urlThe page URL e.g. ‘http://www.example.com’text
page_urlschemeScheme aka protocol e.g. ‘https’text
page_urlhostHost aka domain e.g. ‘“www.snowplow.io’text
page_urlpathPath to page e.g. ‘/product/index.html’text
page_urlqueryQuerystring e.g. ‘id=GTM-DLRG’text
page_urlfragmentFragment aka anchor e.g. ‘4-conclusion’text
mkt_mediumType of traffic source e.g. ‘cpc’, ‘affiliate’, ‘organic’, ‘social’text
mkt_sourceThe company / website where the traffic came from e.g. ‘Google’, ‘Facebook’text
mkt_termAny keywords associated with the referrer e.g. ‘new age tarot decks’text
mkt_contentThe content of the ad. (Or an ID so that it can be looked up.) e.g. 13894723text
mkt_campaignThe campaign ID e.g. ‘diageo-123’text
mkt_clickidThe click ID e.g. ‘ac3d8e459’text
mkt_networkThe ad network to which the click ID belongs e.g. ‘DoubleClick’text
default_channel_grouptext
page_referrerURL of the referrer e.g. ‘http://www.referrer.com’text
refr_urlschemeReferer scheme e.g. ‘http’text
refr_urlhostReferer host e.g. ‘www.bing.com’text
refr_urlpathReferer page path e.g. ‘/images/search’text
refr_urlqueryReferer URL querystring e.g. ‘q=psychic+oracle+cards’text
refr_urlfragmentReferer URL fragmenttext
refr_mediumType of referer e.g. ‘search’, ‘internal’text
refr_sourceName of referer if recognised e.g. ‘Bing images’text
refr_termKeywords if source is a search engine e.g. ‘psychic oracle cards’text
geo_countryISO 3166-1 code for the country the visitor is located in e.g. ‘GB’, ‘US’text
geo_regionISO-3166-2 code for country region the visitor is in e.g. ‘I9’, ‘TX’text
geo_region_nameVisitor region name e.g. ‘Florida’text
geo_cityCity the visitor is in e.g. ‘New York’, ‘London’text
geo_zipcodePostcode the visitor is in e.g. ‘94109’text
geo_latitudeVisitor location latitude e.g. 37.443604float
geo_longitudeVisitor location longitude e.g. -122.4124float
geo_timezoneVisitor timezone name e.g. ‘Europe/London’text
user_ipaddressUser IP address e.g. ‘92.231.54.234’text
useragentRaw useragenttext
br_langLanguage the browser is set to e.g. ‘en-GB’text
br_viewwidthViewport width e.g. 1000number
br_viewheightViewport height e.g. 1000number
br_colordepthBit depth of the browser color palette e.g. 24text
br_renderengineBrowser rendering engine e.g. ‘GECKO’text
os_timezoneClient operating system timezone e.g. ‘Europe/London’text
categoryCategory based on activity if the IP/UA is a spider or robot, BROWSER otherwisetext
primary_impactWhether the spider or robot would affect page impression measurement, ad impression measurement, both or nonetext
reasonType of failed check if the IP/UA is a spider or robot, PASSED_ALL otherwisetext
spider_or_robotTrue if the IP address or user agent checked against the list is a spider or robot, false otherwiseboolean
useragent_familyUseragent family (browser) nametext
useragent_majorUseragent major versiontext
useragent_minorUseragent minor versiontext
useragent_patchUseragent patch versiontext
useragent_versionFull version of the useragenttext
os_familyOperating system family e.g. ‘Linux’text
os_majorOperation system major versiontext
os_minorOperation system minor versiontext
os_patchOperation system patch versiontext
os_patch_minorOperation system patch minor versiontext
os_versionOperation system full versiontext
device_familyDevice typetext
device_classClass of device e.g. phonetext
device_categorytext
screen_resolutiontext
agent_classClass of agent e.g. browsertext
agent_nameName of agent e.g. Chrometext
agent_name_versionName and version of agent e.g. Chrome 53.0.2785.124text
agent_name_version_majorName and major version of agent e.g. Chrome 53text
agent_versionVersion of agent e.g. 53.0.2785.124text
agent_version_majorMajor version of agent e.g. 53text
device_brandBrand of device e.g. Googletext
device_nameName of device e.g. Google Nexus 6text
device_versionVersion of device e.g. 6.0text
layout_engine_classClass of layout engine e.g. Browsertext
layout_engine_nameName of layout engine e.g. Blinktext
layout_engine_name_versionName and version of layout engine e.g. Blink 53.0text
layout_engine_name_version_majorName and major version of layout engine e.g. Blink 53text
layout_engine_versionVersion of layout engine e.g. 53.0text
layout_engine_version_majorMajor version of layout engine e.g. 53text
operating_system_classClass of the OS e.g. Mobiletext
operating_system_nameName of the OS e.g. Androidtext
operating_system_name_versionName and version of the OS e.g. Android 7.0text
operating_system_versionVersion of the OS e.g. 7.0text
v_collectortext
event_id2text
Code
Source
{#
Copyright (c) 2020-present Snowplow Analytics Ltd. All rights reserved.
This program is licensed to you under the Snowplow Community License Version 1.0,
and you may not use this file except in compliance with the Snowplow Community License Version 1.0.
You may obtain a copy of the Snowplow Community License Version 1.0 at https://docs.snowplow.io/community-license-1.0
#}

{{
  config(
    materialized='incremental',
    on_schema_change='append_new_columns',
    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"],
    post_hook="{{ snowplow_web.stitch_user_identifiers(
      enabled=var('snowplow__page_view_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_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

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

models/page_views/scratch/default/snowplow_web_page_views_this_run.sql

Details

Columns
Column NameDescriptionType
page_view_idA UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
event_idA UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
app_idApplication 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
platformPlatform e.g. ‘web’text
user_idUnique ID set by business e.g. ‘jon.doe@email.com’text
domain_useridUser identifier specified in your project variables. By default this is the true domain_userid, a user ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
original_domain_useridTrue User ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
stitched_user_idtext
network_useridUser ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’text
domain_sessionidSession identifier specified in your project variables. By default this is the true domain_sessionid i.e. a visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
original_domain_sessionidTrue domain_sessionid i.e. a visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_sessionidxA visit / session index e.g. 3number
page_view_in_session_indexA page view index within a single sessionnumber
page_views_in_sessionDistinct count of page_view_id within a sessionnumber
dvce_created_tstampTimestamp event was recorded on the client device e.g. ‘2013-11-26 00:03:57.885’timestamp_ntz
collector_tstampTime stamp for the event recorded by the collector e.g. ‘2013-11-26 00:02:05’timestamp_ntz
derived_tstampTimestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’timestamp_ntz
start_tstampTimestamp for the start of the page view, based on derived_tstamptimestamp_ntz
end_tstampTimestamp for the end of the page view, based on derived_tstamptimestamp_ntz
model_tstampThe current timestamp when the model processed this row.timestamp_ntz
engaged_time_in_sTime spent by the user on the page calculated using page pings.number
absolute_time_in_sThe time in seconds between the start_tstamp and end_tstampnumber
horizontal_pixels_scrolledDistance the user scrolled horizontally in pixelsnumber
vertical_pixels_scrolledDistance the user scrolled vertically in pixelsnumber
horizontal_percentage_scrolledPercentage of page scrolled horizontallyfloat
vertical_percentage_scrolledPercentage of page scrolled verticallyfloat
doc_widthThe page’s width in pixels e.g. 1024number
doc_heightThe page’s height in pixels e.g. 3000number
content_groupCustom defined rule builder to classify page based on url title, etc. Defined in macro content_group_query.text
page_titleWeb page title e.g. ‘Snowplow Docs – Understanding the structure of Snowplow data’text
page_urlThe page URL e.g. ‘http://www.example.com’text
page_urlschemeScheme aka protocol e.g. ‘https’text
page_urlhostHost aka domain e.g. ‘“www.snowplow.io’text
page_urlpathPath to page e.g. ‘/product/index.html’text
page_urlqueryQuerystring e.g. ‘id=GTM-DLRG’text
page_urlfragmentFragment aka anchor e.g. ‘4-conclusion’text
mkt_mediumType of traffic source e.g. ‘cpc’, ‘affiliate’, ‘organic’, ‘social’text
mkt_sourceThe company / website where the traffic came from e.g. ‘Google’, ‘Facebook’text
mkt_termAny keywords associated with the referrer e.g. ‘new age tarot decks’text
mkt_contentThe content of the ad. (Or an ID so that it can be looked up.) e.g. 13894723text
mkt_campaignThe campaign ID e.g. ‘diageo-123’text
mkt_clickidThe click ID e.g. ‘ac3d8e459’text
mkt_networkThe ad network to which the click ID belongs e.g. ‘DoubleClick’text
default_channel_groupThe channels by which users arrived at your site.text
page_referrerURL of the referrer e.g. ‘http://www.referrer.com’text
refr_urlschemeReferer scheme e.g. ‘http’text
refr_urlhostReferer host e.g. ‘www.bing.com’text
refr_urlpathReferer page path e.g. ‘/images/search’text
refr_urlqueryReferer URL querystring e.g. ‘q=psychic+oracle+cards’text
refr_urlfragmentReferer URL fragmenttext
refr_mediumType of referer e.g. ‘search’, ‘internal’text
refr_sourceName of referer if recognised e.g. ‘Bing images’text
refr_termKeywords if source is a search engine e.g. ‘psychic oracle cards’text
geo_countryISO 3166-1 code for the country the visitor is located in e.g. ‘GB’, ‘US’text
geo_regionISO-3166-2 code for country region the visitor is in e.g. ‘I9’, ‘TX’text
geo_region_nameVisitor region name e.g. ‘Florida’text
geo_cityCity the visitor is in e.g. ‘New York’, ‘London’text
geo_zipcodePostcode the visitor is in e.g. ‘94109’text
geo_latitudeVisitor location latitude e.g. 37.443604float
geo_longitudeVisitor location longitude e.g. -122.4124float
geo_timezoneVisitor timezone name e.g. ‘Europe/London’text
user_ipaddressUser IP address e.g. ‘92.231.54.234’text
useragentRaw useragenttext
br_langLanguage the browser is set to e.g. ‘en-GB’text
br_viewwidthViewport width e.g. 1000number
br_viewheightViewport height e.g. 1000number
br_colordepthBit depth of the browser color palette e.g. 24text
br_renderengineBrowser rendering engine e.g. ‘GECKO’text
os_timezoneClient operating system timezone e.g. ‘Europe/London’text
categoryCategory based on activity if the IP/UA is a spider or robot, BROWSER otherwisetext
primary_impactWhether the spider or robot would affect page impression measurement, ad impression measurement, both or nonetext
reasonType of failed check if the IP/UA is a spider or robot, PASSED_ALL otherwisetext
spider_or_robotTrue if the IP address or user agent checked against the list is a spider or robot, false otherwiseboolean
useragent_familyUseragent family (browser) nametext
useragent_majorUseragent major versiontext
useragent_minorUseragent minor versiontext
useragent_patchUseragent patch versiontext
useragent_versionFull version of the useragenttext
os_familyOperating system family e.g. ‘Linux’text
os_majorOperation system major versiontext
os_minorOperation system minor versiontext
os_patchOperation system patch versiontext
os_patch_minorOperation system patch minor versiontext
os_versionOperation system full versiontext
device_familyDevice typetext
device_classClass of device e.g. phonetext
device_categoryDerived from the device_class it is used to classify devices into one of the following: Desktop / Mobile / Tablet / Other.text
screen_resolutionCombines dvce_screenwidth x dvce_screenheight.text
agent_classClass of agent e.g. browsertext
agent_nameName of agent e.g. Chrometext
agent_name_versionName and version of agent e.g. Chrome 53.0.2785.124text
agent_name_version_majorName and major version of agent e.g. Chrome 53text
agent_versionVersion of agent e.g. 53.0.2785.124text
agent_version_majorMajor version of agent e.g. 53text
device_brandBrand of device e.g. Googletext
device_nameName of device e.g. Google Nexus 6text
device_versionVersion of device e.g. 6.0text
layout_engine_classClass of layout engine e.g. Browsertext
layout_engine_nameName of layout engine e.g. Blinktext
layout_engine_name_versionName and version of layout engine e.g. Blink 53.0text
layout_engine_name_version_majorName and major version of layout engine e.g. Blink 53text
layout_engine_versionVersion of layout engine e.g. 53.0text
layout_engine_version_majorMajor version of layout engine e.g. 53text
operating_system_classClass of the OS e.g. Mobiletext
operating_system_nameName of the OS e.g. Androidtext
operating_system_name_versionName and version of the OS e.g. Android 7.0text
operating_system_versionVersion of the OS e.g. 7.0text
v_collectortext
event_id2text
Code
Source
{#
Copyright (c) 2020-present Snowplow Analytics Ltd. All rights reserved.
This program is licensed to you under the Snowplow Community License Version 1.0,
and you may not use this file except in compliance with the Snowplow Community License Version 1.0.
You may obtain a copy of the Snowplow Community License Version 1.0 at https://docs.snowplow.io/community-license-1.0
#}

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

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

    ev.app_id,
    ev.platform,

    -- user fields
    ev.user_id,
    ev.domain_userid,
    ev.original_domain_userid,
    {% if var('snowplow__page_view_stitching') %}
      -- updated with mapping as part of post hook on derived page_views table
      cast(domain_userid as {{ type_string() }}) as stitched_user_id,
    {% else %}
      cast(null as {{ snowplow_utils.type_max_string() }}) as stitched_user_id,
    {% endif %}
    ev.network_userid,

    -- session fields
    ev.domain_sessionid,
    ev.original_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,
    {{ content_group_query() }} as content_group,
    ev.page_url,
    ev.page_urlscheme,
    ev.page_urlhost,
    ev.page_urlpath,
    ev.page_urlquery,
    ev.page_urlfragment,

    -- marketing fields
    ev.mkt_medium,
    ev.mkt_source,
    ev.mkt_term,
    ev.mkt_content,
    ev.mkt_campaign,
    ev.mkt_clickid,
    ev.mkt_network,
    {{ channel_group_query() }} as default_channel_group,

    -- referrer fields
    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,

    -- geo fields
    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.dvce_screenwidth || 'x' || ev.dvce_screenheight as screen_resolution,

    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('ev')}},

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

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

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

    {%- if var('snowplow__page_view_passthroughs', []) -%}
      {%- set passthrough_names = [] -%}
      {%- for identifier in var('snowplow__page_view_passthroughs', []) %}
      {# Check if it's a simple column or a sql+alias #}
        {%- if identifier is mapping -%}
          ,{{identifier['sql']}} as {{identifier['alias']}}
          {%- do passthrough_names.append(identifier['alias']) -%}
        {%- else -%}
          ,ev.{{identifier}}
          {%- do passthrough_names.append(identifier) -%}
        {%- endif -%}
      {% endfor -%}
    {%- endif %}

  from {{ ref('snowplow_web_base_events_this_run') }} as ev
  left join {{ ref(var('snowplow__ga4_categories_seed')) }} c on lower(trim(ev.mkt_source)) = lower(c.source)

  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 %}
)

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

    p.app_id,
    p.platform,

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

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

    row_number() over (partition by p.domain_sessionid order by p.derived_tstamp, p.dvce_created_tstamp, p.event_id) 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('p.derived_tstamp', 'coalesce(t.end_tstamp, p.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,

    p.doc_width,
    p.doc_height,

    p.content_group,

    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.default_channel_group,

    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.screen_resolution,

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


    p.iab_category as category,
    p.iab_primary_impact as primary_impact,
    p.iab_reason as reason,
    p.iab_spider_or_robot as spider_or_robot,

    p.ua_useragent_family as useragent_family,
    p.ua_useragent_major as useragent_major,
    p.ua_useragent_minor as useragent_minor,
    p.ua_useragent_patch as useragent_patch,
    p.ua_useragent_version as useragent_version,
    p.ua_os_family as os_family,
    p.ua_os_major as os_major,
    p.ua_os_minor as os_minor,
    p.ua_os_patch as os_patch,
    p.ua_os_patch_minor as os_patch_minor,
    p.ua_os_version as os_version,
    p.ua_device_family as device_family,

    p.yauaa_device_class as device_class,
    p.yauaa_agent_class as agent_class,
    p.yauaa_agent_name as agent_name,
    p.yauaa_agent_name_version as agent_name_version,
    p.yauaa_agent_name_version_major as agent_name_version_major,
    p.yauaa_agent_version as agent_version,
    p.yauaa_agent_version_major as agent_version_major,
    p.yauaa_device_brand as device_brand,
    p.yauaa_device_name as device_name,
    p.yauaa_device_version as device_version,
    p.yauaa_layout_engine_class as layout_engine_class,
    p.yauaa_layout_engine_name as layout_engine_name,
    p.yauaa_layout_engine_name_version as layout_engine_name_version,
    p.yauaa_layout_engine_name_version_major as layout_engine_name_version_major,
    p.yauaa_layout_engine_version as layout_engine_version,
    p.yauaa_layout_engine_version_major as layout_engine_version_major,
    p.yauaa_operating_system_class as operating_system_class,
    p.yauaa_operating_system_name as operating_system_name,
    p.yauaa_operating_system_name_version as operating_system_name_version,
    p.yauaa_operating_system_version as operating_system_version
    {%- if var('snowplow__page_view_passthroughs', []) -%}
      {%- for col in passthrough_names %}
        , p.{{col}}
      {%- endfor -%}
    {%- endif %}
  from prep as 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 %}

  where page_view_id_dedupe_index = 1

)


select
  pve.page_view_id,
  pve.event_id,

  pve.app_id,
  pve.platform,

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

  -- session fields
  pve.domain_sessionid,
  pve.original_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.content_group,

  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.default_channel_group,

  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,
  case when pve.device_class = 'Desktop' then 'Desktop'
    when pve.device_class = 'Phone' then 'Mobile'
    when pve.device_class = 'Tablet' then 'Tablet'
    else 'Other' end as device_category,
  pve.screen_resolution,
  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
  {%- if var('snowplow__page_view_passthroughs', []) -%}
    {%- for col in passthrough_names %}
      , pve.{{col}}
    {%- endfor -%}
  {%- endif %}

from page_view_events pve