Liquid guide to Lookups

  • Updated
Description

You can use Lookups to pull data directly from objects (tables) in the Optimizely database. Based on a certain set of conditions, you get an array of objects.

 
Examples: 

Example 1: "I want to lookup a list of product names and prices, sorted by highest price in descending order. "

-Use liquid code:

{% assign most_expensive_products = lookup.products(name, price).top(price) %}

Breakdown:

  • assign most_expensive_products: “I want to call this list of things this name.”
  • Lookup.products: “I want a list of products” ('products' is the name of the Object, see below)
  • name, price: “For every product in this list, I only want the name and price.”
  • .top: “I want the highest of…”
  • price: “...the price field.” 

Example 2: "What if I want more than just the name and price of a product?"

-Change the name, price to include the fields of whatever table you’re looking up

 

Example 3: "What if I want to get the most expensive products of a certain category?"

-Use a where clause. Example below;

 

'Where' example:  This will return a list of products that have more than 5 items in stock (qty>5) and a price that isn’t 0 (price != 0)

-Use liquid code:

{% assign most_expensive_products = lookup.products(name, price, qty).where(qty > 5 and price != 0).top(price) %}

Breakdown:

    • .where: “I want a list of products that satisfy the conditions I put in these parentheses.”
    •  qty>5 and price != 0: “I want the quantity of the item to be more than 5, and a price that isn’t equal to 0.”

You can find more examples and details on Lookups in the following documentation: https://support.optimizely.com/hc/en-us/articles/4407775333261-Advanced-Liquid-capabilities-in-ODP 

Additional Info:

You can perform lookups for:

    • Events (lookup.events)
    • Customers (lookup.customers - this is already done for you, so just use {{customer.field_name}})
    • Products (lookup.products)
    • Orders (lookup.orders) - this is for accessing order-level fields
    • any custom Object

To see your Objects and their respective fields and relationships:

- In ODP , click the Cog Wheel (top right), then navigate to Data Management > Objects & Fields

 

When you do lookups, you can access fields in the Object you’re looking up

    • “lookup.events(event_type)” would work, because “event_type” is a field of the events table
    • “lookup.products(event_type)” would NOT work, because “event_type” is NOT a field of the products object

You can reference fields in any relational child objects by explicitly prefacing the fields with its relationship key.

1. lookup.events(page, product.name., product.url)

    Starting at Events, the query keys into the child Products object to grab its fields.

2. lookup.products(name, parent_product.name)

    Starting at Products, the query keys into the parent_product relationship to grab the field.

3. lookup.events(product.name, product.category.path)

    Starting at Events, the query keys into its child Products object, which has a child Categories object.

 

Sorting your Results

You can use the following to sort the records returned from your lookup:

  1. .top
  2. .bottom
  3. .newest (can only be used for Events queries)
  4. .oldest (can only be used for Events queries)

Depending on the lookup object, .top and .bottom sort on different criteria:

- When doing lookup.products, you supply a field for those clauses to sort on

  • .top(qty) → shows products with highest quantity level first.
  • .bottom(price) → shows products with the lowest prices first.

- When doing lookup.events, without supplying a field to the clause

  • .top → shows the most occurred events.
  • .bottom → shows the least occurred events.

-When doing lookup.events, with supplying a field to the clause.

  • .top(event_type) → sorts the results’ by their event type in descending alphanumeric order.
  • .bottom(page) → sorts the results’ by their page in ascending alphanumeric order.

- .newest and .oldest take no argument and sort on the timestamp of the event.

  • .newest → shows the most recent events first.
  • .oldest → shows the oldest events first.

.

More Examples:

Example: What if I want to access my best selling products?

You CAN reference products in an event lookup, by explicitly prefacing the product field(s) with “product.” before the field name:

Use Liquid code:

{% assign popular_products = lookup.events(product.name, product.url).where(event_type = 'order' and vdl_action = 'purchase').top %}

 

Example: What if I want to access my best selling categories?

The Categories object is hidden in the ODP UI, but the fields you can reference via Liquid are:

- Root Category = category.name_0

- Category Path = category.path

Applying the logic above, you can explicitly preface the category field(s) with “category.”

Use Liquid code:

{% assign popular_categories = lookup.events(product.category.name_0, product.category.path).where(event_type = 'order' and vdl_action = 'purchase').top %}

 

 

Additional Resources