Get a list of ABC Items

 

One of the very first steps in setting up your new website is developing enriched, web-ready data for your most important items. It can be overwhelming when you consider doing so for your entire catalog, so we recommend using the "ABC" method for breaking down your list of items into manageable groups that deliver the most effective use of your time.

"A" items should be your first priority, then "B", then "C". The number of items on each of these lists is your choice. We recommend creating an "A" list of 10000 items or 20% of your website-enabled items.

NOTE - this is the method used in the PIM-Enterprise when using the "Ranked Items" tool.

Using SQL to collect your list of "A" items

Here's a SQL query that we recommend for collecting your list of top-selling items. You'll get the most effect by focusing your time on your most popular items, meaning the items that are sold the most often, rather than the items that sell the most in terms of quantity.

P21 ABC Query 
select top 10000 
 item_id
 ,item_desc
 ,count(hdr.invoice_no) as invoice_hits
 ,sum(qty_shipped) as total_shipped
FROM [invoice_line] line with (nolock)
 left join [invoice_hdr] hdr with (nolock)
 on line.invoice_no=hdr.invoice_no
 where qty_shipped>0
 and tax_item='n'
group by item_id, item_desc
 having COUNT(hdr.invoice_no)>10
 order by COUNT(hdr.invoice_no) desc

Run that query on your P21 SQL Database. Use the results as your list of "A" items. 

TrulinX ABC Query 
select top 10000 
 line.item
 ,line.Description
 ,count(hdr.invoiceNumber) as invoice_hits
 ,sum(Quantity) as total_shipped
FROM [tblarInvoiceLine] line with (nolock)
 left join [tblarInvoice] hdr with (nolock)
 on line.invoiceNumber=hdr.invoiceNumber
 where Quantity>0
group by line.item, line.Description
 having COUNT(hdr.invoiceNumber)>10
 order by COUNT(hdr.invoiceNumber) desc

Run that query on your TrulinX SQL Database. Use the results as your list of "A" items. 

The above query is a good starting point, and fits item catalogs universally. However, feel free to update this query to better fit your item catalog.

As the queries are written above, you'll see a return of your Top 10000 items ordered by the number of invoices that each item appears on. The "total_shipped" column shows the total number of units shipped for that item, across all invoices. We do recommend focusing your "A" item list based on the "invoice_hits" column, because it indicates how many times any customer has ordered that item, rather than the quantity of the item that was ordered. This is a better reflection of item popularity, which is important in WebAlliance.


You can change "10" in the "having COUNT(hdr.invoice_no)>10" section to a lower number to return items that sell less frequently, or to a higher number to tighten that restriction.


Of course, if you don't have 10000 items that fit the bill, the returned list will return fewer than 10000 items.