Skip to main content

Snowplow Ecommerce Models

caution

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

Snowplow Ecommerce​

Snowplow Ecommerce Base Events This Run​

models/base/scratch/<adaptor>/snowplow_ecommerce_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 ecommerce 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, and all of the fields parsed from the various e-commerce contexts except the product 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_ecommerce_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
page_view_idA UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’
domain_useridUser ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’
ecommerce_user_idThe ecommerce user id extracted from the ecommerce user context.
ecommerce_user_emailThe ecommerce user email, extracted from the ecommerce user context.
ecommerce_user_is_guestA boolean extracted from the ecommerce user context to ascertain whether a user is a guest or not.
checkout_step_numberThe checkout step index e.g. 1
checkout_account_typeThe type of account that is conducting the checkout e.g. guest
checkout_billing_full_addressThe full billing address provided at the checkout step e.g. 1 Lincoln Street
checkout_billing_postcodeThe full billing postcode/zipcode provided at the checkout step e.g. 90210
checkout_coupon_codeThe coupon code used at the checkout step e.g. SNOWPLOW50
checkout_delivery_methodThe delivery method selected at the checkout step e.g. Store pickup
checkout_delivery_providerThe delivery provider selected at the checkout step e.g. SantaPost
checkout_marketing_opt_inA boolean to describe whether or not the user has opted in for marketing emails at the checkout step
checkout_payment_methodThe chosen payment method selected at the checkout step e.g. Credit Card
checkout_proof_of_paymentThe proof of payment given at the checkout step, e.g. invoice or receipt
checkout_shipping_full_addressFull shipping address
checkout_shipping_postcodeShipping address postcode
ecommerce_page_typeThe type of the page that was visited E.g. homepage, product page, checkout page
ecommerce_page_languageThe language that the web page is based in
ecommerce_page_localeThe locale version of the site that is running
transaction_idThe ID of the transaction
transaction_currencyThe currency used for the transaction (ISO 4217).
transaction_payment_methodThe payment method used for the transaction.
transaction_revenueThe revenue of the transaction.
transaction_total_quantityTotal quantity of items in the transaction.
transaction_credit_orderWhether the transaction is a credit order or not.
transaction_discount_amountDiscount amount taken off
transaction_discount_codeDiscount code used.
transaction_shippingTotal cost of shipping on the transaction.
transaction_taxTotal amount of tax on the transaction.
cart_idThe unique ID representing this cart e.g. abc123
cart_currencyThe currency used for this cart (ISO 4217)
cart_total_valueThe total value of the cart after this interaction
ecommerce_action_typeThe type of ecommerce action that was performed, e.g. transaction, add_to_cart, etc.
ecommerce_action_nameThe name that is associated with the ecommerce action. E.g. when a list_click occurs, the name of the product list such as 'recommended' or 'shop the look'
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_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_sessionidA 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’
Code
Source
{{
    config(
        sort='collector_tstamp',
        dist='event_id',
        tags=["this_run"]
    )
}}

