Migrate Gmail logs to Google Workspace logs in BigQuery

This article is for Google Workspace administrators who use Gmail logs in BigQuery only, or who use both Gmail and Google Workspace logs in BigQuery. This article has detailed steps for migrating your Gmail BigQuery views, queries, and scripts to Google Workspace logs and reports in BigQuery.

To streamline BigQuery tasks, and to let you get all your BigQuery data in one place, we’re merging Gmail logs in BigQuery with Google Workspace logs in BigQuery. 

With this change, you get:

• One place to store and manage BigQuery exports for all your Google Workspace services audit events.
• More complete data about Gmail events in BigQuery, including device types and inbox actions (for example open, delete, clicked links, and attachment downloads). This data includes all events and columns from the security investigation tool.
• Partitioned data, so you can export smaller data sets and reduce your BigQuery data storage costs.
• Data recovery, so you can correct misconfigurations and permissions changes.

What you need to do

After Gmail logs in BigQuery is merged with Google Workspace logs in BigQuery, you won’t be able to use Gmail logs in BigQuery in your Google Admin console.

To keep your Gmail BigQuery data available, migrate your views, queries, and scripts to Google Workspace logs and reports in BigQuery:

Step	Description	More information
1	Set up service log exports to Google Workspace BigQuery	If you’ve already set up your Google Workspace logs and reports in BigQuery, skip this step and go to Step 2. If you skip this step, be sure to: Get your current project ID and dataset name that you’ll use for exporting Gmail data. Understand how the dual export period can affect your BigQuery storage requirements.
2	Migrate Gmail BigQuery logs views, queries & scripts	Do this step only if you have custom views or saved queries for Gmail logs in BigQuery that you want to migrate to Google Workspace logs in BigQuery. If you don’t have any custom views or saved queries to migrate, skip this step and go directly to Step 3.
3	Turn off Gmail log exports to your Gmail-only BigQuery project	We recommend you verify that your saved queries run as expected before completing this step.

Step 1: Set up service log exports to Google Workspace BigQuery

Do this step in your Google Admin console.

If you’ve already set up your Google Workspace logs and reports in BigQuery, skip this step and go to Step 2: Migrate Gmail BigQuery logs views, queries & scripts. If you skip this step, be sure to:

• Get your current project ID and dataset name that you’ll use for Gmail data exports.
• Understand how the dual export period affects your BigQuery storage requirements.

Get detailed information about BigQuery and Reports API data in Set up service log exports to BigQuery.

1. Sign in to your Google Admin console.

   Sign in using your administrator account (does not end in @gmail.com).

2. On the left, click Reporting.
3. Scroll down and click BigQuery Export.
4. Point to BigQuery Export and click Edit.
5. To turn on BigQuery logs, check the Enable Google Workspace data export to Google BigQuery box. 
6. Under BigQuery project ID, select the project where you want to store Gmail logs. Choose a project with write access. If you don’t see the project, you must set it up in BigQuery. For details, go to Quickstart using the Google Cloud console.
7. Under New dataset within project, enter a name for the new dataset used to store logs in the project. You can’t use an existing dataset, and dataset names must be unique for each project.
8. Click Save.
   Note: If you can’t save the project, go to the Google Cloud console, delete the new dataset, then save it again in the Admin console.

The dataset is automatically created. Activity log events are typically available about 10 minutes after this setting is turned on. Return later to verify that the dataset and the activity table appear. The past 6 months of your Gmail log event data is backfilled into the new table.

Temporary dual export period and storage requirements

After you set up Gmail logs export to Google Workspace BigQuery, Gmail logs data is exported to 2 destinations: 

• Your previous, Gmail-only BigQuery project 
• Your new or existing Google Workspace BigQuery project, which stores data for all Google Workspace applications

Gmail logs data will be exported to both projects until you complete Step 3: Turn off Gmail log exports to your Gmail-only BigQuery project. Temporarily exporting your data to both locations helps ensure that your data is transferred as expected, and that no data is lost. 

Step 2: Migrate Gmail BigQuery logs views, queries, & scripts

Do this step in your Google Cloud console.

Do this step only if you have saved queries or custom data set views that you want to migrate to Google Workspace logs in BigQuery:

• To check if you have saved queries, follow these steps.
• To check your custom data set views, follow these steps.

If you don’t have any custom views or saved queries to migrate, skip this step and go to Step 3: Turn off Gmail log exports to your Gmail-only BigQuery project.

To store Gmail data with the same structure as your current Gmail daily_ tables, create a view for the Gmail activity table. When you migrate your existing queries to Google Workspace BigQuery, you’ll modify the table name. You won’t modify the table format or the column names. For detailed information about BigQuery views, visit Introduction to views.

1. In the Google Cloud console, open an active BigQuery project. Open the project that has the saved queries you want to migrate.  
2. Select the project dataset that you want to migrate.
3. To create a single view for the activity table, follow these steps to run the script:
   1. Copy the script text:

      CREATE VIEW `PROJECT_ID.DATASET_NAME.gmail_view`(event_info, message_info, date) AS SELECT gmail.event_info, gmail.message_info, _PARTITIONTIME FROM `PROJECT_ID.DATASET_NAME.activity` WHERE gmail IS NOT NULL;

   2. Go to the Editor field. If the Editor field isn't visible, then click Compose new query .
   3. Paste the script text into the Editor field.
   4. In the pasted script, replace PROJECT_ID.DATASET_NAME with the project ID and dataset name from Step 1: Set up service log exports to Google Workspace BigQuery.
   5. In the upper left, click the Run button.
4. Review saved queries that select from the daily_ tables. For existing queries, update the table names and add a WHERE condition as needed. Make one of these changes:
   • Change FROM `daily_YYYYMMDD` selections to FROM `gmail_view` WHERE date = “YYYY-MM-DD”. For example, change FROM `daily_20220815` to FROM `gmail_view` WHERE date = “2022-08-15”
   • Change FROM `daily_*` selections to FROM `gmail_view`

Step 3: Turn off Gmail log exports to your Gmail-only BigQuery project

Do this step in your Google Admin console.

Gmail logs data will be exported to two projects until you complete this step. Exporting data to both projects helps ensure that projects and queries are transferred as expected, and that no data is missing. We recommend you verify that your saved queries run as expected before doing this step. 

To immediately stop exporting Gmail logs to your Gmail-only BigQuery project:

1. Sign in to your Google Admin console.

   Sign in using your administrator account (does not end in @gmail.com).

2. From the Admin console home page, go to AppsGoogle WorkspaceGmail​SetupEmail Logs in BigQuery.
3. Point to the setting and click Edit .
4. Uncheck the Enable box.
5. Click Save.

Related topics

• Google Workspace Admin Help: Logs and reports in BigQuery
• Google Workspace Admin Help: Reports and monitoring
• Google Cloud BigQuery documentation