Guides

Data Samples

Explore your data by executing these queries to get the best of data share

Explore your data by executing these queries to get the best of data share

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.

📘

Same across all versions: the SQL below uses the V1_6 views. To run against V1.5 or V1.3, replace the _V1_6 suffix with _V1_5 or _V1_3.

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_6
	LIMIT 1000)
	UNION ALL
	(SELECT CUSTOMER_ID
	FROM VW_ENGAGED_CUSTOMERS_V1_6
	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_6 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_6
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_6 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_6 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_6 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_6 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_6
WHERE CUSTOMER_ID IN (
	SELECT CUSTOMER_ID
	FROM cust
);
-- Validate the customers targeted by triggered campaigns dataset
SELECT * FROM CUSTOMERS_TARGETED_TRIGGERED_CAMPAIGNS;

Updated 10 days ago

Did this page help you?