256 Kilobytes

In a previous job I was head of eCommerce for a large motorcycle brand. This involved building, maintaining and marketing a Magento store with over 550,000 products from 45 different price lists and 35 different suppliers.

To put that into scale your typical Walmart Super Center will carry 120,000 products. This was a huge operation and we did this with 3 people including me.

With such a huge range of products and suppliers you can start to understand that scaling and maintaining such a store was a bit of a nightmare. The market itself was also decades behind what you can imagine, many suppliers would send us updated price lists on CD through the post. Some would send dozens of screenshots of their spreadsheet instead of the spreadsheet itself. Some only accepted orders through the phone, which they only answered when they had enough staff.

But these were only some of the issues of getting the products onto the store. The major issue here was lack of data. Out of all our suppliers only 3 actually supplied images, none of them provided product descriptions, data was muddled up and everything was a bit of a mess. When I started at the company 95% of the products only had:

  • A title - most of these were written in shorthand. "Chrome Brake Rotor" would be "Chrm Brk Rotor".
  • A SKU number.
  • A Price.

When you have a website with 525k products with no data, not even organised into categories (so they were orphaned pages as well) the result was a website that had no organic traffic and had less than 250 pages indexed.

This was clearly something we needed to fix. In this article I will be showing you how to generate the content we need which can be used for:

  • Meta data such as descriptions
  • Product descriptions
  • Google shopping feed content for ads

But outside of eCommerce, this method can also be used to mass generate content for a huge range of uses from video descriptions for platforms like YouTube or even mass generating contact emails for outreach or lead generation. The method can be used in any scenario where you have a spreadsheet of data (or you can create one).

Even for stores that get all the supplier information they need... You're going to be getting the exact same data as all the 100's of other online stores which stock those products. This is still something which can be beneficial for you.

Note: 99% of the time you're going to have better results with real, hand written content. However this obviously isn't always inside peoples budgets and can't be done quickly. Mass generating content isn't strictly white hat and within Googles guidelines. But if you look at this from the users perspective at least they have the information they need to make a purchase and from the businesses perspective at least they might make a sale.

How does this work?

The basic idea of this is to find attributes for the products that we can extract from the price list and to then insert these into a list of sentences which we have written and then piece these sentences together to quickly build out a high quality product description that's very readable for a human.

To understand how scale-able this is let's go off topic for a second and think about a desk of cards.

There are 52 cards in a deck. That means when you shuffle a deck of cards there are a huge number of different orders the cards could be organised in.

That number is:
8,059,999,999,999,999,660,317,004,150,386,417,419,458,446,991,720,740,591,424,740,786,176

Or 8.06e+67 for short.

Fact: There are so many possible orders of a shuffled deck of 52 cards that it's unlikely that particular order of cards has ever existed before.

If we bring it back to what we are discussing in this article you can start to see how powerful this technique could be.

If we had 10 product attributes, with 10 random sentences for each attribute we could end up with 1 million unique product descriptions with our products data passed in to each sentence.

Step 1: Let's look at the data.

The first step in this process is real simple, we want to start with a list of products. This can be done one supplier at time if you wish or you can combine lists together. The first few steps will depend on what your supplier gives you, or what you could scrape.

For the example given I've created a price list as I don't have access to the old wholesale data that I used to. A lot of this first step will require a bit of knowledge of what you're selling and common sense.

For the tutorial I'm going to be using excel which handles large data sets better than Google sheets in my experience, but Google sheets will also do the job if you're more comfortable using that. My example is below, we are starting with the bare minimum of what I experienced in the price list. Obviously your price list will look different but the process is the same.

We have:

  • Sku number - this is useless to us in terms of writing a description but you're going to need it later to import.
  • Product title - lots of these are written in short hand and need work.
  • Price - In terms of a product description using price should be avoided, unless you want to update the product description every time you change price.

Before we begin finding other data we should deal with and fix the horrible shorthand titles. These words are not ones that will be recognised by search engines and therefore they will harm your website in terms of SEO. Replacing them with the proper titles will allow search engines to get a real understanding of what the page is about. Product titles are generally used in the

and

tags on most eCommerce templates, these are super important tags for SEO.

