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.
To enable group mode, you make a normal Grid API request, with two changes:
- Specify
groupByin one or morecolumnsin the request. - Specify
aggregateFunctionfor 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:
{
"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:
{
... // 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]
]
}The Grid API supports the following aggregate functions, depending on the column type:
| Aggregation Function | What it Does | Supported Column Types |
|---|---|---|
count_distinct | Counts the number of unique values in the group | string, entity_reference |
count_rows | Counts the number of rows in the group | N/A (this function does not require a column ID) |
sum | Sums the values in the group | number |
avg | Averages the values in the group | number |
median | Finds the median value in the group | number |
min | Finds the minimum value in the group | number, timestamp |
max | Finds the maximum value in the group | number, timestamp |
percentile_25 | Finds the 25th percentile value in the group | number |
percentile_50 | Finds the 50th percentile value in the group | number |
percentile_75 | Finds the 75th percentile value in the group | number |
percentile_90 | Finds the 90th percentile value in the group | number |
percentile_95 | Finds the 95th percentile value in the group | number |
percentile_99 | Finds the 99th percentile value in the group | number |
standard_deviation | Finds the standard deviation of the values in the group | number |
youngest | Returns the most recent value according to the entity's age | timestamp |
oldest | Returns the oldest value according to the entity's age | timestamp |
list_unique_items | Returns the list of unique values in the group | entity_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.
{
"columnId": "RUN_END_MONTH",
"type": "timestamp",
... // other properties omitted for brevity
// This column supports the `max` and `min` aggregate functions:
"supportedAggregateFunctions": [
"max",
"min"
],
}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.
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_DOMAINandCOOKIE_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