1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

[Tutorial] Creating scalable, easy to generate and semi unique content in mass.

Discussion in 'Black Hat SEO' started by Ste Fishkin, Nov 11, 2015.

  1. Ste Fishkin

    Ste Fishkin BANNED BANNED

    Joined:
    May 14, 2011
    Messages:
    2,058
    Likes Received:
    8,214
    One of the major problems with running an ecommerce store is finding relevant content which not only helps in terms of SEO, but enables your buyer to learn enough about the product to make a purchase in the first place. Now some of you guys out there that work on ecommerce stores will be lucky enough to have suppliers which hand you the data on a plate which is great in terms of user experience, but you will find every other seller online is also using the exact same content in exactly the same way, and as we all know duplicate content will not get you very far in 2015.

    So how did I solve this?

    For the last 18 months I have been working in the motorcycle niche running an online store. Here is some stats to help you get your head around the problem I had:
    • Built in Magento community edition
    • 487,000 products
    • 44 price lists
    • 35 suppliers
    • it would cost you 66,000,000 to buy 1 of each item
    • We have items that cost 1 penny, right up to 27,000.
    • 54 Online PDF's uploaded with over 80,000 pages between them.

    When I first started there we simply had nothing. No product images, no descriptions, a price and a title, that's it. Sales were very poor and it needed to be turned around or we were going out of business quickly.

    Obviously if you jump on an ecommerce website and you see no image, no description, no details or specs, you're not very likely to spend your money as who knows what you will be getting.

    So today I will be teaching you to fix this issue, and showing you how I wrote 487,000 product descriptions using excel in just a few days. Now if you don't run an ecommerce store you might not give a fuck, but think about it... this can be used in all sorts of applications from meta tags to maybe even product reviews or fake comments. By using your head the possibilities are endless and this is super scalable. It took me 3 days to generate content for almost half a million products from almost nothing!

    So this is what we start with, this is a real price list from a supplier containing 9,000 products:
    [​IMG]

    In truth this is one of the better catalogues we deal with, typically we get a sku number, a title which could be written in German shorthand and price. Here we have weight, decent titles (not brilliant), brand and unit of measure. The first step is witling the data down, for our purposes we want to separate product data into as many columns as we can, but only if we can write something about it. In our case availability, dealer price, retail price and catalogue are of no use to us so these will be removed.

    [​IMG]

    After this we need to start looking into our products and picking out what data we can create to give us more information. The more information we have the better. By spending 5 mins looking through my price list I have worked out I can add the following fields from the information available:
    1. Fitment - what bike the products fit
    2. Category - the category the product will fit into (chains, handlebars, seats and shit)
    3. Finish/colour
    4. Construction Material
    5. Size

    Now, you can see I have simply added fields for these variables which we will add. You might be thinking where this information is coming from but its quite easy. Some brands such as Saddlemen only produce seats, so the category is seat, material will be leather or vinyl, what bike it fits will be in the product title and so on. There is no shortcuts here, you will need to go through your price list by hand and extract the information from what you have and as every pricelist and niche is different I cannot really help you with shortcuts. You will need to just roll your sleeves up, stick some headphones on and get on with it.

    Note: You absolutely could outsource this to someone else, but as the information you are gathering is important I would not recommend it. I tried it and it just didn't work well. They either missed massive amounts of data or they were simply incorrect. You need product knowledge to do this, the last thing you want is customers returning items as you put the wrong information in the description. Spend an entire day on this if you need to, it's got to be right.

    Top tip: formatting as a table and searching for colours/fitment info/finish/materials in your columns will save you a lot of time.
    A few hours later you will have something like this:
    [​IMG]

    Note: As I am doing this purely to teach you fuckers I have just now focused on 50 or so products. I thought these shocks would be good examples as they contain a lot of the data, but not it all. You will also find this when you do it yourself... not every product will have all the information available and unless they are expensive products where you are making really good money it's just not worth the time and effort it would take to hunt down this information. So do what you can. Generally the 80:20 rule applies to this, you will spend 20% of your time and you will get 80% of the information, you will then spend the other 80% of your time getting the last 20% of information... it's not worth it.

    So now we have all the info we can get on with it. The basic premise of this idea is to simply write 10+ sentences for each piece of information (column) we have in a way that they can be randomly added together to create 200+ words of semi unique, readable and informative content which Google will like, your users will like and in a way which it will sell your product. It sounds a bit mental, but it will make sense.

    So to demonstrate how effective this method is and how much time it can save you lets pretend you have just 6 product attributes to write about. If then write 10 sentences which use these attributes there is 1,000,000 possible combinations of these sentences, meaning in theory that's 1,000,000 little unique bits of content you have generated from just 60 sentences.

    10 x 10 x 10 x 10 x 10 x 10 = 1,000,000

    Start a new sheet and lay it out like this:
    [​IMG]

    You will notice I have increased the size of the cells, it just helps with writing. Also its usually better to write in Word and paste it over, excel has no spell check.

    Again, you could outsource the writing, but in truth this is the easy bit. If you went through and grabbed the data from your price list earlier yourself you will find this to be a very simple job. Just get it done, the more you write the more unique combinations you can create.

    [​IMG]

    That is what it looks like when done, as you can see where I intend to insert the variable for each sentence I have used the column name in a pair of square brackets. It will all work out don't your worry bae.

    You may also notice I got lazy, some of these sentences are repeated, some are just poor... My example does not matter, you will do a better job than me I am sure. You will also notice I dropped the material row... there's no point seeing as my example products don't have material listed but its easy enough to add your own columns and modify all this to suit yourself.
    The next step is a little more techy, we are going to write the formula we use to pull all the sentences in.

    First, on your pricelist sheet you will need to add a column to hold the sentences. Now there are two ways to do this, you can either pull all the sentences into individual columns then concatenate them together, or you can write one bad boy monster formula to pull sentences in and concatenate them in 1 single move.

    As I am trying to keep this simple let's do one at a time, so add your columns. They should look like this.

    [​IMG]

    So the formula we are going to use in each cell is this:

    =INDEX(sentences!$B$2:$F$2,1,TRUNC((5*RAND())+1))

    This randomly selects each sentence from your sentences sheet. You are going to need to modify the numbers highlighted red each time to pick the correct sentence. Change it to the number of the row your sentences are located on.

    You will also need to change the green highlighted number depending on how many sentences you have for each variable.

    Once you have completed this for each variable you will have something that looks like this:

    [​IMG]

    The more eagle eyes among you will notice some are blank... why is this? Simply put there is no available variable for that column sentence. So why have it? 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. If you were not lazy earlier and you have no blank spaces ignore this step.

    The next step is to concatenate this all together into one cell, simply apply this formula to a new column on the end of your sheet. If you need to alter it the concatenate command is very simple, but also really powerful. It's well worth spending 15 mins learning it.

    Here is the concatenate formula, the " ." things are for punctuation, grammar is important bitch.

    Code:
    =CONCATENATE(K8455,". ",L8455,". ",M8455,". ",N8455,". ",O8455,". ",P8455,". ",Q8455,". ",R8455,".")
    It should look this this:

    [​IMG]

    And it should now be outputting paragraphs like this inside the concatenated column:
    Code:
    Ste's bike store is proud to introduce this fantastic product to the world.. [Brand] have been producing some epic parts for your motorcycle over the last few years. It's time you paid attention and bought their shit you son of bitch. . You can have this product in any colour you like, as long as its [Colour]. Looking for a [Size] incher? We got you covered. Replace those old tired [Category] parts with shiny brand new ones.. This product is available in packs of [UM]. This product weights [Weight]KG.
    You should now start to see all your work come together... great.

    The last step to all this ball breaking work is to substitute all the square bracket place holders with real variables. Let's use the substitute command.

    Add a new column to the end of the sheet, call it whatever you like. From here we can nest substitution commands inside each other to do all the work in 1 go, or you can do it column by column, your choice. But heres the nested formula for you:
    Code:
     =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(S8457,"[Brand]",C8457),"[Fitment]",D8457),"[Colour]",E8457),"[Size]",G8457),"[Category]",H8457),"[UM]",I8457),"[Weight]",J8457) 
    Next step, grab a fucking beer and admire your work.

    You are now done. From here you need to work out what the best way is to upload this data to your platform but I am sure you will manage this. In our case we run magento, we use magmi.

    Here is an example of a completed description:
    Code:
    Ste's bike store is proud to introduce this fantastic product to the world.. PROGRESSIVE SUSPENSION have been producing high quality motorsport products for a number of years, if you're looking at improve the performance or style of your ride you cannot look any further than PROGRESSIVE SUSPENSION, they offer unbeatable performance for reasonable prices.. If you own one of the following models, this part will bolt right on your ride. Harley-Davidson. Is your favourite colour Black? Well you will just need to buy these parts.. Looking for a 11.5" incher? We got you covered. Looking for a new set of Shock Absorbers, we don't blame you.. We sell this product in packs of PAIR. This epic product weights 3.331KG.
    Looks good right?

    When you use your head and the tools you have at hand writing massive amounts of content can be broken down into very little steps. I hope this demonstrates this, and also shows you the power of excel. A program we all have for free (who the hell pays for it amirite?) can be so much more effective than any SEO tool you can throw at me.

    It's also absolutely scalable, add more sentences and for very little extra work you can literally be generating MILLIONS of pieces of content in seconds. I guarantee you ain't seen a method this effective for a long ass time.

    Enjoy your night, I need a long sit down in a dark room after posting a serious helpful tutorial. Fuck.
     
    • Thanks Thanks x 65
  2. Cryogenesis

    Cryogenesis Jr. VIP Jr. VIP

    Joined:
    Sep 1, 2013
    Messages:
    1,769
    Likes Received:
    2,506
    Gender:
    Male
    Location:
    India
    Home Page:
    Thank you for this,Ste.
     
  3. Ste Fishkin

    Ste Fishkin BANNED BANNED

    Joined:
    May 14, 2011
    Messages:
    2,058
    Likes Received:
    8,214
    Download the example excel sheet here:

    Virus Total
    https://www.virustotal.com/en/file/76aafd341dbe866ff29d428ebe5e2576f3d8eeb845513f48e7cbf2ab0821e78b/analysis/1447276726/

    Code:
    [TABLE="width: 100%"]
    [TR]
    [TD]SHA256:[/TD]
    [TD]76aafd341dbe866ff29d428ebe5e2576f3d8eeb845513f48e7cbf2ab0821e78b[/TD]
    [/TR]
    [TR]
    [TD]File name:[/TD]
    [TD]pricefile example.xls[/TD]
    [/TR]
    [TR]
    [TD]Detection ratio:[/TD]
    [TD="class: text-green"]0 / 54[/TD]
    [/TR]
    [TR]
    [TD]Analysis date:[/TD]
    [TD]2015-11-11 21:18:46 UTC ( 1 minute ago )[/TD]
    [/TR]
    [/TABLE]
    
    Download
    https://mega.nz/#!hsInnKoY[FONT=source_sans_proregular]

    [/FONT]
    Decryption Key
    !ENErzUbp9xrkJ4HgAegRKHtiMJtd_rQJ_J9Zk2KQRDI
     
    Last edited: Nov 11, 2015
  4. tomarm

    tomarm Registered Member

    Joined:
    Oct 4, 2015
    Messages:
    52
    Likes Received:
    15
    Location:
    Matrix
    Home Page:
    What are the chances you have this post as a PDF? xD It is absolute gold
     
  5. t0mmy

    t0mmy Executive VIP Jr. VIP

    Joined:
    Jun 5, 2011
    Messages:
    6,726
    Likes Received:
    14,424
    Gender:
    Male
    Location:
    Spain
    Home Page:
    Epic Ste.. The seal of approved marketer's head honcho in his element!
     
  6. Ste Fishkin

    Ste Fishkin BANNED BANNED

    Joined:
    May 14, 2011
    Messages:
    2,058
    Likes Received:
    8,214
    The seals pick the winners, I just sign the certificates.
     
    • Thanks Thanks x 2
  7. MoneyEagle

    MoneyEagle Regular Member

    Joined:
    Nov 6, 2015
    Messages:
    328
    Likes Received:
    146
    Gender:
    Male
    Occupation:
    Internet Marketing
    Solid Work. Thanks for posting.
     
  8. frifster

    frifster Newbie

    Joined:
    Sep 1, 2014
    Messages:
    14
    Likes Received:
    5
    Great post! thanks for this idea.
     
  9. Ste Fishkin

    Ste Fishkin BANNED BANNED

    Joined:
    May 14, 2011
    Messages:
    2,058
    Likes Received:
    8,214
    Edited: Double posted something like a fucking idiot.
     
    Last edited: Nov 11, 2015
  10. Hobo82

    Hobo82 Newbie

    Joined:
    Apr 22, 2009
    Messages:
    43
    Likes Received:
    10
    Gender:
    Male
    Occupation:
    Worked in marketing, a content and copy writer.
    Location:
    New Jersey, USA
    Wow, thanks for that informative post. Would love to download it, but it is asking for a decryption key, sorry if I have missed it.

    Thanks again
     
  11. Ste Fishkin

    Ste Fishkin BANNED BANNED

    Joined:
    May 14, 2011
    Messages:
    2,058
    Likes Received:
    8,214
    Ahh fuck sorry dude, I've added it to the download post.
     
  12. Riders On The Storm

    Riders On The Storm Jr. VIP Jr. VIP

    Joined:
    Feb 27, 2012
    Messages:
    1,287
    Likes Received:
    518
    Thats good stuff mate. Will come handy for my stores.

    Btw, how you handle the product images and thumbnails? Is there any way to automate that?
     
  13. Ste Fishkin

    Ste Fishkin BANNED BANNED

    Joined:
    May 14, 2011
    Messages:
    2,058
    Likes Received:
    8,214
    Some suppliers I rip them from their server using wget, some of them will supply you with the files if you bribe them with beer at trade shows.
     
    • Thanks Thanks x 2
  14. Riders On The Storm

    Riders On The Storm Jr. VIP Jr. VIP

    Joined:
    Feb 27, 2012
    Messages:
    1,287
    Likes Received:
    518
    I meant how to upload the product images and thumbnails? Lets say we generate the product details of 1000 products with your tut and upload to our store. But how do we upload the product images and thumbnails for those?
     
  15. JimBean04

    JimBean04 Junior Member

    Joined:
    Mar 13, 2015
    Messages:
    156
    Likes Received:
    49
    nice tut, gonna read this later
     
  16. antichrist

    antichrist Jr. VIP Jr. VIP

    Joined:
    Aug 21, 2012
    Messages:
    1,942
    Likes Received:
    2,562
    Location:
    On top of the world!
    Strange choice of niche for you my man.
     
  17. Ste Fishkin

    Ste Fishkin BANNED BANNED

    Joined:
    May 14, 2011
    Messages:
    2,058
    Likes Received:
    8,214
    Depends on what software you're running.

    I do all imports of any kind through magmi, including images.

    Magmi is a magneto extension.
     
    • Thanks Thanks x 1
  18. Sherbert Hoover

    Sherbert Hoover Jr. Executive VIP Jr. VIP

    Joined:
    Dec 26, 2010
    Messages:
    1,290
    Likes Received:
    10,799
    This is one of the best guides I've seen on here. I work with Excel day in and day out and never once thought to use INDEX and CONCATENATE functions to build product descriptions. Solid, solid, solid stuff, Steve.
     
  19. ChanzGrande

    ChanzGrande Elite Member

    Joined:
    Feb 16, 2008
    Messages:
    2,487
    Likes Received:
    1,179
    Occupation:
    Accountant
    Location:
    Northern Woods Counting Money
    Been doing some spinning over the years with excel as well. I find your guide to be extremely well thought out, and put together. Of course there will be many who don't realize the side implications and usages of what you have demonstrated, but certainly these techniques are fertile ground for alternative content generation strategies. Don't like to lay everything out there, but I imagine one isn't only limited to spinning words in the cell contents.

    This approach can pretty much be utilized in a number of ways to feed content into your systems and/or to simply spin and generate. So valuable! Thanks Ste. Hughes!!!
     
  20. Hawkster

    Hawkster Jr. VIP Jr. VIP

    Joined:
    Jun 22, 2013
    Messages:
    3,510
    Likes Received:
    3,728
    Gender:
    Male
    Occupation:
    Listen to everyone - Follow no-one
    Location:
    UK
    Home Page:
    Just when you think Ste Hughes has lost the plot totally - he goes and redeems himself with a quality post!


    Cheers Ste.
     
    • Thanks Thanks x 4