Build a social media analytics dashboard with historical snapshots

A dashboard can show today's follower count with one API request. Showing how that count changed over three months requires something else: a collection pipeline that remembers what it saw.

In this tutorial, we will collect account and post analytics through Outstand, store timestamped observations in PostgreSQL, and render a historical follower table. The same storage pattern supports post-performance charts and client exports without making every dashboard visit trigger another upstream request.

The examples are integration building blocks, not a deployed application. You supply a scheduler, a PostgreSQL adapter, and your existing account and published-post inventory. For network-by-network coverage, keep the social media analytics API reference alongside this tutorial.

1. Decide what each number means

Separate three concepts before creating tables:

A date range does not turn a current follower count into a historical follower count. Even an account response containing period engagement can contain current profile counts alongside it.

TikTok and Bluesky account metrics are current snapshots. YouTube channel statistics are lifetime values. Those endpoints do not provide historical account series through Outstand. Collecting today starts your own history today; it cannot reconstruct yesterday's missing observation.

Likewise, subtracting two post view observations measures the change between reads, not necessarily views that occurred during that exact interval. Reporting delays and corrections can affect the result. Never sum repeated lifetime counters to calculate period performance.

2. Confirm the two response envelopes

Create a server-side API key with analytics:read, connect the accounts, and retain Outstand account IDs and published post IDs. Keep that key out of browser code. Platform permissions and account roles still determine which metrics are available.

The account metrics endpoint is:

1GET https://api.outstand.so/v1/social-accounts/{id}/metrics?since=...&until=...

since and until are Unix seconds. Omitting them defaults to the last 30 days. Successful responses wrap account metrics in data:

1type AccountData = {
2	account_id: string;
3	network: string;
4	followers_count: number | null;
5	engagement: Record<string, unknown> | null;
6	period: { since: number; until: number; note?: string };
7	fetched_at: string;
8};
9type AccountResponse = { success: true; data: AccountData };

This intentionally models only the fields used below. Preserve the complete response data, including platform_specific, follower-growth availability, and period notes.

The post analytics endpoint has a different envelope:

1GET https://api.outstand.so/v1/posts/{id}/analytics

1type PostRow = {
2	social_account: { id: string; network: string };
3	platform_post_id: string;
4	metrics: Record<string, unknown> | null;
5	metrics_error?: { code: string; message: string };
6};
7type PostResponse = {
8	success: true;
9	post: { id: string };
10	metrics_by_account: PostRow[];
11	aggregated_metrics: Record<string, number>;
12};

There is no post-level data wrapper. Platform-specific post fields live inside metrics.platform_specific, not beside metrics.

Only request analytics for published Outstand posts. Drafts and scheduled posts return 400. A successful request can still contain an account row with metrics: null and a metrics_error; other account rows remain usable. Aggregated totals can be partial, so use per-account rows when calculating coverage.

3. Store observations without flattening away evidence

Use one row per account observation, or per post-account observation. Keep the original JSON alongside indexed dimensions:

1CREATE TABLE analytics_observation (
2	org_id text NOT NULL,
3	kind text NOT NULL CHECK (kind IN ('account', 'post')),
4	subject_id text NOT NULL,
5	account_id text NOT NULL,
6	job_key text NOT NULL,
7	network text NOT NULL,
8	observed_at timestamptz NOT NULL,
9	api_fetched_at timestamptz,
10	requested_since bigint,
11	requested_until bigint,
12	payload jsonb NOT NULL,
13	PRIMARY KEY (org_id, kind, subject_id, account_id, job_key)
14);
15
16CREATE INDEX analytics_observation_history
17	ON analytics_observation
18	(org_id, kind, account_id, observed_at DESC);

observed_at records when your collector received the response. Account api_fetched_at preserves Outstand's fetched_at, which is response-generation time, not a guarantee that the platform's underlying measurements are equally fresh. The post endpoint has no equivalent collection timestamp, so leave that column null.

The requested period and the returned payload.period are separate evidence. Neither should become the timestamp of a follower snapshot.

Make job_key stable across retries of one scheduled collection, including its reporting range. A later scheduled observation gets a new key. The primary key prevents duplicate inserts when a job is delivered twice; it does not erase prior snapshots.

Keep a metric-definition version in your reporting configuration as well. When a platform changes what a metric counts, annotate the boundary or split the series. Reusing the same JSON field name does not make measurements comparable across a definition change. Retain raw observations only as long as your customer agreement and data-retention policy allow.

4. Collect account and post rows server-side

Here, db.query is your adapter's parameterized PostgreSQL query method. Pass a secret-loaded key and an organization ID from trusted job configuration, never from an unverified browser request.

1type DB = {
2	query(sql: string, values: unknown[]): Promise<unknown>;
3};
4
5async function readOutstand<T>(path: string, key: string): Promise<T> {
6	const res = await fetch(`https://api.outstand.so${path}`, {
7		headers: { Authorization: `Bearer ${key}` },
8		signal: AbortSignal.timeout(20_000),
9	});
10	if (!res.ok) throw new Error(`Outstand HTTP ${res.status}`);
11	const body = await res.json() as T & { success?: boolean };
12	if (body.success !== true) throw new Error('Unsuccessful analytics response');
13	return body;
14}
15
16async function collect(
17	db: DB, key: string, orgId: string, jobKey: string,
18	kind: 'account' | 'post', id: string, since: number, until: number,
19) {
20	const encoded = encodeURIComponent(id);
21	const account = kind === 'account';
22	const path = account
23		? `/v1/social-accounts/${encoded}/metrics?since=${since}&until=${until}`
24		: `/v1/posts/${encoded}/analytics`;
25	const body = await readOutstand<AccountResponse | PostResponse>(path, key);
26	const observedAt = new Date().toISOString();
27	const rows = 'data' in body
28		? [{ accountId: body.data.account_id, network: body.data.network,
29			fetchedAt: body.data.fetched_at, payload: body.data }]
30		: body.metrics_by_account.map(row => ({
31			accountId: row.social_account.id, network: row.social_account.network,
32			fetchedAt: null, payload: row,
33		}));
34
35	for (const row of rows) {
36		await db.query(`
37			INSERT INTO analytics_observation
38			(org_id, kind, subject_id, account_id, job_key, network,
39			 observed_at, api_fetched_at, requested_since, requested_until, payload)
40			VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11::jsonb)
41			ON CONFLICT DO NOTHING`, [
42			orgId, kind, id, row.accountId, jobKey, row.network,
43			observedAt, row.fetchedAt, account ? since : null,
44			account ? until : null, JSON.stringify(row.payload),
45		]);
46	}
47}

