Hi everyone! Quick question specific to GA4. GA4 data is written into 2 event tables: "events_" and "events_intraday_". (events_intraday_ contains roughly the last 2 rolling days of data, while events_* contains all data before that. events_intraday_* tables are periodically moved to events_*) So to get a real-time view with history, we need to get data from both tables. That means we need to add a union all to each query. I was wondering whether there is a centralized way to redefine events as a union all of both these table, so do we don't have to add that union all to each query? Thanks for the help!

Thank you, @fresh-football-47124!

It should go on the experiment assignment query. You should add a group by to filter out duplicates because I believe there might be a possibility of duplicates between the interday table and the other tables

Interesting thought @careful-machine-67313 I'll check that. Thank you!

Could you maybe check if the query:

SELECT
user_pseudo_id as anonymous_id,
TIMESTAMP_MICROS(event_timestamp) as timestamp,
experiment_id_param.value.string_value AS experiment_id,
variation_id_param.value.int_value AS variation_id,
geo.country as country,
traffic_source.source as source,
traffic_source.medium as medium,
device.category as device,
device.web_info.browser as browser,
device.operating_system as os
FROM
defaultProjectName.defaultDataSet.events_*,
UNNEST(event_params) AS experiment_id_param,
UNNEST(event_params) AS variation_id_param
WHERE
_TABLE_SUFFIX BETWEEN '{{startYear}}{{startMonth}}{{startDay}}' AND '{{endYear}}{{endMonth}}{{endDay}}'
AND event_name = 'experiment_viewed'

AND experiment_id_param.key = 'experiment_id'
AND variation_id_param.key = 'variation_id'
AND user_pseudo_id is not null

UNION ALL

SELECT
user_pseudo_id as anonymous_id,
TIMESTAMP_MICROS(event_timestamp) as timestamp,
experiment_id_param.value.string_value AS experiment_id,
variation_id_param.value.int_value AS variation_id,
geo.country as country,
traffic_source.source as source,
traffic_source.medium as medium,
device.category as device,
device.web_info.browser as browser,
device.operating_system as os
FROM
defaultProjectName.defaultDataSet.events_interday,
UNNEST(event_params) AS experiment_id_param,
UNNEST(event_params) AS variation_id_param
WHERE
event_name = 'experiment_viewed'

AND experiment_id_param.key = 'experiment_id'
AND variation_id_param.key = 'variation_id'
AND user_pseudo_id is not null

GROUP BY
user_pseudo_id, event_timestamp, experiment_id, variation_id, country, source, medium, device, browser, os

works for you for anonymous id? If so I'll make the change for everyone so future people adding the datasource will get this by default.

Sure, I'll check

Thanks.

The second query after union all also needs the where statement to filter by data, no? Intraday usally has 2 days in them (today and yesterday)

I think the GROUP BY at the end should get rid of duplicates which is needed anyway, no?

It's not about the duplicates, but that it will always include all intraday records even if the date filter is set outside of that range (e.g. for last week).

ah yes. That makes sense. We wouldn't want to include them always.

_Table_suffix is more efficient as far a I know, as the table is partitioned by date (I think it only queries the respective partitions instead of the entire table)

but that doesn't work on the interday table right?

it does, the intraday also has 2 partions (usually)

ah. I see. And the two tables are still in the same format as with the event_* but with just the last two days?

yep. They are streamed in real time. The reason for the separation is that it doesn't contain all values yet. Every day the older intraday partition is converted in an event_ partition and populated with some additional info that google has processed. And then a new empty intraday_ table is created

Ok then maybe the best approach is like what was in the link that Graham sent. But I believe that duplicates need to get removed still

Thats why "usually 2". Sometimes there are 3 for a short period of time

So for the user_id

SELECT
  user_id as user_id,
  TIMESTAMP_MICROS(event_timestamp) as timestamp,
  experiment_id_param.value.string_value AS experiment_id,
  variation_id_param.value.int_value AS variation_id,
  geo.country as country,
  traffic_source.source as source,
  traffic_source.medium as medium,
  device.category as device,
  device.web_info.browser as browser,
  device.operating_system as os
