Articles in this section

See more

Convert Fields - "aggregate"

Avatar
Tom Sherwood
  • Updated
Follow

Take values for all lines with a matching key, and perform actions according to the rules specified (for example calculate the sum of the values or create layered metrics). 

Before running an aggregate function, it is a prerequisite that the relevant fields be converted from "string" into an acceptable numeric format using the 'convertnumbers' transformation.   
Available Functions:
  • sum: 1 + 1 = 2
  • len: counts rows that were processed.
  • avg: calculates the mean average.
  • min: returns minimum value of aggregated column. Works with non-numeric data types as well.
  • max: returns maximum value of aggregated column.
  • json: creates a dictionary with field_to_be_aggregated as an array of [column_as_key, column_as_value].

Example 1

Prerequisite Convertnumbers Transformation:
example_1_convertnumbers_prerequisite.png

Aggregate Transformation:
aggregate_example_1.png

Example Input 1
Campaign Ad Group Clicks
Brand media 7
Brand ecommerce 3
Dashboard media 18
Brand ecommerce 4
Dashboard media|social 5
Dashboard media 11
Example Output 1
Campaign Ad Group Sum_Clicks Max_Clicks
Brand ecommerce 7 4
Brand media 7 7
Dashboard media 29 18
Dashboard media|social 5 5

 

Example 2

Prerequisite Convertnumbers Transformation:
example_2_convertnumbers_prerequisite.png

Aggregate Transformation:
aggregate_example_2.png

Example Input 2
Campaign Ad Group Sum_Clicks Max_Clicks
Brand ecommerce 7 4
Brand media 3 7
Dashboard media 29 18
Dashboard ecommerce 5 5
Example Output 2
Campaign Dict_Sum Dict_Max
Brand {“ecommerce”:”7”,”media”:”7”} {“ecommerce”:”7”,”media”:”7”}
Dashboard {“media”:”29”,”media|social”:”5”} {“media”:”18”,”media|social”:”5”}

 

I Ain't Afraid of No Code!

Example 1 
[
    [
        "aggregate",
        {
            "subtable": null,
            "presorted": false,
            "rules": [
                [
                    "Sum_Clicks",
                    "Clicks",
                    "sum"
                ],
                [
                    "Max_Clicks",
                    "Clicks",
                    "max"
                ]
            ],
            "key": [
                "Campaign",
                "Ad group"
            ],
            "_comment": null
        }
    ]
]

Example 2

[
    [
        "aggregate",
        {
            "subtable": null,
            "presorted": false,
            "rules": [
                [
                    "Dict_Clicks",
                    [
                        "Ad Group",
                        "Sum_Clicks"
                    ],
                    "json"
                ],
                [
                    "Dict_Max",
                    [
                        "Ad Group",
                        "Max_Clicks"
                    ],
                    "json"
                ]
            ],
            "key": [
                "Campaign"
            ],
            "_comment": null
        }
    ]
]

param str subtable: Name a subtable in which output will be stored (will create subtable where necessary).

param boolean presorted: Default is false.

param list rules: Rules to specify how aggregation should be performed. Possible operators are: sum , len , avg , min , max , json

param list key: Field(s) to be used as a key for aggregation.

param str comment: A comment that will appear in the Transformation UI.

Related articles

Comments

0 comments

Article is closed for comments.