gianhossle.com/projects/social-analytics-pipeline
← all projects
in weekly useproject · 2026sys / social-analytics

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.

spec sheetrev. W38
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
⟲ drag to rotate · scroll to zoom
Stabilise the signal, then decide.The gimbal keeps the shot level whatever the airframe does. The pipeline does the same for data: three platforms, three formats, one comparable metric.
  • +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
01 / objective

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.

before
  • 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
after
  • 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
02 / architecture

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.

layer 1 / 6
$instagram 2 files · tiktok 2 files · youtube 1 file · unmapped columns: 1, reported
L01 · Sources · ingest.py

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])

System map

inputs
Instagram exportposts · daily
TikTok exportposts · daily
YouTube exportvideos · watch time
Apify actors4 calls · public data
engine · Claude Code
trendreportskill · the watchlistwatchlist.jsonSKILL.md
social-channel-reviewskill · 7 scripts · 2,046 lineswatchlistplan_externalingestnormalize_externalanalyzeexec_metricsbuild_dashboard
outputs
Dashboardone self-contained HTML
Weekly brief8 graded actions
ArchiveKPIs → next baseline
03 / progress

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.

instagram
+40%62,419 → 87,559 · lifetime views of the posts published each week
62,419W35
73,790W36
60,604W37
87,559W38

2-week baselineW38 is the best week of the window: +30% against the two-week baseline, well outside the ±20% noise band.

+422new Instagram followers in four weeks
1.55MInstagram account views in 28 days
31×YouTube median views per upload: 1,070 for the four since 25 August, 35 for the 40 before

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.

reelscarousels
Median views
25,59118,138
New follows
5116
Shares
59756
Saves
18653
Best reach

First half of basic training, told by a recruit

Reel · 46 s · 15 Sep
41,774
views
22,008
accounts reached
218
shares + saves
Best amplification

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
04 / position

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

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.

05 / engineering

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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 06

    It compounds

    Every week's KPIs are archived as the next baseline. Week four of the system knows more than week one.

debrief

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.

  • data pipeline design
  • web scraping · Apify
  • AI agent orchestration
  • Python
  • KPI design
  • dashboard UX
← all projects