Yahoo Search Marketing Bulk Uploads In 10 Easy Steps

Managing a large PPC account can be, and often is, a full-time job. With thousands of ads, keywords, and creatives, simply updating product offerings can take hours each week. Add to that the constantly changing landscape of PPC and you're looking at real headache potential.

Luckily, all the major engines have tools to help ease the pain. One such tool is the bulk upload spreadsheet, which is the fastest way to make mass changes to a PPC account. Instead of having to navigate the user interface on the Web, you can create and edit ad groups in Excel, using all its function tools, and then just upload the finished product to the engine.

Bulk upload spreadsheets have been around since Yahoo Search Marketing was still Overture. In fact, Overture had a bulk upload feature before Google did, and it was a great tool. I used it regularly to add and change PPC listings.

Then, along came Yahoo's Panama, with a new interface and a new spreadsheet. Yahoo conducted several webinars about Panama, including one on the bulk upload sheet (on this page, titled "Importing Campaigns Overview.").

Great idea, right? I thought so, and I was eager to see the new tool in action. Well..... I consider myself a power Excel user - I use Excel more than any other Microsoft Office program, and I'm pretty good at it. Yet, I was lost about 5 minutes into the bulk upload webinar. It was so frustrating, I just gave up and started making changes manually - a very time-consuming process. Others agreed that the bulk upload tool was just too hard to figure out.

Well, I finally got tired of all the typing, and decided to buckle down and learn how to use the bulk upload. I'm glad I did, especially in view of the fact that Yahoo now requires short descriptions on all their ads.

I started with Yahoo's instructions for importing campaigns, but they're somewhat daunting. Here are 10 easy steps to a successful Yahoo bulk upload:

1. If possible, edit only one campaign at a time. Don't download your entire account - you'll have to do a lot of filtering in Excel, and that means lots of error potential.
2. To download a campaign, from within the campaign you want to edit, just click the link that says "download campaign."
3. Open the file and save it to your hard drive as an Excel (xls) file. You'll need to change this format later, but you'll want full use of all of Excel's features while editing.
4. Make your changes - adding short descriptions, turning Content on or off, editing copy, etc. One of the best uses of the bulk upload sheet is for adding keyword-level URLs. This is painful in the interface, but simple via bulk upload using the Copy and Concatenate functions.
5. To delete a keyword or ad group, just delete those rows from the spreadsheet.
6. To add keywords or ad groups, add them to the spreadsheet. You'll need to be careful and follow the Ad GroupAdKeyword convention, though - use an existing ad group as a template. Just make sure to leave the Ad ID, Keyword ID, Checksum, and Error Message columns blank!
7. Once you've made all your changes, you'll need to save the file as Unicode Text. But, make sure to give it a .csv extension! Excel wants to save Unicode Text files as .txt, but that'll cause errors when you try to upload the file.
8. Go back into the YSM interface, to the Campaigns tab, and click Import. Then just follow the instructions from there to upload your file.
9. It only takes a few seconds to upload, and the interface will tell you if there are errors. If you got errors, go back to the Help section and see if you can figure them out!
10. Don't forget to check the Editorial Status section to see if any of your changes are waiting there. Usually they'll go through in a day or so, but it's good to know what's pending there, just in case.

That's it! It's really not that hard once you do it a few times. I've started using this function for almost all my ad edits lately.

Google AdWords' Search Query Report: The Good, The Bad, and The Ugly

Recently, Google added a new report to the AdWords arsenal: the Search Query Report. In a nutshell, this report gives advertisers a list of all the actual search queries on which their ads appeared, along with useful statistics such as impressions, clicks, conversions, average position, and average CPC. This is data advertisers have been requesting for a long time, but until recently had to pull from other sources, such as log files or other analytics tools.

The Good: It's pretty obvious. This report is a great way to discover new keywords, especially those oft-ballyhooed tail terms that are so hard to find, yet convert like crazy. It's also useful for discovering negative keywords. I just added over 170 negatives to one campaign after poring over this report! (Thank goodness for AdWords Editor!) Best of all, if you're using Google's Conversion Tracker or Analytics, the report includes conversion stats, so it's easy to see which keywords are converting and which aren't.

