11. Users

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

#Object Users preparation

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

SELECT
  CAST(FARM_FINGERPRINT(CONCAT(
    IFNULL(CAST(null as STRING),""),
    IFNULL(CAST(null as STRING),""),  
    IFNULL(CAST(null as STRING),""),    
    IFNULL(user_pseudo_id,""),    
    IFNULL(CAST(null as STRING),""),    
    IFNULL(CAST(null as STRING),"")    
     )) as STRING) as userHash,
  CAST(null as STRING) as clientId,
  CAST(null as STRING) as userId,
  CAST(null as STRING) as visitorId,
  user_pseudo_id as userPseudoId,
  CAST(null as STRING) as phone,
  CAST(null as STRING) as email
FROM
  input AS i, `{{projectId}}.{{datasetName}}._temp_sessions`
WHERE
  date BETWEEN i.date_start AND i.date_end
GROUP BY 1,2,3,4,5,6,7

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