Connecting your data to Tableau Prep Builder with Supermetrics API [a step-by-step tutorial]

You need clean and well-prepared data in order to create a Tableau report that is actionable and informative.

Cleaning data for analysis can be difficult because businesses may have data from many places and formats.

Tableau Prep Builder was introduced by Tableau to enable data users, including marketers and analysts who might not have advanced analytical skills, to transform their data.

This article will show you how to connect to Tableau Prep Builder with Supermetrics API. I also share ways that you can use your data there.

These are the steps that we will follow:

Install Tableau Prep BuilderInstall, and then set up TabPyCreate. This small Python script will contain the Supermetrics API CallCreate flows in Tableau Prep Builder. It will create Tableau extract files.

Once we are done, you will be able to combine Supermetrics API queries and other data sources via Union. You can also schedule an incremental refresh for a Tableau extract using data from our API.

Does that sound good? Let’s get started!

Supermetrics API in action

Find out how to easily feed marketing data into Tableau, any other BI tool or database that supports JSON and CSV inputs.

1: Install Tableau Prep Builder

Tableau Prep Builder was released by Tableau in 2018 as a data transformation tool to assist users in cleaning and preparing data for analysis.

Tableau Prep Builder’s best feature is its ability to visualize all data transformation operations. This will allow you to quickly see what’s going on with your data, and help you identify any errors. The output of Tableau Hyper Extract is what you see. It is optimized for creating reports in Tableau.

You may already have Tableau Prep Builder access because it is part of the Tableau Suite.

It is easy to set it up. It is easy to download the Tableau software, install it on your PC, and then enter your license key.

Step 2: Install TabPy

TabPy is the second component we will need to complete this solution.

TabPy is a Python extension to Tableau. You can run Python scripts directly within Tableau dashboards, or, most importantly, inside Tableau Prep Builder.

The installation instructions can be found on the package’s Github. To make it work, you’ll need to have the most recent version of Python. After the installation is completed, TabPy can be used by opening a terminal window and running the background process. It will tell you that the background process is running and listening for any incoming requests from Tableau at port 9004.

TabPy’s final step is to connect it to Tableau Prep Builder.

The required option can be found in the Help menu of Tableau Prep, under ‘Settings & Performance’ > “Manage Analytics Extension Connect”.

This guide assumes that you are setting it up on your local machine. To do this, ensure that TabPy remains running and that the port displayed in the window corresponds to the port TabPy listens on.

You’re done when you click the “Sign In” button

Step 3: Install the Python script

Now it’s time for the Python script to be set up.

Do not be concerned about this step. It is very simple. It will only take a few lines to create the script that we require. Most of these are API links from Supermetrics.

This can be copied and pasted into a text document. Then, replace the “[PLACEHOLDER FUR YOUR API LINK]” with your link.

import requests import pandas as pd def get_data_to_flow(input): response = requests.get(“[PLACEHOLDER FOR YOUR API LINK]”) results = response.json() return pd.DataFrame(results[‘data’][1:], columns=results[‘data’][0])

This file can be saved with the extension.py and stored somewhere you’ll find it easily again. This will be used in the next steps.

Let’s create an API link in Supermetrics Query Manager and, while we’re there download a sample data file in CSV format.

You can only access the Supermetrics query manager if you have a valid Supermetrics API licence. Start your 14-day trial if you do not have a license.

Next, select ‘Integrations > ‘Query Manager’. A sidebar will appear to the left of your screen. You can start building your query there. In this example, Google Ads will be used as a data source. Fill in the following fields to continue building your query:

Select datesSelect metricsSplit according to dimensionsFilter and Options

Once you’re done, click ‘Run’. Your data will be displayed in a preview table or raw JSON format.

This tutorial will show you how to use Supermetrics API.

This sample will be needed by Tableau Prep to understand the data structure in the API response.

The format selection should be set to “JSON” and not “Tableau”. This is because the Python script, which was written to handle the JSON response — will handle the API call. The results were exported as a CSV file (1), and the API URL was copied to the Python script file (2).

After pasting the Supermetrics API URL, don’t forget about saving the script file!

Step 4: Combine it all in Tableau Prep Builder

We are almost there!

We need to connect the CSV file that we downloaded in the previous step to allow data flow from Supermetrics API to your Tableau Prep flow.

Tableau Prep Builder has a very strict policy about data types. It won’t accept our Python script output without this step. Tableau Prep Builder will be able to understand the CSV file if it has the same structure and data from Supermetrics API.

