4. Pageviews

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:

Field name Type  
date DATE  
pseudo_session_id STRING  
hitId STRING  
time INTEGER  
pageviews INTEGER  
event_name STRING  
eventTrafficSourceHash STRING  

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.