https://www.growthbook.io/ logo
#ask-questions
Title
# ask-questions
j

jolly-raincoat-49731

12/05/2023, 5:07 PM
We recently switched from GA4 daily to streaming via BigQuery. It seems we need to update
events_*
to
events_intraday_*
in the queries. Is there an easier way to do that rather than go to each metric or wherever a query is created?
b

billions-xylophone-11752

12/05/2023, 5:27 PM
Hey Erik - unfortunately, we don't have any provisions for bulk updates like this at this time. Depending on how valuable your historical data is, I might suggest creating a new project, and then creating a completely new data source and associated metrics in that new project. This will allow you to retain historical data and easily differentiate between new features/experiments that use the streaming tables vs the legacy features/experiments that use the daily export tables. (All of this is assuming Google doesn't offer any trick services that port the old daily data to the new tables.)
j

jolly-raincoat-49731

12/05/2023, 5:28 PM
Ok, thanks!
@billions-xylophone-11752 Would I be able to update all metric queries and assignment queries manually to do
Copy code
AND ((_TABLE_SUFFIX BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}') OR
       (_TABLE_SUFFIX BETWEEN 'intraday_{{date startDateISO "yyyyMMdd"}}' AND 'intraday_{{date endDateISO "yyyyMMdd"}}'))
?
b

billions-xylophone-11752

12/05/2023, 5:43 PM
Hmm... I don't think the
OR
statement should cause problems, but won't there still be the issue of the
FROM
clause? I'm pretty sure standard SQL doesn't support the
OR
operator in the
FROM
clause.
Perhaps you could update the metrics with a JOIN clause... let me see if that could work.
j

jolly-raincoat-49731

12/05/2023, 6:44 PM
That is what I got when I created a new metric after streaming from BigQuery was enabled.
b

billions-xylophone-11752

12/05/2023, 6:54 PM
Erik - I just spoke to a colleague and he mentioned that table format should be identical, it's just the table suffix that is different (it has
_intraday
added to the table name). Additionally, we did add support for this recently for any newly created metrics/data sources so it queries both tables at once. And it just does what you mentioned earlier, of updating the WHERE clause.
Copy code
WHERE 
((_TABLE_SUFFIX BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}') OR
   (_TABLE_SUFFIX BETWEEN 'intraday_{{date startDateISO "yyyyMMdd"}}' AND 'intraday_{{date endDateISO "yyyyMMdd"}}'))
We don't have a way to bulk update metric SQL, so they will just have to go through each one and copy/paste this new WHERE clause in. We are working on our new
Fact Tables
and one of the goals of that is to have the common SQL in one place so it's easy to make sweeping changes like this.
Apologies for the runaround.
j

jolly-raincoat-49731

12/05/2023, 6:55 PM
Gotcha. Thanks for getting back to me so quick! Not a huge deal on editing the metrics. It also looks like we needed to edit the assignment queries as well. I did a quick test earlier before lunch and it all seems to jive now.
@billions-xylophone-11752 Do you know if adding that WHERE clause to each metric will cut down on BigQuery usage? We're forecast to use ~$800 this month and we want to cut that down significantly if we can. If there are other ways to cut that down, let me know. Thanks!
b

billions-xylophone-11752

12/06/2023, 9:46 PM
Hey Erik - yea, the WHERE clause pasted above can improve query performance to make sure we are only scanning relevant data and hopefully filtering based on some table you have partitioning set up on. It's possible depending on your setup that it doesn't improve performance, but for most out of the box GA4 integrations, it will improve performance.
j

jolly-raincoat-49731

12/06/2023, 9:46 PM
Ok, awesome. Appreciate the quick response!
5 Views