Any PPC and SEO marketer can tell you that long-tail keywords are where the money is at.
But when dealing with thousands to millions of unique variations, how can you begin to comprehend the information? Your eyes just cannot process rows upon rows and multiple columns. The eye can, however, use its grouping ability to understand relationships, allowing the brain to do its job of reasoning.
In my last post, I shared 6 ways that data visualization can help you boost PPC campaigns. In this post, I’ll give a how to with Excel and Google Spreadsheets for conditional formatting and bubble charts.
Getting iSpionage Keyword Monitoring Data into Excel
Step 1: Go to any keyword monitoring report within iSpionage and click on Export.
Step 2: Choose Excel. A file will now download into your default directory (\Downloads).
Step 3: The iSpionage reports are all Delimited, click Next.
Step 4: The iSpionage reports are all Tab delimited, click Next again.
Allowing Quick Sorting for Data
Before we even start to add the charting elements, let’s enable the columns to be quickly sorted.
Step 1: Select the First Row in the Worksheet.
Step 2: Go to Data Toolbar, click on Filter.
Conditional Formatting—Quick and Dirty
Next we need to set up conditional formatting by following these steps.
Step 1: Select any column you want to visualize.
Step 2: Within the Home toolbar click on Conditional Formatting.
Step 3: Choose a formatting type.
By default when selecting color scales, Excel gives you Green-Yellow-Red and auto selects “Lowest – Mid – Highest” Values. You can change this by clicking on Manage Rules.
Step 4: Select Manage Rules.
Step 5: Choose the Conditional Formatting Rule you just setup.
Customizing the Color Palette and Conditional Formulas
Next, we’re going to customize Color Palettes and Conditional Formulas.
To get the color scale seen in the picture below, use the following settings:
- The Type to Number
- The Value to 2, 5, and 10 (or your desired threshold). If a lower value means something good, you’ll likely need to reverse the color palette by reversing the colors.
Another advantage to this approach occurs when there is a lot of disparity between the highest and lowest numbers. In our dataset, for example, there are a large amount of $.05 and N/A CPC values. Since the Percent and Percentile Type selections are based off of mean averages, this will dramatically change the color scale, which means we need to adjust accordingly.
Here are two ways you can handle this:
Other types of Conditional Formatting
Format only cells that contain: This allows you to color code keywords that contain the word “data” or have a NULL value.
Format only top or bottom ranked values: If you are looking for the highest or lowest values in a column. This is best used when comparing across columns, such as highlighting top ranked keywords and then another column color coded another way.
Format only values that are above or below average: This is essentially a shortcut to Percent and Percentile Types within “Format all cells based on their values”.
Format only unique or duplicate values: This is invaluable in cases where you need to dedupe keywords, page URLs, etc.
Use a formula to determine which cells to format: If none of the above work, the fearless can create their own calculations to come up with custom solutions.
If you want to learn more clever ways to use Conditional Formatting, you can find a wonderful resource on Tech Republic: 10 Cool Ways to Use Excels Conditional Formatting Feature.
To be honest the example above really has too much color. It looks more like a Star Trek terminal than something we can easily analyze. So let’s start to use our sight senses to our advantage with bubble charts because this dataset is perfect for them.
The goal is to find a good KEI mix of popular keywords, but also ones with a $2-$4 price range. There are thousands of keywords which means conditional formatting won’t work so well.
In this bubble chart example we’ll be able to see which keywords fall into the range we’ve selected ($2-$4).
Step 1: Go to the spreadsheet we’ve been using and choose any two columns you want to compare. In this example, we’ll use KEI and CPC.
Step 2: Go to Insert in the Toolbar.
Step 3: Expand the charts area by clicking on the small arrow at the bottom of toolbar.
Step 4: Select Bubbles and then the one on the left (YOU DON’T EVER WANT TO USE THE 3-D OPTION).
The chart will then appear within the spreadsheet (although you may want to cut and paste into a new sheet).
Now you can see the clusters of keyword opportunities. With that said, there is still quite a bit of data here and a lot of white spaces. Since we’re not interested in anything above $5, let’s use our filters to remove them.
Use these steps to filter the results:
Step 5: Go back to the data cells (if you added the chart to a new sheet) and click on the arrow to the right of CPC.
Step 6: Click on Number Filters.
Step 7: Select Custom Filter.
Step 8: Enter the range as is less than 4 and is greater than .1.
Now we can zoom in to visualize how many keywords are in our sweet spot by search volume.
If you’re wanting to create a dashboard style spreadsheet like the one we created in the example, you may notice that the bubble chart disappears along with (possibly) the first rows in the spreadsheet.
You can fix this by:
Step 1: Select / Right Click on the chart and select Format Chart Area.
Step 2: Select Properties.
Step 3: Click on “Don’t move or size with cells.”
That should do the trick.
Now let’s take a look how to use Google docs to visualize the same data.
Getting the Data into Google Spreadsheets
First, we’ll assume you have a Google Docs Account. Start by opening a new file (Ctrl + O).
Step 1: Go to the File menu and select Import (Not New or Open, that will turn it into a Text File, not the CSV we need).
Step 2: Go to Upload and select a file from your computer.
Step 3: Go to your Downloads folder and retrieve the export.
Step 4: Leave “Create a new spreadsheet” unless you desire otherwise.
Step 5: Google does a great job of autodetecting (including dates).
Step 6: Press Import (you’ll see a status window as it uploads).
Step 7: Once the upload is complete click Open Now.
Step 8: Select Column C.
Step 9: Click Format.
Step 10: Click Currency.
File imported successfully!
While using functions and scripts makes the functionality endless with Google Docs, some things are just not worth it. Color gradient Conditional Formatting is one of those. While it is easy enough to perform 2-color ranges, it does not have nearly the power Excel does. Google Spreadsheets has many strengths, but this is not one of them.
My suggestion is to create Conditional Formatting in an Excel Document and simply to paste it into Google Docs. You will be amazed at the transformation (and the time saved by manually setting all 8+ color ranges). You may miss bar graphs and icons but Color Scales stay intact.
Interestingly enough, Google does not have the same options available in Google Analytics Bubble Charts or their Google Charts. There is very limited options for the size and color options.
We’ll do our best by using Scatter Plots to mimick what we did with Excel.
Step 1: Select two columns for your X and Y axis.
Step 2: Select Insert.
Step 3: Click on Chart.
Step 4: Choose Charts.
Step 5: Choose Scatter.
Step 6: Choose Scatter Plot.
Step 7: Click Insert.
Want to Size the Bubbles by Average Search Volume?
There are a couple small tricks to do so.
Google Spreadsheets requires a Color Dimension before it will use Size (the existing data set does not include this). So, let’s create one!
Step 1: Insert New Column.
Step 2: Enter Google in D1.
Step 3: Copy D1 Cell (Ctrl + C), select entire Column (Ctrl + Spacebar) then Paste (Ctrl + V) (re-enter Search Engine).
You are left with Column D being entirely Google.
Now to make the Bubble Chart!
Step 4: Select Columns A:E
Step 5: Select the Bubble Chart (or the bottom example in Charts).
In this post we introduced some of the benefits of being able to export iSpionage data, but hopefully you learned a lot about charting tips and tricks for both Microsoft Excel and Google Spreadsheets.
We looked for other tools that had these basic features and came up short. Bubble charts and comparable conditional coloring and formatting are hard to come by.
- More flexibility with graphs (use virtually any column as x, y, color, or shape, greater scaling of size and or axis, and more styling options)
- Easier use of functions including statistical modeling
- Greater speed to render graphics
- Quickly combine interactivity between reports (one page or many)
- Greater data source flexibility
- Greater flexibility in combining data sources
As a matter of fact, comparing these Enterprise solutions sounds like a great post for the future. Stay tuned!
I hope you learned something helpful from this post. If you have any questions, feel free to ask by leaving a comment.
Thomas 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.