Unit 1 of 4 · MBA Sem 3

Unit 1: Understanding data and market analysis

Marketing Analytics notes · PTU syllabus (MBA 961-18)

3 min read6 topics10 exam questions
On this page
  1. Unit summary
  2. Introduction to marketing analytics
  3. Sales forecasting
  4. Market share and performance indicators
  5. Customer choice, profitability and lifetime value
  6. Product portfolio analysis
  7. Cannibalisation analysis
  8. Key terms
  9. Quick revision
  10. Important questions

Unit summary

Marketing analytics turns sales, customer and product data into better marketing decisions. This unit covers an introduction to analytics and data interpretation in MS Excel, sales forecasting, market share and performance indicators, customer choice, profitability and lifetime value analysis, product portfolio analysis and cannibalisation analysis.

After this unit you can

  • Interpret marketing data using Excel
  • Forecast sales and compute market share and performance indicators
  • Analyse customer choice, profitability and lifetime value
  • Apply product portfolio and cannibalisation analysis

PTU syllabus topics

  • Introduction to analytics and data interpretation on MS Excel
  • sales forecasting
  • market share and performance indicators
  • customer choice/profitability/lifetime value analysis
  • product portfolio analysis and cannibalization analysis
1

Topic 1

Introduction to marketing analytics

  • Marketing analytics: measuring, managing and analysing marketing performance to maximise effectiveness and return on investment.
HierarchyTypes of marketing analytics
  1. Prescriptive

    What should we do? Budget allocation, pricing optimisation

  2. Predictive

    What will happen? Sales forecasts, churn and response models

  3. Diagnostic

    Why did it happen? Driver analysis, attribution

  4. Descriptive

    What happened? Sales, share, campaign reports

  • Excel for marketing data: tables, sorting and filtering, SUMIFS and COUNTIFS for segment totals, pivot tables by region, product and month, charts for trends, Goal Seek and Solver for planning, Analysis ToolPak for regression and descriptive statistics.

Example

A pivot table of 50,000 invoices by state and pack size shows that 60% of growth came from small packs in tier-2 cities — a pattern hidden in the raw data.

2

Topic 2

Sales forecasting

Sales forecasting estimates future sales for a defined period and market. Methods are judgemental (sales force composite, executive opinion, Delphi), time series (moving averages, exponential smoothing, trend projection) and causal (regression on drivers such as price, advertising and income).

ClassificationSales forecasting methods
Forecasting
  • Judgemental

    Sales force composite, executive opinion, Delphi

  • Time series

    Moving average, exponential smoothing, trend (FORECAST.LINEAR, TREND), seasonal indices, Excel Forecast Sheet

  • Causal

    Regression on price, advertising, distribution, income

  • Market build-up

    Number of buyers × purchase rate × price

  • Accuracy measures: MAD, MAPE, tracking signal.
Key formulasForecast error
  • MAPE

    Average of (absolute error ÷ actual) × 100

  • Tracking signal

    Cumulative error ÷ MAD; outside ±4 signals bias

Example

A 3-month moving average: a store sold 400, 440 and 480 units in July, August and September. The forecast for October = (400 + 440 + 480) ÷ 3 = 440 units. Because sales are rising, the moving average lags the trend — trend projection would forecast closer to 520.

Exam tip

Choose the method by data availability, time horizon and the accuracy needed.

3

Topic 3

Market share and performance indicators

Market share shows how a brand performs relative to competitors, not just in absolute sales. Relative market share compares a brand with its largest competitor.

Key formulasShare and penetration metrics
  • Market share (value)

    Brand sales revenue ÷ total market revenue × 100

  • Relative market share

    Brand share ÷ share of the largest competitor

  • Penetration

    Customers who bought the category or brand ÷ total population

  • Share of wallet

    Brand spending ÷ customer's total category spending

  • Heavy usage index

    Brand buyers' category purchases ÷ average category purchases

  • Decomposing share: share = penetration share × share of requirements × heavy usage index.
  • Other KPIs: sales growth, distribution (numeric and weighted), brand awareness, consideration, conversion, repeat rate, customer satisfaction and NPS.

Example

Calculating share: a brand sells ₹30 crore in a ₹200 crore category, so value share = 15%. Its biggest rival sells ₹50 crore, so relative market share = 30 ÷ 50 = 0.6.

Exam tip

