Auditing your website loading performance with PageSpeed

Learn how to build a report of your website's loading performance with PageSpeed, all in a spreadsheet.

Intro

Back in 2006, Amazon found that 1 additional millisecond in their pages' load time could cost them $1 million in revenue. This has quickly become one of the most referenced data points when claiming the importance of page load performance.

In the last few years, Google kept increasing the relative weight of website performance in ranking domains, making the engineering and data investment in light, flexible and responsive code even more important.

In this short guide, we’ll walk you through everything you need to generate a detailed audit report of your website load performance with Google PageSpeed, all in a spreadsheet.

Here’s a sneak peek of what you’ll get:

page speed detailed

What is Google PageSpeed

Google PageSpeed executes a real-live test browsing session to measure the main loading performance indicators of any webpage. The overall score gives you a glimpse of how fast a webpage can load its content.

The tracked metrics include:

  • First Contentful Paint (FCP), the time the page takes to render any part on the screen
  • First Input Delay (FID), the time the page takes to process the event in response to the first user interaction, e.g. click on a link or tap on a button
  • Large Contentful Paint (LCP), the time the page takes to render the biggest object - text block or image - on the page, visible within the viewport. This is one of the most user-centric metrics for measuring the perceived load speed
  • Cumulative Layout Shift (CLS) measures the largest burst of layout shift scores for every unexpected layout shift that occurs during the entire lifespan of a page. A layout shift occurs any time a visible element changes its position from one rendered frame to the next
  • Interaction to Next Paint (INP) measures the overall responsiveness of a webpage, by computing all click, tap, and keyboard interactions
  • Time to First Byte (TTFB), the time the page takes to process a request for a resource and deliver the first byte. It helps identify how responsive your connection configuration and web server are.

Google classifies the performance into three buckets: Good, Needs Improvement, or Poor, and sets the following thresholds:

MetricGoodNeeds improvementPoor
FCP[0, 1800ms](1800ms, 3000ms]over 3000ms
FID[0, 100ms](100ms, 300ms]over 300ms
LCP[0, 2500ms](2500ms, 4000ms]over 4000ms
CLS[0, 0.1](0.1, 0.25]over 0.25
INP[0, 200ms](200ms, 500ms]over 500ms
TTFB[0, 800ms](800ms, 1800ms]over 1800ms

Let's go!

To understand how we’re going to use it, let’s go step by step. Use our template at the following link Google Pagespeed detailed audit and save it in one of your workspace’s folders.

This template allows you to run a detailed audit of any website loading performance.

Let's reverse-engineer it together.

All you need to do is to enter the full URL of the website and pick the mode you want to check (desktop or mobile) respectively in cells B1 and B2. These two cells are the argument of Rows' PAGESPEED_GOOGLE() function, enclosed in cell B4, that executes the audit.

1                      =PAGESPEED_GOOGLE(B1,B2)

Please wait up to 25 seconds for the analysis to be completed.

ezgif-4-75ff762fc6

Once the audit is done, cell B4 will turn into a data cell. In Rows, a data cell stores more than a string or a number, the typical content of standard spreadsheets' cells. In this specific case, it encloses a collection of data points about your website loading performance to be expanded and made readable in a table. To make the result table simpler and easier to read, we chose to handpick only some of the available data points. To get them, you need to click on the ... icon in cell B4 and hit View data.

Screenshot 2022-08-23 at 18.26.36

A panel will open on the right-hand side showing a structured document (in JSON format). Next to each data point, there is a COPY button. Hit it and paste in any cell: you will obtain a formula similar to the following, that uses our PARSE() function to read and extract specific data points from any JSON document. The first argument is the data cell H2, and the second includes the path to follow to retrieve that specific element.

1 =PARSE('Audit setup'!B4,"['loadingExperience'].['metrics'].['CUMULATIVE_LAYOUT_SHIFT_SCORE'].['category']")

The table below, 'PageSpeed results' contains the parsed output of the audit. All PARSE() are triggered by AUTOFILL() functions in the table header.

Rows AUTOFILL() has been designed to fill the column automatically, based on the data in previous columns. It should always be placed in the column header and has to include the following mandatory arguments: the column title in quotation marks and the formula to be filled in the column itself - in this case, the PARSE() for the PageSpeed data cell B4.

The UPDATE function in cell N2 of the results table helps you build a log to track the performance evolution over time. Its mandatory arguments include:

  • the data you want to upsert, including the header. Here is the output of the PageSpeed audit: range A1:M2
  • the keys, i.e. the values Rows will check against before allowing for the update. Here, the date and the URL.
  • the destination table's header. Here the log table (range 'Results log'!A1:M1).

With UPDATE() whenever you run the test again, Rows will check if you already run a test for that URL on the same day, and won't update the Log table. Intraday audits to the same URL will not be stored.

Schedule automation

Instead of coming back to the spreadsheet and triggering the function manually, you can also use SCHEDULE() to execute the audit based on your target frequency. Add SCHEDULE() function to cell B3 of the Setup table: use B4 as the first argument and pick a refresh schedule as a second, i.e. "every monday 08:00", or "every 1 of month". Rows will trigger the audit for you at the defined schedule and populate the log. Please beware that, with the current UPDATE() configuration, the frequency schedule should not be below daily.

1                  =SCHEDULE(B4, "every 1 of month")

Embed into documents

Finally, if you are using Notion or any other internal tools that support iframes to build a report, you can embed any table or chart of this spreadsheet in your document. Just click on the ... icon in the top right corner of the table and pick Embed. A modal will open, as follows:

Screenshot 2022-09-07 at 10.20.26

Before embedding any table or chart of a spreadsheet, you need to grant access to it. Follow the yellow message, and toggle on the Share privately option in the Share tab, as follows:

ezgif-4-ff406c1d5a

Once the spreadsheet's sharing option has been enabled, you just need to hit Copy URL from the Embed tab and paste the link into your document.

Screenshot 2022-09-07 at 10.20.26

This is how Rows embed looks on Notion:

ezgif-2-64c35aa649

You might also like