on 03-05-2018 10:46 AM
How can I produce a list of all Ebay selling categories in which I have an item listed?
I am not talking about my own store's categories. And I don't want a full list of Ebay categories - I just want the list of categories in which I have something listed.
Bonus if the list tells me how many items I have listed in each category too.
Solved! Go to Solution.
03-05-2018 11:19 AM - edited 03-05-2018 11:20 AM
on 03-05-2018 11:48 AM
Thank you. That's a part solution. For example, I see the category "Home & Garden" showing 600+ items. Beneath that, some of the subcategories are listed.
However, I also see Collectables with only 14 items listed. There are no subcategories beneath that. However when I click on "Collectables" I see 8 subcategories, one of which has 0 items listed.
Hence the initial list is not comprehensive. Even if I expand and collate all the lists I then still need to filter out categories having 0 listings.
For another example, when I view one of our products, it shows the following category breadcrumbs at top of the listing:
Home & Garden > Tools > Power Tools > Other Power Tools
Yet when I look at the listing you suggested, and view "Home & Garden", I cannot see "Tools" or "Power Tools" or "Other Power Tools" listed under that category - even after I click "more" to show all items under "Home & Garden".
What I would like is a list in which one element is:
Home & Garden > Tools > Power Tools > Other Power Tools
... and likewise for every other category in which I have something listed.
on 03-05-2018 12:18 PM
The collectables has 7 sub categories with collectables just being repeated and shows 0 as you are now viewing subs.
I have come across the other you mention that when in listing it shows where it is categorized but cannot find that category in the index.
Unfortunately that's an eBay programming fault.
Luckily most buyers will search product and use the store filters if interested in multiple items from same seller.
on 03-09-2018 12:40 PM
That's not viable for large numbers of SKUs.
The information is obviously already within the Ebay system - it would be nice to get a report on it.
on 03-09-2018 07:03 PM
Works for me with over 2000 ISBNs.
But I personally list my items, take my own pics...
03-13-2018 12:36 AM - edited 03-13-2018 12:37 AM
If you just want the data summary to look at, I don't think it's available apart from kope's suggested method.
If you are ok with extracting and sorting the data yourself, then you can download active listings to a CSV file, or files (AFAIK you have to edit listings to access the option to export them to a data file, which means you may have to do it in batches), open the file(s) in Excel or similar, combine them into a single spreadsheet for (slightly more) ease, then you can run a count for how many time a particular value (keyword) occurs (the full categories are listed, so you can check for main and / or sub categories by specific keywords).
on 03-13-2018 10:51 AM
One additional detail I've found - you can specify whether your store displays its own store categories, or the ebay listing categories.
This seems to be comprehensive - in that it does account for all items to all levels in the hierarchy, but it will only display one node expanded at a time.
on 03-13-2018 11:12 AM
Have also found in the File Management Centre, you can initiate a Download Request - in the dropdown box titled "Listings", choose "Active".
The resulting CSV has a field named Category, giving a number.
The accepted answer at the link below will allow me to correlate these numbers (IDs) with the category nodes:
03-13-2018 12:23 PM - edited 03-13-2018 12:25 PM
That is the method I use and it works like a charm.
If you make a separate worksheet with 2 columns of category number and category name, you can then use VLOOKUP to insert the actual category name into an adjunct column in your downloaded CSV (once it's been coverted to XLSX or XLS format that is)
The category numbers/names are also available as a separate download so no biggie.
on 03-13-2018 01:20 PM
Yep - thats the plan exactly. Can do the maths from there - sumif, or pivot table, to get number of items listed in each category. The only caveat is that secondary categories are not accounted for. However, for now, primary categories will get me going.
So the full solution (for primary categories) is:
First, get to the File Management Centre (Hover over My Ebay (at top right) > Selling/Sold > Selling Tools panel > File Management Centre)
Next, get the full list of categories supported by Ebay (Left menu > Overview section > right-click "Instructions and Resources" > Open link in new tab > Selling Manager Pro File Management Centre User Guide (pdf) > scroll down to the contents > find the "Template field definitions" section, then the "Design and display fields" subsection, then click on "*Category" (which is on page 46 at the moment) > in the first paragraph is a link for "Category IDs" - click that
This will download a CSV file containing all Ebay categories (9,000+ at the moment).
Next, you want a list of your active listings:
Back in the File Management Centre > Left Menu > Download section > Download Files > Listings and Records dropdown > choose "Active" > Email Address field - enter your email address > Save button
This will email you a CSV list of all your active listings.
If you open your CSV list of active listings in Excel, then you should see data filling columns up to column W (at time of writing).
Also open your list of categories (the first CSV) - that will have 2 columns of data. The category ID in column A and the Category path in column B.
Select these 2 columns (Click on column A header and drag to column B before releasing the mouse button) > Copy (eg. CTRL+C) > go to your workbook for the list of active listings (the other CSV) > click in cell AA1 (not A1) > Paste (CTRL+V)
Select cells AA1 and AB1 > right-click > Delete... > Shift cells up (this gets rid of the column headers).
Now select columns AA1 and AB1 > Alt+D, then S on your keyboard (to access the sort dialogue) > Sort by column AA from Smallest to Largest > OK button (because we will use the VLOOKUP function which requires the index - our category IDs - to be in alphabetical order).
In cell X2, enter the following formula: =VLOOKUP(P2,AA:AB,2)
This assumes column P has the heading "Category Number". If it does not, then change P2 to whichever column has the category numbers.
Now copy cell X2 (Select the cell, then CTRL+C) > CTRL+End on your keyboard (this takes you to the last cell being used in this sheet) > use your Left arrow on the keyboard to move across to Column X > CTRL+SHIFT+Up to select all cells from here back up to X2 > Paste (CTRL+V)
Column X now displays the full path to the (primary) category for each of your listings.
Now let's count them.
In cell Y2, enter the following formula: =COUNTIF(X:X,X2)
Now similarly copy this formula down the column (Copy cell Y2 > CTRL+End > Left arrow back to column Y > CTRL+Shift+Up > Paste)
Column Y now shows the total number of listings in the category that is displayed on that row. In my case I have fewer than 2,000 listings, but there are over 9,000 categories in columns AA and AB - hence I have about 7,000 rows (under my listings) showing "#N/A" for the category.
Now to produce a simple list of the categories you are using:
Go to the last row of your listings and select columns X and Y back up to the top > Insert menu > Pivot Table > OK.
This creates a new worksheet.
In the PivotTable Fields menu area, drag the value from the main panel, onto the "Rows" panel. Drag it again from the main panel onto the "Values" panel.
You should now have an alphabetised list of the categories in which you have active listings, together with a count of the number of listings in each category.