In data connections, you now have the capability to use composite keys. This feature allows you to look up values using a combination of multiple data points merged into a single key. This is particularly useful when a dataset lacks a single unique identifier, but a combination of attributes (like a customer's country and a specific brand) can create one.

What are Composite Keys?

Composite keys are formed by joining multiple values together with an underscore (_). This combined value can then be used in lookup formulas to find specific rows in your data connection.

For instance, you can create a composite key by combining a country and a brand:

[%DC_UUID:[%COUNTRY%]_[%BRAND%]::return_field:fallback_value%]

This formula will search the data connection for an entry where the key column exactly matches the combined [%COUNTRY%]_[%BRAND%] value and then return the information from the specified field.

When to Use Composite Keys

You should consider using a composite key when:

• Your dataset does not have a unique identifier for each row, but a combination of fields can serve as one (e.g., country + brand or campaign ID + user id).
• You need to avoid relying on external APIs and placeholder functions due to limited resources for building and maintaining them.
• You have the ability to pre-merge values into a composite key in your source system or file before uploading it.

It is best to avoid composite keys if your data already contains a unique ID for each row, as a single key is simpler and more reliable.

Preparing Your Data

To utilize composite keys, you must prepare your data beforehand. This involves creating a new column in your dataset that combines the desired key fields using an underscore.

Example CSV Data:

In this example, a new country_brand_key column is manually created by joining the country and brand columns. This new column will then be designated as the lookup key when you set up the data connection.

Example JSON Data:

[
  {
    "country": "UK",
    "brand": "Nike",
    "country_brand_key": "UK_Nike",
    "value": "Welcome UK!"
  },
  {
    "country": "FR",
    "brand": "Adidas",
    "country_brand_key": "FR_Adidas",
    "value": "Bonjour FR!"
  }
]

You can directly upload this JSON file and use country_brand_key as your lookup key.

Building the Formula in a Template

Currently, there is no user interface to help you build composite key formulas. You will need to manually enter the lookup formula into your template.

Here is an example of a complete formula:

[%DC_add9e2ea-adc2-4242-ac2e-aa12facfb91e:[%COUNTRY%]_[%BRAND%]::value:Default fallback%]

In this formula:

• DC_add9e2ea... is the unique ID of the Data Connection.
• [%COUNTRY%]_[%BRAND%] forms the composite key.
• value is the field from which to return data.
• Default fallback is the value that will be returned if no match is found.

Composite key formulas can also be used in mobile channels. Simply compose your formula in a text editor or an email template and then copy and paste it into the mobile channel editor.

Key Limitations and Recommendations

• Up to 3 Keys Supported: You can combine a maximum of three values to create a single composite key (e.g., [%COUNTRY%]_[%CAMPAIGN_ID%]_[%LANGUAGE%]). It is recommended not to exceed this limit to maintain performance and manageability.
• Preview and Testing:
  • Composite key lookups will not render in the Preview mode.
  • You can test them using Test Sends, as long as all the necessary lookup values are available.
  • To verify combinations of composite keys and general tags (like [%EMAIL%]), a real campaign test is required.
• Clearing Data: If you plan to upload a new dataset to an existing data connection via an API, it is recommended to first use the /clear endpoint. This will remove all previous records, ensuring your connection only contains current and relevant data.