Autopilot Data Studio Dashboard

At Jepto we love automation, and one of our favorite tools for B2B and B2C marketing is Autopilot. Visualizing the Beta sign-ups and Mailing list numbers was a great way for the team to see their progress over time and how they were going against achieving their goal. 

This post is going to cover in detail the steps to get your Autopilot data into Data Studio utilizing the free tiers of, Stitch Data and Big Query. Unless you have a lot of contacts (more than 250,000) you should comfortably sit in the free tier. No technical skills are required to set this up as everything is done via the browser. 

To get the data from Autopilot into Data Studio we will need to do two things, first we will need to get our data from Autopilot into BigQuery. To do this we will use a managed ETL (Extract Transform Load, a technical term for: move data from here to there and format it along the way) service called Stitch Data. Shout out to the Stitch team for doing a fantastic job of making this process easy. Then we can connect BigQuery to Data Studio and enjoy all the reporting customizations that Data Studio is famous for.

Let’s get started. 

Step 1 — Create a Stitch account at https://www.stitchdata.com

Step 2 — Select Autopilot as an integration.

‍Step 3 — Copy your API Key from Autopilot settings.

‍Step 4 — Insert your API Key, the rest of the settings are OK to be left as default.‍

Step 5 — Stitch data will check that the API key is valid.

Step 6 — Select all the tables, even if you are unsure if you don’t need all the data in Data Studio select everything, so you do not need to come back later.‍

Step 7 — Stitch does another check and will confirm that the integration is complete.

Step 8 — Now we need to select where to put the Autopilot data, so select Big Query.‍

Step 9 — Sign in to the Google Cloud Platform‍

Note: You will need a Google account for the next steps. It’s free to sign up though. Create a new empty project.

You can call the project whatever you want, but we will need this in a later step.‍

Step 10 — Go back to Stitch and authenticate with the Google Account you used to create the project above.‍

Step 11 — Select the Project and the closest data center to where you are located.

‍Step 12 — Stitch will confirm that it can access your Destination.

Step 13 — In the earlier step we set Stitch to run the data replication on a set period. This will ensure that your data is kept up to date in the future, but we want to get our data into Big Query now, so we can start building a Data Studio report. To do that, we go back to integrations and then on the Extractions tab select Run Extraction Now. Depending on the size of your Autopilot account this can take a few minutes.‍

Step 14 — Now we get to the fun part, Data Studio. Let's create a new Report and Create a new Data Source.‍

Step 15 — Select Big Query and then the Contacts Table, then hit Connect.

‍Step 16 — You should be presented with a complete list of all Contact attributes, click Add to Report.

‍Step 17 — Repeat the step above for the other Big Query Tables (lists, smart_segments and smart_segments_contacts) ‍

Step 18 — You should end up with 4 data sources.

‍Step 19 — We need to create a Data blend to get the number of contacts on each list, by the date the contact was created. As data blends are a left outer join, select contacts as the first source and then list as the second. Add all the required fields you would to the dimension and then save.‍

Step 20 — Finally we can add our first chart, a time series chart which shows the amount of contacts added to the list on the given day. This is just a basic example, and you can change the graph to be cumulative to show how your lists are growing over time.

This whole process is repeatable with Smart Segments, and you have a wealth of information around each contact for you to create all sorts of visualizations with your data. If you are curious, the report at the top of this page is a bar chart of each date, overlaid with a cumulative time series graph. 

If you have any questions or get stuck trying to get your Autopilot data into Data Studio please reach out.