Multi-Platform Social Listening & Competitive Intelligence

Twitter / X first: the highest-signal, lowest-friction wedge

X is the correct first platform for a Kenyan listening product. Kenyans on Twitter (KOT) is the loudest, most text-dense, most quote-able public square in the country — and X is the only one of the four with a usable, paid, official firehose-style API. Nail the end-to-end pipeline on X, then clone the pattern to the harder platforms.

The X ingestion decision (production posture)

X's official API was repriced into tiers. As of 2025 the practical options for a startup:

Phase-1 build order (X only)

The starter worker that does steps 2–4 ships alongside this blueprint as x_ingestor.py.

1Scraper & Data Ingestion Architecture

A resilient ingestion tier is an adapter pattern behind a queue, not a pile of scrapers. Each platform is one adapter implementing a common contract; everything after the adapter is platform-agnostic.

1a · Official APIs vs stealth automation vs managed providers

Net: only X gives you a clean pay-to-play public firehose. For the other three, a reputable managed provider is the realistic production source, with official APIs layered in where eligibility allows. Build for this asymmetry — don't assume parity across platforms.

1b · Resilience patterns (assume everything throttles and breaks)

• Adapter contract: every adapter implements fetch(rule, cursor) → (RawItem[], next_cursor). The orchestrator never knows whether bytes came from an API or a provider.
• Token-bucket rate limiting per source per credential (Redis), so one tenant's heavy rule can't exhaust a shared quota. Respect x-rate-limit-remaining/reset headers and back off proactively.
• Exponential backoff + jitter + circuit breaker per adapter. On repeated 429/403, open the breaker, park the rule, alert.
• Cursor checkpointing (since_id / pagination cursor persisted per rule) so a crash resumes instead of re-pulling.
• Credential/proxy pool rotation for provider/scraper paths; health-check and quarantine bad IPs/sessions.
• Idempotent ingest — dedup key (platform + native_post_id) means re-delivery is harmless. Critical for at-least-once queues.
• Raw-payload landing zone — store the untouched JSON in cheap object storage (S3/R2) before normalization, so you can replay/re-parse when a schema changes without re-scraping.
• Two ingest modes: streaming (X filtered stream for real-time) and polling (scheduled search for platforms without streams + historical backfill).

1c · Inferring location when the user never states it

Explicit geo is rare. Resolve location with a confidence-scored cascade, stored as structured geo with a geo_confidence field so the UI can filter on trust level:

Build a Kenya gazetteer (counties, towns, neighborhoods, malls, landmarks, common misspellings & Sheng aliases) as a Postgres table with trigram + full-text indexes. Combine signals in a small scoring function; never overwrite a high-confidence native tag with a low-confidence inference. Expose geo_confidence as a dashboard filter so a user can choose "Nairobi (confirmed)" vs "Nairobi (inferred)."

2Data Pipeline & Normalization Schema

A Tweet, a TikTok caption, and a Facebook post are the same abstract object: an authored public utterance, at a time, maybe a place, that mentions things and carries engagement. Model that abstraction once; push platform quirks into JSONB.

2a · Mapping disparate models to one canonical "Mention"

Normalization rule of thumb: promote to a typed column only what you filter, sort, or join on (tenant, platform, time, geo, sentiment, category). Everything else lives in raw JSONB and gets pulled forward later if it earns a query.

2b · PostgreSQL schema (JSONB + pgvector)

Postgres over Mongo here: you need multi-tenant relational integrity (tenants↔rules↔mentions), and JSONB flexibility, and vector search for semantic dedup/RAG — all in one engine via pgvector. Partition the hot table by time.

-- Tenancy ----------------------------------------------------------
CREATE TABLE tenants (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name         text NOT NULL,
  plan         text DEFAULT 'mvp',
  created_at   timestamptz DEFAULT now()
);

CREATE TABLE users (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id    uuid REFERENCES tenants(id) ON DELETE CASCADE,
  email        citext UNIQUE NOT NULL,
  role         text DEFAULT 'member'
);

-- What each tenant is listening for --------------------------------
CREATE TABLE keyword_rules (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id    uuid REFERENCES tenants(id) ON DELETE CASCADE,
  label        text,                          -- "Unga prices"
  terms        text[] NOT NULL,            -- ['unga','bei ya unga','maize flour']
  platforms    text[] DEFAULT '{x}',        -- start with X
  geo_filter   jsonb,                         -- {"country":"KE","city":"Nairobi"}
  cursors      jsonb DEFAULT '{}',           -- per-platform since_id / pagination
  is_active    boolean DEFAULT true,
  created_at   timestamptz DEFAULT now()
);

-- The canonical, platform-agnostic record --------------------------
CREATE TABLE mentions (
  id              uuid DEFAULT gen_random_uuid(),
  tenant_id       uuid NOT NULL,
  rule_id         uuid,
  platform        text NOT NULL,          -- x | facebook | instagram | tiktok
  native_post_id  text NOT NULL,          -- id on that platform
  content_hash    text NOT NULL,          -- sha256(normalized text) for dedup
  content         text,
  lang            text,
  author          jsonb,                     -- {handle, display, followers, verified}
  engagement      jsonb,                     -- {likes, shares, comments, views}
  media           jsonb,
  geo             jsonb,                     -- {country, city, lat, lng}
  geo_confidence  real DEFAULT 0,
  sentiment       real,                      -- -1..1
  sentiment_label text,                      -- pos | neu | neg
  categories      text[],                    -- ['RetailPricing','ConsumerSentiment']
  entities        jsonb,                     -- [{name:'Naivas',type:'BRAND',span:[..]}]
  embedding       vector(768),               -- pgvector: semantic dedup + RAG
  raw             jsonb,                     -- untouched source payload
  published_at    timestamptz NOT NULL,
  ingested_at     timestamptz DEFAULT now(),
  PRIMARY KEY (id, published_at)
) PARTITION BY RANGE (published_at);

-- Dedup + the indexes that make the dashboard fast -----------------
CREATE UNIQUE INDEX ux_mentions_dedup
  ON mentions (tenant_id, platform, native_post_id, published_at);
CREATE INDEX ix_mentions_tenant_time ON mentions (tenant_id, published_at DESC);
CREATE INDEX ix_mentions_categories  ON mentions USING GIN (categories);
CREATE INDEX ix_mentions_entities    ON mentions USING GIN (entities jsonb_path_ops);
CREATE INDEX ix_mentions_geo         ON mentions USING GIN (geo);
CREATE INDEX ix_mentions_embedding   ON mentions
  USING hnsw (embedding vector_cosine_ops);

2c · Deduplication (two layers)

• Exact: the unique index on (tenant, platform, native_post_id) kills re-delivery. content_hash catches cross-rule duplicates of the same post.
• Near-duplicate / repost: the same claim retweeted/reworded thousands of times shouldn't count as thousands of independent signals. Use the embedding with an HNSW cosine threshold to cluster near-dupes into a thread/cluster_id, so Share-of-Voice counts distinct narratives, not raw volume.

3AI Categorization & Sentiment Engine

A tiered pipeline: cheap deterministic filters first, small fast models in the middle, an LLM only where ambiguity actually requires it. Running an LLM on every post is how you go bankrupt.

3a · The NLP pipeline, stage by stage

• Preprocess: strip tracking params, normalize unicode/emoji, expand handles & hashtags into tokens, language-detect (fastText/CLD3). Keep the original; transform a copy.
• Deterministic extractors (free & precise): regex for prices (Ksh\s?\d[\d,]*, "bob", "/=", "k" for thousands), promo/discount terms, and a brand dictionary for the entities you already know you care about (Naivas, Quickmart, Carrefour, Chandarana, Cleanshelf…).
• Embeddings: a multilingual model (e.g. bge-m3 / multilingual-e5) that handles English + Swahili + code-switching, stored in pgvector. Powers dedup, semantic search, and zero-shot categorization.
• Categorization: embedding + zero-shot/few-shot against category prototype vectors → cheap, no per-call LLM cost. Falls back to an LLM only when top-2 categories are close.
• Sentiment: a fine-tuned transformer (start from a multilingual/AfriBERTa-style base) on labeled Kenyan data; LLM adjudicates low-confidence and sarcasm-prone cases.
• Entity extraction & linking: NER → link surface forms ("Naivas", "naivas supermarket", "@NaivasKenya") to a canonical entity in an entities table, so comparisons aggregate correctly.

