buffer-analytics

name	buffer-analytics
description	Collect and analyze social media data from Buffer in a local SQLite database. Stores your full post history and metrics across connected channels (such as LinkedIn, X/Twitter, Bluesky, and others) so you can run SQL queries or view reports on engagement, clicks, and views. Activate when you need to analyze social media performance, find the best days or times to post, identify top-performing content, or query Buffer data with SQL.
license	Apache-2.0
metadata	{"category":"analytics","tags":"buffer, social-media, analytics, sql, metrics, optimization","author":"Daniela Petruzalek ([e-mail])","version":"0.2.0","catalog":"https://skills.danicat.dev"}

Buffer Analytics: SQLite Ingestion & SQL Query Engine

The buffer-analytics skill provides high-performance data warehousing and SQL querying for social media data downloaded via the Buffer CLI (@bufferapp/cli). It ingests raw payloads without filtering into a local SQLite database and provides a SQL interface for deep content crunching.

Available scripts

• scripts/buffer_analytics.py: Automated sync and report CLI (incremental sync, backfill, pre-packaged reports, ad-hoc queries). Executed via uv run scripts/buffer_analytics.py (requires Node.js 18+ and @bufferapp/cli).
• scripts/test_buffer_analytics.py: Unit and regression test suite validating schema, query extraction, and CLI flags.

---

⚡ Quick Start & Primary Actions

All operations are driven via the bundled Python script in scripts/buffer_analytics.py:

# 1. Incremental Sync (New posts + 2-day lookback metrics refresh)
uv run scripts/buffer_analytics.py sync --db path/to/database.db

# 2. Full Historical Backfill (Paginates through entire history)
uv run scripts/buffer_analytics.py sync --full --db path/to/database.db

# 3. Run Pre-Packaged Reports
uv run scripts/buffer_analytics.py report overview --db path/to/database.db
uv run scripts/buffer_analytics.py report top-posts --db path/to/database.db
uv run scripts/buffer_analytics.py report channels --db path/to/database.db
uv run scripts/buffer_analytics.py report timing --db path/to/database.db
uv run scripts/buffer_analytics.py report hooks --db path/to/database.db

# 4. Run Ad-Hoc SQL Query
uv run scripts/buffer_analytics.py query "SELECT service, AVG(impressions), AVG(reactions) FROM v_posts_summary WHERE status = 'sent' GROUP BY service" --db path/to/database.db

If --db is omitted, the script defaults to buffer_analytics.db in the current working directory.

---

🗄️ Database Schema & Relational Structure

The database maintains 6 normalized relational tables and high-performance SQL views. Detailed DDL and schema definitions are in references/schema.md.

Tables

1. channels: Connected social accounts and metadata.
   • Key columns: id (PK), organization_id, name, service (linkedin, twitter, bluesky), display_name, timezone, is_disconnected, raw_json, synced_at.
2. posts: Individual posts, scheduling state, and content.
   • Key columns: id (PK), channel_id (FK), channel_service, status (sent, scheduled, draft), text, external_link, sent_at, due_at, char_count, word_count, has_link, has_media, thread_count, raw_json, synced_at.
3. post_metrics: Time-series metrics per post.
   • Key columns: id (PK), post_id (FK), channel_service, metric_type (impressions, reach, reactions, comments, reposts, clicks, engagementRate), value, synced_at.
4. post_assets: Attached images, videos, and media URLs.
   • Key columns: id (PK), post_id (FK), type, mime_type, source, thumbnail, raw_json.
5. post_tags: Campaign and topic tags assigned in Buffer.
   • Key columns: , (FK), , .

---

📊 Core Analytical View: v_posts_summary

The primary view for SQL analytics is v_posts_summary, which pivots metrics and computes calendar dimensions:

Column	Type	Description
post_id	TEXT	Buffer Post ID
service	TEXT	Network (linkedin, twitter, bluesky)
channel_name	TEXT	Account handle/name
status	TEXT	sent, scheduled, draft
sent_at	TEXT	Full ISO timestamp
sent_date	TEXT	Publication date (YYYY-MM-DD)
year_month	TEXT	Calendar month (YYYY-MM)
day_of_week	TEXT	Day name (Monday, Tuesday, etc.)
hour_of_day	INTEGER	UTC hour (0–23)
char_count / word_count	INTEGER	Text length metrics
has_link / has_media	INTEGER	1 if link or media is present

---

🔍 SQL Analytics Cookbook

Pre-tested SQL query recipes are documented in references/queries.md.

1. Best Day of the Week by Channel

SELECT
    service,
    day_of_week,
    COUNT(*) AS posts,
    ROUND(AVG(impressions), 0) AS avg_impressions,
    ROUND(AVG(reactions), 1) AS avg_reactions,
    ROUND(AVG(engagement_rate), 2) AS avg_eng_rate
FROM v_posts_summary
WHERE status = 'sent' AND day_of_week IS NOT NULL
GROUP BY service, day_of_week
ORDER BY service, avg_impressions DESC;

2. Best Posting Hours (UTC)

SELECT
    service,
    hour_of_day || ':00 UTC' AS hour,
    COUNT(*) AS posts,
    ROUND(AVG(impressions), 0) AS avg_impressions,
    ROUND(AVG(reactions), 1) AS avg_reactions
FROM v_posts_summary
WHERE status = 'sent' AND impressions > 0
GROUP BY service, hour_of_day
HAVING COUNT(*) >= 3
ORDER BY avg_impressions DESC;

3. Impact of Links in Body vs. First Comment

SELECT
    service,
    CASE WHEN has_link = 1 THEN 'Link in Body' ELSE 'No Link / First Comment' END AS placement,
    COUNT(*) AS posts,
    ROUND(AVG(impressions), 0) AS avg_impressions,
    ROUND(AVG(reactions), 1) AS avg_reactions
FROM v_posts_summary
WHERE status = 'sent' AND service = 'linkedin'
GROUP BY placement;

---

📚 Progressive Disclosure & References

• Full DDL Schema Reference: references/schema.md — Exact SQL table definitions, column types, constraints, and views.
• SQL Query Recipes: references/queries.md — Analytical queries for timing, link penalties, hooks, and topic cohorts.
• Workflows Guide: references/workflows.md — Operational guidance for periodic backfills and cron automations.
• Inquiry Playbook: references/inquiry_playbook.md — Strategic questions for campaign and social retrospectives.