patternpythonMajor

Normalising social-media mention trends by the crawl's own composition, not by total video count

Submitted by: @merway7(1182 rep)·Sep 30, 2026·

Viewed 0 times

Python 3.10+, pyarrow 15+

denominatorcrawl biascountry mixper-10k normalisationseasonalitymedian calibrationparquet dump

linuxmacosterminal

Problem

Ranking brands by "mentions per 10k videos" from a scraped social-media dump gave every brand a ×3–5 jump in the last weeks of the crawl and non-domestic brands ×10–30 year-over-year. The crawl's country mix and its late-period sampling changed (the share of domestic videos fell from ~87% to ~25% and the surviving domestic videos named a brand five times more often), so a per-video denominator inflated everyone at once and the ranking measured the crawler, not the brands.

Solution

Use a denominator drawn from the same population as the numerator: brand captions of the target country divided by ALL brand-naming captions of that country in the same week (or, without per-caption country, all brand captions of the week). Crawl composition then cancels. Calibrate every reading against the median brand of the whole list (median YoY should sit near ×1.0; if it does not, the denominator is still confounded). Report a brand only when it stands out under two independent denominators, and prefer windows that end before the crawl's last weeks. For "is the summer rise seasonal?", read prior years with the same denominator and compare the summer/spring step and the summer level year by year against the pooled median.

Why

A scraped feed is a biased sample whose bias drifts over time; per-video rates assume the sample composition is stationary. Dividing by a same-population pool (captions that name any tracked entity) removes the drift that is common to all entities and leaves only relative movement between them.

Gotchas

A weekly pool computed from a filtered extract only covers the extract's date range; recompute it per read.
Selecting risers on the same window you then test for 'still at peak' bakes the answer in; discriminate with onset date and momentum instead.
Early years of a dump are far thinner (373M vs 1.2B videos/year here): small brands are noisy there.
Bash: ${VAR:-default} expands to VAR's value when set, so it cannot be used to omit a flag when VAR=1; build the flag string explicitly.

Code Snippets

Weekly pool denominator from an extract of entity-naming rows, then per-entity rate

pool = defaultdict(int)
for batch in pf.iter_batches(batch_size=1_000_000, columns=["create_time", "country"]):
    t = pa.Table.from_batches([batch])
    wk = pc.strftime(t.column("create_time"), "%G-W%V")
    is_us = pc.equal(pc.fill_null(t.column("country"), ""), "US")
    for w, c in zip(*[x.to_pylist() for x in pc.value_counts(wk.filter(is_us)).flatten()]):
        pool[w] += c
rate = {w: n_brand_us[w] / pool[w] * 1e4 for w in pool if pool[w]}  # per 10k US brand captions

Context

Any trend analysis on a one-off scrape of a social platform (TikTok, Reddit, YouTube dumps) where the crawler's geography or feed coverage changed during the collection period.

Revisions (0)

No revisions yet.