increment

Sort a data extract and add a new column displaying the difference between each row and the previous row.

This guide explains how to configure the increment instruction. To learn about another instruction, go back to the Available custom script instructions overview.

Introduction

Use the increment instruction to sort a data extract based on given column names and a column containing numerical values. Once sorted, a new column is added to the data extract containing the numerical difference between the value in the current row and the value in the previous row.

This transformation is similar to the sumup transformation. The increment transformation calculates the difference between rows and the sumup transformation calculates the sum total of rows.

To use the increment custom script instruction, the values used in the instruction must be in numeric format. If necessary, use the convertnumbers custom script instruction to convert string values to a numeric format. Add the convertnumbers instruction to the same custom script transformation before the increment instruction.

Creating a custom script transformation using the increment instruction

To create and configure a custom script using the increment instruction, follow these steps:

Create a custom script transformation.
In the Configuration step, select the increment instruction.
To configure the custom script instruction, fill in the required fields below, as well as any optional fields you require:

Input Field: Enter the name of the column that contains the numeric values for which the transformation will calculate the difference.
Output Field: Enter the name of the new column that you want to contain the calculated differences.
Keys: Enter the names of one or more columns that you want to use to sort the data. Every new unique combination of Key fields will reset the difference calculation to 0.; If you do not enter any column names in this field, your data will not be sorted. The rows will stay in the order in which they were fetched from your data source.

Subtable

Enter the name for a subtable that you want to use within this custom script.

A subtable is a temporary table that only exists for this custom script. You can apply additional instructions within the same custom script to the subtable. However, the subtable cannot be used in any other custom scripts.

If a subtable does not exist for the current custom script, the transformation is applied to the data extract, and the enriched data is output into the subtable. If the subtable already exists for the custom script, the subtable is used as the input for the transformation and optionally as the output.

Example

Transformation configuration

Input Field: Clicks
Output Field: Click Difference
Keys: Campaign; Ad Group

Data table before transformation

Date	Campaign	Ad Group	Clicks
2022-06-20	Brand	ecommerce	7
2022-06-20	Brand	socials	3
2022-06-20	Dashboard	ecommerce	18
2022-06-21	Brand	ecommerce	4
2022-06-21	Brand	social	6
2022-06-21	Dashboard	ecommerce	5
2022-06-22	Brand	ecommerce	11
2022-06-22	Brand	social	45

Data table after transformation

The table has been sorted by the values in the Campaign column, then by Ad Group. The difference between the Clicks value in each row and the previous row is displayed in the Click Difference column.

Date	Campaign	Ad Group	Clicks	Click Difference
2022-06-20	Brand	ecommerce	7	7
2022-06-21	Brand	ecommerce	4	-3
2022-06-22	Brand	ecommerce	11	7
2022-06-20	Brand	socials	3	3
2022-06-21	Brand	social	6	3
2022-06-22	Brand	social	45	39
2022-06-20	Dashboard	ecommerce	18	18
2022-06-21	Dashboard	ecommerce	5	-13

The thick border lines in the table above will not appear in your data extract after the increment transformation. The thick border lines have been added to highlight where the difference calculation resets and how the data extract is sorted.