How to load historical marketing data into Power BI

The Supermetrics API makes it easy to get fresh marketing data into Power BI using the “web data connector”. 

But, Power BI starts to run into issues when the data volume grows beyond 10k rows from a single query. For marketing teams and agencies with large amounts of advertising data, this is simply a non-starter.

To overcome these limitations, we recommend storing a historical CSV export of your marketing data locally in Power BI and then creating a new calculated table that draws data from both the web-based table and your CSV-based table.

To get there, all you have to do is follow these five simple steps.

1. Download your historical data from query manager
2. Configure your incremental URL
3. Import your historical data into Power BI
4. Import your incremental data query
5. Create a calculated union table

But before we start, if you don’t have a license to Supermetrics API just yet, you can always start your 14-day free trial now.

Step 1: Download your historical data from query manager

Supermetrics’ query manager is a great place for profiling your data before loading it into your BI tool, but it can also be used to export historical data from your marketing data sources. Simply run your query for the dates you’re interested in, and use the button at the bottom to export the results to a CSV. 

Pro tip: since the query manager is designed for data preview, it automatically limits the results shown to the first 1,000 rows. You can change this using the option under the “Rows to fetch” option under the “Split by” dropdown.

You may need separate CSVs if your data is too large, perhaps one per year, per quarter or per month depending on your data size. See the “Limitations” section below for more details.

Step 2: Configure your incremental URL

Historical data is great, but it’s static and unchanging. You want to be able to click one button and have Power BI pull in the latest data directly from your sources. This quick trick can help you do just that.

In ‘Query Manager’, go to the ‘select dates’ section. Select your desired start date, then write ‘today’ as the end date—overwriting the date in YYYY-MM-DD format—then run the query once. The start date parameter should stay fixed, but make sure that it aligns with the last day of data in your historical export.

You can now import the URL shown into Power BI, and Supermetrics will see ‘today’ as the end date of your query and return all of the available data up to the given day when Power BI goes to refresh the query.

Step 3: Import your historical data into Power BI

Next, you’ll load your historical data into Power BI. Note that if you have large datasets, it might be advisable to break your data into multiple different files rather than one single large file. 

I’m going to do this and rename my table “facebook_historical”.

Step 4: Import your incremental data query

After you’ve imported all of your historical data, you’ll use your modified query URL from the query manager to set up a web-based data source. For additional instructions on this, follow these steps.

I’m going to name this table “facebook_incremental”.

Step 5: Create a calculated union table

In this last step is where the magic happens. 

Now, we’re going to create a calculated table that merges our two tables as one. The (UNION) function in Power BI can take any number of tables and perform a SQL-style UNION on them. Basically, that means that with this single calculated table, you’re able to build visualizations based on the data contained in both “facebook_historical” and “facebook_incremental”.

The best part about this is that when you refresh your incremental table, your refreshes will run significantly faster since you’re only pulling a few months worth of data from the Supermetrics API rather than potentially several years.

Limitations:

The main purpose of this procedure is to increase refresh speed and work with larger data volumes without using any other tools. There are, but some limitations that you should take into account:

1. You may not be able to get all of your historical data from the query manager in a single query. The query manager can return up to 1M rows from a single query, but that may not be enough for everyone. If you find that your row counts are exceeding the maximum threshold, try breaking them down into smaller time intervals, maybe by year, quarter, or month.
2. The query manager has a query timeout of 30 minutes. If you can’t retrieve your data in 30 minutes, try reducing the date range of data requested for a single query and use the chunking method described above.
3. Power BI does not allow uploading files more than 1GB in size. If your files are approaching this limit, it’s best to chunk them out as described above.
4. There is technically no limitation to the size of an overall Power BI workbook. But, several users have reported to us that they experience significantly decreased performance when the size of their workbook approaches or exceeds 1GB.

One way to overcome these limitations is by investing in a scalable, cloud-based data warehouse for your marketing data. Data warehouses solve these size problems because they store data in the cloud, not on your local computer, freeing up your computer’s resources for actually running Power BI. The other big reason is that data warehouses are architected to store several terabytes of data, so size won’t be an issue as long as it has been correctly architected.

If you’re interested in learning about data warehouses that are compatible with both Supermetrics and Power BI, please check out information on the following solutions:

• BigQuery
• Azure Synapse
• Snowflake