Call collect for each due account and published post. Use bounded concurrency and a queue rather than launching every request simultaneously. Add runtime response validation at the boundary before production; TypeScript assertions do not validate external JSON.

Store failed post-account rows too. They explain a gap and retain the reason. A whole-request HTTP or network failure should enter a separate job-attempt log, not create an observation filled with zeroes. Retry database failures with the same job key; for a strict same-response retry, retain the fetched payload in the job until persistence finishes.

5. Query history and render an honest dashboard

For a daily follower table, choose the latest observation in each UTC day. Do not add all observations together:

1WITH daily AS (
2	SELECT DISTINCT ON ((observed_at AT TIME ZONE 'UTC')::date)
3		(observed_at AT TIME ZONE 'UTC')::date AS day,
4		observed_at,
5		(payload->>'followers_count')::double precision AS followers
6	FROM analytics_observation
7	WHERE org_id = $1 AND kind = 'account' AND account_id = $2
8		AND observed_at >= $3::timestamptz
9		AND observed_at < $4::timestamptz
10	ORDER BY (observed_at AT TIME ZONE 'UTC')::date, observed_at DESC
11)
12SELECT day::text, observed_at, followers
13FROM daily ORDER BY day;

Run this behind an authenticated application endpoint that derives org_id from the session and checks account access. Return the rows to your browser, not the Outstand key or unrestricted raw payloads.

For a period-engagement panel, select observations collected for the same requested range, then inspect the returned period and platform notes before labeling the result. Never add seven overlapping seven-day reports into a monthly total. Some platforms return their own daily breakdowns, but those require a platform-aware transform rather than treating the whole response as one daily measurement.

This small DOM renderer makes the history visible without a charting dependency. It expects a <table id="followers"> element:

1type DailyRow = {
2	day: string;
3	observed_at: string;
4	followers: number | null;
5};
6
7function renderFollowers(rows: DailyRow[]) {
8	const table = document.querySelector<HTMLTableElement>('#followers');
9	if (!table) return;
10	table.replaceChildren();
11	const caption = table.createCaption();
12	caption.textContent = 'Follower snapshots, UTC. Missing dates were not collected.';
13	const header = table.createTHead().insertRow();
14	for (const label of ['Date', 'Followers', 'Collected at']) {
15		const th = document.createElement('th');
16		th.scope = 'col';
17		th.textContent = label;
18		header.append(th);
19	}
20	const body = table.createTBody();
21	for (const row of rows) {
22		const tr = body.insertRow();
23		for (const value of [row.day,
24			row.followers == null ? 'Unavailable' : row.followers.toLocaleString(),
25			new Date(row.observed_at).toISOString()]) {
26			tr.insertCell().textContent = value;
27		}
28	}
29}

Zero renders as zero. Null renders as unavailable. Empty results need an explicit “No observations collected” state, not an empty growth chart. If you add a line chart, insert missing calendar days as null points and disable connecting gaps.

If you display growth between two follower observations, label it “net change between collected snapshots” and show both timestamps. A missing intermediate day lengthens the comparison interval. Negative changes are valid observations, not values to clamp to zero, and net change is not the number of newly acquired followers.

For post cards, retain the same rule: row.metrics?.views ?? null, never || 0. Show metrics_error.code for failed rows and metrics.platform_specific.unavailable_fields when supplied. Label a sum as “reported views, 3 of 4 accounts measured,” not a complete total. Cross-network reach is not deduplicated audience.

6. Schedule for freshness, cost, and failure

Start account snapshots daily. For recent posts, choose a few early observations, then daily collection, then a slower archive cadence. These are starting policies, not freshness guarantees. Let customers see collection time and configure their reporting needs.

Treat exceptions explicitly:

• Collect Instagram Story insights within approximately 24 hours of publishing. After expiry, preserve the last observation rather than expecting a backfill.
• Instagram data can lag up to 48 hours. Revisit recent supported reporting periods, retaining collection timestamps so revisions remain explainable.
• Give X a deliberate budget: account engagement can read up to 500 posts and consume upstream quota or pay-per-use charges. Dashboard refreshes should read your database.
• Skip optional Facebook include=retention unless needed; it adds an upstream request per video.
• Retry transient network failures and server errors with bounded exponential backoff and jitter. Honor Retry-After when supplied on throttled responses.
• Route expired-token or missing-scope errors to reconnection or permissions workflows. Do not retry them indefinitely. Treat metrics_expired as terminal for that observation source.

Track job failures separately from measurement availability. Alert on missed collection windows and prolonged staleness, while continuing to serve the last known data with its timestamp. A failed collection must never overwrite a successful count with zero.

You now have the essential reporting loop: collect, retain evidence, query at a defined grain, and render uncertainty honestly. From here, the same observations can feed CSV exports or a warehouse. The exportable social media reports solution explains that reporting use case; the Outstand documentation is the starting point for connecting the rest of your application.