The purpose of SKU Quality Control application is to investigate the atypical purchases that are recorded in one audit period. The process of finding atypical purchases reaches the SKU level per store of the Emrc Retail Audit sample.
The distance measure which is used to implement the above, based on Shanon’s theory, is the following:
A typical example that describes this property is the following: We have 2 stores where the purchases for the same SKU in 2 consecutive periods are
We immediately observe the doubling of the purchases in the period t + 1, that is, we have a 100% increase in both stores. Here, the increase in store A should not worry us because it is reasonable and expected to buy an additional SKU and we must pay attention to store B, something that the classic distance measures do not indicate. However, by using the above type of distance, we obtain
This application implements the following 2 procedures.
- The application builds for each SKU, per audit period (monthly, bi-monthly, etc.) and per cluster,
the distribution of the distance measure
. Each such distribution is built with the past data and updated every audit period with the new data. From the study of these distributions the percentiles 90%, 95% and 99% are calculated.
- For all stores "i" of the audit period, the corresponding
is calculated per SKU and compared with the 3 critical percentiles of the corresponding
The application performs analysis on user input files and results are stored in an sqlite3 database. The user interacts with the application through a GUI and he can import files for analysis, export files, delete database contents and read the application documentation. Application info is displayed in the console.
Below is the application main interface. The user can navigate from the top menu and select an action.
The various messages produced by user actions are displayed on the console.
- The console can be cleared by selecting Console → Clear from the top level dropdown menu or by clicking on the console, pressing Ctrl+A and then Delete.
- The contents of the console can be copied by clicking on the console, pressing Ctrl+A, then Ctrl+C and then Ctrl+V to paste them anywhere you like.
The application expects actions in the following logical order:
- Import Clusters
- Import SKUS for analysis
- Import Outlets to perform analysis based on the previous step
The application creates a database file (db.sqlite3) in the same folder of the executable, where the imported items are stored. If this file is deleted, it will be automatically created again the next time the application is launched and will have to be populated again with entries from new imports.
- Select About → Database Current Status to print counts for the database tables on the console.
- Select About → Importing to print information about importing files (see below).
- Select About → SKU Quality Control Application to print quick info about the app.
- Select About → Read the Docs to launch the documentation file.
The application accepts as input .csv or .xlsx/.xls files. The .csv files are imported much faster. In the case of .xlsx/.xls imports, the application will try to convert the file to .csv before parsing it.
The imported file must follow the rules the user can see by selecting About → Importing → Clusters from the top level dropdown menu.
Select Import → Clusters from the top level dropdown and choose a cluster file to import.
The appropriate messages will be displayed on the console in the case of success or failure accord- ingly.
Note that:
- The imported file must comply with the rules mentioned above,
- Every time a new file is submitted, all existing clusters will be deleted and replaced by the new ones.
The imported file must follow the rules the user can see by selecting About → Importing → SKUs from the top level dropdown menu
Select Import → SKUs → and the period type for the file you want to import from the top level dropdown.
A popup will appear with the period type that was selected from the menu. Make sure to select the file of the correct period type, or an error message will appear while parsing it.
The appropriate success/error messages will appear while parsing the file. If parsing is successful, a progress window will appear displaying the progress of the SKU analysis. Wait until the process is finished.
Note that:
- The imported file must comply with the rules mentioned above,
- Every time a new file is submitted, the entries will be appended to the database.
- If any SKU from the selected period type already exists in the database, a warning message will appear and the database will not be updated. All SKUs of a specific period type must update the database from a single file.
- Clusters must be up to date before importing SKUs.
The imported file must follow the rules the user can see by selecting About → Importing → Outlets from the top level dropdown menu.
Select Import → Outlets → and the period type for the file you want to import from the top level dropdown.
the relevant pop up for selecting outlet file according to the selected period type, the success/error messages and the progress window will appear just as in the SKUs section. A report will also appear on the console about the atypical and missing entries found per outlet:
After a successful file import, two database tables will be populated, a table for the atypical entries found and a table for the missing ones.
Note that:
- The imported file must comply with the rules mentioned above,
- Every time a new file is submitted, all existing atypicals and missing entries will be deleted and replaced by the new ones.
- Clusters must be up to date before importing Outlets.
Select Export → Clusters from the top level dropdown and choose a cluster file name to export.
an excel file will be created with the columns
- id_outlet: the id of the outlet
- mountly, food, non_food: the cluster number for the corresponding period type.
Select Export → SKUs → SKUs or SKU Analysis from the top level dropdown and choose a file name to export
- SKUs will export all the SKUs that were imported in the database. Columns:
- id_product, id_brand, id_sku, period_type, sku_name: As in the imported files.
- sku_file_name: The name of the file rom where it was imported
- imported_date: The date where it was imported -SKU Analysis will export a joined table with all the SKUs that were imported in the database and the statistics from the analysis, where present. Columns:
- id_product, id_brand, id_sku, period_type, sku_name: As above.
- cluster: The cluster from the corresponding outlet and period type
- count, mean_diff, perc90_diff, perc95_diff, perc99_diff: The statistics from the analysis performed
Select Export → Outlets → Missing or Atypicals from the top level dropdown and choose a file name to export.
- Missing will export the missing entries from the database based on the outlet file on which the outlet analysis was performed. Columns:
- id_outlet, id_product, id_brand, id_sku, period_type, cluster: As above.
- lm_purch, purch: The corresponding last month purchases and the current purshases respectively.
- Atypicals will export the atypical entries from the database based on the outlet file on which the outlet analysis was performed. Columns:
- id_product, id_brand, id_sku, period_type, cluster, sku_name, lm_purch, purch: As above.
- stars:
- *** → D > perc99_diff,
- ** → D > perc95_diff and <= perc99_diff,
- * → D > perc90_diff and <= perc95_diff,
- proposed_purch_1, proposed_purch_2: Two proposed values for purchases produced by the analysis based on Newton’s method.
Select Delete → Clusters or SKUs from the top level dropdown.
- Choosing Clusters will delete the Clusters database table contents which will have to be repopulated by a new import before performing SKU or Outlet analysis.
- Choosing SKUs will delete all the other database tables’ contents which will have to be repopulated by a new import before performing SKU or Outlet analysis.
The application is made with Python. GUI is made with PySimpleGUI, executable file with PyInstaller and code is optimized with LineProfiler. Other packages used are sqlite3, math, numpy, pandas, subprocess, base64, tempfile, webbrowser