Set up Gmail logs in BigQuery
This feature is only available with G Suite Enterprise and G Suite Enterprise for Education. Only users with one of these licenses can use this feature. You can assign licenses to individual users, to everyone in an organizational unit, or to everyone in your G Suite account. Learn more about how licensing works.
Gmail logs store records for each stage of an message in the Gmail delivery process. To analyze Gmail flow through the delivery process, assign Gmail logs to a dataset in a BigQuery project. After the Gmail logs are assigned, you can review reports.
Note: Email logs created before you set up Email Logs in BigQuery can't be exported to BigQuery.
Assign Gmail logs to a BigQuery dataset
From the Admin console home page, go to AppsG SuiteGmailAdvanced settings.
- Under Advanced settings, go to Email Logs in BigQuery and click Configure.
- In the Add setting window, enter a description.
- Select the BigQuery project you want to use for Gmail logs. Select a project with write access.
- Enter a dataset name where Gmail logs are stored.
- Click Add setting to return to the settings page, then click Save.
Note: If an error occurs, try clicking Add setting again. You might need to go to the BigQuery console and remove the previously created dataset.
- After saving settings, go back to your BigQuery project. A dataset with this information is now in the project:
- The standard roles: project owners, project editors, and project viewers
- Four service accounts that are designated dataset editors:
- firstname.lastname@example.org: Writes the logs.
- email@example.com: Writes the logs.
- firstname.lastname@example.org: Automatically restores the template table if it's accidentally removed.
- email@example.com: Updates the schema in the future.
Note: Do not remove these service accounts or change their roles. These are required accounts.
- To verify these service accounts are added, point to the new dataset and click Down next to the dataset name.
- Click Share dataset. Daily email logs are now exported to BigQuery. It can take up to an hour for your changes to take effect.
After you turn on email logs in BigQuery, a new table named daily_ is added to the dataset. This table is a template that provides the schema for the daily tables. After you create the daily_ template, daily tables are automatically created in your dataset. The logs are then available for use.
What you should know about the daily_ table:
- It's always empty and never expires.
- Don't remove, modify, rename, or add data to this table.
- Use GMT time to determine the date change line.
Gmail log queries
Try some example queries for Gmail logs in BigQuery. The examples are common use cases for Gmail logs.
Compose your own, custom queries using the schema for Gmail logs in BigQuery.
SQL dialects for queries
BigQuery supports two SQL dialects for queries
The expiration time for these BigQuery sandbox objects is 60 days:
- Partitions in partitioned tables
You can change the default table expiration time for tables.
If a table expires or is removed, it can be restored within 2 days.