Marketing Campaign Analysis Dashboard
Microsoft Fabric | Power BI Development | Multi-Channel Ad Performance Analytics
01The challenge
A business running paid campaigns across Facebook, Instagram, and Pinterest had no unified way to compare performance across channels. Each platform's native reporting showed its own numbers in isolation, there was no single view to answer which channel was actually most cost-efficient, which campaigns or cities were converting best, or where ad spend should be reallocated. Marketing decisions were being made channel by channel instead of holistically.
02The data problem
Campaign data lived separately across each ad platform's own reporting, each with its own metrics structure, naming conventions, and level of granularity (channel, campaign, device, city, ad creative). There was no consistent way to compare cost per conversion or revenue per conversion across platforms, no unified CTR calculation, and no single source combining spend, clicks, impressions, and revenue into one comparable model.
03My approach
I built a Data Factory pipeline in Microsoft Fabric to pull campaign data directly from each ad platform on a scheduled basis, landing it in a Fabric Lakehouse as a central staging layer rather than relying on manual exports from each platform separately. From there, I used Dataflows Gen2 and SQL in the Fabric Warehouse to reconcile the platform-specific differences, standardizing metric definitions (impressions, clicks, CTR, conversions, spend, revenue) so Facebook, Instagram, and Pinterest data could be compared on equal footing for the first time.
Once the data was modeled into a consistent star schema, I connected Power BI natively to the Fabric Warehouse and built the report around three focused views: an Overview page for a fast executive read on impressions, clicks, conversions, cost, and profit with trend sparklines and month-over-month comparisons, an Impressions & CTR page drilling from channel down through city, campaign, and device level to surface exactly where engagement was strongest or weakest, and an Ads Cost & Revenue page built specifically to answer the efficiency question, cost per conversion and revenue per conversion broken out by channel, so spend could be judged against actual return rather than raw volume.
I built DAX measures for the drill-down hierarchies (Channel/City to Campaign to Device to Ad) so users could zoom from a top-line channel comparison down to individual ad-level performance without needing separate reports, and added a clicks-versus-conversion scatter plot to help surface which campaigns were converting efficiently relative to their click volume rather than just generating traffic. The finished dashboard is live and connected directly to the client's ad accounts through the Fabric pipeline, so performance data refreshes automatically rather than requiring manual updates.
04The result
The dashboard gave the client a true cross-channel comparison for the first time. It revealed that despite Pinterest driving the lowest click-through rate (0.99% vs. Facebook's 1.29% and Instagram's 1.42%), it delivered the highest revenue per conversion at $55.05, more than 75% higher than Facebook's $31.39, a signal that Pinterest traffic converted at a much higher value even though it generated less volume. That insight directly challenged an engagement-first view of channel performance and gave the client a concrete case for rebalancing budget toward value per conversion rather than raw click volume.
Tools & tech stack
- Microsoft Fabric Data Factory: used to build a scheduled pipeline pulling campaign data directly from Facebook, Instagram, and Pinterest ad accounts
- Fabric Lakehouse: staging layer for raw multi-platform campaign data before transformation
- Dataflows Gen2 & T-SQL (Fabric Warehouse): used to standardize metric definitions and reconcile platform-specific reporting differences
- Power BI Desktop & DAX: used to build drill-down hierarchies (Channel/City > Campaign > Device > Ad), cost/revenue-per-conversion measures, and trend comparisons
- Power BI Service: live connection to the client's ad accounts through the Fabric pipeline, keeping the dashboard continuously up to date
- Data Structure: star schema unifying cross-platform campaign metrics for direct comparison
Have data that isn't telling you anything yet?
Send me what you are working with — even if it is four spreadsheets and a CRM export that don't agree with each other.