4. Pageviews

NEW documentation: This article covers setup for OWOX BI. For the latest guides on setting up FREE connectors and Data Marts, visit the new OWOX Data Marts documentation.

Use the SQL code below and change {{projectId}} and {{datasetName}} to your own.

#Preparing Pageviews object
  WITH input AS
    (
      SELECT
          PARSE_DATE('%Y%m%d','20220901') AS date_start,
          PARSE_DATE('%Y%m%d','20220930') AS date_end
    ) 
 
  SELECT
    date, 
    pseudo_session_id, 
    TO_HEX(MD5(CONCAT(user_pseudo_id,'_',events.timestamp,'_',ROW_NUMBER() OVER (PARTITION BY user_pseudo_id)))) AS hitId,
    events.timestamp AS time,
    1 as pageviews,
    event_name,
    CAST(FARM_FINGERPRINT(CONCAT( --deviceHash
          IFNULL(events.source, ''),
          IFNULL(events.medium, ''),
          IFNULL(events.campaign, ''),
          IFNULL(events.keyword, ''),
          IFNULL(events.adContent , '')
        )) AS STRING) AS eventTrafficSourceHash
  FROM input as i, `{{projectId}}.{{datasetName}}._temp_sessions`,
  UNNEST (events) as events
  WHERE date BETWEEN i.date_start AND i.date_end AND event_name='page_view'

You will get the data in the table 'pageviews' with the schema below: