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 viauv 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
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.
- Key columns:
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.
- Key columns:
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.
- Key columns:
post_assets: Attached images, videos, and media URLs.- Key columns:
id(PK),post_id(FK),type,mime_type,source,thumbnail,raw_json.
- Key columns:
post_tags: Campaign and topic tags assigned in Buffer.- Key columns:
id,post_id(FK),name,color.
- Key columns:
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.
- Key columns:
📊 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 |
thread_count | INTEGER | Number of posts in thread |
impressions | REAL | Total impressions / views |
reach | REAL | Unique accounts reached |
reactions | REAL | Likes and reactions |
comments | REAL | Comments received |
reposts | REAL | Retweets / reshares |
clicks | REAL | Link click count |
engagement_rate | REAL | Total engagement % |
external_link | TEXT | Live post URL |
text | TEXT | Full 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
阅读完整来源 ↗