A Database Marketing Veteran’s Guide to Managing SKUs

The Guide to Managing SKUs

  1. Do NOT delete your historical SKUs
  2. Don’t replace or rewrite a SKU because it’s basically the same thing as deleting it
  3. If you do happen to delete your SKUs write down every category and then see if those words are found in the description of your products.
  4. Maintaining your SKUs is planning for your future because if you can understand past buyer behavior you can make better decisions for the future.

Below is a lightly edited transcript of Episode 27 of the Inevitable Success Podcast.


Damian: So here I am sitting at the Buyer Genomics Headquarters here in Manhattan, right in Midtown and I bring that up because New York Fashion Week is upon us and clients are bringing that up left and right because a lot of them are fashion retailers.

One of the topics that has come up recently is, “I have, you know, a new season of products coming. What do I do with the existing SKUs?” So this might seem like a very boring data hygiene thing, but the decisions that you make each season with your products and your SKUs have long lasting ramifications. If you’re serious about, you know, understanding your customer and their historical patterns you can make better decisions in the future.

Stephen: That is correct.

Damian: But I digress.


Stephen: No, no, no, it’s all good because I’m going to talk about why you have to keep the history intact for the prediction of the future behavior. So let’s get into all of that. And I’ll give you the short answer to your question first.

Damian: Yes, so if you don’t want to listen the whole thing, this is it.

Stephen: Exactly, just hear this out and you can turn it off after this. Number one rule – Do not ever delete your SKU history in your Shopify or any other data sets.

Damian: Okay, so I think that’s actually part one.

Stephen: That’s one. Number two.

Damian: Now I’ve got a new product, do I use the same SKU? I don’t delete it. Or do I make a new one?

Stephen: Depends on what they are. If their attributes are different, maybe you need to, but we’ll get into that too. One other thing, SKU numbers are long.

Damian: Uh oh, it looks like you have to listen to the whole podcast episode now.

Stephen: Yeah, yeah I think you should, because I’m going to talk about how all this will help you determine who the best customers are.

So we’ll get to that. So anyway, number two. Do not recycle either. Don’t delete – number one rule. Number two, do not recycle. If it’s different, then don’t use the same number. You have about what, typical SKU numbers are over 12 digits. Just using alpha numeric code your talking about infinite number of combinations. Do not please them.

Damian: All right so there’s no need to use the same one.

Stephen:  And you know what, you can even have the rules set up so the machine can generate it for you. But when you do the rules set I’ll ask you this. This is rule number three. When you set up SKUs, please keep the most general category first. Do not put things that variant which are – let’s say you are selling shoes. What are the variants?

Okay fine. Size. Okay size, color, width, maybe style right?

More like Oxford or a Brogues – Kingsman’s line, that was the password. Do you remember that?

Damian: Vaguely.

Stephen: The answer is Oxfords but not Brogues. Anyways, when you do this I’ve seen SKU numbers where they put all this, what we call variant SKU in the middle of the description. So to find out that it is just loafer shoes or men’s dress shoes or a mule, whatever that is. And all these things are in the middle of the SKU to find out what it was, to search and figure out what the rule was.

Now I’ll get to why you need all this. The reason why we did this is that – here’s what customer behavior looks like and when we ask for transaction data from customers, and they go, “Oh transactions that is a lot of data, what do you want?” Then we say, “Okay let’s break it down.” We want who bought it, that’s important. And what is who? Name, address email, first/last name, all that kind of stuff. Determining that, okay I know that –

Damian: Right, individual identifiers of people.

Stephen: Exactly, we need that so we know, okay that is done by one person and we connect all these dots using such PIIs. PII stands for Personally Identifiable Information – that’s who.

Second is, how much does she or he spend – how much. Now that’s a little tricky. You’re talking about what was the price, how many units did she buy, what was the net price total, tax, shipping, coupon amount, discount amount, whatever it is. And finally how much does she swipe, the swipe amount – total amount. okay. You get all this fine. Part of it, okay. What you want is a list – can we figure out how much discount we’re talking about because I want to find out who the heavy bargain seekers are. So yeah all these numbers become something, that’s what I’m trying to illustrate here.

Then I want to know when. When is very important because you’re talking about RFM, you’re talking about recency. But hey, it gets better. What about things like days between transactions? Right? So to do that we need to time-stamp. Is she a morning buyer/evening buyer? Is she a weekend buyer? I need to know the time-stamp to know these things so that you know when to send an email to her.

Then it gets further. So we talk about who, when, how much, right? And then we talk about channel. Is she an online buyer/offline buyer? What if you send an email and she buys this pair of shoes and she wants to try it on and shows up in a store. Now she’s responding to email, that’s your channel of communication on your part, but she bought through a store – that’s an offline transaction. The point that I’m making is that yeah we need all of it, outbound channel and inbound channel.

Now finally what, what did she buy? Now that is a SKU number level thing and of course do I have to really know that she bought these kind of shoes in color red, size 9, with this and all that – do we really need to know all of them to predict what she’s going to do in the future?

Yeah well if your goal is to maintain perfect inventory management system, yes you do need them all. Fine.

