Synetic College Logo
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
Key formulasCustomer and market metrics

  • Market share

    Your sales / total market sales × 100

  • Customer lifetime value

    Margin per period × retention / (1 + discount − retention)

  • Cannibalisation rate

    Sales lost by old product / sales of new product

  • Customer profitability

    Revenue − cost to serve

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

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

3

Topic 3

Market share and performance indicators

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.
4

Topic 4

Customer choice, profitability and lifetime value

  • 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

  • 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.
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.

WhatsApp us