Sign up for weekly recaps of the ever-changing search marketing landscape.
PPC Excel Tips For Every Level: Part 3, Advanced Level Keyword Research And Data Visualization
We close out our three-part series of Excel tips from Bing Ads Evangelist John Gagnon today with some advanced level tools that, in fact, even beginners can start using successfully today. 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 John’s 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, says John. 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.
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
John is especially 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.