跳至正文
OpenAgentSkill
浏览已开放 · 目录记录日期 2026-11-21 · 自动更新和账户服务暂时关闭。
数据与分析

buffer-analytics

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.

danicat · 已有 AI 审核记录

安装与使用

运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。

npx skills add danicat/skills --skill 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 (daniela@danicat.dev) 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: 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:

ColumnTypeDescription
post_idTEXTBuffer Post ID
serviceTEXTNetwork (linkedin, twitter, bluesky)
channel_nameTEXTAccount handle/name
statusTEXTsent, scheduled, draft
sent_atTEXTFull ISO timestamp
sent_dateTEXTPublication date (YYYY-MM-DD)
year_monthTEXTCalendar month (YYYY-MM)
day_of_weekTEXTDay name (Monday, Tuesday, etc.)
hour_of_dayINTEGERUTC hour (0–23)
char_count / word_countINTEGERText length metrics
has_link / has_mediaINTEGER1 if link or media is present
thread_countINTEGERNumber of posts in thread
impressionsREALTotal impressions / views
reachREALUnique accounts reached
reactionsREALLikes and reactions
commentsREALComments received
repostsREALRetweets / reshares
clicksREALLink click count
engagement_rateREALTotal engagement %
external_linkTEXTLive post URL
textTEXTFull text copy

🔍 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 
阅读完整来源 ↗