# Snowplow Web dbt package
> Legacy Web dbt package for web event data modeling. Superseded by the Unified Digital package.
> Source: https://docs.snowplow.io/docs/modeling-your-data/modeling-your-data-with-dbt/dbt-models/legacy/dbt-web-data-model/
> Documentation index: https://docs.snowplow.io/llms.txt

[v1.0.1](https://github.com/snowplow/dbt-snowplow-web "Latest Web package version") · [Maintained](/docs/sources/tracker-maintenance-classification/ "Production ready, released less often") · [Snowplow Community License](/community-license-1.0/)

> **Info:** This package has been superseded by the [Unified Digital](/docs/modeling-your-data/modeling-your-data-with-dbt/dbt-models/dbt-unified-data-model/) that combines data from both web and mobile sources. For more information, see the Unified Digital page.

**The package source code can be found in the [snowplow/dbt-snowplow-web repo](https://github.com/snowplow/dbt-snowplow-web), and the docs for the [model design here](https://snowplow.github.io/dbt-snowplow-web/#!/overview/snowplow_web).**

The package contains a fully incremental model that transforms raw web event data generated by the [Snowplow JavaScript tracker](/docs/sources/web-trackers/) into a series of derived tables of varying levels of aggregation.

The Snowplow web data model aggregates Snowplow's out of the box page view and page ping events to create a set of derived tables - page views, sessions and users - that contain many useful dimensions as well as calculated measures such as time engaged and scroll depth.

![Web Package data flow](/assets/images/web-process-light.drawio-a2b276daa0d6a441c3851bc3bb5db941.png)

| snowplow-web version | dbt versions        | BigQuery | Databricks | Redshift | Snowflake | Postgres |
| -------------------- | ------------------- | -------- | ---------- | -------- | --------- | -------- |
| 1.0.1                | >=1.6.0 to <2.0.0   | ✅        | ✅          | ✅        | ✅         | ✅        |
| 0.16.2               | >=1.5.0 to <2.0.0   | ✅        | ✅          | ✅        | ✅         | ✅        |
| 0.15.2               | >=1.4.0 to <2.0.0   | ✅        | ✅          | ✅        | ✅         | ✅\*      |
| 0.13.3\*\*           | >=1.3.0 to <2.0.0   | ✅        | ✅          | ✅        | ✅         | ✅        |
| 0.11.0               | >=1.0.0 to <1.3.0   | ✅        | ✅          | ✅        | ✅         | ✅        |
| 0.5.1                | >=0.20.0 to <1.0.0  | ✅        | ❌          | ✅        | ✅         | ✅        |
| 0.4.1                | >=0.18.0 to <0.20.0 | ✅        | ❌          | ✅        | ✅         | ❌        |

\* Since version 0.15.0 of `snowplow_web` at least version 15.0 of Postgres is required, otherwise you will need to [overwrite](/docs/modeling-your-data/modeling-your-data-with-dbt/package-mechanics/overridable-macros/) the `default_channel_group` macro to not use the `regexp_like` function.

\*\* From version v0.13.0 onwards we use the `load_tstamp` field so you must be using [RDB Loader](/docs/api-reference/loaders-storage-targets/snowplow-rdb-loader/) v4.0.0 and above, or [BigQuery Loader](/docs/api-reference/loaders-storage-targets/snowplow-rdb-loader/) v1.0.0 and above. If you do not have this field because you are not using these versions, or you are using the Postgres loader, you will need to set `snowplow__enable_load_tstamp` to `false` in your `dbt_project.yml` and will not be able to use the consent models.

## Overview

This model consists of a series of modules, each producing a table which serves as the input to the next module. The 'standard' modules are:

- Base: Performs the incremental logic, outputting the table `snowplow_web_base_events_this_run` which contains a de-duped data set of all events required for the current run of the model.
- Page Views: Aggregates event level data to a page view level, `page_view_id`, outputting the table `snowplow_web_page_views`.
- Sessions: Aggregates event level data to a session level, `domain_sessionid`, outputting the table `snowplow_web_sessions`. Includes other events but requires at least one `page_view` or `page_ping` event in the session.
- Users: Aggregates session level data to a users level, `domain_userid`, outputting the table `snowplow_web_users`.
- User Mapping: Provides a mapping between user identifiers, `domain_userid` and `user_id`, outputting the table `snowplow_web_user_mapping`. This can be used for session stitching.

## Optional Modules

See the subsections for information on the optional modules.

## Overridable Macros

> **Tip:** For information about overriding our macros, see [here](/docs/modeling-your-data/modeling-your-data-with-dbt/package-mechanics/overridable-macros/)

- `filter_bots(table_alias)`[source](https://github.com/snowplow/dbt-snowplow-web/blob/main/macros/filter_bots.sql): used to define the filter to remove bot events from events processed by the package. Of the form `and <condition>`. Used throughout the package to filter out bots from all models.
- `channel_group_query()`[source](https://github.com/snowplow/dbt-snowplow-web/blob/main/macros/channel_group_query.sql): defines the channel a user arrived at using various fields, populates the `default_channel_group` field. Must be a valid sql `select` object e.g. a complete `case when` statement. Used in `sessions_this_run` table.
- `engaged_session()`[source](https://github.com/snowplow/dbt-snowplow-web/blob/main/macros/engaged_session.sql): defines if a session was engaged or not, populates the `is_engaged` field. Must return `true` or `false` and be a valid sql `select` object e.g. a complete `case when` statement. Used in `sessions_this_run` table.
- `content_group_query()`[source](https://github.com/snowplow/dbt-snowplow-web/blob/main/macros/channel_group_query.sql): defines the content groups by classifying the page urls for page views. Must be a valid sql `select` object e.g. a complete `case when` statement. Used in `page_views_this_run` table.

## Engaged vs. Absolute Time

At a page view- and session-level we provide two measures of time; **absolute**, how long a user had the page open, and **engaged**, how much of that time the user was on the page. Engaged time is often a large predictor of a customer conversion, such as a purchase or a sign-up, whatever that may be in your domain.

Calculating absolute time is simple, it's the difference between the `derived_tstamp` of the first and last (page view or page ping) events within that page view/session.

The calculation for engaged time is more complicated, it is derived based on page pings which means if the user isn't active on your content, the engaged time does not increase. Let's consider a single page view example of reading an article; partway through the reader may see something they don't understand, so they open a new tab and look this up. They might stumble upon a Wikipedia page on it, they go down a rabbit hole and 10 minutes later they make it back to your site to finish the article. In this case there will be a gap for those 10 minutes in the page pings in the events data.

To adjust for these gaps we calculate engaged time as the time to trigger each ping (your heartbeat) times the number of pings (ignoring the first one), and add to that the time delay to the first ping (your minimum visit length). The formula is:

$$ engaged\_time=t\_{heartbeat}\times (n\_{distinct\_pings} -1) + t\_{min\_visit\_length} $$

and the below shows an example visually for a single page view.

![Page views and pings showing gaps to highlight the difference between absolute and engaged time](/assets/images/engaged_time_light.drawio-7db13a561e8e35da0c9ec1a0d605a344.png)

At a session level, this calculation is slightly more involved, as it needs to happen per page view and account for [stray page pings](#stray-page-pings), but the underlying idea is the same.

## Stray Page Pings

Stray Page Pings are pings within a session that do not have a corresponding `page_view` event within **the same session**. The most common cause of these is someone returning to a tab after their session has timed out but not refreshing the page. The `page_view` event exists in some other session, but there is no guarantee that both these sessions will be processed in the same run, which could lead to different results. Depending on your site content and user behavior the prevalence of sessions with stray page pings could vary greatly. For example with long-form content we have seen around 10% of all sessions contain only stray page pings (i.e. no `page_view` events).

We take different approaches to adjust for these stray pings at the page view and sessions levels, which can lead to differences between the two tables, but each is as accurate as we can currently make it.

### Sessions

As all our processing ensures full sessions are reprocessed, our sessions level table includes all stray page ping events, as well as all other view and ping events. We adjust the start time down based on your minimum visit length if the session starts with a page ping, and we include sessions that contain only (stray) pings. We also count page views based on the number of unique `page_view_ids` you have (from the `web_page` context) rather than using absolute `page_view` events to include these stray pings, and account for stray pings in the engaged time. Overall this is a more accurate view of a session and treats the stray pings as if they had a corresponding `page_view` event in the same session, even when they did not.

The result of this is you may see misalignment between sessions and if you tried to recalculate them based directly off the page views table; this is because we discard stray pings during page view processing as discussed below, so the values (`page_views`, `engaged_time_in_s`, and `absolute_time_in_s`) in the sessions table may be higher, but are more accurate at a session level.

![Stray page ping sessionisation](/assets/images/stray_sessions_light.drawio-fa6d6267e82193eda817072a25e62d82.png)

### Page Views

For page views, because we cannot guarantee the sessions with the `page_view` event and all subsequent `page_ping` events are processed within the same run, we choose to discard all stray page pings. Without doing this it could be possible that you would get different results from different run configurations.

**Without enforcing within-session view**

![Stray page ping page views](/assets/images/stray_views_old-light.drawio-99737733b79738a26736f5958f49625f.png)

**With enforcing within-session view**

![Stray page ping page views](/assets/images/stray_views_new-light.drawio-2cc5a036f12bec0cdc82533701523a75.png)



> **Info:** Currently we do not process these discarded stray page pings in any way, meaning that engaged time and scroll depth in these cases may be under representative of the true value. Due to session level reprocessing this remains a complicated issue to resolve, but please [let us know](https://github.com/snowplow/dbt-snowplow-web/issues) if you would like to help solve this!
