Problem Statement: Part - III

Now that you have developed various hypotheses for the issues faced by ElecKart, lets consider one among them, the lack of market mix modelling. As a data scientist or an analyst working for ElecKart, you need to develop a market mix model based on the given information and the data sets related to consumer purchases, monthly spends on advertising channels, climatic information and the NPS/stock index.

Lets hear from Amit about what this capstone project will revolve around and the steps that you need to perform to build an effective model that optimises the marketing spends to achieve its target revenue with high turnover rates.

Play Video

718352

You can download the data set from this link.

Since the data set is huge, it will be difficult for you to download it and work on it offline. Hence, we have downloaded the raw data set and shared it with you at this Google Drive link. Please do not create any files in this drive folder. You are advised to use Google Colab to write your Python code. Create a separate folder in your Google Drive to save any code/CSV files (processed data) and the colab notebooks that you create during this project.

Google Colab notebooks are almost the same as Jupyter notebooks in terms of UI and functionalities. However, there are few things that you need to be equipped with before you start working on Colab and hence, we have provided a short document to ease this process. Refer to the attachment below that guides you through how to work with Google Colab.

Colab User Manual

Data Understanding You have to use the data from July 2015 to June 2016. The data consists of the following types of information:

Order level data

FSN ID: The unique identification of each SKU

Order Date: Date on which the order was placed

Year/Month: Year and month when the order was placed

Order ID: The unique identification number of each order

Order Item ID: When the customer orders two different products at the same time, the system generates two different order Item IDs under the same order ID. Note that orders are tracked using Order Item IDs.

GMV: Gross merchandise value or revenue

Units: Number of units of the specific product sold

deliverybdays: Number of business days between the placement of the order and the final delivery day

deliverycdays: Number of calendar days between the placement of the order and the final delivery day

Order Payment Type: How the payment was made, whether prepaid or cash on delivery

SLA: Number of days it typically takes to deliver the product

Cust id: Unique identification of a customer

pincode: Pin location from where the order was placed

product_analytic_super_category: Super category to which the product belongs

product_analytic_category: Category to which the product belongs

product_analytic_sub_category: Sub-category to which the product belongs

product_analytic_vertical: Assortment vertical to which the product belongs

Product MRP: Maximum retail price of the product

Product procurement SLA: Time typically taken to procure the product

Apart from this, the following information is also available:

Monthly spend on various advertising channels

Days when there was any special sale on products

Monthly NPS score (this may work as a proxy to the voice of the customer)

Stock index of the company on a monthly basis

Climatic information of Ontario during 2015 and 2016

Project Pipeline

The project pipeline can be briefly summarised in the following steps:

Data Preparation You have to create market mix models for three product subcategories: Camera Accessory, Home Audio, and Gaming Accessory. Also, the models must be built at a weekly level for each of the subcategories.

Feature Engineering As the e-commerce company is based in the Ontario region, we will need to include its climate data to analyse whether it has any effect on the companys revenue. Create as many features as possible with the available data.

Note: Create separate columns for Pay Date (if the first or fifteenth of the month) and holidays by creating a flag as a 0 or 1. For example, if its a holiday, the value will be 1.

Exploratory Data Analysis Perform univariate analysis, bivariate analysis, correlations, cross-tabs, and create visualisations on the pre-analytical data set. Obtain insights and shortlist variables for modelling. Create the KPIs according to the requirement of the model and write appropriate comments for choosing those KPIs.

Model Building and Evaluation Build basic, logarithmic, and multiplicative models or any other possible model with the available data set and fine-tune their hyperparameters until you get the desired level of performance. Analyse the impact of various attributes on the target variable using appropriate metrics.

Presentation of Results Choose the best results of the market mix model for each of the three product subcategories and create a PowerPoint presentation along with a video explaining the analysis and results to the relevant business stakeholders such as CMO/CFO.

The audience should be able to intuitively understand the model/analysis that you have built/performed and its financial impact on the business. Point out any surprising or unexpected trends that you notice.

Let's take a look at the evaluation rubric in the next segment.