10. Geo

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

#Object Geo 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( --deviceHash
          IFNULL(country, ''),
          IFNULL(region, ''),
          IFNULL(city, '')                  
        )) AS STRING) AS geoHash,
    country,
    region,
    city
FROM
  input AS i, `{{projectId}}.{{datasetName}}._temp_sessions`
WHERE
  date BETWEEN i.date_start AND i.date_end         
GROUP BY 1,2,3,4

 

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

Field name Type  
geoHash STRING  
country STRING  
region STRING  
city STRING  

 

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

0 Comments

Please sign in to leave a comment.