MARKETING EFFECTIVENESS

Welcome to the module on predictive analytics and decision making. In this module, you will learn how
predictive analytics can be used to make business decisions. To understand this, let's consider an
example of an e-commerce firm such as Flipkart. Flipkart markets its platforms, products, services, and
sale events such as Big Bill in Day through different campaigns across various channels. Channels.
These channels can be broadly classified into two categories: traditional marketing channels such as TV,
print, radio, and digital marketing channels such as email, social media, and mobile apps. Suppose you
are the marketing head of Flipkart. In this position, you may not be aware of each and every campaign
simultaneously running on various channels. Regardless, you will still be responsible for the performance
and results of your company's marketing activities. The best way to evaluate the performance of a
marketing channel on a strategic level is by analysing how much money is going into each channel and
how these spends are generating the demand for the products which is eventually driving the sales. This
is where you will start off with the first session. In Session 1, you will learn about marketing mix modelling,
which is used to analyse the impact of marketing activities in each channel on the sales.

From this analysis, you will be able to answer questions such as, which channels are performing better?
What is the contribution of each channel to sales? And which channel yields a better ROI? With the help
of marketing mix modelling, you can comment on the performance of each channel, but it doesn't help
you analyse the performance of a specific campaign or ad on a particular channel. Neither does it give
the reason why a channel is performing well or why an ad campaign on a specific channel is contributing
more to the conversions or sales. These reasons can be analysed using attribution models which you
already learned in Module 1. This model helps the marketing team gain insights into the consumer
journey and the behaviour that led to the purchase of the product. Firms can then use these insights to
identify similar customers by segmentation and devise an appropriate strategy to target them. From the
basics of segmentation, you learned how firms perform segmentation on the basis of geographic,
demographic, behavioural, and psychographic parameters. In Session 2, you will learn how to segment
customers based on the customer data using predictive analytics techniques and analyse the results to
decide the retargeting and remarketing strategy.

To this end, you will utilise the data of different leads who have shown interest in your ad campaigns.
Even after targeting a specific segment, it is important for you to pursue the leads who are more likely to
buy your products. This will enable you to direct your marketing efforts towards the leads who are likely to
convert, thereby increasing your ROI. While acquiring a customer is important for your business, It is
always costly for you to acquire a new customer compared to getting business from the existing
customers. So engaging and retaining your customers is of paramount importance. So in Session 3, you
will learn about propensity modelling, which is used to predict the actions of customers, such as whether
a lead is likely to convert or not, whether an existing customer is likely to continue buying from the firm,
etc. Based on these predictions, you will learn how to employ appropriate customer acquisition and
retargeting strategies. Without any further ado, let's dive into the module.
Every year, firms spend crores of Rupees on their marketing activities. But how do they know which of
these marketing activities are really effective? Is there a way in which they can know what the impact of
their marketing activities on sales is? For instance, suppose you are the marketing head of a leading
packaged food company. Your company's product portfolio consists of biscuits, potato chips, instant
noodles, and breakfast cereals. You recently launched a premium biscuits brand named Banket. To
promote the brand extensively, you decided to create and launch a marketing campaign and run ads on
TV channels, newspapers, radios, and other digital channels. You also decided to give introductory
discounts and offers that were communicated through hoardings and other promotions in the trade
channel. As a result of these marketing activities, your brand got a huge sales revenue and achieved a
significant market share in the premium biscuit category. You wanted the brand to continue with this
momentum and gain a larger market share in the coming years. But these results were achieved by
spending a huge amount of money on marketing. Furthermore, you aren't sure which marketing activity
worked out well for your brand. Now you want to identify the business drivers that led to the capture of a
huge market share in the previous year.

You also want to analyse the contribution of marketing activities to sales in each channel. Using this
analysis, your company wants to invest more in the effective marketing activities to boost sales and
increase the market share in the next year. Do you know of a method or approach to analyse this
situation quantitatively in a systematic manner? In this segment, you will learn about marketing mix
modelling, which will help you identify the business drivers that enable the brand to achieve the highest
market share and then optimise the marketing spend accordingly in order to achieve its growth target in
the next year.

Marketing mix modelling is an analytical approach that quantifies the effectiveness and impact of
marketing activities on sales or the market share. But as you know, the sales of a product or the market
share depends on a multitude of both external and internal factors. It is not easy for you to quantitatively
analyse it at the very outset. Before you dive into analysing the effectiveness of different marketing
activities, let's first understand what all are the factors that are driving the sales of the product. The total
sales of a product Baseline sales can be broadly classified into two categories. One, the baseline sales
component and the incremental sales component. Baseline sales is a component of total sales that is not
driven by any marketing or promotional activity. In other words, it is the proportion of total sales that would
anyway happen, even in the absence of marketing or promotional activities. The factors that impact the
baseline sales are defined as baseline factors. Example of baseline The factors are price of the product,
category trend, macroeconomic factors, seasonality of the product, competition activities, product
availability, etc. On the other hand, incremental sales form the component of total sales that is driven by
marketing and promotional activities.

