Social Analytics Pipeline& Competitor Benchmark Dashboard
I designed and built an automated system that scrapes public data on 35 competitor accounts with Apify, merges it with first-partyInstagram, TikTok and YouTube analytics through two Claude Code agent skills and 7 Python scripts, and delivers a weekly dashboard that says where the channels stand against the category, and what to post next.
- Role
- Designed and built end to end: product, data model, pipeline, dashboard
- Stack
- Apify · Claude Code agent skills · Python · HTML / SVG dashboard
- Scope
- 3 platforms · 35 watched accounts · 7 hashtags
- Code
- 7 Python scripts · 2,046 lines · 2 skills
- Cadence
- Weekly, on a 14-day scrape window
- Run cost
- $0.45 in week 38, hard cap $2
- +40%Instagram post viewsweek 35 → week 38
- 31×YouTube median views per uploadsince late August vs earlier 2026
- 20.2%Share of voice on Instagramamong 20 accounts in the category
- $0.45Per weekly run61 posts scraped, 25 accounts benchmarked
From four tabs of gut feel to one weekly read
The channels were posting three times a week with no way to tell whether a number was good. The platforms only show your own data, and only in their own units. The objective: know where we stand against the category, and what to post next, in one read.
- Three analytics dashboards, three export formats
- Spreadsheets merged by hand every week
- No idea what “good” looks like in the category
- Posting decided by whichever post last felt good
- One schema for all three platforms, built automatically
- 35 competitor accounts benchmarked every week
- Every KPI an index against the category median
- A graded brief: what to post next, and why
Six layers, from raw exports to a decision
From raw data at the top, darkest, to the decision at the bottom, lightest. Pick a layer to see what it does and the code behind it, or run the whole week.
Three platforms, three export formats, one front door
Instagram, TikTok and YouTube each export differently: German column headers, TikTok dates without a year, YouTube as a zip. The ingester classifies every file by its headers, not its name, so a renamed download still lands in the right place.
- CSV, TSV, XLSX and YouTube zips
- Unknown numeric columns are reported, never dropped
- Unit assumptions are written into the data notes
# files are classified by their headers, not their names
kind = classify(headers) # posts | daily | video
if kind is None:
notes["unrecognised"].append(path.name)
continue
rows = map_columns(headers, SYNONYMS[platform])Who we compare against lives in exactly one place
Competitor accounts, hashtags and scrape depth sit in their own Claude Code skill. The analysis skill reads them fresh on every run instead of keeping a copy, so editing one line changes next week's benchmark and the two can never drift apart.
- 28 Instagram + 7 TikTok accounts, 7 hashtags
- My own accounts are deliberately absent
- Handles without a platform label are skipped, never guessed
# the watchlist is read at run time, never copied
src = TRENDREPORT / "SKILL.md"
if not src.exists():
warn("trendreport unreadable, falling back")
src = ROOT / "watchlist.json"
out = parse_watchlist(src) # accounts, hashtags, depthSpend is planned and capped before anything runs
Scraping is the only step that costs money, so the watchlist is turned into a deterministic call plan with a cost estimate and a hard cap. The agent executes the plan through the Apify MCP tools; every call also caps its billed items.
- Week 38: planned $1.24, spent $0.45
- Hard cap $2.00, --budget swaps in cheaper actors
- 61 usable posts from the public web
for c in calls:
c["est_usd"] = round(c["expected_items"] * c["unit_usd"], 4)
# cap billed items so a runaway actor can't overspend
c["callOptions"] = {"maxItems": c["expected_items"]}
if total > args.cap and not args.force:
return 3Fields are matched by meaning, not by column name
Actors rename their output fields without warning, and every actor names things differently. Items are flattened to dot-paths and matched against synonym tables, the same way the export ingester works, so my data and the scraped data end up in one schema.
- Carousel per-slide plays removed (they inflated engagement past 50%)
- Collab posts under co-author accounts archived separately
- 13 of my own posts folded in on the same metric
SYN = {
"views": ["playcount", "videoplaycount", "videoviewcount",
"viewcount", "views", "stats.playcount"],
"likes": ["diggcount", "likescount", "likecount", "likes"],
…
}Rates, shares and indices behind a noise band
The top of the dashboard carries no bare counts: every number is a share, a rate or an index where 100 is the category median. A change only becomes a finding once it clears a ±20% noise band, and breakout posts are scored by median absolute deviation so one viral post can't mask the next.
- 8 executive KPIs, each computed inside one platform
- Views are never summed across platforms
- Latest week vs a trailing baseline, not just vs last week
NOISE_BAND = 0.20 # +/- this vs baseline reads as "flat"
def delta_block(current, baseline):
pct = (current - baseline) / baseline
state = "flat" if abs(pct) < NOISE_BAND \
else ("up" if pct > 0 else "down")One self-contained page, and a brief that says what to post
The output is a single HTML dashboard with no external dependencies: it opens from disk, survives email and publishes as-is. Every recommendation is one concrete action with its evidence and a grade. The week's KPIs are archived and become next week's baseline.
- 8 graded recommendations in week 38
- Evidence badges: your data · scraped · platform
- A platform claim without a source and a date fails the build
html = TEMPLATE.replace("/*__PAYLOAD__*/", json.dumps({
"analysis": analysis,
"review": review_md,
"recs": recs, # each: action, evidence, badge, confidence
}))
archive(f"exec-{week}.json", tiles) # next week's baselineSystem map
What the data-led weeks delivered
Measured by the pipeline across weeks 35 to 38 of 2026, on the same three posts a week. Switch the metric; every one of them ends the window higher than it started.
2-week baselineW38 is the best week of the window: +30% against the two-week baseline, well outside the ±20% noise band.
2-week baselineW38 is the best week here too: +29% against the two-week baseline.
Interactions peaked at 8,968 in W36 and stayed above the W35 level in every week since.
The call the data made: Reels first
Six Reels against six carousels in the same four weeks. Reels drew 41% more views per post, and almost all of what grows a channel: 76% of new follows, 91% of shares and 78% of saves. The brief's first recommendation was to invert the mix.
First half of basic training, told by a recruit
Reel · 46 s · 15 Sep- 41,774
- views
- 22,008
- accounts reached
- 218
- shares + saves
Urban training, raw footage, English caption
Reel · 15 s · 3 Sep- 0.88%
- shares + saves per view, 3× the account
- 1,196
- likes
- 23
- follows, the most of any post
One account in twenty, a fifth of the attention
Share of voice is my views over the views of everyone in the watched set, inside one platform. These are the pipeline's own week-38 readings.
- Reach per post
- 105index · 100 = category median
- Posting cadence
- 3.0×3 posts a week vs a median of 1
- Accounts in the set
- 20me + 19 watched units
- Public engagement
- 5.5%likes + comments per view
- Posting cadence
- 2.0×2 posts a week vs a median of 1
- Followers
- 30.7Ksteady through the window
Posting three times a week against a category median of one is a lever fully in my control, and the benchmark made it visible. On Instagram my median post out-reaches the category median while I post three times as often.
Six decisions that make the numbers trustworthy
A dashboard is only useful if the people reading it believe it. These are the design rules the whole system follows.
- 01
Single source of truth
Who we compare against and how we analyse live in two separate skills that meet only at run time. Either can change without breaking the other.
- 02
No double counting
My accounts are folded in once, from first-party exports, on the same public metric. Listing them twice would drag the category median toward my own numbers.
- 03
Missing is not zero
No export, no views on a carousel, no YouTube benchmark yet: each shows as a labelled gap. A zero would read as a collapse.
- 04
Comparable or nothing
Competitors only expose public numbers, so the benchmark uses only likes and comments over views, computed the same way for everyone.
- 05
Budget as a guardrail
A deterministic plan, an estimate and a hard cap before a single actor runs. A typical week costs less than a coffee.
- 06
It compounds
Every week's KPIs are archived as the next baseline. Week four of the system knows more than week one.
A weekly workflow turned into a product: a defined user, one source of truth, a measurement framework, a budget guardrail and a release every Monday.