Guides

Customer 360 View

Optimove's Single Customer View aggregates hundreds of attributes describing all aspects of customer activity, forming a comprehensive 360-degree customer profile. This guide walks through the core data components that build Optimove's Customer 360 page and their appearance in the data, to help you explore the data further.

📘

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.

Overview of CUSTOMER_PROFILES

The CUSTOMER_PROFILES table captures a daily snapshot of each customer's profile, enriched with attributes calculated per customer. This snapshot is the foundation of the Single Customer View in Optimove.

To get an initial look at the data in this table, use:

SELECT *
FROM CUSTOMER_PROFILES
LIMIT 10;

Field Naming in CUSTOMER_PROFILES

The field names in CUSTOMER_PROFILES may appear generic (e.g., STR12 for categorical values, FIELD153 for numeric values). You can map these fields to more descriptive names through VW_CUSTOMER_PROFILE_FIELDS_V1_6, where ALIAS_FIELD_NAME provides the familiar attribute names from Optimove.

SELECT *
FROM VW_CUSTOMER_PROFILE_FIELDS_V1_6;

For more intuitive column labels, VW_CUSTOMER_PROFILES_REAL_FIELD_NAMES translates field names into recognizable attribute names.

SELECT *
FROM VW_CUSTOMER_PROFILES_REAL_FIELD_NAMES;

Note: The CLIENT_CUSTOMER_ID field represents each customer's unique ID as defined in the data delivered to Optimove.

To see the full profile for a specific customer, use the following:

SELECT *
FROM VW_CUSTOMER_PROFILES_REAL_FIELD_NAMES
WHERE CLIENT_CUSTOMER_ID = '1111113001034507'
LIMIT 100;

Learn more about Customer Profiles in the Optimove Documentation.

Customer Marketing Impact

On the Customer 360 page, Optimove's Customer Marketing Impact chart presents various key performance indicators (KPIs).

First, we will create a view that contains different KPIs for the last month. here is an example query:

SELECT
	PROFILE_DATE,
	CLIENT_CUSTOMER_ID,
	NUMBEROFCAMPAIGNS_1M,
	ECOM_TOTAL_GROSS_SALES_AMOUNT_1M,
	ECOM_TOTAL_NET_SALES_AMOUNT_1M,
	ECOM_TOTAL_REFUND_AMOUNT_1M,
	ECOM_NUMBER_OF_ORDER_DAYS_1M,
	ECOM_NUMBER_OF_ORDERS_1M
FROM
	VW_CUSTOMER_PROFILES_REAL_FIELD_NAMES
WHERE
	CLIENT_CUSTOMER_ID = '1111113001034507'
LIMIT 100;

Cumulative KPI Values

To get cumulative KPI values (shown on the chart's Y-axis), use:

SELECT
	PROFILE_DATE,
	CLIENT_CUSTOMER_ID,
	ECOM_TOTAL_GROSS_SALES_AMOUNT,
	ECOM_TOTAL_NET_SALES_AMOUNT,
	ECOM_TOTAL_REFUND_AMOUNT,
	ECOM_NUMBER_OF_ORDER_DAYS,
	ECOM_NUMBER_OF_ORDERS
FROM
	VW_CUSTOMER_PROFILES_REAL_FIELD_NAMES
WHERE
	CLIENT_CUSTOMER_ID = '1111113001034507'
LIMIT 100;

Campaign Targeting Days

To view the days on which this customer received a campaign, use:

SELECT *
FROM
	VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_6
WHERE
	CLIENT_CUSTOMER_ID = '1111113001034507'
	AND PLAN_DETAIL_ID IS NOT NULL
ORDER BY EXECUTION_DATE DESC
LIMIT 100;

Combined View: Campaign Targeting Days and KPIs

To create a comprehensive view showing campaign targeting days alongside various KPIs, join VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_6 with VW_CUSTOMER_PROFILES_REAL_FIELD_NAMES as follows:

SELECT
	TAEC.EXECUTION_DATE,
	TAEC.CLIENT_CUSTOMER_ID,
	TAEC.PLAN_ID,
	TAEC.PLAN_DETAIL_ID,
	TAEC.CAMPAIGN_ACTION_ID,
	TAEC.CAMPAIGN_ACTION_NAME,
	CPRFN.ECOM_TOTAL_GROSS_SALES_AMOUNT,
	CPRFN.ECOM_TOTAL_NET_SALES_AMOUNT,
	CPRFN.ECOM_TOTAL_REFUND_AMOUNT,
	CPRFN.ECOM_NUMBER_OF_ORDER_DAYS,
	CPRFN.ECOM_NUMBER_OF_ORDERS,
	TAEC.LAST_UPDATED_TIME
FROM
	VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_6 AS TAEC
LEFT JOIN
	(SELECT
		PROFILE_DATE,
		CLIENT_CUSTOMER_ID,
		ECOM_TOTAL_GROSS_SALES_AMOUNT,
		ECOM_TOTAL_NET_SALES_AMOUNT,
		ECOM_TOTAL_REFUND_AMOUNT,
		ECOM_NUMBER_OF_ORDER_DAYS,
		ECOM_NUMBER_OF_ORDERS
		FROM
			VW_CUSTOMER_PROFILES_REAL_FIELD_NAMES
		WHERE
			CLIENT_CUSTOMER_ID = '1111113001034507') AS CPRFN
ON
	TAEC.CLIENT_CUSTOMER_ID = CPRFN.CLIENT_CUSTOMER_ID
	AND TAEC.EXECUTION_DATE = CPRFN.PROFILE_DATE
WHERE
	TAEC.CLIENT_CUSTOMER_ID = '1111113001034507'
	AND TAEC.PLAN_DETAIL_ID IS NOT NULL
ORDER BY
	EXECUTION_DATE DESC
LIMIT 100;

Sent Campaigns List

The next section in Customer 360 lists all campaigns sent to the customer.

To get additional details about campaigns sent to a customer, including targeting information, use this query:

SELECT
	TAEC.EXECUTION_DATE,
	TAEC.CLIENT_CUSTOMER_ID,
	SC.TARGET_GROUP_NAME,
	TAEC.PLAN_ID,
	TAEC.PLAN_DETAIL_ID,
	TAEC.CAMPAIGN_ACTION_ID,
	TAEC.CAMPAIGN_ACTION_NAME,
	SC.CHANNEL_NAME,
	SC.TEMPLATE_NAME,
	SC.PROMOTION_ID,
	SC.TAG_NAMES
FROM
	VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_6 AS TAEC
LEFT JOIN
	VW_SCHEDULED_CAMPAIGNS_V1_6 AS SC
ON
	SC.PLAN_ID = TAEC.PLAN_ID
	AND SC.PLAN_DETAIL_ID = TAEC.PLAN_DETAIL_ID
WHERE
	TAEC.CLIENT_CUSTOMER_ID = '1111113001034507'
	AND TAEC.PLAN_DETAIL_ID IS NOT NULL
ORDER BY
	EXECUTION_DATE DESC
LIMIT 100;

This query provides detailed information about the campaigns sent to the specified customer, including TARGET_GROUP_NAME, CHANNEL_NAME, TEMPLATE_NAME, PROMOTION_ID, and TAG_NAMES.

Updated 13 days ago

Did this page help you?