Topic Dashboards and data

Power BI for Marketing and Sales Analytics: A Setup Guide

The short answer

Power BI for marketing means bringing GA4, ad platform and CRM data into one model so you can see funnel, spend, cost per lead, pipeline and retention in one place. Start with five reports, a simple star-schema model and a handful of DAX measures, and set a daily scheduled refresh.

Written by

Vedika Didwania, Chief Marketing Officer

Published

Updated

Reading time

11 min read

Loose receipts, flyers and customer cards drift in on curved arrows towards one open book showing a bar chart and a line.
In this post11 min read
  1. Is Power BI good for marketing analytics?
  2. The five marketing reports to build first
  3. How do you connect marketing data to Power BI?
  4. A simple data model for marketing
  5. Example DAX measures
  6. Refresh, sharing and access
  7. Designing reports people actually use
  8. When is a BI tool enough, and when is a custom dashboard better?
  9. How we’d approach a marketing analytics setup

Key takeaways

Marketing data lives in too many places. Website behaviour is in GA4, spend is in Google Ads, Meta and LinkedIn, leads and deals are in the CRM, and targets are in a spreadsheet. Each tool reports its own version of the truth, and none of them can tell you what a paying customer from a given channel actually cost.

Microsoft Power BI is one way to bring those sources together into a single model and a single set of reports. This guide is a practical setup: the five reports to build first, how to connect each source, a simple data model, example DAX measures, refresh and sharing, and how to tell when a BI tool is enough and when a custom dashboard fits better.

Is Power BI good for marketing analytics?

Power BI is a good fit for marketing analytics when your data sits in several systems, your team already works in Microsoft 365, and the reports are for internal use. Its strengths are combining sources in one model, reusable measures and interactive filtering. Its weak spots for marketers are connectors for some ad platforms and the learning curve of DAX.

What it does well for marketing and sales teams:

  • One model, many sources. Join GA4, ad spend, CRM and finance data on shared dates and channels, so every report uses the same numbers.
  • Measures defined once. Cost per lead or win rate is written once in DAX and used in every report, so two pages never disagree.
  • Interactive filtering. Slicers and drill-through let a manager go from a channel to its campaigns to individual deals without asking for a new report.
  • Sharing inside Microsoft 365. Reports can sit in Teams and SharePoint, with access controlled by the same accounts.

The five marketing reports to build first

Start with five reports that together answer three questions: where buyers come from, what they cost and whether they stay. Build these before anything else. Each card names the question the report answers, what goes on the page, the data it needs and the trap to avoid.

5 reports

Where buyers come from

  1. The funnel report

    What it needs
    GA4 traffic and key events, and CRM leads, opportunities and customers, all mapped to the same channel list.
    Where it goes wrong
    Stages counted by different tools on different definitions, so the rates between them mean nothing.
  2. Spend and acquisition cost by channel

    What it needs
    Ad spend from each platform, and leads and customers from the CRM.
    Where it goes wrong
    Judging channels on cost per lead alone: cheap leads that never qualify make a channel look better than it is.
  3. Campaign performance

    What it needs
    Campaign-level spend and clicks, and campaign names that match between the ad platform and the CRM.
    Where it goes wrong
    Return on ad spend shown without revenue data behind it, or campaigns renamed halfway through the quarter.

Whether they close and stay

  1. The sales pipeline

    What it needs
    CRM opportunities linked to the lead, and the lead’s original channel kept on the record.
    Where it goes wrong
    Arguments about lead quality. This report ends them only if sales and marketing look at the same deals.
  2. Customer retention

    What it needs
    Customer records with a first purchase date, the acquisition channel and a churned flag, from the CRM or billing.
    Where it goes wrong
    A channel with cheap customers who leave quickly may be the most expensive one you have.

Planning a dashboard?

How do you connect marketing data to Power BI?

You connect marketing data to Power BI through Get Data in Power BI Desktop, using a built-in connector where one exists, and a database, a file export or a third-party connector where it doesn’t. Each source then goes through Power Query, where you clean it and shape it before it reaches the model.

