10. Users

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

#Users object

WITH 
  #Set the date range for the calculation
    dates as 
      (
        SELECT
          '20220901' AS date_start,
          '20220930' AS date_end
      ),

  #Select user data from streaming
    web_users as 
      (      
        SELECT
          clientId,
          CAST(null as STRING) as userId,
          CAST(null as STRING) as visitorId,
          CAST(null as STRING) as userPseudoId,
          CAST(null as STRING) as phone,
          CAST(null as STRING) as email,
        FROM
          dates as d, `{{projectId}}.{{datasetName}}.ga_sessions_*`
        WHERE 
          _TABLE_SUFFIX BETWEEN d.date_start and d.date_end          
        GROUP BY 1,2,3,4,5,6  
      )

SELECT
  CAST(FARM_FINGERPRINT(CONCAT(
    IFNULL(clientId,""),
    IFNULL(userId,""),  
    IFNULL(visitorId,""),    
    IFNULL(userPseudoId,""),    
    IFNULL(phone,""),    
    IFNULL(email,"")    
     )) as STRING) as userHash,
  clientId,
  userId,
  visitorId,
  userPseudoId,
  phone,
  email
FROM web_users
GROUP BY 1,2,3,4,5,6,7

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