In this post I discuss the sequencing feature of Watson Commerce Insights and include details about its use, the underlying implementation in WebSphere Commerce, and some troubleshooting tips.
Sequencing in Store Preview
The sequencing tools allow a merchandiser to specify a different sorting criteria for products under a category. The rule of thumb is that products that need to move faster should be near the top. There are two main options for sequencing: manual and intelligent, and they can be accessed from within Store Preview when the Show Controls option is active. Sequencing is only available on leaf categories.
With Manual Sequencing, the merchandiser can drag and drop products to a selected position in the category. Products can also be moved using the scroll bar to the right.
Besides the sequence, business users can select a different image to feature with the product. The feature image is shown in the category page. When the product is clicked, the shopper is redirected to the SKU page instead of the product page.
The Intelligent Sequencing feature allows to sort the products in the category based on predefined rules. It has the advantage that the merchandiser does not have to constantly be re-sorting products based on changing conditions, but it doesn't give the ability to select the exact position for each product as manual sequencing does.
For example, you could setup a rule that products whose SKUs are out of inventory are displayed last, or products with the most views are shown first. Commerce Insights feeds the Commerce server with updated stats daily so the sorting remains current.
Besides sequencing, you can pin products to the top of the category (so they show first) and also select different featured images (under the Product details tab).
The configuration of Intelligent Sequencing is done with the steps in Enabling Intelligent sequencing and featured image features for Commerce Insights, that are followed during WCI on-boarding. The setup works as follows:
Download of sequencing files:
- Nightly, after processing the analytics data, WCI makes available the intelligent sequencing files under the download FTP account (inbox/metrics/external_*_kpi.txt). These files contain normalized metrics for all the products in the catalog. The metrics include items sold, product views, items added, items sold and more. The file containing inventory metrics is refreshed after inventory uploads.
- The TriggerMetricFileDownload job is scheduled to run hourly in the WebSphere Commerce server. It makes a REST call (external_file/fetch) to Search to perform the download.
- The Search server connects to WCI with the download account stored in wc-component.xml and saves the sequencing files with the index.
- The new files are registered in confFiles.xml and are replicated with the index.
Use of the Intelligent Sequencing files during category display:
- If Intelligent Sequencing is enabled for the category, the Search server calculates the sequence using the rules and the normalized values stored in the sequencing files.
- Pinned items are moved to the front.
Precedence of the different sequencing options
The precedence is set using the following rules:
- When Watson Commerce Insights is not used, the Management Center sequence applies (CATGPENREL.SEQUENCE).
- When Manual Sequence is applied, all the loaded products (page size is 50) are assigned a sequence up to the first 500 products. These products are shows first. The remaining products are shown after using their Management Center sequencing.
If products are added to the category after Manual Sequencing was applied, these are listed at the end using the Management Center sequencing.
- If Manual Sequencing is saved on a category previously configured with Intelligent Sequencing, the Manual Sequencing arrangement is used.
- If Intelligent Sequencing is saved on a category previously configured with Manual Sequencing, the Manual Sequencing arrangement is lost.
Storage of sequence and featured image information in WebSphere Commerce
When a featured image is selected, or sequencing is applied to a category, the information is saved on the database and in sequencing files within the Search server:
Manual and Intelligent Sequencing configurations, as well as feature images are stored in the following database tables:
- Feature Image, Manual Sequencing and pinned Intelligent Sequencing Items:
The following query joins the catalog tables to list the sequencing for a particular category and store:
SELECT F.CEDISPCTX_ID, F.CEDISPCONF_ID, G.CATGROUP_ID, G.IDENTIFIER, F.CATENTRY_ID, C.PARTNUMBER, D.NAME, X.DISPLAYMODE, F.PROPERTY, F.VALUE
FROM CEDISPCONF F,
WHERE F.CEDISPCTX_ID = X.CEDISPCTX_ID
AND C.CATENTRY_ID = F.CATENTRY_ID
AND C.CATENTRY_ID = D.CATENTRY_ID
AND G.CATGROUP_ID = X.CATGROUP_ID
AND X.STOREENT_ID = 10001
AND G.IDENTIFIER = 'Computers Tablets'
AND D.LANGUAGE_ID = -1
ORDER BY F.CEDISPCTX_ID ASC, F.PROPERTY, F.VALUE DESC
- Intelligent Sequencing configuration:
The following sample query list all the intelligent sequencing configurations (excluding pinned products and feature images):
SELECT F.CEDYNDISPCTX_ID, F.CEDYNDISPCONF_ID, X.STOREENT_ID, X.CATGROUP_ID, G.IDENTIFIER, F.ATTRIBUTE, F.WEIGHT, F.SORT_PREF
FROM CEDYNDISPCONF F,
WHERE F.CEDYNDISPCTX_ID = X.CEDYNDISPCTX_ID
AND X.CATGROUP_ID = G.CATGROUP_ID
ORDER BY F.CEDYNDISPCTX_ID
Sequencing and featured image files
Sequencing and featured image information is saved in external files with the Solr index. By keeping the data external to the index, changes are reflected immediately without having to re-index.
The files are stored under the CatalogEntry data directory:
$ ls -l
drwxr-xr-x 2 wasuser wasgroup 4096 Sep 14 10:24 index
-rw-r--r-- 1 wasuser wasgroup 83 Sep 20 21:22 external_3074457345616676673_715838084_image.txt
-rw-r--r-- 1 wasuser wasgroup 950 Sep 20 21:24 external_3074457345616676673_715838084_reseq.txt
-rw-r--r-- 1 wasuser wasgroup 137 Sep 07 13:43 external_3074457345616676696_715838084_reseq.txt
-rw-r--r-- 1 wasuser wasgroup 89 Sep 26 17:00 external_items_abandoned_rate_0_715838084_kpi.txt
-rw-r--r-- 1 wasuser wasgroup 74 Sep 26 17:00 external_items_abandoned_rate_1_715838084_kpi.txt
-rw-r--r-- 1 wasuser wasgroup 92 Sep 26 17:00 external_items_abandoned_rate_2_715838084_kpi.txt
-rw-r--r-- 1 wasuser wasgroup 112 Sep 26 17:00 ...
Manual Sequencing and pinned Intelligent Sequencing products:
Intelligent Sequencing files:
_1_ Last Week
_2_ Last Month
Recreating sequencing files from the database
If the sequencing files are lost for any reason, it is possible to re-create them from the database tables:
- Manual Sequencing and Featured Image
Use the ProductDisplayPropertiesUpdate?storeId=<storeId>&refresh=true scheduler job.
See this page for more details: Troubleshooting customized product display sequences.
- Intelligent Sequencing
The TriggerMetricFileDownload job should be configured hourly to download the latest intelligent sequencing files. It's also possibly to run it manually to have the files refreshed immediately.
Following are some error scenarios you might encounter:
Sequencing files are not created in the master server
Solr slave servers should not be used in the Authoring environment. This will ensure the sequencing files are created in the master server from where they can be replicated.
Sequencing files are not getting replicated
Ensure the confFiles.xml file contains the list of sequencing files to be replicated. Also validate solrconfig.xml to ensure confFiles.xml is included in replication.
The following command can be used to ensure the sequencing files are included: (look for confFiles)
Sequencing changes are not reflected in the live store
Invoke indexprop with the parameters that do cache invalidation, to ensure that the core is reloaded and the new sequencing files are picked up.
./indexprop.sh -solrHome solrHome -masterCatalogId masterCatalogId -instance instance_name -dbuser dbuser -dbuserpwd dbuserpwd indexprop utility
You might also need to clear Dynacache.
Category Pages fail to render
Categories where WCI sequencing was applied fail to render in the store. The following errors are seen in the Search server's SystemOut logs:
Caused by: org.apache.solr.common.SolrException: sort param field can't be found: 3074457345616676710_715838084_reseq
Caused by: org.apache.solr.common.SolrException: undefined field: "3074457345616676673_715838084_image"
Ensure to have followed the steps under: Enabling Intelligent sequencing and featured image features for Commerce Insights and that the sequencing changes have been replicated to all the Search servers. Validate that the schema.xml file is updated on all the machines (point 5 d in the doc).
Products in the wrong order under Intelligent Sequencing
If after previewing or applying an intelligent sequencing rule products are listed in the wrong order, there could be an issue with the intelligent sequencing setup.
In the next example, products are listed by Product Views, from Yesterday and High to low. You can see the sorting is incorrect as the Kitchen Tablet (130 views) should be listed before the Multimedia Tablet with 103 views. Product Views and other metric data is retrieved from the Commerce Insights servers and was populated from your Analytics provider. The sorting is done based on the normalized metrics downloaded into the Search Server from the WCI servers. If the version of the Intelligent Sequencing files in the Search servers is outdated, the sorting wont match the metrics.
The ranking flags match the analytics data. If you use them to verify the sorting, ensure the metric and the period (Yesterday, last month, last year) match the intelligent sequencing criteria.
Follow these steps to verify your configuration:
- Confirm the intelligent sequencing files exist in the Search server:
ls -l /usr/WebSphere/CommerceServer80/instances/demo/search/solr/home/MC_<masterCatalogId>/<lang>/CatalogEntry/data/external*kpi.txt
You might need to verify the master and replicator/slaves in case the files were not replicated correctly.
- Compare the local files (including date) with the files under the WCI download FTP account.
# sftp -P 29001 firstname.lastname@example.org
Connected to cin-bac.prod.coc.ibmcloud.com.
sftp> cd inbox/metrics
sftp> ls -l
-r-------- 1 mw100000 users 89 Sep 26 06:45 external_items_abandoned_rate_0_715838084_kpi.txt
-r-------- 1 mw100000 users 74 Sep 24 06:31 external_items_abandoned_rate_1_715838084_kpi.txt
-r-------- 1 mw100000 users 92 Sep 21 06:30 external_items_abandoned_rate_2_715838084_kpi.txt
-r-------- 1 mw100000 users 112 Sep 26 06:45 external_items_added_0_715838084_kpi.txt
-r-------- 1 mw100000 users 93 Sep 24 06:31 ...
Note: Only the _0_ period (yesterday) is updated daily. The file for week (_1_) and month (_2_) can have earlier time stamps.
If the files don't match, this confirms the theory that the TriggerMetricFileDownload job is not running.
- Validate the following steps: Enabling Intelligent sequencing and featured image features for Commerce Insights
The TriggerMetricFileDownload job should be scheduled to run hourly. Validate that the job is running without errors. Verify both, the WebSphere Commerce and Search server logs. Also ensure wc-component.xml is updated correctly with the FTP download user and password, and that the FTP server access has been whitelisted in your network configuration.
If needed, enable this trace on both the Search and Commerce servers and rerun the TriggerMetricFileDownload job: