Creating transformations

You create transformations in the Data Transformation Studio. In the Studio, you work on a sample of the data that will be transformed: you add transformation steps one by one and you see the result of each change in a preview.

You can open the Studio in two ways:

• From a workflow - in the "Data Transformation Studio" node, you can create a transformation directly in the workflow where it is used. If the node is directly preceded by a node that provides data (for example, "Local File" node), the Studio can take the sample data from that node, so you don't have to prepare a separate sample file. You can use such a transformation only in this node, or save it as a template in the transformations library to reuse it in other workflows.
• From the transformations library - in Automation Hub > Transformations, you can create a transformation that you can later use as a template in any Data Transformation Studio node. A template is copied to the node, so changes you make later to the template in the library don't affect the workflows in which it was used.

The Studio consists of the following parts:

• Preview (on the left) - the Source tab shows the sample data before transformation, and the Output tab shows the data after all transformation steps. After you change the transformation steps, click Update results at the bottom of the preview to refresh it.
• Source (on the right) - the source of the sample data.
• Transformation steps - operations performed on the data, from top to bottom. You add them by clicking Add step.
• Output - the transformation goal and the list of columns required by this goal.

Open the Data Transformation Studio

Depending on where you want to use the transformation, select one of the options:

To change the default name of the transformation (Unnamed), click the pencil icon next to the name and enter a new name.

Add sample data

Transformations are created based on sample data that reflects the structure of the data that will be transformed in the workflow. The sample data is used only to build and preview the transformation. It isn't passed to the workflow.

Sample data requirements

• The file can contain up to 500 rows.
• Supported file formats: .CSV, .XML feed, .JSONL, .JSON
• XML files must have the structure of an RSS feed: the rss element contains a channel element, and each record is a separate item element inside channel. Each item element becomes one row. For example, Google Merchant Center product feeds have this structure. If the elements that hold the records have a different name (for example, product), the file isn't read and the preview shows no data.

Add the sample

Select one of the options:

From workflow contextFrom external file

This option is available when you create a transformation in a Data Transformation Studio node which is directly preceded by a node that provides data, such as Local File. Only the directly preceding node is available as the source of sample data.

1. Select the From workflow context tab.
2. Next to the node that provides the data, click Get sample data.
   If the node doesn't contain data yet (for example, no file is uploaded in the Local File node), the button is inactive.

1. If you create a transformation from a workflow, select the From external file tab.
2. Click Upload a new file or drag one here and select the file, or drag the file onto that area.
3. For CSV files, verify the Delimiter, Quotation mark, and Escape character settings. They must match the separators used in the file.
4. Optionally, to check if the file is read correctly, click Preview sample data.
5. Click Apply.

Result: The preview of the sample data appears in the Source tab.

To replace the sample data, in the Source section on the right, click Replace. The existing transformation steps remain on the list, but they aren't applied to the new sample data. Steps that refer to columns which don't exist in the new sample data are reported as issues.

Example sample files

name,surname,email_address Dwight,Addams,dwight.addams@test.com Martin,Smith,martin.smith@test.com Kurt,Doe,kurt.doe@test.com

customId,orderId,paymentInfo,finalUnitPriceAmount,productQuantity,sku,eventTimestamp,productName,productPriceAmount 123345,448362,cash,13.35,1,642,13.02.2022,shoe1,13.35 123345,448362,cash,40,1,453,13.02.2022,shoe2,40 345678,45678990,online,13.99,1,726,13.02.2022,shoe3,13.99

Select a goal

Optionally, you can select the goal of the transformation, for example, an import of transactions to Synerise. When you select a goal, the Output section shows the list of columns that the output data must contain. The system checks which of these columns are already present in the output and marks them as completed.

If your workspace uses non-unique emails, email isn't available as a profile identifier in the Import profiles, Import transactions, and Import events goals.

If you want to create a transformation diagram without a specific goal and you know the structure of the output data, skip this step.

1. In the Output section, click Choose goal.
2. On the left pane, select a goal:
   • Import Profiles
   • Import Transactions
   • Import Vouchers
   • Import Events
   • Import Promotions
   • Create New Customer List (Google Ads)
   • Update Customer List (Google Ads)
   • Remove Customer List (Google Ads)
   • Upload Profiles to Facebook Custom Audience

   

3. On the right pane, check the list of columns required by the goal.
4. Click Apply.
   Result: The Output section shows the required columns and how many of them the output data already contains.

To select a different goal, in the Output section, click Change goal.

Add transformation steps

Transformation steps are performed in the order in which they appear on the list, from top to bottom. Each step works on the result of the previous step.

1. Click Add step.
   
   TIP: To insert a step between existing steps, click the Add step button in the place where you want to add the step.
   

2. From the list, select the transformation. You can search for it by name or filter the list with the Columns and Values tabs.

   

   • Column actions:
     • Add Column
     • Remove Columns
     • Enrich with Catalog
     • Merge Columns
     • Rename Columns
     • Reorder Columns
   • Value actions:
     • Decrypt Data
     • Edit Values
     • Encrypt Data
     • Filter Rows
     • Regex Replace
3. Configure the step according to the instructions in the article about the selected transformation.
4. Click Apply.
   Result: The step appears on the list with a summary of its settings.
5. To check the result of the transformation, open the Output tab in the preview.
6. Repeat steps 1-5 for each transformation you want to perform.

Manage the steps

On the list of steps, you can:

• edit a step by clicking it,
• change the order of the steps by dragging them by the handle on the left side of the step,
• duplicate a step by clicking the duplicate icon in the upper-right corner of the step,
• delete a step by clicking the trash icon in the upper-right corner of the step.

Rules and error handling in a step

• A single step can contain more than one rule. For example, in one Add column step, you can add several columns. To add a rule, in the configuration of the step, click Add rule. The rules are performed one after another, in the order in which they appear in the step. To change the order, drag a rule by the handle on its left side.
• Error handling applies to the entire step, including all of its rules:

You can define what happens with rows in which the step fails (for example, as a result of Jinja rendering). From the dropdown list next to Add rule, select one of the following options:

• Skip row if error occurred - It omits the row when an exception occurs.
• Skip if result is null or empty string - It omits rows with empty strings and null (which may be the result of rendering Jinja).
• Stop further transformation - The transformation stops at the row with an error. The data before the line that caused the error is transformed (and imported if the transformation is used in the Data Transformation Studio node).
• Insert null if error occurred - Wrong values are replaced with null. If you use such a transformation for the import of profiles, the existing value of an attribute is cleared and replaced with the null value.
• Insert empty string if error occurred - Wrong value is removed and the cell remains empty.

Check the issues

If the transformation contains problems, a warning icon with the number of issues appears in the upper-right corner of the Studio. To see the list of issues, click the icon. The list includes:

• errors in transformation steps, for example, when a step refers to a column that doesn't exist in the sample data. To correct the step, click Edit next to it.
• columns required by the goal that are missing in the output data.

If the output data doesn't contain all columns required by the goal, you can't apply the transformation in the workflow.

Save the transformation

The saving options depend on where you opened the Studio.

Transformations library

The Automation Hub > Transformations list contains all transformations saved in your workspace. For each transformation, you can see its goal, creation and update dates, and the author.

Access settings

You can limit which users or roles can manage a specific transformation. See Change access settings.

What's next

Use the transformation in the "Data Transformation Studio" node while preparing a workflow that transforms data.