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. An Enterprise plan is required to use Azure SQL exports.

How We Store Data in Azure SQL Exports

Prerequisites

Before you begin:

• Make sure you have an Enterprise plan with Power My Analytics
• Create the Data Explorer report(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 Explorer Report for Initial Backfill

1. In your PMA hub, go to Reports > Data Explorer and find your report. Click the pencil icon to Edit Report.
   
   
   
   
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 report.
   
   
   

Step 2: Connect to Azure SQL

1. Go to Exports > SQL in the left sidebar.
2. Click + Create Export.
   
   
   
   
3. Click the blue plus button under Destination to add a new destination.
   
   
   
   
4. In the Add Account dialog:
   
   • TySelect Azure SQL as the Type.
   • Enter the host, port, username, and password.
   • Provide a nickname for this destination (e.g., "Azure SQL instance - [your organization]").
   • Click Connect.
     
     
   
   
   
   
5. Choose the server you just added under Destination.
6. Under Database, enter your database name. Click Next.
   
   
   

Step 3: Choose Your Report

1. Under Report or Report Group, select either Single report or Group of reports.
   
   
   
   
2. Choose the specific report or report group from the dropdown. Click Next.
   
   
   
   
3. For now, select Monthly under Schedule and choose any day and hour (we'll adjust this later). Click Add to create your export, then click Confirm.
   
   
   

Step 4: Run the Initial Backfill

1. On the Exports to SQL page, find your new export. Click the play icon to run the export manually.
   
   
   
   
2. The export will begin running, as indicated by the three dots icon. Wait for the three dots icon to change back to a play button, indicating the export has finished.
   
   
   
   
3. Click the paper icon to view the logs and confirm the export was successful.
   
   
   

Step 5: Set Up Rolling Updates

Configure your Data Explorer report

1. Go back to your report in Data Explorer. Click the pencil icon to Edit Report.
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. Find your export and click the pencil icon to Edit Export.
   
   
   
   
2. Under Schedule, choose your desired frequency: monthly, weekly, daily, or hourly. Select the appropriate day/time for the export to run.
3. Click Update.
   
   
   

Important: Ensure your report's date range in Data Explorer is at least as long as the refresh period in the Azure SQL export in order to avoid missing data. For example, if your report'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 clicking the paper icon next to your export.
2. Verify that your Azure SQL credentials are correct and that the PMA IP addresses are whitelisted.
3. Ensure your report's date range aligns with or exceeds your export schedule to prevent data gaps.