dim_outbound_messages

Use this catalog to explore message definitions and their current known configuration, including messages that have no performance yet. Use daily message performance for engagement and attributed revenue.

Grain: One row per (sm_store_id, source_system, message_id).
Identity: message_identity_kind distinguishes a native send, a reusable message definition and a reporting group. These represent different things; counting catalog rows does not count delivered messages.
Refresh: The whole catalog refreshes, so changes to older definitions can appear. This is current known configuration, not a history of every edit.
Coverage: Includes available definitions from configured Klaviyo, Postscript, HubSpot and Omnisend sources. Available fields differ by provider, and missing metadata remains unknown. Postscript rows retain their reporting-group identity; HubSpot publication and Omnisend workflow definitions do not establish a recipient delivery.

What to explore

Compare the mix of campaigns, flows and message channels; inspect audience labels and segment counts; and find which definitions have variation or experiment settings. These settings describe how a test was configured. They do not establish a winning variant or causal lift.

SELECT
  source_system,
  message_channel,
  message_identity_kind,
  message_status,
  COUNT(*) AS catalog_entries
FROM `your_project.sm_transformed_v2.dim_outbound_messages`
GROUP BY 1, 2, 3, 4
ORDER BY catalog_entries DESC;

Join to performance

Join using all three catalog keys. A native provider ID alone is not a cross-provider or cross-store key. Existing daily performance covers Klaviyo and Postscript; a catalog entry for another provider does not add that provider’s performance to the report.

