Internal Link Clicks Report -

Would you like to know which internal links are popular with your visitors? Are you pushing them in the right direction? Or are they going out of their way to find a valuable link? Are your articles pushing people to lead pages? If so, this report is for you.

This is a Data Studio report that gets its data from GA4 via a materialised BigQuery table. This article explains how to set it up for your own account.

But first, another reason, and the inspiration for building this report…

Speculation Rules (Predictive Loading)

Speculation Rules is an experimental browser feature that lets you prefetch (download the HTML) or prerender (fully load) internal links on your pages. This can greatly speed up the user’s experience as they navigate your website.

You define speculation rules to determine whether and when certain links are prefetched or prerendered.

However, you want to do this carefully, as it increases how much a user downloads, especially if you are prerendering. So you want to define rules that only prefetch or prerender when the probability of a click is high.

This is where the Internal Link Clicks Report comes in handy. It tells you the probability that a link will be clicked, so you can speculate, knowing there is a high probability that the speculation will come to good use.

Users of our Page Lightning app can use this information to create custom speculations targeting frequently clicked links. Some common examples:

Links in a home page banner – You could specify specific links or use a CSS selector to specify any links in the banner.
Specific pages that have a prominent and frequently clicked link. Maybe a category with one or two products.
A frequently clicked menu link on the home page.
A next page link once the user has scrolled to the bottom of the list, and it comes into view.
A prominent link in an article that directs readers to the product it promotes.

How to create your own report

For our Page Lightning customers, contact us, and we can help you set up the reports.

Have a GA4 property

The first requirement is to have an active GA4 property.

A bonus is if you can add a ‘page_type’ parameter to your page_view events. This enables more filtering in the report. If you already use ‘content_group’ or another parameter, you can later adjust the SQL query to pull that in.

Link GA4 to BigQuery

We need to send the GA4 data to BigQuery so we can generate the report data.

Google provides detailed instructions on how to do that.

Generate the report data in BigQuery

Run the following SQL query in BigQuery, after making the following changes:

Change the two occurrences of ${ProjectID}.${DatasetID} to your GA4 BigQuery Project and DataSet ID.
Optionally set force_site_host to your host name if you want to restrict the report.
Optionally, change the page_type to the parameter name you use to define a page type.

DECLARE force_site_host STRING DEFAULT '';

CREATE OR REPLACE TABLE `${ProjectID}.${DatasetID}.internal_link_clicks` AS

WITH base AS (
  SELECT
    device.category AS device,
    (SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING)) FROM UNNEST(event_params) WHERE key = 'page_type') AS page_type,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'  LIMIT 1) AS page_location,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer'  LIMIT 1) AS page_referrer
  FROM `${ProjectID}.${DatasetID}.events_*`
  WHERE event_name = 'page_view'
  AND (_table_suffix BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AND FORMAT_DATE('%Y%m%d',CURRENT_DATE()))
),
norm AS (
  SELECT
    COALESCE(NULLIF(force_site_host,''), REGEXP_EXTRACT(page_location, r'(?i)://([^/]+)')) AS site_host,
    CASE
      WHEN page_referrer IS NULL OR page_referrer = '' THEN NULL
      WHEN REGEXP_CONTAINS(page_referrer, r'(?i)://') THEN
        COALESCE(REGEXP_EXTRACT(page_referrer, r'(?i)://([^/]+)'), '') 
      ELSE '' 
    END AS ref_host,
    CASE
      WHEN page_referrer IS NULL OR page_referrer = '' THEN NULL
      WHEN REGEXP_CONTAINS(page_referrer, r'(?i)://') THEN
        COALESCE(REGEXP_REPLACE(REGEXP_EXTRACT(page_referrer, r'(?i)://[^/]+(/[^?#]*)'), r'//+', '/'), '/')
      ELSE REGEXP_REPLACE(CONCAT('/', REGEXP_REPLACE(page_referrer, r'^[./]*', '')), r'//+', '/')
    END AS source_path,
    COALESCE(REGEXP_EXTRACT(page_location, r'(?i)://([^/]+)'), '') AS dest_host,
    COALESCE(REGEXP_REPLACE(REGEXP_EXTRACT(page_location, r'(?i)://[^/]+(/[^?#]*)'), r'//+', '/'), '/') AS dest_path,
    page_type
  FROM base
),
pageviews AS (
  SELECT
    COALESCE(NULLIF(force_site_host,''), REGEXP_EXTRACT(page_location, r'(?i)://([^/]+)')) AS site_host,
    COALESCE(
      REGEXP_REPLACE(REGEXP_EXTRACT(page_location, r'(?i)://[^/]+(/[^?#]*)'), r'//+', '/'),
      '/'
    ) AS source_path,
    device,
    COUNT(*) AS pageviews,
    ANY_VALUE(page_type) AS page_type
  FROM base
  GROUP BY 1,2,3
),
internal_transitions AS (
  SELECT
    n.site_host,
    n.source_path,
    n.dest_path,
    COUNT(*) AS transitions,
    ANY_VALUE(n.page_type) AS page_type
  FROM norm n
  WHERE n.source_path IS NOT NULL
    AND (
      LOWER(n.ref_host) = LOWER(n.site_host)
    )
  GROUP BY 1,2,3
),
sum_from_source AS (
  SELECT site_host, source_path, SUM(transitions) AS total_from_source
  FROM internal_transitions
  GROUP BY 1,2
)
SELECT
  it.site_host,
  pv.device,
  it.source_path,
  pv.page_type AS source_page_type,
  it.dest_path,
  it.page_type AS dest_page_type,
  pv.pageviews,
  it.transitions,
  s.total_from_source,
FROM internal_transitions it
LEFT JOIN pageviews pv USING (site_host, source_path)
LEFT JOIN sum_from_source s USING (site_host, source_path)
WHERE it.source_path <> it.dest_path
ORDER BY it.transitions DESC, it.site_host, it.source_path, it.dest_path;

This generates report data covering the last 30 days.

Note that it removes query strings from the paths to make the reports cleaner.

Create a Data Source in Data Studio

Go to the Data Studio and click Create->Data Source. Select BigQuery and find the report table in your dataset called ‘internal_link_clicks’.

Copy the Report using your new data source

Go to the reports template and make a copy via the top right … menu. Then select your new data source for the report.

And you now have a shiny new Internal Link Clicks Report.

BigCommerce apps by BigCommerce experts for BigCommerce stores

Internal Link Clicks Report