Ravindra BagaleCourses & study guides मराठी Track your progress

22. KPI in All Industries

22.14 Marketing and Digital Ads

By Last updated:

Performance-marketing interviews ask cost per order and return on ad spend (ROAS), and they ask whether the order row actually has a campaign id (Module 21.7). Impressions and clicks are easy to celebrate and easy to waste. CTR (clicks divided by impressions) is a diagnostic, not the decision.

KPI What the number means Who uses it Action it drives
CPA (cost per order) Spend divided by attributed orders. Media buyer A high CPA stops or rewrites that campaign.
ROAS Attributed sales divided by spend. Marketing lead Under 1 means the campaign returned less sales than it cost. Pause it in the practice file.
CTR Clicks divided by impressions. Only as a diagnostic A high CTR with no orders is a curious audience, not a result.

Fictional sample rows. One month. Every order below is attributed. Campaign names are labels, not real ads. Type these rows into a new sheet from cell A1, press Ctrl + T, and name the table tblAds (Table Design › Table Name).

Campaign City Impressions Clicks Spend Orders Sales
Diwali Pune Pune 1,00,000 2,000 ₹20,000 100 ₹80,000
Monsoon Nashik Nashik 20,000 800 ₹8,000 80 ₹40,000
Ganesh Kolhapur Kolhapur 80,000 400 ₹10,000 20 ₹15,000
Weekend Nagpur Nagpur 10,000 500 ₹5,000 50 ₹30,000
Clearance Solapur Solapur 5,000 250 ₹2,500 40 ₹12,000
New user Sambhaji Sambhaji Nagar 60,000 300 ₹9,000 15 ₹6,000

Excel formulas for the whole file (type them in empty cells below or beside the table):

KPI Excel formula Result Number format
CTR =SUM(tblAds[Clicks])/SUM(tblAds[Impressions]) 1.5% 0.0%
CPA =SUM(tblAds[Spend])/SUM(tblAds[Orders]) ₹179 ₹#,##0
ROAS =SUM(tblAds[Sales])/SUM(tblAds[Spend]) 3.36 0.00

Calculated columns (one result per row). Click the first empty column of the table, type the header, then the formula. The Table fills every row by itself.

New column Formula in the first row
CTR =[@Clicks]/[@Impressions]
CPA =[@Spend]/[@Orders]
ROAS =[@Sales]/[@Spend]

Expected result per row:

Campaign CTR CPA ROAS
Diwali Pune 2.0% ₹200 4.00
Monsoon Nashik 4.0% ₹100 5.00
Ganesh Kolhapur 0.5% ₹500 1.50
Weekend Nagpur 5.0% ₹100 6.00
Clearance Solapur 5.0% ₹62 4.80
New user Sambhaji 0.5% ₹600 0.67

One city with SUMIFS. CTR for Pune only:

=SUMIFS(tblAds[Clicks],tblAds[City],"Pune")/SUMIFS(tblAds[Impressions],tblAds[City],"Pune")

Result for Pune: 2.0%. Change "Pune" to a cell reference (for example $K$1) and add a drop-down, and the same formula answers for any city.

Good KPI Vanity metric
From the same rows Monsoon Nashik CPA ₹100 and ROAS 5.00 Diwali and Ganesh impressions (1,00,000 and 80,000)
Why Nashik spent less and returned five rupees of sales per rupee. Ganesh looks like a big campaign. It brought 20 orders at ₹500 each. Sambhaji returned 67 paise per rupee.

What the manager does. The Nashik buyer keeps Monsoon and may test a small extra spend in the practice file, still labelled fictional. The Pune buyer keeps Diwali (ROAS 4) but does not judge it by the 1,00,000 impressions. The Kolhapur buyer pauses Ganesh. Clearance Solapur is the quiet winner: CPA 2,500 ÷ 40 = ₹62, ROAS 12,000 ÷ 2,500 = 4.80, on only 5,000 impressions. Small is allowed to win.

Ravindra Bagale's Tip

Calculate ROAS only on orders that carry a campaign tag. Put untagged orders in an "Others" row and do not divide them by any campaign's spend.