Customer 360

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.

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_2, where ALIAS_FIELD_NAME provides the familiar attribute names from Optimove.

SELECT * 
FROM VW_CUSTOMER_PROFILE_FIELDS_V1_2;

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_2 
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_2 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_2 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,
    SC12.TARGET_GROUP_NAME,
    TAEC.PLAN_ID,
    TAEC.PLAN_DETAIL_ID,
    TAEC.CAMPAIGN_ACTION_ID,
    TAEC.CAMPAIGN_ACTION_NAME,
    SC12.CHANNEL_NAME,
    SC12.TEMPLATE_NAME,
    SC12.PROMOTION_ID,
    SC12.TAG_NAMES
FROM 
    VW_TARGETED_AND_EXCLUDED_CUSTOMERS_V1_2 AS TAEC
LEFT JOIN 
    VW_SCHEDULED_CAMPAIGNS_V1_2 AS SC12 
ON 
    SC12.PLAN_ID = TAEC.PLAN_ID 
    AND SC12.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.