Tracking Earned Media Performance: A Sentiment & Reach Dashboard (Power BI + PostgreSQL)

🎯 Objective

To build a communications-team-facing dashboard tracking day-to-day media sentiment, reach, and branded vs. earned coverage — giving the team a fast, reliable read on how their story is actually landing, and who’s carrying it: the brand’s own channels, or independent third-party coverage.

🔗 Step 1: Data Connection — PostgreSQL (AWS RDS)

📡 Source:

• Centralized PostgreSQL database hosted on AWS RDS
• Power BI connected in Import Mode
• Measures in this model carry an MW_ prefix — a naming holdover from an earlier version of this report that ran on a Meltwater/Brand24 Google Sheets export. The prefix stayed even after the data source moved to the database, a good reminder that naming conventions tend to outlive the systems that inspired them.

🔍 Representative query shape:

sql

SELECT
    m.mention_id,
    m.date,
    m.reach,
    m.sentiment,
    m.source_domain
FROM final_mentions m
JOIN dim_source s     ON m.source_domain = s.source_domain
JOIN dim_narrative n  ON m.new_narratives = n.new_narratives
WHERE m.date BETWEEN '2026-01-01' AND '2026-06-30'

🧹 Step 2: Data Cleaning & Migration

Kept the model intentionally lean — no city or author breakdown needed here, since this report cares about what was said and where it ran, not who said it:

• Only three tables pulled in: final_mentions, dim_source, dim_narrative
• Sentiment values standardized to lowercase positive / negative / neutral strings
• Source domains cleaned and standardized so the same outlet didn’t fragment into multiple near-duplicate domain entries

📊 Step 3: Data Modeling

TableTypeDescriptionfinal_mentionsFactMention-level reach and sentimentdim_dateDimensionCalendar tabledim_sourceDimensionSource domain attributesdim_narrativeDimensionNarrative taxonomy

🔗 Relationships:

• One-to-many from each dimension into final_mentions, single-direction
• All 13 measures live in one dedicated Communication Measures table, kept separate from any physical data table

🧠 Step 4: DAX Measures & Calculations

📌 Sentiment counts — the building blocks everything else is layered on:

dax

MW_Positive Sentiments =
CALCULATE(
    COUNTROWS('final_mentions'),
    'final_mentions'[sentiment] = "positive"
)

MW_Negative Sentiments =
CALCULATE(
    COUNTROWS('final_mentions'),
    'final_mentions'[sentiment] = "negative"
)

📌 Net sentiment score — positive minus negative, a single number that swings intuitively as coverage shifts:

dax

MW_Sentiment_Adjusted = [MW_Positive Sentiments] - [MW_Negative Sentiments]

📌 Reach trend — standard prior-period comparison:

dax

MW_Reach Previous Month =
CALCULATE(
    [MW_Reach],
    PREVIOUSMONTH('final_mentions'[date])
)

MW_Reach % Change =
DIVIDE(
    [MW_Reach] - [MW_Reach Previous Month],
    [MW_Reach Previous Month]
)

📌 Branded vs. earned coverage — a hardcoded allowlist of owned domains, split against everything else:

dax

MW_Branded Count =
CALCULATE(
    COUNTROWS('final_mentions'),
    'final_mentions'[source_domain] IN {
        "up.com",
        "nscorp.com",
        "up-nstranscontinental.com"
    }
)

MW_Non-Branded Count =
CALCULATE(
    COUNTROWS('final_mentions'),
    NOT (
        'final_mentions'[source_domain] IN {
            "up.com",
            "nscorp.com",
            "up-nstranscontinental.com"
        }
    )
)

(Domain names shown above are illustrative placeholders, not the live client’s actual owned domains.)

📐 Step 5: Dashboard Layout & Visualizations

🖥️ Top Panel (KPI Cards):

• Positive / Negative / Neutral sentiment counts
• Net Sentiment score and Negative Sentiment %

📈 Reach Trend:

• Month-over-month reach with a % change indicator
• Conditional color formatting on the negative-sentiment share, using hex-coded thresholds (green / amber / red) rather than a plain number

🥧 Branded vs. Earned Split:

• Simple donut showing how much coverage is owned-domain vs. third-party — a quick read on how much of the conversation the brand controls directly versus how much is earned

🔄 Filters & Slicers

• Date range slicer
• Source domain multi-select
• Narrative filter

📤 Step 6: Publishing & Housekeeping

• Published to Power BI Service with scheduled refresh
• No display folders or measure descriptions configured yet — next on the list for a metadata cleanup pass
• Branded-domain list is currently hardcoded into two DAX measures rather than stored as a flag column on the source dimension — a small refactor that would make it a one-place update instead of two whenever a new owned domain gets added

📌 Insights Delivered

• Gave the comms team a fast, reliable daily read on sentiment direction
• Net Sentiment score made it easy to spot inflection points at a glance, instead of eyeballing three separate percentages
• Branded vs. earned split clarified how much of the total conversation was actually organic third-party coverage versus owned-channel content
• A clean, purpose-built fact table meant the whole report could be built and trusted quickly, without wrestling the data itself along the way