Methods to join your information to Tableau Prep Builder with Supermetrics API


With a purpose to construct an actionable and insightful report in Tableau, you might want to have well-prepared and clear information.

Nevertheless, since companies have information in many alternative locations and codecs, cleansing information for evaluation can develop into a heavy course of.

That’s till Tableau launched Tableau Prep Builder to empower information customers — together with analysts and entrepreneurs who could not have deep analytical expertise — to remodel their information.

On this article, I’ll present you the best way to join your information to Tableau Prep Builder utilizing Supermetrics API and share some methods you possibly can leverage your information there.

Listed below are the steps we’ll undergo:

  1. Set up Tableau Prep Builder
  2. Set up and arrange TabPy
  3. Create a small Python script that can include the Supermetrics API name
  4. Create the flows in Tableau Prep Builder, which is able to create Tableau extract recordsdata for us
How to connect Supermetrics API to Tableau Prep Builder Flow

After we’re accomplished, you’ll have the ability to mix the output of Supermetrics API queries with different information sources by way of Union and schedule an incremental refresh of a Tableau extract with information from our API.

Sounds good? Let’s get began!


See Supermetrics API in motion

Uncover the best technique to feed your advertising information into Tableau or every other BI instrument, database, or information warehouse that helps JSON or CSV inputs.

Step 1: Set up Tableau Prep Builder

Tableau Prep Builder is a knowledge transformation instrument that Tableau launched in 2018 to assist its customers clear and put together information for evaluation.

The perfect factor about Tableau Prep Builder is that it enables you to visualize all of your information transformation operations. That approach, you’ll have the ability to comply with what’s occurring along with your information and determine errors shortly. The result’s the output of your information set in Tableau Hyper Extract — which is optimized for constructing experiences in Tableau. 

Since Tableau Prep Builder is accessible within the Tableau suite, you could have already got entry to it.

Setting it up is kind of simple too. All you might want to do is obtain it from the Tableau web site, set up it in your pc, and eventually, enter your license key or begin the free trial.

Step 2: Set up TabPy

TabPy is the second element that we’d like for this answer. 

TabPy is a Python extension for Tableau. It enables you to run Python scripts proper inside Tableau dashboards or, and most significantly for our case right here, inside Tableau Prep Builder. 

You’ll find the set up directions on the bundle’s Github. After all, you have to a latest model of Python for it to work. As soon as the set up is full, you can begin utilizing TabPy by operating the background course of in a terminal window. It’ll let you know that it’s operating and listening for incoming requests from Tableau on port 9004. 

The ultimate piece of prep for TabPy is to configure the connection to it in Tableau Prep Builder. 

You’ll find the required choice within the ‘Assist’ menu of Tableau Prep underneath ‘Assist’ > ‘Settings and Efficiency’ > ‘Handle Analytics Extension Connection’. 

For this information, we assume you might be setting this up in your native machine, so all you might want to do is be sure that TabPy remains to be operating and that the port you see within the window matches the port that TabPy is listening on.

Tableau Prep Supermetrics API, analytics extension connection

Click on the ‘Check in’ button, and also you’re accomplished!

Step 3: Arrange the Python script

It’s time to arrange the Python script. 

In case you might be nervous about that step, don’t be. It’s fairly easy. The script we’d like will simply have just a few traces of code, most of that are the API hyperlink from Supermetrics. 

You may even simply copy and paste this right into a textual content file and substitute the ‘[PLACEHOLDER FOR YOUR API LINK]’ with your individual hyperlink: 

import requests
import pandas as pd

def get_data_to_flow(enter):
    response = requests.get("[PLACEHOLDER FOR YOUR API LINK]")
    outcomes = response.json()
    return pd.DataFrame(outcomes['data'][1:], columns=outcomes['data'][0])

Save this as a file with the .py extension someplace the place you’ll simply discover it once more. We’ll be utilizing this within the following steps. 

Let’s generate an API hyperlink in Supermetrics Question Supervisor subsequent, and whereas we’re there, we will even obtain a pattern of our information in CSV format. 

To do that, log in to the workforce web site.

Be aware that you could entry the Supermetrics Question Supervisor with a legitimate Supermetrics API license. If you happen to don’t have a license but, begin your 14-day free trial.

