buffer-analytics

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: id, post_id (FK), name, color.
6. sync_history: Audit trail of all sync executions.
   • Key columns: id (PK), channel_id, sync_mode, posts_fetched, posts_inserted, posts_updated, started_at, status.

📊 Core Analytical View: v_posts_summary #

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

🔍 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.