One thing I like to do is to monitor how much traffic each page of mine gets. I think of my blog like a garden with many blossoming plants or fruit-bearing trees. I have to tend to each corner of the garden, and to each plant, depending on its independent needs.

In this post I want to show how to measure traffic per page. It's based on:

  • A Google Sheets workbook of all the posts in your blog.
  • A GA4 (Google Analytics 4) query that pulls in session data (I used to do this with Universal Analytics)
  • Some clever formulas to tie it all together.

You can do this report automatically in many ways. But they display the information in a less useful way. I couldn't get the automatic report to be easier than my manually structured one and to deliver all the same benefits. So I think this is still best done via Google Sheets and GA4 data.

Set Up Google Sheet and the GA4 Plugin

In a Google Sheet, collect all the URLs you want to analyse.

This is just a sheet with the URLs on the left. You include only what you want to analyse, in whatever order or grouping you want.

Setting up list of URLS for blog post

Once you have that, you can plug in the rest via automatic data and formulas.

You need Michele Pisani's GA4 Analytics Add-on to pull in data. Google hasn't updated their own spreadsheet plug in, and they've lost my interest with false promises of updates. (You can check out this thread if you're interested.)

I've chatted with Michele and he seems like a stand-up guy (note for non-Europeans, "Michele" is pronounced "Mikel-e" and is a man's name in Italy, equivalent to "Michael"), just a developer out to make something that works. He listens to feedback. And his plugin is free, as it should be! (If it becomes not free, I'll work out a way of doing this via API calls.)

Read more about Michele and his plug-in on his blog here.

While Michele's plug-in lets you set up sheets using his automation form, you need to do a couple of things manually.

This is how I've set up the query:

Google Analytics 4 query setup

The key parts are:

  • Start 90 days ago, end yesterday. This is one quarter of results.
  • Measuring sessions. You can change this to pageviews if you want.
  • The two dimensions are yearWeek, and pagePath.
  • The "order by" is important or your data will be in semi-random order, as it comes from Google. I use dimension|yearWeek__ASC

Based on that, run your query. This will create a page of results with the sheet name listed as "Report Name".

Analysis

There are three key formulas I use to do the analysis.

One of them is to auto-populate the week names across the top row.

I use this:

=transpose(sort(unique('Sessions by Page Weekly'!A16:A)))

This formula will create 12-13 column headings across the top row.

Auto-creating the header rows
Auto-creating the header rows (for each week)

Secondly, I use an array formula to extract the path from each URL.

=arrayformula(if(A2:A="","",right(A2:A, len(A2:A)-18)))

The 18 number is the number of characters before the first trailing slash (in this example, the number of characters in https://myblog.com). You'd modify this based on the length of your own URL.

Using an array formula to trim the path.

Finally, I use sumifs to calculate the traffic per page.

=sumifs('Sessions by Page Weekly'!$C$16:$C, 'Sessions by Page Weekly'!$B$16:$B, $B2, 'Sessions by Page Weekly'!$A$16:$A, C$1)
Using a sumifs formula to count traffic by page for a website
Using a formula to count traffic by page

Copy this down and across.

Every time you want to add a new post, you need to copy this row again.

If you want to see this workbook, here it is, here. All the data is dummy data.

Comparison — Why Not Automate?

It's possible to do versions of this in Looker Studio, and not using any plug-ins for Google Sheets.

I had set up tables in Looker Studio in the past, analysing information from Google Sheets and Google Analytics 4.

But I found it fell short significantly, for a few reasons.

Firstly, I use the Google Sheet to archive data. I only request the last three months of data (otherwise the requests are huge and the analytics can be painful and slow). And for old data, I just copy and paste-as-values to keep it as hard-coded data, so I can look at long-term trends whenever I want.

Most recently, this was useful as I had to update all my formulas and tools from Universal Analytics to Google Analytics 4.

Secondly, I like to move around or omit some unimportant pages sometimes. Doing this meant I had to be fiddly with formulas and filters. It was cumbersome.

In fact, I wanted to also pull in "date" into my table, and then to sort by it (to have oldest post to newest). Just couldn't do it. It would just keep giving me "System error".

Finally, I find Looker Studio generally unreliable. Sometimes the table will just stop working. It says "unable to fetch data". Every time, I have to poke around and search online for things until it works. It feels like coding, whereas Google Sheets feels like a functional tool.

Still, for some people, doing it in Looker Studio is a more palatable option.

Wrap Up

If you find this post useful, feel free to send me a note of thanks!