Add support for GA4 pivot reports

[techanvil]

Feature Description

As discussed on Slack, while we are initially implementing the Audience Tiles to use separate, per-audience reports to retrieve the cities and "top content" metrics (one report per audience per metric), we can reduce the number of reports needed by using pivot reports. This will allow us to retrieve metric data for all of the audiences in a single report (i.e., one report per metric).

We should add support for running pivot reports, and refactor the Audience Tiles to use them.

This is not critical to the release and can be done post-launch, hence the P2 priority.

Please also note this comment relating to the AudienceTile and AudienceTiles refactoring: #8484 (comment)

Because of the amount of work involved, this ticket has been split, only the GA4 pivot report infrastructure should be worked on in this ticket. Updating the Audience Tiles should be completed in #8726.

---

Do not alter or remove anything below. The following sections will be managed by moderators only.

Acceptance criteria

• REST and datastore APIs should be added to Site Kit to provide support for retrieving Analytics pivot reports.
  • On the REST front, a new route: GET /google-site-kit/v1/modules/analytics-4/data/pivot-report.
  • On the datastore side, a new selector/resolver pair: getPivotReport().
• Update the mock data generator to generate valid responses to pivot reports.

Implementation Brief

Pivot Report Infrastructure

• Create a new class, SharedReportParsers, and move parse_dimensions, parse_dateranges and parse_orderby methods from includes/Modules/Analytics_4/Report.php into this class so that they can be shared by Report and PivotReport classes. Update both of these classes to use these methods from the new SharedReportParsers class.

• Create a new class, PivotReport, in includes/Modules/Analytics_4/PivotReport.php, based on the existing includes/Modules/Analytics_4/Report.php class with these differences:

  • Import the class Google\Site_Kit_Dependencies\Google\Service\AnalyticsData\Pivot as Google_Service_AnalyticsData_Pivot.
  • Create a new protected method parse_pivots, which takes Data_Request and returns Google_Service_AnalyticsData_Pivot[], modelled on the parse_orderby method.
    • In the array_map, create a new Google_Service_AnalyticsData_Pivot and use setFieldNames, setLimit and setOrderBys to build each pivot request value.

• Create a new class. Request, in includes/Modules/Analytics_4/PivotReport/Request.php, based on includes/Modules/Analytics_4/Report/Request.php, which extends PivotReport:

  • Import the class Google\Site_Kit_Dependencies\Google\Service\AnalyticsData\RunPivotReportRequest as Google_Service_AnalyticsData_RunPivotReportRequest.
  • In the create_request method:
    • The definition of $request should create a new Google_Service_AnalyticsData_RunPivotReportRequest.
    • Set keep empty rows on the request using $request->setKeepEmptyRows( true );
    • Parse and add, dimensions, dimensionFilters and metrics to the pivot report in the same way as the standard report request.
      • After parsing but before settings the dimensions to the request, add the hostName dimension using $dimensions[] = array('name' => 'hostName');.
        This is needed because of the hostname dimension filter we add to all requests here to filter out domains other than the WordPress sites domain.
    • Parse the request pivots with $pivots = $this->parse_pivots( $data ); and add them to the request using $request->setPivots( $pivots ).
    • Return the Google_Service_AnalyticsData_RunPivotReportRequest $request.
  • Create a new SharedCreateRequest class and extract common logic from the Request and PivotRequest classes' create_request methods, into this class.

REST Endpoint

• Create a new REST endpoint GET:pivot-report, modelled on GET:report, with the following differences:
  • Return a missing_required_param WP_Error if the pivots param is missing from the request.
  • The return statement should call runPivotReport GA API method.
• Add the new REST endpoint to the $datapoints array in get_datapoint_definitions, setting service to analyticsdata and sharable to true.

Pivot Reports store and getPivotReport selector

• Create a new isValidPivots util in the file assets/js/modules/analytics-4/utils/pivots-validation.js, based on assets/js/modules/analytics-4/utils/report-validation.js. A valid pivots object must:

  • Be an array of object.
  • Each object in the array must be isPlainObject
  • Must have a fieldNames prop which must contain and array of strings.
  • If a limit prop is present, it must be a number.
  • If a orderBys prop is passed it must validate using the existing isValidOrders helper.

