Combining Yahoo Interface Reports with the Desktop Tool for Bid Adjustments

The Issue:

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.

A Solution:

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:

1. Pull the actual CPC into the report that shows us our KPIs.
2. 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:

1. Log into your Yahoo! account, and go to the Reports tab.
2. Choose the Keyword Performance report from the left hand navigation.
3. 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.
4. Download the report to a .csv, and open the file. Save the workbook as an excel document, and give it an appropriate name.
5. The report heading information is not necessary, and all of those rows should be deleted so cell A1 contains the header “Keyword”.
6. The row immediately below the report headers can also be deleted, because we don’t need to know any of the totals.

Once you have cleaned everything up your sheet should look like this:

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:

1. 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.
2. 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.
3. Export the entire account to excel.
   1. Go to “More Actions” in the upper right hand column, and choose “Export to Bulksheet (CSV).
   2. Click the account box so all of your campaigns are automatically selected, and hit “Export”
   3. Save the file, and open it.
   4. Copy the entire sheet, and paste it into a new tab in the report download file that you saved earlier.
   5. 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.

First, you will need to insert a column on the far left of the sheet, like so:

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:

=CONCATENATE(D2,C2,B2)

Drop it down for all of the other rows in your sheet. Just be sure to copy this formula into cell A2 when you start. Once you do that, your sheet will look like this:

Set up the second tab of your workbook the same way, but use this formula instead:

=CONCATENATE(B2,C2,G2)

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.

1. If you’ve been using formulas to get your new bids, copy and paste the values.
2. Go to the second sheet that contains your desktop tool download.
3. Highlight the header row, and turn on filters.
4. 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.
5. Take the filter off. You should now only see keywords.
6. 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:

=VLOOKUP(A2,’Interface KW Report’!A:Q,17,FALSE)

7. Copy and paste the values of the bids, and format them to be currency.
8. Delete Column A that contains your concatenated names.
9. 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.

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!