And this is why SKU numbers are important because, you know, well I’m running low on the red shoes and I have plenty in black and you’ve got to know these things.

Damian: You know I’m going interrupt you for a second. Because I actually think that there is – some brands that do this but not all, and I don’t know why, especially in fashion with size. I mean, this is a long, long, long, long time ago, maybe like 10 years ago, but I remember working with a client and they were scratching their heads around why some products had such like poor conversion rates. And if you speak to the people that do the merchandising inventory, it was pretty obvious like in two seconds. They were like out of stock on basically all the sizes except the sizes that nobody wants. So basically one of the things that’s interesting is if you know your customer – knowing their size is pretty important to knowing the customer.

Stephen: Especially when you’re all sizes. In fact, even when I say I don’t care about the size, sometimes I do. What if you were specialized in petite size or large size? You’ve got to know the size.

Damian: Yeah because then you can send messaging or waste touches or you know maybe even what’s worse than missing a touch is sending like misinformation or disinformation about something that they would want but they can’t have.

Stephen: Of course if you’re spinning some kind of machine learning, or AI, even colors could be interesting. But based on my experience in having to look at like 70 models, documents per day for seven years in my lifetime, color is not that important. Maybe it is indicated that only –

Damian: If you’re like Steve Jobs.

Stephen: Yeah you only wear black turtlenecks, but what are you going to do with that information? Now the goal is again – if the goal is to predict what they’re going to do next, those things are pretty important. If your goal is to maintain a perfect inventory management system, then yes you do need all these things. But I’ll illustrate why all this categorical data is important, because I talked about when and how much and all those things right? Now, we’ve been doing the RFM marketing for what? Forever right? It’s RFM, eh it works. That’s why it’s lasted so long. But can we do more than that? The answer is absolutely, positively yes. So how far do we go? Now we can talk about clustering segmentation and modeling, all these things, and you need good data ingredients to make those modeling process work better. So, it’s one thing to just enter dates. We had recency – great she bought something recently. So the last transaction date or week since the last transaction has been say, forty-two weeks or something. That could be information that a human being or a model can use right? Now wouldn’t it be more interesting that, you know what, I’m looking at Damian’s record and the last time you spent any money in the sporting equipment category is this – but the last time you spent some money on a home electronic category is that – that paints you a very different picture. And then what drives that difference is the product.

Now if you’re only selling shoes, that’s interesting too. Is it men, women, children, whatever or what kind of casual shoes, running shoes, workout shoes, dress shoes, could be loafers, whatever – slippers, right? Beachwear. So what part determines and makes things more colorful in terms of all these RFM variables? Now I talked about the recency, but hey what about the days between transactions for casual shoes is more frequent than dress shoes. We can even describe that person, definitely. Hmm, maybe he’s more casual than formal for example. Or what about the dollar spending? What if we can track maximum spend amount, and minimum spend amount, and average amount for every category. And by the way we do – which is kind of scary but yeah we do.

So that means I know the threshold of any purchase. In other words, I’m a very cheap spender when it comes to home electronics. I think that’s a commodity. When I buy a T.V. I watch the price for a few days and I pull the trigger only when I feel that – okay, this is a good bargain and I’m going to pick that merchant and I’m going to buy from there. That’s the decision that I made. But when it comes to musical instruments –  it’s a very intimate purchase. I’m not by any means, a bargain seeker. I want to have an instrument that I like to play for example. And women feel that way about certain dresses. They maybe make a utilitarian bargain seeking purchase – one day you look for yoga pants or work out clothes – but they may act very differently to buy a suit for work or dress for some event or whatever it is, right? Maybe she’s not a bargain seeker when such merchandise is involved.

Damian: Yes. Some purchases I’ve actually heard clients describe them as coveted purchases.

Stephen: Yes in fact, some products – hey the more expensive the better! They sell better. For example like all these name brand Italian handbags – sometimes it sells better if it’s more expensive. So product is very important – this is why the SKU is very important – to identify what it was. Related to that we will look to see the SKUed description to make sure that yeah that’s in that category. And of course I want to have categories because when you deal with any kind of data like this you could have upwards of a few thousand or sometimes upwards of two hundred thousand SKUs for each merchant.

Damian: So this is just from my practical experience handling SKUs, most SKU systems, you have two types of SKUs: one’s like a parent or a category SKU, it’s like basically a fictional product that doesn’t actually exist. The other one is the physical like, you know, inventory level SKU that, you know, it’s on shelf number six, aisle five. And you know, sometimes I’ve found that you can use the category part of the non-physical SKU – maintain that one and then you keep changing like the variants for – that’s a good strategy and it works well for product listing ads and things like that too.

Stephen: Absolutely, and all I ask is very simply, please have some rules and please do a variant part of the SKUed description or the number towards the end. So that if you use only the first six digits of a SKU number, for example, it means a general product or all these numbers mean they are men’s shoes for example.

Damian: So I want to sum up some stuff and then I have one really important question which revolves around, “That’s awesome, I listened to this podcast too late and I already deleted everything, what do I do?” So hold that, I’ll let you think about that for a second.

Stephen: That’s a good one.

