🔧 Tools & Platforms
PowerBI + Claude — Intelligent Follower Dashboard
A hybrid analytics system that pairs PowerBI's visualization engine with Claude's natural language analysis to build follower and audience dashboards that don't just show data — they explain it. PowerBI handles the heavy lifting of data ingestion, modeling, and charting. Claude layers on narrative intelligence: weekly summaries, anomaly detection, trend explanations, and actionable recommendations written in plain language. The result is a dashboard that a content creator can glance at and immediately know what happened, why, and what to do next.
2
Minutes
4
Concepts
+15+30
Read+Quiz
1
How It Works
Data Ingestion Pipeline
Social media data flows into PowerBI through multiple paths depending on the platform:
- API-first — Twitter/X API v2, Instagram Graph API, LinkedIn Marketing API, YouTube Data API v3 all return JSON that a Python script normalizes into a common schema
- Metricool — aggregates cross-platform metrics and exports CSV or connects via API, reducing the number of individual platform integrations
- CSV fallback — for platforms without API access, manual or scheduled exports land in a OneDrive folder that PowerBI auto-refreshes from
- Power Automate — scheduled flows can call APIs on a timer, transform the JSON, and push rows into a Dataverse table or SharePoint list that PowerBI reads natively
PowerBI Data Model
The core model uses a star schema with a central Posts fact table and dimension tables for Platforms, ContentTypes, DateDimension, and Campaigns.
DateDimension ──┐
├── Posts (fact table)
Platforms ──────┤ - post_id, date_key, platform_key
│ - impressions, reach, engagement, saves, shares
ContentTypes ───┤ - followers_gained, followers_lost
│ - content_type_key, campaign_key
Campaigns ──────┘DAX Measures
Key calculated measures that power the dashboard visuals:
dax
// Engagement Rate — interactions divided by impressions
Engagement Rate =
DIVIDE(
[Total Engagements],
[Total Impressions],
0
)
// Follower Growth Rate — period-over-period percentage change
Follower Growth Rate =
VAR CurrentFollowers = [Total Followers]
VAR PreviousFollowers =
CALCULATE(
[Total Followers],
DATEADD(DateDimension[Date], -7, DAY)
)
RETURN
DIVIDE(CurrentFollowers - PreviousFollowers, PreviousFollowers, 0)
// Best Posting Day — day of week with highest average engagement
Best Posting Day =
TOPN(
1,
SUMMARIZE(
Posts,
DateDimension[DayOfWeek],
"AvgEngagement", AVERAGE(Posts[Engagements])
),
[AvgEngagement], DESC
)
// Content Type Performance — which format drives the most reach
Top Content Type =
TOPN(
1,
SUMMARIZE(
Posts,
ContentTypes[TypeName],
"AvgReach", AVERAGE(Posts[Reach])
),
[AvgReach], DESC
)Claude Narrative Layer
Claude receives a structured data payload (JSON or CSV) and returns three types of intelligence:
Weekly Summary Prompt:
You are analyzing social media performance for a personal brand.
Here is this week's data across all platforms: {json_payload}
And here is last week's data for comparison: {prev_json_payload}
Write a 3-paragraph executive summary:
1. What changed significantly vs. last week (cite specific numbers)
2. What's driving performance — identify the top post, worst post, and any anomalies
3. Two specific actions for next week based on this data
Tone: direct, specific, no filler. Use actual numbers from the data.Anomaly Detection Prompt:
Here are daily follower counts for the past 30 days: {time_series}
Identify any days where the change was >2 standard deviations from the mean.
For each anomaly, suggest what might have caused it (viral post, external mention, algorithm change).
Return as JSON: [{"date": "...", "change": N, "severity": "high|medium", "hypothesis": "..."}]Content Recommendations Prompt:
Here is performance data grouped by content type, posting time, and platform: {grouped_data}
Based on this data, recommend:
1. The 3 content formats to double down on
2. The 2 formats to stop producing
3. Optimal posting schedule (day + time) per platform
Support each recommendation with a specific data point.