Web Metrics | Search Marketing
Site Strategy

"In a nutshell, Jason created our marketing analytics capability. He was able to figure out what data we collect, where it is, what was missing, and hook it all up so we canget meaningful, actionable data. Our marketing efforts have improved leads and conversions in some cases by an order of magnitude. He knows his stuff."
Chris Foleen, Marketing Project Coordinator, TransCore, Inc.


Friday, June 27, 2008

Analyzing PPC with Tableau

One of my favorite tools is Tableau. It makes data analysis very easy. So easy, in fact, I hardly ever even look at spreadsheets anymore. I just drag them into a Tableau window and get on with it.

One of the things I do a lot of is manage PPC spend. There are a lot of ways to manage PPC, and everyone has their own little tricks and tools they like. But, overall, PPC is a numbers game--trying to find the most efficient use of client dollars to get the most valuable traffic flowing into a website. To do this, we look at a number of different metrics--bounce rates, conversion rates, CPC, total cost, etc. In fact, there are so many different metrics to look at, it can be difficult to know where to start!

Where I like to start is with Tableau and a month's worth of PPC keyword data. First, I export a keyword report from Google. Then I delete some junk in the spreadsheet, and plop it into tableau (well, actually, I point my pre-existing Tableau PPC workbook at the new spreadsheet) and instantly the new data populates the charts and dashboards with the current information.

Now, from here you can create all sorts of different views into the performance of your keywords. When I mean all sorts, I mean ALL SORTS. I've spent days and days creating new and ever more fancy iterations of keyword data only to reformat it all the next month and come up with something else. But over time, I've found that there are a couple of views that really help, and those are the ones I go back to every time. If you are trying to control costs, or look for ways to cut out fat, or are just looking for where to start, a great chart to create is a Click-Cost Scatter Chart. When you apply linear averaging and 95 Percentile reference lines, you get an instant view of which individual keywords are budget hogs and if that spend is justified. This is mostly useful for trimming back on costs by finding trouble spots and increasing efficiency, not for expanding campaigns.
In this example, just look for anything above the expectation line (red dashed line--linear average), anything dark green, and anything in the upper right quadrant (set for 95 Percentile). If it meets those 3 criteria, investigate it. That keyword had better be producing conversions (or contributing to some sort of business goal) to justify it's expense. If it isn't, it may be time to cap spending on that keyword or even pause it. In any case, it takes about 5 seconds to spot trouble.
In Tableau, when you mouse over the circle, it gives you the details of the keyword, including, what keyword it is, which I have hidden for this example. This is just a starting point for analyzing PPC. It's a good way to save time and find the fat in a consistent, repeatable way.