E-Commerce Products Into A Google OneBox

OneBox To Rule Them All

Shopping feeds — especially Google Products — are a no brainer for you and your e-commerce clients. If you work for someone who sells on the Internet, then there’s virtually no better free way to get your items and pages high up on the search results page than putting them into a data feed. Of course, free rarely equals easy in this industry, and shopping feeds are no different. Here’s an outline of my recent efforts to get a client into the (One)Box.

In the example above, a search for “gray suit” returns more than 5 million pages. According to the Google Keyword Tool, there are over 27,000 global monthly searches for the term, and it has a 47% competition. It’s a popular keyword that would take some serious linking and SEO efforts to rank for organically. Paid advertising, will cost at least a dollar per click, which isn’t inordinately expensive, but it’s not exactly cheap, either.

You’ll notice from the screen capture that the Google Shopping results appear in a more prominent location than the Google Ads, and they’re also more prominent than all but the first organic result. This part of the search results page is actually called a OneBox, and you’ll notice these types of search results for queries of other kinds, such as weather, images, news, and so on. (Of course, a lot of the thinking behind getting your products into the Products OneBox apply to getting your businesses in the Places OneBox, trips in the Travel OneBox, and so on.)

The Basics

The basics of ranking within Google Products are similar to those for ranking in organic search. Google is (ostensibly) trying to serve up the best search results, so pay attention to your titles and descriptions, and try to avoid being spammy. For Google Products, price is also important, and it’s one area where your good clients can really shine. If they’re selling the same products at cheaper prices, then they can rank higher than products sold at more ‘powerful sites.

It seems like shopping feeds are pretty simple, then, right? They are, if you have a pathological attention to detail bordering on OCD, and if you can wrangle data — sometimes up to tens of thousands of lines of it — into a usable (and highly specific) format. Read on to see where I’ve encountered some problems, and how I overcame them.

The Details

Our particular client is a fairly large e-commerce retailer that uses Volusion for its back-end CMS and inventory management. Volusion actually provides pretty straightforward instructions for exporting products directly to Google Merchant Center (the Google interface for Google Products, which show up in the Google OneBox; what can I say, the company likes Upper Case Branding).

In an ideal world, you’d set up the Volusion back end, enter your Google log-in credentials, and press a button once every 30 days to get your products humming along atop the organic search results. If you find an inside track to this “ideal world” place, let me know because that’s not where my client and I are at.

If you take a close look at Google’s feed specifications, you’ll see what I mean.

There are approximately ten to twenty feed specifications you need (in the United States, at least) to have a good feed. That’s the bare bones basics.

Exporting your data straight from Volusion can work — if the product data is good to start with. One problem we ran into with our client was that their data was all over the place. Some of it was good, some had misspellings. Other data had some basic SEO errors.

The long and short of it is that I ended up hacking together a custom query in the “Inventory > Import/Export > Saved Exports”. The custom export I created included the products’s

• Internal Product Code
• Name
• Manufacturer
• Part Number
• Description
• Price
• Sale Price
• URL
• Image URL
• Shipping Cost
• Shipping Weight
• Availability
• Product Type
• Product Category
• Condition
• Number in Stock

Once you click “Export”, you’re given a many thousand line CSV file to work with in Excel. And now the process really begins.

The Feed

A quick note: Working with a massive spreadsheet is process- and memory-intensive, apparently. On my late-2010 MacBook Air, I see the dreaded beach ball more often than I’d like. This is a computer than runs Photoshop and all its fun filters with ease. Crunching numbers is tough, but it must be done.

OK, getting started let me say that “Find and Replace” is going to be your best friend. Does your data contained a commonly misspelled word? “Find and Replace”. Are your products frequently using the wrong SEO keyword? “Find and Replace”. Are you using the Google URL Builder to tag your URLs for tracking purposes? “Find and Replace”. Do you need to fit your products into the right Google Products categories? You better fire up “Find and Replace”.

Manipulating large amounts of data by hand is very tedious, so you’ll want to do it as ‘mechanically’ as possible at every step. I proceeded by sorting our products by Title, which roughly grouped them into categories. Our client’s product taxonomy was, in a word, woeful. Even “Find and Replace” didn’t help as much as I would have liked, but I managed to categorize everything according to Google’s preferences.

I then made sure that all the product Titles were strong, with the correct keywords in place for ranking and usefulness. I tagged all the URLs so we could track our success in Google Analytics. For data like ISBNs and UPCs, you need to make sure the format is set to something like “Numeral System” so that the number doesn’t show up in scientific notation.

The final big sticking point for wrangling the product feed into shape was the Descriptions field. Besides the Price, this may be the most important part of your product feed, since it will contain keywords for ranking purposes, and the copy that will (or won’t) compel visitors to click through to your site. Google limits your Descriptions to 10,000 characters. (For point of reference, this post is about 6,400 characters long, right now.) That’s a lot of characters, but you may run into your product descriptions being too long. The larger problem with the Descriptions field crops up when you go to export your data into a .TXT (plain text) file.

Brief Interpolation about Text Files

If you upload your Google Feed via the Google Merchant Center or FTP, you’ll need it to be in a text format — not .CSV, .XLS, or anything else. Perhaps the easiest way to do this is to — once you’ve ‘massaged’ or cleaned up your data — go to “Edit > Select All”, and then simple Copy and Paste it into a text editor like Notepad.exe or TextEdit.app. That’s it.

Back to the Description Field

The problem I ran into is the data I was working with doubled as the data on the site. Titles, Prices, URLs, and so on translate reasonably fine. But the Descriptions were marked up with HTML or had a lot of carriage returns. All this extra stuff in the Descriptions would cause, for example, a 3,000 line spreadsheet of products to balloon into a 30,000 line text file. Uploading such a file makes Google think you have 30,000 products, only some of which have a complete stable of information.

I was lamenting my problem when a colleague offered up some superlative Excel advice, which I’ll share with you here.

Previously, I had been removing carriage returns by hand (as best as I could) in a text editor. This was insane. My colleague suggested I use two easy Excel functions to remove all my carriage returns before I even got them into the text editor.

The Clean Excel function removes all the non-printable, weird characters from a cell. The Trim Excel function gets rid of leading and trailing spaces. The way I look at these two functions is that they sort of wash the data inside.

In the screen capture above, I pasted in some text from a website. You’ll notice that it all technically occupies one cell, but if you were to copy and paste the single row the cell is in, it takes up thirteen lines.

If I enter into another cell:

=TRIM(CLEAN(B2))

that will remove the carriage returns and any extraneous characters. Copying and pasting that row into a text editor now shows it to take up one line. A helpful hint: You can copy and paste the above formula down an entire column. Then, you can copy the column and select “Paste Values”, and overwrite the original column with ‘clean’ data.

You can apply the above formula to any cells that offer you problems, and then your text file will have data clean and pure as the driven snow.

Conclusion

Hopefully you found the above helpful in your shopping feed endeavors. The same general ideas apply to Bing, PriceGrabber, and any other shopping engines. You may also be wondering why I didn’t just use SingleFeed to manage my shopping feeds, since that service collects and submits all your data automatically. The short answer is that I’ve tried it, and found that — like working with the straight Volusion to XML export — it works perfectly in an ideal world. But I found myself having to fix the data all too often, and just dealing with the data myself ended up being quicker.

I just wanted to share a bit of our process with the shopping feeds, mostly for other e-commerce sites out there who might feel a bit daunted about submitting their products to shopping engines. Once you get the process down, it takes little time, and it can reap huge rewards.

Insanely Useful Links Mentioned In This Post

• Google Feed Specifications
• Google URL Builder
• Google Products categories