prehistoric-beard-84272
09/17/2023, 8:50 AMfresh-football-47124
prehistoric-beard-84272
09/18/2023, 8:07 AMwhite-fireman-22476
09/18/2023, 8:07 AMprehistoric-beard-84272
09/18/2023, 8:08 AMwhite-fireman-22476
09/18/2023, 8:09 AMSELECT
user_pseudo_id as anonymous_id,
TIMESTAMP_MICROS(event_timestamp) as timestamp,
experiment_id_param.value.string_value AS experiment_id,
variation_id_param.value.int_value AS variation_id,
geo.country as country,
traffic_source.source as source,
traffic_source.medium as medium,
device.category as device,
device.web_info.browser as browser,
device.operating_system as os
FROM
defaultProjectName.defaultDataSet.events_*,
UNNEST(event_params) AS experiment_id_param,
UNNEST(event_params) AS variation_id_param
WHERE
_TABLE_SUFFIX BETWEEN '{{startYear}}{{startMonth}}{{startDay}}' AND '{{endYear}}{{endMonth}}{{endDay}}'
AND event_name = 'experiment_viewed'
AND experiment_id_param.key = 'experiment_id'
AND variation_id_param.key = 'variation_id'
AND user_pseudo_id is not null
UNION ALL
SELECT
user_pseudo_id as anonymous_id,
TIMESTAMP_MICROS(event_timestamp) as timestamp,
experiment_id_param.value.string_value AS experiment_id,
variation_id_param.value.int_value AS variation_id,
geo.country as country,
traffic_source.source as source,
traffic_source.medium as medium,
device.category as device,
device.web_info.browser as browser,
device.operating_system as os
FROM
defaultProjectName.defaultDataSet.events_interday,
UNNEST(event_params) AS experiment_id_param,
UNNEST(event_params) AS variation_id_param
WHERE
event_name = 'experiment_viewed'
AND experiment_id_param.key = 'experiment_id'
AND variation_id_param.key = 'variation_id'
AND user_pseudo_id is not null
GROUP BY
user_pseudo_id, event_timestamp, experiment_id, variation_id, country, source, medium, device, browser, os
works for you for anonymous id? If so I'll make the change for everyone so future people adding the datasource will get this by default.SELECT
user_id as user_id,
TIMESTAMP_MICROS(event_timestamp) as timestamp,
experiment_id_param.value.string_value AS experiment_id,
variation_id_param.value.int_value AS variation_id,
geo.country as country,
traffic_source.source as source,
traffic_source.medium as medium,
device.category as device,
device.web_info.browser as browser,
device.operating_system as os
FROM
`defaultProjectName`.`defaultDataSet`.`events_*`,
UNNEST(event_params) AS experiment_id_param,
UNNEST(event_params) AS variation_id_param
WHERE
_TABLE_SUFFIX BETWEEN '{{startYear}}{{startMonth}}{{startDay}}' AND '{{endYear}}{{endMonth}}{{endDay}}'
AND event_name = 'experiment_viewed'
AND experiment_id_param.key = 'experiment_id'
AND variation_id_param.key = 'variation_id'
AND user_id is not null
UNION ALL
SELECT
user_id as user_id,
TIMESTAMP_MICROS(event_timestamp) as timestamp,
experiment_id_param.value.string_value AS experiment_id,
variation_id_param.value.int_value AS variation_id,
geo.country as country,
traffic_source.source as source,
traffic_source.medium as medium,
device.category as device,
device.web_info.browser as browser,
device.operating_system as os
FROM
`defaultProjectName`.`defaultDataSet`.`events_interday`,
UNNEST(event_params) AS experiment_id_param,
UNNEST(event_params) AS variation_id_param
WHERE
event_name = 'experiment_viewed'
AND experiment_id_param.key = 'experiment_id'
AND variation_id_param.key = 'variation_id'
AND user_id is not null
GROUP BY
user_id, event_timestamp, experiment_id, variation_id, country, source, medium, device, browser, os
should work for the user_id one.defaultProjectName.defaultDataSet
bit to be what you have now.prehistoric-beard-84272
09/18/2023, 8:14 AMwhite-fireman-22476
09/18/2023, 8:14 AMprehistoric-beard-84272
09/18/2023, 8:17 AMwhite-fireman-22476
09/18/2023, 8:18 AMprehistoric-beard-84272
09/18/2023, 8:21 AMwhite-fireman-22476
09/18/2023, 8:22 AMprehistoric-beard-84272
09/18/2023, 8:29 AMwhite-fireman-22476
09/18/2023, 8:30 AMprehistoric-beard-84272
09/18/2023, 8:30 AMwhite-fireman-22476
09/18/2023, 8:32 AMprehistoric-beard-84272
09/18/2023, 8:34 AMwhite-fireman-22476
09/18/2023, 8:35 AMprehistoric-beard-84272
09/18/2023, 8:36 AMwhite-fireman-22476
09/18/2023, 8:44 AMSELECT
user_id as user_id,
TIMESTAMP_MICROS(event_timestamp) as timestamp,
experiment_id_param.value.string_value AS experiment_id,
variation_id_param.value.int_value AS variation_id,
geo.country as country,
traffic_source.source as source,
traffic_source.medium as medium,
device.category as device,
device.web_info.browser as browser,
device.operating_system as os
FROM
`defaultProjectName`.`defaultDataSet`.`events_*`,
UNNEST(event_params) AS experiment_id_param,
UNNEST(event_params) AS variation_id_param
WHERE
REGEXP_EXTRACT(_TABLE_SUFFIX, r'[0-9]+') BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}'
AND event_name = 'experiment_viewed'
AND experiment_id_param.key = 'experiment_id'
AND variation_id_param.key = 'variation_id'
AND user_id is not null
GROUP BY
user_id, event_timestamp, experiment_id, variation_id, country, source, medium, device, browser, os
should work if you replace the defaultProjectName
.defaultDataSet
, right?prehistoric-beard-84272
09/18/2023, 8:51 AMwhite-fireman-22476
09/18/2023, 8:55 AMprehistoric-beard-84272
09/18/2023, 8:56 AMwhite-fireman-22476
09/18/2023, 9:01 AMprehistoric-beard-84272
09/18/2023, 9:05 AMwhite-fireman-22476
09/18/2023, 9:08 AMprehistoric-beard-84272
09/18/2023, 9:09 AMwhite-fireman-22476
09/18/2023, 9:10 AMprehistoric-beard-84272
09/18/2023, 9:14 AMwhite-fireman-22476
09/18/2023, 9:18 AMprehistoric-beard-84272
09/18/2023, 9:19 AMwhite-fireman-22476
09/18/2023, 9:26 AMprehistoric-beard-84272
09/18/2023, 9:28 AMwhite-fireman-22476
09/18/2023, 9:29 AMprehistoric-beard-84272
09/18/2023, 9:31 AMwhite-fireman-22476
09/18/2023, 9:54 AMprehistoric-beard-84272
09/18/2023, 10:04 AMwhite-fireman-22476
09/20/2023, 9:02 AMWHERE TABLE_SUFFIX BETWEEN ...
2. How it is in the PR: WHERE REGEXP_EXTRACT(_TABLE_SUFFIX, r'[0-9]+') BETWEEN ...
3. Using substr: WHERE (TABLE_SUFFIX BETWEEN ... OR SUBSTR(_TABLE_SUFFIX, 9) BETWEEN ...)
prehistoric-beard-84272
09/20/2023, 9:06 AMwhite-fireman-22476
09/20/2023, 9:32 AMI think it might need to be SUBSTR(__TABLE__SUFFIX, 10)
UNION ALL
.prehistoric-beard-84272
09/20/2023, 10:24 AMwhite-fireman-22476
09/20/2023, 10:32 AMUNION ALL
there seems to be the same number of rows returned. I guess that is because yesterday was fully loaded.prehistoric-beard-84272
09/20/2023, 10:37 AMwhite-fireman-22476
09/20/2023, 10:50 AMprehistoric-beard-84272
09/20/2023, 10:55 AMwhite-fireman-22476
09/20/2023, 11:04 AMprehistoric-beard-84272
09/20/2023, 11:06 AMwhite-fireman-22476
09/20/2023, 11:07 AMprehistoric-beard-84272
09/20/2023, 11:09 AMwhite-fireman-22476
09/20/2023, 12:22 PMWHERE (_TABLE_SUFFIX BETWEEN "20230901" AND "20230919" OR _TABLE_SUFFIX BETWEEN "intraday_20230901" AND "intraday_20230919")
prehistoric-beard-84272
09/20/2023, 1:20 PMwhite-fireman-22476
09/20/2023, 2:12 PMprehistoric-beard-84272
09/20/2023, 2:13 PMwhite-fireman-22476
09/20/2023, 3:25 PMFROM project.dataset.events_202309*
. If it is a good bit faster/cheaper for bigger GA database then we might have to go with the UNION ALL to take advantage of it. Also how old is your GA database. ... want to calculate what percentage of the data is older than 202309 that we would be excluding?prehistoric-beard-84272
09/21/2023, 10:02 AMwhite-fireman-22476
09/21/2023, 10:04 AMprehistoric-beard-84272
09/21/2023, 10:04 AMwhite-fireman-22476
09/21/2023, 10:05 AMprehistoric-beard-84272
09/21/2023, 10:05 AMwhite-fireman-22476
09/21/2023, 10:10 AMevents_* WHERE _TABLE_SUFFIX BETWEEN "20230801" AND "20230831"
prehistoric-beard-84272
09/21/2023, 10:19 AMwhite-fireman-22476
09/21/2023, 10:33 AMprehistoric-beard-84272
09/21/2023, 10:35 AMwhite-fireman-22476
09/21/2023, 10:36 AMDownside with this one is, that it will through an error for intraday tables if there aren't any intraday_tables with that suffix (like for the August date range).Does that throw an error for the union all approach? Theoretically we can also have the default query ignore the intraday bit of the query if the end_date is older than 2 days ago.
prehistoric-beard-84272
09/21/2023, 10:38 AMwhite-fireman-22476
09/21/2023, 10:39 AMprehistoric-beard-84272
09/21/2023, 10:50 AMwhite-fireman-22476
09/21/2023, 11:13 AMprehistoric-beard-84272
09/21/2023, 11:21 AMwhite-fireman-22476
09/21/2023, 11:30 AM