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:

    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:

    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


The following describes how cross-table enrichment actually works:

  1. When an event comes into the data stream cart_data_stream, pass the event to the cross-table enrichment process.
  2. A field on the event is identified as the source key. In this example, prd_id is the source key. The prd_id value is used to look in the table product for a record whose product_id value matches. This is the product record.
  3. A new event is created by making a copy of the original event.
  4. The product record has a field product_description. The value from this field is added to the new event.
  5. The new event is sent to Lytics on the data stream enriched_cart_data_stream.
  6. An LQL query for the data stream enriched_cart_data_stream adds the data from the new event to the user table:
    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