In simple terms, these are the extra unit of sales that happen because of the discounts offered or other
promotional activities. The factors that impact this component of sales are is defined as incremental
factors. For example, these factors include media promotions, direct-to-direct consumer promotions, in-
store promotions, etc. As you learned, baseline sales are primarily driven by external factors such as
macroeconomic factors, seasonality, competition activity. You do not have any control over this
component of sales. To improve the total sales of your brand, you have to significantly rely on improving
the incremental sales component through your marketing activities. It is therefore important for you to
decompose the total sales volume into these two components and then identify the contribution of each
marketing activity on the total sales in order to evaluate the effectiveness of the marketing activities. Let's
take the weekly marketing data of a hypothetical biscuit brand, Banquet Biscuits, to understand this. This
data set consists of the weekly unit sales and advertising data of the brand during the week of the date
shown in the table. The advertising data consists of information on the gross rating points in print, TV
media, and the number of impressions in digital media such as display, YouTube, search, and Facebook
advertisements.

Before delving into the marketing mix modelling of this data, you need to understand the methodology
that you will be following in market mix modelling. First, Identify the baseline and incremental components
of the sales. Second, analyse and determine the contribution of each marketing activity to the total sales.
Third, identify the better performing marketing channels by calculating the efficiency or ROI of each
channel. Fourth, optimise the budget allocation for each marketing activity to achieve a high overall ROI.
To perform this analysis, regression analysis is used to decompose and analyse the contribution of each
marketing activity. So it is important for you to first understand the basics of regression analysis. In next
segment, you will learn the basics of regression and the methods to estimate the relationship between
total sales and marketing channels.
Marketmix modelling focuses on analysing marketing data across a plethora of data that we have from
marketing. Marketing data includes data on media investments, both online as well as offline, price,
promotion, distribution, and external variables like weather and holidays. These marketing data, which
would form as inputs or investments, are referred to as independent variables, whereas the dependent
variable is the marketing KPI that we seek to analyse. In MMM, or market mix modelling for short, we
seek to create a statistically significant model of marketing KPIs that explain or are explained by
marketing inputs or investments. Here is a snapshot of the various marketing data that goes into building
a market mix model. As you can see on the left are investments or inputs which are from the media
spectrum. It could be offline or online mediums that go into investments behind increasing the KPI. The
second part is understanding about price, about promotions, be it value promotions or volume
promotions, retail distribution, and also the external variables like weather, temperature, festivals, and so
on. All of these influence your KPIs, which could be awareness, consideration, website visits, downloads,
form fills, and so on. And the relationship between the KPI and these different independent variables
which are over there, is what forms the crux of a market mix model.

As you can see, the independent variable could be many, whereas the output of the KPI variable is
usually one. And As I mentioned earlier, we're trying to understand the formula of what really drives our
marketing KPI. In this case, there is one example which we have for e-commerce sales in which we have
a bunch of different independent variables from your print spends to brand hot star spends to the Virat
Kholi post on social media and negative posts on e-commerce delivery. Whereas on app downloads, if
that is the KPI, we can understand that the model for app downloads is driven by the brand spend,
influencer videos, the Facebook post, competitor Instagram posts, and even PR mentions. Likewise,
brand sales volume is driven by a whole bunch of other factors, be it price, distribution, discounts,
competitor price, and the brand TV ad.
In this segment, you will learn how to determine the relationship between the marketing activities in each
channel and the total unit sales. For the sake of simplicity, let's assume that only the print media variable
affects the total unit sales and determine its relationship with the sales. This type of linear regression,
where only one independent variable is used to explain or predict the dependent variable, is known as
simple linear regression.

As you just learned, in simple linear regression, the relationship between the independent and dependent
variable is explained using a straight line equation, as shown. Here, B₀ is the constant term, and B₁ is
known as the regression coefficient of the independent variable X. In your example, the dependent
variable Y denotes the total unit cells, and the independent variable X represents the GRP or Gross
Rating Points of the advertisements in the print media for that particular week. Grp measures the
advertising impact. It is measured as percentage of total target market reach multiplied by exposure
frequency. So if an ad is viewed by 20% of the target households, and the same ad is aird five times daily
over a week, which is seven days, then GRP will be 700. In Excel, with the help of a tool, you can test the
significance of this relationship by performing a hypothesis test, and the regression coefficient of the
independent variable can be estimated. So let's look at the null and already hypothesis. Since you want to
test if print activities impact your cells or not, the null hypothesis will be that print media does not
significantly influence the total unit cells.

Mathetically, it can be represented as B1 is equal to zero, where B1 is the regression coefficient of X in

the equation, Y is equal to B0 plus B1 multiplied by X. And the alternative hypothesis will be the exact
opposite of the null hypothesis. That is, the print media has a significant influence on the total unit cells.
This can be mathematically expressed as B1 not equal to zero in the equation. While testing this
hypothesis, if you fail to reject the null hypothesis, it implies that B1 is equal to zero. Hence, you can
conclude that the print media has no significant influence on the total unit cells. If you reject the null
hypothesis, it means that B1 is not equal to zero. That is, the print media has a significant influence on
the total unit cells. Now, let's take the historical data of cells and print media of the banquet biscuits that
you saw in the previous segment. To perform the regression analysis using the data analysis tool in
Excel. Using this, you can estimate the values of B0 and B1 in the simple linear regression equation.
Open the data in Excel sheet, click on the data tab in the main menu bar, and select data analysis.