Typical routes for each source:

  • GA4. Power BI’s Google Analytics connector reads GA4 properties for summary data. For event-level detail and long history, turn on GA4’s BigQuery export and use Power BI’s Google BigQuery connector. Check Microsoft’s connector documentation for current GA4 support before you pick one.
  • Ad platforms (Google Ads, Meta, LinkedIn). Check the Get Data list for your platform. Where there is no connector, the common options are a scheduled export to a SharePoint or OneDrive folder, a third-party connector, or a data pipeline that loads spend into a warehouse that Power BI reads.
  • CRM. Dynamics 365 and Salesforce have built-in connectors. For other CRMs, use the vendor’s own Power BI connector if it offers one, its API, or a regular export.
  • Targets and budgets. A small Excel table on SharePoint, so the marketing team can update targets without touching the model.

Whatever the route, give every source the same date and channel columns before loading it. For a step-by-step on each connector type, see our guide to connect Power BI to your data sources.

A simple data model for marketing

Use a star schema: a few fact tables holding the numbers, joined to shared dimension tables that hold the things you filter by. It keeps measures simple and filters consistent across every report.

Star schema

Five fact tables hold the numbers, one row per event or per day. Three dimension tables hold what you filter by, one row per thing. A channel mapping table lines them all up.

Fact: traffic
Date, channel, sessions, engaged sessions, key events.
Fact: spend
Date, campaign, impressions, clicks, cost.
Fact: leads
Lead ID, created date, channel, campaign, status.
Fact: opportunities
Opportunity ID, lead ID, created date, close date, stage, value, closed flag.
Fact: customers
Customer ID, first purchase date, acquisition channel, revenue to date, churned flag.
Dimensions
Date, one row per day, marked as the model’s date table. Channel, your agreed list of channels. Campaign, with its name, platform and channel.
Channel mapping
One row per label a source uses, turned into one agreed channel. The table that makes or breaks the model.

The table that makes or breaks the model is the channel mapping. GA4 says google / cpc, the CRM says “Google Ads”, and the ad platform says the campaign name. A small mapping table that turns every source’s label into one agreed channel list is what lets spend, leads and customers line up. Agree that list with sales before you build anything.

Example DAX measures

These measures cover most of the five reports. Create each one as a separate measure in Power BI Desktop. The table and column names match the model above, so rename them to fit yours.

Total Spend = SUM ( Spend[Cost] )

Leads = COUNTROWS ( Leads )

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

Qualified Leads = CALCULATE ( [Leads], Leads[Status] = "Qualified" )

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

New Customers = COUNTROWS ( Customers )

Marketing CAC = DIVIDE ( [Total Spend], [New Customers] )

Win Rate =
DIVIDE (
    CALCULATE ( COUNTROWS ( Opportunities ), Opportunities[Stage] = "Won" ),
    CALCULATE ( COUNTROWS ( Opportunities ), Opportunities[IsClosed] = TRUE () )
)

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

A few notes:

  • DIVIDE instead of / returns a blank rather than an error when the denominator is zero, which happens often when you filter to a small channel.
  • Marketing CAC here counts ad spend only. For full CAC, add salaries, tools and agency fees from a costs table.
  • Time comparisons such as Leads Last Month only work when the Date table is marked as the date table and related to each fact table.

Refresh, sharing and access

Build the reports in Power BI Desktop, then publish them to the Power BI service to share them and refresh them on a schedule.

  • Scheduled refresh. Once a day, early in the morning, suits most marketing data, because ad platforms and CRMs settle the previous day’s numbers overnight. Sources on your own network need Microsoft’s on-premises data gateway.
  • Licences. Desktop is free. Sharing reports with colleagues needs paid licences or capacity, and plans change, so check Microsoft’s current pricing page before you plan the rollout.
  • Row-level security. Use it when sales reps or regional teams should see only their own pipeline. It is set up once in the model and applies to every report.
  • Apps and workspaces. Publish the five reports together as one Power BI app, so people open one place instead of hunting for links.

Designing reports people actually use

A few rules keep marketing reports useful after the first month. Most of them are about trust: a number nobody can read as good or bad, or explain, stops being opened. Choosing which numbers earn a place comes first, and our guide to digital marketing KPIs covers that.

01

Make every number readable

  • One question per page, written as the page title.
  • Targets beside actuals, so every number reads as good or bad at a glance.
  • Definitions on the page: a short note under each KPI saying exactly how it is calculated.
02

Keep the way around predictable

  • The same slicers on every page (date and channel), synced across pages.
  • Drill-through for detail instead of crowding the summary page.

