This data set represents the entire Amazon Category Tree from the root category (department) down to the end nodes. This set can help to identifying all relevant ASINs that match category criteria. Using node IDs rather than names ensures that you do not introduce errors through ambiguity of names (e.g. “t-shirts” might be men’s tshirts or women’s tshirts). A name match can be used, but beware of ambiguity as well as the fact that names can sometimes change or appear differently (e.g. “Home and Kitchen” vs “Home & Kitchen”) while IDs do not change.

When using this data set in conjunction with our sales_estimates set, the best practice is to identify all relevant category node IDs along the branch(es) of interest and then use an overlap statement in your query to capture any ASIN that meet your criteria.

SELECT 
count (distinct "asin") 
FROM "sales_estimates_weekly_v2"
where arrays_overlap (breadcrumb_path_category_ids, array[
2975359011,
17044954011,
2975360011,
6514322011,
116011806011,
6514323011,
2975361011,
6514321011,
7239525011,
6514317011,
6514318011,
6514319011
]);

Click here for table definitions

https://www.loom.com/share/01a01b7689e54fbd87e5cc5ad380fcca?sid=ab3808be-623f-4f4e-a5c4-9c63266a235b