Choose regression from the list and click on okay. In the dialogue box, select the dependent variable in
the input Y-range, and all the independent variables in the input X-range. Select the Levels checkbox if
you have selected the data along with the column names. Select the Confidence Level checkbox. Choose
the Confidence Level and click on Okay. You can also select the location where you want the result to be
displayed, the name of the worksheet, and click on okay. The result of the simple regression will be
displayed on a new worksheet.
Now that you have understood the null and normative hypothesis in regression, you will see how to
interpret the results obtained from hypothesis testing. In the lower section of the results output, you can
see that the corresponding P-value of the regression coefficient is 5.87e minus 15, which means that the
value is 5.87 multiplied by 10 to the power of minus 15. Therefore, Now, the P-value is significantly low.
You learned in the previous modules that if P-value is less than the critical value, you reject the null
hypothesis, and if it is greater than the critical value, you fail to reject the null hypothesis. Here, as you are
considering a 95% confidence level, the critical value is 0.05. As the P-value observed in the result's
output is much smaller than 0.05, you reject the null hypothesis that says the print media has no
significant influence on the total cells at a 95% confidence level. This implies that the print media has a
significant influence on the total unit cells, and B1 is not equal to zero in the equation, Y is equal to B0
plus B1 into X. Also, Also, note that the lower the P-value, the more significant the relationship between
the dependent and the particular independent variable is.

Now, you know that B1 is not equal to zero, then what is the value of B1 takes? From the regression
results, you can find the regression coefficient of the print media. It takes the value of 3968 1.09.
Similarly, the constant B0 in the equation Y is equal to B0 plus B1x takes the value of 5256 525.025.
Substituting the value of constant B0 and the regression coefficient B1, the simple linear regression
equation becomes Y is equal to 5256 525.025 plus 39681.09 multiplied by X. This equation implies that
for a unit increase in GRP of the print media, the total unit cell's the value increases by 39681.09 units.
The constant term B0 implies that the total unit cells will be equal to 5 to 56, 5 to five, even if there is no
marketing activity. So B0 in the equation represents the baseline component of the cells. As mentioned
earlier, the estimated regression equation tries to approximately explain the relationship between the
dependent and independent variable. So it does not explain the complete variation between the variables
for each data point. So you have a measure called R². R², which shows what fraction of the variation
between the dependent and independent variables is explained by the regression equation.

The R² value always lies in the range of zero to one. If the regression equation is able to capture the
entire variation between the dependent and independent variables for all the data points, the R² value will
be one. In the result table, you can observe that the value of R² is 0.3276. This means that the regression
model is able to explain 32.76 percentage of the variation. Another method to evaluate the model is to
examine the accuracy of the prediction. From the results output, it can be observed that the value of the
standard error is 1233986. It is typically the average prediction error by this model while predicting the
total unit cells from the print media. The smaller the standard error, the more accurate the predictions are.
From the low R² value, you can notice that the simple linear regression model developed is not yielding
good results.
In the previous segment, you tried to determine the impact of the total unit sales only on the basis of the
GRP in the print media. But in a real-life scenario, you need to build a model that encompasses all the
marketing activities that you employ to determine the individual contribution to the total unit sales. Is there
any method to solve this problem? One option is to use simple linear regression on sales and marketing
activities in each channel independently. But such a model would completely ignore the impact of other
channels. Furthermore, it is difficult to analyse the contribution of each channel to the total unit sales from
these independently developed regression lines. A better approach to this problem is to accommodate all
the channels in a single model to explain the total unit sales. In this case, a regression analysis needs to
be performed on more than one independent variable. This type of regression where multiple
independent variables are used to explain the dependent variable is called multiple linear regression. In
this segment, you will learn how to perform multiple linear regression in Excel to accommodate two or
more independent values and interpret the results obtained from it.

In multiple linear regression, the regression equation is extended to accommodate more than one
independent variable. Therefore, the new regression equation would be as shown here, X1, X2, and X3,
and so on are the independent variables used to explain the dependent variable Y. B₀ is a constant term,
and B₁, B₂, B₃, and so on are the regression coefficients of the independent variables X1, X2, X3, and so
on, respectively. Recall the marketing data of the banquet biscuits. Since you want to determine the
contribution of several channels on the total sales, here you need to include all this channel as
independent variables. In our example, the independent variables X1 and X2 represent the GRP in the
print media and TV, and the independent variables X3, X4, X5, and X6 represent the number of
impressions in display, YouTube, search, and Facebook channels, respectively. The total unit cells will be
the dependent variable Y. Therefore, the multiple linear regression equation becomes as shown. Here,
B1, B2, B3, B4, B5, and B6 are the regression coefficients of the independent variables, print, TV,
display, YouTube, search, and Facebook, respectively. From the regression modelling perspective, the
units of measurement for all the independent variables need not be the same.

The independent variables regarding the marketing activities can have various measures such as TRPs,
GRPs, impressions, or spend. Regardless of the units in a regression model, we will be referring to them
with the channel names from now on. Now that you have an understanding of regression equation, let's
take the marketing data to perform multiple linear regression using the data analysis tool in Excel. Open
the Excel sheet that has the data of your case. Click on the data tab in the main menu bar and then on
data analysis. Choose regression from the list and click on okay. In the dialogue box, select the
dependent variable in the input Y-range and all independent variables in the input X-range. Select the
Levels checkbox. If you have selected the data along with the Column headers. Select the Confidence
Level checkbox. Choose the Confidence Level. You can also select the location where you want the
result to be displayed, the name of the worksheet, and then click Okay. The results of the regression will
be displayed on a new worksheet.
Before jumping into the analysis of this table, let's look at the hypothesis testing used in multiple
regression. Multiple linear regression has two sets of hypothesis testing, the overall significance test and
the individual significance test. The overall significance hypothesis test is used to verify the significance of
the relationship between the dependent variable and all the independent variables as described in the
multiple linear equation. Let's look at the null and alternative hypothesis to test the overall significance.
The null hypothesis, h₀, in the overall significance test is that the regression coefficient of each
independent variable is zero. In other words, the null hypothesis or H₀ is that B₁ equal to B₂ equal to B₃
equal to B₄ equal to B₄ equal to B₄ equal to B₄ equal to B₄ equal to zero. This implies that none of the
channels has any significant influence on the total unit cells. And the alternative hypothesis, or H₀ is that
not all regression coefficient The total unit cellizations are zero, which implies that at least one out of B₁,
B₂, B₃, B₃, B₄, B₄, B₄, B₄, and B6 is non-zero. In other words, at least one of the independent the
dependent variables has a significant influence on the dependent variable, which implies that at least one
of the channels has a significant influence on the total unit cells.