The Bad: The report makes it plainly obvious that there are serious issues with Google's Broad Match. These issues have been widely publicized in the search marketing forums and blogs, so this isn't news. However, I found some really crazy stuff in my report. All our campaigns are set to US-only and English-only, since we're not authorized to sell overseas. Yet our ads are showing on all kinds of non-English keywords, according to the Search Query report. I even found keywords in Russian and Arabic character sets! Not only are these keywords not relevant, we shouldn't be showing on these searches at all.

The Ugly: First, a little refresher. Most SEMs know about Google's Quality Score, which is supposed to reward relevant ads, keywords, and landing pages. Google makes several suggestions as to how keyword Quality Score can be improved, including the following: "You can also narrow your targeting options (ie, using regional targeting) or matching types (ie, use exact keyword matching). We also suggest adding your keyword to your ad text."

Now, a little story to illustrate just how ugly this is in reality. I have generic ads running for keywords like "magazines" and "magazine" and such that go to our home page. I also have more targeted ads for specific magazines such as "newsweek magazine" with specific ad copy, that go to the page for the magazine in question (the Newsweek Magazine page, in this example).

In the Search Query report, I noticed the keyword "self magazine" (phrase match) in my Search Query Report for my generic ads. Avg position = 1.2, avg CPC = $0.51. OK, that's all fine - we have "magazine" as a broad match keyword in that ad group. Here's the ugly part. I also have an ad group in a different campaign for Self Magazine. The ad has that exact phrase in the copy and that keyword in the ad group as exact match, with a landing page for that magazine (as opposed to our home page for the generic ad).

Minimum CPC for [self magazine] in that ad group? $10.00.

I'm sorry, but I just don't get it! Google tells us that to help our Quality Score, we should use targeted keywords, ads, and landing pages. So I've done that. Yet, in this example, I've been rewarded for following the rules with a $10 minimum CPC. Yet, Google shows our generic ad on "magazine" as a broad match keyword for that same query - and charges us 1/20th of the price.

The ugliest part of all is that the visitor has to then search on our site for the magazine they TYPED INTO GOOGLE in the first place. Making searchers search multiple times is one of my big pet peeves. If they are asking for something specific, we as SEMs should make every effort to give it to them, and the engines should help us out, not hamper our efforts!

Just to back up a bit... All in all, I am thrilled with this report and with the fact that Google is providing us with such great granular data. I'm just a little frustrated by the obvious glitches in the system that are now really, glaringly obvious.

SMX Advanced '07 Group Photo

The last event of SMX Advanced '07 was listed in the program as "Goodbye and Group Photo." The group photo was fun, and I ended up near the front! I'm the only one wearing pink.

Can't wait till next year!

John Marshall Leaving ClickTracks

Via Search Engine Watch Blog, I learned today that John Marshall, founder of ClickTracks, is leaving the company.

This is a big surprise to me. I've met John a couple of times at SES, and he is one of the most knowledgeable, open, and honest people I've ever met. He knows analytics backwards and forwards, and has an uncanny knack for taking that vast knowledge and breaking it down so the average marketer can understand it. In fact, that's the beauty of ClickTracks - ease of use, and flexibility. He is one of the reasons we chose ClickTracks as our analytics provider a year ago.

What's next for ClickTracks? How will their market focus change? What's next for John? Time will tell.

John, I wish you well in whatever your new endeavor may be!

Edited to add: Avinash Kaushik, the well-known analytics expert and frequent ClickTracks webinar guest, has an excellent tribute to John on his Occam's Razor blog. Well worth the read.

SMX Advanced: Over Already?

It's been exactly 3 hours since SMX Advanced wrapped up its inaugural conference. What a fantastic 2 days of SEM sessions, networking, and overall fun. This was a relatively small (in comparison to SES) group of advanced search engine marketers, all gathered to learn insider tips and tricks for organic and paid search.

