engine9 global message tables

Global message warehouse views put a message’s identity, platform engagement, last-click attribution, and primary-source-code dictionary or legacy origin fields on a single row. Most message performance reports and data grids read these views instead of joining the underlying tables by hand. Use this skill to select a view, choose the correct metric family, and avoid double counting or incorrect attribution.

Quick reference

Both sit under the account global_table_prefix (often empty). They are views over global_message, global_message_stats / _by_date, plugin metadata, and source_code_summary / _by_date; they are not base tables you write to.

Rules

Rule: Say transaction, never donation.

Rule: Email sends, opens, and clicks always come from global_message_summary on publish_date. Do not use global_message_summary_by_date or its date column for email.

Rule: global_message_summary_by_date.date is the day a statistic occurred. Use it for ads and other long-running message types. It is not a substitute for email publish_date.

Rule: Prefer attributed_* for cross-platform fundraising truth; platform revenue and transactions are native conversions only.

Rule: Do not add platform revenue to attributed_revenue; the metric families overlap and would double count.

Rule: Do not sum origin_* across messages that share a primary source code; those values are code-level totals repeated on each message.

Concepts

Column categories

Treat columns as separate categories. Mixing them (e.g. adding revenue + attributed_revenue, or treating origin_* as message-level last click) double-counts or misattributes.

1. Message identity — from global_message

2. Plugin context — legacy bot_* columns

These come from plugin / deployment metadata. Column names still use the older bot vocabulary; treat them as plugin fields.

Filter or group by bot_nickname / bot_path the same way you would by plugin. Do not invent a separate “bot” concept when explaining results to users — say plugin.

3. Platform engagement and native conversions — from global_message_stats / _by_date

Platform-reported metrics from the messaging or ad system — not engine9 last-click attribution.

On _by_date, these numbers are for activity on that date as reported by the platform. That is the right grain for ads and other long-running types. Email platforms store lifetime sends, opens, and clicks on the message; report those from global_message_summary by publish_date, even if a by-date row also exists.

4. Last-click attribution — also on global_message_stats / _by_date

Rollups of transactions whose final message id is this message. Pipeline details: e9-source-code (last-click A–F).

On _by_date, attributed_* are grouped by transaction calendar day (ts), not by publish_date. A day can have attributed revenue with zero impressions (or the reverse): engagement days and conversion days are independent.

5. Source-code elements and legacy origin — from source_code_summary / _by_date

Joined on message.final_primary_source_code = summary.source_code (and matching date for the by-date view).

origin_* and element fields on a message row are properties of the primary source code, not of the message alone. If several messages share one primary code, they each show the same code-level origin totals.

Workflow

Choose a view and metrics

Trace related objects

Stale attributed_* with good transaction_summary.recommended_message_id usually means attribution stats have not run for that publish window — diagnose with e9-source-code pipeline A–F, not by rewriting these views.

Examples

Query lifetime and daily metrics

-- Email (and other one-shot sends): lifetime totals by publish month.
-- Do not switch this query to global_message_summary_by_date.
SELECT
  date_trunc('month', publish_date) AS month,
  sum(sent) AS sent,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks,
  sum(attributed_revenue) AS attributed_revenue
FROM global_message_summary
WHERE channel = 'email'
  AND publish_date >= DATE '2026-01-01'
GROUP BY 1
ORDER BY 1;

-- Ads and long-running types: stats on the day they occurred (`date`),
-- not on publish_date. Also the axis for last-click revenue by transaction day.
SELECT date, sum(spend) AS spend, sum(attributed_revenue) AS attributed_revenue
FROM global_message_summary_by_date
WHERE date >= DATE '2026-01-01'
GROUP BY date
ORDER BY date;

Interpret inventory statistics

e9-inventory warehouse statistics read these views. Grain is month (YYYY-MM). impressions is platform opens.

These blocks are aggregate message stats (kind: aggregate) — the usual inventory audit. They are not per-person timeline entries. Per-person send/open/click rows live under Timeline → Messages (statistics.inputs, subcategory: messages). Audit this aggregate work more often than timeline entries.

Rule: Home email and any other email sends/opens/clicks read statistics.messages (publish_date). A by-date series that only has recent date rows must not replace a year of publish-month email history.

Use message_activity when the question is “on which days did this ad or long-running message report stats?” Do not use message_summary_by_date for email — it drops rows without spend. Do not use inputs.by_plugin_entry_type_month as a substitute for these views.

Troubleshooting

Related documentation

• Source codes and last-click attribution
• Acquisition and LTV models
• Inventory statistics and Home mapping
• Timeline per-person message entries