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 |
0 Comments