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

27. 20 Industry Projects

27.13 Marketing and Digital Ads: Campaign ROAS and Cost per Order

By Last updated:

Example first. Ruhi Bagale runs fictional digital ad campaigns for a quick-commerce brand and wants to know which campaigns earn back their spend.

Marketing and Digital Ads project — in 30 seconds

What: A Marketing and Digital Ads tracker: one Table tblCampaigns, the calculated column ROAS, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real Marketing and Digital Ads report: Total Spend, Overall ROAS, Cost per Order are the numbers a manager reads first.

Tiny example: From 6 rows: Total Spend = ₹85,000, Overall ROAS = 3.95, Cost per Order = ₹89.95.

First look at the data (before → after):

Marketing and Digital Ads project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated column

Campaign ROAS
Diwali-Search 4.96
Diwali-Social 3.69
Weekend-Search 4.25
Weekend-Social 2.67
App-Install 1.90
Retarget 7.00

Expected result — KPI cells (expected results)

KPI Formula Result
Total Spend =SUM(tblCampaigns[Spend]) ₹85,000
Overall ROAS =SUM(tblCampaigns[Revenue])/SUM(tblCampaigns[Spend]) 3.95
Cost per Order =SUM(tblCampaigns[Spend])/SUM(tblCampaigns[Orders]) ₹89.95
Before table and formula as text

Before — Table tblCampaigns (fictional sample rows)

Campaign City Spend Clicks Orders Revenue
Diwali-Search Pune 25,000 4,200 310 1,24,000
Diwali-Social Pune 18,000 5,100 190 66,500
Weekend-Search Nashik 12,000 2,100 150 51,000
Weekend-Social Nagpur 9,000 2,600 80 24,000
App-Install Solapur 15,000 3,900 95 28,500
Retarget Kolhapur 6,000 900 120 42,000

Formula / click path

ROAS: =[@Revenue]/[@Spend]
Total Spend: =SUM(tblCampaigns[Spend])
Overall ROAS: =SUM(tblCampaigns[Revenue])/SUM(tblCampaigns[Spend])
Cost per Order: =SUM(tblCampaigns[Spend])/SUM(tblCampaigns[Orders])

Do it step by step:

  1. Type the data and press Ctrl+T; name the Table tblCampaigns in Table Design › Properties › Table Name.
  2. Add the column ROAS with =[@Revenue]/[@Spend]; the Table fills it down.
  3. On a KPI sheet type =SUM(tblCampaigns[Spend]) and the other KPI formulas from the table below.
  4. Click Insert › PivotTable › From Table/Range, put City in Rows and Revenue in Values (Sum).
  5. Click PivotTable Analyze › PivotChart › Clustered Column, then PivotTable Analyze › Insert Slicer › Campaign.
  6. Move the KPI cells, the chart and the slicer onto a sheet named Dashboard.

The brief. Build a campaign sheet: ROAS per campaign (revenue ÷ spend), total spend, overall ROAS (SUM ÷ SUM), cost per order, revenue by city, a chart and a Campaign slicer.

Calculated column formulas:

Column Formula
ROAS =[@Revenue]/[@Spend]

KPI cells (check your numbers):

KPI Formula Result
Total Spend =SUM(tblCampaigns[Spend]) ₹85,000
Overall ROAS =SUM(tblCampaigns[Revenue])/SUM(tblCampaigns[Spend]) 3.95
Cost per Order =SUM(tblCampaigns[Spend])/SUM(tblCampaigns[Orders]) ₹89.95

PivotTable (expected):

City Sum of Revenue
Kolhapur ₹42,000
Nagpur ₹24,000
Nashik ₹51,000
Pune ₹1,90,500
Solapur ₹28,500
Grand Total ₹3,36,000

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Column › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick Campaign and click OK.
  4. Insert a new sheet named Dashboard. In it, link a card cell to each KPI (for example =KPI!B2) and format it large and bold.
  5. Cut and paste the chart and slicer onto the Dashboard sheet.
  6. On the View tab, untick Gridlines in the Show group. Select the dashboard area and click Page Layout › Print Area › Set Print Area.

Template and guide: download the practice template Marketing_Digital_Ads_Template.xlsx and read the step-by-step template guide. The template's sample rows may differ from the rows above; the method is the same.

Practice task (try it before you open the answer):

  1. Which city has the highest sum of Revenue?
  2. Filter the Table on City = Pune (Data › Filter): what is Total Spend for Pune alone?
  3. Add the Clustered Column PivotChart and the Campaign slicer to your Dashboard sheet.
Answer

Pune (₹1,90,500). For Pune only (2 rows): Total Spend = ₹43,000. The chart shows 5 bars, one per city.

Ravindra Bagale's Tip

Check Total Spend by hand for two rows before you trust the dashboard. If your hand answer and the cell differ, the formula range is usually wrong.

Loading practice tasks...

  1. Total clicks (Easy): Add up Clicks in D2:D11.
  2. Average spend (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Google (Easy): How many rows have Platform "Google"?
  4. Largest clicks (Easy): The largest value in D2:D11.
  5. Clicks for Google (Medium): Total of D2:D11 where B is "Google".
  6. Google in Madrid (Medium): Total of D2:D11 where B is "Google" and C is "Madrid".
  7. Best in Google (Medium): Largest E2:E11 value where B is "Google".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Vikram Singh (Medium): The Clicks for "Vikram Singh" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Google (Hard): Share of D2:D11 that is "Google", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →