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 didnt realize he was the original inspiration! If not for occasionally going back and reading old posts Ive 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.
Posted: 08.31.11
Devin Concannon:
This is GENIUS! Thank you for just saving me countless hours…
Natzir:
This post is AWESOME! thanks a lot Brett
Mike G:
Wow. This is gold. Thank you!
Steve at Choomka:
Thanks for the article, for some reason using Excel for creating unique descriptions like this hasn’t really crossed my mind before, strange as I use Excel pretty much daily for managing product data/csvs. Going to give this a go though, I love how expandable it it, previously I was purchasing unique descriptions from content writers, this could potentially save a fair bit of money/time though and I can no doubt rejig the descriptions I already have!
Pretty valuable, now I just need to find a little time to implement it! :)
Brett Snyder:
@Devin @Natzir @Mike Thanks so much! Glad this can save you some time!
@Steve I don’t think you’re alone there, I even forget sometimes that Excel is much more than just a numbers beast :-)
Hawaii SEO:
It never ceases to amaze be the things you can do with Excel. Way to push Excel to it’s limits.
Tamsin M:
“I was not able to find a way to substitute both variables from the same cell, which is why it required multiple steps.”
Can’t you just nest the substitutes?
=SUBSTITUTE(SUBSTITUTE(I2,”[Product]“,A2),”[Category]“,B2)
For the whole thing in one cell:
=SUBSTITUTE(SUBSTITUTE(CONCATENATE((INDEX(Sentences!$C$4:$G$4,1,TRUNC((5*RAND())+1))),” “,(INDEX(Sentences!$C$6:$G$6,1,TRUNC((5*RAND())+1))),” “,(INDEX(Sentences!$C$5:$G$5,1,TRUNC((5*RAND())+1))),” “,(CONCATENATE(“The product fits the following themes: “,C2,”, “,D2,” and “,E2,”. “)),” “,(INDEX(Sentences!$H$5:$L$5,1,TRUNC((5*RAND())+1))),” “,(CONCATENATE(“Manufactured by “,F2,”. “)),(CONCATENATE(“[Product] comprised of “,G2,”. “)),(CONCATENATE(“Available in “,H2,”. “)),(INDEX(Sentences!$C$7:$G$7,1,TRUNC((5*RAND())+1)))),”[Product]“,A2),”[Category]“,B2)
Luminaire:
Great post! Thank you for sharing such a helpful method.
Martokus:
I’ve used same principle in the past but for generating website product descriptions. Used a software called Parabuilder (no longer existing I think) that besides rotating sentences also shuffles the words of each sentence. I prefer it to Excel because it’s web based and you just call it via your program and it returns a set of content.
Jeff:
Just when you think you’ve seen every little technique in Excel, you come up with this! Awesome!
Definitely going to use this in the future for the larger sites with too many duplicate (or non existent) meta info.
gotcha5832:
Pretty impressive, but what don’t you did it by php?
Annie Cushing:
Wow! These are some awesome formulas – and even better strategy! Well done!
And thanks for the shout out. :)
Brett Snyder:
@Hawaii @Luminaire @Jeff Thanks for the kind words!
@Martokus I’m not familiar with Parabuilder but you must be right that it doesn’t exist anymore b/c I wasn’t really able to find much on it. I’ll keep looking though, thanks for the tip!
@gotcha I’m not much of a programmer so not really versed in creating things like this for PHP…I just knew I had a problem and wanted a solution in the best tool I knew how :-)
Brett Snyder:
@Tamsin BRILLIANT! I was playing around with nested substitutes but I couldn’t quite get it to work right. Thank you SO much for the tip, going to add it to this post right now!
Carlo Branca:
Wow, i still have not the need to make a e’commerce website so big,
but your solution it’s great to remember when it will be the right moment.
I loved also more the part where You say that can be apllyable to metadescription and that it’s a part i will test for a client for sure. Thank You!
Luminaire:
@Brett I tried to create a tool like yours with excel but I didn’t see how to manage with the “concatenate” function coupled with the “random” one.
It seems easy when you give all the formula;
Brett:
@Carlo I’m glad you’ll be able to use it with your clients!
@Luminaire Appreciate that, that’s why I wanted to give each step as well so if you want to construct it piece by piece you’ve got all the tools!
Cole Whitelaw:
I would abstract that formula one step further.
You’re hardcoding in that you have 5 sentences which means that you have a lot of formulae to change if you want more variety in the future.
so instead of =INDEX(Sentences!$C$6:$G$6,1,TRUNC((5*RAND())+1))
I’d extend the ranges to the end of the sheet and use COUNTA() to see how many actual sentences there are in there – that way you could have differing numbers of intro, body or finals or just more.
=INDEX(Sentences!$6:$6,1,TRUNC((COUNTA($6:$6)*(RAND())+1)))
Brett:
@Cole Love the thinking here and taking it to the next level! I agree it makes perfect sense and allows it to be more fluid for building it out, thanks!
Mitchell Allen:
Brett, this is a totally cool way to generate semi-random content. I’ve used Excel to generate everything from HTML to Sudoku. However, I would have been partial to importing the whole kit and kaboodle into Microsoft Access. Here’s why:
Once you’ve discovered how convennient this is, you’re going to want to tweak out the bits you DON’T like about the concatenation schema. Excel’s row,column limitation stifles creativity. Instead of thinking about randomness, context and other content-relevant issues, you have to expand columns, update formulas and consider using named arrays to simplify those pesky references.
With Access – or any database application, you can take advantage of queries and look-up tables. This means that your choices don’t have to fit in an MxN array. You could have 100 introductory sentences – which is really the meat of this varied content project. The other random fields could be enlarged over time. (Cole Whitelaw’s suggestion, Access-style!)
But here is the best part. You can sub-divide your sentence groups, making even more granular randomness. Remember Mad-libs? Let’s take “Category Sentences”, for example. You could fine-tune things by creating multiple random choices for this snippet:
All your [Category] friends will be [adjective].
By creating a list of adjectives, you’ve opened up more variety. Personally, that’s just the beginning, as people can sense Mad-lib substitutions. Someone mentioned Parabuilder in the context of word order. This would be quite simple to do in Access, as long as you keep the basic sentence structure clear.
Of course, if this were an ad hoc quick-fix, I would definitely use Excel. :)
Cheers,
Mitch
David:
Some great tips here thanks a lot. Always on the look out for ways to make things quicker with excel.
msaez:
Superb! Efficiency above all…and long live Excel!
Brett:
Thank you both, appreciate it!
Rich Norton:
Oh wow, that is quite incredible. Excel is a very flexible tool which I am still not entirely on top of.
You claims that you are unfamiliar with coding, but a lot of the Excel formula you posted looks a great deal like ugly Actionscript 2.0. From the little I know of Excel’s formula system it seems very similar syntactically to some of the cruder, less flexible coding languages.
Maybe you’d find coding more familiar than you suspect?
Thanks,
Rich
Hussain:
The formula is not working for me…! seriously need ur help! I am sure that I have make some mistake! Would love to get ur help in this regard!
Hussain:
Oh thnx, I got it :)
Luminaire enfant:
Hi,
The worksheet is no more downloadable ?!?
How can I retrieve it ?
Luminaire enfant:
Could anyone {give|send} me a link to retrieve the worksheet please ? (@Hussain @Rich @David …)
Thanks !!