In a hurry? Scroll all the way down for SQL code generator.
When you link GA4 to BigQuery in the sandbox environment (without a credit card or billing account), it “just works”.
But.. the analytics dataset will be configured to automatically expire after 60 days, even if you have added a credit card later!
Here’s how to undo the expiry of your analytics dataset and event tables:
Step 1: Unset the expiry of your analytics_nnnn dataset
Navigate to your dataset, and click “Edit Details”.
Then, un-select the Enable table expiry box and click SAVE.
Now, all the new tables in this set will have expiry set to Never.
Step 2: alter your existing event table shards to they won’t expire
If you have 60 days of events, you need to EDIT the details of all 60 daily tables manually.
Option 1: manually click all tables. That’s a lot of clicking.
Click.. select date, click.. edit details, click… “None”, click.. SAVE.
Option 2: type an “ALTER TABLE” statement.
One per table shard:
ALTER TABLE projectname.analytics_nnnnn.events_20230725 SET OPTIONS(expiration_timestamp=null)
To speed this up, you can use this alter table shard to unset expiry generator and use the output.
- enter your projectname
- enter your dataset name
- click refresh and copy the SQL statements
The following code will unset expiry for shards 62 days ago until today
Copy-paste this code into a new query and run.