The P-value corresponding to the overall significance test can be seen in the result output in the table.
Note that the P-value is much lower than 0.05. Therefore, you can to reject the null hypothesis at a 95%
confidence level. This implies that there is at least one regression coefficient that is non-zero. Had we not
been able to reject the null hypothesis, it would have meant that B1, B2, B3, B4, B5, and B6 are all zero.
This would mean that there is no relationship between dependent variable and all the independent
variable. Variables. But in our case, the P-value is significantly low. So we know that at least one
independent variable has a significant influence on the dependent variable. To find out which variables
have a significant relationship with the dependent variable, we need to conduct an individual hypothesis
test for each independent variable, just like you did in case of simple linear regression. This is also called
individual significance test. For example, the null hypothesis of the individual significance test for the print
media will be that the GRP of the print media does not significantly influence the total unit cells. This is
mathematically represented as B1 is equal to zero.

The alternative hypothesis will be that the GRP in the print media has a significant influence on the cell
variables which can be mathematically represented as B1 not equal to zero. The hypothesis for other
independent variables will be similar to this. In Excel, the tool also gives you the P values for the
individual significant test for each independent variable. From the results table, you can see that
corresponding P values of the constant and the coefficients of print and such are lower than 0.0 0.05.
Therefore, you can reject the null hypothesis at a 95% confidence level. This implies that the
corresponding regression coefficients, B₀, B₁, and B₀, are non-zero. The P values for the coefficients of
the other independent variables are more than 0.05. So you fail to reject the null hypothesis at 95%
confidence level. Therefore, the regression coefficients B2, B3, B4, and B6 all are equal to zero, which
means that the marketing activity is in TV, display, YouTube, and Facebook channel have no significant
influence on the total unit cells. Now that you know that only the constant term and the regression
coefficients of print and search are non-zero, What are the values that these coefficients can take?

This can be obtained from the coefficients column for the corresponding independent variables.
Therefore, the coefficients of print and search are 28203 and 0.5645, respectively, and the constant is
5179306. The coefficient of TV, display, YouTube, and Facebook are zero, as they don't have a
significant influence on the total unit cells. The regression equation thus becomes as shown. Similar to
the simple linear regression equation, this estimated equation also approximately explains the
relationship between the dependent variable and all the independent variables. So It is not capable of
explaining the complete variations between the variables. You already learned that R² helps to measure
the fraction of variation that is explained by the regression equation. From the result, you can observe
that R² value is 0.3650. But you have to be careful while using this measure because it does not consider
is the effect of multiple independent variables in the regression equation. The interesting thing to note
here is that every time you include an extra independent variable in your model, the value of R² always
increases. Therefore, the R² measure has to be adjusted to the number of independent variables. A better
measure to use in this case of multiple linear regression is the adjusted R².

In the results, you can see that the value of the adjusted R² is 0.3394. This means that the multiple linear
regression equation is able to explain 33 3.94% of the variation between the dependent and the
independent variables. The model has a low adjusted R² because of the assumptions that you made
regarding the advertisement in each channel. In multiple linear regressions, You assume that advertising
variables such as GRP in TV, print, and the number of impressions in search have a linear relationship
with the total image cells. But these variables are considered to have a nonlinear relationship with sales
because of the two reasons. The first reason is diminishing returns. The basic function of all above the
line advertising, like TV advertisement, is to create awareness among customers to influence short term
purchase decision, and help building brand equity in the long term. A customer may be exposed to a TV
ad today, but over time, the impact of the ad diminishes. The second reason is the carry-over effect,
according to which there is always an impact of past advertisement on the present cells. So the
advertising variable should be transformed to capture this non-linearity. These models are complex and
out of scope of this model.

So let's proceed with the linear model that you have developed.
In the previous segment, you learn how to develop a multiple linear regression model and interpret the
regression equation. If you recall the methodology of market mix modelling, the first step is to identify the
baseline and incremental components of sales. You learn that this can be done using regression analysis.
Now that you learn how to perform a multiple linear regression, in this segment, you will learn how to
determine the contribution of each incremental factor and analyse the effectiveness of marketing activities
in each channel.

Before discussing the effectiveness of each marketing channel, let's also understand the factors that
contribute to the baseline component of the sales. As you already saw, the baseline sales are component
of total sales that is primarily driven by external factors such as macroeconomic factors, seasonality,
competition activity, etc. In other words, the component of the sales which will be there even without any
marketing activities. We can also determine the contribution of these factors to the total unit cells. Let's
consider the example of banquet biscuits to understand this in detail. Recall the multiple linear regression
equation. You learned that in order to study the influence of baseline and incremental factors on cells, you
need to develop a regression model with the total unit cells as dependent variable, and all the baseline
and incremental factors as the independent variables. The baseline factors include independent variables
such as price of the product, the distribution or the presence of the product across the market, category
growth, etc. And the incremental factors include the independent variables such as marketing activities in
print, TV, display, YouTube, YouTube, Search, and Facebook. If all the independent variables are
significant, the multiple linear regression equation can be written as shown.

