TOPICS ON THIS PAGE


Product Analysis - Basic Analysis

Understanding the variation in product purchases by different customers is important for creating product-specific campaigns. 

Some important questions which come up before doing a Product Analysis are: 

  1. Which products are performing the best?
  2. Is there a consistency in performing the same throughout?  
  3. Do customers prefer to buy low or high-cost products more?
  4. What products do customers generally buy together?

Our aim is to address all the above questions and come up with useful insights from the data.

The Product Analysis (PA) notebook is used to understand how well the brand’s products are doing, their variability and what can be done to improve the KPIs of the products. 

This notebook must be run one command at a time (since changes are required in the commands in between).

Components of Notebook

This notebook is the first part of the entire Product Analysis. The following are the sections covered in this notebook.

  1. Overall look of the data: Product categories performance in terms of basic KPIs, Trend of product categories on a monthly and quarterly basis based on Quantity and Sales.
  2. Top N and Bottom M Item SKUs comparison: Percentile based division of item SKUs decided based on the quantity sold. 
  3. Price Point Segments: Dividing items into 4 segments based on Average Price Point percentile values (25 percentile each) and analyzing contribution and preferences.
  4. Basket Items Analysis: Seeing how the number of products/ type of products differs in different basket sizes.

India Cluster | SEA Cluster | EMEA Cluster

Inputs to be provided

  • Org_id of the brand
  • Start Date and End Date - Duration for which the notebook is to be run. Format: yyyy-mm-dd. (example: 2020-01-16)
  • Product Database - Name of the database where the Product table is stored (examples: read_api_100, ds_capillary)
  • Product Table - Name of the Product table in the Product database
  • Product Itemcode - Column name which would be joined with bill_lineitems on item_code
  • Item Percentile Value - For the Top N vs Bottom M items percentile comparison. Put the value for N here. (example: N = 20, comparison is done between Top 20 vs Bottom 80 percentile items)

For each section, a graph has been created to represent how the data can look like finally in the deck. These graphs will have to be made in Excel only by downloading the data outputs.

Selecting the required product data for the different sections is something that cannot be automated. So, the user will have to edit the SQL commands according to their product category requirements. 

Please read all the comments carefully inside the command where there are SQL codes (SQL code is written inside the triple quotes) and follow accordingly. The important points are also mentioned below:

  • Cmd 6 - In line 6, enter the product categories (as many as required). You’ll also need to edit the group by and order by commands depending on the number of categories added. Examples: If 2 categories are added then group by and order by till 5 (1,2,3,4,5). If 4 categories are added then group by and order by till 8 (1,2,3,4,5,6,7).
    Inside the where condition from line 16, add any extra condition which is required pertaining to examples: an outlier condition, deep dive into a particular product category.
  • Cmd 16 - Same for both SQL codes in the cmd.
    Inside the where condition from line 16, add any extra condition which is required pertaining to examples: an outlier condition, deep dive into a particular product category.
    Similarly, follow the same for the second SQL code.
  • Cmd 23 - Use only one product category at a time. On line 7 enter just one product category and in line 16 the required conditions.

Examples: 

  1. Overall Footwear
    - line 7: Product as Category_1
    - line 16: and Product = ‘Footwear’
  2. Ladies Footwear
    - line 7: Product as Category_1
    - line 16: and Product = ‘Footwear’ and Gender = ‘LT’  

Take different subcategories separately and run the cmds 23 to 25 each time.

  • Cmd 29 - According to the type of the brand, the case when statement for making the basket items buckets will have to be changed. Some idea of the brand’s average items bought, max items bought in a transaction etc. will be needed.
    Examples:
    i) Footwear/Clothing brand could be -
    (case when cnt = 1 then "1 item" when cnt = 2 then "2 items" when cnt = 3 then "3 items" when cnt = 4 then "4 items" when cnt = 5 then "5 items" else ">=6 items" end) as Tag
    ii) Supermarket brand could be
    (case when cnt = 1 then "1 item" when cnt in (2,3,4) then "2to4  items" when cnt in (5,6,7) then "5to7 items" when cnt in (8,9,10,11) then "8to11 items" when cnt in (12,13,14,15) then "12to15 items" else ">15 items" end) as Tag

