用于Google BigQuery的SQL查询，用于计算会话和网页浏览量

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.

我正在使用Google Big Query查询我网站的每日Google分析报告。我一次在7个表(每日7个报告)上运行查询,因为我想使用每周结果。

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.

我想运行一个查询,显示“用户具有= = x个会话且具有> = y页面浏览量”。我在构建此查询时遇到了困难。

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?

生成的表应显示fullVisitorId,totals.visits(会话数),totals.pageviews(会话中的综合浏览量总数)。我应该使用子查询,还是有其他方法?

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

如果您想查看完整方案,请使用以下链接:https://support.google.com/analytics/answer/3437719?hl = zh-CN

1 个解决方案

#1

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

要在示例数据库上运行此查询,请访问:https://support.google.com/analytics/answer/3416091?hl = zh-CN

#1