Damian: But first off, takeaways – do not delete historical SKUs.

Stephen: Yes because the past will tell you about future predictions. Love it.

Damian: Two, don’t replace or rewrite a SKU because it’s basically the same thing as deleting it with some caveat. Actually it’s worse.

Stephen: It’s worse. I’ll tell you why, because in the prediction business or even a basic data mining business – let me just say this, I’m going to say again, maybe I said it already, in data mining or any kind of analytics business, data consistency is even more important than accuracy. Let’s say that I categorize you as a gift buyer for expensive women’s accessories. Maybe you buy for your wife for example right? So I categorize you that way – and by the way I categorized a buyer – not product. And by the way that’s another trick that I’m going to share right now, which is when you describe all these categories we’re not doing this to categorize product. We’re trying to do the categorization of people, people who are behind the purchase, to describe the buyer. Now imagine that you switched the description or the product itself. Now that SKU’s use for something else. I’m going to put somebody who bought something completely different in the same category as you. Now is that right?

Damian: Right. We use the same SKU for, you know, brake pads that we do for handbags. It’s just doesn’t work

Stephen:  Maybe it’s not even that different, but hey what if you’re Amazon, okay? That’s a good example actually. It is because it can be that different.

Damian: Okay so, you didn’t listen to the podcast. You have deleted all your SKUs, what is one to do?

Stephen: It’s time to hit the SKU description and hope to God all the words are intact and that you somehow kept in your transaction history, that you kept your SKU description, even if you change the way your number system works in the present day. But for the past transactions that you may have kept the SKU name and that description – well it is time to go text mining. And there’s a good and easy way to do it, or there’s a more complicated way to do it. And if you’re not diving into – okay well I just have all these SKU descriptions that I use for the website and I only need the first two category lists.

In other words, when we get the category lists do we break them into level one, level two, and level three categories. For example, level one could be men, women, children, petite, large – that type of break. Some people use their level one category as a designer name or a collection name if you deal with a lot of different brands. Right. Level two would be what it is – is it types of shoes, accessories, women’s dresses, whatever it is. Or if it’s just women’s apparel then I’ve seen things like – yeah, okay, fine this is women’s clothes, but what kind of a category is it: bridal wear, underwear, swimwear, loungewear, formal wear, casual wear, workout wear, whatever – it’s just called something. The third category is something more specific and that’s different from merchant to merchant. Sometimes they use some designer’s name or the collection name in there.

Sometimes the collection is in level one. I’m not going to try to dictate what people do with these things, but it all depends on what value do you put and a good indication is that when I go to the website of that merchant, which is the first category break on the website? That’s a dead giveaway actually. Oh and another thing by the way, please don’t put “Summer Clearance” as a category because that’s not product.

Damian: That’s actually a good little piece of intel there. I know what you’re talking about.

Stephen: I’ve seen so many things like that. Now is that bad? Because yeah sure, why not, your ultimate goal is to describe the buyer, not the product itself so it may just work. But it becomes very inconsistent, I’ll just put it to you that way.

Damian: Yep, absolutely. We could have a whole extra discussion about proper categorization.

Stephen: That’s right.

Damian: And I think it depends on the purpose of, you know, who you’re trying to help – a person of a channel like Google product listing ads or you know, you’re just inventory management. But that is definitely a whole different aspect.

Stephen:  In fact I wrote a long article about it, so if you’re curious, you can read it. If you just Google my name and look for a title called Freeform Data Are Not Exactly Free.

Damian: You’ll have to remember the title so – We can put it in the show notes. We could do that. Yeah because we have that technology.

Stephen: So in that link, you can find the article about how to categorize things properly. Now I’m not talking about the product categories, I’m talking offer categories, any kind of a freeform category.

So how do we salvage, in a case where something is reused again? Again, if you have wiped out the whole thing and don’t even have the record of the past transaction and product name or the description in the transaction record, well we’re not in the business of having to recreate the data when they’re all gone. That’s more like a forensic type of analytics. It’s not even what we do.

However, let’s say that you do have it. In that case, what I do is I look at, again, go back to the website of the particular merchant, write down every category that I see and then see if those words are found in the description. Now it’s a good proxy. Another way is that you go to the open source text mining techniques and then actually there is open source coding that resolves these product descriptions and puts them into categories. You could do it that way.

Damian: I’m just going to call you if that comes up.

Stephen: Well call us, actually so we’ll help you. But hope to God that – no you don’t want to spend extra resources or money to retrace all these things. So again what’s rule number one? Please don’t delete.

Damian: Right. So I guess to sum up, this is an exercise in planning for your future because if you can understand past buyer behavior, you know, you can make better decisions. That’s right. And if you did delete then there’s a couple of things you can do. That’s right. But this was fun.

Stephen: Yeah it was fun. But again we can talk about a lot of other things coming from this is – which are: why such predictions are important, is it for product recommendation or just to find out what kind of a buyer you’re dealing with and try to predict if she’s going to respond or not? All these things come into play. So we’ll talked about different types of modeling or prediction – we could talk about that next time.

Damian: Sounds good. Alright, go enjoy Fashion Week.