Skip to content
/
Grid API Grouping
Last updated

Grid API Grouping

By default, the Grid API returns rows of data, where each row represents a single record. But the Grid API can also provide aggregate data, where each row represents a group of records and includes statistics for the group.

This is similar to the GROUP BY feature in SQL.

How to Use Group Mode

To enable group mode, you make a normal Grid API request, with two changes:

  1. Specify groupBy in one or more columns in the request.
  2. Specify aggregateFunction for all non-grouped columns.

In group mode, every column must either specify groupBy or use an aggregateFunction.

All other Grid API features work the same in group mode: filtering, pagination, and sorting, etc.

For example, to see the list of unique tags found across all your websites, with the total number of pages where each tag was found, you can use the following request:

Example grouped request
{
  "columns": [
    {
      "columnId": "TAG",
      "groupBy": true // This is the key. Each row will represent a unique tag (e.g., Adobe Analytics)
    },
    {
      // Return the total number of unique pages where each tag was found
      "aggregateFunction": "count_distinct",
      "aggregatedColumn": {
        "columnId": "FINAL_PAGE_URL"
      }
    }
  ],
  "sortBy": [
    {
      // Sort by the total number of unique page for each tag,
      // most frequent tags first
      "columnIndex": 2,
      "sortDesc": true
    }
  ]
}

This would return a list of tags, where each row represents a unique tag and the total number of unique pages where that tag was found:

Example grouped response
{
  ... // metadata and filters omitted for brevity
  "rows": [
    [["Google Tag (gtag)", 655],  53872],
    // ^^^^^^^^^^^^^^^^^   ^^^    ^^^^^
    //     Tag name      Tag ID   Total unique page count
    [["Google Analytics 4", 940], 52503],
    [["Google Fonts", 1167], 51811],
    [["Google reCAPTCHA Loader", 1277], 48912],
    [["Amazon Advertising", 523], 2968],
    [["Floodlight Counter", 15], 2911],
    [["Adobe Launch", 556], 1490]
  ]
}

Supported Aggregate Functions

The Grid API supports the following aggregate functions, depending on the column type:

Aggregation FunctionWhat it DoesSupported Column Types
count_distinctCounts the number of unique values in the groupstring, entity_reference
count_rowsCounts the number of rows in the groupN/A
(this function does not require a column ID)
sumSums the values in the groupnumber
avgAverages the values in the groupnumber
medianFinds the median value in the groupnumber
minFinds the minimum value in the groupnumber, timestamp
maxFinds the maximum value in the groupnumber, timestamp
percentile_25Finds the 25th percentile value in the groupnumber
percentile_50Finds the 50th percentile value in the groupnumber
percentile_75Finds the 75th percentile value in the groupnumber
percentile_90Finds the 90th percentile value in the groupnumber
percentile_95Finds the 95th percentile value in the groupnumber
percentile_99Finds the 99th percentile value in the groupnumber
standard_deviationFinds the standard deviation of the values in the groupnumber
youngestReturns the most recent value according to the entity's agetimestamp
oldestReturns the oldest value according to the entity's agetimestamp
list_unique_itemsReturns the list of unique values in the groupentity_reference with isList: true
(for columns with arrays of values)

The self-documenting schema endpoint will tell you exactly which aggregate functions are supported for each column. To query the schema endpoint, see the Grid API Columns page.

Example schema endpoint response
{
    "columnId": "RUN_END_MONTH",
    "type": "timestamp",
    ... // other properties omitted for brevity

    // This column supports the `max` and `min` aggregate functions:
    "supportedAggregateFunctions": [
        "max",
        "min"
    ],
}

Multi-Column Grouping

You can group by multiple columns as well, which allows powerful aggregation. For example, you can group by COOKIE_NAME and FINAL_PAGE_DOMAIN to see which cookies are the most common across each of your domains.

Examples

Group reporting allows many powerful reports. Here are a few examples:

  • Tag Inventory: gropu by TAG, sorted by the number of pages where they were found
  • Cookie Inventory: group by COOKIE_DOMAIN and COOKIE_NAME (2 columns), sorted by the number of pages where they were found
  • Domain Inventory: group by domain, sorted by the number of pages where they were found
  • Accessibility Issues: group by ACCESSIBILITY_ISSUE_IMPACT, sorted by the number of pages where they were found, to find the most impactful accessibility issues
Previous page
Next page