页面序列路径BigQuery中的Google Analytics(分析)数据 [英] Page sequence paths Google Analytics data in BigQuery

问题描述

我正在使用BigQuery处理有关某个主题的Google Analytics(分析)数据，以了解页面序列路径.这些页面的范围是自定义维度(命中级别)，并且正在对一组页面进行分组.此外，还包括时间戳，以获取有关序列的更多见解.

I am working in BigQuery with Google Analytics data on a topic to understand the page sequence paths. The pages are scoped to the custom dimension (hit level) with is grouping a set of pages. Furthermore, TimeStamp is included to gain more insights about the sequence.

查询(旧版SQL):

SELECT 
date,
STRFTIME_UTC_USEC(SEC_TO_TIMESTAMP(visitStartTime + hits.time/1000),"%Y-%m-%d %H:%M:%S") AS TimeStamp,  
concat(fullvisitorid,".", string(visitId)) as sessionsid, 
MAX(IF(hits.customDimensions.index=3, hits.customDimensions.value, NULL)) 
WITHIN hits AS CustomDimension, 
hits.hitNumber as hits.hitNumber, 
visitNumber,    
FROM
TABLE_DATE_RANGE([XXXXXXXX.ga_sessions_], TIMESTAMP('2017-08-01'), 
TIMESTAMP('2017-08-05'))
having sessionsid = "3712616268105648149.1501799276"
ORDER BY
hits.hitNumber AS

结果(基于1个sessionid): 图像结果查询

我有两个问题:

1) 在下面的示例中，CustomDimension谢谢"是谢谢"页面.奇怪的事实是CustomDimension"thankYou"被多次计数.在这种情况下，彼此相距2秒，但在其他情况下，有时在同一时刻. GA跟踪似乎很好，并且只能发送1次匹配(不考虑刷新).如果我在查询中遗漏了一些会影响这种行为的想法，那么有什么主意吗?

1) In the example below the CustomDimension "Thankyou" is the "Thank you" page. Strange fact is that CustomDimension "thankYou" is counted more than once. In this case 2 seconds after each other, but in other cases sometimes at the same moment. GA tracking seems to be fine and can only can send 1 hit (not taking a refresh into consideration). Any ideas if I missing something in the query that is effecting this behaviour?

2)页面顺序正在开始CustomDimension谢谢".这是订购过程的谢谢"页面，然后用户继续进入主页.用户从此页面开始是不合逻辑的.您知道这是否是由查询引起的吗?

2) The page sequence is starting CustomDimension "ThankYou". This is the Thank you page of the order process and then the users continues to the Homepage. It is not logic that a user starts at this page. Do you know if this is caused by the query?

希望有人能给我一些建议，使我朝正确的方向前进.

Hope that somebody can give me some advice to put me in the right direction.

谢谢.

推荐答案

相关信息和方便的示例可在"BigQuery食谱"在线文档点击顺序"中找到.

Related information and handy examples are to be found on the "BigQuery cookbook" on-line document, "Sequence of hits" sub-chapter. The general pattern is given as:

SELECT fullfullVisitorID, visitID, visitNumber, hits.hitNumber, hits.page.pagePath
FROM [ ‘Dataset Name’ ]
WHERE hits.type=[ ‘type of hit’ ];

在第一行上，您确定访客ID和访客ID的唯一组合.每次点击都将属于这两个维度的唯一组合. 在第一行，您还列出了页面路径的元素. FROM语句列出了数据源. 在WHERE语句中，您将查询限制为特定类型的匹配，以控制观察到的互动类型.

On the first line, you identify the unique combination of visitor ID and visit IDs. Every hit will belong to a unique combination of these two dimensions. On the first line, you also list the elements of a page path. The FROM statement lists the data source(s). In the WHERE statement, you limit the query to specific types of hits to control what kinds of interactions you observe.

可以在"LunaMetrics Google BigQuery食谱"页面上阅读直接相关的示例，更确切地说，可以将其作为会话中的所有相关页面"

A directly relevant example can be read on the "LunaMetrics Google BigQuery Recipes" page, more precisely as the "All Related Pages in a Session" query:

SELECT
  cur_hit.fullVisitorId AS fullVisitorId,
  cur_hit.visitId AS visitID,
  cur_hit.hits.hitNumber AS cur_hitnumber,
  cur_hit.hits.page.pagePath as cur_pagePath,
  cur_hit.hits.time AS cur_time,
  MAX(prev_hit.hits.hitNumber) AS prev_hitNumber,
FROM
  FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits) AS prev_hit
INNER JOIN
  FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits) AS cur_hit
ON
  prev_hit.fullvisitorid = cur_hit.fullvisitorid
  AND prev_hit.visitid = cur_hit.visitid
WHERE
  prev_hit.hits.hitNumber < cur_hit.hits.hitNumber
  AND prev_hit.hits.type = "PAGE"
  AND cur_hit.hits.type = "PAGE"
GROUP BY fullVisitorId, visitID, cur_hitnumber, cur_pagePath, cur_time

这篇关于页面序列路径BigQuery中的Google Analytics(分析)数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！