It seems the events table is split by date in Goog...
# ask-questionsf
flaky-wall-25429
04/15/2024, 9:50 AMIt seems the events table is split by date in Google, then how can I calculate a date range in GB?
f
fresh-football-47124
04/16/2024, 6:21 AM
GrowthBook handles this for you with some fancy logic - if you look at the default queries for GA4, you'll see something like:
Copy code
FROM
`ga4-sample-1234`.`analytics_3557776766`.`events_*`,
UNNEST(event_params) AS experiment_id_param,
UNNEST(event_params) AS variation_id_param
WHERE
((_TABLE_SUFFIX BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}') OR
(_TABLE_SUFFIX BETWEEN 'intraday_{{date startDateISO "yyyyMMdd"}}' AND 'intraday_{{date endDateISO "yyyyMMdd"}}'))
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 nullf
flaky-wall-25429
04/16/2024, 6:31 AMThanks.
flaky-wall-25429
04/16/2024, 9:46 AMI need this group aggregator to calculate 14 days signup rate, but in GB it requires timestamp and user_id for each query. How can I make it?
f
fresh-football-47124
04/16/2024, 7:00 PM
the query needs to return a timestamp
fresh-football-47124
04/16/2024, 7:01 PM
but that timestamp can be based on anything you like
fresh-football-47124
04/16/2024, 7:03 PM
so you should return the event_date - the where clause will remove invalid rows - we have template variables you can use in the queries for the dates:
fresh-football-47124
04/16/2024, 7:03 PM
13 Views