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.

Transform data

To transform data in Klaviyo:

1. Navigate to the Transformation tab under CDP > Data management > Transformation. 
   
2. Choose a pre-built transformation, or select the Create button to define your own transformation rules from scratch.
3. If creating your own transformation, choose the profile property you’d like to transform, along with the transformation method. The transformed property and values will be saved under a new profile property called Property_transformed; Property being the name of your original profile property with the _transformed suffix appended.

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

Only a single Format and Standardize transformation can be applied per 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. Delete quotes
   Deletes quotation marks at the beginning and end of values (e.g., “ABC” to ABC).
3. Delete special characters
   Deletes all characters that are not numbers and 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
   Revises date and time formats so they can be stored in a consistent format. You can format either dates only, or date and time. Dates are stored in the ISO 8601 format in our system (YYYY-MM-DD, and YYYY-MM-DD HH:MM:SS.SS), but how they appear in the app will depend on your locale.

You can select as many of the formatting options as you wish when transforming a property’s values.

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.
   

   The Contains operator requires the property value being replaced to contain the specified substring you enter for the If value. Note that the entire property value will be replaced, not just the substring matched. Contains only supports text values.

   The Equals operator requires an exact match between the value being replaced and the replacement value. The entire property value will be replaced by the replacement value.

2. Select the data type for the If value.
3. Enter your desired values to be replaced in the If value condition. You'll see a drop-down showing up to 512 unique values that exist on your account for the property being transformed. You can include up to 30 values in one condition, or create a new value by selecting the Enter new value option in the drop-down.
   
   

   Properties with a large number of unique values like Email, Phone Number, First Name, and Last Name will not appear in drop-downs.

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

You can create up to 10 standardization rules, which are interpreted with "If else” logic. If the first condition doesn’t match, the next will be evaluated, and this will continue until a condition matches. After there is a match, the remaining conditions are not evaluated. If no condition matches, the original value will be set for the transformed profile property.

Merge

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

Transformations are applied in the following order: 

1. Merge
2. Format
3. Standardize 

To create a merge rule:

1. Select up to 10 profile properties to merge. By default, the merge must include 2 properties, but more can be included with the Add button.

The order of the properties included in the transformation determines how the values are prioritized during the merge, with items at the top of the list having the most priority. In the example above, Hair type will be the primary property that gets set on profiles as part of this merge.

     2. To change the priority of properties included in the merge, use the up and down arrows. 

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

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.

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.
   

To implement a pre-built transformation, navigate to the Transformation tab under CDP. If this is your first transformation, you’ll immediately see the prebuilt transformations on the page. If you already have previous transformations, select Create.

When you select a pre-built transformation, you’ll be brought to the transformation builder with the rules already defined. You can add or edit any of these rules here based on the needs of your brand.

Once you are ready to apply a pre-built transformation, select Save in the transformation builder. The transformation will be activated and will be visible on 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
  The timestamp of the most recent profile property transformation.

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
• Enable inactive transformations

To update or retransform a property, you can use the Edit option.

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

Understanding data types
Profile properties reference