Marketing analytics · Social media
Social Media
Performance
2025 vs 2024 · year-on-year marketing comparison in Power BI
scroll ↓
The problem
Each KPI told part of the story.
When marketing performance was reviewed, the main KPIs were analysed separately. That made it hard to understand how results, investment and efficiency related to each other.
My aim was to bring them into a single analysis, so 2025 could be compared with 2024 on the same basis.
The goal
Was the investment working better than the year before?
I wanted to answer a simple question: were we getting better results from the investment made in 2025?
To answer it, I compared the main indicators with 2024 and put the KPIs in the same frame of reference: Impressions, Reach, Engagement, Clicks, CTR, Spend, Conversions, CPC and Conversions €. Filtered by platform, post type, campaign and month.
I wasn’t only looking at whether a figure had gone up or down, but trying to understand what lay behind that change.
metrics analysed together
ways to cut the data
less spend, same result
My approach
Comparing KPIs is more useful when they all speak the same language.
One of the first steps was to define a common frame for the indicators. I reviewed the metrics, their definitions and how they were calculated, to make sure the comparison between years was consistent.
That way I could analyse different KPIs without losing sight of what each one was measuring.
Current value vs previous year
Each KPI is shown next to the same figure from last year. That way I can see straight away whether something improved or got worse, without looking up the comparison figure separately.
Monthly sparkline
A small trend chart inside each card. It shows whether a KPI had been rising or falling for a while, without changing page to check.
Boosted vs organic
A filter to separate posts with paid promotion from those without. Mixing them can hide a good organic result behind a paid campaign, or the other way round.
Data model
I organised the data in a star schema.
To analyse the results from different perspectives, I structured the data in a dimensional model: a fact table (fact_table) and seven dimensions: Calendar, Platform, Content, Campaign, Post type, Target audience and Boosted. That makes it possible to cut the information by platform, content type, campaign and the other variables in the analysis.
This structure also let me keep the Power BI calculations tidier and made the later analysis easier.
I didn’t build the model because «a star schema is good practice». I built it because I needed to analyse the KPIs from different perspectives without duplicating logic or losing consistency.
Schema
Visualisation in Power BI
Nine KPIs to read performance from the same perspective.
With the model ready, I brought the main indicators together in a single dashboard. The idea was that someone could move from the overview to the detail without switching criteria between metrics. The dashboard compares results with the previous period and makes it quick to spot where the main changes happen.
At first sight, the results looked worse. Compare two figures and the story changes.
If I look only at impressions, the result seems negative:
Impressions −12.8%
But compared with the investment:
Spend −26.3%
The reading changes: investment fell more than impressions. That’s why looking at one metric in isolation could lead to an incomplete conclusion.
This was one of the points of the analysis that interested me most: a figure can look negative until we put it in context.
Key findings
Volume dropped, but that doesn’t automatically mean efficiency got worse.
To avoid interpreting the results through a single metric, I separated two concepts:
Volume
Measures how much result was achieved. It’s useful to know whether activity rose or fell, but on its own it doesn’t explain how much it cost to achieve.
Efficiency
Relates the result achieved to the investment made. It shows whether we’re using resources differently from the previous period.
That’s why, in this analysis, I didn’t stop at «results have gone down». The next question was: how much did we invest to get them?
Tools
The tools I used
To build this analysis I worked mainly with:
Power BI
To model the data, create the measures and build the full dashboard.
DAX
To write the measures that compare each indicator with the previous year.
Disconnected table
To change the period analysed without breaking the comparison between years.
Star schema
To organise the data around a central table and seven dimensions, and cut it from different angles.
Sparklines
To see each KPI’s trend at a glance, without leaving the card.
YoY comparison
So every metric is always read next to its value from the previous year.
A technical detail
A disconnected table let me simplify how the KPIs read.
One of the parts of the build that interested me most was using a disconnected table in Power BI to control which KPI was shown in certain visuals. That let me keep the same visual structure and switch the indicator without creating a different chart for each metric.
It’s a relatively small technical detail, but it helped me keep the dashboard cleaner and, above all, avoid repeating visuals unnecessarily. I also learnt that nine KPIs on a single page only work if the design has a clear hierarchy. Without it, the density of information paralyses the user instead of guiding them.
From «results fell» to understanding what was really going on.
This project let me test something I try to apply every time I work with data: an isolated metric rarely tells the whole story.
By putting the indicators in context I could tell a drop in volume apart from a change in efficiency, and better understand what might be behind the results.
For me, that’s one of the most interesting parts of a data analyst’s work: moving from looking at numbers to finding the question behind them.
Interactive dashboard published in the Power BI Service
Full Power BI portfolio