by Chris Williams

The Google Analytics (GA) add-on for Google Sheets is an accessible introduction to the power of the GA API (Application Programming Interface), letting non-developers easily collect, manipulate, and share the data.

Here I’m going to show you how to load and analyze your website’s 1,000 most popular URLs. Follow along with your own analytics account, or use the demo sheet provided below that I created with data from Google’s Demo Account. The link appears after the third step, running the report.

Note: While my demo sheet uses data from the demo account, you can not access the API with the demo account. From the docs: “You cannot use the demo account with the Analytics Reporting API. Attempts to do so result in a permissions error: (403) insufficientPermissions.” :sadface. So I downloaded CSV files from GA, and manually edited the demo sheet to reflect what it would look like if it were accessing the API.

First step is to link your GA account to Sheets:

After it’s linked, return to the “Add-ons” menu to create a new report. Name it ‘DateRange1Pageviews.’ (You can name the report something else, but then you will need to edit the functions I provide.)

Set your GA details in section 2 with the proper Account, Property, and View (Profile) settings.

For section 3, set Metrics to ‘Pageviews’ and Dimensions to ‘Page’:

Click “Create Report.”

Here you’ll need to make some additions to the generated settings. First, set sort to ‘-ga:pageviews’ so popular pages are a the top of the list. Then set max results to 1,000 or lower. You can go as high as 10,000, but it will significantly show things.

Finally, select a date range. I’m going to set this one to March and the second to April. Clear the value of ‘Last N Days’ and set the start date to ‘2017-03-01’ and the end date to ‘2017-03-31.’ (Look at the screenshot below if you’re stuck.)

Now copy the entire column, and paste it into the third column. Change the name to ‘DateRange2Pageviews’ and the start/end dates to April (2017-04-01 through 2017-04-30).

Now the fun starts! Run the report:

You’ll have two new sheets named after the two reports. (Here’s a link to the demo sheet. You can’t edit it, so make a copy before continuing. (File -> Make a copy.))

Create five new sheets: Pageviews, Rising, Falling, New, Exiting. (You can rename “Sheet1” if it’s there.) Your spreadsheet should now look like this:

Make a note of how many values there are for the two date ranges. Scroll to the bottom of ‘DateRange1Pageviews’ and record the last row with data. This will be referred to as ‘<LASTROW1>’ in the provided code. Now do the same for the second report, which will be ‘<LASTROW2>’ in the code.

For each of those 5 sheets, enter in 5 column names: Page, Range 1 Views, Range 2 Views, Difference, Percent. Lock the row. (View -> Freeze -> 1 row)

Go back to the  “Pageviews” sheet. This will be the most complicated sheet because each column requires a different formula.

The first thing we need to do is create a column of all the unique pages. In the “Page” column, set A2 to the following, but replacing <LASTROW1> and <LASTROW2> with your own values:

=UNIQUE({DateRange1Pageviews!A16:A<LASTROW1>;DateRange2Pageviews!A16:A<LASTROW2>})

Note: If you have 1000 results returned, then the last row will be 1015. In the demo sheet, you will enter:

=UNIQUE({DateRange1Pageviews!A16:A839;DateRange2Pageviews!A16:A1015})

In the second column, “Range 1 Views,” you will be setting the value to the Pageview column in the “DateRange1Pageviews” sheet if a value exists, or NA if it does not. Do that by placing the following code into the B2 cell, making sure to use your own value for “<LASTROW1>”:

=IF(ISNA(MATCH($A2,DateRange1Pageviews!$A$1:$A$<LASTROW1>, 0)), NA(), INDIRECT("DateRange1Pageviews!"&CONCATENATE("B", MATCH($A2,DateRange1Pageviews!$A$1:$A$<LASTROW1>, 0))))

Here’s a plain english explanation: If there’s not a MATCH then set the cell value to NA(), and if there is a match then set the cell value to the value in the Pageview (B) column. The INDIRECT and CONCATENATE functions are the magic behind the formula!

Paste almost the same formula into C2, but change the name of the sheet and the value for “<LASTROW2>.”

=IF(ISNA(MATCH($A2,DateRange2Pageviews!$A$1:$A$<LASTROW2>, 0)), NA(), INDIRECT("DateRange2Pageviews!"&CONCATENATE("B", MATCH($A2,DateRange2Pageviews!$A$1:$A$<LASTROW2>, 0))))

The Difference column is the simple formula:

=$C2-$B2

and the Percent column is

=ROUND(100*$D2/$B2, 2)

Now highlight the cells B2 through E2 and drag the little blue box in the lower right all the way down to the last row. Record the number of rows like you did before (the provided demo spreadsheet goes to row 1246):

You finished the hardest one! This is basically the halfway mark.

Now switch to the “Rising” sheet. Only one formula is needed. Place the next formula into cell A2 (below the header row you created earlier). Replace “<LASTROWPV>” with the number of rows you recorded in the Pageviews sheet:

=FILTER(Pageviews!A2:E<LASTROWPV>, ISNUMBER(Pageviews!B2:B<LASTROWPV>), ISNUMBER(Pageviews!C2:C<LASTROWPV>), Pageviews!D2:D<LASTROWPV> > 0)

In plain english: The FILTER function lets you copy a block of rows and columns, which is the first parameter. Here we want to copy from A2 (first element below the header) through the last value in column E (the Percent column). Filter then takes one or more filtering parameters, and since this is the Rising sheet, the Pageviews for both date ranges need to be numbers (rather than NA) and the difference needs to be greater than 0.

Risings done! Switch to “Falling.” Copy this formula into A2, once again replacing “<LASTROWPV>” with the number of rows you recorded in the Pageviews sheet. This is just like the Rising sheet, except we want the difference to be less than 0.

=FILTER(Pageviews!A2:D<LASTROWPV>, ISNUMBER(Pageviews!B2:B<LASTROWPV>), ISNUMBER(Pageviews!C2:C<LASTROWPV>), Pageviews!D2:D<LASTROWPV> < 0)

Switch to the “New” sheet, and paste this formula into A2:

=FILTER(Pageviews!A2:E<LASTROWPV>, ISNA(Pageviews!B2:B<LASTROWPV>), ISNUMBER(Pageviews!C2:C<LASTROWPV>))

In plain english: New pages are ones where the pageviews for the first date range are NA.

Finally, switch to the “Exiting” sheet, and paste this formula into A2:

=FILTER(Pageviews!A2:E<LASTROWPV>, ISNUMBER(Pageviews!B2:B<LASTROWPV>), ISNA(Pageviews!C2:C<LASTROWPV>))

In plain english: Exiting pages are ones where the pageviews for the second date range are NA.

That’s it!

With just a few semi-complicated formulas you can quickly view how your pages are performing in Google Analytics.

Here’s the final demo spreadsheet.

Next steps: Alter this for your site! Customize it by changing values in the “Report Configuration” sheet. Care about sessions instead of pageviews? Replace ‘ga:pageviews’ with ‘ga:sessions’. Here’s the full list of metrics and dimensions available from the API. Learn what else you can do from Google’s add-on documentation.


 

Posted
AuthorRyan Harrington