Combining Yahoo Interface Reports with the Desktop Tool for Bid Adjustments
June 2, 2010
The keyword report from the interface gives you the average CPC, not your max CPC, and the desktop tool doesn’t show you the Key Performance Indicators (KPIs) that you will want to see to base your decisions on, like average position and click-through rate.
I’ve always been a big fan of using excel to help make tasks as “automatic” as possible, so I wanted to share the following process for combining the interface report with the desktop tool to make your bid changes. There is a lot of information here, but I promise that if you go through it a few times it will shave time off of your bid adjustments, especially if you are still manually typing bids into the interface one by one.
The point of downloading both the report and the desktop tool is so we can do two things:
- Pull the actual CPC into the report that shows us our KPIs.
- Create a file with the updated bids that you can easily import into the desktop tool.
Formulas To Use:
First you will need to know a little about the following formulas in excel. I’ll explain how to use them later on:
Vlookup – This formula is used when you want to look up, and pull over information from another sheet. When creating a vlookup formula I use the following saying to help keep everything in order: I want to look up “X” in “this range”, and what I want to pull is in column number “Y”, and if it can’t find what I’m looking for, give me “FALSE”. Now, if you’ve never done this formula, I’m sure that saying doesn’t really make much sense, but it will be easier to understand when you put it into practice below.
Concatenate – This formula allows you to combine text and/or cells together. You will need to put a comma between each piece that you are concatenating, and remember to put quotes around spaces and text that you are adding. If you’d like some more information, you can review another post of mine about adding tracking parameters to URLs.
Setting Up Your Bid Change Workbook
The first thing you will need to do your bid changes is a keyword report from the Yahoo! interface, and a download of the keywords from the Yahoo! desktop tool.
To get the keyword report from the interface, follow these steps:
- Log into your Yahoo! account, and go to the Reports tab.
- Choose the Keyword Performance report from the left hand navigation.
- Set your time frame for the report. We typically run our reports for 30-day time periods, but you can do more or less depending on the amount of volume your account gets.
- Download the report to a .csv, and open the file. Save the workbook as an excel document, and give it an appropriate name.
- The report heading information is not necessary, and all of those rows should be deleted so cell A1 contains the header “Keyword”.
- The row immediately below the report headers can also be deleted, because we don’t need to know any of the totals.
Looking at the interface report you will see that there is a column for cpc (in yellow above), but as I mentioned before, this is the AVERAGE cpc over the time period you are looking at, and NOT the actual cpc bid that you have set. Because this data isn’t what we want, you can delete all of the data in that column.
To get the keyword download from the desktop tool, follow these steps:
- Open up the desktop tool, and make sure you have downloaded the most recent data of the account you want to look at. You do this by clicking on the “Get Account” button in the upper right hand corner, and entering your login information.
- When the account download is finished, make sure you have the entire account highlighted in the left hand navigation, and click on the keyword tab so you are looking at every keyword in the account.
- Export the entire account to excel.
- Go to “More Actions” in the upper right hand column, and choose “Export to Bulksheet (CSV).
- Click the account box so all of your campaigns are automatically selected, and hit “Export”
- Save the file, and open it.
- Copy the entire sheet, and paste it into a new tab in the report download file that you saved earlier.
- You won’t need the desktop export file anymore, so go ahead and close it.
Now that you’ve downloaded everything you will have an excel file with 2 sheets, one with the interface data, and one with the desktop tool data.
If you think about it, the probability of having duplicate keywords in your account is pretty high. For example, if you have restructured your account and moved “Keyword A” from “Ad Group 1” to Ad Group 2”, even though you’ve paused it in Ad Group 1 it will show up in the account download. When you download your account it would then look something like this:
If you were to do a vlookup in this example, the cpc bid for Keyword A would pull from the first time it shows up, which would in Ad Group 1 where it is paused. But we would want to know where it is Active in Ad Group 2. But never fear – it is possible to get around this by concatenating the Campaign, Ad Group, and Keyword, to create a unique “name” that we can vlookup against. That may sound more difficult than it is, so let’s walk through the few quick steps to set this up.
Then, starting in cell A2, you will need to create a concatenation formula that will combine your campaign, ad group, and keyword into one long phrase. You can copy and paste the following into your sheet:
Set up the second tab of your workbook the same way, but use this formula instead:
Tip: Double check that your concatenation is pulling from the Campaign, Ad Group, then Keyword columns on both sheets, as you may have different report layouts set up. At this point I would also save the file if you haven’t done so already.
Combining the reports
A simple vlookup is all we need now to pull the actual CPC bid from the keyword download into the interface report. To do this, just copy and paste the following formula into the report in the CPC column:
=VLOOKUP(A2,’Desktop Tool Download’!A:J,10,FALSE)
Be sure to place this formula in the cell H2 when starting, and verify that it is pulling from the correct columns on your sheets. When you have made sure the formula is correct, pull it down to the rest of your rows so your sheet looks like this:
Tip: You’ll notice in the image above that I’ve also added a column header to the far right called “New Bid”. By doing this you can easily type in your new bids, or set up a calculation to increase or decrease your bids. This is also a good way to keep a record of the changes you make so you can revert back to the original bids should your changes cause a negative effect on the campaign.
I’m sure by now you’ve noticed those pesky bids called “Default”. When you see that it means that the keyword is using the ad group default bid, and you can pull that in by doing a vlookup against just the ad group name:
=VLOOKUP(C3,’Desktop Tool Download’!C:J,8,FALSE)
Tip: You will need to change the cell numbers to match your sheet. This is just an example to get you started.
Pulling It All Together
Once you have set your new bids, the final step is to put it back into the format of the desktop tool.
- If you’ve been using formulas to get your new bids, copy and paste the values.
- Go to the second sheet that contains your desktop tool download.
- Highlight the header row, and turn on filters.
- Set a custom filter to show you everything that does NOT contain “keyword” in the component type column. Delete all of the rows that are showing.
- Take the filter off. You should now only see keywords.
- Assuming you have not deleted any columns in either sheet, in the “Sponsored Search Bid (USD)” column enter the following vlookup formula to pull your new bid in:
- Copy and paste the values of the bids, and format them to be currency.
- Delete Column A that contains your concatenated names.
- Copy the entire sheet, and save it in a new file as a Unicode Text File. Once the file is saved, change the extension to .csv.
=VLOOKUP(A2,’Interface KW Report’!A:Q,17,FALSE)
10. Upload the file into the desktop tool using “More Actions”.
If you’ve hung in there until the end, well done! Though this process has enough set up steps that is seems daunting when you are getting started, it really will help shave time off of your bulk bid adjustments in the end.
If you have any other suggestions on how to easily make bulk adjustments, please share them with us!
Keynotes from Bing & Google + the Hero Conf Advanced Track
Where do you go to get the most relevant information in PPC, plus the opportunity to network with the best and brightest the industry has to offer? Why Hero Conf, of course.
3 Questions You Need To Answer Before Beginning Paid Advertising
So you’ve you decided you want to start PPC advertising, but now you are asking, “Where do I start?” We've listed and answered 3 questions you should be asking.
Road Trip - 5 Tips For Creating International Campaigns
International markets can add complexity to any account, but here are a few settings and opportunities to make your global ad strategy stand out in non-US markets.
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
Discover 3 Levels Of Geo Targeting For Beginners, Intermediates, & Experts
Setting geographic targets is day one. Whether it’s to exclude places you cannot sell or serve or to specifically target your primary customer base, the implementation of geo-targets is on the must-do list.
[New Webinar!] What You Need To Know About New AdWords Features
In this new live webinar, Hanapin Associate Director of Services Jeff Baum and Optmyzr’s CEO Frederick Vallaeys will walk you through the new AdWords tools and show how you can use them to optimize your accounts.
The Social Strategies You Need To Know For App Promotion
Create social strategies to promote app awareness and leverage geographic and demographic data reported by these platforms to refine audiences.
Guide the Industry and Take the 2016 State of PPC Survey!
Each year, Hanapin Marketing manages extensive research into the State of PPC Report, which provides a statistical breakdown of the digital ad industry and guides expectations.
What's In A Name? Building Powerful Product Titles For Ecommerce
Over the years, we've tested a variety of different product title alterations in hopes of finding the perfect title. Did we? Yes, and no. The fact of the matter is, it depends.
What Upgraded Bing URLs Mean For You
Bing is continuing its effort to catch up with Adwords. Upgraded URLs, which isolate tracking parameters from final URLs, are now available to Bing advertisers.
Where Does Your Hero Conf Dollar (or Pound) Go?
5 Common Excel Issues And How To Fix
Excel, while a fabulous tool for PPC, is not without its occasional hang-ups. In this post, we’ll go through some common problems, and how to fix them.
[New Whitepaper!] Guide to Google AdWords: Advanced Edition
In this whitepaper, you will gain a greater understanding of the distinction between e-commerce and lead generation PPC strategies, learn the effective use of Remarketing, how to utilize advanced segmentation for improved targeting, cross-device attribution, and how to use automated rules in your accounts.
Google Announces Controversial Updates To The Keyword Planner Tool
Google updated the AdWords Community on the state of the Keyword Planner tool and the response from several advertisers was quite dramatic.