SEO

How I Wrote 500,000 Unique GoogleBase Descriptions in 2 Hours

The Problem

Anyone who has worked on an eCommerce site with a new or non-optimized GoogleBase feed is probably familiar with this scenario: either there are no descriptions at all or the descriptions are just a mashup of product attributes with no discernible flow, logic, or consistency.  I recently ran into this with a Google Base feed exceeding 500,000 products and turned to Excel for the solution.  For example, we might see the following description in the GoogleBase feed for a website that sells NFL apparel:

Home > Oakland Raiders > Raiders Jerseys > Men’s > Large > Darren McFadden. Keywords: football, running back, silver, black, home, replica. Designed by Reebok. 100% polyester. Machine wash cold.

Now if I’m Google, I’ve already got some issues with this description based on my editorial guidelines:

  • Describe your message clearly and accurately – Strike one.
  • Use standard spelling and grammar – Strike two.
  • Avoid any repeated and unnecessary use of punctuation, capitalization or symbols - Strike three (> will be replaced with the > sign).

Best case scenario: these descriptions provide no added value to this product feed.  Worst case scenario: these descriptions detract from the feed as a whole, thus inhibiting other products that may  have quality descriptions from ranking in Google Product or Universal Search.

The Solution

Time for Excel to swoop in and solve our problems!  However, before we begin please note that in an ideal world, we would always create wholly unique descriptions for all our products.  The solution provided below is not necessarily meant to replace unique descriptions, but to allow you to scale your descriptions across multiple products quickly and easily.

Essentially what we want to do next is to create some generic content strings that, when our specific details are included, transform into unique sentences that can be strung together to create a comprehensive description.

The Foundation

To continue with our previous example, we’re going to go ahead and launch Brett’s NFL Shop, the premier source for all football gear and apparel now that the lockout has officially ended.  We’ve got our database of products, but how in the world are we going to write unique product descriptions for our 500,000 products?!?  Well, to begin let’s breakdown what our descriptions should look like.  If we go with Google’s recommendations that the foundation of a good description is 500 to 1000 characters, we might look to create our product descriptions based on the following format:

  • 1 Introductory Sentence (Should introduce the product and/or brand)
  • 1 Body Sentence (May speak generically to the use and/or application of the product)
  • 1 Category Sentence (Should talk about the category that the product falls under)
  • 1 Theme Sentence (Should highlight key themes about the product)
  • 1 Body Sentence (May speak generically to the use and/or application of the product)
  • 2-3 Detail Sentences (May vary based on product or industry, but some examples might include manufacturer, product material, available colors/sizes, or brand)
  • 1 Conclusion Sentence (Should reinforce, while staying within the guidelines for gimmicky repetition, why you should buy this product).

That being said, there are some details we will need, which should already be available in our database:

  • Product – We need to know what product we’re trying to sell in order to cater a description to promote that product.
  • Category – Let’s say that for Category we will refer to the NFL team associated with that product.
  • Details – For the sake of argument let us assume that our Details are manufacturer, product material, and color.
  • Theme – Let’s say we want 3 keywords that describe the product we’re writing the description for.

Next, and here’s where it gets fun, we want to create some generic sentences that include variables for our details mentioned above

The reason we want to create multiple generic sentences is that we will eventually randomly select these sentences, substitute our variable details, and be left with a coherent and comprehensive description of the product.

Let’s say we write 5 introductory sentences, 10 body sentences, 5 category sentences, and 5 conclusion sentences.  For Brett’s NFL Shop, it may look like this (only 5 of 10 Body Sentences shown):

Let’s Concatenate!

Now that we have our example sentences written, we’re almost there!  Now, what we want to do is write an Excel formula that will do the following:

  • Randomly pull 1 of 5 Introductory Sentences
  • Randomly pull 1 of 5 Body Sentences

  • Randomly pull 1 of 5 Category Sentences
  • Insert sentence that highlights our top 3 thematic keywords 
  • Randomly pull 1 of 5 Body Sentences

  • Insert sentence that talks about the manufacturer

  • Insert sentence that talks about the product material
  • Insert sentence that talks about product size

  • Randomly pull 1 of 5 Conclusion Sentences

I have attached my worksheet so you can see it in its entirety here, but assuming our detail attributes all exist within the same spreadsheet and the sentences exist in a tab titled Sentences, the Excel formula should read as follows:

Which will generate one of our descriptions as so:

Almost There!

The last thing we need to do is to replace our “[Product]” and “[Category]” attributes with the appropriate information.  We needed to do this after our descriptions were generated because the variables appear at different points depending on which sentences were randomly selected.  However, it is as simple as a quick Substitute function

and

And voila, you have a readable, unique description that can be scaled across however many products you have, provided the Product, Category, KW Themes, Manufacturer, Material, and Size are available!

Note: If anyone knows how to combine these steps, please let me know!  I was not able to find a way to substitute both variables from the same cell, which is why it required multiple steps.

From here, simply apply this formula to your entire product list, and you will have unique descriptions for all!

Updated: 9.1.11

A huge shout out and thank you to Tamsin whose comment below provided a solution to my “multiple cell” problem I was having! As you can see from the comment, we can nest the substitutes such that the formula reads:

In fact, we can even nest the substitutes into one cell, eliminating any additional steps.  The final formula would therefore look like this:

Once again, thank you Tamsin!!

Other Applications

Lastly, I’d like to leave you with some alternative applications of the above:

  • Meta Descriptions – If you ever find yourself needing to write unique meta descriptions (perhaps to address the pesky Webmaster Tools HTML Suggestions report), this could be used to do so
  • eCommerce Product Descriptions – If these descriptions could apply to Google Shopping, why not the product descriptions themselves?
  • Scalable Link Outreach – While I agree with the common opinion that the best link outreach cannot be scaled, this tool could potentially help you to create semi-unique introduction paragraphs or even entire outreach emails that can then be reviewed manually to improve readability and personalization.  I know that I often find it is easier to edit an outreach template to add personalization than to try and write completely personalized messages from scratch.

Let Us Know What you Think!

When people ask me what I use Excel for, I always give them the same answer: problem-solving.  Excel can do so many amazing things as long as you know a) what the problem is you are trying to solve and b) what your ideal solution is.  There are tons of great Excel problem-solvers out there, like Mike Pantoliano, Richard Baxter, and Annie Cushing to name a few, and Excel has always been one of the most robust tools in the SEO arsenal.  But one of the things you should never overlook with Excel is that you are not necessarily confined to numerical data. :-)

Anyone familiar with concatenation schemas, which Stoney deGeyter wrote about a few months ago, will recognize the strategy as the subconscious motivation for my solution (I say subconscious because I internalized Stoney’s strategy so deeply that I almost didn’t realize he was the original inspiration!  If not for occasionally going back and reading old posts I’ve tagged in Delicious, I would have neglected to give credit where credit is due).

However, as Google gets better at identifying content footprints, it is my humble opinion that we should go the extra step to diversify our content even more.  The only criticism I have with the traditional concatenation schema is that it still looks like a generic description with only minor variability of preset attributes, something that I have no doubt Google can algorithmically decipher.  This is intended to be an extension of that schema, thus providing even another layer of uniqueness to our descriptions.

Once again, the worksheet is available here with some additional product examples included so you can see the randomization in action.  Feel free to drop in your own examples to see how it works!

Have suggestions for improving the worksheet or other applications for this type of advanced concatenation schema?  We always value input to help us get better, so drop your thoughts in the comments or hit me up on Twitter.