We begin by noting down some changes we need to make, to do this jot down some of the shorthand words and the word they are meant to be. In this case my fake supplier sells aquarium filters, product knowledge (and common sense) shows me that:

  • "fltr" should be "filter"
  • "/w" should be "with"
  • "cnstr" should be "canister"
  • "HOB" should be "Hang on Back"
  • "pwr" should be "power"

Using the find and replace feature (Ctrl+F) these changes are quick and simple to make. Once you have replaced any shorthand from your product data we can move on to the next step. Your product list should look something like this.

Step 2: Planning out our attributes.

Now we need to plan out what we are going to do next and think about how we are going to get the data we need. We need to list out all the attributes we can for these products we have. The more we have the better our descriptions will be and the more unique combinations we can generate.

In my case we are dealing with aquarium filters, I can get quite niche in what I can list as attributes. If you are dealing with 1000's of products from the entire catalogue of a supplier you have two options really.

  • Stick to generic attributes.
  • Split your list into files for each category and repeat this process for each category.

The second option is the better choice, but it's more work. You have to make a judgement call on what you can realistically do.

Examples of generic attributes could be:

  • Colour
  • Manufacturer
  • Size
  • Pack quantity
  • Weight
  • Category

There's not a real answer in terms of what attributes you should be including, but keep in mind we're going to be building sentences around these later on. Ask your if these attributes useful to users? Can you actually get this data? Can you write 5+ sentences around this piece of information later on that don't suck?

For my product list I will be getting these attributes:

  • Form factor
  • Water output
  • Water output measurement (litres per hour/gallons per hour)
  • Max aquarium size
  • Manufacturer
  • 3 features

If you're looking for good hints on what attributes to include in your product list using a tool such as SEO Hero may help. By searching for the product category it will generate a list of words which are commonly featured in articles that already rank for that category keyword. These can be used inside the sentences we are going to write later on if you don't feel that they are a good attribute to include.

Step 3: Get the information.

There is no real way to get this, we now need to get the information for the attributes which you are going to use. You should have thought ahead in the previous steps to work out where to get this information when selecting the attributes. Create a column for each attribute on the sheet and set a semantic title then get to work.

There are a few thing which can help cut down time on getting this information when dealing with manufacturers. In my case one of the attributes I want to use is manufacturer which is contained in the product name column.

In Excel, if you format the sheet as a table you get some extended options to filter information. You can then do a "contains" search for the attribute you need and in the results of the filter, write it in at the top of the relevant column and drag down.

It's important that you get as much information as you can. Leaving spaces is going to cause more work later on, this is part of the process that is going to take the longest but it's important we get the information.

Other source of information may be scraping of competitors websites, it's possible they have published this information. Pay attention to their terms of service to make sure they allow this. Other options for this could be using services such as Mechanical Turk.

It's important we get the correct info and as much of it as we can. Don't move forward until you're 100% sure you have exhausted every avenue.

Step 4: Organisation for writing.

Hopefully we now have a table with lots of attributes to use and form natural sounding, universal sentences that work well together.

Before we go and write a load of stuff out it's going to help us to think about how the finished descriptions will look when put together. With the attributes I have I want to create something like this:

  • Brief intro using Form Factor
  • Talk about the manufacturer
  • Sentence about the filter output
  • Sentence about the max rated aquarium size
  • List of features

This is very simple, if you're going to do this with a real world example you might want to make it a little more complicated but for the example this is fine. You're going to need to alter this to suit the attributes you have and content you need.

We now need to make a new sheet, call it "sentences" and lay it out similar to below. Each sentence we need will have a dedicated row, the numbers at the top are for each sentence you're going to write. In my case I have 5 but I recommend doing more than this. The more you have, the more unique combinations you have.

With 5 sentences and 5 attributes we have 3125 possible outcomes:
5 x 5 x 5 x 5 x 5 = 3125.

Step 5: Writing the sentences.

It's now time to write our content. You could pay someone to do this for you but you've come this far, from here in it's all fairly easy and things will be moving a little faster.

When writing your content we need to pass in values from the attributes but we're not going to do this just yet. For now we're going to use the attributes title from your first sheet wrapped in square brackets to act as a placeholder for this content.

