DEV Community

Nazma Akter
Nazma Akter

Posted on

Analytics for 10 social networks: polling post stats by age, and why NULL isn't zero

#go #postgres #webdev #showdev

VeloxonHub Social has an Analytics page that shows views, reactions, comments and shares for everything you publish through it, across Facebook, Instagram, YouTube, LinkedIn Pages, Threads, Bluesky, Mastodon, Tumblr, DEV.to and WordPress, plus a daily follower count per account. None of these networks push numbers to you, so it's all polling. Here's how we keep that cheap and honest.

Two tables, nothing clever 

-- Latest snapshot per published target. NULL = the platform doesn't report it.
CREATE TABLE post_metrics (
    post_target_id UUID PRIMARY KEY REFERENCES post_targets (id) ON DELETE CASCADE,
    platform       social_platform NOT NULL,
    views BIGINT, likes BIGINT, comments BIGINT, shares BIGINT,
    error          TEXT,                 -- last fetch error, e.g. missing permission
    fetched_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- One follower count per account per day.
CREATE TABLE account_daily_stats (
    social_account_id UUID NOT NULL REFERENCES social_accounts (id) ON DELETE CASCADE,
    day               DATE   NOT NULL,
    followers         BIGINT NOT NULL,
    PRIMARY KEY (social_account_id, day)
);
Enter fullscreen mode Exit fullscreen mode

A "target" is one post on one account, so a post sent to Instagram and Facebook has two rows. We keep only the latest snapshot per target, not a history: the chart groups by the day a post was published, so it never needs yesterday's numbers.

NULL means "not reported", not zero

Networks disagree about what they share. Some report views, some don't; LinkedIn only reports stats for company Pages. If we stored a missing metric as 0, a network that hides views would drag the engagement rate down and look like it's failing.

So every metric is a pointer in Go and a nullable column in Postgres:

// PostStats is an engagement snapshot. Nil means the platform doesn't report it.
type PostStats struct {
    Views, Likes, Comments, Shares *int64
}
Enter fullscreen mode Exit fullscreen mode

The frontend drops a metric chip entirely when it's null, and the views tile shows a dash instead of a zero.

Poll by post age

Numbers move fast in the first days and barely at all after a month. Each network client that can report stats implements one small interface, and the worker asks the database which targets are due:

metricsCadence = repository.PollCadence{
    FreshFor: 3 * 24 * time.Hour, Fresh: 3 * time.Hour, // first 3 days: every 3 hours
    Stale:    24 * time.Hour,                           // then daily
    MaxAge:   30 * 24 * time.Hour,                      // then never
}
Enter fullscreen mode Exit fullscreen mode 
WHERE pt.status = 'published'
  AND pt.published_at > now() - $2::interval            -- MaxAge
  AND (pm.fetched_at IS NULL OR pm.fetched_at < now() -
       CASE WHEN pt.published_at > now() - $3::interval  -- FreshFor
            THEN $4::interval ELSE $5::interval END)     -- Fresh / Stale
ORDER BY pm.fetched_at NULLS FIRST, pt.published_at DESC
LIMIT $6
Enter fullscreen mode Exit fullscreen mode

One indexed query replaces a scheduler per post. The worker runs every 5 minutes, takes up to 100 due targets and fetches 8 at a time with a 30-second timeout each, so one slow network doesn't stall the rest. Several replicas can run it without locks: the worst case is a duplicate fetch, and saves are upserts.

Keep the last good numbers

Tokens expire and permissions get revoked. When a fetch fails we record the error but keep the last good numbers, so a post's views don't drop to nothing because a token lapsed:

ON CONFLICT (post_target_id) DO UPDATE SET
    views = CASE WHEN $8 = '' THEN EXCLUDED.views ELSE post_metrics.views END,
    -- likes, comments, shares the same way
    error = EXCLUDED.error, fetched_at = now()
Enter fullscreen mode Exit fullscreen mode

Followers: one row per day

Follower counts are checked every 12 hours and upserted on (social_account_id, current_date), so the second check of a day just corrects the first. A failed check only moves followers_checked_at, so we don't retry a broken account in a tight loop. The audience sparklines and the "+6,660 in 30d" figure come straight from these rows.

The chart doesn't skip quiet days

The daily series comes from generate_series left-joined to that day's published targets, so days with no posts are real zeros on the chart instead of missing bars that squeeze the axis.

Ranges and plans

7 and 30 days are on every plan. Longer ranges (90 days, 1 year) are part of the Grow add-on; the API checks entitlements before running the query, and the UI shows a lock on those buttons instead of an error.

If you're building something similar

  • Poll by age. Most engagement happens in the first few days.
  • Let the database decide what's due.
  • Store "not reported" as NULL and let the UI leave it out.
  • On a failed fetch, keep the last good numbers and record why.

Analytics is part of every VeloxonHub Social plan, with a 7-day free trial: https://social.veloxonhub.com. Happy to go into the per-network quirks in the comments.

Top comments (1)

Collapse
 
101beardo profile image
Tarun Sharma •

The keep-last-good-numbers upsert has one catch: a post deleted on the network, or a permission that's gone for good, keeps showing its old views and looks perfectly healthy. Surfacing fetched_at in the UI (a small "updated 12d ago" hint when error is set) lets people tell stale from current, since the numbers themselves look identical either way.