From this regression equation, you can guess that the capital B₀ plus plus B1 into price plus capital B2
into distribution plus capital B3 into category growth gives you the baseline component of sales. And
small B1 into print plus small B2 into TV plus small B3 into display plus small B4 into YouTube plus small
B5 into search plus small B6 into Facebook gives you the incremental component of the cells. In this way,
you can decompose the total cells into baseline and incremental components for each week by
substituting the values of the respective channels in the equation. But in this case, since you are only
interested in determining the contribution of the marketing channels, you will focus only on the
incremental factors. Recall the multiple linear regression equation developed using the marketing data of
the banquet biscuits. In this equation, Y gives you the estimated value of total unit of weekly sales from
the independent variables. As you didn't consider any baseline factor, the constant term represents all the
other baseline factors. Its value represents the component of the total cells due to all the baseline factors.
So 5179 306 is the baseline cells component. On the other hand, print and search channels are
incremental factors.

So 28203x1 plus 0.5645x5 gives you the incremental sales component. Therefore, the value of the
weekly incremental sales component can be calculated by substituting the GRP in print and the number
of impressions in charge channel in this equation.
Now that you know how to decompose total sales, let's move ahead to analyse the contribution of each
marketing channel in the total sales. From the results of the multiple linear regression, you know that the
print and search are the two channels that have a significant influence on total unit sales. Let's delve into
the interpretation of this regression coefficient. The regression coefficient of the print channel is 28203,
which means that for every unit increase of GRP in print, the total unit sales value increases by 28203.
Similarly, the regression coefficient of the search channel is 0.5645. This means that for every unit
increase in the number of impressions in search ads, the total unit sales value value increases by 0.5645.
These regression coefficients give you an idea of the contribution or impact of each marketing channel on
the total cells. Now, let's look how to calculate the contribution of each marketing channel on the total unit
cells. In the table, Y1, P1, S1 denotes the estimated total unit cells, the GRP in print media, and the
number of impression in search channels for the week one, respectively. For example, the contribution of
the print media to the total unit cells for week one can be calculated as the regression coefficient of the
print media multiplied by the GRP of the print media of that week, divided by the estimated cells of that
week, multiplied by You can calculate the estimated total unit cells for that particular week by substituting
the respective GRP and number of impressions in print and search channels in the regression equation.

The value of the total unit cells in a week is calculated as 5179306 plus 28203 multiplied by the GRP of
print in that week plus 0.5645 multiplied by the number of impressions in such channels in that week.
Now, let's calculate the total estimated sales for each year. The value of the total estimated sales for year
one is the sum of all the weekly estimated cells. Similarly, you can calculate the estimated total unit cells
for all the weeks in the three years. Now that you have obtain If you have sustained the estimated value
of the total cells, you can calculate the contribution of print media to total unit cells in year one. The
contribution of the print media in the total unit cells in year one is calculated as The regression coefficient
of the print media multiplied by the sum of all GRP of print in that year, divided by the total estimated cells
multiplied by 100. Note Note that a year has 52 weeks. This comes out to be 9.36% in the example
considered. The contribution of search channel in the total unit cells in year one is calculated as the
regression coefficient of search channels multiplied by the sum of all number of impressions in the search
channels in that year, divided by the total estimated cells multiplied by 100.

Similarly, the contribution of print and search channels can be calculated for year two and year three. The
contribution of each factor to total unit cells for three years is summarised as shown. With the help of the
histogram, you can visualise the contribution of each factor in the total unit cells of each year. From the
Histogram of Year 2, you can see that the contribution of baseline sales is 86.18% on the total sales in
that year. That of print is 9.47%, and that of search channel is 4.35%. Similarly, from the Histogram of
Year 3, you can see that the contribution of baseline sale is 83.39% of the total cells in that year. That of
print is 11.09%, and that of search channels is 5.52. If you observe the total unit cells estimated increased
from year 2 to year 3. Let's calculate the percentage of increase in sales, which is done by dividing the
change in the total sales from year 2 to year 3 by the total unit sales in year 2 multiplied by 100. From the
data, you can see that the percentage growth in sales from year 2 to year 3 is 3%. Now, let's analyse how
much of this sales growth is contributed by each channel.
This method of determining contribution of each channel in increasing the sales is done by a method
called due to analysis. The contribution of each Each channel to the sales growth from year 2 to year 3
can be calculated by the change in absolute unit sales contribution by each channel, divided by the
change in the total unit cells, multiplied by the sales growth percentage in the corresponding period. Let's
first calculate the absolute unit sales contribution of each channel in year 2 and year 3. For the baseline
factors in year 2, this is calculated as total inuit sales in year 2 multiplied by the percentage of contribution
of baseline sales that you calculated earlier. Similarly, you can calculate the absolute Inuit sales
contribution for the print and search channels. The absolute Inuit sales contribution of each factor in year
three is also calculated in a similar way. Now that you have the values of the absolute Inuit sales
contribution of each channel for year two and year three, you can derive the change by calculating the
difference in the corresponding values for the two years. Now, the contribution of each channel to the
sales growth can be calculated by dividing this change in absolute unit cells of each channel, divided by
the change in total unit cells, multiplied by the sales growth percentage.

