Unit 1: Understanding data and market analysis
Marketing Analytics notes · PTU syllabus (MBA 961-18)
On this page
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
Topic 1
Introduction to marketing analytics
- Marketing analytics: measuring, managing and analysing marketing performance to maximise effectiveness and return on investment.
- Prescriptive
What should we do? Budget allocation, pricing optimisation
- Predictive
What will happen? Sales forecasts, churn and response models
- Diagnostic
Why did it happen? Driver analysis, attribution
- 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.
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).
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.
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.
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.
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.
Topic 4
Customer choice, profitability and lifetime value
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).
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).
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.
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.
Topic 6
Cannibalisation analysis
- Cannibalisation: sales of a new product taken from the firm's existing products.
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
- Q1.What is marketing analytics?
- Q2.Name two time-series forecasting methods in Excel.
- Q3.Define relative market share.
- Q4.State the simple CLV formula.
- Q5.What is RFM analysis?
- Q6.What is cannibalisation?
Long-answer questions
- Q1.Explain how Excel is used to interpret marketing data.
- Q2.Discuss methods of sales forecasting and measures of forecast accuracy.
- Q3.Explain customer profitability and lifetime value analysis with an example.
- 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.
