aggregate

Convert existing fields in the data extract by performing calculations on selected values.

Introduction

Use the aggregate instruction to convert existing fields in the data extract by performing calculations on selected values. For example, calculate the sum of values that belong to a specific combination of dimensions.

Creating a custom script transformation using the aggregate instruction

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

1. Create a custom script transformation.

2. In the Configuration step, select the aggregate instruction.

3. To configure the custom script instruction, fill in the required fields below, as well as any optional fields you require:

Key fields (required)

Specify the columns whose unique combinations of values are used in the calculation.

Select a column in one of the following ways:

• Select String and enter the name of the column.

• Select Integer and enter the position of the column in the data extract as an index value. Counting starts at 0. To select the first column, enter 0. To select the second column, enter 1. To select the last column in the data extract, enter -1.

Rulesets (required)

Specify the rules used to calculate the new values.

• In Output field, specify the name of the new column which contains the calculated values.

• In Input field, specify the column whose values are used in the calculation.

• To the left of Input field, specify the data type.

• In the bottom field, select one of the following calculations to perform:

  • Select sum to display the sum of the values for each unique combination of dimension values you specified.

  • Select min to display the minimum value for each unique combination of dimension values you specified.

  • Select max to display the maximum value for each unique combination of dimension values you specified.

  • Select len to display the number of values found in the unique combination of dimension values you specified.

  • Select list to display the values in a list for each unique combination of dimension values you specified.

  • Select dict to display the values in a dictionary as key-value pairs for each unique combination of dimension values you specified.

  • Select avg to display the average of the values for each unique combination of dimension values you specified.

  • Select json to display the values in JSON format as key-value pairs for each unique combination of dimension values you specified.

Presorted by key

Select this checkbox to speed up the transformation if the key columns of the source table are the same as the columns specified in Key fields.

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

Key Fields

Campaign

Ad Group

Rulesets

Output field: Sum_Clicks

Input field: Clicks

Calculation: sum

Data table before transformation

Campaign	Ad Group	Clicks
Brand	media	7
Brand	ecommerce	3
Dashboard	media	18
Brand	ecommerce	4
Dashboard	media|social	5
Dashboard	media	11

Data table after transformation

Campaign	Ad Group	Sum_Clicks
Brand	media	7
Brand	ecommerce	7
Dashboard	media	29
Dashboard	media|social	5