Overview

The Google Analytics Data API v1 allows you to generate pivot tables. Pivot tables are a data summarization tool that visualize data by rearranging the information in the table by pivoting (rotating) your data on one or multiple dimensions.

As an example, consider the following raw data table:

Raw data table

Using this data it is possible to construct a pivot table, breaking down the sessions data by browser, with country and language dimensions selected as additional pivots.

Pivoted data table

Shared Features with Core Reports

Pivot reporting requests have the same semantics with Core report requests for many shared features. For example pagination, Dimension Filters and User Properties, behave the same in Pivot Reports as Core Reports. This guide focuses on pivot reporting features. To familarize yourself with the Core Reporting functionality of the Data API v1, read the reporting basics guide, as well as advanced use cases guide.

Pivot reporting methods

The Data API v1 supports pivot functionality in the following reporting methods:

  • runPivotReport This method returns a customized pivot report of you Google Analytics event data. Each pivot describes the visible dimension columns and rows in the report response.

  • batchRunPivotReports This is a batch version of the runPivotReport method which allows generating multiple reports using a single API call.

Selecting a Reporting Entity

All methods of the Data API v1 require the Google Analytics 4 property identifier to be specified inside a URL request path in the form of properties/GA4_PROPERTY_ID, such as:

  POST  https://analyticsdata.googleapis.com/v1beta/properties/GA4_PROPERTY_ID:runPivotReport

The resulting report will be generated based on the Google Analytics event data collected in the specified Google Analytics 4 property.

If you are using one of the Data API client libraries, there is no need to manipulate the request URL path manually. Most API clients provide a property parameter that expects a string in the form of properties/GA4_PROPERTY_ID. See Quick start guide for examples of using the client libraries.

Pivot Report Request

To construct a request with a pivot table, use either runPivotReport or batchRunPivotReports method.

To request pivoted data, you can construct a RunPivotReportRequest object. We recommend starting with these request parameters:

  • A valid entry in the dateRanges field.
  • At least one valid entry in the dimensions field.
  • At least one valid entry in the metrics field.
  • At least two valid pivot entries in the pivots field.

Here is a sample request with the recommended fields:

HTTP

POST https://analyticsdata.googleapis.com/v1beta/properties/GA4_PROPERTY_ID:runPivotReport
  {
    "dateRanges": [{ "startDate": "2020-09-01", "endDate": "2020-09-15" }],
    "dimensions": [
        { "name": "browser" },
        { "name": "country" },
        { "name": "language" }
      ],
    "metrics": [{ "name": "sessions" }],
    "pivots": [
      {
        "fieldNames": [
          "browser"
        ],
        "limit": 5
      },
      {
        "fieldNames": [
          "country"
        ],
        "limit": 250
      },
      {
        "fieldNames": [
          "language"
        ],
        "limit": 15
      }
    ]
  }

Pivots

Use Pivot objects in the pivot field of the request body to define report pivots. Each Pivot describes the visible dimension columns and rows in the report response.

The Data API v1 supports multiple pivots as long as the product of the limit parameter for each pivot does not exceed 100,000.

Below is a snippet demonstrating the usage of pivots to build a report of session counts by country, pivoted by the browser dimension. Note how the query uses the orderBys field for sorting, and the limit and offset fields to implement pagination.

    "pivots": [
      {
        "fieldNames": [
          "country"
        ],
        "limit": 250,
        "orderBys": [
          {
            "dimension": {
              "dimensionName": "country"
            }
          }
        ]
      },
      {
        "fieldNames": [
          "browser"
        ],
        "offset": 3,
        "limit": 3,
        "orderBys": [
          {
            "metric": {
              "metricName": "sessions"
            },
            "desc": true
          }
        ]
      }
    ],
    ...

Dimensions

Dimensions describe and group event data for your website or app. The city dimension, for example, indicates the city ("Paris" or "New York") from which each event originated. In a report request, you can specify zero or more dimensions.

Dimensions must be defined inside the dimensions field of a request body. To be visible in a report, those dimensions must also be listed in the fieldNames field of a Pivot object. A dimension will not be visible in a report if it is not used in any pivot of a pivot query. Not every dimension must be present in a pivot's fieldNames. Dimensions can be used exclusively in filters and not in the fieldNames of any pivot.