From the due to analysis, it can be seen that 1.99% of the growth in sales is due to the print media, and
1.35% is due to the search channel. From these values, it is evident that the print media has a significant
influence in increasing the total unit sales from year two to year three. If you have data on the marketing
spend of each channel during that period, the efficiency of each channel can be calculated as a unit sales
contribution by that channel multiplied by the average unit price, divided by the marketing spend of that
channel. Suppose the marketing spend on the print channel in that year, 3, is Rupes 15 crores, and the
average unit price of the product is Rupes 23. Then the efficiency of the print channel is calculated as 23
multiplied by 3580 2626, divided by 15 crore, which is equal to 5.48. The return on The investment of
each channel is calculated as the efficiency multiplied by the gross margin of the product. Suppose the
gross margin of the product is 32%, then the ROI of the print channel is 0.32 multiplied by 5.48, which is
1.75. In this way, you can calculate the ROI for each channel, and you can identify the channels that
performs better.

So you can decide to invest more in the channels which gives you a high ROI among all the channels. In
this way, you can allocate your budget channels, whichever has high ROI, to maximise the impact of your
marketing activities, to increase the sales of your product.
Conducting a regression test and understanding the different metrics for market mix modelling. Looking at
this data set which we have over here, we have sales data coming as sales volume, TV data as TV
GRPs, print spends, radio spends, internet spends, outdoor spends, promotion pack spends, and
promotion voucher spends. So you have two different promotion variables over here, and these variables
are what we call as the media mix between television, print, radio, internet, and outdoor. What we're
trying to do is, under understand what influence do these inputs of media be it television, print, radio,
internet, outdoor, and whether the promotions which we've conducted across pack promotions and
voucher promotions, Did all of these inputs that we have over here, did they have an impact on sales?
And this is monthly data. So before we start, it's very important for us to check whether in Microsoft Excel,
you need to go to data and check if you have the data analysis tool over here. To check for that, the first
thing is click on the analysis tools and you would get these options over here. Now, just to I'll show you as
an example, because in all cases, it's not selected as a default.

So you need to go to data over here, click on the data tab. You usually start from the home tab. So you
need to browse horizontally to the data tab, go to the corner, which is analysis tools, select these options,
both analysis tool pack as well as solver add-ins, click Okay, and you should see these buttons over here.
All right? So once this is done, the most important thing is then click on the data analysis tool. You would
find the regression test coming in over here. You would have a host of other tests from Manova,
correlation, covariance, and so on. But the one which we want to test today is the regression test to bring
about a market mix model. All right? So you click on okay. And since we want to understand the influence
of all the other variables on sales, your dependent variable, which is the Y-range, is what we select as a
sales volume. So you select the whole column, going all the way down. And the X-range are all the
variables that we have on top over here, ranging from television, print, radio, internet, outdoor, promotion
pack, and promotion voucher. Now, you need to select all of these.

You click and drag it all the way down. And the first thing which we need to do is select the dependent
variable, which is over here. And then we select the independent variable, which is the X range, which is
what we look over here. And you need to click on labels. Otherwise, what happens is you would not you
will get the labelling right. You will get column one, column two, column three, and so on. So you click on
the labels over here. The Confidence interval, which you see over here, is a default of 95% confidence
interval. All right? So this is important. It's a good default to have. We don't need to change it to 99%, but
typically, we take it as a 95%. The other option is that you could choose this as a default, which is you will
have a new sheet coming in over here, sheet 3, with the output. So once we select this, the dependent
variable, which is the Y-range, which is sales, the independent variables, which are TV, print, radio,
internet spend, outdoors, promotion, pack, and promotion voucher. You click okay over here, and it goes
on to a new sheet.

And this is your summary output, and I'm just going to expand it a little bit more so so you can see it
clearly. Now, you might notice that you have a lot of data under headings coming in over here. But as you
would have seen on the platform, the most important ones are actually just five important ones from this
whole list. Number one, it's the R², right? The second one is the adjusted R². The third one is the
intercept. The fourth, the coefficient, and the fifth is the P-value of each independent variable over here.
All right? So I'm just going to highlight it as these are the independent variables. If you remember, these
were the values which we took under X. And we want to understand what the influence of each of these
variables are on our sales. All right? So going one by one on these five topics, which is the R², the
adjusted R², the intercept, coefficients, and the P-value. The R² basically gives you an idea that if I use
these 6-7 variables over here, we have seven independent variables. If I create a model in which these
seven variables can explain my sales, then it explains only 22%, or in other words, if I'm using the same
number over here, this is 22% of dependent variable, which is sales, is explained by this model made up
of seven independent variables.

All right? So that's what the R² tells you. The R basically stands for residual, all right? And this is
basically, if I were to expand this, it would be the residual values, square. And the adjusted R² basically
tells us that if I were to remove or add another variable over here, and I have to compare the summary
output of one model to another model, then the adjusted R² helps explain if I need to add or delete or an
independent variable. It also gives me as a... It's a good guide to select between models. And what is a
model? This is a summary output. So this is a whole output of a model which is created by seven different
independent variables. Using these many observations and trying to explain our dependent way to be,
which is sales. So our square helps us understand how good a particular model is, which is 22%, which is
not that good, because for a sales model, you usually want to do something in the range of about sales
models, typically it should be in the high 90% range. And in other words, the explanatory variables, which
are the independent variables, should explain about 90 % over here.

