Azure Recommendations API requires a product catalog snapshot and the transactions history to train a model. This blog post will show you how you can export a Sitecore Commerce reference storefront catalog using PowerShell Extensions.
Let’s start small. At a minimum, the Recommendations API needs your SKU #s, product name, and the category name:
AAA04294,Office Language Pack Online DwnLd,Office AAA04303,Minecraft Download Game,Games C9F00168,Kiruna Flip Cover,Accessories
The following script will give us the data we need:
$catalog = '/sitecore/Commerce/Catalog Management/Catalogs/Adventure Works Catalog'
The result looks like this:
Name __Display name Category ---- -------------- -------- 22565422120 Gift Card Departments AW007-08 Black Diamond Quicksilver II Carabiners AW009-08 Black Diamond Quicksilver II SaleItems AW013-08 Petzl Spirit Adventure Works Catalog ...
Features need to be exported in a special format. Different products in a given catalog may have different features and even have different number of them. Azure solves this by requiring features as a comma separated list of name value pairs:
AAA04294,Office Language Pack Online DwnLd,Office,, softwaretype=productivity BAB04303,Minecraft DwnLd,Games,, softwaretype=gaming, compatibility=iOS, agegroup=all C9F00168,Kiruna Flip Cover,Accessories,, compatibility=lumia, hardwaretype=mobile
The following addition to the script will add features to the list:
PSObject is a dynamic type that you can modify on the fly. First, I extracted a collection of features into a new
Features property. Then I applied features to become new properties on the product object. CSV export will be able to pick it up transparently. I hope.
It should now be easy to export the list as CSV. There’s a caveat though.
Export-CSVwill happily export the list for you but will normalize every record to the common set of fields.
You won’t see the features in the list. Here’s a trick to get every product in the export have its own features:
# ... (see above)
Instead of piping the entire set to the
ConvertTo-CSV, I basically processed the list one by one in the
foreach loop. I also removed the type info and the CSV headers. Azure doesn’t need labels anyway. Works like a charm!
"AW007-08","Black Diamond Quicksilver II","Carabiners","Straight","BasePrice=10.0000" "AW009-08","Black Diamond Quicksilver II","SaleItems","Straight" "AW013-08","Petzl Spirit","Adventure Works Catalog","Straight" "AW014-08","Petzl Spirit","Carabiners","Straight","BasePrice=14.0000" "AW029-03","Women's woven tee","Shirts","Short-sleeve, breathable henley, 100% cotton knit","BasePrice=35.0000","Brand=Litware"
There’s one more thing that I needed to do for Azure Recommendations API to absorb the catalog. As you could tell, the catalog format is not exactly CSV. Every line can have different number of fields basically. Neither does Azure backend use CSV parsing to read it.
The double quotes in the export above were taken literally. Azure would think that the SKU # is
"AW007-08", for example. And then the commas in the descriptions where messing up the parsing as well. My next post will be about the Recommendations API itself and I will write more about it, but here’s the final version that produces a clean catalog export ready to go:
Got to love PowerShell. Cheers!