Enable Gmail logs in BigQuery
As a G Suite administrator, you can search your Gmail logs to analyze and report on your organization's email. Email log search within a BigQuery project dataset lets you:
- Analyze Gmail logs using sophisticated, high-performance, custom queries
- Retain Gmail data as long as necessary with a user-configurable retention period
- Utilize the BigQuery web UI, command line, or third-party tools to do deep analysis
- Create custom reports and dashboards using analytics tools, such as Google Data Studio
Before you begin
- Create or identify an active BigQuery project to use.
• If you don't have a project, you can set up a free trial to try BigQuery.
• Follow these instructions to create a project.
- Locate the Project ID.
- Go to the IAM & Admin page of your project.
- Add the ID of the administrator who will act as the project editor to set up the project in the Admin console. (You can also add the admin as the project owner.)
Assign Gmail logs to a BigQuery dataset
To analyze the flow of email through Gmail's delivery pipeline, you assign your Gmail logs to a dataset in a BigQuery project. After the Gmail logs are assigned, the data becomes available for analysis and reporting.
From the Admin console Home page, go to AppsG SuiteGmailAdvanced settings.
Tip: To see Advanced settings, scroll to the bottom of the Gmail page.
- Under General Settings Setup, 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 get 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 delete the previously created dataset.
- After saving settings, go back to your BigQuery project. A dataset containing the following information is added to the project:
- The normal roles for project owners, project editors, and project viewers
- 4 required service accounts, designated as dataset editors—not the Cloud project IAM. (do not remove these service accounts or change their roles)
- 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 deleted.
- email@example.com. Updates the schema in the future.
- To verify that the service accounts are correctly added, point to the new dataset and, next to the dataset name, click Down.
- Click Share dataset.
It can take up to an hour for your changes to take effect.
A new table named daily_ is added to the dataset, which serves as a template to provide a table schema for actual daily tables. It’s left empty and never expires. Don’t remove, modify, rename, or add data in this table. Once the daily_ table is created, daily tables are automatically created in your dataset. Use GMT time to determine the date change line. The logs then become available for use.
Exporting daily email logs
Daily email logs are exported to BigQuery once the setting is enabled. Logs created before enabling the setting can't be exported to BigQuery.
Gmail log contents
Gmail logs store records of different stages an email went through in Gmail’s email delivery pipeline. There are typically multiple records for a single email message, which is uniquely identified by a message ID. Before reading the complete schema of a Gmail log table to compose your custom query, try some example queries for the most common use cases.
BigQuery syntax and functions
BigQuery supports 2 SQL dialects for queries—standard SQL and legacy SQL. By default, BigQuery runs queries using legacy SQL. For more information about query syntax and functions, see the BigQuery Query Reference (legacy SQL) and BigQuery Query Reference (standard SQL).