Data Lookups (Strategy Designer)

Owned by Ondřej Tesař
Sept 06, 2024
9 min read

Data Lookups are a crucial concept in Strategy Designer. They allow you to specify a data set that can be reused across your strategies in different ways. There are two kinds of lookups: simple and advanced.

A general guide to deciding whether to use the simple or advanced look-up:

  • Use simple lookup if you only need to fetch a single row for the SKU currently being calculated, and pick a value from it without any transformation.

  • Use advanced lookup if:

    • you want to fetch data for a different SKU than the one being calculated,

    • you need to filter, sort, or aggregate the data,

    • you need to look up a value based on a range or a value which comes from another data lookup.

You can create a Data Lookup by clicking the Add Lookup button above the list of lookups.

In this section:

Simple Data Lookups

Product Extension & Company Parameter Lookup

These two simple lookups can be compared to Excel’s VLOOKUP function, simply fetching a single row based on a key or a set of keys. They always require all key fields to be specified and always produce just one value. They do not allow filtering, sorting, or aggregating data.

The Product Extension and Company Parameter lookups work almost the same, with just one slight difference: the Product Extension lookup is always filtered by the SKU of the currently calculated product.

Below is an example of a simple lookup of a company parameter table called ‘AdditionalDiscount’. This table identifies each value based on three keys: Business Unit, Product Group, and Product Class. There is an input for each key that needs to be specified. Based on the specified set of the three keys, the lookup identifies a single row and returns its column called ‘Discount %’.

If you need to perform a Product Extension lookup for a different SKU than the one currently being calculated, or if you need to filter, sort, and aggregate your data, you need to use the advanced data lookup.

Competition Lookup

The Competition Lookup looks into the Competition Data table and allows you to filter records based on its columns. It is always filtered for the SKU currently being calculated and always returns a price, either the highest, lowest, or an average of all the records that the lookup produces.

Notice the Filters category in the toolbox that allows you to drag & drop the filter criteria. Everything within the logical block with the ‘ALL’ option selected will be logically AND-ed together. Everything within the logical block with the ‘ANY’ option will be OR-ed together. Everything which is directly plugged into the Filter input is AND-ed together.