The number of buckets can be increased or decreased but ideally, it should be between 5 to 8 buckets.

  • Cmd 32 - Put the same case when a statement on line 16 is done in Cmd 29.
    In line 23, edit the name of the product category to use inside the brackets of collect_set() examples: collect_set(Product), collect_set(Gender)
    In line 32 add any extra condition needed. Don’t put any subcategory conditions here.

    Edit line 6 according to subcategories inside the Main category chosen. See the example given in the notebook In line 6. Different buckets will need to be created like for example -  Footwear, Accessories, Socks, Footwear+Socks, Footwear+Accessories, Footwear+Accessories+Socks (All), Others. The part in inverted quotes will need to be edited inside array_contains(). size(Materials) is just the number of subcategories being taken. 

If you’re adding product categories in the SQL codes, please give similar aliases (example: Category1, Category2, Category5 etc.). Don’t change alias names anywhere.


Product Analysis - Price Sensitivity & Core/Non-Core Segments

Understanding the variation in product purchases by different customers is important for creating product-specific campaigns. 

This part of the Product Analysis aims to cover two main parts: 

  • Distribution of products into Premium and Non-Premium categories based on sales and quantity sold i.e High in-demand, Moderate popularity, Least bought etc.  
  • Distribution of products based on price to understand the price variation of products. 


Also, we will see the distribution of RFM segments or Advanced customer segments with the above mentioned 2 segments to get an idea of which segment prefers what price range of products, and what segments need to be pushed to purchase from a higher product segment.

The Product Analysis (PA) notebook is used to understand how well the brand’s products are doing, their variability and what can be done to improve the KPIs of the products.

Methodology

Identifying Premium Categories or products:

Price sensitivity




Sample engagement strategy or outcome



Components of Notebook

Notebook links: India Cluster | APAC Cluster | EMEA Cluster

This notebook is the second part of the entire Product Analysis. The sections covered in this notebook are:

  1. RF Segment-wise Best Products - Segmenting products into 3 categories based on their Sales and Quantity contribution
  2. Price Sensitivity - Understanding the price variation of products
  3. Distribution of KPIs and Customers across Price Sensitivity segments and Premium/Non-Premium segments
  4. Price Sensitivity Segments with External tables (RFM, Advanced Segments etc.)

Understanding the output

  1. Core/Non-Core Segments - Cmds 12 and 13 have the final outputs for product-wise and category-wise KPIs
  2. Price Sensitivity - Cmds 19 and 20 have the final outputs for product-wise and category-wise KPIs
  3. Cmd 23 shows the combination of KPIs for the 2 Segments, and cmd 27 shows the customer counts across the 2 segments
  4. Cmd 32 has output of Customer counts for Price Sensitivity segment with an external table (examples: RFM, Advanced Segments etc.)

Inputs to be provided

  • Org_id of the brand
  • Start Date and End Date - Duration for which the notebook is to be run. Format: yyyy-mm-dd. (example: 2020-01-16)
  • Product Database - Name of the database where the Product table is stored (examples: read_api_100, ds_capillary)
  • Product Table - Name of the Product table in the Product database
  • Product Itemcode - Column name which would be joined with bill_lineitems on item_code
  • Product Category - Column name from which segments are to be defined. Choose the main product category
  • MRP column in the product table

Using external tables can be skipped, but it would be good to show the distribution of customers with an RFM table at least.

  1. External Database - Name of the database where the External table is stored. The External table can be the RFM table, Customer Advanced Segments table etc.
  2. External Table - Name of the external table in the External database
  3. External user_id -  Column name which would be joined with Price Sensitivity Segments on User Id.
  4. External Segment - Column name in the external table with which we will see the final output with the Price Sensitivity Segments. (examples of values inside column: RFM  Tag (Base_Repeater, Mid_Repeater etc.), Advanced Segment Tag (Crown, Premium etc.) 

The graphs shown in cmd 8 and cmd 21 are just for reference. Similar graphs can be made in excel using the data.

Similar to the Part 1 Product Analysis notebook, download the outputs and show them in a deck.