Analytics-Mastery Pty Ltd

Contact Us
,

BigQuery – query the number of pageviews per medium for a specific landing page

1–2 minutes

Overview

The following BigQuery script will list the number of pageviews per medium for a specific landing page

Code

SELECT

DATE,

--trafficSource.source AS source,

trafficSource.medium AS medium,

SUM(totals.pageviews) AS pageviews,

-- SUM(totals.visits) AS sessions,

--COUNT(DISTINCT fullVisitorId) AS users

FROM `table name`,

UNNEST (hits) AS hits

WHERE _TABLE_SUFFIX BETWEEN 'start date e.g. 20220111' AND 'end date e.g. 20220111' 

and REGEXP_CONTAINS(hits.page.pagePath, r'page path')

and hits.type = 'PAGE'

AND hits.isEntrance = TRUE

and hits.page.hostname = 'hostname'

GROUP BY

DATE,

--source,

medium

Note:

Most of organizations have different environments for their website e.g. DEV, QA, UAT, and PROD. That’s why we need hits.page.hostname = ‘hostname’ to limit our query to a particular environment.

Feel free to uncomment the lines source, sessions, and users to view their results.