#702 - How To Use Amazon Flat Files

#702 - How To Use Amazon Flat Files Speaker 2: Ever struggled with Amazon flat files or felt unsure about how to use them? You're not alone. In this session, we're bringing on Sasha Zubitov, who will walk you through how to download and access Amazon flat files, provide step-by-step guidance on how to utilize them effectively, and answer all of your burning questions. This is really going to help you perform those tasks that might feel impossible inside of regular seller central. Unknown Speaker: Hello everybody and welcome to another episode of the Serious Sellers Podcast by Helium 10. I'm your host, Bradley Sutton, and this is a show that's completely BS-free, unscripted and unrehearsed, organic conversation about serious strategies for serious sellers of any level in the e-commerce world. Speaker 2: I'm really excited to have you on and I guess the first thing that I really would like to ask you is especially for the beginners who might not know what flat files are, could you maybe tell me about you and then tell me what flat files are? Speaker 1: Thanks, Shivali. I've been consulting and managing Amazon business since 2017, so about eight years. It started in just a bet between me and my friend that I could beat his I'm a website sales with Amazon sales. Eventually, a lot of US manufacturers who have their branded products or sold their products in traditional channel, which means that they were selling through independent distributors and then sold to retailers. As a result, their brands had tons and tons of products listed on Amazon, but they had no idea what was happening with their Amazon listings because they didn't list them themselves. They had extensive product catalogs with huge problems because you'd have one product listed by 10 different ASINs or you might have one ASIN with 10 different UPCs attached. With that kind of mess to untangle, you really have to use flat files. And flat files are really just very long Excel files that contain all the information that you usually see in your user interface when you click the edit button on your listing. So all of that information, all those fields or the data is listed in the Excel file and then you can edit it and upload them in bulk. And that's sort of the main use of the files is to Do many listings at one time instead of editing one listing field by field. Speaker 2: Do you want to go ahead and present your live bit and then we'll get into questions. Unknown Speaker: If you want to watch the Serious Sellers Podcast in video form, make sure to go to YouTube and check out our brand new YouTube channel. Just type in the search bar Helium 10 Serious Sellers Podcast and you'll see our S logo come up. Make sure to hit that subscribe button. Go to the channel and binge watch any of our episodes that we have done lately. We'll see you there. Speaker 1: So most of the presentation is using Excel file for a live company that have this catalog listing report, which is a download of all of your information in your account. And I've updated to hide the particular company's information, but it was actual data. It's several months old, so it's a little bit different from what you're going to see, but it was a live account. When you download your flat file, and I'll show you where you do that, but let me show you the flat file first. When you download it, this is what this monstrosity looks like. It's a collection of fields in rows and columns that represent all of your listing data. All right, and so this is what we're going to be looking at today. Where you download this information, you go to your inventory reports and you download what's called a category listing report or CLR. And this is something that by now everybody should have, at least everybody who is brand registered. And if you don't just ask seller support and they will make it available for you. And you'll download that Excel file and then when you make changes to it, you're going to upload it back to Amazon and here are the links that you need to upload the file and get additional Information using the file. So when you get the category listing report, this is a version of the live data that's in your account for all of your listing and all of your categories. And it's very difficult to work with. And so the first thing that you want to do is format it and clean up a little bit and make it useful for you to work with. All right. We're going to look at some of the basic things that you want to do with this flat file. After you format it, we want to talk about what kind of SEO impact you're going to be able to have like search engine optimization factors. I mentioned, you know, how you might want to use this file to defend against hijackers. One of the main ways that I use flat files to help people is to help them manage their variations. That is something that's very useful to work with. Using flat files to reorganize your parent and child listings today. It's especially important because we've had announcement from Amazon about Deprecated variation themes. So this is something that will become relevant very quickly. And finally, there is a role for flat files in supporting your issues with compliance problems. If your listings get taken down, if there's blocks, if you're facing appeals, there's an important component there. So these are some of the things we can mention. I'll try to run through them quickly and maybe there are questions that we can answer down the road. So here's again our category listing report. A lot of things that you want to do is you want to unhide some of the rows that Amazon hit here, format the columns, create drop-downs. I'll do that very quickly. I have a macro. Did I have a macro? Maybe I need to open a file. Let's try that again. I have a macro that does that for me. You can record a macro yourself so that once you do it and format the file the way you like it, you can then repeat that Every time without having to go through each step separately, but essentially, here we go. We've got this row that was hidden, which actually lists the names of each of those attributes of each of those columns. So not just the header name, which is a user friendly name, but also the technical name that you might need to talk to technical support or to understand error reports. So that's listed here. And you also want to be able to have dropdowns so that you can sort or filter just the information that you want to work with. Once you do that, these become a lot easier to work with. You also want to freeze the page so that you can just zero in just on the information that you need. And so what you see here is that This file contains really all of the fields, the attributes that you see in your user interface when you click on the edit button, right? So it contains your SKU, right? It contains the ability to update or delete the information. This item type, this is your category. And all the other fields keeps going, going your images. This, this file has about 300 fields. So 300 columns are pretty extensive. Then yours will range from, you know, a hundred to 500 or so typically. Right. So, um, once you make changes to this file, You're going to upload it and Amazon is going to give you a response, a result, and that's one of the first things that you want to pay attention to, and this is one of the most helpful things because if all is well and all you're doing is you want to update several fields, maybe many fields, then maybe what you want to do is use the Helium 10 listing builder. If you don't want to learn and invest time in learning this entire flat file environment and you want to stay within the Helium 10 environment, which I often do by using Cerebro and Index Checker or Keyword Tracker, and that works really well together with Listing Builder. All of those things together when you make changes, you can track that in Helium 10. If all you want to do is update All of the important keywords, all of the important attributes in your listing, then you might want to stay within that. But if you want to resolve problem, And you're sensing that there is some conflict. There is some problem with Amazon accepting your uploads. Then you may upload your category list and report a portion of it. And what you get is a feed processing summary, something like this, where Amazon will tell you what the problem is. And so this one is a very straightforward one. You can tell immediately that you put a decimal point somewhere that doesn't belong. And it even tells you here which particular row and column number you need to look at. It doesn't happen in every error message, but this is helpful to fix many of them. Now, this is helpful. It can become a little bit too helpful when you get a very large report. And because you do get, you can get bucked down in a lot of these error messages, what I recommend is that when you work with a template, you don't upload your entire category listener. I'm the founder and CEO of Helium 10. I would search for SKUs that are part of a particular group. And one of the nice things that you get when you're working with flat files, when you create your listings, you can actually name them yourself using easy readable names. And so I've selected listings that belong in a group. In other words, there's a parent right here. I've highlighted it and the rest are children. So what you can do is you can select this group like this and you can just paste it in a separate file or just save this file as a separate file and just work with that group and then you'll get fewer errors. I would suggest that you even start with a single listing because in Amazon, the errors can actually affect each other. And so when you have a large group of errors, you might not actually be able to see what the problem is because they affect each other. So once you reduce it to a small number of errors, you actually get to see the problem. So this is something that you wouldn't be able to do with a listing builder. So when you get to this point, then you want to use flat files. What you want to do with these listings in your flat file is fill out as much information as you can that's relevant to your to your listing. And when you do that, that That optimizes your listing both for search and for AI. This is not optimized. You can see that there's a lot of empty fields, and so they need to be filled in, or I would recommend that you fill them in. Let me give you one example that I really like particularly. So this is Bradley's listing from Helium 10. It's a coffin shelf. And when Bradley talks about his confidential listing, he often mentions this particular feature called the special feature. Special feature is an attribute in a file and the nice thing about this attribute is that it is one of those attributes that gets indexed so that when you fill it out and when you have You have something like this and you fill it out and you put your keyword email coffee shelf in the special feature column, the special feature attribute, then it becomes part of that display, your product display page, and it also becomes indexed. And so now you can, you can appear, you are now available to appear for searches. All right. So that's, that's one important factor. Now, special features is not an attribute that appears in every category, but most categories do have attributes like this, which are, which give you this capability of indexing very quickly. So, let's see, where are we? So one of the things, again, what you want to do is fill this out as much as you can with relevant information, information that's relevant to your listing. Another thing that you will notice is that some of the values are blacked out. They're grayed out by Amazon. You see this entire section is black and the reason they're blacked out is Amazon does not consider those to be relevant to your listing. And so if they're not relevant, then maybe you don't want to fill them out. But occasionally what happens is that they are blacked out, but you think that they are relevant to your listing and you'd like to fill them out. So, first of all, think about this for a second. What controls in this file, what controls, if you know, what controls whether this attribute is blacked out or not? Which attributes are relevant and are displayed in this file to be filled out? So a lot of people think that this is controlled by your category, which is this column. It's called the item type keyword, right? But it's actually controlled by another column, which is not available in your user interface. So this is something you will not see if you click the edit button, right? And this column is this one. It's called the product type. We're the feed product type in the columns. And you can see here, this file contains several different product types. So there's baking chocolate, baking mix, there's candy, chocolate, candy, etc. And let's see what happens. And hopefully this is live. So hopefully this will work. See what happens if I change the product type. From baking chocolate to let's say chocolate candy. Okay. You see that the flat file changes and this attribute now suddenly becomes available. And so you can fill out the model number for, for this product. Very often the product types are very similar to each other and they control how this product is displayed on the display page. So they control, for example, the shape of this area, the placement, these badges. That's all controlled by your product type. So it's not as much as changing your category. It is in a way changing your sub subcategory, if you will. Right. And as opposed to changing your Item type which Amazon guards religiously. You know that this column is very difficult to change and requires quite a bit of effort. Amazon is fairly flexible when it comes to this attribute. So if you determine that some of your listings do need to belong in slightly different product type because it might have been selected by accident or Amazon changed it or you mistook it. Then you can modify it through the flat file, upload it, and it will be updated. And if it doesn't, then you will usually have success having seller support do that for you. So that's several advantages that you have working with flat files. You have those type of capabilities. You can work with keywords that can be indexed in certain attributes, and you can actually control the way your display page looks and which attributes are utilized as part of that display page. All right. So let's take a look at what I use this file for most of the time, and that's variations. So I think actually, yes, we already saved this here. Alright, so let's take a look at where the variations are. So the variations, the parent group is controlled by this section here and in new flat files, they are starting to rework these These columns the way that they are presented so they're going to be a little bit different There's going to be a lot more limitations. It can be a lot less Opportunity to change things that's true, but these are still available the old ones What this column tells you is the type of the type of variation it is and it looks like Amazon wants to do away with that and So you might not see this going further. This is a column that allowed you to choose how your listings, how your child appears along with your parent. One of the options was a variation. Another option used to be accessory. That was quite a different way of handling your child variation. I don't think there's any here that's relevant. So variation might not appear in this file. Then there's the variation theme, which describes how your child listings are related. And that's something that you're very familiar with. This has to do with color, size, flavor. Amazon has announced that they're going to reduce the number of options that are available here. This is called what they're calling deprecation, which is another word for saying that's being phased out. Essentially, this is where you choose how you want to relate your children. This is what tells each child what the parent SKU is. In each of these child listings, you see that the parent SKU is attached to this particular SKU right here. That's the SKU that's being listed here. Then there's the choice between the child and parent which is straightforward. So when you want to create a new variation or even just a new listing, a simple way of doing that is to copy a row, paste it and just give it a new SKU name and you will create a new SKU which is going to be the same ASIN. It will just be a new SKU for that particular ASIN and often that is useful just in and of itself. Many, many SKUs to track, for example, different batches of shipments from their supplier. So every shipment might have a different SKU in their system, especially if your products have an expiration date. You might have a reason to attach a different SKU name to that particular batch. So this will create a new SKU. What you want to do is actually create a different variation. So here you would change the values of the variation so that where it says here, flavor and size, you would want to change the name of the flavor and change the name of the size. And you may want to change other items like pricing, for example. And that will create a new variation that will become part of this group. Now, in terms of what's going on with this deprecation announcement, Amazon sent out these emails recently announcing that they will update these deprecated themes, which look like this. If you couldn't see that from that other page. I've listed all of the different options that were available for one particular listing. You see on the right, there are quite a few. Amazon has deprecated all of them, meaning they will all stop being available for use after November 30th. So they will encourage people to use flavor size or size. Interestingly, for one particular product, they even deprecated flavor. All right, so they only left these two options for that particular SKU. So guess what controls which theme is going to be available for your listing. So based on what we talked about so far, well, again, it's going to be the feed product type. And so what you'll find is that the feed product type for I mean, sorry, the variation theme that's available for one product type is going to be different than the variation theme that's available for another product type. All right. And so that's another reason for you to take a look at your product types to see what Themes are going to be available under that product type, and that's a bit of an advanced tweak, but it is something to look at down the road. I do not recommend that you make any changes right now, and I haven't been doing any changes, and I'm not going to until Accelerate and until we hear more from Amazon, because there is no particular danger at this point. Amazon said that they will keep all of your current active listings, active meaning that you've had sales in the last 12 months. It will keep them working so they will not disappear with a limitation that you won't be able to make changes to them unless you update the theme. And so for now, I would say don't be an early adapter. Don't take a risk because there might be more. There might be more changes or clarifications down the road. There are quite a few risks and implications for your listings when you make a change in your variation theme or if you make a change in your product type. It could affect your SEO factors. So you don't want to make those kinds of changes lightly, all right? So that's just to mention what's happening with deprecated themes. And if there are more questions that can follow up and then what I what I wanted to finish up with is the last, well, Another reason to use flat files that's important is to address violations. When you get an email from Amazon with something like a listing violation, you're going to need to respond to it. You're going to want to appeal to it. Doing that flat file is extremely important because one of the things that Amazon wants you to do is take responsibility for what happened and make a plan of action and say what you have done and what you will do. Very often, people jump the gun and respond to those violations with saying, oh, this wasn't my fault. This isn't actually a problem and so on. What I recommend that you do is that you back up your category listing report as this, that you address the violation by deleting the listing. You make the change to the listing. You wait 24 hours at least. And with the updated listing, you upload it. And what you're going to get, of course, you're going to get some kind of result. Your listing might be accepted or it might not be accepted by Amazon. But either way, what you're going to have as a record, you're going to have a batch ID for that update. And you can take that batch ID and you can put it in a seller support message and then the seller support can pull up that batch ID and see what's been done. And the appeals process will also see that you've made that update. So it gives you a reference to what you've done and that is critical for them to process your appeal and approve it. So that's another really major benefit of using Flat files. One maybe, just one maybe final thing, final, final thing that I will bring up that might be helpful is that when you fill out your category listing report, you are, you can, you are allowed to add attributes that aren't part of it. So here are these attributes that I'm just marking yellow. They are not part of this category listing report. I've added them. When you download your category listing report the next time, they're not going to appear here because they're not the type of attributes that normally are associated with your type of product. But if you're looking to protect your listings against hijackers, then what you want to make sure is that you fill out as many fields in your file as possible, relevant fields, and you also Fill out these fields which are especially dangerous which are because they're so general. Hijackers like to use them to fill them out inappropriately and take over listings or just shut down the listing and you'll receive a block and then you'll have to deal with that. So this can be helpful both in blocking hijackers but also increasing your contribution juice because every time you upload these files and they are accepted, Amazon gives you A bit of a score, an improvement in your contribution score. Your ability to update and modify these files is not absolute even if you have brand registry. There are other users, other sellers that have higher contribution score than you. They have higher authority if you will. And so by uploading these files often, you remind Amazon of your authority and they will up your score internally. And you'll have an easier and easier time making updates and blocking hijackers or inadvertent users who might be messing with you. So that's all that I wanted to share with you quickly. If there's questions about what we've discussed, I'm happy to take them. Speaker 2: Thank you so much, Sasha, for helping deconstruct what tends to be a complicated topic. A lot of people have questions in the comments that I see, some that you've touched on, some not so much. I know that we've had people join even throughout the session. We'll go ahead and get started with questions for whatever time permits, starting with some that were just asked on the last bit of what you presented. Starting with Paul's question, which is regarding appeals, for deleting the listing, would you then lose all the momentum and reviews? Speaker 1: So deleting listings is not dangerous. That's a misnomer. Deleting a listing is something that seller support recommends themselves. You don't lose reviews when you lose the listings. You don't lose the information. You do lose the time, right? So your listing, when you delete it, will not be live for 24 hours. And so the impact of that is going to be felt on your sales and it's going to be felt on your best BSR and your best seller rank. So that isn't something that you undertake willy-nilly and at random. You're doing this because you're addressing a problem where it's serious enough that that's something that you are willing to do. Now, certainly if your listing is already taken down by Amazon, then you're already experiencing the loss of sales and BSR. Occasionally what happens is Amazon will break your parent group apart because they will say that your children are not related to each other and that's been happening really for a long time. Your listing is live but it's not part of the parent group and so it may not have the sales that it had because it doesn't have the rank as a standalone listing. So you might be willing to do it and delete that listing and update it for 24 hours. I actually recommend if you're going through the effort of deleting it for 24 hours, I would delete it for a little bit longer. The reason that you're doing this delete is so that the change is a refresh that propagates through the Amazon system. And so you want to make sure that all of that really takes hold and not wonder did it actually take effect or not. In practice, Your deletion will most likely be processed and take effect within the first half hour, but you really want to go ahead and let those 24 hours pass so that you know that you won't have errors, you won't have conflicts. There might be ASINs in other marketplaces that are in other servers that will become in conflict if you don't let this take time. Reviews will not go away. So that doesn't happen. Reviews do separate. So if you're part of a group and you delete Delete one of the child listings. Then over a period of a day, 24 hours, Amazon will update the total number of reviews. Then when you update the listing again and it becomes part of your parent group again, they will update the reviews again. I haven't heard anybody talk about this recently as having their reviews disappear from deleting the listing for 24 hours. Speaker 2: Albert asks, is the delete step and the appeal troubleshooting process not too risky if there's a struggle with Amazon accepting a new upload to restore the listing on the existing ASIN? Speaker 1: There are trade-offs in doing that. The reason you go to that step of deleting the listing is that you've tried a couple of other things. First, you've tried to update the listing yourself in the first place, right? You tried to update it and it will not let you update because the listing is effectively already taken down. So you're not deleting a live listing at that point. When you're appealing that process, you're already up against the wall where your listing is taken down, you're not having sales, and the system will not let you make even an update to it. So this is the step that you want to do to really go to that next level where you are You want to make sure you go through that hard refresh at that point. In my experience, the deletion does not cause extra difficulties. The deletion doesn't actually make the ASIN disappear from the Amazon system. It is still there and that's why you can update it again and it'll come back. In fact, in some of the more advanced I'm part of the Helium Elite group where we meet on Fridays on networking calls and we discuss these things. I would recommend that you might even go through an extra step of not just deleting the listing but before that you might want to delete a lot of the information that you have in the listing. That is a little bit more difficult to do and a little bit more difficult to handle if you're not In the appeals process, what they really want to see is that the problem has been resolved and you've taken steps to it. Very often, what I recommend is that you upload a bare bones listing so that it has an easy time being accepted and approved. Once it's approved, you can then fill it out again with relevant information. You'll get your listing back a lot faster. You do have the opportunity of making a lot of mistakes along the way. This is not an easy process. It's not something that I recommend lightly, but I do want everybody to be aware of that if you're working with This is an agency. This is essentially the process that they're going to go through when they work with your listing. So you might not be willing to take this on yourself, but if you're at that level where you need to recover a lost listing, your hero listing that's generating sales and do it quickly, this is the level that you're going to need to be able to live with. Speaker 2: If we're not willing, somebody else is willing to do it for you. Speaker 1: Absolutely. Agencies will do it and I do this and I haven't had any problems with listings going away. I understand the difficulty, the conceptual difficulty of deleting the listing and I don't know that this process is the best way for Amazon to sort of create it for users who are not in the weeds with these flat files, but you should get to a comfort level that when you do a delete in these In the update that you're not really deleting the listing entirely from Amazon. Speaker 2: Now another question that we have is can we use flat files to merge two parents and their children into the parent with the most reviews? Speaker 1: So yes, you can. And this is a really important point to bring up. This is very, very important. And this is a takeaway that I would write down and remind yourself is that before you make changes to your parent group, before you try to add these child listings to another parent, you have to delete the existing parent. That's a must. So you must delete it and you have to give it a little bit of time to propagate through the system as well because if you don't, you're going to have a ghost in the system and cause yourself a lot of problems. So to answer the question, what you would do is you would delete this parent and let's pretend, so let's pretend that we have two groups like this, right? We have seasonals 14a, we have seasonals 15. And so here what you would do when you have two groups like this, you will create a new parent So you can create a new parent first, not a problem. Sorry, my system Excel sometimes wants to update. So you create a new parent like that. And then before assigning these child listings, you need to delete these two all parents. So we delete them. Give it some time. And then for all of these. For all of these, these are child listings now. You need to put the new parent into their parent field, wherever that was. Let me find that. All right, so here. So what you're going to want to do is do that. OK. And that that's the way you reassign your parent group. And then all of these child listings will be part of this new parent. Now, you have to be careful when you do that because when you're combining groups like this, you want to make sure that each of your child listings is unique in terms of because you're claiming here that the variation It is what it is. It's flavor and size. So they all are different flavor and size. But if you happen to have one flavor size combo here, that is the same as a flavor size here in the second group. They will be in conflict. And that's one of the most common problems that I see with variations is that in creating new listings, new child listings, users leave conflicting information within those listings. And Amazon will have a difficulty dealing with that. Now, if you're lucky, they will find the conflict and they will give you an error saying that you can't do it. But you might have a conflict not just here, but also in the description of these of these listings. All right. In any case, that's the process to go through when you want to reparent or combine the child listings of two different parents. Hopefully that made sense. I went through it quickly. Speaker 2: Thank you. Now, I know there's quite a few questions that we weren't able to get to in this hour. How is the best way for these folks to reach our special secret guests? Unknown Speaker: Thanks, Shivali. Speaker 1: I've listed my contact information, so by email is the best, should be. Should be an email. I'm happy to, for example, if you're having a difficulty with formatting this file, I'm happy to send you my macros and macros for your particular flat file. If you send me a file with just the top Top three rows, I'll send you the macros that should work with that. Don't send me the entirety of your category list and report, but just the headers and I will see if I can get you the macros that will make this easy for you to format the flat file so that you can work with. Speaker 2: Can we actually go back to the other screen just because the… This one? Yes. Yeah, because the screen was actually… it took it off for a little bit. So hopefully you guys, those of you that are watching this live and are on video format, you can see his contact info there. Speaker 1: I think by email is probably the easiest way to get my attention. Speaker 2: Thank you. We appreciate your time and knowledge and for those of you that watch, thanks for being here. Thanks for joining from all over the world. It's always incredible to see and I hope you were able to learn something. I know I certainly did. With that, we'll close out and if you need to get in touch with Sasha, you have his email on screen. Thank you so much and we'll catch you next time.