Scheduled Campaigns

This shared table contains the details of all scheduled campaigns that were set in Optimove, alongside its metadata that can be used for further analysis by any campaign property. This data can be used to group by different properties such as 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 that are split into more than one channel will be represented in different rows. Channels that test more than one template will be represented by the first template only in order of their appearance in Campaign Builder.

📣

Latest version available: BI.VW_SCHEDULED_CAMPAIGNS_V1_3.

Version releases -

• PROMOTION_NAME
• PROMOTION_CODE

Glossary

Read more about terminology in Data Share Glossary

V1_3

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

🔑 Primary Keys:

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

👍

LAST_MODIFY_DATE- Timestamp of the last recorded modification to this campaign record. Not recommended as an incremental ingestion watermark — see Ingestion Recommendations below.

Ingestion Recommendations

⚠️ Full load recommended

VW_SCHEDULED_CAMPAIGNS_V1_3 is a small-volume reference table, typically containing fewer than 10,000 records. A full load (truncate-and-replace) is the recommended ingestion strategy.

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

Recommended approach:

-- 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_3;

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

If incremental ingestion is required (e.g., due to 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_3 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 here 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