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. 

What is webpage performance analytics?

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.

Why webpage performance analytics is important?

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.

Data needed for webpage performance analytics

Individual page data is needed for this analysis. One of the most common tools used for this is Google Analytics and should contain:

  • Page title
  • Date
  • Entrances - number of times this was the first page visited in a session
  • Time on page
  • Bounce rate - percentage of times this was the only page visited in a session
  • Exit rate - percentage of times this was the last page visited in a session

With this data, webpage performance can be analyzed.

How to create webpage performance in SQL?

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.


SELECT
  page_title,
  AVG(avg_time_on_page) AS avg_time_on_page,
  AVG(bounce_rate) AS average_bounce_rate,
  MEDIAN(bounce_rate) AS median_bounce_rate,
  AVG(entrances) AS average_entrances,
  SUM(entrances) AS total_entrances,
  AVG(exit_rate) AS average_exit_rate,
  MEDIAN(exit_rate) AS median_exit_rate,
  AVG(pageviews) AS average_pageviews,
  SUM(pageviews) AS total_pageviews,
  AVG(unique_pageviews) AS average_unique_pageviews,
  SUM(unique_pageviews) AS total_unique_pageviews,
  AVG(users) AS average_users,
  SUM(users) AS total_users
FROM google_page_analytics
GROUP BY page_title

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.


SELECT
  page_title,
  AVG(avg_time_on_page) AS avg_time_on_page,
  AVG(bounce_rate) AS average_bounce_rate,
  MEDIAN(bounce_rate) AS median_bounce_rate,
  AVG(entrances) AS average_entrances,
  SUM(entrances) AS total_entrances,
  AVG(exit_rate) AS average_exit_rate,
  MEDIAN(exit_rate) AS median_exit_rate,
  AVG(pageviews) AS average_pageviews,
  SUM(pageviews) AS total_pageviews,
  AVG(unique_pageviews) AS average_unique_pageviews,
  SUM(unique_pageviews) AS total_unique_pageviews,
  AVG(users) AS average_users,
  SUM(users) AS total_users
FROM google_page_analytics
WHERE date >= ‘’
GROUP BY page_title

Custom metric

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:


(average_users * average_time_on_page) - median_bounce_rate AS customer_webpage_metric

Calculating this (or any other) metric using the CTE is then straightforward.


WITH agg_metrics AS (SELECT
    page_title,
    AVG(avg_time_on_page) AS avg_time_on_page,
    AVG(bounce_rate) AS average_bounce_rate,
    MEDIAN(bounce_rate) AS median_bounce_rate,
    AVG(entrances) AS average_entrances,
    SUM(entrances) AS total_entrances,
    AVG(exit_rate) AS average_exit_rate,
    MEDIAN(exit_rate) AS median_exit_rate,
    AVG(pageviews) AS average_pageviews,
    SUM(pageviews) AS total_pageviews,
    AVG(unique_pageviews) AS average_unique_pageviews,
    SUM(unique_pageviews) AS total_unique_pageviews,
    AVG(users) AS average_users,
    SUM(users) AS total_users
  FROM google_page_analytic
  GROUP BY page_title)
SELECT *,
  (average_users * average_time_on_page) - median_bounce_rate AS custom_webpage_metric
FROM agg_metrics

Best/Worst Performing Pages

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.


WITH agg_metrics AS (SELECT
    page_title,
    AVG(avg_time_on_page) AS avg_time_on_page,
    AVG(bounce_rate) AS average_bounce_rate,
    MEDIAN(bounce_rate) AS median_bounce_rate,
    AVG(entrances) AS average_entrances,
    SUM(entrances) AS total_entrances,
    AVG(exit_rate) AS average_exit_rate,
    MEDIAN(exit_rate) AS median_exit_rate,
    AVG(pageviews) AS average_pageviews,
    SUM(pageviews) AS total_pageviews,
    AVG(unique_pageviews) AS average_unique_pageviews,
    SUM(unique_pageviews) AS total_unique_pageviews,
    AVG(users) AS average_users,
    SUM(users) AS total_users
  FROM google_page_analytic
  GROUP BY page_title),
cust_metric AS (SELECT *,
    (average_users * average_time_on_page) - median_bounce_rate AS custom_webpage_metric
  FROM agg_metrics)
SELECT *
FROM cust_metric
ORDER BY custom_webpage_metric DESC
LIMIT 

The bottom N performing webpages can be generated by dropping the DESC in the ORDER BY line second from the bottom.

Conclusion

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.

No-code/low-code data prep and visualization