Marketing Insights with AI in Excel: Campaign Metrics, ROAS & Budget Allocation using Copilot and ChatGPT

Read campaign performance and turn ad spend into decisions — with Microsoft Copilot and ChatGPT inside every step. This tutorial covers the marketing analytics domain: measuring what works, what wastes budget, and what to do next.

Table of Contents

• What Are Marketing Insights
• Building a Campaign Performance Table
• Key Marketing Metrics
• Finding What Works with AI
• Acting on the Data with AI

What Are Marketing Insights

Marketing insights are the "so what" behind campaign numbers — not just clicks, but whether those clicks made money and which channel deserves more budget. Excel holds the raw metrics; AI reads them fast.

Why it is useful: Teams drown in dashboards. Insights produce one clear sentence: "Double down on email, cut paid social."

Use AI from the start — ChatGPT to design the tracker:

"I run email, paid social, and search ads. Suggest the Excel columns and formulas I need to track CTR, conversion rate, CAC, and ROAS per campaign."

ChatGPT returns a column plan and the four formulas, so your tracker is correct before the first row is entered.

Building a Campaign Performance Table

One campaign per row with its spend and outcomes.

Keep Revenue separate from Spend — that gap is what you analyze.

Use AI — Microsoft Copilot to clean and check:

"Check this campaign table for rows where revenue is below spend or clicks exceed impressions, and flag them."

Copilot catches impossible rows (e.g., clicks exceeding impressions) and flags thin performers — here Paid Social earns $9000 from $5000 (ROAS 1.8×), profitable but the weakest of the three, so it surfaces as the one to scrutinize.

Key Marketing Metrics

Four formulas tell most of the story. Add them as columns:

CTR            = Clicks / Impressions
Conversion Rate= Conversions / Clicks
CAC            = Spend / Conversions
ROAS           = Revenue / Spend

Example (Email): CTR 4000/40000 = 10%; Conversion 320/4000 = 8%; CAC 2000/320 = $6.25; ROAS 16000/2000 = 8.0× (earn $8 per $1).

Simple explanation: CTR shows if the ad is interesting. Conversion Rate shows if the page closes. CAC is the price of a customer. ROAS shows if it paid off — above 1× means profit.

Use AI — Microsoft Copilot to compute and rank:

"Add CTR, conversion rate, CAC, and ROAS columns, then rank the campaigns from best to worst ROAS."

Copilot builds the columns and sorts the verdict for you.

Use AI — ChatGPT to explain a metric:

"My Paid Social ROAS is 1.8 but CAC is $33. Explain in plain terms whether this campaign is worth keeping."

ChatGPT clarifies that 1.8× ROAS with high CAC signals thin or negative margin — context a formula alone doesn't state.

Common Mistake: Comparing ROAS across campaigns with different margins. A 5× ROAS on low-margin goods can beat 3× on high-margin goods. Always pair ROAS with margin.

Finding What Works with AI

With metrics in place, the next job is spotting the winner and the waste.

Use AI — Microsoft Copilot for the comparison:

"Which campaign has the best ROAS and which has the highest CAC? Summarize which one I should pause."

Copilot reads the columns, computes the comparison, and returns a recommendation with a chart.

Use AI — ChatGPT to validate the read:

"Email ROAS 8.0, Paid Social 1.8, Search 6.0. Before I pause Paid Social, what could justify keeping it?"

ChatGPT may note Paid Social drives top-of-funnel reach that later converts via email — a nuance that prevents a costly cut.

Acting on the Data with AI

Insights only matter if they change budget. Move from read to action with AI.

Use AI — Microsoft Copilot to simulate reallocation:

"If I move 30% of Paid Social's budget to Email, show the projected new spend per campaign."

Copilot recalculates the split directly in the sheet.

Use AI — ChatGPT to draft the plan:

"My campaigns: Email ROAS 8.0, Paid Social 1.8, Search 6.0. Suggest a budget reallocation and the reasoning."

ChatGPT replies: "Shift 30% of Paid Social budget to Email and Search; Paid Social's CAC is ~5× higher with sub-2× ROAS, signaling inefficient targeting."

Note: Never paste customer-level private data into ChatGPT. Use aggregated campaign totals only.