Once you have a data table ready in Data Builder, you are able to export it to BigQuery. Data exports are available as a paid add-on on the Business, Grow, and Pro plans, and are included with the Custom plan when your negotiated agreement includes data exports. Follow the instructions in this article to get started.

Grant Required Roles and Permissions

• BigQuery Admin
• BigQuery Data Editor
• BigQuery Owner

Add the Job User Role

Create a Dataset

The user creating an export must have Write permissions to the dataset.

Step 1: Configure Your Data Builder Data Table for Initial Backfill

Note: Data tables exported to BigQuery must include at least one date field. Without a date field, the export will fail to complete successfully.

1. In your PMA hub, go to Reports > Data Builder and find your data table. Click the three-dots action menu and select Edit.
2. Go to Available Data Range and set it to the entire range of data you want to export (up to 2 years for most PMA connectors). To backfill from a specific start date up to yesterday, choose Custom start to date and enter a start date.
3. Click Save Changes to save the data table.

Step 2: Create Your Export and Choose the Destination

1. Go to Exports > BigQuery in the left sidebar.
2. Click + Create export. The Create export wizard opens and walks you through four steps: Destination, Data, Schedule, and Review.
3. Under Destination, select the destination account associated with your BigQuery dataset.
   • If you haven't yet connected your Google account, make sure you are logged into the account associated with your BigQuery dataset, then click the plus button to grant BigQuery permissions to Power My Analytics. Once the account is added, return to the wizard and restart the export setup.
4. For Dataset, select the BigQuery dataset you'd like to use. Datasets are grouped by Google Cloud project. To create a new dataset, click the plus button to open the Google Cloud console in a new tab, create the dataset there, then return and check again.
5. Optionally, enter a Table name prefix. The prefix starts every table name this export creates; for example, a prefix of "myprefix" produces table names like "myprefix_orders_YYYYMMDD" when the data has dates. If you leave the prefix blank, table names are based on the data table name alone. Click Next.

Step 3: Choose the Data to Export

A Data Table is a single table in Data Builder. A Dataset groups related data tables. Exporting a whole dataset sends every data table in it. (A Data Builder dataset is different from the BigQuery dataset you selected in the previous step.)

1. Under Data table or dataset, search for and select either a single data table or an entire dataset from Data Builder.
2. Click Next.

Step 4: Set Your Export Schedule

1. Choose a Refresh period: On demand, Monthly, Weekly, Daily, or Hourly.
   • On demand: Your export has no schedule and runs only when you run it manually from the exports list. Leave Run as soon as you create the export enabled to run the export immediately after you create it.
   • Monthly: Choose a day of the month (1 through 28), the hour, and the timezone.
   • Weekly: Choose a day of the week, the hour, and the timezone.
   • Daily: Choose the hour and the timezone.
   • Hourly: Your export runs every hour; no further settings are needed.
2. For scheduled refresh periods, the Timezone is required and determines the local wall-clock time at which your export will run. Your export will run on this schedule until you pause or delete it.
3. Click Next.

Tip: For your initial backfill, select On demand and leave Run as soon as you create the export enabled. You can switch to a scheduled refresh period afterward (see Set Up Rolling Updates below).

Step 5: Review and Create Your Export

1. Under Review, check the Destination, Data, and Schedule summaries. Click Edit on any card to make a change.
2. Click Create export. If you left Run as soon as you create the export enabled, your initial backfill begins immediately.

Step 6: Verify the Backfill

1. On the Exports to BigQuery page, find your new export. Click the three-dot action menu at the end of the row and select View logs. Confirm the export completed successfully.
2. To run the export manually at any time, open the same three-dot menu and select Run export now.

Set Up Rolling Updates

Configure your Data Builder data table

1. Go back to your data table in Data Builder. Click the three-dots action menu and select Edit.
2. Set the Available Data Range to a rolling window (e.g., last 30 days for e-commerce to account for returns).
3. Then click Save Changes.

Configure your BigQuery export

1. Return to Exports > BigQuery. Open your export's three-dot action menu and select Edit export.
2. Under Schedule, choose your desired refresh period: Monthly, Weekly, Daily, or Hourly. Select the appropriate day and time, and confirm or update the Timezone in which the export should run.
3. Save your changes.

Make sure your data table's date range in Data Builder is at least as long as the refresh period in your export. Failing to do so may result in data missing from your backfill. For example:
- If your data table's date range is one week but your export refreshes monthly, your export will fail to include data from before the last week of the month.
- If your data table's date range is one week and your export refreshes daily, your export will overwrite the past seven days, every day.

Date Partitioning

How PMA exports to BigQuery

Exports ranging less than 90 days

Exports ranging 90 days or more

Export Limitations

Timeout Errors and "Failed to Fetch" Errors

• Long date ranges (e.g., a full year of data)
• Large number of fields in one data table
• Excessive use of live fields (indicated by the lightning bolt ⚡ icon in Schema Explorer)

Understanding Live Fields

How to Resolve Timeout Errors

1. Split by time period: Instead of exporting a full year at once, run separate exports for quarterly segments (Q1, Q2, Q3, Q4).
2. Reduce live fields: Remove fields marked with the ⚡ icon to eliminate real-time API calls. Use Schema Explorer to identify these fields.
3. Limit fields per data table: Group related fields into separate data tables (e.g., one for order metrics, another for product metrics, another for customer data).

How to accommodate large datasets

1. Return to Data Builder.
2. Edit your data table and set Available Data Range to the second date range.
3. Go to BigQuery and run your export again.

1. Reduce the size of the date range until the export runs successfully, indicating this range is within BigQuery's export limits.
2. Run the export for each date range according to the instructions above.