Key takeaways
- Power BI suits marketing analytics when data sits in several systems and the reports are for internal teams.
- Build five reports first: funnel, spend and CAC by channel, campaigns, pipeline and retention.
- Use a star schema with a shared Date table and, above all, one agreed channel mapping.
- Write measures once in DAX, with
DIVIDE, and refresh daily. - Choose a custom dashboard when the audience is public, the design is yours, or the data is out of a connector’s reach.
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.
Planning a dashboard?
Tell us which decisions your marketing and sales teams make each week, and we’ll talk through whether a BI tool or a custom dashboard fits.
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:
DIVIDEinstead 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 Monthonly 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.
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.
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.