I can't begin to summarize everything I've learned over the past 2 days - I'll need a couple more days to digest everything. Probably my favorite session of all was "Paid Search: The Giant Focus Group." All 4 major engines were represented on the panel (Google, Yahoo, MSN, and Ask), and they basically took feedback from the audience the entire time. We got to get a lot of things off our chests, and the engine reps got to hear, from the horses' mouths, what we need as PPC marketers. It was a great give and take, and I'm hoping to see some good things come out of it over the next weeks and months.

There were so many good things about the conference, but a few stood above the rest:
* The smaller group, and the fact that everyone is advanced, meant there were some really meaty sessions.
* Networking opportunities, as expected, were excellent. I met with my MSN rep for an hour, and met Carolyn and Jeanie from the adCenter 411 team. I had a good conversation with Yahoo as well. I met tons of great people in the sessions and at lunch. There were evening events as well, and even though I didn't stay long (darn jet lag!), I did get to meet some of the key players in the SEM space.
* The food. Oh my. I was expecting the typical SES chunk of bagel as breakfast and box sandwich (or lame pretzel) for lunch. This was more like a cruise. Breakfast was a real breakfast - cereal, hard-boiled eggs, yogurt, pastries, bagels, juices, coffee.... Lunch was a gourmet feast. They had prime rib, carved turkey breast, pastas, salads, fancy desserts... And there were snacks at every break, too. Cookies, fruit, snack bars.... Honestly, you could have made do on the free food at the conference and nothing else. I did enjoy a nice dinner tonight, but that was just because I wanted to try a bit of Seattle.

My only real complaint is that it was over too soon. It just flew by. I could have easily spent another day "speaking search" with everyone here.

There were photographers galore at the show, and the usual live bloggers: Barry Schwartz and Tamar from SE Roundtable, all the SEOMoz crew, Lisa Barone from Bruce Clay, and lots more. I'll add links to conference coverage as I come across it (I'm a little behind on my news feeds at the moment!).

Danny and the Search Engine Land crew, thanks for a great experience. I look forward to next year's SMX Advanced.

First Day of SMX

Well, I actually haven't gotten my registration materials yet, but I am here in Seattle, in the Edgewater Hotel. What a place. Of course it should be, for $279/night... Anyway, in about an hour, I'll be registered and ready to check out all the sessions.

There actually was an event last night, but I missed it. Thus the main point of this post...

Getting here was an adventure, as airline travel usually is these days. My flights were on schedule, which was great (and surprising). Getting to the hotel was a different story. Since this place is so pricey, I'm trying to save money on other things. So, instead of taking a $40 cab ride, I booked a $22 round-trip ticket on the Gray Line "Airporter" shuttle. Ugh. I guess you get what you pay for. The shuttle was 20 minutes late, and then the driver yakked on the intercom the whole way to downtown Seattle. He told us at least 3 times that he hadn't gotten a lunch break since he started the day at 1 p.m. (it was now after 7 p.m.). Then he told a bunch of jokes. Whatever. Well, to get to my hotel, I had to take their "connector," which means I got off the main bus from the airport and into a shuttle van. No problem. But the yakky bus driver took my ticket and kept it, and gave me one little stub in return. I didn't know how the system worked, so I didn't worry about it; but then when I got in the shuttle, that driver told me I needed my original voucher. So he chased down the bus and argued with the guy, who finally gave me all the tickets I need for my return trip. The rest of the way to the hotel, the connector driver complained about the other guy. Very unprofessional. I don't care about the breakdowns in their silly system - I just want to get to and from my hotel in a timely manner.

Based on this experience, I realize I need to leave very early for the airport on Wednesday when it's time to go home. Not what I had planned, but whatever. And by the time all that was said and done, there was only an hour left of the MSN party. I didn't feel like getting cleaned up to go there for an hour when I was already exhausted from a long day of travel. Today should go better!

I'll post more as the show progresses.