Question 2: I have 7 queries on my first test exp...
# ask-questions
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 }}')
If you share the full query that is failing in that modal I can try to help you out.
Another issue why you might not be seeing any data on your other queries is that for some reason your experiment exposure IDs aren't matching with your metric IDs, but I'm not certain. Perhaps @brief-honey-45610 can help with that next week if that is indeed still a problem.
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, 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
, 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
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