Tableau Prep Builder will open the file and begin a new flow. It will then read in the CSV content. The infobox will show that the program has correctly parsed each field type as string, date, and number. That’s exactly what you want.

As the next step, we need to add a text script. Click the plus sign beside the CSV file box and select ‘Script.

Here TabPy is activated. Make sure you activate the radio button for “Tableau Python (TabPy Server”) Server” and that TabPy remains running on your computer. Next, browse to the script file that you created in step 1.

Also, we need to tell Tableau Prep Builder what functions from the file we wish to use. This tiny script only contains one function. If you are following the guide, enter the name of the function ‘get_data_to_flow” into the field.

After these settings have been made, Tableau Prep will immediately execute the Supermetrics API query. Data will be displayed in the preview window.

Congratulations! Congratulations! You just loaded data from Supermetrics API into your Tableau Prep flow.

Leverage your data in Tableau Prep Builder

Editing the API link allows you to control which data Supermetrics API loads. You can also use this data input element as you wish in the Tableau flow.

This documentation article will help you to modify parameters if you are using our short URLs.

You can experiment with the results as long as the data shape isn’t changed by adding or subtracting columns. Tableau Prep will be able to interpret any API response if you have to modify the data’s shape.

Let’s now look at some Tableau Prep Builder functions that can be used to analyze your data.

Supermetrics ppend data to a CSV dataset. (UNION).

First, we can perform a UNION operation. Instead of just providing the CSV file to Tableau Prep Builder for interpretation, we will use it to import static data.

Imagine having historical data that doesn’t change. One common use case is to export last year’s data to a metric and then add a daily updated dataset. This will keep your data current.

To get 2020 Google Ads data, I created a query in Query Manager and downloaded the results to CSV.

Next, I changed the date selector to ‘Year-to-date’ and copied the API URL for this query into my Python script file. Here is the script file. You will notice that my API key has been removed.

We now follow the same steps as previously. It is necessary to include a UNION operation as well as an ‘Output” in the flow.

Create a new flow using Tableau Prep. Connect to the CSV file first. Click and drag the CSV step onto the Union step, creating a triangle. Add an ‘Output” step after that.

The flow will now produce a hyper-extract which can be used to create a Tableau data source. The API call is relative in date (year to date) so you can keep running the flow to update it!

You have the option to save the output file on your local hard drive, or to publish it directly to Tableau Server or Tableau Online for sharing with other Tableau users within your organization.

Supermetrics data – Incremental extract refresh

This method works well for small- to medium-sized data sets. It’s better to request the API full-time ranges on every flow run, and then overwrite the previous extract.

Tableau Prep has an option to incrementally update an extract file. It is as easy as naming the column of the data table that will be used to determine the new rows from the refresh. The rest will be taken care of by the flow.

Tableau Prep Builder doesn’t support data via a script execution.

Here are some ways to get around these limitations and create a workable solution for incremental data refresh.

The process will be divided into two parts.

First, create a Tableau Hyper Extract which will include the update. Next, create a Tableau Hyper Extract that will contain the update. This is crucial because incrementally updating an extracted file can be supported if another extract file is used as the source of refresh data.

We create a flow again that includes a script to get there. Our API call uses a relative time range to keep the data we request light. In this example, I use “the last 10 days”.

We will use the information we have learned about Tableau Prep Builder to create the extract file that we call our ‘Refresher.

To demonstrate that the script works, I used an extract file from another file.

The output of this flow without the UNION operation will be the same as what our script steps are pulling via Supermetrics API.

After saving the output file as ‘increment.hyper” on my drive, I can now set-up the final flow to update an extract published for multiple reports.

It might seem counterintuitive, but it was true for me.

Connecting to Tableau Prep Builder’s increment file is easy. All we have to do is to add an “Output” that points to the file we want to update.

This optional cleaning step separates them. Important is to enable incremental refresh of the ‘Input,’ our ‘increment.hyper.’ file. The field that will detect new rows entering the flow must be set. This is the field ‘Date’ in our case.

Next, select the Output’ that points to the destination you wish to update. Match the Date’ field from the ‘Input’ with the corresponding field in the “Output”.

This setup allows us to run the flow incrementally, which is a great option. Great!

This method preserves any data that we have already in the output file. This can be a large extract with many millions of rows. We are simply adding rows to it. It takes only a few seconds for these flows to be created and run. We are only querying Supermetrics API for 10 days.