AMG-blog-header-adwords-tips

Using Tableau to Visualize Search Data

John Warner AdWords Tips 1 Comment

Share Button

Tableau is a business intelligence software that makes building data visualizations simple and easy to understand.  It allows companies of all sizes to analyze, present, and update data to create interactive visualizations.   I love the software and by no means does an individual’s purchase of Tableau have to be strictly used for AdWords purposes.  There are many other interesting uses of Tableau as it allows you to easily analyze a wide variety of data.  I hope that didn’t sound too mushy. Let’s get started on why you are here!

When You Should Use Tableau

There are two uses for Tableau as an Account Manager and they have different levels of effectiveness.

  1. Showing Data to Your Clients
  2. Using Data Internally

Showing Data to Your Clients is an excellent use of Tableau. It allows you to inform them of the state of an account and pinpoint areas of improvement you have made or would like to make.  It is the perfect tool for signing new clients when they ask what changes can be made in their account. Tableau is also excellent for illustrating progress in an existing account.  The same is true if you are an account manager working for a large corporation whose CMO may not be as comfortable with Paid Search as yourself.

Using Data Internally: If you are an account manager, you are able to view the trends in your data quickly and easily without a data visualization.  Other tools, such as Optymyzr, give you the ability to analyze and create solutions and I would recommend using tools specifically catered to AdWords for account optimizations.

Skills Required for Using Tableau

To build visualizations in Tableau, you only need three important skills:

  1. Dragging
  2. Dropping
  3. Clicking

That’s it for technical training required.  Let’s get started!

Exporting Data From AdWords to Excel

The first thing that we need to do is export our data from AdWords.  For most accounts, I recommend using the last 90 days data to have a good feel for the account.  There are several ways to have your AdWords data appear in Tableau.  I prefer to export the data from Tableau into Excel and take the data from an Excel sheet.  It is just more comfortable for me and it allows me to keep old data sets from previous reports saved on my computer.    The steps to do this are pretty simple.

(Note: for this article, I will be using Adventure Media Group’s Internal AdWords Data.  Recently, it has been focused on marketing CEO Isaac Rudansky’s new retargeting course)

First, access your AdWords Account.  Next, look at the top of the page and click the “Reports” tab.  If you have an existing table report that you would like to analyze, feel free to click that, otherwise we will have to create a new report.  Click the +report  button and you will be brought to this page.

Hit  Save as  and give the report a name. The button is in the top right corner.

Now it’s decision time.  Huzzah! You’re going drag different metrics into this report to load them into your Excel report that will be uploaded into Tableau.  There are two points that I’d like to touch on before you make your decision on what you want to select.

  1. Know your data limits: Be careful about the number of rows in the bottom corner. Too much and Excel will struggle to process it.
  2. Certain data will not match with each other: That’s an unfortunate part of this process that is unavoidable. Some data within Google is not easily compatible with each other.

There is no standard amount of fields you need to use.  Feel free to be adventurous with your data.  I prefer to use the fields below to create my reports.

 

Columns

Rows

Campaign

Impressions

Search Keyword

Cost

Quality Score

Avg. Position

Ad Group

Interaction Rate

Interactions

Conversions

All Conv. value/cost

Conv. rate

Search Impr. Share

Bounce Rate

Pages/session

One last thing before you export the data: Make Sure to Set Up Filters.  Go to the top left part of your web page and click the filter button under your reports name.  I recommend changing the Campaign, Ad group, and Search keyword state to only show enabled keywords.  Once you have completed this, click the download button and save the report as an Excel.csv.

Uploading Your Report from Excel to Tableau

There are two steps to this process.

First: Save your Excel.CSV from the AdWords report you just created.  Within Excel, change the Excel.CSV file to a standard Excel Workbook. This will allow Tableau to access it.

Second: Open up Tableau.  Go to the blue Connect column and click  Blue Excel Picture. Find your file and select it. You will be brought to this page.

Picture after second

Now it is Data Visualization Time!

Creating Your Visualizations

Hooray You Made It!  We have gotten through the incredibly monotonous set up so, at this point, it would seem proper for you to give yourself a good pat on the back, stretch a bit, and to let the pride of completing your first step consume your entire self for a few moments…

