SQL query for Google BigQuery to count Sessions and Pageviews

2
votes

I am using Google Big Query to query the daily Google analytics reports for my website. I am running queries on 7 tables (the 7 daily reports) at a time, because I want to use weekly results.

I would like to run a query that shows "Users with >= x sessions and with >= y page views". I am having difficulties framing this query.

The resulting table should show the fullVisitorId, totals.visits (The number of sessions), totals.pageviews (Total number of pageviews within the session). Should I use a subquery, or is there some other method?

Please use the following link if you'd like to have a look at the complete scheme: https://support.google.com/analytics/answer/3437719?hl=en

1
sqlgoogle-analyticsgoogle-bigquery
Done. Thanks for the info. - Shreyans

1 Answers

2
votes

A basic query would look like:

SELECT
  fullVisitorId,
  SUM(totals.visits) as visits,
  SUM(totals.pageviews) as pageviews,
FROM
  TABLE_DATE_RANGE([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_], 
                   TIMESTAMP('2013-09-10'), 
                   TIMESTAMP('2013-09-17'))
GROUP BY
  fullVisitorId
HAVING visits>0 and pageviews>0

To run this query on a sample database visit: https://support.google.com/analytics/answer/3416091?hl=en