Scheduled Campaigns

Explore all past, present, and future campaigns and all their different properties.

This shared table contains the details of all scheduled campaigns set in Optimove, alongside metadata that can be used for further analysis by any campaign property — grouping by Tags, Marketing Actions, and Channels for insightful analysis.

Each row represents a channel within an action of a campaign occurrence. Actions are set in Optimove Campaign Builder, under the Execute section. Campaign Actions split into more than one channel appear in different rows. Channels that test more than one template are represented by the first template only, in order of appearance in Campaign Builder.

Query

-- FOR V1.6
SELECT *
FROM [SHARED_DB].[BI].[VW_SCHEDULED_CAMPAIGNS_V1_6]

-- FOR V1.5
SELECT *
FROM [SHARED_DB].[BI].[VW_SCHEDULED_CAMPAIGNS_V1_5]

-- FOR V1.3
SELECT *
FROM [SHARED_DB].[BI].[VW_SCHEDULED_CAMPAIGNS_V1_3]

Glossary

• ACTION_NAME — 'DN' represents control groups that are not targeted with any communication ("Do Nothing").
• CAMPAIGN_TYPE_NAME
  • Test/Control — a simple campaign using a single action tested against a control group.
  • Self-Optimizing — a campaign configured to optimize its action distribution according to performance. Read more.
  • A/B/N — a campaign with multiple actions, may or may not include a control group.
  • A/B — a campaign testing only two actions, without a control group.
  • No Control — a campaign with a single test group only.
• MARKETING_ACTION_NAME — the action name used in a campaign. Set on a campaign action level, managed in Optimove under Plan > Actions.
  Read more about terminology in the Data Share Glossary.

Primary Keys

• Composite Key: PLAN_ID, PLAN_DETAIL_ID, TEMPLATE_ID, PLAN_DETAIL_CHANNEL_ID, PROMOTION_ID

Ingestion Recommendations

Although LAST_MODIFY_DATE appears to be a natural watermark column, using it for incremental ingestion can produce data gaps:

Recommended approach (swap the suffix for the version you use):

-- Full load pattern (run daily or on schedule)
CREATE OR REPLACE TABLE MY_DB.MY_SCHEMA.SCHEDULED_CAMPAIGNS AS
SELECT *
FROM DATA_SHARE_DB.DATA_SHARE_SCHEMA.VW_SCHEDULED_CAMPAIGNS_V1_6;

Because the view is small, full loads complete in seconds and eliminate all three failure modes above.

If incremental ingestion is required (e.g., pipeline framework constraints), supplement it with a periodic full reconciliation:

-- Reconciliation: detect stale/deleted records
SELECT dest.PLAN_ID, dest.PLAN_DETAIL_ID
FROM MY_DB.MY_SCHEMA.SCHEDULED_CAMPAIGNS dest
LEFT JOIN DATA_SHARE_DB.DATA_SHARE_SCHEMA.VW_SCHEDULED_CAMPAIGNS_V1_6 src
    ON dest.PLAN_ID = src.PLAN_ID
    AND dest.PLAN_DETAIL_ID = src.PLAN_DETAIL_ID
WHERE src.PLAN_ID IS NULL;
-- Any rows returned exist in your destination but have been
-- removed from the source view and should be deleted.

This also applies to detecting metadata drift — compare TEMPLATE_ID, TEMPLATE_NAME, and other mutable fields against the source periodically.

Table Description