We are going to start building our visualizations now.  I cannot go through every variation of data you can put together.  Instead, I am going to show you some of the things I like to add to my graphs and some of the cool features you can use.

At this point, you may be asking yourself, “Self, where do we begin?” Let’s get started with building the foundations for our first visualization.

The first graph I always like to create is a comparison of ROAS (All conv. value / cost) and Search Impression Share.  This allows me to analyze where I am converting compared to my cost with where I am spending my advertising budget.

Click the orange Orange Sheet 1 button.  This will bring you to your first graph.  Head back to your Data Source tab (at the bottom) and change your Search Impr. Share from a string into a decimal.

Number Decimal

You do this by hitting the ABC button where the Search Impr. Share column begins and choosing Number (Decimal).  This will give your Search Impression Share a numerical value rather than a string value.

Now, Drag Search Impr. Share into the Column and ROAS into the Row section above.

All conv. value

After that, drag Search keyword to under the Detail and Tooltip boxes.

Search Keyword

This will ensure that your data is separated and can be analyzed accurately.  At this point, your graph should look like this.

Graph

Not too exciting I know, but how about we spruce it up a bit? Click the Show me 2 button in the top right-hand corner and change it to the chart on the bottom right corner.  It is called packed bubbles.

We’re going to add a minimum impression threshold by dragging impressions into the filter, selecting all values, clicking, at least, and changing the number in this section.  For this account, we are going to select 25 impressions.

Range of Values

One last thing for this graph.  Let’s change the colors of the Sum (Search Impression Share).  Hit the drop down menu and select edit colors.  Choose Red-Green Diverging as your colors.  Next, hit Advanced and select the center box.  Change the number to .75 or whatever number you might consider to be a good Search Impr. Share for your account.

Edit Search Impr. Share Photos

Click Okay and your graph will look something similar to this.

Bubble Graph

If you have a billion small dots due to your large account, don’t worry.  Just make sure to raise the impressions if you feel there is too much small clutter.  You can also analyze your account by the ad group or campaign level if it is large enough to warrant.  The large bubble on the left shows us that we have a profitable Phrase Match keyword that does not have a high Search Impr. Share.  We now know that we should bid more aggressively on this keyword.

Analyzing Total Cost and ROAS

Awesome! You have your first graph.  We are going to build a few more graphs to accompany the one previously made to create an interactive Dashboard.

The first graph we are going to build is used to determine what is and is not working and how much you are paying.  Right-click the tab of the sheet you just created at the bottom of the page and select duplicate sheet.

Duplicate Sheet

You should have a copy of the sheet you just made.  Go to the  Show me 2  button and select Scatter Plot.  Switch Out Search Impr. Share for Cost and drag the Ad group into the Color button box.  This will allow you analyze which keywords are in what Ad group.  With larger accounts, this is something I love doing at the Campaign level rather than the Ad group level.  But being that this account does not have a ton of keywords in it still active, we will do this at the Ad group level.

Graph 2

We have a ton of points now displayed on the graph and that is fantastic. We need a baseline ROAS to determine what will and will not be profitable for your account which we will say is 3.5 for this account.  Let’s visualize this!

Right-click the y-axis in your scatter plot and select Add Reference Line.

Edit Axis

Pick Band and a gray color will become visible on your entire graph.  Make sure the Scope is set to Per Pane to analyze only a specific part of the graph.  We are going to change the top band by clicking value, creating a new parameter, naming it “Not Profitable Above”, and making the current value 0.  Make sure that both labels are set to computational.

Create Parameter

Next, change the bottom band by clicking value, creating a new parameter, naming it “Profitable Above” and make the current value 3.5 (or if you are working with another set of data, whatever would be a high enough ROAS for your company to be profitable).

In formatting, choose to insert a solid black line and change the fill color to red as these points are worrisome.  This will act as an alert to which keywords are wasting spend.

Graph 3

Highlighting Total Cost and ROAS

We have established there are several keywords that have not been profitable, let’s bring home that changes should be made!  Create a new graph by hitting the button 1 button.  Add Cost to Columns and Campaign to rows.  Select packed bubbles again and you will have a bubble graph of your total cost of each campaign.