Over here, it's a poor model as we look over here. The adjusted R², as I explained, it helps us to decide
whether if I have one summary output over here for a model, and if I have another summary output in
which I remove a few variables and I redo the model, it helps me compare by looking at the adjusted R²
as to which model, model A or model B or model C, which one should I select? All right? Those are two
important variables over there or two important concepts you learned on the Upgrad Marketing analytics
platform. The third one which you would have learned in module three was about the intercept, which is
this particular value. Now, the intercept is a very interesting concept. You would notice in this particular
example, example of a model that the intercept coefficient is very high. All right? So it basically helps to
explain that if either of these independent variables were zero, then how much is the base level of sales?
An example is, last year during the lockdown, during the first lockdown of COVID-19 in India, none of
these, for some brands, would have been active. A lot of advertising would have been pulled off.

So it would still indicate that there are some sales happening without any of these inputs. And that is what
we call as the base value or the intercept. So I'm just going to type it out over here or the base, which we
call in this case. The intercept also helps you understand that if you have a poor R², your intercept tends
to capture a whole lot of hidden variables. So the more better your R², it could help understand that you
have a much smaller base at times, simply because we are trying to attribute the effect of sales on other
parts over here or other independent variables, correct? Or explanatory variables, as we call it. So that
was about the R², adjusted R², and the intercept or the base. Now, the coefficients are essentially what
we call as the multipliers, right? Imagine a coefficient as a recipe, all right? So just like in a recipe, you
have, if I have to make, say, dal, then I have to have 500 grammes of dal, tur dal. Then I have to have 20
grammes of salt. I have to have some masa. Say, probably 50 grammes of masa.

Pretty spicy, I guess. And if I have some other vegetables over here, like tomatoes, of half kg and so on.
All right? So just to give you an example of what a coefficient is, if this is a recipe over here for dal, this is
the quantum, right? And the quantity or the quantum is the multiplier or the amount of how much you
have to put an input or an ingredient, right? So essentially, it's the same thing. Like the ingredient is the
independent variable, while the quantity is the coefficient. So this much amount amount of tuvar dal and
this much amount of salt. Likewise, the coefficient times the TBGRP value, the coefficient of print spend
times the print spend value is how we look at it in that sense. So this is just an example for you to
understand how to look at coefficients. It's essentially the multiplier. Now, coming to the most important
part when it comes to selecting or deselecting variables over here. How do I choose which variable I need
to drop or which I need to pick? The easy test over here is a P-Value test. The P-Value test is the short
form for the probability value, all right?

And probability value. And it basically explains what is the probability that across the data points, across
the 34 observations that we've seen, that the TV-GRPs can explain sales, whether it is positively
explaining or whether it is negatively explaining, but is it consistently explaining sales? And it gives you
that probability value. So the P value is very important because if we remember, it is at a 95% confidence
interval. So essentially, if the P value is lesser than 0.05, and 0.05 mainly because at a confidence
interval of 95%, the error is about 5%, which is about 100% minus this particular value, which is 5%. So
this is the error value, and this is the confidence value. So across the confidence value and the error
value. So what we're trying to establish over here is, is the error value within the error range of 0 to 5 %?
Or in other words, are these probability values, are they lesser than 0.05. So if you keep looking at each
one of these independent variables, you can easily establish whether the error values are less than 0.05.
If it is greater than 0.05, then our confidence reduces. And hence, I cannot confidently take this particular
independent variable in the model.

So that's the easy way of explaining the P-value, and we just need to check whether it is lesser than 0.05.
An easy way how to do that is you can use the if function. If the probability value is lesser than 0.05, then
you can basically put a whole message over there as in choose the variable. L What you could do is you
could mention drop. And this is just a small trick on how you can do that. I mean, quickly look through a
model. And by doing it for this whole model across the independent variables over here, we easily can
understand by looking at the probability values, whether we need to choose or whether we need to drop
them. So this model Model as an example which we've created. It shows that the promotion value is the
only promotion for voucher, which is amongst the two promotions that we have. This is the only
independent variable that consistently explains our sales. So this is a roundup of understanding how to
look at the data which you get, how to select the data tab, the data analysis, go on to creating this model,
and how do you read this particular output.

So I hope this has been helpful for you to understand the concepts more from understanding about why
we do the whole regression test. We are trying to understand how each one of these independent
variables, can they explain the dependent variable called sales. And sales. And how the R², the adjusted
R², the intercept, the coefficient, and the P-value, which is the probability value, how these five concepts
which you've been exposed to and which you would have learned Upgrad marketing analytics platform.
This is just a live example on how these five concepts come to life.
Vapour is a food delivery app that offers pick-up and drop facilities for local neighbourhood restaurants in
Indian cities. Vapour's marketing department has invested over 50 croes in advertising and promotion
every year to drive app downloads since the launch in 2019. The downloads for Vapour app was rising at
a steady pace in 2019, but then picked up in 2020, owing to the the easing of the lockdown in 2020.
However, the company slashed advertising and promotion budgets when the lockdown due to COVID-19
hurt the businesses from March 20th onwards. And resumed only 50 % investment levels across
advertising and promotion since the Unlock one in June 2020. The advertising budget for Vapour is
spread out between newspapers, radio, YouTube, and two promotions, one on TikTok, and the other
being an offer promotion. A critical internal budget meeting is to take place between the CEO and CFO.
And as part of the marketing team, we have to go ahead and give a recommendation on how much
should be spent on marketing the next year. So Vapour's internal analytics team have created a model
taking into account the key variables models that drive app downloads for Vapour. Take a look at the
model output provided by the analytics team and help the marketing team answer the key questions that
the Vapour product and marketing team seek to answer to the CXO during the meeting next week.

