FluentCRM Aggregate Reporting via CLI

Created 2026-04-23

Status planned

Tags planinfrastructureemailfluentcrmreportinganalytics

Context

FluentCRM’s web UI shows per-campaign statistics one campaign at a time. There’s no built-in cross-campaign rollup — no “open rate over the last 12 months,” no “top-performing subject lines by segment,” no engagement decay by cohort. The data is all there in the WordPress database; the UI just doesn’t surface it in aggregate form.

Claude Code has SSH + WP-CLI access to crm.baseworks.com (credentials documented in the changelog repo per the CRM SSH access reference). FluentCRM tables sit alongside WP core tables and are queryable directly. Building reports against them is straightforward: read tables, aggregate, render markdown.

This is not urgent. Marketing is currently driven by editorial judgment, not analytics dashboards. The point of this plan is to capture the capability so it’s a few hours of work — not a discovery exercise — when a real reporting need surfaces (e.g. “which subject-line patterns actually pulled clicks last year,” “which segments are decaying,” “did the Brain Fodder cadence change open rates”).

The companion plan ses-cli-access-and-tracking-config-plan establishes that SES tracking is the wrong place to build reporting on top of (per-message infrastructure telemetry, no contact context). FluentCRM is the source of truth for marketing analytics.

Goals

Map the FluentCRM database schema for the tables relevant to reporting (campaigns, sends, opens, clicks, subscribers, segments, automations).
Build a small set of reusable SQL queries (or a single Python/PHP script that wraps them) that produce common aggregate reports.
Define a delivery surface — markdown report dropped into a known vault location, or printed to terminal, or posted to an inbox on request.
Document everything so future sessions can extend the report library without re-discovering the schema.

Non-goals

Building a dashboard UI. Reports render as markdown; Obsidian is the viewer.
Real-time analytics. These are batch reports, run on demand.
Replacing or extending FluentCRM’s per-campaign analytics. The web UI handles single-campaign deep dives; this plan is strictly cross-campaign rollups.
Touching SES (covered in the sister plan).

Approach

Phase 1 — Schema discovery

Over SSH to crm.baseworks.com, dump the relevant table schemas:

wp db tables --all-tables-with-prefix='fc_' --format=csv

For each table, capture the structure:

wp db query "DESCRIBE wp_fc_campaigns;"
wp db query "DESCRIBE wp_fc_campaign_emails;"
wp db query "DESCRIBE wp_fc_subscribers;"
wp db query "DESCRIBE wp_fc_subscriber_meta;"
wp db query "DESCRIBE wp_fc_campaign_email_meta;"
wp db query "DESCRIBE wp_fc_lists;"
wp db query "DESCRIBE wp_fc_tags;"

(Confirm exact prefix; FluentCRM uses wp_fc_* by default but the WP install may have a custom prefix.)

Document the schema in a vault reference file: 03-resources/vault-and-tooling/fluentcrm-schema.md. Note especially:

Which table holds the open event (vs the click event).
Whether opens/clicks are stored as event rows or as counters on the email row.
How is_open / is_click columns relate to last_open_at / last_click_at timestamps.
How automation-driven sends are distinguished from broadcast campaigns.

Phase 2 — Core report library

Define the initial report set. Each is a single SQL query (or short script) that produces one markdown table. Starting set:

Campaign performance over time — every broadcast campaign in the last N months with: send date, subject, recipients, opens, open rate, clicks, click rate, unsubscribes.
Subject-line performance — group by subject-line patterns (length buckets, presence of em dashes/colons/questions, first word) to surface what’s pulling.
Segment engagement — per list/tag, average open rate and click rate over the last N campaigns; flag segments with decay.
Subscriber cohort decay — opens-per-90-days bucketed by signup year, to see how engagement ages.
Automation performance — opens/clicks for automation emails (welcome series, etc.) vs broadcasts.
Inactive subscriber list — subscribers with zero opens in the last 12 months, candidates for re-engagement or pruning.

Each report is a .sql file under scripts/fluentcrm-reports/ in the vault repo (vault-native scripts are explicitly OK per CLAUDE.md). A wrapper script (fcrm-report.sh or fcrm-report.py) on Patrick’s Mac runs them via ssh crm.baseworks.com 'wp db query ...' and renders the result as markdown.

Phase 3 — Delivery surface

Reports land in 02-areas/communications/reports/ with a date prefix:

02-areas/communications/reports/2026-04-23-campaign-performance.md
02-areas/communications/reports/2026-04-23-segment-engagement.md

A single command produces the full bundle:

./scripts/fcrm-report.sh --bundle --period=12m

For ad-hoc one-off questions, the script accepts a single report name and parameters:

./scripts/fcrm-report.sh subject-line-performance --period=6m

Phase 4 — Document & memorialize

Add 02-areas/communications/reports/index.md listing the available reports.
Add 03-resources/vault-and-tooling/fluentcrm-schema.md with the schema map and example queries.
Add a one-line memory pointer: [FluentCRM aggregate reporting](reference-fluentcrm-reporting.md) — script + schema + delivery convention.
Move this plan to 03-resources/plans/executed-plans/ once Phase 3 verifies clean.

Critical Files / Surfaces

SSH access to crm.baseworks.com (already documented in changelog repo).
scripts/fluentcrm-reports/*.sql (new) — query library.
scripts/fcrm-report.sh (new) — wrapper that runs queries over SSH and renders markdown.
02-areas/communications/reports/ (new directory) — output destination.
03-resources/vault-and-tooling/fluentcrm-schema.md (new) — schema reference.

Risks & Mitigations

Heavy queries against the live CRM database. Mitigation: read-only queries only; add LIMIT and date-bounded WHERE clauses; avoid table scans on wp_fc_subscriber_meta without indexed predicates. If a report needs heavy aggregation, run it during low-traffic hours.
Schema drift across FluentCRM updates. Mitigation: the schema reference doc gets re-validated whenever a query stops working; queries fail loud rather than silently miscount.
Reports stale after one run. Mitigation: each output file embeds the run timestamp and the period covered in its filename and frontmatter, so old reports stay self-describing.

Verification

End-to-end success means:

A single command on Patrick’s Mac produces the full bundle of reports as markdown files in 02-areas/communications/reports/.
Each report renders cleanly in Obsidian and contains the data you’d expect from spot-checking against the FluentCRM web UI for one or two campaigns.
Re-running a report on a different time window changes the numbers as expected.
Schema reference doc is complete enough that adding a 7th report is a matter of writing the SQL, not re-discovering tables.

When to pick this up

When marketing has a real cross-campaign question that the FluentCRM UI can’t answer in a few clicks. Until then, the capability is documented and ready; no work needed.