{%- set lower_limit, upper_limit = snowplow_utils.return_limits_from_model(ref('snowplow_ecommerce_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

{% if var('snowplow__enable_mobile_events', false) -%}
    {{ snowplow_utils.get_sde_or_context(var('snowplow__atomic_schema', 'atomic'), var('snowplow__context_mobile_session'), lower_limit, upper_limit, 'mob_session') }},
    {{ snowplow_utils.get_sde_or_context(var('snowplow__atomic_schema', 'atomic'), var('snowplow__context_screen'), lower_limit, upper_limit, 'mob_sc_view') }},
{%- endif %}

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,
        {% if var('snowplow__enable_mobile_events', false) %}
            coalesce(
                ms.mob_session_session_id,
                a.domain_sessionid
            ) as domain_sessionid,
        {% else %}
            a.domain_sessionid,
        {% endif %}
        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,
        count(*) over (partition by a.event_id) as event_id_dedupe_count

    from {{ var('snowplow__events') }} as a
    {% if var('snowplow__enable_mobile_events', false) -%}
        left join {{ var('snowplow__context_mobile_session') }} ms on a.event_id = ms.mob_session__id and a.collector_tstamp = ms.mob_session__tstamp
    {%- endif %}
        inner join {{ ref('snowplow_ecommerce_base_sessions_this_run') }} as b
            on
            {% if var('snowplow__enable_mobile_events', false) %}
                coalesce(
                    ms.mob_session_session_id,
                    a.domain_sessionid
                )
            {% else %}
                a.domain_sessionid
            {% endif %} = 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",[])) }}
        and {{ snowplow_ecommerce.event_name_filter(var("snowplow__ecommerce_event_names", "['snowplow_ecommerce_action']")) }}

),


{% if not var('snowplow__disable_ecommerce_user_context', false) -%}
    {{ snowplow_utils.get_sde_or_context(var('snowplow__atomic_schema', 'atomic'), var('snowplow__context_ecommerce_user'), lower_limit, upper_limit, 'ecommerce_user') }},
{%- endif %}
{% if not var('snowplow__disable_ecommerce_checkouts', false) -%}
    {{ snowplow_utils.get_sde_or_context(var('snowplow__atomic_schema', 'atomic'), var('snowplow__context_ecommerce_checkout_step'), lower_limit, upper_limit, 'checkout') }},
{%- endif %}
{% if not var('snowplow__disable_ecommerce_page_context', false) -%}
    {{ snowplow_utils.get_sde_or_context(var('snowplow__atomic_schema', 'atomic'), var('snowplow__context_ecommerce_page'), lower_limit, upper_limit, 'ecommerce_page') }},
{%- endif %}
{% if not var('snowplow__disable_ecommerce_transactions', false) -%}
    {{ snowplow_utils.get_sde_or_context(var('snowplow__atomic_schema', 'atomic'), var('snowplow__context_ecommerce_transaction'), lower_limit, upper_limit, 'transaction') }},
{%- endif %}
{% if not var('snowplow__disable_ecommerce_carts', false) -%}
    {{ snowplow_utils.get_sde_or_context(var('snowplow__atomic_schema', 'atomic'), var('snowplow__context_ecommerce_cart'), lower_limit, upper_limit, 'cart') }},
{%- endif %}

{{ snowplow_utils.get_sde_or_context(var('snowplow__atomic_schema', 'atomic'), var('snowplow__sde_ecommerce_action'), lower_limit, upper_limit, 'ecommerce_action', single_entity = false) }},

{{ snowplow_utils.get_sde_or_context(var('snowplow__atomic_schema', 'atomic'), var('snowplow__context_web_page'), lower_limit, upper_limit, 'page_view') }}


select ev.*,
    {% if var('snowplow__enable_mobile_events', false) %}
        coalesce(
            sv.mob_sc_view_id,
            pv.page_view_id
        ) as page_view_id,
    {% else %}
        pv.page_view_id,
    {% endif %}

    {% if var('snowplow__disable_ecommerce_user_context', false) -%}
        cast(NULL as {{ type_string() }}) as ecommerce_user_id,
        cast(NULL as {{ type_string() }}) as ecommerce_user_email,
        cast(NULL as {{ type_boolean() }}) as ecommerce_user_is_guest,
    {%- else -%}
        usr.ecommerce_user_id,
        usr.ecommerce_user_email,
        usr.ecommerce_user_is_guest,
    {%- endif %}

    -- unpacking the ecommerce checkout step object
    {% if var('snowplow__disable_ecommerce_checkouts', false) -%}
        cast(NULL as {{ type_int() }}) as checkout_step_number,
        cast(NULL as {{ type_string() }}) as checkout_account_type,
        cast(NULL as {{ type_string() }}) as checkout_billing_full_address,
        cast(NULL as {{ type_string() }}) as checkout_billing_postcode,
        cast(NULL as {{ type_string() }}) as checkout_coupon_code,
        cast(NULL as {{ type_string() }}) as checkout_delivery_method,
        cast(NULL as {{ type_string() }}) as checkout_delivery_provider,
        cast(NULL as {{ type_boolean() }}) as checkout_marketing_opt_in,
        cast(NULL as {{ type_string() }}) as checkout_payment_method,
        cast(NULL as {{ type_string() }}) as checkout_proof_of_payment,
        cast(NULL as {{ type_string() }}) as checkout_shipping_full_address,
        cast(NULL as {{ type_string() }}) as checkout_shipping_postcode,
    {%- else -%}
        checkout.checkout_step as checkout_step_number,
        checkout.checkout_account_type,
        checkout.checkout_billing_full_address,
        checkout.checkout_billing_postcode,
        checkout.checkout_coupon_code,
        checkout.checkout_delivery_method,
        checkout.checkout_delivery_provider,
        checkout.checkout_marketing_opt_in,
        checkout.checkout_payment_method,
        checkout.checkout_proof_of_payment,
        checkout.checkout_shipping_full_address,
        checkout.checkout_shipping_postcode,
    {%- endif %}

    -- unpacking the ecommerce page object
    {% if var('snowplow__disable_ecommerce_page_context', false) -%}
        CAST(NULL as {{ type_string() }}) as ecommerce_page_type,
        CAST(NULL as {{ type_string() }}) as ecommerce_page_language,
        CAST(NULL as {{ type_string() }}) as ecommerce_page_locale,
    {%- else -%}
        ecom_page.ecommerce_page_type,
        ecom_page.ecommerce_page_language,
        ecom_page.ecommerce_page_locale,
    {%- endif %}

    -- unpacking the ecommerce transaction object
    {% if var('snowplow__disable_ecommerce_transactions', false) -%}
        CAST(NULL AS {{ type_string() }}) as transaction_id,
        CAST(NULL AS {{ type_string() }}) as transaction_currency,
        CAST(NULL AS {{ type_string() }}) as transaction_payment_method,
        CAST(NULL AS decimal(9,2)) as transaction_revenue,
        CAST(NULL AS {{ type_int() }}) as transaction_total_quantity,
        CAST(NULL AS {{ type_boolean() }}) as transaction_credit_order,
        CAST(NULL AS decimal(9,2)) as transaction_discount_amount,
        CAST(NULL AS {{ type_string() }}) as transaction_discount_code,
        CAST(NULL AS decimal(9,2)) as transaction_shipping,
        CAST(NULL AS decimal(9,2)) as transaction_tax,
    {%- else -%}
        trans.transaction_transaction_id as transaction_id,
        trans.transaction_currency,
        trans.transaction_payment_method,
        trans.transaction_revenue,
        trans.transaction_total_quantity,
        trans.transaction_credit_order,
        trans.transaction_discount_amount,
        trans.transaction_discount_code,
        trans.transaction_shipping,
        trans.transaction_tax,
    {%- endif %}

    -- unpacking the ecommerce cart object
    {% if var('snowplow__disable_ecommerce_carts', false) -%}
        CAST(NULL AS {{ type_string() }}) as cart_id,
        CAST(NULL AS {{ type_string() }}) as cart_currency,
        CAST(NULL AS decimal(9,2)) as cart_total_value,
    {%- else -%}
        carts.cart_cart_id as cart_id,
        carts.cart_currency,
        carts.cart_total_value,
    {%- endif%}

    -- unpacking the ecommerce action object
    action.ecommerce_action_type,
    action.ecommerce_action_name,

    dense_rank() over (partition by domain_sessionid order by derived_tstamp) AS event_in_session_index

from events_this_run ev

{% if not var('snowplow__disable_ecommerce_user_context', false) -%}
    left join {{ var('snowplow__context_ecommerce_user') }} usr on ev.event_id = usr.ecommerce_user__id and ev.collector_tstamp = usr.ecommerce_user__tstamp
{%- endif %}
{% if not var('snowplow__disable_ecommerce_checkouts', false) -%}
    left join {{ var('snowplow__context_ecommerce_checkout_step') }} checkout on ev.event_id = checkout.checkout__id and ev.collector_tstamp = checkout.checkout__tstamp
{%- endif %}
{% if not var('snowplow__disable_ecommerce_page_context', false) -%}
    left join {{ var('snowplow__context_ecommerce_page') }} ecom_page on ev.event_id = ecom_page.ecommerce_page__id and ev.collector_tstamp = ecom_page.ecommerce_page__tstamp
{%- endif %}
{% if not var('snowplow__disable_ecommerce_transactions', false) -%}
    left join {{ var('snowplow__context_ecommerce_transaction') }} trans on ev.event_id = trans.transaction__id and ev.collector_tstamp = trans.transaction__tstamp
{%- endif %}
{% if not var('snowplow__disable_ecommerce_carts', false) -%}
    left join {{ var('snowplow__context_ecommerce_cart') }} carts on ev.event_id = carts.cart__id and ev.collector_tstamp = carts.cart__tstamp
{%- endif %}
    left join {{ var('snowplow__sde_ecommerce_action') }} action on ev.event_id = action.ecommerce_action__id and ev.collector_tstamp = action.ecommerce_action__tstamp
    left join {{ var('snowplow__context_web_page') }} pv on ev.event_id = pv.page_view__id and ev.collector_tstamp = pv.page_view__tstamp
{% if var('snowplow__enable_mobile_events', false) -%}
    left join {{ var('snowplow__context_screen') }} sv on ev.event_id = sv.mob_sc_view__id and ev.collector_tstamp = sv.mob_sc_view__tstamp
{%- endif %}
where
    ev.event_id_dedupe_index = 1

Depends On

Referenced By

Snowplow Ecommerce Base New Event Limits​

models/base/scratch/snowplow_ecommerce_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
{{ 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_ecommerce') -%}

{% set min_last_success,
         max_last_success,
         models_matched_from_manifest,
         has_matched_all_models = snowplow_utils.get_incremental_manifest_status(ref('snowplow_ecommerce_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 Ecommerce Base Quarantined Sessions​

models/base/manifest/snowplow_ecommerce_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_idThe session_id of the quarantined sessiontext
Code
Source
{{
  config(
    materialized='incremental',
    full_refresh=snowplow_ecommerce.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 {{ dbt.type_string() }}) session_id
)

select *

from prep
where false

Depends On

Referenced By

Snowplow Ecommerce Base Sessions Lifecycle Manifest​

models/base/manifest/<adaptor>/snowplow_ecommerce_base_sessions_lifecycle_manifest.sql

Description

This incremental table is a manifest of all sessions that have been processed by the Snowplow dbt ecommerce package. 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

File Paths

models/base/manifest/default/snowplow_ecommerce_base_sessions_lifecycle_manifest.sql

Details

Columns
Column NameDescriptionType
session_idA visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_useridUser ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
start_tstampThe collector_tstamp when the session begantimestamp_ntz
end_tstampThe collector_tstamp when the session endedtimestamp_ntz
Code
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_utils.get_value_by_target_type(bigquery_val=["session_id"], snowflake_val=["to_date(start_tstamp)"]),
    full_refresh=snowplow_ecommerce.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_ecommerce_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_ecommerce') %}

with

{% if var('snowplow__enable_mobile_events', false) -%}
    {{ snowplow_utils.get_sde_or_context(var('snowplow__atomic_schema', 'atomic'), var('snowplow__context_mobile_session'), lower_limit, upper_limit, 'mob_session') }},
{%- endif %}

new_events_session_ids as (
  select
    {% if var('snowplow__enable_mobile_events', false) %}
      coalesce(ms.mob_session_session_id, e.domain_sessionid) as session_id,
      max(coalesce(ms.mob_session_user_id, e.domain_userid)) as domain_userid,
    {% else %}
      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.
    {% endif %}
    min(e.collector_tstamp) as start_tstamp,
    max(e.collector_tstamp) as end_tstamp

  from {{ var('snowplow__events') }} e
  {% if var('snowplow__enable_mobile_events', false) -%}
      left join {{ var('snowplow__context_mobile_session') }} ms on e.event_id = ms.mob_session__id and e.collector_tstamp = ms.mob_session__tstamp
  {%- endif %}
  where
    {% if var('snowplow__enable_mobile_events', false) %}
      coalesce(ms.mob_session_session_id, e.domain_sessionid) is not null
      and not exists (select 1 from {{ ref('snowplow_ecommerce_base_quarantined_sessions') }} as a where a.session_id = coalesce(ms.mob_session_session_id, e.domain_sessionid)) -- don't continue processing v.long sessions
    {% else %}
      e.domain_sessionid is not null
      and not exists (select 1 from {{ ref('snowplow_ecommerce_base_quarantined_sessions') }} as a where a.session_id = e.domain_sessionid) -- don't continue processing v.long sessions
    {% endif %}
    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 {{ event_name_filter(var("snowplow__ecommerce_event_names", ["snowplow_ecommerce_event"]))}}
    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(sl.start_tstamp) as start_tstamp_date
  {%- endif %}

from session_lifecycle sl

Depends On

Referenced By

Snowplow Ecommerce Base Sessions This Run​

models/base/scratch/snowplow_ecommerce_base_sessions_this_run.sql

Description

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

Type: Table

Details

Columns
Column NameDescriptionType
session_idA visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_useridUser ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
start_tstampThe collector_tstamp when the session begantimestamp_ntz
end_tstampThe collector_tstamp when the session endedtimestamp_ntz
Code
Source
{{
  config(
    tags=["this_run"],
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt')),
    post_hook=[
      "{{ snowplow_utils.quarantine_sessions('snowplow_ecommerce', var('snowplow__max_session_days')) }}"
    ],
  )
}}

{%- set lower_limit,
        upper_limit,
        session_start_limit = snowplow_utils.return_base_new_event_limits(ref('snowplow_ecommerce_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_ecommerce_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

Referenced By

Snowplow Ecommerce Cart Interactions​

models/carts/snowplow_ecommerce_cart_interactions.sql

Description

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

Type: Table

Details

Columns
Column NameDescriptionType
event_idA UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
page_view_idA UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_sessionidA visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
event_in_session_indexThe index of the event in the corresponding session.number
domain_useridUser ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
network_useridUser ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’text
user_idUnique ID set by business e.g. ‘jon.doe@email.com’text
ecommerce_user_idThe ecommerce user id extracted from the ecommerce user context.text
derived_tstampTimestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’timestamp_ntz
derived_tstamp_dateDate value of the timestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’date
cart_idThe unique ID representing this cart e.g. abc123text
cart_currencyThe currency used for this cart (ISO 4217)text
cart_total_valueThe total value of the cart after this interactionnumber
cart_createdA boolean to describe whether the cart was created in this actionboolean
cart_emptiedA boolean to describe whether the cart was emptied in this actionboolean
cart_transactedA boolean to describe whether the cart was transacted (purchased) in this actionboolean
ecommerce_action_typeThe type of ecommerce action that was performed, e.g. transaction, add_to_cart, etc.text
Code
Source
{{
  config(
    materialized="incremental",
    unique_key='event_id',
    upsert_date_key='derived_tstamp',
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt')),
    partition_by = snowplow_utils.get_value_by_target_type(bigquery_val = {
      "field": "derived_tstamp",
      "data_type": "timestamp"
    }, databricks_val='derived_tstamp_date'),
    tags=["derived"],
    tblproperties={
      'delta.autoOptimize.optimizeWrite' : 'true',
      'delta.autoOptimize.autoCompact' : 'true'
    },
    snowplow_optimize=true
  )
}}

select *

from {{ ref('snowplow_ecommerce_cart_interactions_this_run') }}
where {{ snowplow_utils.is_run_with_new_events('snowplow_ecommerce') }}

Depends On

Snowplow Ecommerce Cart Interactions This Run​

models/carts/scratch/snowplow_ecommerce_cart_interactions_this_run.sql

Description

This staging table tracks and stores information about cart interactions that occurred within the current run, with interactions being when a user adds or removes an item from their cart. It possesses all of the same columns as snowplow_ecommerce_cart_interactions. If building a custom module that requires cart interactions, this is the table you should reference for that information.

Type: Table

Details

Columns
Column NameDescriptionType
event_idA UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
page_view_idA UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_sessionidA visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
event_in_session_indexThe index of the event in the corresponding session.number
domain_useridUser ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
network_useridUser ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’text
user_idUnique ID set by business e.g. ‘jon.doe@email.com’text
ecommerce_user_idThe ecommerce user id extracted from the ecommerce user context.text
derived_tstampTimestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’timestamp_ntz
derived_tstamp_dateDate value of the timestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’date
cart_idThe unique ID representing this cart e.g. abc123text
cart_currencyThe currency used for this cart (ISO 4217)text
cart_total_valueThe total value of the cart after this interactionnumber
cart_createdA boolean to describe whether the cart was created in this actionboolean
cart_emptiedA boolean to describe whether the cart was emptied in this actionboolean
cart_transactedA boolean to describe whether the cart was transacted (purchased) in this actionboolean
ecommerce_action_typeThe type of ecommerce action that was performed, e.g. transaction, add_to_cart, etc.text
Code
Source
{{
    config(
        tags=["this_run"],
        sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
    )
}}

with cart_product_interactions AS (
    select
        e.event_id,
        e.page_view_id,

        -- session fields
        e.domain_sessionid,
        e.event_in_session_index,

        -- user fields
        e.domain_userid,
        e.network_userid,
        e.user_id,
        e.ecommerce_user_id,

        -- timestamp fields
        e.derived_tstamp,
        DATE(e.derived_tstamp) as derived_tstamp_date,

        -- ecommerce cart fields
        e.cart_id,
        e.cart_currency,
        e.cart_total_value,

        -- ecommerce action field
        e.ecommerce_action_type,
        {% if var('snowplow__disable_ecommerce_products', false) -%}
            cast(NULL as {{ type_float() }}) as product_value_added
        {%- else -%}
            SUM(pi.product_price) as product_value_added
        {%- endif %}


    from {{ ref('snowplow_ecommerce_base_events_this_run') }} as e
    {% if not var('snowplow__disable_ecommerce_products', false) -%}
        left join {{ ref('snowplow_ecommerce_product_interactions_this_run') }} as pi on e.event_id = pi.event_id AND pi.is_add_to_cart
    {%- endif %}
    where e.ecommerce_action_type IN ('add_to_cart', 'remove_from_cart', 'transaction')
    group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14

)

select
    -- event fields
    event_id,
    page_view_id,

    -- session fields
    domain_sessionid,
    event_in_session_index,

    -- user fields
    domain_userid,
    network_userid,
    user_id,
    ecommerce_user_id,

    -- timestamp fields
    derived_tstamp,
    derived_tstamp_date,

    -- ecommerce cart fields
    cart_id,
    cart_currency,
    cart_total_value,
    product_value_added = cart_total_value as cart_created,
    cart_total_value = 0 as cart_emptied,
    ecommerce_action_type = 'transaction' as cart_transacted,

    -- ecommerce action field
    ecommerce_action_type


from cart_product_interactions

Depends On

Referenced By

Snowplow Ecommerce Checkout Interactions​

models/checkouts/snowplow_ecommerce_checkout_interactions.sql

Description

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

Type: Table

Details

Columns
Column NameDescriptionType
event_idA UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
page_view_idA UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_sessionidA visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
event_in_session_indexThe index of the event in the corresponding session.number
domain_useridUser ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
network_useridUser ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’text
user_idUnique ID set by business e.g. ‘jon.doe@email.com’text
ecommerce_user_idThe ecommerce user id extracted from the ecommerce user context.text
derived_tstampTimestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’timestamp_ntz
derived_tstamp_dateDate value of the timestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’date
ecommerce_action_typeThe type of ecommerce action that was performed, e.g. transaction, add_to_cart, etc.text
ecommerce_action_nameThe name that is associated with the ecommerce action. E.g. when a list_click occurs, the name of the product list such as 'recommended' or 'shop the look'text
ecommerce_page_typeThe type of the page that was visited E.g. homepage, product page, checkout pagetext
checkout_step_numberThe checkout step index e.g. 1number
checkout_account_typeThe type of account that is conducting the checkout e.g. guesttext
checkout_billing_full_addressThe full billing address provided at the checkout step e.g. 1 Lincoln Streettext
checkout_billing_postcodeThe full billing postcode/zipcode provided at the checkout step e.g. 90210text
checkout_coupon_codeThe coupon code used at the checkout step e.g. SNOWPLOW50text
checkout_delivery_methodThe delivery method selected at the checkout step e.g. Store pickuptext
checkout_delivery_providerThe delivery provider selected at the checkout step e.g. SantaPosttext
checkout_marketing_opt_inA boolean to describe whether or not the user has opted in for marketing emails at the checkout stepboolean
checkout_payment_methodThe chosen payment method selected at the checkout step e.g. Credit Cardtext
checkout_proof_of_paymentThe proof of payment given at the checkout step, e.g. invoice or receipttext
checkout_shipping_full_addressFull shipping addresstext
checkout_shipping_postcodeShipping address postcodetext
session_entered_at_stepA boolean to describe whether the session was entered at this checkout stepboolean
checkout_succeededA boolean to describe whether the checkout succeededboolean
ecommerce_user_emailThe ecommerce user email, extracted from the ecommerce user context.text
ecommerce_user_is_guestA boolean extracted from the ecommerce user context to ascertain whether a user is a guest or not.boolean
Code
Source
{{
  config(
    materialized="incremental",
    unique_key='event_id',
    upsert_date_key='derived_tstamp',
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt')),
    partition_by = snowplow_utils.get_value_by_target_type(bigquery_val = {
      "field": "derived_tstamp",
      "data_type": "timestamp"
    }, databricks_val='derived_tstamp_date'),
    tags=["derived"],
    tblproperties={
      'delta.autoOptimize.optimizeWrite' : 'true',
      'delta.autoOptimize.autoCompact' : 'true'
    },
    snowplow_optimize=true
  )
}}

select *

from {{ ref('snowplow_ecommerce_checkout_interactions_this_run') }}
where {{ snowplow_utils.is_run_with_new_events('snowplow_ecommerce') }}

Depends On

Snowplow Ecommerce Checkout Interactions This Run​

models/checkouts/scratch/snowplow_ecommerce_checkout_interactions_this_run.sql

Description

This staging table tracks and stores information about checkout interactions that occurred within the current run, with interactions being when a user progresses through the checkout flow or completes a transaction. It possesses all of the same columns as snowplow_ecommerce_checkout_interactions. If building a custom module that requires checkout interactions, this is the table you should reference for that information.

Type: Table

Details

Columns
Column NameDescriptionType
event_idA UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
page_view_idA UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_sessionidA visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
event_in_session_indexThe index of the event in the corresponding session.number
domain_useridUser ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
network_useridUser ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’text
user_idUnique ID set by business e.g. ‘jon.doe@email.com’text
ecommerce_user_idThe ecommerce user id extracted from the ecommerce user context.text
derived_tstampTimestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’timestamp_ntz
derived_tstamp_dateDate value of the timestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’date
ecommerce_action_typeThe type of ecommerce action that was performed, e.g. transaction, add_to_cart, etc.text
ecommerce_action_nameThe name that is associated with the ecommerce action. E.g. when a list_click occurs, the name of the product list such as 'recommended' or 'shop the look'text
ecommerce_page_typeThe type of the page that was visited E.g. homepage, product page, checkout pagetext
checkout_step_numberThe checkout step index e.g. 1number
checkout_account_typeThe type of account that is conducting the checkout e.g. guesttext
checkout_billing_full_addressThe full billing address provided at the checkout step e.g. 1 Lincoln Streettext
checkout_billing_postcodeThe full billing postcode/zipcode provided at the checkout step e.g. 90210text
checkout_coupon_codeThe coupon code used at the checkout step e.g. SNOWPLOW50text
checkout_delivery_methodThe delivery method selected at the checkout step e.g. Store pickuptext
checkout_delivery_providerThe delivery provider selected at the checkout step e.g. SantaPosttext
checkout_marketing_opt_inA boolean to describe whether or not the user has opted in for marketing emails at the checkout stepboolean
checkout_payment_methodThe chosen payment method selected at the checkout step e.g. Credit Cardtext
checkout_proof_of_paymentThe proof of payment given at the checkout step, e.g. invoice or receipttext
checkout_shipping_full_addressFull shipping addresstext
checkout_shipping_postcodeShipping address postcodetext
session_entered_at_stepA boolean to describe whether the session was entered at this checkout stepboolean
checkout_succeededA boolean to describe whether the checkout succeededboolean
ecommerce_user_emailThe ecommerce user email, extracted from the ecommerce user context.text
ecommerce_user_is_guestA boolean extracted from the ecommerce user context to ascertain whether a user is a guest or not.boolean
Code
Source
{{
  config(
    tags=["this_run"],
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
  )
}}

select
  -- event fields
  event_id,
  page_view_id,

  -- session fields
  domain_sessionid,
  event_in_session_index,

  -- user fields
  domain_userid,
  network_userid,
  user_id,
  ecommerce_user_id,

  -- timestamp fields
  derived_tstamp,
  DATE(derived_tstamp) as derived_tstamp_date,

  -- ecommerce action fields
  ecommerce_action_type,
  ecommerce_action_name,
  ecommerce_page_type,

  -- checkout step fields
  CASE WHEN ecommerce_action_type = 'transaction' THEN {{ var("snowplow__number_checkout_steps", 4) }}
    ELSE checkout_step_number END as checkout_step_number,
  checkout_account_type,
  checkout_billing_full_address,
  checkout_billing_postcode,
  checkout_coupon_code,
  checkout_delivery_method,
  checkout_delivery_provider,
  checkout_marketing_opt_in,
  checkout_payment_method,
  checkout_proof_of_payment,
  checkout_shipping_full_address,
  checkout_shipping_postcode,
  event_in_session_index = 1 as session_entered_at_step,
  ecommerce_action_type = 'transaction' as checkout_succeeded,

  -- ecommerce user fields
  ecommerce_user_email,
  ecommerce_user_is_guest

from {{ ref("snowplow_ecommerce_base_events_this_run") }}
where ecommerce_action_type IN ('transaction', 'checkout_step') -- the two checkout step action types. Either you've initiated the checkout or you've finished with a transaction step

Depends On

Referenced By

Snowplow Ecommerce Incremental Manifest​

models/base/manifest/snowplow_ecommerce_incremental_manifest.sql

Description

This incremental table is a manifest of the timestamp of the latest event consumed per model within the Snowplow dbt ecommerce 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 timestamp of the latest event consumed by the model, based on collector_tstamptimestamp_ntz
Code
Source
{{
  config(
    materialized='incremental',
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt')),
    full_refresh=snowplow_ecommerce.allow_refresh(),
    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

Referenced By

Snowplow Ecommerce Product Interactions​

models/products/snowplow_ecommerce_product_interactions.sql

Description

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

Type: Table

Details

Columns
Column NameDescriptionType
product_event_idA surrogate key which is a combination of product_id and event_idtext
event_idA UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
page_view_idA UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_sessionidA visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
event_in_session_indexThe index of the event in the corresponding session.number
domain_useridUser ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
network_useridUser ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’text
user_idUnique ID set by business e.g. ‘jon.doe@email.com’text
ecommerce_user_idThe ecommerce user id extracted from the ecommerce user context.text
derived_tstampTimestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’timestamp_ntz
derived_tstamp_dateDate value of the timestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’date
product_idThe SKU or product ID.text
product_categoryThe category the product belongs to. Use a consistent separator to express multiple levels. E.g. Woman/Shoes/Sneakerstext
product_subcategory_1text
product_subcategory_2text
product_subcategory_3text
product_subcategory_4text
product_currencyThe currency in which the product is being priced (ISO 4217).text
product_priceThe price of the product at the current time.number
product_brandThe brand of the product.text
product_creative_idIdentifier/Name/Url for the creative presented on a list or product view.text
product_inventory_statusThe inventory status of the product E.g. in stock, out of stock, preorder, backorder.text
product_list_priceThe list or recommended retail price of a product.number
product_nameThe name or title of the producttext
product_list_positionThe position the product was presented in a list of products E.g. search results, product list page.number
product_quantityThe quantity of the product taking part in the ecommerce action.number
product_sizeThe size of the product.text
product_variantThe variant of the product.text
is_product_viewA boolean to describe whether this product interaction was a product viewboolean
product_view_typeThe type of product view that occurred, e.g. list_view or product_viewtext
is_add_to_cartA boolean to describe whether this product interaction was an add to cart actionboolean
is_remove_from_cartA boolean to describe whether this product interaction was a remove from cart actionboolean
product_list_nameThe name of the list presented to the user E.g. product list, search results, shop the look, frequently bought with.text
is_product_transactionA boolean to describe whether this product interaction was a transaction (purchase) interactionboolean
transaction_idThe ID of the transactiontext
ecommerce_user_emailThe ecommerce user email, extracted from the ecommerce user context.text
ecommerce_user_is_guestA boolean extracted from the ecommerce user context to ascertain whether a user is a guest or not.boolean
Code
Source
{{
  config(
    materialized="incremental",
    unique_key='product_event_id',
    upsert_date_key='derived_tstamp',
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt')),
    partition_by = snowplow_utils.get_value_by_target_type(bigquery_val = {
      "field": "derived_tstamp",
      "data_type": "timestamp"
    }, databricks_val='derived_tstamp_date'),
    tags=["derived"],
    tblproperties={
      'delta.autoOptimize.optimizeWrite' : 'true',
      'delta.autoOptimize.autoCompact' : 'true'
    },
    snowplow_optimize=true
  )
}}

select *

from {{ ref('snowplow_ecommerce_product_interactions_this_run') }}
where {{ snowplow_utils.is_run_with_new_events('snowplow_ecommerce') }}

Depends On

Snowplow Ecommerce Product Interactions This Run​

models/products/scratch/<adaptor>/snowplow_ecommerce_product_interactions_this_run.sql

Description

This staging table tracks and stores information about product interactions that occurred within the current run, such as a user viewing a product on a product page, or in a product list. It possesses all of the same columns as snowplow_ecommerce_product_interactions. If building a custom module that requires checkout interactions, this is the table you should reference for that information.

Type: Table

File Paths

models/products/scratch/default/snowplow_ecommerce_product_interactions_this_run.sql

Details

Columns
Column NameDescriptionType
product_event_idA surrogate key which is a combination of product_id and event_idtext
event_idA UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
page_view_idA UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_sessionidA visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
event_in_session_indexThe index of the event in the corresponding session.number
domain_useridUser ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
network_useridUser ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’text
user_idUnique ID set by business e.g. ‘jon.doe@email.com’text
ecommerce_user_idThe ecommerce user id extracted from the ecommerce user context.text
derived_tstampTimestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’timestamp_ntz
derived_tstamp_dateDate value of the timestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’date
product_idThe SKU or product ID.text
product_categoryThe category the product belongs to. Use a consistent separator to express multiple levels. E.g. Woman/Shoes/Sneakerstext
product_subcategory_1text
product_subcategory_2text
product_subcategory_3text
product_subcategory_4text
product_currencyThe currency in which the product is being priced (ISO 4217).text
product_priceThe price of the product at the current time.number
product_brandThe brand of the product.text
product_creative_idIdentifier/Name/Url for the creative presented on a list or product view.text
product_inventory_statusThe inventory status of the product E.g. in stock, out of stock, preorder, backorder.text
product_list_priceThe list or recommended retail price of a product.number
product_nameThe name or title of the producttext
product_list_positionThe position the product was presented in a list of products E.g. search results, product list page.number
product_quantityThe quantity of the product taking part in the ecommerce action.number
product_sizeThe size of the product.text
product_variantThe variant of the product.text
is_product_viewA boolean to describe whether this product interaction was a product viewboolean
product_view_typeThe type of product view that occurred, e.g. list_view or product_viewtext
is_add_to_cartA boolean to describe whether this product interaction was an add to cart actionboolean
is_remove_from_cartA boolean to describe whether this product interaction was a remove from cart actionboolean
product_list_nameThe name of the list presented to the user E.g. product list, search results, shop the look, frequently bought with.text
is_product_transactionA boolean to describe whether this product interaction was a transaction (purchase) interactionboolean
transaction_idThe ID of the transactiontext
ecommerce_user_emailThe ecommerce user email, extracted from the ecommerce user context.text
ecommerce_user_is_guestA boolean extracted from the ecommerce user context to ascertain whether a user is a guest or not.boolean
Code
Source
{{
  config(
    tags=["this_run"],
    sort='derived_tstamp',
    dist='product_event_id'
  )
}}

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


with {{ snowplow_utils.get_sde_or_context(var('snowplow__atomic_schema', 'atomic'), var('snowplow__context_ecommerce_product'), lower_limit, upper_limit, 'ecommerce_product', single_entity = false) }},

product_info as (
  select
    {{ dbt_utils.generate_surrogate_key(['t.event_id', 'r.ecommerce_product_id', 'r.ecommerce_product__index']) }} as product_event_id,
    t.event_id,
    t.page_view_id,

    -- session fields
    t.domain_sessionid,
    t.event_in_session_index,

    -- user fields
    t.domain_userid,
    t.network_userid,
    t.user_id,
    t.ecommerce_user_id,

    -- timestamp fields
    t.derived_tstamp,
    DATE(derived_tstamp) as derived_tstamp_date,

    -- ecommerce product fields
    r.ecommerce_product_id as product_id,
    r.ecommerce_product_category as product_category,
    {{ snowplow_utils.get_split_to_array('ecommerce_product_category', 'r', var('snowplow__categories_separator', '/')) }} as product_categories_split,
    r.ecommerce_product_currency as product_currency,
    r.ecommerce_product_price as product_price,
    r.ecommerce_product_brand as product_brand,
    r.ecommerce_product_creative_id as product_creative_id,
    r.ecommerce_product_inventory_status as product_inventory_status,
    r.ecommerce_product_list_price as product_list_price,
    r.ecommerce_product_name as product_name,
    r.ecommerce_product_position as product_list_position,
    r.ecommerce_product_quantity as product_quantity,
    r.ecommerce_product_size as product_size,
    r.ecommerce_product_variant as product_variant,
    t.ecommerce_action_type,
    t.ecommerce_action_name,

    -- ecommerce action booleans
    t.ecommerce_action_type IN ('product_view', 'list_view') as is_product_view,
    CASE WHEN t.ecommerce_action_type IN ('product_view', 'list_view') THEN t.ecommerce_action_type END as product_view_type,
    t.ecommerce_action_type = 'add_to_cart' as is_add_to_cart,
    t.ecommerce_action_type = 'remove_from_cart' as is_remove_from_cart,
    CASE WHEN t.ecommerce_action_type = 'list_view' THEN t.ecommerce_action_name END as product_list_name,
    t.ecommerce_action_type = 'transaction' as is_product_transaction,

    t.ecommerce_user_is_guest,
    t.ecommerce_user_email,
    t.transaction_id


  from {{ ref('snowplow_ecommerce_base_events_this_run') }} t
  inner join {{ var('snowplow__context_ecommerce_product') }} r on t.event_id = r.ecommerce_product__id and t.collector_tstamp = r.ecommerce_product__tstamp and mod(r.ecommerce_product__index, t.event_id_dedupe_count) = 0 -- ensure only a single match per total number of dupes

)

select
  product_event_id,
  -- event fields
  event_id,
  page_view_id,

  -- session fields
  domain_sessionid,
  event_in_session_index,

  -- user fields
  domain_userid,
  network_userid,
  user_id,
  ecommerce_user_id,

  -- timestamp fields
  derived_tstamp,
  derived_tstamp_date,

  -- ecommerce product fields
  product_id,
  product_category,
  {%- for i in range(var("snowplow__number_category_levels", 4)) %}
  {% if target.type in ['postgres'] %}
  product_categories_split[{{i+1}}]::varchar as product_subcategory_{{i+1}},
  {% else %}
  product_categories_split[{{i}}]::varchar as product_subcategory_{{i+1}},
  {% endif %}
  {%- endfor %}
  product_currency,
  product_price,
  product_brand,
  product_creative_id,
  product_inventory_status,
  product_list_price,
  product_name,
  product_list_position,
  product_quantity,
  product_size,
  product_variant,

  -- ecommerce action booleans
  is_product_view,
  product_view_type,
  is_add_to_cart,
  is_remove_from_cart,
  product_list_name,
  is_product_transaction,

  -- transaction and user fields
  transaction_id,
  ecommerce_user_email,
  ecommerce_user_is_guest

from product_info

Depends On

Referenced By

Snowplow Ecommerce Sessions​

models/sessions/snowplow_ecommerce_sessions.sql

Description

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

Type: Table

Details

Columns
Column NameDescriptionType
domain_sessionidA visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_useridUser ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
start_tstampThe first time the session interacted with the websitetimestamp_ntz
end_tstampThe last time the session interacted with the websitetimestamp_ntz
number_unique_cart_idsThe total number of unique cart_ids over the session's lifetimenumber
number_carts_createdThe total number of carts created over the session's lifetimenumber
number_carts_emptiedThe total number of carts emptied over the session's lifetimenumber
number_carts_transactedThe total number of carts transacted over the session's lifetimenumber
first_cart_createdThe timestamp of when the session first created a carttimestamp_ntz
last_cart_createdThe timestamp of when the session last created a carttimestamp_ntz
first_cart_transactedThe timestamp of when the session first transacted (purchased) the contents of a carttimestamp_ntz
last_cart_transactedThe timestamp of when the session last transacted (purchased) the contents of a carttimestamp_ntz
session_cart_abandonedA boolean to describe whether the session's cart was abandonedboolean
session_entered_at_checkoutboolean
number_unique_checkout_steps_attemptedThe total distinct number of checkout steps that a session went throughnumber
number_checkout_steps_visitedThe total number of (non-unique) checkout steps that a session went throughnumber
checkout_succeededA boolean to describe whether the checkout succeededboolean
first_checkout_attemptedThe timestamp of when the session first attempted any checkout stepstimestamp_ntz
last_checkout_attemptedThe timestamp of when the session last attempted any checkout stepstimestamp_ntz
first_checkout_succeededThe timestamp of when the session first succeeded in checking out productstimestamp_ntz
last_checkout_succeededThe timestamp of when the session last succeeded in checking out productstimestamp_ntz
first_product_viewThe timestamp of when the session first viewed a producttimestamp_ntz
last_product_viewThe timestamp of when the session last viewed a producttimestamp_ntz
first_product_add_to_cartThe timestamp of when the session first added a product to their carttimestamp_ntz
last_product_add_to_cartThe timestamp of when the session last added a product to their carttimestamp_ntz
first_product_remove_from_cartThe timestamp of when the session first removed a product from their carttimestamp_ntz
last_product_remove_from_cartThe timestamp of when the session last removed a product from their carttimestamp_ntz
first_product_transactionThe timestamp of when the session first purchased a producttimestamp_ntz
last_product_transactionThe timestamp of when the session last purchased a producttimestamp_ntz
number_product_viewsThe number of product views that occurred within a sessionnumber
number_add_to_cartsThe number of add to cart actions that occurred within a sessionnumber
number_remove_from_cartsThe number of remove from cart actions that occurred within a sessionnumber
number_product_transactionsThe number of product transaction actions that occurred within a sessionnumber
first_transaction_completedThe timestamp of when the session first completed a transactiontimestamp_ntz
last_transaction_completedThe timestamp of when the session last completed a transactiontimestamp_ntz
total_transaction_revenueThe total amount of revenue coming from transactions over the session's lifetimenumber
total_transaction_quantityThe total quantity of products coming from transactions over the session's lifetimenumber
total_number_transactionsThe total number of transactions over the session's lifetimenumber
total_transacted_productsThe total number of transacted products over the session's lifetimenumber
Code
Source
{{
  config(
    materialized="incremental",
    unique_key='domain_sessionid',
    upsert_date_key='start_tstamp',
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt')),
    partition_by = snowplow_utils.get_value_by_target_type(bigquery_val = {
      "field": "start_tstamp",
      "data_type": "timestamp"
    }, databricks_val='start_tstamp_date'),
    tags=["derived"],
    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_ecommerce_sessions_this_run') }}
where {{ snowplow_utils.is_run_with_new_events('snowplow_ecommerce') }}

Depends On

Snowplow Ecommerce Sessions This Run​

models/sessions/scratch/snowplow_ecommerce_sessions_this_run.sql

Description

This staging table tracks and stores aggregate information about sessions that occurred within the current run. It possesses all of the same columns as snowplow_ecommerce_sessions. If building a custom module that requires session level data, this is the table you should reference for that information.

Type: Table

Details

Columns
Column NameDescriptionType
domain_sessionidA visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_useridUser ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
start_tstampThe first time the session interacted with the websitetimestamp_ntz
end_tstampThe last time the session interacted with the websitetimestamp_ntz
number_unique_cart_idsThe total number of unique cart_ids over the session's lifetimenumber
number_carts_createdThe total number of carts created over the session's lifetimenumber
number_carts_emptiedThe total number of carts emptied over the session's lifetimenumber
number_carts_transactedThe total number of carts transacted over the session's lifetimenumber
first_cart_createdThe timestamp of when the session first created a carttimestamp_ntz
last_cart_createdThe timestamp of when the session last created a carttimestamp_ntz
first_cart_transactedThe timestamp of when the session first transacted (purchased) the contents of a carttimestamp_ntz
last_cart_transactedThe timestamp of when the session last transacted (purchased) the contents of a carttimestamp_ntz
session_cart_abandonedA boolean to describe whether the session's cart was abandonedboolean
session_entered_at_checkoutA boolean to describe whether the session started within the checkoutboolean
number_unique_checkout_steps_attemptedThe total distinct number of checkout steps that a session went throughnumber
number_checkout_steps_visitedThe total number of (non-unique) checkout steps that a session went throughnumber
checkout_succeededA boolean to describe whether the checkout succeededboolean
first_checkout_attemptedThe timestamp of when the session first attempted any checkout stepstimestamp_ntz
last_checkout_attemptedThe timestamp of when the session last attempted any checkout stepstimestamp_ntz
first_checkout_succeededThe timestamp of when the session first succeeded in checking out productstimestamp_ntz
last_checkout_succeededThe timestamp of when the session last succeeded in checking out productstimestamp_ntz
first_product_viewThe timestamp of when the session first viewed a producttimestamp_ntz
last_product_viewThe timestamp of when the session last viewed a producttimestamp_ntz
first_product_add_to_cartThe timestamp of when the session first added a product to their carttimestamp_ntz
last_product_add_to_cartThe timestamp of when the session last added a product to their carttimestamp_ntz
first_product_remove_from_cartThe timestamp of when the session first removed a product from their carttimestamp_ntz
last_product_remove_from_cartThe timestamp of when the session last removed a product from their carttimestamp_ntz
first_product_transactionThe timestamp of when the session first purchased a producttimestamp_ntz
last_product_transactionThe timestamp of when the session last purchased a producttimestamp_ntz
number_product_viewsThe number of product views that occurred within a sessionnumber
number_add_to_cartsThe number of add to cart actions that occurred within a sessionnumber
number_remove_from_cartsThe number of remove from cart actions that occurred within a sessionnumber
number_product_transactionsThe number of product transaction actions that occurred within a sessionnumber
first_transaction_completedThe timestamp of when the session first completed a transactiontimestamp_ntz
last_transaction_completedThe timestamp of when the session last completed a transactiontimestamp_ntz
total_transaction_revenueThe total amount of revenue coming from transactions over the session's lifetimenumber
total_transaction_quantityThe total quantity of products coming from transactions over the session's lifetimenumber
total_number_transactionsThe total number of transactions over the session's lifetimenumber
total_transacted_productsThe total number of transacted products over the session's lifetimenumber
Code
Source
{{
    config(
        tags=["this_run"],
        sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
    )
}}

with cart_session_stats AS (
    {% if var('snowplow__disable_ecommerce_carts', false) -%}
        select
        CAST(NULL as {{ type_string() }}) as domain_sessionid,
        CAST(NULL as {{ type_int() }}) as number_unique_cart_ids,
        CAST(NULL as {{ type_int() }}) as number_carts_created,
        CAST(NULL as {{ type_int() }}) as number_carts_emptied,
        CAST(NULL as {{ type_int() }}) as number_carts_transacted,
        CAST(NULL as {{ type_timestamp() }}) as first_cart_created,
        CAST(NULL as {{ type_timestamp() }}) as last_cart_created,
        CAST(NULL as {{ type_timestamp() }}) as first_cart_transacted,
        CAST(NULL as {{ type_timestamp() }}) as last_cart_transacted,
        CAST(NULL as {{ type_boolean() }}) as session_cart_abandoned

    {%- else -%}
        select
            t.*,
            number_carts_transacted < number_carts_created as session_cart_abandoned

        from (
            select domain_sessionid,

            COUNT(DISTINCT cart_id) as number_unique_cart_ids,
            COUNT(DISTINCT CASE WHEN cart_created THEN event_id END) as number_carts_created,
            COUNT(DISTINCT CASE WHEN cart_emptied THEN event_id END) as number_carts_emptied,
            COUNT(DISTINCT CASE WHEN cart_transacted THEN event_id END) as number_carts_transacted,

            MIN(CASE WHEN cart_created THEN derived_tstamp END) as first_cart_created,
            MAX(CASE WHEN cart_created THEN derived_tstamp END) as last_cart_created,

            MIN(CASE WHEN cart_transacted THEN derived_tstamp END) as first_cart_transacted,
            MAX(CASE WHEN cart_transacted THEN derived_tstamp END) as last_cart_transacted


            from {{ ref('snowplow_ecommerce_cart_interactions_this_run') }}
            group by 1

        ) as t
    {%- endif %}
), checkout_session_stats AS (
    {% if var('snowplow__disable_ecommerce_checkouts', false) -%}
       select
            CAST(NULL as {{ type_string() }}) as domain_sessionid,
            CAST(NULL as {{ type_boolean() }}) as session_entered_at_checkout,
            CAST(NULL as {{ type_int() }}) as number_unique_checkout_steps_attempted,
            CAST(NULL as {{ type_int() }}) as number_checkout_steps_visited,
            CAST(NULL as {{ type_boolean() }}) as checkout_succeeded,
            CAST(NULL as {{ type_timestamp() }}) as first_checkout_attempted,
            CAST(NULL as {{ type_timestamp() }}) as last_checkout_attempted,
            CAST(NULL as {{ type_timestamp() }}) as first_checkout_succeeded,
            CAST(NULL as {{ type_timestamp() }}) as last_checkout_succeeded
    {%- else -%}
     select
            domain_sessionid,

            CAST(MAX(CAST(session_entered_at_step as {{ type_int() }})) as {{ type_boolean() }}) as session_entered_at_checkout,
            COUNT(DISTINCT checkout_step_number) as number_unique_checkout_steps_attempted,
            COUNT(DISTINCT event_id) as number_checkout_steps_visited,
            CAST(MAX(CAST(checkout_succeeded as {{ type_int() }})) as {{ type_boolean() }}) as checkout_succeeded,

            MIN(CASE WHEN checkout_step_number = 1 THEN derived_tstamp END) as first_checkout_attempted,
            MAX(CASE WHEN checkout_step_number = 1 THEN derived_tstamp END) as last_checkout_attempted,
            MIN(CASE WHEN checkout_succeeded THEN derived_tstamp END) as first_checkout_succeeded,
            MAX(CASE WHEN checkout_succeeded THEN derived_tstamp END) as last_checkout_succeeded

        from {{ ref('snowplow_ecommerce_checkout_interactions_this_run') }}
        group by 1

    {%- endif %}
), product_session_stats AS (
    {% if var('snowplow__disable_ecommerce_products', false) -%}
        select
            CAST(NULL as {{ type_string() }}) AS domain_sessionid,
            CAST(NULL as {{ type_timestamp() }}) AS first_product_view,
            CAST(NULL as {{ type_timestamp() }}) AS last_product_view,
            CAST(NULL as {{ type_timestamp() }}) AS first_product_add_to_cart,
            CAST(NULL as {{ type_timestamp() }}) AS last_product_add_to_cart,
            CAST(NULL as {{ type_timestamp() }}) AS first_product_remove_from_cart,
            CAST(NULL as {{ type_timestamp() }}) AS last_product_remove_from_cart,
            CAST(NULL as {{ type_timestamp() }}) AS first_product_transaction,
            CAST(NULL as {{ type_timestamp() }}) AS last_product_transaction,
            CAST(NULL as {{ type_int() }}) AS number_product_views,
            CAST(NULL as {{ type_int() }}) AS number_add_to_carts,
            CAST(NULL as {{ type_int() }}) AS number_remove_from_carts,
            CAST(NULL as {{ type_int() }}) AS number_product_transactions,
            CAST(NULL as {{ type_int() }}) AS number_distinct_products_viewed
    {%- else -%}
        select
            domain_sessionid,

            MIN(CASE WHEN is_product_view THEN derived_tstamp END) AS first_product_view,
            MAX(CASE WHEN is_product_view THEN derived_tstamp END) AS last_product_view,
            MIN(CASE WHEN is_add_to_cart THEN derived_tstamp END) AS first_product_add_to_cart,
            MAX(CASE WHEN is_add_to_cart THEN derived_tstamp END) AS last_product_add_to_cart,
            MIN(CASE WHEN is_remove_from_cart THEN derived_tstamp END) AS first_product_remove_from_cart,
            MAX(CASE WHEN is_remove_from_cart THEN derived_tstamp END) AS last_product_remove_from_cart,
            MIN(CASE WHEN is_product_transaction THEN derived_tstamp END) AS first_product_transaction,
            MAX(CASE WHEN is_product_transaction THEN derived_tstamp END) AS last_product_transaction,

            COUNT(DISTINCT CASE WHEN is_product_view THEN event_id END) AS number_product_views,
            COUNT(DISTINCT CASE WHEN is_add_to_cart THEN event_id END) AS number_add_to_carts,
            COUNT(DISTINCT CASE WHEN is_remove_from_cart THEN event_id END) AS number_remove_from_carts,
            COUNT(DISTINCT CASE WHEN is_product_transaction THEN event_id END) AS number_product_transactions,
            COUNT(DISTINCT CASE WHEN is_product_view THEN product_id END) as number_distinct_products_viewed


        from {{ ref('snowplow_ecommerce_product_interactions_this_run') }}
        group by 1

    {%- endif %}
), transaction_session_stats AS (
    {% if var('snowplow__disable_ecommerce_transactions', false) -%}
        select
            CAST(NULL as {{ type_string() }}) AS domain_sessionid,
            CAST(NULL as {{ type_timestamp() }}) AS first_transaction_completed,
            CAST(NULL as {{ type_timestamp() }}) AS last_transaction_completed,
            CAST(NULL as {{ type_float() }}) AS total_transaction_revenue,
            CAST(NULL as {{ type_int() }}) AS total_transaction_quantity,
            CAST(NULL as {{ type_int() }}) AS total_number_transactions,
            CAST(NULL as {{ type_int() }}) AS total_transacted_products

    {%- else -%}
         select
            domain_sessionid,

            MIN(derived_tstamp) AS first_transaction_completed,
            MAX(derived_tstamp) AS last_transaction_completed,
            SUM(transaction_revenue) as total_transaction_revenue,
            SUM(transaction_total_quantity) as total_transaction_quantity,
            COUNT(DISTINCT transaction_id) as total_number_transactions,
            SUM(number_products) as total_transacted_products

        from {{ ref('snowplow_ecommerce_transaction_interactions_this_run') }}
        group by 1
    {%- endif %}
)
select
    s.session_id as domain_sessionid,
    s.domain_userid,
    s.start_tstamp,
    s.end_tstamp,

    css.number_unique_cart_ids,
    css.number_carts_created,
    css.number_carts_emptied,
    css.number_carts_transacted,

    css.first_cart_created,
    css.last_cart_created,
    css.first_cart_transacted,
    css.last_cart_transacted,
    css.session_cart_abandoned,

    chss.session_entered_at_checkout,
    chss.number_unique_checkout_steps_attempted,
    chss.number_checkout_steps_visited,
    chss.checkout_succeeded,
    chss.first_checkout_attempted,
    chss.last_checkout_attempted,
    chss.first_checkout_succeeded,
    chss.last_checkout_succeeded,

    pss.first_product_view,
    pss.last_product_view,
    pss.first_product_add_to_cart,
    pss.last_product_add_to_cart,
    pss.first_product_remove_from_cart,
    pss.last_product_remove_from_cart,
    pss.first_product_transaction,
    pss.last_product_transaction,
    pss.number_product_views,
    pss.number_add_to_carts,
    pss.number_remove_from_carts,
    pss.number_product_transactions,

    tss.first_transaction_completed,
    tss.last_transaction_completed,
    tss.total_transaction_revenue,
    tss.total_transaction_quantity,
    tss.total_number_transactions,
    tss.total_transacted_products



from {{ ref('snowplow_ecommerce_base_sessions_this_run') }} as s
left join cart_session_stats as css on s.session_id = css.domain_sessionid
left join checkout_session_stats as chss on s.session_id = chss.domain_sessionid
left join product_session_stats as pss on s.session_id = pss.domain_sessionid
left join transaction_session_stats as tss on s.session_id = tss.domain_sessionid

Depends On

Referenced By

Snowplow Ecommerce Transaction Interactions​

models/transactions/snowplow_ecommerce_transaction_interactions.sql

Description

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

Type: Table

Details

Columns
Column NameDescriptionType
event_idA UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
page_view_idA UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_sessionidA visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
event_in_session_indexThe index of the event in the corresponding session.number
domain_useridUser ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
network_useridUser ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’text
user_idUnique ID set by business e.g. ‘jon.doe@email.com’text
ecommerce_user_idThe ecommerce user id extracted from the ecommerce user context.text
derived_tstampTimestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’timestamp_ntz
derived_tstamp_dateDate value of the timestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’date
transaction_idThe ID of the transactiontext
transaction_currencyThe currency used for the transaction (ISO 4217).text
transaction_payment_methodThe payment method used for the transaction.text
transaction_revenueThe revenue of the transaction.number
transaction_total_quantityTotal quantity of items in the transaction.number
transaction_credit_orderWhether the transaction is a credit order or not.boolean
transaction_discount_amountDiscount amount taken offnumber
transaction_discount_codeDiscount code used.text
transaction_shippingTotal cost of shipping on the transaction.number
transaction_taxTotal amount of tax on the transaction.number
ecommerce_user_emailThe ecommerce user email, extracted from the ecommerce user context.text
ecommerce_user_is_guestA boolean extracted from the ecommerce user context to ascertain whether a user is a guest or not.boolean
number_productsThe number of products that are contained in a transaction, counted from the product interactionsnumber
Code
Source
{{
  config(
    materialized="incremental",
    unique_key='transaction_id',
    upsert_date_key='derived_tstamp',
    sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt')),
    partition_by = snowplow_utils.get_value_by_target_type(bigquery_val = {
      "field": "derived_tstamp",
      "data_type": "timestamp"
    }, databricks_val='derived_tstamp_date'),
    tags=["derived"],
    tblproperties={
      'delta.autoOptimize.optimizeWrite' : 'true',
      'delta.autoOptimize.autoCompact' : 'true'
    },
    snowplow_optimize=true
  )
}}

select *

from {{ ref('snowplow_ecommerce_transaction_interactions_this_run') }}
where {{ snowplow_utils.is_run_with_new_events('snowplow_ecommerce') }}

Depends On

Snowplow Ecommerce Transaction Interactions This Run​

models/transactions/scratch/snowplow_ecommerce_transaction_interactions_this_run.sql

Description

This staging table tracks and stores information about transaction interactions that occurred within the current run, with interactions being when a user completes a transaction. It possesses all of the same columns as snowplow_ecommerce_transaction_interactions. If building a custom module that requires checkout interactions, this is the table you should reference for that information.

Type: Table

Details

Columns
Column NameDescriptionType
event_idA UUID for each event e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
page_view_idA UUID for each page view e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
domain_sessionidA visit / session UUID e.g. ‘c6ef3124-b53a-4b13-a233-0088f79dcbcb’text
event_in_session_indexThe index of the event in the corresponding session.number
domain_useridUser ID set by Snowplow using 1st party cookie e.g. ‘bc2e92ec6c204a14’text
network_useridUser ID set by Snowplow using 3rd party cookie e.g. ‘ecdff4d0-9175-40ac-a8bb-325c49733607’text
user_idUnique ID set by business e.g. ‘jon.doe@email.com’text
ecommerce_user_idThe ecommerce user id extracted from the ecommerce user context.text
derived_tstampTimestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’timestamp_ntz
derived_tstamp_dateDate value of the timestamp making allowance for innaccurate device clock e.g. ‘2013-11-26 00:02:04’date
transaction_idThe ID of the transactiontext
transaction_currencyThe currency used for the transaction (ISO 4217).text
transaction_payment_methodThe payment method used for the transaction.text
transaction_revenueThe revenue of the transaction.number
transaction_total_quantityTotal quantity of items in the transaction.number
transaction_credit_orderWhether the transaction is a credit order or not.boolean
transaction_discount_amountDiscount amount taken offnumber
transaction_discount_codeDiscount code used.text
transaction_shippingTotal cost of shipping on the transaction.number
transaction_taxTotal amount of tax on the transaction.number
ecommerce_user_emailThe ecommerce user email, extracted from the ecommerce user context.text
ecommerce_user_is_guestA boolean extracted from the ecommerce user context to ascertain whether a user is a guest or not.boolean
number_productsThe number of products that are contained in a transaction, counted from the product interactionsnumber
Code
Source
{{
    config(
        tags=["this_run"],
        sql_header=snowplow_utils.set_query_tag(var('snowplow__query_tag', 'snowplow_dbt'))
    )
}}

with transaction_info AS (
    select
        -- event fields
        e.event_id,
        e.page_view_id,

        -- session fields
        e.domain_sessionid,
        e.event_in_session_index,

        -- user fields
        e.domain_userid,
        e.network_userid,
        e.user_id,
        e.ecommerce_user_id,

        -- timestamp fields
        e.derived_tstamp,
        DATE(e.derived_tstamp) as derived_tstamp_date,

        -- ecommerce transaction fields
        e.transaction_id as transaction_id,
        e.transaction_currency as transaction_currency,
        e.transaction_payment_method,
        e.transaction_revenue,
        e.transaction_total_quantity,
        e.transaction_credit_order,
        e.transaction_discount_amount,
        e.transaction_discount_code,
        e.transaction_shipping,
        e.transaction_tax,

        -- ecommerce user fields
        e.ecommerce_user_email,
        e.ecommerce_user_is_guest,

        {% if (var("snowplow__use_product_quantity", false) and not var("snowplow__disable_ecommerce_products", false) | as_bool() )  -%}
        SUM(pi.product_quantity) as number_products
        {%- else -%}
        COUNT(*) as number_products -- count all products added
        {%- endif %}

    from {{ ref('snowplow_ecommerce_base_events_this_run') }} as e
    {% if not var("snowplow__disable_ecommerce_products", false)  -%}
    left join {{ ref('snowplow_ecommerce_product_interactions_this_run') }} as pi on e.transaction_id = pi.transaction_id AND pi.is_product_transaction
    {%- endif %}
    where ecommerce_action_type = 'transaction'
    group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22
)

select *

from transaction_info

Depends On

Referenced By

Was this page helpful?