aggregate

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

Introduction

Use the aggregate enrichment 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 the enrichment

For more information on creating an enrichment, see Using custom scripts.

Configuring the enrichment

To configure the enrichment, fill in the following fields. Required fields are marked with an asterisk (*).

Key Fields

Specify the fields 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

Specify the rules used to calculate the new values.

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

• In Input field, specify the field 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

If the key columns of the source table are the same as the columns specified in Key Fields, enable this option to speed up the enrichment process.

Subtable

Enter the name for a subtable that you want to contain the enriched data. The enrichment is applied to the whole data extract, then the enriched data is output into the subtable you have named here.

This subtable is a temporary table, which means it 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.

Example

Enrichment configuration

Key Fields

Campaign

Ad Group

Rulesets

Output field: Sum_Clicks

Input field: Clicks

Calculation: sum

Data table before enrichment

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 enrichment

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