2. Sessions

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

#Preparing sessions object

  WITH input AS
          PARSE_DATE('%Y%m%d','20220901') AS date_start,
          PARSE_DATE('%Y%m%d','20220930') AS date_end

    pseudo_session_id AS pseudo_sessionId,
    session_Id AS sessionId, 
    (SELECT MAX(platform) FROM UNNEST(events) AS e) AS touchpoint,
    sStart as visitStartTime,
    (SELECT MAX(visitNumber) FROM UNNEST(events) AS e) AS visitNumber, 
    IF(session_Id is not null,1,0) as sessions,
    IFNULL((SELECT SUM(hits) FROM UNNEST(events) AS e),0) AS hits,
    IFNULL((SELECT SUM(pageviews) FROM UNNEST(events) AS e),0) AS pageviews,
    IFNULL((SELECT SUM(events) FROM UNNEST(events) AS e),0) AS events,
    IFNULL((SELECT COUNT(DISTINCT transaction_id) FROM UNNEST(events) AS e),0) AS transactions,
    IFNULL((SELECT SUM(transactionRevenue) FROM UNNEST(events) AS e),0) AS transactionRevenue,
    IF((SELECT MAX(not_bounces) FROM UNNEST(events) AS e)=1,0,1) AS bounces,
    sEnd-sStart as timeOnSite,
    IFNULL((SELECT SUM(cost) FROM UNNEST(events) AS e),0) AS cost,
    IFNULL((SELECT SUM(impressions) FROM UNNEST(events) AS e),0) AS impressions,
    IFNULL((SELECT SUM(clicks) FROM UNNEST(events) AS e),0) AS clicks,
                IFNULL(CAST(null as STRING), ''),
                IFNULL(CAST(null as STRING),""),  
                IFNULL(CAST(null as STRING),""),    
                IFNULL(CAST(null as STRING),""),    
                IFNULL(CAST(null as STRING),"")   
              )) AS STRING) AS userHash,
    CAST(FARM_FINGERPRINT(CONCAT( --userTrafficSourceHash
                  IFNULL(user_source, ''),
                  IFNULL(user_medium, ''),
                  IFNULL(user_campaign, '')
                )) AS STRING) AS userTrafficSourceHash,          
                  IFNULL(session_source, ''),
                  IFNULL(session_medium, ''),
                  IFNULL(session_campaign, ''),
                  IFNULL(session_keyword, ''),
                  IFNULL(session_adContent, '')
                )) AS STRING) AS trafficSourceHash,              
                  IFNULL(deviceCategory, ''),
                  IFNULL(operatingSystem, ''),
                  IFNULL(operatingSystemInfo, ''),
                  IFNULL(browser, ''),
                  IFNULL(browserVersion , '')
                )) AS STRING) AS deviceHash,              
                  IFNULL(country, ''),
                  IFNULL(region, ''),
                  IFNULL(city, '')                  
                )) AS STRING) AS geoHash                                                             
  FROM input as i, `{{projectId}}.{{datasetName}}._temp_sessions`
  WHERE date BETWEEN i.date_start AND i.date_end


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

Field name Type  
date DATE  
pseudo_sessionId STRING  
sessionId STRING  
landingPage STRING  
landingHost STRING  
touchpoint STRING  
dataSource STRING  
visitStartTime INTEGER  
visitNumber INTEGER  
sessions INTEGER  
hits INTEGER  
pageviews INTEGER  
events INTEGER  
transactions INTEGER  
transactionRevenue FLOAT  
bounces INTEGER  
timeOnSite INTEGER  
cost FLOAT  
impressions INTEGER  
clicks INTEGER  
userHash STRING  
userTrafficSourceHash STRING  
trafficSourceHash STRING  
deviceHash STRING  
geoHash STRING  


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


Please sign in to leave a comment.