For example, if you have an attribute called "color" we will use it in a sentence like this:

This product has a very nice high quality finished, painted [color] you will be sure to stand out from the crowd.

With this in mind it's time to write, keep in mind though that Excel does not have a spell checker. You might prefer to write in MicroSoft Word and paste over when it's complete. Also don't be afraid to add any HTML you need to add for formatting reasons. This won't interfere with anything later on. It's also a great time to drop in various keywords, this will help your SEO. Re-read the SEO Hero report we ran earlier to give you some ideas of what other (high ranking) web pages are writing about.

When you're done you should have a sheet that looks something like this.

Once this is complete you're ready for the next step. Spend your time here, all that prep work could mean nothing if you don't do a good job!

Step 6: Putting the sentences together.

Now we have all the sentences done, we can start to piece things together. The easiest way to do this is to hide your attribute data (as we don't need it anymore) and create a new set of columns next to the hidden cells.

Once this is done we are going to use the below function.


=INDEX(sentences!** Range of correct sentences **,1,TRUNC((** Number Of Options ** *RAND())+1))

The first piece of data you need to change in the above formula is the ** Range of correct sentences **, the second bit is ** Number Of Options **. This needs to be replaced with the number of sentences you wrote.

So for my case, the formula for the "Sentence 1" column that we just created is:


=INDEX(sentences!B2:F2,1,TRUNC((5*RAND())+1))

For the "Sentence 2" column the forumula will be:


=INDEX(sentences!B3:F3,1,TRUNC((5*RAND())+1))

And so on...

When you've set all these up you should have something that looks like this.

For those of you didn't manage to find attributes for every single product you may not want to import sentences for those products. It's easy to do this, simply sort each column A to Z for content and only apply the relevant formula which pulls in the data to these cells.

Next up we need to concatenate all of these sentences together to get the final structure. We will create another new column at the end of the sentences, call it "concatenated content".

In this row run the concatenate function for the 5 (or however many you have) sentences. You can also use this opportunity to pass in spaces between each sentence to format them proper. It should look something like so:


=CONCATENATE(M2," ",N2," ",O2," ",P2," ",Q2)

Once this is done you should be getting the concatenated content, this will be the completed product descriptions except the variables have not been passed in yet, they should look something like this:

The [Product Name] is a fantastic filter for all your aquarium needs. As with all [Form Factor] filters you can expect great benefits such as easy installation, easy cleaning and high performance. If you're looking for an aquarium filter from an award winning brand, [Manufacturer] have you covered. This filters pump is rated to [Water Output] [WO Measurement], but keep in mind this is variable and can be turned down for smaller tanks. This [Form Factor] filter suits all kinds of aquariums up to [Rated Size]. Beyond this rated size we would recommend something bigger. This product has some awesome features including: [feature 1] [Feature 2][Feature 3]

It's worth reading a bunch of these to make sure they make sense, adjust your sentences as needed.

Step 7: Where are the variables, please.

How all this is done, we are left with a single thing to do and that is to pass in variable for our content. We will get rid of the [variable] tags and have real, finished content!

To do this we will use the substitute function, that's it. But unfortunately as we have more than 1 variable we will be having to nest multiple formulas inside each other. Make another column at the end of the spreadsheet called Substitution. You can also hide the sentence columns we made earlier.

Our formula will begin like this:

//Blank Function
=SUBSTITUTE(text, old_text, new_text, [instance_num])

//For my specific use, [instance_num] is optional.
=SUBSTITUTE([concatenated content], "[Variable To Replace]", [Cell with variable value in it])

//actual fomula used for [Product Name]
=SUBSTITUTE(R2, "[Variable To Replace]", B2)

In this case any instance of [Product Name] will actually be replaced with the value in B2, which is where my variable is. But... we need to do this a lot. So we will be nesting this inside another substitute function where the text value would go.

This formula would replace both [Product Name] and [Form Factor] variables:

=SUBSTITUTE(SUBSTITUTE(R2, "[Product Name]", B2), "[Form Factor]", D2)

This continues, until you have done all your variables:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(R2, "[Product Name]", B2), "[Form Factor]", D2), "[Manufacturer]", E2), "[Water Output]", F2), "[WO Measurement]", G2), "[Rated Size]", H2), "[Feature 1]", I2), "[Feature 2]", J2), "[Feature 3]", K2)

