Question 2: I have 7 queries on my first test experiment (the one where preview mode works - though it has not gathered any data in 35 days despite me being able to see hundreds of pageviews on that page in Google Analytics for that duration. A growthbook team member helped me sort out some errors with these queries the other day caused by my dataset not being defined by default in the assignment queries but now I have different errors: 2 of 5 show the “Could not cast literal “2023-12-21 231400" to type DATE at [98:22]” and everything else shows “_No rows returned”._ Btw I’m far from a developer so I feel i’m already over my head in GB but any way someone can help guide me would be appreciated. I am on a paid plan.

Happy to help with this one. Whatever query is failing probably has some kind of template variable, like

{{ startDate }}

You probably need need to wrap with something like

DATETIME('{{ startDate }}')

Thanks for your willingness to help! Here’s one of the full queries that display the error: -- AOV (revenue) WITH __rawExperiment AS ( 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

ga4bigquery-403xxx

.

analytics_xxx

.

events_*

, UNNEST (event_params) AS experiment_id_param, UNNEST (event_params) AS variation_id_param WHERE ( (_TABLE_SUFFIX BETWEEN ‘20231221’ AND ‘20240126’) OR ( _TABLE_SUFFIX BETWEEN ‘intraday_20231221’ AND ‘intraday_20240126’ ) ) 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 ), __experimentExposures AS ( -- Viewed Experiment SELECT e.anonymous_id as anonymous_id, cast(e.variation_id as string) as variation, CAST(e.timestamp as DATETIME) as timestamp FROM __rawExperiment e WHERE e.experiment_id = ‘test-experiment’ AND e.timestamp >= ‘2023-12-21 231400’ AND e.timestamp <= ‘2024-01-26 191256’ ), __experimentUnits AS ( -- One row per user SELECT e.anonymous_id AS anonymous_id, ( CASE WHEN count(distinct e.variation) > 1 THEN ‘__multiple__’ ELSE max(e.variation) END ) AS variation, MIN(e.timestamp) AS first_exposure_timestamp FROM __experimentExposures e GROUP BY e.anonymous_id ), __distinctUsers AS ( SELECT anonymous_id, cast(‘All’ as string) AS dimension, variation, first_exposure_timestamp AS timestamp, date_trunc(first_exposure_timestamp, DAY) AS first_exposure_date FROM __experimentUnits ), __metric as ( -- Metric (AOV) SELECT anonymous_id as anonymous_id, m.value as value, CAST(m.timestamp as DATETIME) as timestamp FROM ( SELECT DATE(TIMESTAMP_MICROS(event_timestamp)) as timestamp, ‘all_users’ as user_id, ‘all_users’ as anonymous_id, SUM(ecommerce.purchase_revenue) / COUNT(DISTINCT ecommerce.transaction_id) as value FROM

ga4bigquery-403xxx.analytics_xxx.events_*

WHERE event_name = ‘purchase’ AND ecommerce.purchase_revenue IS NOT NULL AND ecommerce.transaction_id IS NOT NULL AND ( (_TABLE_SUFFIX BETWEEN ‘20231221’ AND ‘20240129’) OR ( _TABLE_SUFFIX BETWEEN ‘intraday_20231221’ AND ‘intraday_20240129’ ) ) GROUP BY timestamp ) m WHERE m.timestamp >= ‘2023-12-21 231400’ AND m.timestamp <= ‘2024-01-29 191256’ ), __userMetricJoin as ( SELECT d.variation AS variation, d.dimension AS dimension, d.anonymous_id AS anonymous_id, ( CASE WHEN m.timestamp >= d.timestamp AND m.timestamp <= DATETIME_ADD(d.timestamp, INTERVAL 72 HOUR) THEN m.value ELSE NULL END ) as value FROM __distinctUsers d LEFT JOIN __metric m ON (m.anonymous_id = d.anonymous_id) ), __userMetricAgg as ( -- Add in the aggregate metric value for each user SELECT variation, dimension, anonymous_id, SUM(COALESCE(value, 0)) as value FROM __userMetricJoin GROUP BY variation, dimension, anonymous_id ) -- One row per variation/dimension with aggregations SELECT m.variation AS variation, m.dimension AS dimension, COUNT(*) AS users, SUM(COALESCE(m.value, 0)) AS main_sum, SUM(POWER(COALESCE(m.value, 0), 2)) AS main_sum_squares FROM __userMetricAgg m GROUP BY m.variation, m.dimension