PPC Excel Tips For Every Level: Part 3, Advanced Level Keyword Research And Data Visualization

While many of the tips we’ve covered can also help SEOs as well as PPC practitioners, today’s focus on keyword research is particularly relevant to both camps.

Advanced Tip: Bing Ads Intelligence for deep-dive keyword research and Power BI for big data analysis and visualization.

Bing Ads Intelligence is an Excel add-in for keyword research that enables Excel to integrate directly with your Bing Ads accounts. However, you can use this tool for keyword research outside of Bing Ads campaigns as well.

Once you download the add-in, you’ll see the Bing Ads Intelligence tab in the top navigation. If you want to download your campaign data, sign in to your Bing Ads account by clicking “Sign In” icon to the far right. You can then see your keywords along with performance metrics.


These are top 3 features in Bing Ads Intelligence:

1. Keyword Suggestions: Keyword recommendations fine-tuned from Yahoo Bing Network data.
What’s also interesting about the Keyword Suggestion tool is that it will give include suggestions for match types not currently in our account along with the performance data and estimated bids for mainline and sidebar positions. Not only that, but you can run the Keyword Suggestion tool for any list of keywords, not just those already in your account, and it will show the broad, phrase and exact match statistics for each variation. Don’t want to look at phrase match types? Just use the filter to hide them from the list.

 2. Traffic: See the number of searches users conducted on the Yahoo Bing Network for a specific query.
The Traffic tool can be used to look at multiple keywords or one. The cool thing about this feature is you can see historical trending and you can break out performance by device. In the example below, I’ve chosen to see the traffic performance of the keyword “classic cars sale” on Desktops and Laptops and then on Smart phones by week. In this example, Desktop and laptop traffic trended down closer to Thanksgiving, while Smart phone traffic actually ticked up slightly in that final week of November. You could break this down by day if you wanted as well.



3. Keyword Performance: For any keyword, it will show you a snapshot of keyword performance by position. This can be powerful when trying to estimate traffic and set bidding strategies. Again, these don’t need to be words you are currently bidding on in your account, and you can separate results based on device type and match type. To see the performance stats for a keyword by every position, select “All” at the bottom of the Ad Position drop down menu as shown below.



Quick note: You’ll notice the shading in several columns in the tables above. This is done using Conditional Formatting, found under the Home tab. Conditional Formatting allows you to shade and color cells based on their values to be able to better visualize opportunities and problems.

In the Keyword List Generator template shown below, conditional formatting rules are set using the Conditional Formatting Rules Manager to show a different color in each metric column. In this table you can quickly see that, while the search volume for “buy new laptop” is relatively low, the CPC and Average Bid are high. With this template, you can add any sequence of keywords you want. Hitting the “Refresh All” button under the Bing Ads Intelligence tab will update the data keyword variations.



You can find this template and others for download by clicking the “Keyword Research Templates” button under the Bing Ads Intelligence Tab. There is much more to explore with this tool, so if you haven’t done so, download it and start experimenting.

Power BI
For those with the right software, Microsoft’s new Power BI tools for Excel offers advanced-level tools for data analysis. Here is the feature set for Excel in Power BI

• Power Query – Connect to data from public and corporate data sources including databases, web pages, even Facebook graph data.
• Power Pivot – Data Modelling tool that can handle nearly 2 billion rows of data
• Power View – Create reports and analytical views with interactive data visualizations
• Power Map –Visualize your campaign (or other) data geographically in 3-D within Excel

I am excited about Power Map for search advertisers. With it you can take the “geographic location” data from the dimensions report in Bing Ads and put it into Power Map to see where clicks are coming from by geographic location, for example.



There is a barrier to entry here, however. You will need Office Professional Plus 2013 or Office 365 ProPlus to run Power Map. If you meet those requirements, the Power Map and Power Query can be downloaded here. If you’re working with huge amounts of campaign data, Power BI tools could give you the bandwidth you need. Power Map also has broader applications beyond your own campaign data. You can import third-party or your CRM data to map out and see where your current customer and prospect bases are located for clearer insights into content development and campaign targeting. Think about the types of data sources available to you and how they might be able to inform your marketing efforts in new ways using Power BI tools.
That closes out our PPC Excel Tips series, just in time to usher in 2014. If you have tips and tricks of your own to share, please do so in the comments below.

PPC Excel Tips For Every Level: Part 2, Huge Time Savers For Beginners (And Beyond)

