https://www.growthbook.io/ logo
w

worried-yacht-64692

05/17/2023, 2:13 PM
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.
f

fresh-football-47124

05/17/2023, 2:20 PM
Can you alias the “int_value” as value in that metric?
GrowthBook expects it to return a column called value
w

worried-yacht-64692

05/17/2023, 2:30 PM
sorry just to make sure I have this lined up correctly in my mind... Second picture line 5 would become:
Copy code
value_param.value.int_value as value
f

fresh-football-47124

05/17/2023, 2:48 PM
yes
w

worried-yacht-64692

05/17/2023, 2:51 PM
Would I be wrong in thinking that I should have expected to see a value here in the demo query ?
Rather than value null
It does remove the error but yeah, I'm guessing there is another problem somewhere because that shouldn't be null across the board
f

fresh-football-47124

05/17/2023, 3:40 PM
that metric query was auto created for you?
w

worried-yacht-64692

05/17/2023, 3:43 PM
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...
Copy code
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}}'
Which seems to be at least in the ballpark of correct I guess this much info but the chart itself is flat
f

fresh-football-47124

05/17/2023, 3:46 PM
ya
14 Views