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 |
0 Comments