Enable email logs in BigQuery
This feature is only available with G Suite Enterprise.
Gmail log search in BigQuery is now available. You can use this new feature to:
- Analyze Gmail logs using sophisticated, high-performing custom queries
- Retain Gmail data as long as required using a user-configurable retention period
- Utilize BigQuery web UI, command line, or third-party tools to do deep analysis
- Create custom reporting and dashboards using analytics tools, such as Google Data Studio
Follow these instructions to set up your BigQuery project and enable email logs in your organization’s Admin console.
Before you begin
- Create or identify an active BigQuery project to use. Follow these instructions to create a project if you don’t have one. You can set up a free trial to try out.
- Locate the ID of the project you want to export Gmail logs to in https://console.cloud.google.com/
- Go to the IAM & Admin page of your project.
- Add the ID for the administrator who will complete the setup in the Admin console as a project editor. You can also add the administrator as the project owner.
Set up Email logs in BigQuery
Sign in to your Google Admin console.
Sign in using your administrator account (does not end in @gmail.com).
From the Admin console dashboard, 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 for storing email logs.
The drop-down list shows all projects that you can access, including view access only.
- Choose a project with write access.
- Enter the name of the dataset to use for storing the logs in the selected project. If you entered a name that’s already used by an existing dataset, enter a new name.
- Click Add setting to return to the settings page, then click Save.
Note: if you receive an error, retry clicking Add setting. You may need to go to the BigQuery console and delete the newly created dataset from the failed attempt.
- After saving settings, go back to your BigQuery project.
There’s now a new dataset inside the project, as specified. In addition to project owners, editors, or viewers, there are 4 service accounts automatically added as dataset editors:
The first two accounts are required to write the logs. The third account is required to automatically restore the template table if it is deleted accidentally. The fourth account is required to update the schema in the future. Do not remove these service accounts or change their roles to anything other than editor. To verify that the service accounts are added correctly:
- Hover over the newly created dataset and, next to the dataset name, click Down .
- In the pop-up, click Share dataset.
- Inside the dataset, there’s a new table named “daily_”. This table serves as a template to provide table schema for actual daily tables. It’s left empty and never expires. Don’t remove, modify, rename, or insert data into this table.
- It may take up to an hour for your changes to take effect. After that, daily tables are automatically created in your dataset. Use GMT time to determine the date change line.
- The logs are now available for use. See the following Query Examples for sample queries you can use.
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).
Gmail log description
Gmail logs store records of different stages an email went through in Gmail’s email delivery pipeline. There usually are 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, you can try some example queries for most common use cases.