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.

I come with some thoughts, you can only use a max of date ranges per query, So you can´t do this on this way. So the option is to play with the row and columns. What i should do, my dimension will be page and date, metrics pageview (limit 9999), order by pageview. After this with a function that read the json up to bottom, moving the data to a new final array (or json). Why in this way? Because it's not possible to do this only with query. — Kemen Paulos Plaza, Mar 29 '17 at 09:10

sdhaus · Accepted Answer · 2017-03-28T19:52:25.797

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.

Thanks for your answer, but the first query returns only three items (since pagesize is 3). But I wanted to query for the top 3 items per day, resulting in 90 rows for a month. I need to avoid paging. Sorry for the confusing description of my problem. Also I provided`ga:pagePath` as a mere example, I want to query for different compatible metrics sorted by another metric per each single day (with a limit e.g. 3) separately in a single query (if possible). — Nick Russler, Mar 28 '17 at 10:45
I have provided an update. How are you trying to implement this? — sdhaus, Mar 28 '17 at 19:52
I will either query for each day or every nth day and interpolate. Thank you for your time and patience! I will leave this question open until the bounty ends, and if no one else can provide a solution accept your answer. — Nick Russler, Mar 29 '17 at 09:42
Its a bit of a hack but you could do it in one R command by doing the same code but with anti_sample = TRUE and anti_sample_batches = 1, which will force it to so one call per day with the required order. But its not one v4 API call. — MarkeD, May 24 '17 at 19:29

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

1 Answers1