SELECT
  f.sm_store_id,
  f.date,
  f.source_system,
  f.message_id,
  d.message_identity_kind,
  d.message_ab_test_configured,
  d.message_ab_test_objective,
  f.message_unique_clicks,
  f.platform_reported_order_revenue
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily` AS f
LEFT JOIN `your_project.sm_transformed_v2.dim_outbound_messages` AS d
  ON f.sm_store_id = d.sm_store_id
  AND LOWER(f.source_system) = d.source_system
  AND f.message_id = d.message_id;

Interpretation limits

A subject stays unknown when variations are missing or disagree. Scheduled times for recipient-local or optimized sends may be reference times rather than a single global send time. Provider experiment percentages can describe different allocations, so check provider_ab_test_percentage_kind before comparing them. Catalog metadata does not establish recipient-level exposure, actual delivery windows or historical configuration at the time of an event.

Columns

Column	Type	Description
`sm_store_id`	STRING	SourceMedium’s unique store identifier. For Shopify stores, derived from the myshopify.com domain; for other platforms (Amazon, TikTok Shop, Walmart.com), uses platform-specific identifiers.
`audience_priority`	INT64	Provider-supplied audience priority; preserves its native ordering semantics.
`campaign_audience_id`	STRING	Native campaign-audience definition identifier; not an individual subscriber or a delivered audience.
`campaign_audience_name`	STRING	Current configured campaign-audience label. This does not establish who received the message.
`excluded_segment_count`	INT64	Number of configured excluded audience/list references; not a suppression or recipient count.
`flow_id`	STRING	Native automation/workflow identifier. A reusable message definition can exist before any delivery events.
`flow_name`	STRING	Current automation/workflow label, when the provider supplies a verified link.
`included_segment_count`	INT64	Number of configured included audience/list references; not a recipient count. Null means unknown.
`message_ab_test_configured`	BOOL	Whether the provider metadata establishes experiment configuration. Null means unknown; it is not a performance result.
`message_ab_test_objective`	STRING	Configured winner objective normalized to opens, clicks or orders where verified. This is not an observed winner or causal lift.
`message_ab_test_percentage`	FLOAT64	Configured test-audience percentage only where that meaning is established. Klaviyo winner allocation is kept separately.
`message_ab_test_wait_hours`	FLOAT64	Configured delay before choosing a winner, converted to hours where documented. Not measured delivery or conversion latency.
`message_channel`	STRING	Email, SMS or push when the catalog has one unambiguous channel; not a recipient channel preference.
`message_declared_variation_count`	INT64	Number of expected native content references, when established; compare with resolved coverage before interpreting content.
`message_distinct_subject_count`	INT64	Distinct nonblank resolved subject lines. A scalar subject also requires complete coverage and no missing subjects.
`message_first_scheduled_send_at`	TIMESTAMP	Earliest valid provider scheduling reference across scalar/per-variation entries, converted to UTC. For recipient-local or optimized strategies this is not a bound on actual recipient sends.
`message_id`	STRING	Stable catalog identity within workspace and provider. See message_identity_kind; automation block definitions may use a composite identity.
`message_identity_kind`	STRING	native_send, message_definition or reporting_group; these grains must not be treated as interchangeable sends.
`message_last_scheduled_send_at`	TIMESTAMP	Latest valid provider scheduling reference across scalar/per-variation entries, converted to UTC. Not an actual delivery bound; recipient-local and optimized anchors need their strategy context.
`message_metadata_observed_at`	TIMESTAMP	Ingestion observation time for the source object. Current metadata is not a history of how the configuration changed.
`message_metadata_resolution`	STRING	catalog, event_derived or legacy_only; provenance of the available metadata, not proof that every field is populated.
`message_name`	STRING	Current provider label, or an unambiguous name for an existing reporting group.
`message_observed_send_start_at`	TIMESTAMP	Provider-recorded observed send start. Separate from planned/scheduled time and event date.
`message_preview_text`	STRING	Message preview text.
`message_schedule_entry_count`	INT64	Number of supplied scalar and per-variation schedule entries, including invalid entries where validity is tracked.
`message_schedule_metadata_valid`	BOOL	Whether supplied schedule entries parse as explicit UTC/offset instants. Null when the provider projection does not evaluate this check.
`message_schedule_timezone`	STRING	Provider-supplied scheduling timezone; not inferred from ingestion or event timestamps.
`message_scheduled_send_at`	TIMESTAMP	Provider scalar scheduling reference, converted to UTC. Recipient-local and optimized strategies may use a wall-clock or start-date anchor, not one global planned send instant. Never delivery time.
`message_send_strategy`	STRING	Verified common timing category: fixed_time, recipient_local_time, optimized, throttled, send_time_test or immediate. Unknown mappings stay null.
`message_status`	STRING	Verified common send lifecycle state. Publication and subscriber activity are not evidence that a send completed.
`message_subject`	STRING	Unambiguous subject. Missing, unresolved or different variant subjects remain unknown.
`message_type`	STRING	Campaign, flow or transactional classification at this identity grain; unknown values stay null.
`message_variation_count`	INT64	Number of resolved content variations, when established at this identity grain. Null does not mean no A/B test.
`message_variation_metadata_complete`	BOOL	Whether expected content references resolve to this workspace and message without duplicates or missing children. This is coverage, not a test outcome.
`parent_campaign_id`	STRING	Canonical parent campaign identifier, separate from the performance report historical campaign_id.
`parent_campaign_name`	STRING	Current parent campaign name; a name without an ID does not establish parent identity.
`provider_ab_test_percentage`	FLOAT64	Provider-native allocation percentage, interpreted using provider_ab_test_percentage_kind. Zero is a valid value.
`provider_ab_test_percentage_kind`	STRING	winner_allocation or test_audience; those percentages have different meanings.
`provider_ab_test_winner_metric`	STRING	Provider ab test winner metric.
`provider_message_id`	STRING	Native message or block identifier, when known. Reporting groups have no single native message identifier.
`provider_message_status`	STRING	Unmodified provider lifecycle state. Subscriber subscription status is excluded.
`provider_send_strategy`	STRING	Unmodified provider scheduling strategy; unrecognized values remain available here.
`source_relation`	STRING	Native source relation or feed providing this metadata.
`source_system`	STRING	Provider namespace for message identity.
`source_updated_at`	TIMESTAMP	Source updated at.

Get into BigQuery

Activate

SQL Query Library

Common analyses (SQL)

Tables

Schema Standards

What to explore

Join to performance

Interpretation limits

Columns

​What to explore

​Join to performance

​Interpretation limits

​Columns

What to explore

Join to performance

Interpretation limits

Columns