Tracking events stored with precise timestamps, not daily aggregates

Storing email events as daily aggregate counts instead of timestamped rows destroys information that cannot be reconstructed later. Time-of-day open patterns, send-time optimization, session attribution, and sequence latency analysis all require sub-day precision. A schema that records open_count = 47 for a given date tells you nothing about whether those opens happened at 7am or 11pm, making it impossible to determine optimal send windows for your audience or to join open events to downstream conversion events by session. The data-integrity taxon names the exact failure: you have a metric, but you've discarded the resolution needed to act on it.

Store one row per event with a TIMESTAMPTZ column; add daily aggregation on top if performance requires it, but never as the sole record. Create the raw event table with proper indexes:

CREATE TABLE email_events (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  campaign_id UUID NOT NULL REFERENCES campaigns(id),
  contact_id  UUID NOT NULL,
  event_type  TEXT NOT NULL CHECK (event_type IN ('open','click','bounce','unsubscribe','spam_complaint')),
  machine_open BOOLEAN NOT NULL DEFAULT false,
  link_id     UUID REFERENCES tracked_links(id),
  user_agent  TEXT,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_email_events_campaign_occurred ON email_events (campaign_id, occurred_at);
CREATE INDEX idx_email_events_contact_occurred  ON email_events (contact_id, occurred_at);

Precomputed daily aggregates can live in a separate materialized view refreshed on a schedule — the raw event log must stay intact.

• ID: campaign-analytics-attribution.tracking-implementation.event-timestamps

• Severity: low

• What to look for: Examine the database schema or ORM models for email event tables (opens, clicks, bounces, unsubscribes). Check whether events are stored with a precise timestamp column (e.g., occurred_at TIMESTAMPTZ, created_at TIMESTAMP WITH TIME ZONE) or only as daily aggregate counts (e.g., a table with columns date DATE, campaign_id, open_count INTEGER). Precise timestamps enable time-of-day analysis, sequence modeling, and session attribution; daily aggregates cannot be reconstructed into event-level data later.

• Pass criteria: Email events (at minimum clicks and opens) are stored with a precise timestamp column (not just a date). Aggregate counts may exist in addition to raw events, but the raw event log with timestamps is present. Count all event-related tables and list all timestamp columns found — at least 1 must use TIMESTAMPTZ or equivalent precision.

• Fail criteria: Only aggregate tables exist with no row-level event log. Events are stored with DATE type instead of TIMESTAMP/TIMESTAMPTZ.

• Skip (N/A) when: The project relies entirely on a third-party ESP's reporting and stores no event data locally.

• Detail on fail: Example: "email_events table has only a date column, no time — cannot compute send-time optimization or time-of-day open patterns" or "Events stored as daily aggregates only, raw event log not persisted"

• Remediation: Store raw events with precise timestamps alongside any aggregates:

  CREATE TABLE email_events (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    campaign_id UUID NOT NULL REFERENCES campaigns(id),
    contact_id  UUID NOT NULL,
    event_type  TEXT NOT NULL CHECK (event_type IN ('open', 'click', 'bounce', 'unsubscribe', 'spam_complaint')),
    machine_open BOOLEAN NOT NULL DEFAULT false,
    link_id     UUID REFERENCES tracked_links(id),  -- nullable, for click events only
    user_agent  TEXT,
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
  );
  
  CREATE INDEX idx_email_events_campaign_occurred ON email_events (campaign_id, occurred_at);
  CREATE INDEX idx_email_events_contact_occurred ON email_events (contact_id, occurred_at);