Market Mix Model 2d6j5

Market Mix Modelling

For ElecKart is an ecommerce

Project Flow Business Understanding

Data Understanding

Data Preparation

• Objective • Background Business Understanding • Problem Statement

• • • • • •

• • • • •

Data ing Describe Data Understanding level of data Data Quality Check Data Cleaning Understanding Data type

Select Relevant data Integrate the Data Clean the Data Construct the Data Format Data

Model building

Deployment

Linear Model (Mid submission) Multiplicative Model Keyock Distributed lag Hierarchical

Objective To manage brands as a transformed marketplace

To be able to deliver better value through enhanced media and channel experience

To best allocate human and other resources for marketing activities

To be able to leverage cutting edge research tools and new sources of information to stay ahead of competition

ElecKart is an e-commerce firm specialising in electronic products. Over the last one year, they had spent a significant amount of money in marketing. Occasionally, they had also offered big-ticket promotions (similar to the Big Billion Day). The CFO feels that the money spent over last 12 months on marketing was not sufficiently impactful :

To manage brands as a transformed marketplace

Background

To be able to deliver better value through enhanced media and channel experience

To best allocate human and other resources for marketing activities

To be able to leverage cutting edge research tools and new sources of information to stay ahead of competition

Problem Statement

• How to optimize spending on commercials, online campaigns, and pricing & promotion ?

Data Description Order Level Data • • • • • • • • • • •

FSN ID: The unique identification of each SKU Order Date: Date on which the order was placed Order ID: The unique identification number of each order Order item ID: different products under the same order generates different order Item IDs GMV: Gross Merchandise Value or Revenue Units: Number of units of the specific product sold Order payment type: How the order was paid – prepaid or cash on delivery SLA: Number of days it typically takes to deliver the product Cust id: Unique identification of a customer Product MRP: Maximum retail price of the product Product procurement SLA: Time typically taken to procure the product

Other Data • Monthly spends on various advertising channels • Days when there was any special sale • Monthly NPS score – this may work as a proxy to ‘voice of customer’

As per the instruction data used from July 2015 to June 2016.

Data type of columns looked at and modified as per the need

Relevant columns were picked up ignoring the outliers

Data Cleaning

Presence of missing values were looked at and were ignored

After the basic cleansing process of sales, media, NPS score data, their missing values assessment was done

The entire data was looked at from weekly perspective

To develop a market mix model to observe the actual impact of different marketing variables over the last year and built model with potential market driver to generate maximum impact in sales so that ElecCart can Optimizely use the marketing budget in future.

Analytics Solution

Data has been provided in the following files which has been ed in the R project for analytics.

ConsumerElectronics.csv

Data Loading Media data and other information.xlsx

Product Details.docx

Rows containing NA are removed(as only .3%)

Since 80% of the variable has 'No Expected delay'(\\N) will assume these scenarios as zero delay.

Convert order id and order item id from scientific notation to integer

Negative customer ID,PIN converted into positive considering data entry errors

“GMV =List_Price* Unit” has been used to derive the value of GMV wherever it is GMV is zero but MRP is a positive number, the equation also applied to find MRP where GMV is a positive value but MRP is not available.

Converted Monthly data into daily and subsequently aggregated to week.

To understand special sales days, fetch the week number for holiday list

Create three dataframes for each of the product sub-categories i.e, Camera accessory, Home audio and Gaming accessory

Filter out dates which are not in July 2015 and June 2016

Data Preparation

AdStock

Assume Memory Coefffcient is 50%

Creating the adstock for each media investment

Data load

• For this capstone project we limit our model building focus to camera_accessory, Home_audio and Gaming_accessory product sub-categories. • For simplicity will start Linear model building with numerical features, later will consider categorical features.

Weekly aggregrated data is at granularity of product_sub_category, For Exploratory analysis, will collapse product_sub_category level weekly data.

EDA Will start analyzing the weekly Gross Merchandize Value the online website generating in sales.

• GMV, Product_mrp are in of INR, while marketing spend recorded in INR Cr. Lets convert marketing spend to INR. While models won’t be sensitive to these parity in units, but for easy of model explanation, will standadize units.

Unit Scalling

Performance driver analysis

Which KPIs drive the top line performance?

Which among these drivers could be controlled by internal influence and which are external to our control?

KPIs • week

• nps

• Radio_adstock

• gmv

• total_order

• Other_adstock

• units

• online_order_perc

• special_sale_week

• online_order

• TV_adstock

• offline_order

• Digital_adstock

• sla

• Sponsorship_adstock

• product_mrp

• Content.Marketing_adstock

• product_procurement_sla

• Online.marketing_adstock

• list_price

• s_adstock

• discount

• SEM_adstock

Effect of market leavers on sale of Camera Accessories-1 • 0-17th week promotional activity shows a very little or no impact on sales , most of the time GMV is either below 25th percentile or above 75 percentile, that’s why we may consider this data as outlier. • Week 18 through rest of the year there is a stable pattern of sales and increase of sales during promotional period is evident.

• Sales volume increases with the promotion • Most of the unit sold offline • Promotion boast offline sale rather than online

Effect of market leavers on sale of Camera Accessories-2 • Discount given throughout the year • More discount observed on non promotional weeks

• SLA is always greater than procurement SLA ,which is good as there is always buffer margin. • Most of the time NPS is higher in non promotional week, this may be a concern of inferior delivery or quality during promotional week.

Effect of market leavers on sale of Home audio-1 • 0-17th week promotional activity shows a very little or no impact on sales , most of the time GMV is either below 25th percentile or above 75 percentile, that’s why we may consider this data as outlier. • Week 18 through rest of the year there is a stable pattern of sales and increase of sales during promotional period is evident.

• Sales volume increases with the promotion • Most of the unit sold offline • Promotion boast offline sale rather than online

Effect of market leavers on sale of Home audio-2

• SLA is always greater than procurement SLA ,which is good as there is always buffer margin. • Most of the time NPS is higher in non promotional week, this may be a concern of inferior delivery or quality during promotional week.

Effect of market leavers on sale of Gaming accessories-1 • 0-17th week promotional activity shows a very little or no impact on sales , most of the time GMV is either below 25th percentile or above 75 percentile, that’s why we may consider this data as outlier. • Week 18 through rest of the year there is a stable pattern of sales and increase of sales during promotional period is evident.

• Sales volume increases with the promotion • Most of the unit sold offline • Promotion boast offline sale rather than online

Effect of market leavers on sale of Gaming accessories-2

• SLA is always greater than procurement SLA ,which is good as there is always buffer margin. • Most of the time NPS is higher in non promotional week, this may be a concern of inferior delivery or quality during promotional week.

AdStock in different media for camera

Retail Price Inflation

Home Audio

Camera and Accessories

Gaming Accessories

Correlation of different Market levers for Camera and Accessories

Correlation of different Market levers Home Audio

Correlation of different Market levers Gaming Accessories

Model Evaluation for Camera and Accessories Model

Significant Variables

Adjusted R2

Error(RSE)

Remarks

Linear Models

nps + Digital_adstock + Sponsorship_adstock

47%

1822000

Adj.R2 is moderate but error is too high, we will not select this model

Multiplicative Model

discount + Content.Marketing_adstock + Online.marketing_adstock

74%

1.077

Adj.R2 is good, RSE also is less. We are selecting this model.

Koyck Model

nps + Sponsorship_adstock + SEM_adstock

45%

.74

Though RSE is less but Adj.R2 is moderate or less, hence we are not selecting this model.

Distributive Model

Digital_adstock + Sponsorship_adstock + list_price_ma

40%

.77

Though RSE is less but Adj.R2 is moderate or less, hence we are not selecting this model.

Distributive Multiplicative Model