Let’s create a filter to get the point across.  Move All Conv. Value/Cost to the Filter, choose All Value. Select At most and insert 3.5.  You are now displaying the total cost for keywords that do not have an ROAS greater than 3.5.  Use the impression threshold filter as we did on the previous graph.  Make sure you only display campaigns whose keywords matched the impression threshold required to be displayed.  I did this by adding campaign to the filter box and hand selecting only those that were showing on my graphs up to this point.  That way, you will not have a list of campaigns whose data is not being displayed in your future Dashboard.  Also, click the Color button button and change the graph’s color to red as the number you are showing is bad.  Only one of our campaigns matched the criteria and is shown below.

Bubble graph 2

Building a Dashboard

You have now built three graphs.  Fantastic!  Let’s put them all together in our first dashboard.

Next to the button 1button, click the create a dashboard button Button 2 .  Then, drag the graphs over from the left-hand column into Drop Sheets Here.  If you have not renamed your sheets by now, I would suggest changing the names by double-clicking the tabs at the bottom.  You can stretch out the sheets to have them fit nicely on the page and organize the graphs in the way you see fit.  My dashboard currently looks like this.

Graph 4

My favorite part of Tableau is how interactive your Dashboards are.  Just for fun, try fiddling with the column on the right.  It lets you highlight specific aspects of your data.

Building a Story

You have just created your first Dashboard. Congratulations!  For the sake of time, I am going to create a Story using only the Dashboard we have created.  I would recommend making a Story with several customized Dashboards to give a truly fantastic and insightful presentation.

Next to the Button 2 button, select the Story button  Button 4.  In the Story section, select the Automatic size and insert the Dashboard we just created.  Name the gray box at the top “Dashboard 1”.  When creating an entire Story, you will want to have your story follow some type of storyline.  This makes the data easier to understand and helps explain how different Dashboards in your story relate to each other, but as we are just demonstrating how a story can be organized, I will just call it Dashboard 1.  Your Screen should look like this.

Slide 4

You are going to hit  Button 6 and move it to the front of your presentation.  Name this box “Title”.  Hit the Dashboard button again and select an image to place in the middle.  Right click the image and make sure to have it fit inside the Dashboard.  Customize your Dashboard to your liking.  I recommend adding a title and your name below the picture you have selected.  Dashboard 2 should look like this.

Slide 1

Go back into Story 1 and select Title.  Insert Dashboard 2 into this section.

In the top left corner, change the name of the Story.  Your screen should look something like this.

Slide 2

Don’t worry that part of your report is not showing from the beginning.  It will display properly in our presentation.  Speaking of which, hit the Button 7 button and see your masterpiece!  View mine below.

Slide 3 Slide 4 Slide 5

One of the very cool things about Tableau is that your presentations are connected to your Excel spreadsheets.  Once you have mastered a system that works for your business, you can upload data from any account under your MCC and view that data quickly in the graphs you have already created.  I will recommend that you experiment with Tableau more before you begin doing that to get a solid understanding of the software.

Conclusion

You can now create an interactive story for a prospective client, manager, or yourself.  I am sure they will love that you can explain your findings in a story at a precise level.  Visualizations are the key to building a smarter company.  Numbers can only mean so much, but when paired with other statistics and displayed, data becomes infinitely more powerful.  Make sure to use this power wisely and responsibly.

Tableau offers an incredible ability to analyze data like never before and it should be done in a responsible manner.  If you would like to learn more about how to analyze your AdWords Data and make your account run more effectively, check out the rest of our blog or feel free to contact us at any time.

Share Button

Comments 1

  1. Chris

    Hi John – loved the post!

    We actually run a digital advertising agency called Closed Loop just outside Sacramento. I totally agree that anyone running online advertising should be using Tableau.

    We found that our team was spending a TON of time manually wrangling the data from ad platforms, so we built a solution called Forager. Forager connects via API to your ad platforms and pulls reports each night, then normalizes the data and serves up a single master data feed. Then you can connect Tableau directly to this feed via SQL server.

    Additionally, we built a pretty slick “starter template” in Tableau that you can plug your data feed into, no code or technical know-how required.

    I’d love for you to try it and provide us some feedback. Takes about 5 minutes to sign up and connect accounts. Would you have a few minutes to provide your thoughts?

    http://www.getforager.com

    Thanks!
    Chris

Leave a Reply

Your email address will not be published. Required fields are marked *