With a view to construct an actionable and insightful report in Tableau, it is advisable have well-prepared and clear knowledge.
Nevertheless, since companies have knowledge in many alternative locations and codecs, cleansing knowledge for evaluation can change into a heavy course of.
That's till Tableau launched Tableau Prep Builder to empower knowledge customers — together with analysts and entrepreneurs who could not have deep analytical abilities — to remodel their knowledge.
On this article, I’ll present you how one can join your knowledge to Tableau Prep Builder utilizing Supermetrics API and share some methods you'll be able to leverage your knowledge there.
Listed below are the steps we’ll undergo:
- Set up Tableau Prep Builder
- Set up and arrange TabPy
- Create a small Python script that may include the Supermetrics API name
- Create the flows in Tableau Prep Builder, which can create Tableau extract recordsdata for us
After we’re performed, you’ll have the ability to mix the output of Supermetrics API queries with different knowledge sources by way of Union and schedule an incremental refresh of a Tableau extract with knowledge from our API.
Sounds good? Let’s get began!
See Supermetrics API in motion
Uncover the simplest strategy to feed your advertising and marketing knowledge into Tableau or another BI instrument, database, or knowledge warehouse that helps JSON or CSV inputs.
Step 1: Set up Tableau Prep Builder
Tableau Prep Builder is an information transformation instrument that Tableau launched in 2018 to assist its customers clear and put together knowledge for evaluation.
The most effective factor about Tableau Prep Builder is that it allows you to visualize all of your knowledge transformation operations. That method, you’ll have the ability to comply with what’s occurring along with your knowledge and establish errors rapidly. The result's the output of your knowledge set in Tableau Hyper Extract — which is optimized for constructing reviews in Tableau.
Since Tableau Prep Builder is accessible within the Tableau suite, you might have already got entry to it.
Setting it up is sort of simple too. All it is advisable do is obtain it from the Tableau web site, set up it in your laptop, and eventually, enter your license key or begin the free trial.
Step 2: Set up TabPy
TabPy is the second part that we'd like for this answer.
TabPy is a Python extension for Tableau. It allows 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 will have a latest model of Python for it to work. As soon as the set up is full, you can begin utilizing TabPy by working the background course of in a terminal window. It’ll let you know that it’s working 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 below ‘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 it is advisable do is ensure that TabPy continues to be working and that the port you see within the window matches the port that TabPy is listening on.
Click on the ‘Register’ button, and also you’re performed!
Step 3: Arrange the Python script
It’s time to arrange the Python script.
In case you might be apprehensive about that step, don’t be. It’s fairly simple. The script we'd like will simply have just a few traces of code, most of that are the API hyperlink from Supermetrics.
You'll be able to even simply copy and paste this right into a textual content file and exchange the ‘[PLACEHOLDER FOR YOUR API LINK]’ with your personal 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'])
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 can even obtain a pattern of our knowledge in CSV format.
To do that, log in to the workforce website.
Observe you can entry the Supermetrics Question Supervisor with a sound Supermetrics API license. When you 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 screen. Begin constructing your question there. On this instance, I’ll use Google Advertisements as an information supply. Proceed constructing your question by filling in these fields:
- Choose dates
- Choose metrics
- Cut up by dimensions
- Filter & Choices
When you’re performed, click on ‘Run’. You’ll see your knowledge in a preview desk and uncooked JSON format.
Right here’s a tutorial on how one can get began with Supermetrics API.
Tableau Prep will want this pattern to know the information construction that may come within the API response.
Observe that the format choice is about 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 overlook to avoid wasting the script file after pasting the Supermetrics API hyperlink!
Step 4: Deliver all of it collectively in Tableau Prep Builder
We’re nearly there!
To get knowledge 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 sort of choosy concerning the knowledge sorts, 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 how one can interpret that.
After deciding on the file, Tableau Prep Builder will begin a brand new movement and skim within the content material of the CSV. You'll be able to see within the infobox that it has parsed the sector sorts appropriately as date, string, and quantity, respectively. And that’s precisely what we wish.
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’.
That is the place we get to make use of TabPy. So ensure that the radio button for ‘Tableau Python (TabPy) Server’ is activated and that TabPy continues to be working 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 operate, so should you’re following together with this information, sort the operate title ‘get_data_to_flow’ into the sector.
As soon as these settings are performed, Tableau Prep ought to instantly begin executing the Supermetrics API question, and knowledge will seem within the preview window.
Congratulations! You will have simply efficiently loaded knowledge from Supermetrics API to your Tableau Prep movement.
Leverage your knowledge in Tableau Prep Builder
You'll be able to simply management what knowledge to load by means of Supermetrics API by modifying the API hyperlink. In any other case, you should use this knowledge enter factor within the Tableau movement as you want.
When you’re utilizing our brief URLs, you will discover out how one can edit parameters on this documentation article.
So long as you don’t change the form of the information by including or deleting columns, you'll be able to mess around with the outcomes. If it is advisable 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 should use to leverage your knowledge for evaluation.
Append knowledge 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 can even use it as a method to import static knowledge.
Think about having a set of historic knowledge that gained’t change any extra. A standard use case could be exporting final yr’s knowledge for a metric, which you want to use in a Tableau report, but additionally appending a each 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 Advertisements knowledge for 2020 and downloaded the outcomes as CSV.
Then, I modified the date selector from ‘Final yr’ to ‘12 months to this point’ and copied the API hyperlink for this question into the Python script file. Right here’s how the script file seems to be like. Observe that my distinctive API key has been redacted.
Now we undergo the identical steps as earlier than. We have to add a UNION operation and an ‘Output’ within the movement:
- Begin a brand new movement in Tableau Prep by first connecting to the CSV file.
- Add a ‘Script’ step after the CSV with TabPy serving the API name operate.
- Add a ‘Union’ step after the script, then click on and drag the CSV step onto it, forming a triangle.
- Add an ‘Output’ step after the UNION.
Working the movement now will give us a hyper-extract that can be utilized as an information supply on your Tableau mission. And since the API name has a relative date vary (yr to this point), you'll be able to merely hold re-running the movement to replace the extract!
You can even select to avoid wasting the output file to your native laborious drive or straight publish it to a Tableau Server or Tableau On-line to share with different Tableau customers in your group.
Incremental extract refresh with knowledge from Supermetrics
The strategy described above will work nice for small to medium-sized quantities of knowledge. Nevertheless, as soon as your queries to the API change into bigger and extra demanding — by way of 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.
Luckily, Tableau Prep comes with a built-in choice for incremental updates of an current extract file. You simply must specify which column within the knowledge desk must be used for figuring out the brand new rows of knowledge coming from the refresh. The movement will then handle the remaining for you.
Nevertheless, Tableau Prep Builder doesn't assist this for knowledge by means of a script execution.
Right here’s how one can work across the limitations and create a working answer for incremental knowledge refresh.
We'll cut up the method into two components:
First, we create a Tableau Hyper Extract that may include the replace. Then apply that to the precise knowledge supply within the second step. That is necessary as a result of incrementally updating an extract is certainly supported when one other extract file is the supply of refresh knowledge.
To get there, we create a movement that features a script once more. We use a relative time vary in our API name that may hold the quantity of knowledge we request light-weight. I exploit ‘the final 10 days’ on this instance.
To create the extract file that will likely be our ‘Refresher’, now we have to make use of what we’ve realized up to now about scripts in Tableau Prep Builder.
Right here, I used an current extract file because the enter earlier than the script step to display that it additionally works and that the preliminary CSV file we used is only a crucial 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.
Having saved this output file on my drive as ‘increment.hyper’, I can now arrange the ultimate movement that may replace a printed extract used for a number of reviews.
This may 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 elective cleansing step within the movement that separates the 2. The necessary bit right here is to allow incremental refresh on the ‘Enter’, our ‘increment.hyper’ file. We have to set the sector that will likely be used to detect new rows coming into the movement. In our case, that’s ‘Date’.
Then, we’ll choose the ‘Output’ that factors on the vacation spot we want to replace and match the ‘Date’ discipline from the ‘Enter’ with the corresponding ‘Date’ discipline within the ‘Output’.
This setup offers us a brand new choice of working the movement as an incremental refresh. Nice!
This technique preserves no matter knowledge we have already got within the output file. It may be an enormous extract with thousands and thousands of rows — we're simply including new rows to it on this process. After getting created these flows, it takes only a few seconds to run them and replace the information. In spite of everything, we at the moment are solely querying the Supermetrics API for 10-days value of knowledge.
Utilizing Supermetrics API as an information supply in your Tableau Prep Builder helps you carry out complicated knowledge modeling duties which normally require an information warehouse.