Introduction

It is important to check the mapping of the product before analysing the product data. The overall product mapping should be 90% or more, all items are not taken into consideration if it is lower than 90% and the analysis becomes incorrect. Regular checks are required to understand the mapping percentages which can become quite monotonous. 

This notebook automates the process of checking the inventory mapping. A monthly job is scheduled to send out an email containing the data to relevant recipients every month on a particular date. 

Input parameters

You need to provide the following details before running the Notebook.

Parameter NameDescription
Org_id

Enter the org ID of the brand.

Number of Years

Enter the minimum years for the inventory data to be mapped. The end date is the previous month’s last date from the current date when the notebook is run. For example, if the notebook is run on May 10, 2021, and the number of years is configured as 2, then the prescribed duration is Jan 1, 2019, to April 30, 2021. 

The start date is taken at the beginning of the year based on the difference in the number of years from the current date so that any month is not missed out while doing the yearly comparison.

Line Items ItemcodeEnter the line items to join the product table.
Product Database

Enter the name of the database where the product table is stored. For example- read_api_100, ds_capillary.

Product Table

Enter the product table name in the product database.

Product Itemcode

Enter the column name which will join with bill_lineitems on item_code.

Product CategorySelect the main product category column in the product table to see the mapping on a product category level. Mapping of items is seen based on this column. Preferably, choose a column with fewer values so that it is easier to read in the email.
Email List

Enter the email ids of the recipients of this inventory mapping update. The email ids must be separated by commas and without any space in between. For example- abc@gmail.com,xyz@gmail.com.


The final output is sent to the mentioned recipients via email. You can download the list of unmapped item codes from cmd 14, and if the output has more than 100 rows then select Download full results which will cause the cmd to rerun and automatically download the results in your system.


Inventory Mapping Notebook Components

The following is the flow of Automated Inventory Mapping.

  • Product category level mapping: Distribution of main product category values is displayed in terms of percentage of items (mapping), percentage of sales contribution and percentage of quantity sold. If a row has products missing in the product category, MISSING is shown for that product. 
  • YoY Product Mapping: Year-wise mapping percentage is displayed with the most recent year on top of the table.
    MAPPED_ITEMS corresponds to the item codes present in both line items and product tables. MISSING_ITEMS corresponds to the item codes which are only present inline items table and are not mapped to the product table
  • MoM Product Mapping: Month-wise mapping percentage is displayed with the latest month on top of the table with the data of the most recent year. Only MAPPED_ITEMS are displayed in this table. 

Notebook Links

Open your cluster-specific link provided for the Notebook.