Take a good look at the VAPER app download modelling output screen. Looking at the output of the
regression test, you can see that each of the independent variables being given over there have
coefficients and the P-values stated very clearly over there. Looking at this data output, you would need
to come up with answers to the following questions which are asked below.
In this session, you learn that marketing mix modelling is an analytical technique used to quantify the
impact of marketing activities on the sales and to calculate the effectiveness of each marketing channel.
You understood that to determine the effectiveness of the marketing channels, sales can be classified
into two components. The component that is not driven by any marketing activity is called the baseline
component, and the factors influencing this component are called baseline factors. These factors include
the product price, category trend, macroeconomic factors, seasonality of the product, product availability,
etc. The second component is incremental sales, which is driven by marketing activities. The factors
influencing Sales. Saling these components are called incremental factors, which include traditional
media promotions such as TV, print advertising, digital media promotions such as search ads, banner
ads, social media promotions such as Facebook, Instagram ads, and Instro promotions. Then you learn
to identify the baseline and incremental components of sales by establishing the relationship between
sales and all the factors. The relationship can be determined using the regression method. In regression
terminology, you learn that there are two types of variables, the dependent variable and the independent
variable. The dependent variable is the variable that you're interested in predicting, while the independent
variable is the one that may influence or impact the dependent variable.

After this, you learn that the relationship between the dependent and the independent variables is
explained using a straight line equation, Y is equal to B₀ plus B₁x. Then, with the help of the banket
biscuits example, you learned how to develop a simple linear regression equation with sales as the
dependent variable and cross-rating point in print media as the independent variable. You then saw that
the significance of this relationship and the coefficients can be determined through a hypothesis test. The
null hypothesis hypothesis for this test is that the print media doesn't have a significant influence on the
total sales and is mathematically represented as B1 is equal to zero. The alternative hypothesis is that the
print media has a significant influence on the total sales and is mathematically presented as B1 not equal
to zero. From the P values obtained, a null hypothesis is rejected if the P value is less than the critical
value at a certain confidence level. This would mean that the print media has a significant influence on
the total sales. If the P-value is greater than the critical value at a certain confidence level, you fail to
reject the null hypothesis, which means that the print media does not have a significant influence on the
total sales.

If the null hypothesis is rejected, it means that the regression coefficient of print media, which is B1, takes
a non-zero value, which can be obtained from the coefficient columns in the results table. From the
coefficient and the intercept obtained, you can We've developed this simple linear regression equation,
which is an approximate relationship to explain the variations between the dependent and independent
variable. To determine how much of this variation is explained by regression equation, You use the R²
metric. This metric indicates the proportion of variation explained by the regression equation to the total
variation between the dependent variable and the independent variable. You also learn that the RSquare
value lies between zero and one. The higher the RSquare value, the better the model. Thereafter, you
saw that it is not feasible to build several simple linear regression models for each factor to explain their
individual influence on sales. In such cases, it is better to build a multiple linear regression model which
incorporates all the factors as independent variables to explain the dependent variable. Then, with the
help of Pancake Biscuit's example, you saw how to develop multiple linear linear regression model with
sales as the dependent variable and channels such as print, TV, search, display, YouTube, and
Facebook as independent variables.

You learn that the relationship between sales and the marketing channels can be determined by
extending the straight line equation. You also learn that multiple linear regression requires two sets of
hypothesis testing, an overall significance hypothesis test and an individual significance hypothesis test.
The overall significance test is used to determine the significance of the linear relationship between sales
and all the other channels. If the P-value of the overall significance test is less than the critical value, it
means that at least one channel has a significant influence on the sales. Otherwise, it means that none of
the channels has a significant influence on sales. Once you confirm that at least one channel has an
influence on sales, you can look into the P values for the regression coefficient of each channel. The
variables that have a P value lower than the critical value have a significant level of influence on sales.
You then saw that the regression coefficients can be obtained from regression results. From these
coefficients, you can develop multiple linear regression equation. You also learned that the adjusted
RSquare is the metric used in multiple linear regression to evaluate how good the model is. You also saw
that adjusted RSquare value in our example is low due to the assumption that the advertising in the
channel has a linear relation to sales.

You also saw that the nonlinear aspects of advertising can be incorporated by the ad stock transformation
of the channel variables. After developing the regression equation, you observed that the independent
variables corresponding to the baseline factors give you the baseline component of sales, and the
variables corresponding to the incremental factors give you the incremental component of sales. Then,
from due to analysis, you learned how to calculate the contribution of each channel to the overall sales
growth. You also learned how to calculate the efficiency of each marketing channel by dividing the sales
contribution of each channel with marketing spend on that channel. From the efficiency, you calculate the
ROI of each channel. From the ROI, you can decide to invest more into the channels that give you higher
ROI. In this way, you can allocate your budget to maximise the impact of marketing activities in each
channel on your product sales. In this session with With the help of marketing mix modelling, you learned
how to calculate the ROI of each channel and understand which channels are performing better. Even
though this model helps you identify the best channels, it doesn't give you the reasons as to why this
model is performing so well.

From the earlier modules, you learned how to track the consumer journey and identify the touch points
responsible for sale using attribution models. Companies can then use these insights to identify such
customers and devise an appropriate strategy to target them. The next session, you will learn how to
identify similar customers based on their purchase behaviour and devise strategies to target them.