campaign-tracking-plan.md

Campaign & Communications Tracking — Schema Plan

Context

The current email_log table tracks email sends at a high level (subject, recipient_count, template_used) but has no per-recipient tracking and is unused. As the system grows to multiple events per year and multiple communication channels (email via Brevo, LinkedIn posts, on-the-fly marketing materials), we need proper campaign tracking that answers: who was sent what, through which channel, for which event.

This migration will replace email_log with a richer set of tables.

New Tables (5 tables, replaces 1)

1. campaigns

Groups related outreach for a specific event purpose (e.g., "CFP 2026", "Early Bird Registration 2026").

Column	Type	Notes
id	SERIAL PK
name	VARCHAR(200) NOT NULL	e.g., "CFP Invitation — Data & AI Day 2026"
event_id	INTEGER NOT NULL FK → events	Every campaign belongs to an event
campaign_type	VARCHAR(50) NOT NULL	cfp, registration, reminder, thank_you, sponsor, general
description	TEXT	Optional context
status	VARCHAR(20) DEFAULT 'draft'	draft, active, completed, cancelled
created_at	TIMESTAMPTZ DEFAULT NOW()
updated_at	TIMESTAMPTZ DEFAULT NOW()

2. communications

Individual outreach actions within a campaign — an email blast, a LinkedIn post, etc.

Column	Type	Notes
id	SERIAL PK
campaign_id	INTEGER NOT NULL FK → campaigns
channel	VARCHAR(30) NOT NULL	email, linkedin_post, marketing_material
title	VARCHAR(500)	Email subject, LinkedIn post title, or material name
brevo_template_id	INTEGER	Email only — links to templates.json
post_url	TEXT	LinkedIn only — URL to the published post
content_summary	TEXT	LinkedIn post content or material description
recipient_count	INTEGER DEFAULT 0	For emails; 0 for posts/materials
status	VARCHAR(20) DEFAULT 'draft'	draft, sending, sent, published, failed
sent_at	TIMESTAMPTZ	When it was sent/published
created_at	TIMESTAMPTZ DEFAULT NOW()

3. communication_recipients

Per-recipient tracking for emails. Answers "who was sent what".

Column	Type	Notes
id	SERIAL PK
communication_id	INTEGER NOT NULL FK → communications
contact_id	INTEGER NOT NULL FK → contacts
status	VARCHAR(20) DEFAULT 'queued'	queued, sent, delivered, opened, clicked, bounced, failed
brevo_message_id	VARCHAR(100)	Brevo per-message tracking ID
sent_at	TIMESTAMPTZ
metadata	JSONB	Channel-specific data (Brevo webhook events, etc.)
UNIQUE	(communication_id, contact_id)	No duplicate sends

4. marketing_assets

Track materials created for campaigns/events — social graphics, flyers, banners, etc.

Column	Type	Notes
id	SERIAL PK
campaign_id	INTEGER FK → campaigns	Optional — asset may exist outside a campaign
event_id	INTEGER NOT NULL FK → events
asset_type	VARCHAR(50) NOT NULL	social_graphic, flyer, banner, poster, video, presentation
title	VARCHAR(200) NOT NULL
description	TEXT
file_url	TEXT	Link to the file (Brevo media library, cloud storage, etc.)
created_at	TIMESTAMPTZ DEFAULT NOW()

5. asset_distributions

Track where and when materials were shared.

Column	Type	Notes
id	SERIAL PK
asset_id	INTEGER NOT NULL FK → marketing_assets
channel	VARCHAR(50) NOT NULL	linkedin, whatsapp, twitter, print, email, website
distributed_at	TIMESTAMPTZ DEFAULT NOW()
reach_estimate	INTEGER	Optional — estimated reach/impressions
notes	TEXT	e.g., "Posted in SA Data & AI LinkedIn group"

Drop

• email_log — replaced by campaigns + communications + communication_recipients

Relationship Diagram

events
  ├── campaigns (1:many)
  │     ├── communications (1:many)
  │     │     └── communication_recipients (1:many) → contacts
  │     └── marketing_assets (1:many)
  │           └── asset_distributions (1:many)
  └── marketing_assets (1:many, direct)

Closing the Loop: Campaign → Registration

The existing tickets.marketing_source field can reference a campaign name or ID, connecting outreach to actual registrations. For example:

• Send CFP emails (campaign "CFP 2026")
• Speaker registers on Quicket
• Quicket CSV import populates tickets.marketing_source = 'cfp-2026'
• Query: "How many registrations came from the CFP campaign?" becomes answerable

No schema change needed — just a convention for marketing_source values.

Indexes

Table	Index	Why
campaigns	(event_id)	Filter campaigns by event
campaigns	(campaign_type)	Filter by purpose
campaigns	(status)	Filter active/completed
communications	(campaign_id)	All comms for a campaign
communications	(channel)	Filter by channel type
communications	(sent_at)	Timeline queries
communication_recipients	(communication_id, contact_id) UNIQUE	Prevent duplicates
communication_recipients	(contact_id)	"What has this person received?"
communication_recipients	(status)	Find bounced/failed
marketing_assets	(event_id)	All assets for an event
marketing_assets	(campaign_id)	All assets for a campaign
asset_distributions	(asset_id)	All distributions for an asset

Example Queries This Enables

"Who did we send the CFP invitation to?" → JOIN campaigns → communications → communication_recipients → contacts WHERE campaign_type = 'cfp'

"What outreach have we done for Data & AI Day 2026?" → Query campaigns + communications for the event, across all channels

"Has Jane Smith received any emails about the 2026 event?" → Query communication_recipients JOIN contacts WHERE email = 'jane@...' AND event_id = X

"What marketing materials did we create for 2026?" → Query marketing_assets WHERE event_id = X

"Where did we share the CFP flyer?" → Query asset_distributions JOIN marketing_assets

"Show me a timeline of all comms for the CFP campaign" → Query communications + marketing_assets for campaign, ordered by date

Migration File

Create as migrations/003_campaign_tracking.sql:

1. Create tables in FK dependency order: campaigns → communications → communication_recipients → marketing_assets → asset_distributions
2. Drop email_log
3. Add indexes
4. Add CHECK constraints for enum-like columns (channel, status, campaign_type, asset_type)

Impact on Existing Components

• templates.json: No change — communications.brevo_template_id references the same IDs
• Import MCP server: No change — imports don't touch campaign tracking
• CLAUDE.md: Update table count (10 → 14, since we add 5 and drop 1)
• DATABASE.md: Update ER diagram and table documentation

Verification

After running the migration:

1. \dt public.* — confirm 14 tables (5 new, 1 dropped)
2. Insert a test campaign → communication → recipients flow
3. Query: "What has contact X been sent?" — verify JOIN works
4. Query: "What outreach has been done for event Y?" — verify cross-channel view
5. Verify email_log is dropped