Then, select ‘Integrations’ > ‘Question supervisor’. You’ll see a sidebar on the left of your display. Begin constructing your question there. On this instance, I’ll use Google Adverts as a knowledge supply. Proceed constructing your question by filling in these fields:

  • Choose dates
  • Choose metrics
  • Break up by dimensions
  • Filter & Choices

When you’re accomplished, click on ‘Run’. You’ll see your information in a preview desk and uncooked JSON format.

Tableau Prep Supermetrics API, Query Manager

Right here’s a tutorial on the best way to get began with Supermetrics API.

Tableau Prep will want this pattern to grasp the information construction that can come within the API response. 

Be aware that the format choice is ready to ‘JSON’ and never ‘Tableau’ as a result of we’ll let the Python script— which is written to work with the response in JSON format — deal with that API name. Then I exported the outcomes as a CSV file (1) and copied the API URL to the ready Python script file (2). 

Don’t neglect to save lots of the script file after pasting the Supermetrics API hyperlink!

Step 4: Carry all of it collectively in Tableau Prep Builder

We’re nearly there! 

To get information to movement from Supermetrics API into your Tableau Prep movement, we now have to attach the CSV file we downloaded from the earlier step. 

Since Tableau Prep Builder is kind of choosy in regards to the information varieties, it gained’t settle for the output of our Python script with out this step. The CSV file has the identical construction as the information from Supermetrics API, informing Tableau Prep Builder the best way to interpret that.

Tableau Prep Supermetrics API, connect, text file

After deciding on the file, Tableau Prep Builder will begin a brand new movement and skim within the content material of the CSV. You may see within the infobox that it has parsed the sphere varieties accurately as date, string, and quantity, respectively. And that’s precisely what we would like.

Tableau Prep Supermetrics API, connections

We have to add a script as the subsequent step within the movement. Do this by clicking the plus signal subsequent to the field representing the CSV file and deciding on ‘Script’.

Tableau Prep Supermetrics API, connection add

That is the place we get to make use of TabPy. So be sure that the radio button for ‘Tableau Python (TabPy) Server’ is activated and that TabPy remains to be operating in your machine. Then browse to the script file you’ve created within the earlier step. 

We additionally want to inform Tableau Prep Builder which capabilities from that file we need to use right here. Our tiny script solely has one perform, so when you’re following together with this information, sort the perform identify ‘get_data_to_flow’ into the sphere.

Tableau Prep Supermetrics API, connection script

As soon as these settings are accomplished, Tableau Prep ought to instantly begin executing the Supermetrics API question, and information will seem within the preview window. 

Congratulations! You have got simply efficiently loaded information from Supermetrics API to your Tableau Prep movement.

Leverage your information in Tableau Prep Builder

You may simply management what information to load by Supermetrics API by modifying the API hyperlink. In any other case, you need to use this information enter ingredient within the Tableau movement as you want. 

If you happen to’re utilizing our quick URLs, you will discover out the best way to edit parameters on this documentation article.

So long as you don’t change the form of the information by including or deleting columns, you possibly can mess around with the outcomes. If you might want to change the form of the information, keep in mind to vary the CSV file accordingly in order that Tableau Prep can interpret the API response.

Now, let’s discover some capabilities in Tableau Prep Builder you need to use to leverage your information for evaluation.

Append information from Supermetrics to a CSV dataset (UNION)

The very first thing we are able to do is carry out a UNION operation. For that, as a substitute of simply supplying the pattern CSV file for Tableau Prep Builder to interpret the API output, we will even use it as a method to import static information.

Think about having a set of historic information that gained’t change any extra. A standard use case can be exporting final yr’s information for a metric, which you want to use in a Tableau report, but additionally appending a every day up to date dataset to this to maintain it up-to-date.

For instance, I configured a question in Question Supervisor to get Google Adverts information for 2020 and downloaded the outcomes as CSV.

Tableau Prep Supermetrics API, json query

Then, I modified the date selector from ‘Final yr’ to ‘Yr so far’ and copied the API hyperlink for this question into the Python script file. Right here’s how the script file seems like. Be aware that my distinctive API key has been redacted.

Tableau Prep Supermetrics API, json query preview
Tableau Prep Supermetrics API,

