Aggregate metrics reconcile with individual campaign data

Incrementally-updated aggregate counters — open_count bumped by a database trigger on each incoming webhook — diverge from the raw event log whenever a webhook is delivered twice, a contact is suppressed after the fact, or a timezone offset causes an event to land in the wrong reporting bucket. Because the aggregate and raw events are now out of sync, every metric derived from the aggregate (open rate, CTR, RPE) is wrong by an unknown amount. The iso-25010:2011 functional-suitability taxon names this precisely: the reporting function exists but produces systematically incorrect values with no mechanism to detect or correct the drift.

Run a nightly reconciliation job that compares each campaign's cached aggregate against the authoritative count from the raw event table and alerts on any discrepancy:

async function reconcileAggregates() {
  const campaigns = await db.campaigns.findMany({ where: { status: 'sent' } })
  const discrepancies: Array<{ campaign_id: string; field: string; aggregate: number; actual: number }> = []

  for (const campaign of campaigns) {
    const actualOpens = await db.emailEvents.count({
      where: { campaign_id: campaign.id, event_type: 'open', machine_open: false }
    })
    if (campaign.cached_unique_opens !== actualOpens) {
      discrepancies.push({
        campaign_id: campaign.id,
        field: 'unique_opens',
        aggregate: campaign.cached_unique_opens,
        actual: actualOpens
      })
    }
  }

  if (discrepancies.length > 0) {
    console.error('Aggregate reconciliation failures:', discrepancies)
    // Send to your alerting/monitoring system
  }
  return discrepancies
}

Schedule this via a Vercel cron or database scheduled job on a nightly cadence.

• ID: campaign-analytics-attribution.reporting-pipeline.aggregate-reconciliation

• Severity: high

• What to look for: Check whether the system has any mechanism to verify that aggregate/summary metrics match the sum of individual campaign metrics. Inconsistencies arise when events are double-counted (a contact who opens three times counts as three opens in the event log but should count as one unique opener in aggregates), when failed webhook deliveries leave gaps in event data, or when time zone handling causes events to fall in the wrong reporting bucket. Look for reconciliation queries, integrity checks, or monitoring that compares aggregate counts against row-level counts.

• Pass criteria: The system either (a) uses database views or derived queries that always recompute from raw events (self-reconciling), or (b) has a reconciliation check that compares aggregate totals against the sum of per-campaign data and alerts on discrepancies. Enumerate all aggregate tables and count how many have a reconciliation mechanism — at least 1 reconciliation check must exist per aggregate table.

• Fail criteria: Aggregate tables are updated incrementally (e.g., via a trigger that increments a counter), with no reconciliation against raw events. Discrepancies between the aggregate and the sum of raw events would go undetected.

• Skip (N/A) when: Reporting is done entirely through database views that recompute from raw events on each query (no pre-computed aggregates to reconcile).

• Detail on fail: Example: "Aggregate open_count column is updated via trigger increments with no reconciliation check — double-delivery of webhooks would inflate counts permanently" or "No test or monitoring confirms aggregate totals match raw event counts"

• Remediation: Add a reconciliation check:

  // Nightly reconciliation job
  async function reconcileAggregates(date: Date) {
    const discrepancies: Array<{ campaign_id: string; field: string; aggregate: number; actual: number }> = []
  
    // Check open counts
    const campaigns = await db.campaigns.findMany({ where: { status: 'sent' } })
    for (const campaign of campaigns) {
      const aggregateOpens = campaign.cached_unique_opens
      const actualOpens = await db.emailEvents.count({
        where: {
          campaign_id: campaign.id,
          event_type: 'open',
          machine_open: false
        },
        // Count distinct contacts
      })
  
      if (Math.abs(aggregateOpens - actualOpens) > 0) {
        discrepancies.push({
          campaign_id: campaign.id,
          field: 'unique_opens',
          aggregate: aggregateOpens,
          actual: actualOpens
        })
      }
    }
  
    if (discrepancies.length > 0) {
      console.error('Reporting reconciliation discrepancies found:', discrepancies)
      // Alert via your monitoring tool
    }
  
    return discrepancies
  }