As I was thinking about the subject of this post, it occurred to me that, while there are lots of articles telling you what you should do to optimise your marketplace listings – you should create titles in a certain way or make sure everything is listed with as many attributes as possible -I haven’t seen anything that talks about how you go about doing that when you’ve never done it before or how do you do that for 30,000 Skus.
So, this is practical advice on “how” to create optimised listings and how to do that for large inventories that change often. With this process it is then possible and practical to create campaigns on as many channels as you want.
In writing this, my hope is that I can both provide practical guidance to anyone who might need to do this while, at the same time, being able to provide practical marketplace services to sellers.
For the sake of a clear narrative, I’m going to use a fictional Brand, “Superfly” (yeah, sorry) as an example so we can see some actual – but not really actual, if you get my drift – product data and discuss some real-world examples.
BEFORE WE START
We’re going to need to start with some ground rules. I’m going to be talking about product data and how I go about manipulating it at scale so that the listings perform. This means using software.
Of the software that one could use for this sort of thing, I am most familiar with Excel so will reference that but, in the real world, I would always use a listing tool to manage the order side of the equation.
If you aren’t familiar with Spreadsheets and Excel formulas, you might struggle to keep up but I explicity want to discuss how to build the logic that can then be run automatically so you might not get full value form this article if that’s the case.
I’ve included a Glossary of terms as a separate article so that I can just talk about things rather than stop to explain something that some of you will already know.
Right, here are the salient points about Superfly:
- Product Type: Sportswear – Shorts, t-shirts, trainers etc.
- No. of SKUs: 10,000
- Target Marketplace: Amazon UK – (eBay titles probably require special mention thanks to their restriction to 80 characters but I might tackle that at the end).
THE SOURCE DATA FILE
Most retail businesses already have this licked (although not all). Most have a website where people can buy their products so this usually where we get our source data but essentially we just need a product feed from the best source of data we can get.
If you’re a reseller, the chances are that the data you are given is either a) almost nothing or b) compromised. When I say compromised I mean shared by a load of other sellers removing any competitive advantage and probably seen by Google as duplicate content.
For the purposes of this article we will assume we have a source data file from which we can work.
The goal is to have a spreadsheet with one worksheet which Contains the source data and a second that contains all of the columns we are going to need to send to Amazon. This second sheet contains all of the rules to transform the data into the formats that we want. In this way, we can simply paste any new products into the Source data worksheet and the data will be automatically converted.
In point of fact, we will have a couple of other worksheets but we’ll come to those.
STEP 1: CHECK YOUR DATA SOURCE
Start by familiarising yourself with the requirements of the channel. Amazon provides this information in the help section of Seller Central. The page titled: Inventory file templates contains a table where you can download 3 types of file:
Style Guides: This is a pdf suggesting how you should structure your ads.
Inventory file templates: This is a multi-tabbed spreadsheet that enables you to create a bulk upload file specifically for the product type selected.
Browse Tree Guides: Browse Nodes is Amazon’s name for product categories. This file contains a list of available categories along with its unique Node ID.
In the case of Superfly, we’re looking at two categories: Clothing and Shoes so we’ll need both sets.
Take your time with this because it really helps to plan and, the more familiar you are with the data requirements, the more mistakes you will avoid down the line.
The Amazon fields are listed on the Data Definitions tab of the Inventory Template. I tend to place the field name in the first row and the Required? values in the second. Like this (Fig 1):
FIG 1: AMAZON TEMPLATE HEADERS
The quickest way to do this is to simply copy the values from Template worksheet and then use a vlookup to match the Required values:
=VLOOKUP(A1,'[Flat.File.Clothing.uk.xlsm]Data Definitions’!$C:$G,5,0)
With the Required? values on the page, it is relatively straightforward to then remove unwanted columns. You don’t want to touch any that are Required (at this stage) so we’re only looking at removing the Optional ones.
You’re looking for fields where you don’t have any source data or which aren’t relevant: obviously, you won’t need to specify the Skirt Size Value if you don’t have any skirts.
Once you have your framework, it helps to break your work down into sections:
- SKU relationships and structure
- Categorisation
- Attribute mapping
- Titles
- Search Terms.
- The rest
This is pretty much the order to work in but it can help to look ahead so you can see where you might have some missing data points that you’ll need to research.
For the sake of this example, I’ll be working with a simplified version of the client’s file with only 3 products (31 SKUs) but the structure is real and starts like this (Fig 2):
FIG 2: FIRST FEW COLUMNS OF SOURCE DATA
STEP 2: SKU RELATIONSHIPS AND STRUCTURE
Ok, so the first thing we need is for every item to have a unique SKU number. This is shown clearly in column B. However, we will need Parent SKUs in the same column as the Child SKUs: instead, they are listed in column A as an attribute of each child SKU. This means we’re going to need to create them.
TRICK 1: PIVOT TABLES
We can use the sku_ref values as parent SKUs but we’ll need to remove any duplicates first. We can do this in a few ways but the simplest is to create pivot table: highlight the first column, go to the Insert Menu and select Pivot table.
FIG 3: PIVOT TABLE POP UP
Create the table in a new worksheet (this is third of four mentioned earlier) and then click on the field name in the Pivot table fields sidebar. If you drag it from the values to rows box, you end up with a deduplicated list of values which can be copied and pasted into a new worksheet (this is the fourth of four). So my end list looks like this:
In this case we only have 3 values but it works just as easily for hundreds and thousands of values.
We then copy and paste these values below the existing list of SKUs in column B on the original worksheet. We then write a simple cell reference formula in the first available cell in the Seller SKU column of our Amazon worksheet like this: =’Source Data’!B2 and then drag down by grabbing the bottom right corner which should then copy the formula into the rows below.
You want to cover more rows than there are SKUs so you don’t miss any. It will be fairly apparent where the SKUs end:
By doing it in this roundabout way, instead of simply copying and pasting the skus into the Amazon Sheet directly, we can replace the SKUs on the Source Data worksheet with new ones and the Amazon sheet will update itself.
TRICK 2: USING LOOKUPS
Ok, with Trick 1 we use Pivot tables to identify the values we have to work with. Now we’re going to use Lookup functions to pull the data into the export (Amazon) worksheet.
Here is the variation structure that Amazon wants (as specified in the Inventory file template for Apparel):
FIG 4: AMAZON VARIATION FIELDS
The Parentage column is asking us to specify whether a product is a Parent or Child SKU.
Again, we want to use a formula to populate this data. Since we know that only the Parent SKU values exist in column A, we can write a formula to use this:
=IFNA(IF(VLOOKUP(B3,’Source Data’!A:A,1,0)=B3,”Parent”,””),”Child”)
Note the VLOOKUP function that we’re using to identify whether the SKU number we’re looking up exists in column A of the Source Data sheet
And for the Parent SKU we can use an Xlookup:
=IF(XLOOKUP(B3,’Source Data’!B:B,’Sample Data’!A:A)=0,””,XLOOKUP(B3,’Source Data’!B:B,’Sample Data’!A:A))
The key is to ensure that we use formulas to create the values rather than putting text in as, this way, we can change what goes into the master sheet and the Export sheet will take care of itself.
If we extend this to all the variation fields we end up with this (Fig 5):
FIG 5: COMPLETED VARIATION FIELDS
Lookups really come into their own when you combine them with a Pivot. Let’s look at Categorisation by way of example.
STEP 3: CATEGORISATION
Unless you’re only selling one type of product, I always use a lookup to map categories. Especially with large inventories, where there’s a large chance you want to avoid inconsistencies. This means we need a set of values that we can match to Amazon’s Browse nodes.
This file has a lot of data points that we can potentially use to create our mappings. It’s quite common for data files to include values that we don’t need. Most data sets have grown over time as the business grows. Here are the relevant fields from our Source data:
FIG 6: LIST OF CANDIDATE CATEGORY FIELDS
There is clearly some duplication where we have a code and a value that are effectively telling us the same thing so let’s remove these. I tend to keep the values rather than codes as these are potentially useful by themselves:
This file has a lot of data points that we can potentially use to create our mappings. It’s quite common for data files to include values that we don’t need. Most data sets have grown over time as the business grows. Here are the relevant fields from our Source data:
FIG 7: SIMPLIFIED LIST OF CANDIDATE FIELDS FOR CATEGORISATION
Ok, it looks as though the Webtype is a combination of the Classification and the Gender-name so that field looks like a good candidate. But, hang on, that field tells me that the second product is a men’s T-shirt. But the Type column says it’s a Polo. Polo’s and T-shirts have different Browse nodes so that’s not going to work.
I think a combination of the Webtype and type fields will give us enough information to build a category map. For this, I’m going to insert a column at the end of the Source Data and write a concatenate function like this:
=CONCATENATE(P2,”:”,AA2)
I can then pivot the results to get the unique values which I can then manually map to Amazon’s Product Type, Department and Browse Node resulting in a table on the Lookups worksheet like this:
FIG 8: CATEGORY LOOKUP TABLE
Doing it this way instead of individually for every line means that I only need to map each value once and that mapping will work for any new items we add to the list in future.
We can now use a lookup to populate the mapping back to the main worksheet. Using a formula like this one which gives us the Browse node ID:
=VLOOKUP((IFNA(VLOOKUP(B3,’Source Data’!A:BM,65,0),VLOOKUP(B3,’Source Data’!B:BM,64,0))),Lookups!A:D,4,0)
It may look complicated if you’re not used to Excel formulas but it’s really quite simple. This rule only has 2 functions: VLOOKUP (look up the value in B3 in the first column of a table and then count a number of columns across and tell me what’s there) and the IFNA (if the result you get is #N/A – a calculation error – then do something else).
In this case, the rule first needs to find the Source Category value we created and does that by looking in the Parent SKU column but, if that fails, looking in the Child SKU column and then looks up the result in the table above. Simples.
In the end we get this:
FIG 9: MAPPED CATEGORIES BY SKU
STEP 4: ATTRIBUTES
Atttributes typically fall into 2 groups: those that can use any value and those that need to use an Amazon value. The former tend to be infinitely variable where choosing a value from a list makes no sense like the Brand. The latter tend to be used as filters on search results pages like the Colour Map and can be set up the same way as the Category lookups – with a pivot table and lookups.
Registered Brands tend to be case sensitive so, even though we can use a straight vlookup to pull on the values, again, we have some inconsistencies to clean up. Here are the unique values we have:
We have Superfly written with an Initial capital letter and all in caps. We want them with just the first letter capitalised. could use a lookup table to fix it but it’s simpler to just add a Proper function. Like this:
=PROPER(IFNA(VLOOKUP(B3,’Source Data’!A:L,12,0),VLOOKUP(B3,’Source Data’!B:L,11,0)))
For the Colour Map, we can use the same principal of building a lookup table for each attribute that needs to be mapped.
First we use a pivot table to get a list of unique values Then we choose the closest matching value from Amazon’s list of Valid Values for Shirts:
FIG 10: COLOUR LOOKUP TABLE
For the Colour Map, we can use the same principal of building a lookup table for each attribute that needs to be mapped.
First we use a pivot table to get a list of unique values Then we choose the closest matching value from Amazon’s list of Valid Values for Shirts (Fig 11).
FIG 11: SHIRTS AND SHOES COLOUR VALID VALUES COMPARISON
I would then use an IF statement to select which lookup table to use:
=IF(A3=”shoes”,
IFNA(VLOOKUP(VLOOKUP($B3,’Source Data’!B:U,20,0),Lookups!I:J,2,0),””),
IFNA(VLOOKUP(VLOOKUP($B3,’Source Data’!B:U,20,0),Lookups!F:G,2,0),””))
Ignore the line breaks, I just spread the rule over three rows to make it easier to read. IF statements go, IF(Logical test, Value if true, Value if false). The logical test is, IF the product is a shoe, look up the colour in the table in columns I to J, if not, look up the colour in the table in columns F to G.
These techniques can be combined to cater for pretty much all category and attribute mappings.
STEP 5: TITLES
Alright, automating titles can be tricky. Particularly when you’re mapping to eBay where they are so important in search but you have a character limit. It often depends on the quality and consistency of the source data.
Superfly already have a couple of title fields in their feed file (Fig 12).
FIG 12: SOURCE TITLE FIELDS
However, the Short Title looks inconsistent and has issues with the case and double spaces and has clearly been built for internal use so they aren’t values I would trust to always be set out the same way.
The Title field is also problematic. According to the Amazon Style Guide for Clothing, Parent titles should look like this:
[brand_name] + [department_name] + [style_name] + [product_name] + [model_name] + [opacity] + “pack of” + [number of items]
For Child SKUs, you just add the colour and size:
[Parent ASIN Name] + [color_name] + [size_name]
So, our Titles are missing size and colour. There is also an issue with length: “Limit ‘Parent ASIN’ titles to 60 characters and ‘Child ASIN’ titles to 150 characters maximum”.
OK, so if we don’t like these, let’s look at what we could construct with the available data. We have the Brand, the Department, the Style: Adidas Mens Trail Runner. The opacity and number of items isn’t relevant here so this leaves the Product name and Model Name. These are problematic as we don’t have these in their own fields.
The thing to understand is that some of these rules aren’t fixed. If you don’t provide the information exactly as Amazon specifies, they aren’t necessarily going to block your listing – they will if you use offensive language or exceed 150 characters or break any of the other Prohibited rules listed on the style sheet – but we do have some leeway.
In this case, it seems to me that the best option is to use a shortened version of the Title. Maybe everything up to the hyphen:
Adidas Bionic Waterproof Mens Running Sneakers
That’s not bad. And we can then add the size and colour for the child skus. But how about this one:
Superfly Supersonic Womens Crew Neck Top 100% Cotton with Breathable fabric & Easy Care
It’s OK, but it’s already 87 characters long and, because it doesn’t have the same structure as the previous 2 products, it tells me that these titles don’t follow a strict pattern so I’m likely to find further issues with the future products.
That’s the thing about creating titles with rules, the source data is usually inconsistent meaning the titles themselves are inconsistent and you need to build in all kinds of exceptions: if it’s longer than 60 characters, remove anything after a “with”. In this case that would result in:
Superfly Supersonic Womens Crew Neck Top 100% Cotton
Which is fine, but I’m pretty certain there’ll be an instance in future which breaks the rule.
You have a few options here:
- Write your rule and keep tinkering with it when you find issues – I have written exception lists in the past: if the SKU is in this list, lookup the hand-written title from this table here…
- Write your titles by hand – While this does tend to produce the best results, as people can make complex adjustments and find key attributes to squeeze in when space allows, it can be slow and expensive.
- Use a hybrid of the two. Write a rule that gets you 70% there are then flag those that don’t work (usually too long or too short) and send them to your people.
For this example, let’s run through a couple of the more common tricks:
THE FIND FUNCTION
Alright, let’s say we want to remove everything after the hyphen. For this I would use a combination of a FIND and a LEFT function.
What you want is all of the characters left of the hyphen so, the first thing to do, is work out where that hyphen is. For this we use FIND:
=FIND(” – “,’Source Data’!E2)
This rule says, in cell E2, tell me where the “ – “ text is. This brings back a number – 47 in case you are wondering. I.e. this text starts at character 47. Note, I included the spaces because I don’t want those either and it would exclude cases where there is a hyphenated word.
So, now, I can use this number to specify the number of characters of text I want:
=LEFT(‘Source Data’!E2,FIND(” – “,’Source Data’!E2)-1)
Which gives me the left 46 characters or:
Adidas Bionic Waterproof Mens Running Sneakers
Ok, that’s fine but not ideal: I think Trainers is a better keyword than Sneakers in the UK but the description of the product is clear and it’s their brand. I do have the Search Terms field where I can ensure the keyword, “Trainers” gets indexed.
In point of fact I would use a vlookup in place of ‘Source Data’!E2 but you can work that out for yourselves.
But, when I copy down, I can see three, must fix issues (Fig 13):
- The Parent SKUs aren’t working
- The Title for the, “Supersonic” is too long
- The Child SKUs need the size and the colour.
This is where rules start to become unwieldy. We can fix all of these, but the rules can grow quite long and difficult to decipher and I’m not going to do that here. This article’s quite long enough already and I still have a few things left to cover.
Suffice to say, if you are populating the Search Terms field with proper care and have a good set of product features, unlike on eBay, your title doesn’t need to be perfect for the product to perform well.
Fig 13: Calculated Titles
STEP 6: SEARCH TERMS
I don’t want to talk too much about Search Terms save to say do your research. Just google, “Keyword Research” and you’ll get a load of platforms that can help you think of the right terms but I’ve found most to be of limited help.
If you have a Google Adwords account you could use that on the assumption that buyers use the same search queries across platforms. Or, if you have registered Brand with Amazon, you could use their Search Terms Report
Most of the keywords that are unique to one shirt over another tend to be in the listing anyway so you can cover a lot of ground applying Search terms to groups of products. Group your products by type or category and again use a lookup to apply them.
You can always go back later and look for improvements once you have more performance data to look at.
There are a couple of common errors to call out. These are all listed in Amazon’s help section but I’m drawing attention to them as so many sellers seem to ignore these particular points:
1. DON’T REPEAT KEYWORDS
I saw this recently:
Baby buggy baby pram baby stroller
You don’t need the word, “baby” in there 3 times. Once is enough. Similarly, you don’t need to repeat words from the title or feature bullets. Repeating keywords isn’t bad, necessarily, you’re just using up limited space that could be put to better use.
2. DON’T USE COMPETITOR BRAND TERMS
This is against Amazon’s terms and could result in punitive action. I’ve seen many people doing this and getting away with it but this chicken will come home to roost at some point and, I for one, wouldn’t want to risk account suspension.
3. DON’T USE PUNCTUATION
Amazon’s Search engine (A9) is an ever-changing and complex set of algorithms that recognise text strings (a collection of characters) separated by spaces. By adding a character to the end of a word, you change the string.
Amazon have built in a load of exceptions. and refinements so they can often recognise and handle such eventualities, but don’t rely on them to fix everything. Just follow their advice and you won’t go too far wrong.
DESCRIPTIONS, IMAGES, PRICES AND QUANTITIES
There are a few points to make about these fields before I sign off.
DESCRIPTIONS:
Amazon doesn’t allow html in its descriptions any more so, if yours are tagged in this way you will need to remove it. There are a number of online tools that can do this but results can be imperfect so check any results thoroughly.
You may prefer to use an online tool like Listabl if this is an issue as you may struggle to fully automate using Excel.
IMAGES:
Images don’t tend to cause too many problems but there are a couple of issues to be aware of:
If you’re image urls have been supplied in a single field you will need to separate them. This can be done with a simple function in ChannelAdvisor and Listabl but with Excel you need to go round the houses. I tend to use a FIND function as described above but if there are many images all joined, you may need to use a set of nested LEFT and RIGHT Functions and repeat the process to get what you need.
Let’s say I have this value for the images:
https://img.cdn.superfly.com/product/MR63871-1.jpg;https://img.cdn.superfly.com/product/MR63871-2.jpg;https://img.cdn.superfly.com/product/MR63871-3.jpg
This rule: =LEFT(AC34,FIND(“;”,AC34)-1) brings back this result:
https://img.cdn.superfly.com/product/MR63871-1.jpg – that’s good.
I can then subtract the FIND value from the length to get everything to the Right of the semi-colon. So:
=RIGHT(AC34,LEN(AC34)-FIND(“;”,AC34))
gives me: https://img.cdn.superfly.com/product/MR63871-2.jpg;https://img.cdn.superfly.com/product/MR63871-3.jpg. I can then apply the same to rules to the result of that RIGHT rule to split these two images.
If I have a lot of images, I will often create a new worksheet to build this out.
PRICES:
Pricing on Amazon is competitive. If you’re selling the same item as other businesses, in theory, you should all be using the same listing page (ASIN). If this is the case, you should be using a repricer.
Repricer automatically change your pricing based on the parameters you set (what’s your minimum price? how much do you want to undercut your competition? do you want to compete with Amazon themselves etc.).
While there is always a risk allowing software to set your pricing, I would recommend using a Repricer, preferably an algorithmic one. These versions are more sophisticated than their rules-based counterparts and can actually increase your margin while growing sales.
QUANTITIES:
Depending on the sales velocity, sending a feed once a day may not be enough.
Let’s say you have 1 unit left of a given item and that item has been listed on eBay and Amazon. If you sell that item on Amazon, you need to tell eBay you have none left. If you only update your quantities once a day, eBay might not get the update for 24 hours.
For this reason, I tend to recommend that quantity updates are kept separate from the main product feed so that you can process changes quickly (as close to real-time as you can get).
Typically this means using an API but, if you have a small inventory you might get away with using a feed file.
The other option would be to buffer your stock: in this way you subtract a fixed amount from the actual quantity so that you always have at least one (or more) left over. You have 5, you subtract 1 before you before you tell eBay so they think you have 4. If you sell 4, eBay thinks you’ve sold out when, in fact, you have 1 left.
You still run the risk of overselling if you sell more than the buffer quantity before you send an update but the risk is lessened and you can always apply a bigger buffer.
FINAL THOUGHTS
This method means that, once you’ve set it up, as long as any new product data is structured the same way, with the same columns, you can simply paste it into the Source Data worksheet and optimised data will miraculously appear on the Output worksheet. Turning a task that might take weeks into something that can be done in 5 minutes.
Systems like ChannelAdvisor and Listabl enable you to automatically send and receive data as well as manipulate the data in the process (Excel can do this too btw, you just need to really know what you’re doing and the process is easily disrupted).
There is a lot to learn and the set up can take some time but, if your inventory is constantly changing, or you’re managing more than just Amazon, you need an efficient way to manage your data.