Now we undergo the identical steps as earlier than. We have to add a UNION operation and an ‘Output’ within the movement:

  1. Begin a brand new movement in Tableau Prep by first connecting to the CSV file.
  2. Add a ‘Script’ step after the CSV with TabPy serving the API name perform.
  3. Add a ‘Union’ step after the script, then click on and drag the CSV step onto it, forming a triangle.
  4. Add an ‘Output’ step after the UNION.
Tableau Prep Supermetrics API, output function

Operating the movement now will give us a hyper-extract that can be utilized as a knowledge supply in your Tableau mission. And since the API name has a relative date vary (yr so far), you possibly can merely maintain re-running the movement to replace the extract! 

You can even select to save lots of the output file to your native arduous drive or immediately publish it to a Tableau Server or Tableau On-line to share with different Tableau customers in your group.

Incremental extract refresh with information from Supermetrics

The tactic described above will work nice for small to medium-sized quantities of information. Nevertheless, as soon as your queries to the API develop into bigger and extra demanding — when it comes to runtime sources — it’s a good suggestion to carry out an incremental refresh of the information set as a substitute of requesting the full-time vary on every movement run and overwriting the earlier extract.

Thankfully, Tableau Prep comes with a built-in choice for incremental updates of an current extract file. You simply must specify which column within the information desk must be used for figuring out the brand new rows of information coming from the refresh. The movement will then care for the remaining for you. 

Nevertheless, Tableau Prep Builder doesn’t assist this for information by a script execution.

Right here’s the best way to work across the limitations and create a working answer for incremental information refresh.

We are going to break up the method into two elements:

First, we create a Tableau Hyper Extract that can include the replace. Then apply that to the precise information supply within the second step. That is essential as a result of incrementally updating an extract is certainly supported when one other extract file is the supply of refresh information.

Tableau Prep, full refresh of intermediate extract and incremental refresh of final extract flows

To get there, we create a movement that features a script once more. We use a relative time vary in our API name that can maintain the quantity of information we request light-weight. I exploit ‘the final 10 days’ on this instance.

Tableau Prep Supermetrics API, query manager view

To create the extract file that shall be our ‘Refresher’, we’ve got to make use of what we’ve realized to this point about scripts in Tableau Prep Builder. 

Right here, I used an current extract file because the enter earlier than the script step to reveal that it additionally works and that the preliminary CSV file we used is only a vital step to run the script. 

With out the UNION operation, the output of this movement will merely be no matter our script step is pulling by way of Supermetrics API.

Tableau Prep Supermetrics API, output function

Having saved this output file on my drive as ‘increment.hyper’, I can now arrange the ultimate movement that can replace a printed extract used for a number of experiences.

Tableau Prep Supermetrics API, extract function

This would possibly look counterintuitive — it positive was for me. 

We’re connecting to the increment file in Tableau Prep Builder, and all we do is add an ‘Output’ that factors to the file that we have already got and need to replace. 

Right here, I’ve an non-obligatory cleansing step within the movement that separates the 2. The essential bit right here is to allow incremental refresh on the ‘Enter’, our ‘increment.hyper’ file. We have to set the sphere that shall be used to detect new rows coming into the movement. In our case, that’s ‘Date’. 

Then, we’ll decide the ‘Output’ that factors on the vacation spot we want to replace and match the ‘Date’ area from the ‘Enter’ with the corresponding ‘Date’ area within the ‘Output’.

This setup offers us a brand new choice of operating the movement as an incremental refresh. Nice!

Tableau Prep Supermetrics API, output function, incremental refresh — append to table selection

This methodology preserves no matter information we have already got within the output file. It may be an enormous extract with hundreds of thousands of rows — we’re simply including new rows to it on this process. After you have created these flows, it takes just some seconds to run them and replace the information. In spite of everything, we are actually solely querying the Supermetrics API for 10-days price of information.

That’s it!

Utilizing Supermetrics API as a knowledge supply in your Tableau Prep Builder helps you carry out advanced information modeling duties which often require a knowledge warehouse.

If you happen to’d prefer to superpower your Tableau experiences, e-book a demo with our gross sales workforce or begin a 14-day free trial of Supermetrics API.


Please enter your comment!
Please enter your name here