When this is done your Substitution column should contain content just like this:

--------------------------------------------------
This Internal filter is an excellent and popular product from MarineLand. MarineLand are a well respected brand in the aquarium filter world. They have a fabulous history of creating great Internal filters and this MarineLand Magnum Polishing Internal Canister Filter is no different. The flow rate on its highest setting for this filter is 50 GPH. This Internal filter suits all kinds of aquariums up to 15 gallons. Beyond this rated size we would recommend something bigger. This Internal filter offers great features such as:

  • Three stages of filtration
  • Easy to install
  • Two year warranty.

--------------------------------------------------

Finished!

As you can see this is a very powerful method to build out massive amounts of content relatively quickly with simple steps and a few Excel formula function. From this point you will have to do what you need to do to import the content, this depends on your stores software and everything else. I can't help with that here.

This is as scale-able as anything else out there and it can go as far as you need to take it. If you really wanted to build out hyper complex articles you can. If you wanted to generate a few 100 social media updates in bulk, you can. I've used this method for product descriptions, reviews, small simple articles... It's very powerful.

Users Who Have Downloaded More RAM:
August R. Garcia (4 months ago)
🐏 ⨉ 1
Posted by Hash Brown 5 months ago 🕓 Posted at 13 December, 2018 03:08 AM PST
Profile Photo - Hash Brown Hash Brown
🗎 50 🗨 314 🐏 123
Staff
Profile Photo - August R. Garcia August R. Garcia LARPing as a Sysadmi... Portland, OR
🗎 136 🗨 713 🐏 166
Site Owner

This whole process is a classic in a ton of Internet marketing work. Definitely worth experimenting with the process for anyone new (or even moderately experienced in) IM. 

This is as scale-able as anything else out there and it can go as far as you need to take it. If you really wanted to build out hyper complex articles you can. If you wanted to generate a few 100 social media updates in bulk, you can. I've used this method for product descriptions, reviews, small simple articles... It's very powerful.

Related: A former professional blackjack player posted a process on generating "infinite articles" on some other internt forum:


Also, this post reminds me of some code for running spintax in Google Sheets (or anywhere that JavaScript can be used). Will post as an article when I have a minute. Easily able to be combined with your method here if/when variance desired in the output. Ex:

  • Sentence:
    • If {you are|you're} looking for an aquarium filter from {an award winning|a trusted|a reputable} {brand|company}, [Manufacturer] {has you covered|is a solid choice|is worth checking out}.
  • Some Possible Outputs:
    • If you are looking for an aquarium filter from an award winning brand, Coralife is a solid choice.
    • If you're looking for an aquarium filter from an award winning company, Coralife has you covered.
    • If you're looking for an aquarium filter from a trusted brand, Coralife is worth checking out.

Edit: Post with JavaScript code for spintax in Google Sheets:

Download more RAM. 🐏 ⨉ 0 Posted by August R. Garcia 4 months ago

Edit History

• [2019-01-02 2:59 PST] August R. Garcia (4 months ago)
🕓 Posted at 02 January, 2019 02:59 AM PST

The CIA wants all code in the cloud under their lock and key. They want to ban compilers and make people think HTML is computer programming. - Terry A. Davis

Post a New Comment

To leave a comment, login to your account or create an account.

Do you like having a good time?

Read Quality Articles

Read some quality articles. If you can manage to not get banned for like five minutes, you can even post your own articles.

View Articles →

Argue with People on the Internet

Use your account to explain why people are wrong on the Internet forum.

View Forum →

Vandalize the Wiki

Or don't. I'm not your dad.

View Wiki →

Ask and/or Answer Questions

If someone asks a terrible question, post a LMGTFY link.

View Answers →

Make Some Money

Hire freelancers and/or advertise your goods and/or services. Hire people directly. We're not a middleman or your dad. Manage your own business transactions.

Register an Account
You can also login to an existing account or recover your password. All use of this site is subject to terms outlined in the terms of service and privacy policy.