Get top viewed page urls from Google Analytics


#1

Hi,
I would like to display the most views pages of my site, using Google Analytics as data source. I know that GA provides some very powerful api’s, but they’re quite complicated. I wonder if there’s a plugin or a wrapper which is already doing the dirty job.

Thank you
Francesco


#2

Not that I know of, but maybe this plugin can serve as a starting point: https://github.com/gearsdigital/kirby-analytics-dashboard. Guess you might just as well get the most viewed pages instead of visitors from the API.


#3

theres a plugin for google sheets for GA that generates a spread sheet. you could fetch that data as an excel sheet and use @texnixe spreadsheet plugin to display it on your site.

https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on


#4

Nice hint. I’m currently playing with the Google Sheet extension of GA and it works great!


#5

I saw this plugin from @texnixe

but it seems its goal is to display a portion of the sheet inside a page. While this is very interesting, I’d like to parse the sheet and get the cell values.


#6

Well, that one can generate a html table from a spreadsheet.

@texnixe other plugin might be more useful, because it turn spread sheet in to a structure field. From there you can do what ever kirby flavoured stuff u want.


#7

The tag code in the spreadsheet plugin does in fact exactly that: parse data from a spreadsheet. If you install the plugin, you can basically copy and paste the data from the tag and use it in your template (with some modifications, of course).

So you can create a new SpreadsheetReader object like this:

$reader = new SpreadsheetReader('path_to_excel_file');

and take it from there.


#8

That would be an unnecessary detour, because the data could be parsed directly without saving it to Kirby pages first.


#9

Sounds good. But - what if the excel file is actually a Google Sheet file and it’s located somewhere else?

I’m trying to read this file, for instance:


#10

hmm well it used to be that you curl the file down, which could have been tied into a cron job, but seems they turned that option off.

A quick look at the docs hints you can fairly easily use the api to export and download the Excel file. I have never done this, but if its anything like using the calendar API, you will probably need to add a library to your project and set an Oauth token.

You could try googling around, i bet someone out there has written a perl/python/bash script to grab a file from google drive. If the file is publicly accessable, I can’t see why you cant just curl it, but i’ve seen reports of google restricting it.

A cheeky way might be to something similar to the dropbox sync, but rather install the google drive app on your sever if your able to (you would need a VPS for this). You could then symlink the file into your content folder.


#11

Isn’t it possible to access a .csv version of that file?

Well, maybe this is helpful as well: https://www.fillup.io/post/read-and-write-google-sheets-from-php/


#12

I just stumbled on this CLI tool that can save a googlesheet to a JSON file on disk. Might suit your needs.


#13

Alternatively, I just ran a little test. If you make the sheet both public and publish it (File > Publish to the web), you can indeed use curl to grab it. Just use the url google sheets gives you when you publish. Note the query string on the end - if you need CSV or other format, tweak the query string.

curl https://docs.google.com/spreadsheets/d/e/YOURSHEETID/pub?output=xlsx > sheet.xlsx

If you need to keep the the spreadsheet private, I would go with that cli tool i linked yesterday, but that will only give you JSON as a result, or use the docs API to fetch it.


#14

@texnixe and @jimbobrjames: thanks both for your answers. You saved me a lot of time. I ended by downloading the Google Sheets Document as csv and then using some php to convert csv into arrays.