New! Local SEM Competitive Intelligence
Discover Your Competitor’s PPC Keywords & Ad Copy
Enter your #1 competitor's URL

6 Advanced Ways to Better Visualize PPC Data

Data Visualization PPC

While it may seem simple, when we build out PPC reports, we sometimes lose sense of the chart types and the math that get us to real insights. Yes, scatter plots, regression analysis, and those sorts of reports may be a bit much for the design department, but as an analyst in charge of getting results, you should be using the tools needed to get the job done.

The techniques outlined below combine two of my own personal metrics when using data visualization: Time to Insight (TTI), i.e. how much time it takes for me to drill in and see what this graph can reveal, and Information Density (ID), i.e. how much information I can effectively and accurately take in.

Let’s take a look at the six advanced ways to better visualize PPC data.


#1: Simple Excel Conditional Formatting
TTI – 3/5
ID – 2/5

The quickest and simplest way to draw a bit more insight out of your data is to use Conditional Formatting (or color-coding based upon values) in Microsoft Excel. This allows you to highlight metrics based upon their results. For example the higher the bounce rate, the more red the cells become, the higher the page views per visit, the more green the cells become.

How this is helpful: Without even looking at the numbers from the example below, we can quickly tell that our most popular keywords are not engaging users. This means we need to look at the landing pages for those keywords to find opportunities for improvement.

Conditional Formatting Excel PPC

#2: Scatter Plot
TTI – 3/5
ID – 4/5

Scatter plots offer a more graphical representation of multiple columns of data. A scatter plot allows for a x/y axis as well as size and color dimensions. When you need to compare and contrast a few variables against a large set of dimensions (in this case keywords), you can’t get much better than a Scatter Plot.

How this is helpful: After filtering out some of the top keywords such as (not provided) we can quickly see the unfortunate trend of the higher the traffic, the higher the bounce rate. We notice that a majority of our keywords are within the 80% and above range, which isn’t good!

Scatter Plot PPC

The second screenshot drills a bit deeper to understand which campaigns are causing the most issues. When we break down the chart a bit more by campaign, we notice that organic, (notset), and brand campaign groups are performing much better than the other groups. This gives us a place to start concentrating on.

Scatter Plot PPC


#3: Treemap Charts
TTI – 2/5
ID – 3/5

To further analyze the issue, we want to look at the landing pages for each of our campaigns. Since there are many landing pages and many campaigns, this can be nearly impossible to analyze with Excel. No worries, a treemap is excellent for these multi-level groupings due to its clustering abilities. You can use this instead of a Pie Chart!

How this is helpful: Since we found out which campaigns are not performing as well, we can quickly drill into the treemap to see which landing pages we should start to improve. It is quite obvious that in ALL campaign groups the /desktop-trial and /datawatch-tableau page gets the most traffic but has a consistently bad bounce rate. We should certainly start to improve that page.

TreeMap Charts PPC

Heat Matrix
TTI – 3/5
ID – 2/5

Many times as a PPC or Marketing Manager, we need to compare a similar element against each other, like when certain keywords are used together or to compare which pages or products have affinity towards each other. We can use a Heat Matrix with conditional formatting to quickly glean insights from large amounts of information.

How this is helpful: In order to understand a bit more about our two landing pages with a high bounce rate, we use a heat matrix to quickly identify the pages some visitors are clicking through to. In this case it looks as if users go back to the homepage as well as the products section. Perhaps more callouts to the product areas could decrease the bounce rate.

Heat Matrix PPC


Regression Analysis (Forecasting, Estimating)
TTI – 3/5
ID – 4/5

Many ad networks, advanced keyword tools (iSpionage included), and even stock trading work with an age old theory of Probability and Confidence factors. Analysts call this Regression Analysis, and it is now starting to be built into tools (including Excel using the Analysis Tool Kit Plugin). You can use it to view correlations over time, estimate impact in the future, or determine what sales a marketing campaign may yield based upon past results.

How this is helpful: While not a real marketing example, the image below depicts the science surrounding regression analysis. The blue line represents the trend and can be used to forecast results over time.

Regression Analysis

Intelligent Alerts
TTI – 5/5
ID – 4/5

The holy grail is having technology work to provide the WHAT and WHEN but also help in understanding WHY as quickly as possible. Many systems (including Google Analytics) have alerts built in. While Google’s alert system uses very simple algorithms (traffic is up or down by 20%+), other complex systems like Metric Insights look at the significance of the spike in correlation to new events, time of week, etc.

How this is helpful: A spike in traffic from Sunday to Monday in the example below is not as noteworthy as a spike mid week. Thus, an intelligent alert will notify you when a meaningful change takes place.

Intelligent Alerts

This is just the tip of the iceberg. Learn how to use these advanced visualization tactics to take off the report monkey costume and to put on the analyst wizard hat. You’ll impress your boss with the way you analyze the data to find a way to save an extra 7% on broad based keyword groups or get a raise when you suggest a marketing campaign be tested to a small group of users first due to ROI concerns and end up being right.

Bar chart image credit: Lauren ManningCreative Commons, resized


ThomasThomas is the CEO and Founder of MashableMetrics, a web analytics agency specializing in automating, transforming and presenting disparate data. While playing a diverse role in the Internet space, Thomas has kept data-driven management at the forefront of his interactive strategies since 1996. Starting as a “bandwidth salesman” at a time when it still took persuading to sell companies a T-1 broadband pipe, Thomas has since managed and consulted some of the most influential organizations including Microsoft, Caterpillar, Orbitz, MetLife, and State Farm on how to make the most out of the digital channel.