Understand data transformation in Klaviyo

You will learn

Learn about the data transformation tool in Klaviyo, and how you can use it to make your data more useful.

This content requires Functional cookies to be enabled.

Update Cookie Settings

Transform data

To transform data in Klaviyo:

1. Navigate to Data management > Transformation in the Advanced section, then select the Profile properties tab.
2. To use a pre-built transformation, select one from the pre-built options. If you already have transformations, select Create to view those options. To start from scratch, select Create on the page showing the pre-built options.
3. If creating your own transformation, select a Method. For Format or Standardize, choose the Profile property you want to transform. For Merge, select the properties to combine in the merge rules.
4. Configure the rules for your chosen method, as described under Transformation methods below. For a pre-built transformation, review the preset rules and edit them if needed.
5. Select Save. Your transformation appears in the transformation list while Klaviyo applies it to existing profiles.

Transformed values are saved in a new profile property with the suffix __transformed (2 underscores). For example, Property becomes Property__transformed. For a merge, the first property in the list supplies the new property name. The original properties and their values are preserved.

Transformation methods

The data transformation tool in Klaviyo allows you to make the following transformations to your profile property values:

• Format
  Reformat profile property values.
• Standardize
  Set rules to automatically replace specific profile property values.
• Merge
  Merge multiple custom profile properties into 1 property

You can apply 1 Format transformation and 1 Standardize transformation to each original profile property.

Format

The Format transformation method has 5 formatting options:

1. Remove spaces
   Deletes spaces at the beginning and end of values (e.g., “ ABC” to “ABC”).
2. Remove quotes
   Removes straight single or double quotation marks at the beginning and end of values (e.g., "ABC" to ABC).
3. Remove special characters
   Removes characters other than English letters and digits, including spaces, punctuation, and accented letters (e.g., A,B,C to ABC).
4. Capitalize the first letter of every word
   Capitalizes the first letter of each word (e.g., John doe to John Doe).
5. Revise date format
   Store dates in a consistent format. Choose Date only or Date and time. The transformed values are stored as YYYY-MM-DD or YYYY-MM-DD HH:MM:SS, but how they appear in the app depends on your locale. Values that cannot be interpreted as dates are left blank in the transformed property.

You can combine any of the 4 text formatting options. Revise date format must be used on its own and cannot be combined with the text formatting options.

Standardize

With the standardization transformation method, you can set rules to automatically replace specific profile property values.

To create a standardization rule:

1. In the If value condition, select Contains or Equals.

1. Select the data type for the If value.
2. Select up to 30 values to match in the If value condition. The dropdown suggests up to 512 existing values for the property. To match a value that is not listed, select Enter new value and enter it; this adds a match value to the rule, not a new value to your profiles.

1. In the Replace with condition, select the data type for the replacement value.
2. Enter your desired replacement value in the Replace with condition.

You can create up to 50 standardization rules in a transformation. Rules run in order, and a later rule can replace an earlier rule’s result if it matches that result. Check your conditions to avoid unintended replacements. If no standardization rule matches, the value passes through unchanged.

1. To add another condition, select + Add rule and enter its match and replacement values.
2. Select Save when all your standardization rules are ready.

Merge

The Merge transformation allows you to combine multiple custom profile properties into 1, allowing you to effectively use the data in segments and templates.

Transformations are applied in the following order:

1. Merge
2. Format
3. Standardize

To create a merge rule:

1. Select 2–10 profile properties to merge. To include another property, select + Add.

Klaviyo uses the first property in the list with a value. If that property is missing or blank on a profile, it checks the next property. For example, if Hair type is first, its value takes priority when it is available. The output property is called Hair type__transformed.

1. Use the up and down arrows to change the priority of the properties.

You can combine up to 10 properties with a single Merge transformation. Note that once you save the transformation, you can no longer change the topmost priority. Instead, you can delete the transformation and create a new one.

1. Select Save once your merge transformation is set up.

Example Merge transformation

In this example, the Merge transformation is being applied to the Birthday and Birthdate properties to combine into a single property called Birthday__transformed.

If 1 profile has Birthday set to 06/07/1998 and another has only Birthdate set to 06/04/2001, both get a new property called Birthday__transformed with their respective dates. Their original properties and values remain unchanged. If a profile has both properties, the first property in the merge list takes priority.

Pre-built transformations

There are 4 pre-built transformations you can quickly enable with the Transformation tool.

1. Format first names
   Capitalize the first name of profiles for consistency in your messaging (e.g., alex to Alex).
   

2. Standardize country names
   Standardize common country names so they all have the same value, allowing for consistency across similar profiles and easier segmentation (e.g., U.S. or USA to United States).
   

3. Standardize U.S. state names
   Standardize the values for all 50 states, allowing for consistency across similar profiles and easier segmentation (e.g., NY or New york to New York).
   

4. Format birthdays
   Format birthday properties for consistency in segmentation and messaging.
   

5. Navigate to Data management > Transformation in the Advanced section and select Profile properties. If this is your first transformation, the pre-built options appear on the page. If you already have transformations, select Create to view them.

6. Select a pre-built transformation to open the builder with its rules already defined. Review the rules and add or edit them based on your needs.

1. Select Save when the pre-built transformation is ready. The transformation is activated and appears in your transformation list.

Managing transformations

Once you have a data transformation created in Klaviyo, you’ll see it listed on the Transformations page with the following details:

• Profile property
  The name of the original profile property that is being transformed.
• New profile property name
  The name of the transformed profile property.
• Transformation method
  The transformation method used.
• Status
  The status of the transformation (i.e., Active, In progress, Inactive).
• Last edited
  When the transformation was last created or edited.

When a new transformation is first created, its status is In progress. While in this status, the transformation is being applied in bulk to existing profiles. Once bulk transformation is complete, the status becomes Active. Any new profiles or updates to existing profiles will be transformed live.

From the menu next to each transformation, you can:

• Edit existing transformations
• Delete transformations
• Select Set to active to reactivate inactive Format or Standardize transformations.

To update or retransform a property, select Edit from the transformation’s menu, change its rules, and select Save. To delete a transformation, select Delete from the menu and confirm with Delete in the dialog.

Note that deleting a transformation will not delete already transformed properties saved under Property__transformed, but the transformation will no longer be applied to any new data.

Additional resources