Excel with AI for Marketing Teams: Campaign Reports, ROI Analysis & Lead TrackingExcel with AI for Marketing Teams

Plan, measure, and optimize marketing in Excel with Microsoft Copilot and ChatGPT — report on campaigns, prove ROI, and track leads from first touch to customer.

Table of Contents

• 1. Campaign Reports
• 2. ROI Analysis
• 3. Lead Tracking

How to Use AI in This Tutorial

Every task below follows the same two-step AI pattern:

1. Microsoft Copilot (inside Excel) — open the Copilot pane from the ribbon (or press Alt + the Copilot key). Type your request in plain English; Copilot builds the table, pivot, or chart for you.
2. ChatGPT (alongside Excel) — when you need an exact formula or logic, paste your column layout into ChatGPT and ask for the precise Excel formula, then paste it back into the cell.

Use Copilot to build and explore, ChatGPT to get the exact formula. Both are shown in every section.

1. Campaign Reports

A campaign report summarizes spend and results per channel so you can compare performance. The base metrics are cost, impressions, clicks, and conversions.

Click-through rate (CTR):

=Clicks / Impressions

Cost per conversion:

=Spend / Conversions

Copilot prompt:

"From this campaign sheet, build a report comparing CTR and cost per conversion across all channels."

ChatGPT prompt:

"Write an Excel formula that calculates conversion rate from clicks and conversions."

=Conversions / Clicks

Simple explanation: Ratios like CTR and conversion rate normalize raw counts so a small campaign is comparable to a big one. Format these cells as percentages.

Common mistake: Leaving zeros in the denominator (e.g., a channel with 0 clicks). Wrap with IFERROR to avoid divide-by-zero:

=IFERROR(Conversions / Clicks, 0)

2. ROI Analysis

ROI (return on investment) shows whether a campaign earned more than it cost. The formula is gain minus cost, divided by cost.

ROI:

=(Revenue - Spend) / Spend

With Revenue in B2 and Spend in C2:

=(B2 - C2) / C2

Copilot prompt:

"Calculate ROI for each campaign and rank them from best to worst return."

ChatGPT prompt:

"Write an Excel formula that labels a campaign Profitable only if ROI is above 2 (200%)."

=IF((B2-C2)/C2 > 2, "Profitable", "Review")

Simple explanation: ROI of 2 means you got back 3× the spend (200% return). The IF flags anything below that bar for a second look.

Best practice: Keep Revenue and Spend in the same row per campaign so ROI formulas stay one line and Copilot can rank them instantly.

3. Lead Tracking

Lead tracking follows prospects through stages (New → Qualified → Won/Lost) and measures conversion so you know where leads die.

Leads in a stage (column C = Stage):

=COUNTIF(C:C, "Qualified")

Stage conversion (Qualified ÷ New):

=COUNTIF(C:C, "Qualified") / COUNTIF(C:C, "New")

Copilot prompt:

"Show lead counts by stage as a funnel and calculate the overall New-to-Won conversion rate."

ChatGPT prompt:

"Write an Excel formula that counts leads that are Won and came from the 'Webinar' source."

=COUNTIFS(StageColumn, "Won", SourceColumn, "Webinar")

Simple explanation: COUNTIFS filters on two columns at once — here it finds won deals from a specific source, so you can see which channels actually close.

Note: Add a Source column from day one; retrofitting it later is the most common lead-tracking headache.