FROM
  `defaultProjectName`.`defaultDataSet`.`events_*`,
  UNNEST(event_params) AS experiment_id_param,
  UNNEST(event_params) AS variation_id_param
WHERE
  REGEXP_EXTRACT(_TABLE_SUFFIX, r'[0-9]+') BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}'
  AND event_name = 'experiment_viewed'  
  AND experiment_id_param.key = 'experiment_id'
  AND variation_id_param.key = 'variation_id'
  AND user_id is not null
GROUP BY
user_id, event_timestamp, experiment_id, variation_id, country, source, medium, device, browser, os

should work if you replace the

defaultProjectName

.

defaultDataSet

, right?

Yes, that works. I tested it with page_views as I don't have GB experiements in the events table yet (just started over the weekend running a/a tests)

Thanks for checking. I'm not sure yet if the GROUP BY is really needed ... we might deduplicate it elsewhere.

I checked for the experiments query: 91123 rows vs 91118 rows (grouped) I'm quite surprised about that to be honest

hrmm... I need to research more. I think GA4 might delete from the iterday as it adds to historical... but I'm not sure how transactional that is. I've seen some things saying there could be duplicates and others saying there is not. So I'm not sure if the group by removed those 5 duplicate rows, or if new rows were added in the time between your two queries.

Sorry - my blunder there: This is a fairly high traffic site and I had today's intraday included in the last query. So between running these two queries these 5 extra events came in. If I only include the last events_* partition (9/16) and first intraday_* partition (9/17), then both values are identitcal

Yes I'd imagine that might be the case, but I haven't gotten any authoratative source saying so yet. Have you seen it somewhere?

Nope, not that I have noticed that. I don't think that this state will last longer than a few minutes tops

Having the GROUP BY can't hurt the accuracy... though perhaps it makes the query a bit more expensive.

Nope just tested and the bytes billed are exactly the same. The grouped query might run a bit longer though

Hey thanks so much. This is really helpful. I will change the default query to have the new RegEx and to include the GROUP BY then. I would imagine that the difference in time will not be so great and its probably worth it to avoid some future user having complaints because they ran a query at the wrong time and got duplicates.

My pleasure, thank you too! If you need any input regarding GA4 and BigQuery feel free to hit me up any time.

Thanks! Can you check the time of running the two queries just to make sure that they are indeed reasonable? That would be a big help.

That would need some scientific testing. Because I just had a few runs and the results were all over the map. I guess hardware availability (CPU usage) has a bigger impact than the group by.

By all over the place, was it like orders of magnitude different?

No, more like each query ranges between 4 and 8 seconds without having a obvious tendency. Maybe we need to adjust GB to run a/b tests on BigQuery 😛

FWIW here is the PR: https://github.com/growthbook/growthbook/pull/1673 to add this for future GB users. Thanks so much for helping me test and making GB better! Let me know if you would like me to mention you by name or github handle or anything as part of the PR.

No need. Thanks for creating this great tool and provide it as open source!

Thanks Sascha for pointing that out. Yes I have updated the PR to include the metrics and the auto generated metrics. However a reviewer is concerned that the query as is would not be using the partitions correctly. If you are willing to help out more, could you do an EXPLAIN ANALYZE in BQ for the following three queries and post the results. 1. The original query:

WHERE TABLE_SUFFIX BETWEEN ...

2. How it is in the PR:

WHERE REGEXP_EXTRACT(_TABLE_SUFFIX, r'[0-9]+') BETWEEN ...

3. Using substr:

WHERE (TABLE_SUFFIX BETWEEN ... OR SUBSTR(_TABLE_SUFFIX, 9) BETWEEN ...)

Sure will do. Will send an update here

If you could also verify that 2 and 3 return the same thing by using an end date that is an hour in the past or so, to make sure I got the query correct that would be great. (Eventually I'm going to need to set up a GA4 instance for me to test out easier myself. 🙂 )

BigQuery does not have EXPLAIN ANALYZE in the web interface, but the offer additonal menus with details Run for 1 without Union all:

Hrmm the one with the

UNION ALL

there seems to be the same number of rows returned. I guess that is because yesterday was fully loaded.

Run 1 with union all

Thanks! That's good. It does seem like the slot time consumed goes from 5sec to 9sec for either the regex or the substr and 11s for the union all. The Bytes billed is trivially higher which makes sense as there is one more day of data. I'm not an expert on the BQ costs, but I think slot time is the main driver, and it would suggest that by adding the most recent day the query cost might almost double. 😞. For some of our customers with tons of data, the query costs can be substantial. So unless there is a fourth way I'm not sure if we will be able to make this the default for everyone. I'll try and talk more the BQ cost expert here and see if we can come up with anything.

Billing is mostly by query size (bytes), not computation. While they charge for computation, it's usually negligible compared to the bytes billed. The reason why the costs increased alot is because my first day with experiments was Sep 15th, and the last 2 days I ran experiments continuously Actually just double checked the data, and the costs are basically the same. the queries were about 5GB each. Each user has 1TB free per months, and even then its marginal. https://cloud.google.com/bigquery/pricing

From my reading of the pricing page, for people doing on-demand computing then it is based on bytes, and adding the most recent day won't be adding much (or anything if they are below the limit), however for people doing "Capacity Pricing" then it is by compute time and the Slot time consumed will matter.

Hmmm, fair point. Though I'd expect that people on that plan already pay a lot to BQ anyways.

right ... and they wouldn't necessarily want to pay double. Anyway thanks so very very much for helping out here.

Sure thing. Yes, please, would be curious. I think real time might be worth the extra cost.

Hi Sascha. Could you please try one more thing that has a higher chance of using the partitions correctly:

WHERE (_TABLE_SUFFIX BETWEEN "20230901" AND "20230919" OR _TABLE_SUFFIX BETWEEN "intraday_20230901" AND "intraday_20230919")

Interesting idea, never used that before.

What was the slot time consumed on those? Your screenshot cut it off

Sorry, here you go

Thanks

If you don't need queries specific to experiment_viewed, I can use another property and query for about 10 months of data

Yeah it doesn't need to be specific. I think if it is also date partitioned it would give us a good idea of the performance benefit.

Do you have specific scenarios in mind?

Yeah we have both the start date and the end date of the experiment, so we should be able to use the longest common prefix between the two dates.

I see

yes

Downside with this one is, that it will through an error for intraday tables if there aren't any intraday_tables with that suffix (like for the August date range).

Yeah that does seem to have a high variance. It looks like cost wise it wouldn't matter for you and the majority of GA4 users that are billed by data, since that is the same for both queries. For those who pay based on slot time ... yeah that would be too hard to tell without lots more testing. I did see the longer prefix one did take 12s vs 14s .. not sure how variable that is.

I'm not sure. I guess to get representative numbers for that would require test via the API (how GB actually runs the queries) and see if you can get performance stats

Downside with this one is, that it will through an error for intraday tables if there aren't any intraday_tables with that suffix (like for the August date range).

Does that throw an error for the union all approach? Theoretically we can also have the default query ignore the intraday bit of the query if the end_date is older than 2 days ago.

Yes, it also throws an error for union all. A filter might work, but as soon as you run tests across months or new year there are a lot of conditions you need to check

We can do date compare in js.

Yes, sounds good. You will notice if the tickets here go up 🙂

Ok one last survey question for my n of 1 study. It was pointed out by my colleague that if we use intraday tables then when people stop experiments that for the remainder of the day the results might change. 1. How much does that matter to you? 2. If we only use the intraday tables for the cases where the experiment was started less than a day ago - so you would get immediate results on the day you start the experiment, but then it would only update once a day afterwards - would that satisfy your needs?

1.) I'm not sure if I understand it correctly. Doesn't this apply to all tables because of the 72h window? 2.) Especially for high traffic sites this could be a problem. I still would want to query current data, at least manually.

What 72h window? You mean DEFAULT_CONVERSION_WINDOW_HOURS? I believe we don't count people who convert if they converted after the end-date of the experiment - if someone stopped it. Perhaps though this is not an issue if we are doing filtering after we get the results back. Anyway thanks for your response.