Power BI for Marketing: Funnel & Campaign Reporting

Marketing data is scattered by design: ad spend lives in Google and Meta, leads live in your CRM, revenue lives in billing, and traffic lives in analytics. A good Power BI marketing report stitches those sources into one model so you can answer the only question that matters — which spend turns into pipeline and revenue. This guide walks through the metrics, the data model, the DAX, and the layout that get you there.

Start With the Metrics That Decide Budget

Before you touch Power BI, decide what the report has to answer. For most marketing teams it comes down to three things: where money is going, what it is returning, and where the funnel leaks. That maps to a short, opinionated metric set.

• Top-of-funnel volume: impressions, clicks, sessions
• Engagement rates: click-through rate (CTR), bounce rate, conversion rate
• Lead economics: cost per lead (CPL), cost per acquisition (CPA)
• Return: revenue, return on ad spend (ROAS), customer acquisition cost (CAC) vs. lifetime value
• Funnel progression: impression → click → lead → MQL → SQL → customer

The discipline here is to track rates and ratios, not just raw counts. A campaign with the most clicks can still be the worst performer once you divide by spend. Build the dashboard around the derived measures and let the raw numbers sit behind them.

Get the Sources in the Door With Power Query

Every source comes in differently, so the first job is normalization in Power Query, Power BI's data-prep engine. Pull each platform in as its own query — Google Ads, Meta/Facebook Ads, LinkedIn, your CRM export, and a revenue or billing extract.

For each ad source, do the same cleanup so they can be stacked:

• Rename columns to a shared schema: Date, Channel, Campaign, Cost, Impressions, Clicks, Conversions
• Set explicit data types (dates as Date, money as Decimal Number, IDs as Text)
• Add a Channel column if the source doesn't have one (e.g. hardcode "Google Ads")
• Trim and standardize campaign names so Spring_Promo and spring promo don't split into two rows

Then use Append Queries to combine all the ad platforms into a single Spend table. Keep CRM leads and revenue as separate tables — they describe different grains (a lead, a deal) and shouldn't be jammed into the spend table. The goal of Power Query is a handful of clean, consistently named tables, not one giant flattened sheet. For a deeper walkthrough of the cleanup steps, see Power Query data cleaning.

Model It as a Star Schema, Not One Big Table

This is the step that separates a report that scales from one that breaks the moment you add a slicer. Don't merge ads and CRM into a single wide table. Instead, build a star schema: fact tables for the events (spend, leads, revenue) connected to shared dimension tables (date, channel, campaign).

Connect each fact to the dimensions with one-to-many relationships, where the dimension is the "one" side. Now a single Date or Channel slicer filters spend, leads, and revenue at once, because they all hang off the same dimensions. That shared-filter behavior is the entire payoff of the model.

A few rules keep this clean:

• Always build a dedicated Date table and mark it as a date table (Table tools → Mark as date table). Don't rely on Power BI's auto date/time hierarchy for cross-table time intelligence.
• Keep relationships single-direction (one-to-many) unless you have a specific reason; bidirectional filters cause ambiguity fast.
• Mind your join keys. Channel-and-campaign attribution works only when ad campaign names map cleanly to a CRM source/campaign field — invest in that mapping upstream.

Write the Measures Once, Reuse Everywhere

Put your KPIs in DAX measures rather than calculated columns. Measures respond to whatever is filtered on the page, so the same ROAS measure works on a channel chart, a campaign table, and a monthly trend without rewriting anything.

Total Spend = SUM ( Spend[Cost] )

Total Revenue = SUM ( Revenue[Amount] )

Total Leads = COUNTROWS ( Leads )

Customers = DISTINCTCOUNT ( Revenue[CustomerId] )

Clicks = SUM ( Spend[Clicks] )

CTR = DIVIDE ( [Clicks], SUM ( Spend[Impressions] ) )

Cost per Lead = DIVIDE ( [Total Spend], [Total Leads] )

Cost per Acquisition = DIVIDE ( [Total Spend], [Customers] )

ROAS = DIVIDE ( [Total Revenue], [Total Spend] )

Conversion Rate = DIVIDE ( [Total Leads], [Clicks] )

The non-negotiable habit: use DIVIDE(), never the / operator. DIVIDE returns blank (or a value you specify) when the denominator is zero, so a brand-new channel with no spend won't blow up your ROAS column with errors.

For trend and pacing visuals, lean on time-intelligence functions built on your Date table:

Spend MTD = TOTALMTD ( [Total Spend], 'Date'[Date] )

Spend Last Month =
CALCULATE ( [Total Spend], DATEADD ( 'Date'[Date], -1, MONTH ) )

Spend MoM % =
DIVIDE ( [Total Spend] - [Spend Last Month], [Spend Last Month] )

Keep the measure count lean. You rarely need a separate measure per channel — let a Channel legend or column split do that work, and use the visual's "Show value as → Percent of grand total" option for share-of-spend views instead of authoring ten near-identical measures.

Lay Out the Report for How Marketers Actually Read

A marketing report usually wants two or three pages, each with its own job, rather than one crowded canvas.

• Overview page: KPI cards across the top (Spend, Leads, CPL, CPA, ROAS), a spend-vs-revenue trend line, and a channel-performance bar chart. This is the page a manager opens for ten seconds.
• Funnel page: a funnel visual (impressions → clicks → leads → MQL → SQL → customers) with conversion-rate labels between stages, plus a matrix or small-multiples view by channel so you can see where each channel drops off.
• Channel / campaign detail: a matrix with channels as rows, campaigns nested underneath, and Spend, Leads, CPL, CPA, ROAS as columns — sortable, with conditional formatting (red CPA, green ROAS) so outliers jump out.

Layout principles that pay off:

• Put a Date range slicer and a Channel slicer in a consistent spot on every page.
• Lead with the answer (cards and ROAS), and put the detail below it.
• Use color sparingly and consistently — one brand color, one "good" green, one "bad" red. A consistent palette is easier to read and looks finished; you can generate a reusable JSON theme with the free Power BI theme generator.
• Add tooltips to charts so hovering a campaign bar reveals its full metric set without cluttering the visual.

Follow a Realistic Build Order

If you're starting from scratch, the sequence matters more than any single trick:

1. List the questions and the metrics that answer them.
2. Clean and normalize each source in Power Query; append the ad platforms.
3. Build the star schema with a proper Date table and shared dimensions.
4. Author the core measures (CPL, CPA, ROAS, conversion rate) and test them on a simple table.
5. Build the Overview, Funnel, and Detail pages.
6. Apply a consistent theme and tidy the layout last.

Build the whole thing in Power BI Desktop, which is free. You only need a paid license — Power BI Pro lists at roughly $14 per user per month as of 2026, though current pricing and bundling vary, so check Microsoft's pricing page — when you want to publish to the Power BI Service and share live reports with colleagues. The model, measures, and visuals all happen locally in a .pbix (or .pbip) file at no cost.

Let Us Build It For You

If the modeling and DAX are more than you want to take on, that's exactly what Instant PowerBI is for. Send us your raw exports — ad-spend CSVs, a CRM extract, a revenue file — and we'll hand back a finished, branded Power BI report with the funnel modeled, the CPL/CPA/ROAS measures written, and the pages laid out the way this guide describes. It opens in the free Power BI Desktop with your data already embedded, so there's no path setup or connection juggling on your end. Send your data and get your report.