Analyse Traffic per Page on Blogs / Websites

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.

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, or Google’s add-on. Michele’s came out much earlier than Google’s own tool, and I like his plug-in, so I’ll use it for as long as it works. (Read more about Michele and his plug-in on his blog here.)

While Michele’s GA4 analytics 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:

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.

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.

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)

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!