--- 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`:

```bash # 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`](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:

| 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`](references/queries.md).

### 1. Best Day of the Week by Channel ```sql 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) ```sql 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 ```sql 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`](references/schema.md) — Exact SQL table definitions, column types, constraints, and views. - **SQL Query Recipes**: [`references/queries.md`](references/queries.md) — Analytical queries for timing, link penalties, hooks, and topic cohorts. - **Workflows Guide**: [`references/workflows.md`](references/workflows.md) — Operational guidance for periodic backfills and cron automations. - **Inquiry Playbook**: [`references/inquiry_playbook.md`](references/inquiry_playbook.md) — Strategic questions for campaign and social retrospectives.