3b · Auto-tagging into #RetailPricing, #LocalEvents, #ConsumerSentiment

Define each category as (a) a short natural-language definition, (b) a handful of labeled examples, and (c) a prototype embedding (mean of example embeddings). At ingest:

1. Cosine-match the post embedding to every prototype; assign categories above threshold (multi-label — a post can be both pricing and sentiment).
2. If the best matches are ambiguous, send only that post to an LLM with the category definitions for a final multi-label decision.
3. Periodically retrain prototypes from human-corrected labels (active learning) so the taxonomy sharpens over time.

3c · Kenyan context: Sheng, code-switching, sarcasm

Off-the-shelf English sentiment models fail badly on KOT. Concrete handling:

• Sheng/Swahili lexicon & normalizer: a maintained map — "fiti/poa/noma" (good), "mbaya/fake" (bad), "bei ya unga" (price of maize flour, a politically loaded cost-of-living signal), "bob/ganji/doo" (money), "unga" (flour/cash slang). Normalize before the model sees the text.
• KOT & cultural markers: "KOT" as a community signal; recognize ratio/drag patterns, "wue", "ata", "si" intensifiers, and emoji-as-sentiment (🤣 can flip polarity).
• Code-switching: never assume one language per post. Multilingual embeddings + a model fine-tuned on mixed English-Swahili-Sheng is non-negotiable.
• Sarcasm & negation: the hardest class. Route low-confidence / sarcasm-flagged posts to the LLM with few-shot Kenyan examples; bank the corrections as new training data.
• Aspect-based sentiment: "Naivas prices up but Carrefour parking is hell" is negative-price/Naivas + negative-experience/Carrefour. Tie sentiment to the entity+aspect, not the whole post — this is what makes competitive comparison trustworthy.

4High-Level Reporting & Comparison Logic

Reporting is aggregation over the canonical table, sliced by entity, category, geo, and time. Because sentiment is stored per entity+aspect, competitive matrices are a GROUP BY — not a second pipeline.

4a · Price comparison & competitive matrices (Naivas vs Carrefour)

1. Filter to comparable mentions: posts where categories @> '{RetailPricing}' and entities contains the target brands.
2. Extract structured prices from the deterministic price-regex output, attaching each price to (entity, product/aspect) — e.g. (Naivas, 2kg unga, Ksh 210).
3. Normalize units (per kg, per litre) so a 2kg vs 1kg quote is comparable; reject outliers via IQR to kill typos/jokes.
4. Aggregate to a matrix: rows = products, columns = brands, cells = median price + sample size + 7-day trend + crowd-sentiment.
5. Confidence: every cell carries n and a freshness timestamp — never show a single noisy post as a "price."

-- Share of Voice between competing brands, last 30 days, Nairobi
SELECT e->>'name' AS brand,
       count(*) AS mentions,
       round(avg(sentiment)::numeric, 3) AS avg_sentiment
FROM mentions m, jsonb_array_elements(m.entities) e
WHERE m.tenant_id = current_setting('app.tenant_id')::uuid
  AND m.published_at > now() - interval '30 days'
  AND m.geo->>'city' = 'Nairobi'
  AND e->>'name' = ANY(ARRAY['Naivas','Carrefour','Quickmart'])
GROUP BY brand ORDER BY mentions DESC;

4b · Visualization & aggregation

• Share of Voice — stacked area / 100% bar of mentions per brand over time; count distinct narrative clusters, not raw reposts.
• Sentiment time-series — rolling daily mean ±band per entity; annotate spikes with the driving cluster (the "why").
• Price index — median price line per brand per product, with crowd-sentiment overlaid.
• Geo heatmap — county/neighborhood intensity, gated by geo_confidence.
• Topic/entity co-occurrence graph — which brands get mentioned together, and in what sentiment.

5Tech Stack & Roadmap

Scalable, budget-conscious, boring-on-purpose. Every choice favors one operational surface and a clear upgrade path over premature microservices.

Phased roadmap