Data Samples

This step by step guide will walk you through the different data objects of data share. Start exploring the data by using this guide to make sure each dataset purpose and value is fully comprehended.

Step 1: Targeted Customers Sample

Purpose: Create a basic customer list to work with to explore customers data. In this query we extract a list of customers that were targeted by any campaign through Optimove.

As this query might be too broad and heavy, consider using LIMIT to control volumes. In this example, the overall result will be MAX 2000 customers.

-- Generate a temporary table with unique customer IDs from both targeted and engaged datasets
CREATE OR REPLACE TEMPORARY TABLE TARGETED_CUSTOMERS_TEMP AS
SELECT DISTINCT CUSTOMER_ID
FROM (
    SELECT CUSTOMER_ID 
    FROM VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_2
    LIMIT 1000)
    
    UNION ALL
    
    (SELECT CUSTOMER_ID 
    FROM VW_ENGAGED_CUSTOMERS_V1_2
    LIMIT 1000
);

-- Validate the targeted customers dataset
SELECT DISTINCT CUSTOMER_ID FROM TARGETED_CUSTOMERS_TEMP;

Step 2: Sample Customers Table

Purpose: Extract customer profiles from the most recent profile data for the customers selected above. In this query, customer records will also contain an attribute CLIENT_CUSTOMER_ID that can be used to cross this data with internal data.

Note that this query might yield less results, since not all customers necessarily has most recent profile record (dormant customers, for example, do not have daily records)

-- Create a temporary table for customer profiles linked to targeted customers
CREATE OR REPLACE TEMPORARY TABLE cust AS
SELECT cp.*
FROM VW_CUSTOMER_PROFILES_REAL_FIELD_NAMES AS cp
JOIN TARGETED_CUSTOMERS_TEMP AS tc 
    ON cp.CUSTOMER_ID = tc.CUSTOMER_ID
WHERE cp.PROFILE_DATE = (
    SELECT MAX(profile_date) 
    FROM VW_CUSTOMER_PROFILES_REAL_FIELD_NAMES
);

-- Validate the sample customers dataset
SELECT * FROM cust;

Step 3: Targeted and Excluded Customers

Purpose: Identify customers who were targeted and/or excluded customers in scheduled campaigns. Using this query will return a customer-level data about the targeting history of the inspected customers.

-- Create a temporary table for targeted and excluded customers
CREATE OR REPLACE TEMPORARY TABLE targeted_and_excluded AS
SELECT tg.*
FROM BI.VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_2 AS tg
JOIN cust AS cp
    ON cp.customer_id = tg.customer_id
   AND cp.profile_date = tg.execution_date;

-- Validate the targeted and excluded customers dataset
SELECT * FROM targeted_and_excluded;

Step 4: Scheduled Campaigns

Purpose: Retrieve metadata for scheduled campaigns associated with campaigns that were actually targeting customers.

-- Create a temporary table for scheduled campaigns
CREATE OR REPLACE TEMPORARY TABLE scheduled_campaigns_temp AS
SELECT *
FROM BI.VW_SCHEDULED_CAMPAIGNS_V1_2
WHERE plan_id IN (
    SELECT DISTINCT plan_id
    FROM targeted_and_excluded
);

-- Validate the scheduled campaigns dataset
SELECT * FROM scheduled_campaigns_temp;

Step 5: Campaign Results

Purpose: Analyze results of scheduled campaigns executed for the focused customers.

-- Create a temporary table for campaign results
CREATE OR REPLACE TEMPORARY TABLE Campaign_Results_temp AS
SELECT cr.*
FROM BI.VW_CAMPAIGN_RESULTS_V1_2 AS cr
JOIN scheduled_campaigns_temp AS sc
    ON cr.plan_id = sc.plan_id
   AND cr.plan_detail_id = sc.plan_detail_id;

-- Validate the campaign results dataset
SELECT * FROM Campaign_Results_temp;

Step 6: Engagement Activity

Purpose: Investigate customer engagement with all campaigns.

-- Create a temporary table for engagement activity
CREATE OR REPLACE TEMPORARY TABLE engagement_activity_temp AS
SELECT ea.*, sc.EXECUTION_START_DATE
FROM VW_ENGAGEMENT_ACTIVITY_V1_2 AS ea
LEFT JOIN scheduled_campaigns_temp AS sc
    ON sc.PLAN_DETAIL_ID = ea.PLAN_DETAIL_ID
JOIN cust AS cp
    ON cp.customer_id = ea.customer_id
WHERE ea.CUSTOMER_ID IN (
    SELECT CUSTOMER_ID 
    FROM cust
);

-- Validate the engagement activity dataset
SELECT * FROM engagement_activity_temp;

Step 7: Triggered Campaigns Targeting History

Purpose: Examine the history of triggered campaigns involving engaged customers.

-- Create a temporary table for engaged customers
CREATE OR REPLACE TEMPORARY TABLE ENGAGED_CUSTOMERS_TEMP AS
SELECT 
    TO_DATE(ec.engaged_date) AS final_engaged_date,
    ec.*
FROM BI.VW_ENGAGED_CUSTOMERS_V1_2 AS ec
JOIN cust AS cp 
    ON ec.CUSTOMER_ID = cp.customer_id;

-- Validate the triggered campaigns targeting history dataset
SELECT * FROM ENGAGED_CUSTOMERS_TEMP;

Step 8: Triggered Campaigns Metadata

Purpose: Retrieve metadata for triggered campaigns involving engaged customers.

-- Create a temporary table for triggered campaigns metadata
CREATE OR REPLACE TEMPORARY TABLE TRIGGERED_CAMPAIGNS_TEMP AS 
SELECT tc.* 
FROM BI.VW_TRIGGERED_CAMPAIGNS_V1_2 AS tc
JOIN ENGAGED_CUSTOMERS_TEMP AS ec 
    ON tc.CAMPAIGN_DETAILID = ec.CAMPAIGN_DETAIL_ID;

-- Validate the triggered campaigns metadata dataset
SELECT * FROM TRIGGERED_CAMPAIGNS_TEMP;

Step 9: Customers Targeted by Triggered Campaigns

Purpose: Summarize real customers targeted by triggered campaigns, excluding visitors

-- Create a temporary table for customers targeted by triggered campaigns
CREATE OR REPLACE TEMPORARY TABLE CUSTOMERS_TARGETED_TRIGGERED_CAMPAIGNS AS
SELECT 
    TO_DATE(ENGAGED_DATE) AS ENGAGEMENT_DATE,
    *
FROM BI.VW_ENGAGED_CUSTOMERS_V1_2 
WHERE CUSTOMER_ID IN (
    SELECT CUSTOMER_ID 
    FROM cust
);

-- Validate the customers targeted by triggered campaigns dataset
SELECT * FROM CUSTOMERS_TARGETED_TRIGGERED_CAMPAIGNS;