In the example below, the filter could be rewritten like this: Competitor = ‘MainCompetitor’ AND (Country = ‘Germany’ OR Country = 'Italy).

If you need more filtering, sorting, or aggregation options, you need to use the advanced data lookup.

Advanced Data Lookup

The advanced data lookup works in a different way compared to the simple lookup. It splits the data fetching from the aggregation.

  • The data fetching part is configured in the Data Lookups tab. An advanced data lookup does not produce a single value but always fetches a set of rows. You can filter and sort the result as you wish.

  • The aggregation part happens in your strategy. Here you determine how to transform the rows to produce a single value.

The flexibility, however, comes with a price, and that is that the advanced data lookup is generally slower.

Do not use the advanced data lookup if you can achieve the same result with the simple one. The advanced data lookup is generally slower than the simple one.

The advanced data lookup supports these data sources:

  • Company Parameter

  • Product Extension

  • Product Master

  • Customer Extension

  • Customer Master

  • Competition Data

Below is an example of the advanced data lookup. The source is a Product Extension called ‘Product Costs’. The following filter conditions are applied:

  • SKU column is equal to the ID of the product currently being calculated,
    and

  • Date is lower or equal to the target date,
    and

  • Dependency Level Name is ‘Global’.

Additionally, the result is sorted by the Date from the highest (most recent) to the lowest (oldest).

Advanced data lookup example – Product Costs
Result of the Product Costs data lookup

 

This lookup can return zero, one, or multiple rows, as we can see in the Live Preview, and it returns all columns that are present in the table.

Because of this, we can define such a lookup once and use it multiple times in our strategies, each time querying a different column and a different aggregation function.

Using Data Lookups in Strategy

General Information

Before you can use your data lookup in a strategy, you have to save it. This is done by clicking the Save button. Even when you rename a lookup, you need to save it for the new name to be reflected in the strategies. After you save a lookup, a corresponding block will appear in your Strategies workspace in the toolbox under the Data Lookups category.

If you do not have any saved data lookup, the Data Lookups category will be empty.

Using Simple Lookups

After you save a simple data lookup, it will be available in the Data Lookups category in the strategies toolbox as a block with no configuration options. This block returns the value specified in the data lookup’s configuration.

Below is an example of a simple data lookup into the company parameter called ‘AdditionalDiscount’. This data lookup returns a ‘Discount %’. When you use the Additional Discount block in your strategy, it will evaluate the lookup based on the currently calculated product and return the found discount. You can then use it in your strategy.

Simple data lookup for Additional Discount

 

Corresponding block in the strategies toolbox.

 

Additional Discount block used in a simple strategy.

 

Aggregating Values from Advanced Lookup

As we described earlier, the advanced data lookup does not return a single value but a set of rows with multiple columns. When it is being defined, it does not know yet what column will be used and how the values of that column will be aggregated. This is decided when the data lookup is used in a strategy. Therefore, each saved advanced data lookup creates an aggregation block that allows us to select which data lookup we want to use, which column we are going to aggregate, and which aggregation function we use.

Below is an example of several aggregation blocks, each produced for a saved advanced data lookup.

Remember the Product Costs lookup we specified earlier, which returned multiple rows? Here we can decide how to use it. For example, we can take an average of the values in the Cost column.

There are many aggregation functions available. You can reuse the same advanced data lookup multiple times, each with a different column and aggregation function.

Range Lookup

Sometimes you might want to filter a lookup based on a value that is not yet known at the time when the lookup is defined.

Example: We want to calculate a price impact based on a certain product’s weight. We store a price impact value for multiple weight ranges. Therefore we define a lookup that produces these three rows:

However, the problem is that product weight comes from another lookup and therefore cannot be directly used in this lookup’s filter.

In such a case, we can enable lookup by range for this data lookup by checking the corresponding checkbox. When we do that, we are required to specify which columns define the range. In our case it is the columns ‘Pricing Attribute Value From’ and ‘Pricing Attribute Value To’. By doing this, we made the advanced data lookup a little smarter because now it knows how to perform a range lookup.

This opens a new possibility and creates a new block type in the Data Lookups toolbox in the strategies. This block has an additional input where we can specify the value used for the range lookup. In our example, it would be the weight of the product.

Let’s say we have a Product Weight coming from a different lookup, stored in a variable. In our range lookup, we plug this variable into the block so the lookup knows which value we are comparing the range to. Do not forget to take the correct result column, which is not the ‘Pricing Attribute Value From’ but the ‘Price Impact Value’.

 

A range lookup used in a strategy
Weight range returned by the lookup
Result of the range lookup example

 

As you can see, the Product Weight was 120, therefore the range lookup returned a Price Impact Value of 1.1. When multiplied by the Product Cost of 12, the resulting price is 13.2.

If multiple records fit the looked-up range, the block might select one of them at random. Generally, when this happens, it suggests that your data is not defined very well because the ranges overlap and you should fix the data.

Value Lookup

Sometimes you might want to perform a lookup not by a range, but by a single value. The setup is very similar to the range lookup, only this time you check the Enable lookup by value checkbox. When you do that, you need to specify the Lookup field which will be used for such a lookup. The Lookup field should contain unique values only.

In the example below, we have a table with a Color attribute with different colors stored in the Pricing Attribute Value column. Each color has an impact stored in the Price Impact Value column. We need to tell the data lookup that the column we want to use for the value lookup is the Pricing Attribute Value column.

 

 

When we do this and save the lookup, a new kind of data lookup block will appear in the Data Lookups category in the strategies toolbox. It works in the same fashion as the range lookup, only this time the record with the matching ‘Pricing Attribute Value’ will be picked.

If multiple records fit the looked-up value, a random one might be picked as the result. Generally, when this happens, you get a suggestion that your data is not defined very well and you should fix it.

One advanced data lookup might be enabled for both the range lookup and the value lookup. Just make sure the ranges do not overlap and the values in the Lookup field are unique.

Data Lookup Status

When you start using the strategies, you will notice some icons in the list of data lookups. Here is their meaning:

 

  1. Link – This data lookup is used in one or more strategies.
    Hover over this icon to see how many times this strategy is used.

  2. Broken link – This data lookup is used in one or more strategies, but has unsaved changes. You can either save them or revert to the previously saved version.

  3. Triangle with an exclamation mark – This data lookup has validation errors. The icon is shown regardless of whether it is saved or not.

  4. No icon – This data lookup is not used anywhere yet.

Data lookups which are used in one or more strategies cannot be deleted. Also, you cannot change some properties of these lookups, such as whether they are enabled for a range or value lookup.