Cross-table Enrichment
User Profile Enrichment
Imagine a case where an event represents a customer adding a product to a shopping cart. The event contains the product ID, but you want to be able to segment based on product category. The product category data is available from a separate data source, but you need the product category for a specific product. A feature of Lytics called cross-table enrichment makes this possible.
Note: Cross-table enrichment currently must be configured by Lytics. Contact your account representative for help.
How Lytics handles inbound data
Data sent to Lytics is called an event. An event represents data about a single customer.
In order for Lytics to accept an event, a data stream name must be specified. Lytics uses the data stream name to determine how to handle the event.
The most familiar example of how Lytics handles the event is to apply LQL queries to the data in order to map it to fields on the user table. (The user profile is a snapshot of data from the user table at a specific point in time.)
Lytics allows multiple processes to handle event data. Cross-table enrichment is an example. It effectively allows you to reference data from other tables and add that data to the event. To continue the example from above, you can reference the product table to add the product category to the event.
Understanding Lytics tables
When you write an LQL query, one of the required details is the data to populate. Most of the time the table is user, as in the example below:
SELECT
customer_id AS customer_id
, prd_id AS cart_product_id
, prd_qty AS cart_product_quantity
FROM cart_data_stream
INTO user BY customer_id
ALIAS cart_event_query
But you can create additional tables by specifying another name. For example, the following query will populate the table product
when data is sent to the data stream product_data_stream
:
SELECT
id AS product_id
, description AS product_description
, category AS product_category
FROM product_data_stream
INTO product BY product_id
ALIAS product_data_query
Enrichment
The following describes how cross-table enrichment actually works:
- When an event comes into the data stream
cart_data_stream
, pass the event to the cross-table enrichment process. - A field on the event is identified as the source key. In this example,
prd_id
is the source key. Theprd_id
value is used to look in the tableproduct
for a record whoseproduct_id
value matches. This is the product record. - A new event is created by making a copy of the original event.
- The product record has a field
product_description
. The value from this field is added to the new event. - The new event is sent to Lytics on the data stream
enriched_cart_data_stream
. - An LQL query for the data stream
enriched_cart_data_stream
adds the data from the new event to theuser
table:
SELECT
customer_id AS customer_id
, prd_id AS cart_product_id
, prd_qty AS cart_product_quantity
, product_category AS cart_product_category
FROM enriched_cart_data_stream
INTO user BY customer_id
ALIAS enriched_cart_event_query
Updated 7 months ago