Adding rules to the Create or Edit Columns transformation

This guide explains how to add rules to the Create or Edit Columns transformation.

Introduction

When configuring the Create or Edit Columns transformation, apply rules to find certain values. Based on what is found by these rules, the transformation either adds new values or updates existing values in the target column. Add multiple conditions to the search criteria using AND/OR logic.

For example, add rules to find campaigns that have less than 1000 clicks. Only the campaigns that match the search criteria will satisfy the rule, causing the transformation to populate the target column.

Prerequisites

Before you read this reference, perform all of the following actions:

• Create a datastream.

• Create a Create or Edit Columns transformation.

• Find the names of the columns in the data extract containing the data on which to base the rule criteria.

Creating rules to populate target columns

To set up rules that will populate the target column using the rule criteria, follow these steps:

1. Go to the Transformations page.

2. In the table, click on the name of the transformation to which you want to add rules.

3. After the Name new column or Column to edit field, enable the Add rules to build your instruction toggle. This will activate several sections in the page in which the rules are set up.

4. The WHEN section of the page is where you define the search criteria for the rule you are setting up:

   Column

   Select the column which contains the values upon which the rule is based. For example, to set up a rule that searches the data extract for rows that have less than 1000 clicks, select the column that contains the clicks data (typically, clicks should be a metric).

   Operator

   From the drop-down field, select the operator for the rule. The list of operators differs depending on if a metric or dimension is selected in the Column field. For example, to set up a rule that searches for rows of data that have less than 1000 clicks (a metric), select the operator is less than.

   For dates, choose one of the following operators:

   • before

   • after

   For columns with Boolean values, choose one of the following operators:

   • is true

   • is false

   Case-sensitive

   (Optional) Select this checkbox to make the search criteria for the rule case sensitive. Once selected, the values in the selected column must match the values in the Value column exactly to satisfy the search criteria. This checkbox will not be present if a metric is selected in the Column field.

   Value

   Enter the value for the rule search criteria. For example, to set up a rule that searches for rows of data that have less than 1000 clicks, enter 1000 into the Value field.

   The Value field may not be active if certain columns or operators are selected. For example, selecting a column with a Boolean value or the operator is empty will cause the Value field to be hidden as it is not required in these cases.

   + And

   (Optional) Click + And to add another condition to the rule criteria and fill in the Column, Operator, and Value fields for the additional condition.

   If an And condition is added to the rule search criteria, every condition must be met for the rule to populate the target column. For example, if a rule has three And conditions, all three must be met to populate the target column.

   + Or

   (Optional) Click + Or to add another condition to the rule criteria and fill in the Column, Operator, and Value fields for the additional condition.

   If an Or condition is added to the rule search criteria, only one of the conditions must be met for the rule to populate the target column. For example, if a rule has three Or conditions, only one of these three has to be met to populate the target column.

5. The THEN section of the page is where you define the value to populate the target column when the rule search criteria finds a matching value. In Value, enter the value to populate the target column. This value can one or a combination of the following:

   Static value

   Enter text to populate the target column. For example, enter the value new to populate every value in the column with new.

   Existing column values

   In the Value field, click + Add existing values and select the column that contains the values to populate the target column. You can select multiple columns.

   Static values and values from existing columns

   Combine static values with existing column values to create a new value to populate the target column. For example, you can append a static value of _UK to an existing column such as account_name to create account_name_UK. Use special characters such as _ or - to combine multiple existing columns into a single value. For example, column1_column2.

6. The OTHERWISE section of the page is where you define the value that populates the target column when the rule search criteria does not find a matching value. In Value, enter the value to populate the target column. You can enter a static value, values from existing columns, or a combination of the two.

7. You can preview your transformation in the Preview tab. Here, you can see how your current transformation instructions will transform the data you have previously collected using the selected datastream.

   Click Refresh Preview at the bottom of the Configuration tab to see the preview of your latest changes.

   For more information about the preview, see Previewing transformations.

8. Click Save.

As a result, rules have been added to your Create and edit columns transformation.

Adding or copying rule conditions

Multiple conditions can be added to the rule search criteria. The multiple conditions populate the same target column with a variety of user-defined values. For example, add multiple conditions to the search criteria to find:

• Campaigns with less than 1000 clicks in the UK and populate the target column with a new value sub1000_UK.

• Campaigns with less than 1000 clicks in Austria and populate the target column with a new value sub1000_AU.

Adding new conditions to a rule allows you to populate the target columns with multiple values. It is recommended to copy rule conditions if the search criteria is similar for both conditions.

To add or copy rule conditions, follow these steps:

1. Go to the Transformations page.

2. Click on the name of the transformation to which you want to add rules.

3. (Optional) To copy a rule condition, click Clone on the right side of the page and update WHEN and THEN sections of the condition.

4. (Optional) To add a rule condition, in the middle of the page click + Add condition and populate the WHEN and THEN sections of the new rule condition.

5. Click Save.

As a result, new rule conditions have been added to your Create and edit columns transformation.