Excel is that must-have tool for managing, analyzing and reporting on paid search campaigns that is impossible to know too well. Many of us are self-taught — picking up tips and tricks here and there — which makes it even harder to know what shortcuts and secret powers we’re missing out on.



Beginner Tip: Don’t under estimate the time-savings to be gained from Keyboard Shortcuts.
I hesitate to even qualify these as beginner tips because it’s easy to have worked with Excel for years and not know every shortcut.  Rule of thumb: don’t waste time using your mouse if you don’t have to.  Make handy keyboard short cuts a natural part of your work process, and you’ll be amazed how much extra time you’ll have on your hands for actual analysis.

1. Automatically SUM() with ALT + =
John says he’s used the =Sum() formula for years, but recently discovered this function.  Automatically SUM() an entire column or row by clicking in the first empty cell in the column. Then press the ALT button and the = (equals) key to sum up the numbers in the above cells.

 

2. Copy Formulas with a Double-Click

While not technically a keyboard shortcut, this trick is a huge relief for anyone working with repeating formulas, such as a conversion rate or click-through rate calculations. After entering your formula hover the bottom right corner of the cell with your mouse until a little plus sign appears. Then just double click. The formula will copy down into the cells of that column to calculate the data in the rest of the rows in the spreadsheet.



3. Currency And Percentage Formatting Shortcuts
Keyboard shortcuts are only handy if you can remember them. John points out that there is actually logic behind when to use CTRL and SHIFT in keyboard shortcuts that will help you remember when to use them.
To format a number as a currency without having to use your mouse, the keyboard shortcut is CRTL + SHIFT + 4. Why? Well, Shift + 4 is how you type a dollar sign ($). Adding CTRL will then format the cell (or cells) as currency. The same works for formatting numbers as percent with CTRL + SHIFT + 5.

PPC Excel Tips For Every Level: Part 1, Faster Campaign Analysis For Intermediates

This installment of PPC Excel tips focuses on intermediate level techniques for speeding up campaign analysis. Paid search managers will benefit, but really anyone using Excel for data analysis will find good information or a helpful refresher here.

Intermediate Tip: Get more out of Pivot Tables with calculated fields and by fixing #DIV/0 errors.
Pivot tables are truly a must-have Excel skill for marketers. If you haven’t used pivot tables yet, don’t be timid. You can’t damage your data set when working with pivot tables because they simply aggregate the data you are working with in a separate area, typically in a separate sheet.

Calculated Fields: For those with pivot table experience, you know that once data such as impressions, clicks, spend are aggregated, you still need to calculate metrics like CPA and CTR as you do with regular campaign exports. In other words, you can’t Sum cost/conversion or click-through data, you have to calculate them from the aggregated cost, click and impression data. This is what Calculated Fields do for you with just a little set up work. I have to admit, for years I calculated these metrics in cells outside of pivot tables not knowing about the magic of calculated fields.

A great thing about calculated fields is the data columns you want to use in your formula do not have to be included in your current pivot table. For example, in the demo below, i be showing how to calculate CPA to find the total cost per conversion, but only spend is included in his pivot table, not cost/conversion.

One note, your calculated fields can’t use duplicate names from fields already in your data set. Thus the “CPA” field name in this example.

The calculated field will append a new column to the right in your pivot table. You can then sort and filter based on this new column.

Fixing #DIV/0 Errors: Whether you’re using calculated fields or not, if you divide a number by zero in Excel it returns the dreaded #DIV/0! error. This can obviously skew your analysis if you’re looking at CPA data.

“If you ignore the error, you’re missing out on valuable information — a keyword can spend thousands and still not convert a single time,”

The solution: Use the IFERROR() formula in the calculated field to return “spend” or “cost” when there are 0 conversions. Open the Calculated Fields dialog again, go to the CPA field in the Name dropdown, and change the formula to: =IFERROR(Spend/Conversions,Spend)



I am using the 2013 version of Excel. If you’re using Excel 2010, you’ll find calculated fields under the
Options tab in PivotTable Tools.



IFERROR can be used outside of calculated fields as well. It’s a good function for marketers to know, particularly for cost analysis. The logic of the formula basically says, if the calculation returns an error (#DIV/0! in this case) then return X, and X can be your total spend, or it can be a specific number or even text. Essentially, you can customize what the formula returns by putting what you want after that comma in the formula.