• Create a new file assets/js/modules/analytics-4/datastore/pivot-report.js, based on the existing assets/js/modules/analytics-4/datastore/report.js file, which creates a new pivot reports datastore:

  • Create a new createFetchStore with the following keys:
    • baseName: getPivotReport
    • controlCallback returns API.get to the new pivot-report endpoint, passing the options, passed to normalizeReportOptions.
    • reducerCallback should update the pivotReports key within the state object with [ stringifyObject( options ) ]: report, to save reports to a unique caching key based on their options.
    • argsToParams should return { options }
    • validateParams should validate params similarly to the existing reports.js file with these key changes/additions:
      • pivots.length invariant violation: pivots must contain at least one value to be a valid pivot report request
      • isValidPivots(pivots) invariant violation: must validate a given pivots array using the isValidPivots util created above.
      • ! orderby.length invariant violation: orderbys may not be set at the parent level for pivot reports, if passed this must cause an invariant violation.
    • The baseInitialState should contain a single pivotReports key with an empty object by default pivotReports: {}.
    • baseResolvers should contain a single resolver called getPivotReport, which uses the getPivotReport selector to get the report from the store if present.
      • If this report does not already exist in the store, yield the actions fetchGetPivotReport passing through the options.
    • The baseSelectors should contain a single selector, getPivotReport which returns the report for the stringified key of the request options using pivotReports[ stringifyObject( options ) ]
  • Combine fetchGetReportStore with the initialState, selectors, and resolvers created in the last few steps and export the store and it's core components from this file.

• Import the new pivot-report store as pivotReport in assets/js/modules/analytics-4/datastore/index.js and include it in the main combineStores call to make the store available within the app.

Data Mock Updates

• Update getAnalytics4MockResponse in assets/js/modules/analytics-4/utils/data-mock.js to generate valid data when given a valid pivot report object.
• Update the tests to cover new pivot report cases in assets/js/modules/analytics-4/utils/data-mock.test.js

Test Coverage

• Create a new tests file assets/js/modules/analytics-4/datastore/pivot-report.test.js which tests the new store and the getPivotsReport selector.
• Update tests/phpunit/integration/Modules/Analytics_4Test.php adding comprehensive new tests of the new REST endpoint, and the pivot report functions including limits and ordering. Note: when making a pivot report you cannot pass limit or orderbys to the top level, they must be included for each pivot instead.
• Confirm these changes to not cause and regressions in non pivot reports.

QA Brief

• As there are no new user facing changes in this work, the QA time to should focus on confirming the changes to the reporting infrastructure does not cause any regression to Analytics reporting across the app.
• In order to test the new report selector, getPivotReport, you can call the selector in a component or console. There are some example pivot report options we will use in the Audience Tile refactor ticket #8726.
  eg. pivot report request:

		await googlesitekit.data.select( 'modules/analytics-4' ).getPivotReport( {
			startDate: '2024-04-18',
			endDate: '2024-05-15',
			dimensions: [ 'city', 'operatingSystem' ],
			dimensionFilters: {
				operatingSystem: [ 'Windows', 'Macintosh' ],
			},
			metrics: [ { name: 'totalUsers' } ],
			pivots: [
				{
					fieldNames: [ 'operatingSystem' ],
					limit: 3,
				},
				{
					fieldNames: [ 'city' ],
					limit: 3,
					orderby: [
						{
							metric: {
								metricName: 'totalUsers',
							},
							desc: true,
						},
					],
				},
			],
		});

Changelog entry

• Add support for pivot reports from Analytics to improve report request efficiency.

[benbowler]

As discussed here in 8136 there are a number of refactors required to the AudienceTiles and AudienceTile components which are best worked on here:

1. Once the pivot reports are implemented, this data restructuring code should be removed and only the filtered pivot report rows for this audience segment should be passed, without restructuring, to the AudienceTile component.

site-kit-wp/assets/js/modules/analytics-4/components/dashboard/AudienceSegmentation/AudienceTiles.js

Lines 211 to 271 in 5dcc66a

	// TODO: as part of #8484, this data manipulation should be removed and the relevant
	// pivot report rows should be pass directly to the AudienceTile component.
	const metricIndexBase = index * 2;
	const audienceName =
	audiences?.filter(
	( { name } ) => name === audienceResourceName
	)?.[ 0 ]?.displayName || '';
	const visitors =
	Number(
	rows[ metricIndexBase ]?.metricValues?.[ 0 ]?.value
	) || 0;
	const prevVisitors =
	Number(
	rows[ metricIndexBase + 1 ]?.metricValues?.[ 0 ]
	?.value
	) || 0;
	const visitsPerVisitors =
	Number(
	rows[ metricIndexBase ]?.metricValues?.[ 1 ]?.value
	) || 0;
	const prevVisitsPerVisitors =
	Number(
	rows[ metricIndexBase + 1 ]?.metricValues?.[ 1 ]
	?.value
	) || 0;
	const pagesPerVisit =
	Number(
	rows[ metricIndexBase ]?.metricValues?.[ 2 ]?.value
	) || 0;
	const prevPagesPerVisit =
	Number(
	rows[ metricIndexBase + 1 ]?.metricValues?.[ 2 ]
	?.value
	) || 0;
	const pageviews =
	Number(
	rows[ metricIndexBase ]?.metricValues?.[ 3 ]?.value
	) || 0;
	const prevPageviews =
	Number(
	rows[ metricIndexBase + 1 ]?.metricValues?.[ 3 ]
	?.value
	) || 0;
	const topCities = topCitiesReport?.[ index ];
	const topContent = topContentReport?.[ index ];
	const topContentTitles = {};
	topContentPageTitlesReport?.[ index ]?.rows?.forEach(
	( row ) => {
	topContentTitles[ row.dimensionValues[ 0 ].value ] =
	row.dimensionValues[ 1 ].value;
	}
	);

