11. Users

NEW documentation: This article covers setup for OWOX BI. For the latest guides on setting up FREE connectors and Data Marts, visit the new OWOX Data Marts documentation.

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: