https://www.growthbook.io/ logo
Join Slack
Powered by
Hi! We're integrating with Amplitude via BigQuery ...
# 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
73 Views
Open in Slack
PreviousNext
Powered by