A brand can grow sales and still lose share if the market grows faster.

4

Topic 4

Customer choice, profitability and lifetime value

GraphCustomer lifetime value: retention decays over time
% retainedYearsOCustomers retained

CLV = Σ margin × retention^t ÷ (1 + discount rate)^t; improving retention by a few points lifts CLV sharply

  • Customer choice analysis: logit models and conjoint analysis estimate how attributes (price, brand, features) drive choice.
  • Customer profitability: revenue from a customer minus the costs to serve — reveals that a minority of customers often generate most profit (whale curve).
Key formulasCustomer lifetime value
  • Simple CLV

    Margin per period × retention rate ÷ (1 + discount rate − retention rate)

  • Margin multiple

    r ÷ (1 + d − r)

  • Customer acquisition cost

    Marketing and sales spend ÷ new customers acquired

Example

Annual margin per customer ₹2,000, retention 80%, discount rate 10%: CLV = 2,000 × 0.8 ÷ (1 + 0.1 − 0.8) = ₹5,333. If acquisition cost is ₹1,500, acquiring the customer is worthwhile.

  • Uses: decide acquisition spend, prioritise retention, segment customers by value (RFM — recency, frequency, monetary).
5

Topic 5

Product portfolio analysis

Portfolio analysis decides where to invest across products. The BCG matrix classifies products by market growth and relative share into stars, cash cows, question marks and dogs. The Pareto (80/20) principle often applies: a few products or customers give most of the profit.

GraphCustomer pareto: top customers give most of the profit
Cumulative % of profitCumulative % of customersOTop 20%Cumulative profit

The 80/20 pattern guides key-account and retention spending

  • BCG matrix: stars, cash cows, question marks and dogs based on market growth and relative share.
  • Contribution analysis: contribution margin by product, ABC of SKUs, profitability after direct marketing costs.
  • GE–McKinsey matrix: market attractiveness vs business strength for multi-factor assessment.

Example

Pareto in practice: a distributor finds that 20% of its customers produce 78% of profit. It assigns key account managers to them and serves small customers through an online ordering portal to reduce costs.

Exam tip

Draw the BCG matrix and state the strategy for each quadrant.

6

Topic 6

Cannibalisation analysis

  • Cannibalisation: sales of a new product taken from the firm's existing products.
Key formulasCannibalisation
  • Cannibalisation rate

    Sales lost by existing products ÷ sales of the new product

  • Net contribution of new product

    New product contribution − contribution lost from cannibalised products

Example

A new 1-litre pack sells 10,000 units (contribution ₹30 each) but takes 4,000 units from the 500 ml pack (contribution ₹25 each). Cannibalisation rate = 40%; net gain = 3,00,000 − 1,00,000 = ₹2,00,000.

  • Fair share draw: a new product should take sales from brands in proportion to their shares; drawing more from own brands signals excessive cannibalisation.

Key terms

Marketing analytics
Measuring and analysing marketing performance
MAPE
Mean absolute percentage error of forecasts
Relative market share
Brand share relative to the largest competitor
Customer lifetime value
Present value of future margins from a customer
Cannibalisation rate
Share of new-product sales taken from existing products

Quick revision

  • Descriptive, diagnostic, predictive, prescriptive analytics; Excel tools.
  • Forecasting methods; MAPE and tracking signal.
  • Market share, relative share, penetration, share of wallet; share decomposition.
  • Customer profitability, CLV, CAC, RFM.
  • BCG and GE matrices; cannibalisation rate and net contribution.

Important exam questions

Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).

Short-answer questions

  1. Q1.What is marketing analytics?
  2. Q2.Name two time-series forecasting methods in Excel.
  3. Q3.Define relative market share.
  4. Q4.State the simple CLV formula.
  5. Q5.What is RFM analysis?
  6. Q6.What is cannibalisation?

Long-answer questions

  1. Q1.Explain how Excel is used to interpret marketing data.
  2. Q2.Discuss methods of sales forecasting and measures of forecast accuracy.
  3. Q3.Explain customer profitability and lifetime value analysis with an example.
  4. Q4.Explain product portfolio and cannibalisation analysis.

Stuck on this unit?

Message SBS on WhatsApp for help with Marketing Analytics, or to ask about studying MBA at Synetic.

Planning your MBA in Ludhiana? See the MBA at SBS →

WhatsApp us