In this tutorial, we will cover what website performance analytics is, why it’s important, and a step-by-step example of how to build website performance analysis in SQL.
Webpage performance analytics allows the marketing team to measure the impact of each page on the company’s website. This analysis uses information about which pages are being seen, how long a user is spending on the page, if they are linked directly to that page or if they are navigating to it from the rest of the website.
In addition, custom metrics can be created to calculate the value of each page to the company's marketing and sales goals. Using these metrics, each page can be ranked to identify the best and worst performing pages on the site.
A company’s website is one of the key aspects of their entire marketing strategy. Most prospects will visit the site at some point during their buying journey. By improving poor-performing pages and using high-performing pages to suggest additional content, marketing can efficiently improve website conversion rates.
Further, understanding how people engage with the site, what pages they dwell on, what pages they first land on during their session, and what pages cause them to end their session, can help understand if the marketing message resonates, which parts are not, and help identify how to improve that message. Beyond just marketing, this can help product management understand what aspect of the product or brand messaging is resonating with prospective customers.
Individual page data is needed for this analysis. One of the most common tools used for this is Google Analytics and should contain:
With this data, webpage performance can be analyzed.
Since all the data to evaluate the performance of a webpage comes from Google Analytics, we can start by simply aggregating the data over each webpage.
Often, we are only interested in the recent performance of the website. This could be after a change was made to the site or simply to understand the performance in the most recent month or quarter. We can filter to the most recent day by adding a WHERE clause to the prior SELECT statement.
Using the above SELECT statement as a common table expression, we can calculate any custom metrics to rank the page based on these values as follows. In this case, we will pick a simple metric as:
Calculating this (or any other) metric using the CTE is then straightforward.
Using this, the top N best performing pages can be generated by ordering by customer_webpage_metric and limiting the result to the top N.
The bottom N performing webpages can be generated by dropping the DESC in the ORDER BY line second from the bottom.
A company’s webpage is a key aspect of their marketing strategy and important to the sales process. Understanding which pages are performing well and underperforming can help the marketing team hone the website to drive prospects and customers.