Use the SQL code below and change {{projectId}}
and {{datasetName}}
to your own.
#Devices object
WITH
#Set the date range for the calculation
dates as
(
SELECT
'20220901' AS date_start,
'20220930' AS date_end,
)
SELECT
CAST(FARM_FINGERPRINT(CONCAT(
IFNULL(device.deviceCategory,""),
IFNULL(device.browser,""),
IFNULL(device.browserVersion,""),
IFNULL(device.operatingSystem,""),
IFNULL(device.operatingSystemVersion,""))) as STRING) as deviceHash,
device.deviceCategory,
device.browser,
device.browserVersion,
device.operatingSystem,
device.operatingSystemVersion
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
You will get the data in the table 'devices' with the schema below:
Field name | Type | |
---|---|---|
deviceHash |
STRING | |
deviceCategory |
STRING | |
browser |
STRING | |
browserVersion |
STRING | |
operatingSystem |
STRING | |
operatingSystemVersion |
STRING |
0 Comments