Exporting your marketing data to Azure SQL allows you to integrate it with various applications and run complex data queries. This guide will walk you through the process of setting up and managing your Azure SQL exports using Power My Analytics. Azure SQL 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.

How We Store Data in Azure SQL Exports

Creating a table

Our Azure SQL export feature will create a table with the same name as the Data Builder data table you are exporting, with all spaces replaced by underscores (_). The table has columns corresponding to the fields in the Data Builder data table being exported.

Appending and updating rows

Unlike BigQuery exports, exports to Azure SQL are not partitioned by date. Instead, new data from the export is simply appended to the table, while any changed data will be updated in the affected rows.

Adding columns

When you edit the data table in Data Builder to add another field, this field will be added to the table in Azure SQL as a new column the next time the export runs. This column will be populated with the field's values going forward. Historical rows from before the new column was added will have the new field's value set to null.

Dropping columns

When you edit the data table in Data Builder to remove an existing field, this field's corresponding column will be dropped from the table in Azure SQL the next time the export runs.

Be careful: when dropping a column, historical data for the dropped fields will be deleted.

Prerequisites

Before you begin:

• Make sure your Power My Analytics plan includes data exports (available as a paid add-on on the Business, Grow, and Pro plans; included with the Custom plan when your agreement includes data exports)
• Create the Data Builder data table(s) you want to export
  
• Obtain your Azure SQL credentials:
  
  • Hostname
  • Port (default: 1433)
  • Username
  • Password
  • Database name
• Whitelist the following IPs in your Azure SQL instance:
  
  • 35.188.118.242
  • 35.209.185.69

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

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 Date Range and set it to the entire range of data you want to export (up to 2 years for most PMA connectors).
   
3. To backfill from a specific start date up to yesterday, choose Custom start to date and enter a start date.
4. Click Set Range, then Save to save the data table.
   
   
   

Step 2: Create Your Export and Choose the Destination

1. Go to Exports > SQL 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 your Azure SQL server if it is already connected. If you haven't yet connected your server, click the plus button to add a destination account: in the Connect SQL dialog, select Azure SQL as the Type, enter your server's Host (IP address or host name), Port (default: 1433 for Azure SQL), Username, and Password, and under Name provide a nickname for this destination (e.g., "Azure SQL instance - [your organization]"). Click Connect, then return to the wizard and restart the export setup.
4. For Database name, enter the name of the database you want to use. Click Next.

The Help panel in the Connect SQL dialog lists the IP addresses PMA uses to interact with your database: 35.188.118.242 and 35.209.185.69. Make sure both are whitelisted on your server.

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.

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 Schedule, Review, and Create

1. Choose a Refresh period: On demand, Monthly, Weekly, Daily, or Hourly. Since this export is being used for the initial backfill, select On demand and leave Run as soon as you create the export enabled. An On demand export has no schedule and runs only when you run it manually from the exports list. (For the scheduled refresh periods, the Timezone is required and determines the local wall-clock time at which your export will run.) Click Next.
2. Under Review, check the Destination, Data, and Schedule summaries; click Edit on any card to make a change. Then click Create export. Because Run as soon as you create the export was enabled, your initial backfill begins immediately.

Step 5: Verify the Backfill

1. On the Exports to SQL 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.

Step 6: 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 Date Range to a rolling window (e.g., last 30 days for e-commerce to account for returns).
3. Click Set Range, then click Save.
   
   
   

Configure your Azure SQL export

1. Return to Exports > SQL. 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. You will be prompted to choose a day of the month (for Monthly, 1 through 28), day of the week (for Weekly), and hour of the day (for Monthly, Weekly, and Daily). Confirm or update the Timezone in which the export should run.
3. Save your changes.

Important: Ensure your data table's date range in Data Builder is at least as long as the refresh period in the Azure SQL export in order to avoid missing data. 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.

Troubleshooting

If you encounter any issues with your exports:

1. Check the export logs by opening the three-dot action menu next to your export and selecting View logs.
2. Verify that your Azure SQL credentials are correct and that the PMA IP addresses are whitelisted.
3. Ensure your data table's date range aligns with or exceeds your export schedule to prevent data gaps.