ROAS vs ROI Explained: How to Measure Ad Profitability in Google Sheets

How-To • Google Sheets + Meta Ads

This is the hands-on guide to calculating ROAS and ROI in Google Sheets using exported Meta Ads data. You’ll build a simple, reliable worksheet that computes ROAS, CTR, CPC, CPA, and true ROI (profit and margin) without paid connectors. Use the included formulas, sample structure, and QA checks to make your reporting bulletproof.

📊 ROAS, CTR, CPC, CPA 💵 ROI with profit & margin 🧪 QA & governance

On this page

• Sheet setup
• Import & normalize data
• ROAS & ROI formulas
• Build the mini dashboard
• QA checks & troubleshooting
• Optional automation
• FAQ

1) Create your Google Sheets structure

Make a new spreadsheet with three tabs:

• Raw_Data — your pasted CSV from Ads Manager.
• Calc — helper columns and KPI math.
• Dashboard — the clean view to share.

Tip: Freeze header rows and protect Raw_Data from accidental edits.

2) Import & normalize Meta Ads exports

From Meta Ads Reporting, export CSV with at least: Date, Campaign, Spend, Clicks, Purchases/Leads, Revenue (if available), and Impressions (optional).

Paste and map

In Raw_Data, keep a consistent column order, e.g.:

Date | Campaign | Ad_Set | Ad | Spend | Clicks | Conversions | Revenue

Add a loaded_at column in Raw_Data to timestamp each import.

3) Add ROAS & ROI formulas in the Calc tab

In Calc, reference columns from Raw_Data using a formula like:

=QUERY(Raw_Data!A:H,"select A,B,C,D,E,F,G,H",1)

Core KPIs

Add these calculated columns (assuming Spend in E, Clicks in F, Conversions in G, Revenue in H):

CTR: =IFERROR(F2/E2,0) CPC: =IFERROR(E2/F2,0) ROAS: =IFERROR(H2/E2,0) CPA: =IFERROR(E2/G2,0)

ROI with product cost & overhead

Create a small assumptions table (e.g., on the right side of Calc):

AOV (Avg Order Value): =IFERROR(H2/G2,0) COGS % of Revenue: 0.40 Overhead % of Revenue: 0.10

Then add columns:

COGS = H2 * 0.40 Overhead = H2 * 0.10 Profit = H2 - (E2 + COGS + Overhead) ROI = IFERROR( Profit / (E2 + COGS + Overhead) , 0)

Adjust the percentages to your business. Lead gen can swap Revenue for an LTV estimate.

4) Build the mini dashboard tab

On Dashboard, create a compact summary using QUERY or PIVOT TABLE grouped by Date or Campaign:

=QUERY(Calc!A:Z, "select A, sum(E), sum(F), sum(G), sum(H), sum(E)/sum(F), sum(H)/sum(E), sum(E)/sum(G) where A >= date '"&TEXT($B$1,"yyyy-mm-dd")&"' and A <= date '"&TEXT($B$2,"yyyy-mm-dd")&"' group by A label sum(E) 'Spend', sum(F) 'Clicks', sum(G) 'Conversions', sum(H) 'Revenue', sum(E)/sum(F) 'CPC', sum(H)/sum(E) 'ROAS', sum(E)/sum(G) 'CPA'", 1)

Add KPIs at the top using =SUMIFS across the selected date range. Use conditional formatting to flag rising CPA or falling ROAS.

Share it: Set the Dashboard tab to view-only and share a link with stakeholders for a live view.

5) QA checks & troubleshooting

• Blank KPIs? A header changed in your export. Re-map columns or re-export with a saved template.
• Filters broken? Normalize dates to YYYY-MM-DD and ensure numeric columns aren’t text.
• KPIs drift week to week? Lock Raw_Data, add a staging tab for any manual transformations.
• ROI looks off? Check COGS/overhead assumptions and returns/refunds handling.

6) Optional automation

You can keep the manual paste flow (fastest) or add a light automation:

• Apps Script to fetch CSVs from a URL and append.
• Free connector add-ons to pull Meta data on a schedule.
• Regardless of method, keep math in Sheets so you retain control and transparency.

Skip the setup—use our ready-made sheets

Use the free ROI Calculator to get profit and margin instantly, and the SignalLift Dashboard for daily ROAS, CTR, CPC, and CPA.

Free ROI Calculator    Get the Dashboard

FAQ: ROAS & ROI in Google Sheets

What’s the difference between ROAS and ROI?

ROAS measures revenue ÷ ad spend (campaign efficiency). ROI includes all costs to reveal true profitability.

Do I need impressions or CPM to calculate ROAS or ROI?

No. You need Spend and Revenue for ROAS, plus costs (COGS/overhead) for ROI. Impressions can help diagnose CTR/CPC but aren’t required.

Can this handle multiple platforms?

Yes. Append TikTok/Google/LinkedIn exports to Raw_Data with a “Platform” column, then group by Platform in your queries.

How do I estimate ROI for lead gen?

Replace Revenue with Expected LTV per qualified lead or per closed deal, and include sales costs in Total Costs.

Does SignalLift compute ROI automatically?

The paid dashboard focuses on ROAS, CTR, CPC, and CPA. For ROI, use the free ROI Calculator template alongside the dashboard.