nps + online_order_perc + discount1,

79%

.93

Adj.R2 is good, RSE also is less.We are selecting this model.

• Adjusted R2 is the performance of the model on training data • More the Adj.R2 better the model is. • RSE is the measurement of error

Based on the adjusted R2 and RSE We have shortlisted Multiplicative Model and Distributive Multiplicative Model

Camera and accessories Elasticity • The figures are indicating elasticity of a variable with respect to overall sales. • Increasing the spent on the KPIs which have positive elasticity would increase the sales, hence more fund shall be allocated for those KPIs. • As per the Multiplicative model Online marketing shall be given priority while as per the Distributive Multiplicative model Discount will have a positive effect on sales in the subsequent week. • Hence budget shall be focussed on increasing spent on online marketing and discount.

Model Evaluation for Home Audio Model

Significant Variables

Adjusted R2

Error(RSE)

Remarks

Linear Models

discount + Sponsorship_adstock + special_sale_week

44%

2131000

Adj.R2 is moderate but error is too high, we will not select this model

Multiplicative Model

discount + Content.Marketing_adstock + Online.marketing_adstock

71%

.66

Adj.R2 is good, RSE also is less. We are selecting this model.

Koyck Model

nps + Sponsorship_adstock + SEM_adstock

52%

.697

Though RSE is less but Adj.R2 is moderate or less, hence we are not selecting this model.

Distributive Model

Digital_adstock + Sponsorship_adstock + list_price_ma

49%

.71

Though RSE is less but Adj.R2 is moderate or less, hence we are not selecting this model.

Distributive Multiplicative Model

nps + online_order_perc + discount1,

80%

.52

Adj.R2 is good, RSE also is less.We are selecting this model.

• Adjusted R2 is the performance of the model on training data • More the Adj.R2 better the model is. • RSE is the measurement of error

Based on the adjusted R2 and RSE We have shortlisted Multiplicative Model and Distributive Multiplicative Model

Home Audio Model evaluation Elasticity • The figures are indicating elasticity of a variable with respect to overall sales. • Increasing the spent on the KPIs which have positive elasticity would increase the sales, hence more fund shall be allocated for those KPIs. • As per the Multiplicative model TV ment shall be given priority while as per the Distributive Multiplicative model Sponsorship will have a positive effect on sales. • Hence budget shall be focussed on increasing spent on TV ment and Sponsorship.

Model Evaluation for Gaming Model

Significant Variables

Adjusted R2

Error(RSE)

Remarks

Linear Models

discount + Sponsorship_adstock + special_sale_week

49%

1038000

Adj.R2 is moderate but error is too high, we will not select this model

Multiplicative Model

discount + Content.Marketing_adstock + Online.marketing_adstock

82%

.82

Adj.R2 is good, RSE also is less. We are selecting this model.

Koyck Model

nps + Sponsorship_adstock + SEM_adstock

51%

.73

Though RSE is less but Adj.R2 is moderate or less, hence we are not selecting this model.

Distributive Model

Digital_adstock + Sponsorship_adstock + list_price_ma

52%

.68

Though RSE is less but Adj.R2 is moderate or less, hence we are not selecting this model.

Distributive Multiplicative Model

nps + online_order_perc + discount1,

79%

.87

Adj.R2 is good, RSE also is less.We are selecting this model.

• Adjusted R2 is the performance of the model on training data • More the Adj.R2 better the model is. • RSE is the measurement of error

Based on the adjusted R2 and RSE We have shortlisted Multiplicative Model and Distributive Multiplicative Model

Gaming accessories Model evaluation Elasticity

• The figures are indicating elasticity of a variable with respect to overall sales. • Increasing the spent on the KPIs which have positive elasticity would increase the sales, hence more fund shall be allocated for those KPIs. • As per the Multiplicative model Online Marketing and sponsorship shall be given priority . • Hence budget shall be focussed on increasing spent on Online Marketing and Sponsorship.