Hey All, I was kind of curious how stable and trustworthy the auto-generated SQL queries were for BigQuery / GA4 because I tried to add some basic metrics like add_to_cart and purchase for example and when I try to add them I get initial data indicating all is good like shown in the second picture. But when I try to actually use them I instead get errors like this and I have no real context for debugging it unfortunately.

Can you alias the “int_value” as value in that metric?

sorry just to make sure I have this lined up correctly in my mind... Second picture line 5 would become:

value_param.value.int_value as value

yes

Would I be wrong in thinking that I should have expected to see a value here in the demo query ?

that metric query was auto created for you?

I just tried to implement one of the default GA4 ones and tried to set up add_to_cart and purchase as basic count based metrics. I ended up with the following SQL if it's helpful...

SELECT
  user_id,
  user_pseudo_id as anonymous_id,
  TIMESTAMP_MICROS(event_timestamp) as timestamp,
  value_param.value.int_value as value
FROM
  `GOOGLE_CLOUD_PROJECT_ID`.`BIG_QUERY_DATA_ID`.`events_*`,
  UNNEST(event_params) AS value_param
WHERE
  event_name = 'add_to_cart'  
  AND value_param.key = 'value'
  AND _TABLE_SUFFIX BETWEEN '{{startYear}}{{startMonth}}{{startDay}}' AND '{{endYear}}{{endMonth}}{{endDay}}'

ya