Below is a snippet demonstrating the usage of dimension and fieldNames fields for a table with browser, country and language pivots:

    "pivots": [
      {
        "fieldNames": [
          "browser"
        ],
        "limit": 5,
        "orderBys": [
          {
            "metric": {
              "metricName": "sessions"
            },
            "desc": true
          }
        ]
      },
      {
        "fieldNames": [
          "country"
        ],
        "limit": 250,
        "orderBys": [
          {
            "dimension": {
              "dimensionName": "country"
            }
          }
        ]
      },
      {
        "fieldNames": [
          "language"
        ],
        "limit": 10
      }
    ],

Metrics

Metrics are quantitative measurements of event data for your website or app. In a report request, you can specify one or more metrics. See the API Metrics for a full list of API Metric names available to be specified in requests.

In pivot report requests, metrics are defined using the metrics field of the request body, which is similar to Core Reporting methods.

An example below specifies sessions count to be used as a metric value in a report:

    "metrics": [
      {
        "name": "sessions"
      }
    ],

Metric aggregations

Use the metricAggregations field of a Pivot object to calculate aggregated metric values for each pivot.

Aggregations will only be calculated if the metricAggregations field is specified in a request.

Below is a snippet of a query that requests the totals for the browser pivot dimension:

"pivots": [
  {
    "fieldNames": [
      "browser"
    ],
    "limit": 10,
    "metricAggregations": [
      "TOTAL",
    ]
  },
  ...

The calaulated metrics are returned in the aggregates field of the RunPivotReportResponse object. For aggregated metric rows, the dimensionValues field contains a special value of RESERVED_TOTAL, RESERVED_MAX or RESERVED_MIN.

  "aggregates": [
    {
      "dimensionValues": [
        {
          "value": "Chrome"
        },
        {
          "value": "RESERVED_TOTAL"
        },
        {
          "value": "RESERVED_TOTAL"
        }
      ],
      "metricValues": [
        {
          "value": "4"
        }
      ]
    },
    {
      "dimensionValues": [
        {
          "value": "Firefox"
        },
        {
          "value": "RESERVED_TOTAL"
        },
        {
          "value": "RESERVED_TOTAL"
        }
      ],
      "metricValues": [
        {
          "value": "6"
        }
      ]
    },
  ....

  }

Pagination

Similar to Core Reporting methods, pivot requests allow to specify the limit and offset fields in the Pivot object to implement pagination. Pagination settings are applied to each pivot individually. The limit field is required for every Pivot object in order to limit the report cardinality.

The Data API v1 supports multiple pivots as long as the product of the limit parameter for each pivot does not exceed 100,000.

Below is a snippet demonstrating the usage of offset and limit fields to retrieve the next five language dimensions with an offset of 10:

      {
        "fieldNames": [
          "language"
        ],
        "offset": 10,
        "limit": 5
      }

Filtering

Similar to the Core Reporting functionality, a request scoped dimension filter must be used if dimensions filtering is desired in a pivot reporting request.

Sorting

Ordering behavior of pivot report queries can be controlled for each pivot individually using the orderBys field of a Pivot object, which contains a list of OrderBy objects.

Every OrderBy can contain one of the following:

This example shows a snippet for a pivot definition that pivots the report on the browser dimension, ordering results by the sessions metric in descending order.

      {
        "fieldNames": [
          "browser"
        ],
        "limit": 5,
        "orderBys": [
          {
            "metric": {
              "metricName": "sessions"
            },
            "desc": true
          }
        ]
      }

Report Response

The Pivot Report Response of a pivot report API request is primarily a header and rows.

Response Headers

The pivot report header consists of PivotHeaders, DimensionHeaders and MetricHeaders which list the columns in the pivot report.

For example, a report with browser, country, language pivot dimensions and sessions metric will yield headers like this:

{
  "pivotHeaders": [
    {
      "pivotDimensionHeaders": [
        {
          "dimensionValues": [
            {
              "value": "Chrome"
            }
          ]
        },
        {
          "dimensionValues": [
            {
              "value": "Firefox"
            }
          ]
        },
        ...

      ],
      ...
    },
    {
      "pivotDimensionHeaders": [
        {
          "dimensionValues": [
            {
              "value": "United States"
            }
          ]
        },
        {
          "dimensionValues": [
            {
              "value": "Canada"
            }
          ]
        },
        ...

      ],
      ...
    },
    {
      "pivotDimensionHeaders": [
        {
          "dimensionValues": [
            {
              "value": "English"
            }
          ]
        },
        {
          "dimensionValues": [
            {
              "value": "French"
            }
          ]
        },
        ...

      ],
      ...
    }
  ],
  "dimensionHeaders": [
    {
      "name": "browser"
    },
    {
      "name": "country"
    },
    {
      "name": "language"
    }
  ],
  "metricHeaders": [
    {
      "name": "sessions",
      "type": "TYPE_INTEGER"
    }
  ],
  ...

}

The chart below illustrates the role of each component of the Pivot Report Response in rendering the pivot report:

Raw data table

Response Rows

Pivot report response of runPivotReport and batchRunPivotReports methods is implemented differently compared to the Core Reporting methods like runReport and batchRunReports in a way that each pivot report response row represents a single cell of the table, whereas in a regular report a single response row represents a complete table line.

Below is a fragment of a pivot report response for a query with "browser", "country", "language" pivot dimensions and sessions metric. Each cell of the pivoting report is returned individually:

  "rows": [
    {
      "dimensionValues": [
        {
          "value": "Chrome"
        },
        {
          "value": "United States"
        },
        {
          "value": "English"
        }
      ],
      "metricValues": [
        {
          "value": "1"
        }
      ]
    },
    {
      "dimensionValues": [
        {
          "value": "Firefox"
        },
        {
          "value": "Canada"
        },
        {
          "value": "French"
        }
      ],
      "metricValues": [
        {
          "value": "3"
        }
      ]
    },
    ...

  ]

This data corresponds to the two cells highlighted in the table below:

Raw data table

Client libraries

See the Quick start guide for an explanation on how to install and configure client libraries.

Below is an example using the Python client library that runs a pivot query to build a report of session counts by country, pivoted by the browser dimension.

from google.analytics.data_v1beta import BetaAnalyticsDataClient
from google.analytics.data_v1beta.types import DateRange
from google.analytics.data_v1beta.types import Dimension
from google.analytics.data_v1beta.types import Metric
from google.analytics.data_v1beta.types import OrderBy
from google.analytics.data_v1beta.types import Pivot
from google.analytics.data_v1beta.types import RunPivotReportRequest


def run_sample():
    """Runs the sample."""
    # TODO(developer): Replace this variable with your Google Analytics 4
    #  property ID before running the sample.
    property_id = "YOUR-GA4-PROPERTY-ID"
    run_pivot_report(property_id)


def run_pivot_report(property_id="YOUR-GA4-PROPERTY-ID"):
    """Runs a pivot query to build a report of session counts by country,
    pivoted by the browser dimension.."""
    client = BetaAnalyticsDataClient()

    request = RunPivotReportRequest(
        property=f"properties/{property_id}",
        date_ranges=[
            DateRange(start_date="2021-01-01", end_date="2021-01-30"),
        ],
        pivots=[
            Pivot(
                field_names=["country"],
                limit=250,
                order_bys=[
                    OrderBy(
                        dimension=OrderBy.DimensionOrderBy(dimension_name="country")
                    )
                ],
            ),
            Pivot(
                field_names=["browser"],
                offset=3,
                limit=3,
                order_bys=[
                    OrderBy(
                        metric=OrderBy.MetricOrderBy(metric_name="sessions"), desc=True
                    )
                ],
            ),
        ],
        metrics=[Metric(name="sessions")],
        dimensions=[
            Dimension(name="country"),
            Dimension(name="browser"),
        ],
    )
    response = client.run_pivot_report(request)
    print_run_pivot_report_response(response)


def print_run_pivot_report_response(response):
    """Prints results of a runPivotReport call."""
    print("Report result:")
    for row in response.rows:
        for dimension_value in row.dimension_values:
            print(dimension_value.value)

        for metric_value in row.metric_values:
            print(metric_value.value)


Demo application

See the Google Analytics API v1 Pivot Report Demo application for an example of how to build and display a pivot report using JavaScript.