Get top google analytics items of a dimension sorted by a metric of the last X days

Question

E.g. I want to get the three most visited pages of the last 30 days sorted by the number of sessions.

So for each single day I want the three top ga:pagePath items sorted for each day by ga:sessions. Is this possible in a single query?

This query should return 90 items, 3 ga:pagePath items per day.

As another example is it possible to get the three fastest loading (ga:pageLoadTime) pages instead of most visited for each day? This requires not ASC but DESC sorting in regard to the sorting metric.

I can use the v4 API if necessary.

Answer 1

1) So for each single day I want the three top ga:pagePath items sorted for each day by ga:sessions. Is this possible in a single query?

Yes this is possible. This can be done through unique pageviews. Google states: Unique Pageviews is the number of sessions during which the specified page was viewed at least once. A unique pageview is counted for each page URL + page Title combination.

The obvious caveat is the URL + page Title combination, but that might work in your favor depending on the application.

In Google Analytics:

Go to Behavior > Site Content > All Pages. Sort by Unique Pageviews.

In GAv4 API

{
  "reportRequests": [
    {
      "viewId": "VIEW_ID",
      "dateRanges": [
        {
          "startDate": "30daysAgo",
          "endDate": "yesterday"
        }
      ],
      "metrics": [
        {
          "expression": "ga:uniquePageViews"
        }
      ],
      "dimensions": [
        {
          "name": "ga:pagePath"
        }
      ],
      "pageSize": 3,
      "orderBys": [
        {
          "fieldName": "ga:uniquePageViews",
          "sortOrder": "DESCENDING"
        }
      ]
    }
  ]
}

2) Also is it possible to get the three fastest loading (ga:pageLoadTime) pages instead of most visited for each day? This requires not ASC but DESC sorting in regard to the sorting metric.

Yes, this is possible too. However you probably want to use ga:avgPageLoadTime as ga:pageLoadTime is the total time (from all pageviews).

In Google Analytics:

Go to Behavior > Site Speed > Page Timings. Change the numeric column to be Avg. Page Load Time (sec) and sort by this column.

In GAv4 API

{
  "reportRequests": [
    {
      "viewId": "VIEWID",
      "dateRanges": [
        {
          "startDate": "30daysAgo",
          "endDate": "yesterday"
        }
      ],
      "metrics": [
        {
          "expression": "ga:uniquePageViews"
        }
      ],
      "dimensions": [
        {
          "name": "ga:pagePath"
        }
      ],
      "pageSize": 3,
      "orderBys": [
        {
          "fieldName": "ga:avgPageLoadTime",
          "sortOrder": "ASCENDING"
        }
      ]
    }
  ]
}

However, this report may not be too helpful as it will primarily pick up outliers, rather then pages of interest. I would combine this call with another call to retrieve, 1) the top n pages by unique pageviews, and 2) use this as a filter for the page load time call.

For example (in R, using googleAnalyticsR):

1)
#retrieve the top 100 pages (ranked via unique pageviews)
test <- google_analytics_4(VIEW_ID, 
  date_range = c("30daysAgo", "yesterday"), 
  metrics = "uniquePageviews",
  dimensions = "pagePath", 
  max=100, #this is the top n pages
  order = order_type("uniquePageviews", sort_order=c("DESCENDING"),orderType = c("VALUE")))

2)
#call the top 3 pages via avgPageLoadTime
test2 <- google_analytics_4(VIEW_ID, 
  date_range = c("30daysAgo", "yesterday"), 
  metrics = "avgPageLoadTime",   
#But only for the top 100 pages (ranked via unique pageviews).
#filterExpression = uniquePageViews is greater then the unique pageviews 
#of the 100th ranked page.
  filtersExpression=paste("ga:uniquePageViews>",tail(test$uniquePageviews, 1), sep=""),
  dimensions = "pagePath", 
  max=3, 
  order = order_type("avgPageLoadTime", sort_order=c("ASCENDING"),orderType = c("VALUE")))

Update:

What you are trying to achieve is not possible in a single query.

The problem is that the order of the results is reactive to the day which you are trying to query.

A pivot table will not work, even though you can pivot page by day and have 30 columns, it will show you the daily traffic for the top three pages, rather then the top three pages per day.

To get the results you are looking for you would need to run a loop for the last x day's, calling the top 3 results for the metric of interest.