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.
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:
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:
|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|
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.
Please wait up to 25 seconds for the analysis to be completed.
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.
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.
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.
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
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.
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.
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")
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:
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:
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.
This is how Rows embed looks on Notion:
Obsessed with loading performances and SEO? Check out our guides:
- Tracking your competitors’ website loading performance with PageSpeed
- Monitoring your website search performance with Search Console
- Tracking keywords' performance with Search Console
- Planning link-building activity
- Tracking on-page SEO elements
Discover all marketers' guides.