Is there a way to join page ping events to page views?

For data modelers & consumers Redshift
9

Hi @ryanrozich,

You can join atomic.events and atomic.com_snowplowanalytics_snowplow_web_page_1 (and all other atomic tables) on event_id = root_id. For example:

SELECT
  ev.domain_userid,
  ev.domain_sessionidx,
  ev.derived_tstamp,
  ev.event_name,
  wp.id AS page_view_id
FROM atomic.events AS ev
INNER JOIN atomic.com_snowplowanalytics_snowplow_web_page_1 AS wp
  ON ev.event_id = wp.root_id
LIMIT 250

You can think of the unstructured event and context tables in atomic as extensions of atomic.events (with a 1-to-1 join on the event/root ID). So you are correct in your last post. The ID in the web page context is the “page view ID” - all events that happen on the same page view (or load) have the same page view ID (whether it’s the initial page view event that gets sent as soon as the tracker loads, subsequent page ping events, link click events, or any structured or unstructured event).

You might need to join on ON ev.event_id = wp.root_id AND ev.collector_tstamp = wp.root_tstamp if there are some duplicates in both tables. This is an issue that is explained here: Dealing with duplicate event IDs for joining contexts to atomic.events