https://www.growthbook.io/
Join Slack
Menu
Home
Archive
Hi! We're integrating with Amplitude via BigQuery and ran into a few errors with the generated queri...
# ask-questions
b
Hi! We're integrating with Amplitude via BigQuery and ran into a few errors with the generated queries. I've managed to fix the Experiment Assignment Queries by changing 
event_properties:experiment_id
to 
event_properties.experiment_id
and so on, but now have errors with the generated metrics. I have "Unrecognized name: event_name; Did you mean event_time? at [17:15]" when looking at the metrics from the sidebar and "No matching signature for operator = for argument types: JSON, STRING. Supported signature: ANY = ANY at [28:7]" for query results. Not sure if this is a super edge case or something someone may have seen before?
screenshot_2024-07-24-16_03_12.png,screenshot_2024-07-24-16_02_42.png,screenshot_2024-07-24-15_56_53.png
f
Hi James
sorry about that
sounds like we need to update those default queries
can you share the queries that work?
b
This was the working query for the logged in user query, which had the 
:
between 
event_properties.variation_id
in the generated version
Copy code
SELECT
  user_id as user_id,
  event_time as timestamp,
  JSON_VALUE(event_properties.experiment_id) as experiment_id,
  JSON_VALUE(event_properties.variation_id) as variation_id,
  device_family as device,
  os_name as os,
  country,
  paying
FROM
  `product-analytics-54321`.`mc_amplitude_uat`.EVENTS_123456
WHERE
  event_type = 'experiment viewed'
  AND user_id is not null
edit: This needed to convert from JSON to string as well, updated above.
For the other two I don't yet have a working version, I think I can use a cast to fix one but can't see where to edit.
I'll take a look at running directly in BigQuery to find a fix in a couple hours time
I needed to change the following generated metric:
Copy code
SELECT
  amplitude_id as anonymous_id,
  event_time as timestamp
FROM
  `product-analytics-54321.mc_amplitude_uat.EVENTS_123456`
WHERE
  event_time BETWEEN '{{date startDateISO "yyyy-MM-dd"}}' AND '{{date endDateISO "yyyy-MM-dd"}}'
  AND event_name = 'recipe CTA clicked'
to
Copy code
SELECT
  amplitude_id as anonymous_id,
  user_id,
  event_time as timestamp
FROM
  `product-analytics-54321.mc_amplitude_uat.EVENTS_123456`
WHERE
  event_time BETWEEN '{{date startDateISO "yyyy-MM-dd"}}' AND '{{date endDateISO "yyyy-MM-dd"}}'
  AND event_type = 'recipe CTA clicked'
So 
event_name
needed to be 
event_type
and I needed to add 
user_id
in
Looks like these two changes resolved it 🎉 I needed to change 
event_name
to 
event_type
and each of the 
event_properties:experiment_id
lookups to 
JSON_VALUE(event_properties.experiment_id)
f
Thanks, we’ll make those changes
Did you select Amplitude when setting up GrowthBook as the event tracker?
b
yes, selected amplitude, guided and then BigQuery
thanks
69 Views
Open in Slack
PreviousNext
Powered by

GrowthBook Users

Open source platform for stress free deployments, measured impact, and smarter decisions.

Powered by