2. The AudienceTile component props and internals should be updated to expect the raw report rows instead of the current data structure.

site-kit-wp/assets/js/modules/analytics-4/components/dashboard/AudienceSegmentation/AudienceTile/index.js

Lines 50 to 63 in 5dcc66a

	// TODO: as part of #8484 the report props should be updated to expect
	// the full report rows for the current tile to reduce data manipulation
	// in AudienceTiles.
	export default function AudienceTile( {
	title,
	infoTooltip,
	visitors,
	visitsPerVisitor,
	pagesPerVisit,
	pageviews,
	percentageOfTotalPageViews,
	topCities,
	topContent,
	topContentTitles,

3. The AudienceTile stories can then be updated to use the data-mock function instead of the hard coded props:

site-kit-wp/assets/js/modules/analytics-4/components/dashboard/AudienceSegmentation/AudienceTile/index.js

Lines 50 to 63 in 5dcc66a

	// TODO: as part of #8484 the report props should be updated to expect
	// the full report rows for the current tile to reduce data manipulation
	// in AudienceTiles.
	export default function AudienceTile( {
	title,
	infoTooltip,
	visitors,
	visitsPerVisitor,
	pagesPerVisit,
	pageviews,
	percentageOfTotalPageViews,
	topCities,
	topContent,
	topContentTitles,

[benbowler]

For reference, here is an example pivot report and it's response based on my testing:

Report Options

	const reportOptions = {
		startDate: '2024-04-18',
		endDate: '2024-05-15',
		compareStartDate: '2024-03-21',
		compareEndDate: '2024-04-17',
		dimensions: [ { name: 'operatingSystem' } ],
		dimensionFilters: {
			operatingSystem: [ 'Windows', 'Macintosh' ],
		},
		metrics: [
			{ name: 'totalUsers' },
			{ name: 'sessionsPerUser' },
			{ name: 'screenPageViewsPerSession' },
			{ name: 'screenPageViews' },
		],
		pivots: [
			{
				fieldNames: [ 'operatingSystem' ],
				limit: 3,
			},
		],
	};

Report Response

{
  "kind": "analyticsData#runPivotReport",
  "pivotHeaders": [
    {
      "rowCount": 2,
      "pivotDimensionHeaders": [
        {
          "dimensionValues": [
            {
              "value": "Windows"
            }
          ]
        },
        {
          "dimensionValues": [
            {
              "value": "Macintosh"
            }
          ]
        }
      ]
    },
    {
      "rowCount": 2,
      "pivotDimensionHeaders": [
        {
          "dimensionValues": [
            {
              "value": "date_range_0"
            }
          ]
        },
        {
          "dimensionValues": [
            {
              "value": "date_range_1"
            }
          ]
        }
      ]
    }
  ],
  "dimensionHeaders": [
    {
      "name": "operatingSystem"
    },
    {
      "name": "dateRange"
    }
  ],
  "metricHeaders": [
    {
      "name": "totalUsers",
      "type": "TYPE_INTEGER"
    },
    {
      "name": "sessionsPerUser",
      "type": "TYPE_FLOAT"
    },
    {
      "name": "screenPageViewsPerSession",
      "type": "TYPE_FLOAT"
    },
    {
      "name": "screenPageViews",
      "type": "TYPE_INTEGER"
    }
  ],
  "rows": [
    {
      "dimensionValues": [
        {
          "value": "Windows"
        },
        {
          "value": "date_range_0"
        }
      ],
      "metricValues": [
        {
          "value": "102"
        },
        {
          "value": "1.0392156862745099"
        },
        {
          "value": "1.5"
        },
        {
          "value": "159"
        }
      ]
    },
    {
      "dimensionValues": [
        {
          "value": "Macintosh"
        },
        {
          "value": "date_range_1"
        }
      ],
      "metricValues": [
        {
          "value": "51"
        },
        {
          "value": "1.0588235294117647"
        },
        {
          "value": "1.6666666666666667"
        },
        {
          "value": "90"
        }
      ]
    },
    {
      "dimensionValues": [
        {
          "value": "Windows"
        },
        {
          "value": "date_range_1"
        }
      ],
      "metricValues": [
        {
          "value": "46"
        },
        {
          "value": "1.0434782608695652"
        },
        {
          "value": "1.75"
        },
        {
          "value": "84"
        }
      ]
    },
    {
      "dimensionValues": [
        {
          "value": "Macintosh"
        },
        {
          "value": "date_range_0"
        }
      ],
      "metricValues": [
        {
          "value": "41"
        },
        {
          "value": "1.0487804878048781"
        },
        {
          "value": "1.3953488372093024"
        },
        {
          "value": "60"
        }
      ]
    }
  ],
  "aggregates": [
    {
      "dimensionValues": [
        {
          "value": "RESERVED_TOTAL"
        },
        {
          "value": "date_range_0"
        }
      ],
      "metricValues": [
        {
          "value": "143"
        },
        {
          "value": "1.0419580419580419"
        },
        {
          "value": "1.4697986577181208"
        },
        {
          "value": "219"
        }
      ]
    },
    {
      "dimensionValues": [
        {
          "value": "RESERVED_MAX"
        },
        {
          "value": "date_range_0"
        }
      ],
      "metricValues": [
        {
          "value": "102"
        },
        {
          "value": "1.0487804878048781"
        },
        {
          "value": "1.5"
        },
        {
          "value": "159"
        }
      ]
    },
    {
      "dimensionValues": [
        {
          "value": "RESERVED_TOTAL"
        },
        {
          "value": "date_range_1"
        }
      ],
      "metricValues": [
        {
          "value": "97"
        },
        {
          "value": "1.0515463917525774"
        },
        {
          "value": "1.7058823529411764"
        },
        {
          "value": "174"
        }
      ]
    },
    {
      "dimensionValues": [
        {
          "value": "RESERVED_MAX"
        },
        {
          "value": "date_range_1"
        }
      ],
      "metricValues": [
        {
          "value": "51"
        },
        {
          "value": "1.0588235294117647"
        },
        {
          "value": "1.75"
        },
        {
          "value": "90"
        }
      ]
    },
    {
      "dimensionValues": [
        {
          "value": "RESERVED_MIN"
        },
        {
          "value": "date_range_1"
        }
      ],
      "metricValues": [
        {
          "value": "46"
        },
        {
          "value": "1.0434782608695652"
        },
        {
          "value": "1.6666666666666667"
        },
        {
          "value": "84"
        }
      ]
    },
    {
      "dimensionValues": [
        {
          "value": "RESERVED_MIN"
        },
        {
          "value": "date_range_0"
        }
      ],
      "metricValues": [
        {
          "value": "41"
        },
        {
          "value": "1.0392156862745099"
        },
        {
          "value": "1.3953488372093024"
        },
        {
          "value": "60"
        }
      ]
    }
  ],
  "metadata": {
    "currencyCode": "USD",
    "dataLossFromOtherRow": null,
    "emptyReason": null,
    "subjectToThresholding": null,
    "timeZone": "Etc/GMT"
  }
}

I often substitute audienceResourceName with operatingSystem as I don't have two audience with data on my Analytics properties currently but the dimensions are interchangeable.

---

I also have an Insomnia playground export with some example queries which I can share with anyone who picks this up.

[benbowler]

@techanvil I split this ticket into two, moving work specific to Audience Tiles to #8726 as there is a lot of work involved in both parts of this ticket.

[techanvil]

Thanks @benbowler, that sounds sensible!

Regarding the IB, it's a good first take. However with the runReport and runPivotReport GA4 endpoints being two distinct entities, with similar yet crucially different payloads, I would rather see Site Kit provide a similar separation for handling pivot reports, providing a new GET:pivot-report datapoint and runPivotReport() selector/resolver.

The commonalities between the two can be shared either via existing helpers or extracting new ones where appropriate.

This approach should be cleaner and easier to maintain going forward, and we won't have to maintain a mental model of the differences between the two GA4 endpoints to make sense of a single SK endpoint.

I realise the AC was a bit open to interpretation, so I've updated it to be more explicit. Please can you iterate on the IB, taking this into consideration?

[benbowler]

Thanks @techanvil, I did consider this split originally, however I thought it would lead to lots of duplicated code as the report structures are so similar, also our report infra already does not directly map to the GA Data API structure. That's not a reason not to improve things though.

I've updated the IB to have distinct pivot selector and REST endpoint and pivot-reports store. I've increased the estimate as there will be lots of additional changes and requirements for additional tests to complete this work.

[techanvil]

Thanks Ben! IB LGTM ✅

[tofumatt]

QA Brief run as part of the code review, and since it's very straightforward moving directly to Approval, since there isn't much to QA. 😄