Our guide to interactive dashboards in Power BI covers layout, drill-through and storytelling in more depth.

When is a BI tool enough, and when is a custom dashboard better?

A BI tool such as Power BI is often enough when a small internal audience asks fixed questions of data that is already in one place or reachable through connectors. A custom dashboard fits better when the dashboard is public or customer-facing, must carry your own branding, sits inside your product, is heavy on maps, or must combine sources no BI connector reaches.

If the audience is the public, our guide to Power BI for public dashboards covers when embedding a report is enough. The questions that decide it are who will look at it, where it will live, what it must look like, where the data comes from and who will maintain it.

A BI tool such as Power BI and a custom dashboard compared
Compared onBI tool such as Power BIReports built in the toolCustom dashboardBuilt as part of your site or app
Who will look at itA known internal team, such as a marketing team of ten, signed in with work accounts. Sharing needs paid licences or capacity.Anyone you choose: thousands of members of the public, customers or partners, with no Power BI account.
Where it will liveInside Microsoft 365: the Power BI service, Teams or SharePoint. Power BI Embedded can also place reports in your own app, on a paid capacity.On your website or in your app, as part of the page.
A public audiencePublish to web makes a report public to anyone on the internet, with no sign-in. Microsoft warns that viewers can reach the detail-level data in the model, and an admin has to allow it. It does not support row-level security or mobile layouts.Designed for the public from the start: you decide which data ever leaves your server.
What it must look likeStandard charts and layouts, in Power BI’s own visuals.Your own design, charts and interactions.
Where the data comes fromMainstream tools with connectors: GA4, CRMs, Excel, databases.Your own product database, your APIs and sources no connector reaches.
Live dataDirectQuery with automatic page refresh. Microsoft is retiring real-time streaming: new streaming models can be created until 31 October 2027, and existing ones keep working (checked 20 October 2026).It can update as the data arrives, if it is built to.
Who will maintain itAn analyst who knows Power Query and DAX.A development team.
Best whenA small internal audience asks fixed questions of data that is already in one place or reachable through connectorsThe dashboard is public or customer-facing, carries your branding, sits inside your product, is heavy on maps, or needs data a connector cannot reach

Tailwebs built the dashboards for CEEW’s Green Finance Centre (GFC): the EV dashboard and Renewables India with the CEA. The EV dashboard includes a sales-penetration forecast to 2030 and RTO-level data on a map, and Renewables India generates automated reports.

How we’d approach a marketing analytics setup

We’d start with the questions, not the tool: which decisions the marketing and sales teams make each week and month, and which numbers those decisions depend on. Then we’d agree the definitions (what counts as a lead, a qualified lead and a customer) and the channel list with both teams, before connecting a single source. Only then would we choose between a BI tool and a custom build, based on the questions above. If a custom dashboard is the better fit, our dashboard development service covers that build.

02 FAQ

Questions, answered

Can Power BI connect to Google Analytics 4?
Yes. Power BI has a Google Analytics connector that can read GA4 properties, which is enough for summary reports. For event-level data and long history, export GA4 to Google BigQuery (a built-in GA4 feature) and connect Power BI to BigQuery instead. Check Microsoft's connector documentation for the current details before you choose.
Which marketing reports should you build in Power BI first?
Start with five: a funnel report from visits to customers, spend and acquisition cost by channel, campaign performance, the sales pipeline, and customer retention. Together they answer where buyers come from, what they cost and whether they stay. Add other reports only when someone asks a question these five cannot answer.
Is Power BI free for marketing teams?
Partly. Power BI Desktop, where you build reports, is a free download. Sharing reports with colleagues needs paid licences or capacity. Plans change, so check Microsoft's current Power BI pricing page before you budget.
How often should marketing data refresh in Power BI?
Once a day is enough for most marketing reports, because ad platforms and CRMs settle their numbers overnight and decisions are made weekly. Refresh more often only for live campaigns with large daily budgets, and check the refresh limits for your licence first.
When is a custom dashboard a better fit than Power BI?
When the dashboard is public or customer-facing, carries your own branding, sits inside your product, is heavy on maps, or must combine sources a BI connector cannot reach. For a small internal team asking fixed questions of data already in one place, a BI tool is often enough.
Have business queries?
Chat with us