在BigQuery中查询点击量和自定义维度? [英] Query hits and custom dimensions in the BigQuery?

问题描述

我正在使用BigQuery中的GoogleAnalytics数据. 我想输出2列:特定事件操作(点击)和自定义维度(基于会话).所有这些，使用标准SQL.我不知道该怎么做.文档编制也无济于事.请帮我.这就是我正在尝试的:

I am working with the GoogleAnalytics data in the BigQuery. I want to output 2 columns: specific event actions (hits) and custom dimension (session based). All that, using Standard SQL. I cannot figure out how to do it correctly. Documentation does not help either. Please help me. This is what I am trying:

SELECT
  (SELECT MAX(IF(index=80, value, NULL)) FROM UNNEST(customDimensions)) AS is_app,
  (SELECT hits.eventInfo.eventAction) AS ea
FROM  
  `table-big-query.105229861.ga_sessions_201711*`, UNNEST(hits) hits
WHERE 
  totals.visits = 1
  AND _TABLE_SUFFIX BETWEEN '21' and '21'
  AND EXISTS(SELECT 1 FROM UNNEST(hits) hits
    WHERE hits.eventInfo.eventCategory = 'SomeEventCategory'
  )

推荐答案

尝试提供不属于原始表架构的表和子表名称.始终告诉您要引用的表-交叉联接时，基本上是在添加新列(此处为h.*-展平)-但旧列(hits.*-嵌套)仍然存在. 我将其命名为ga_sessions_ * t，并用它来引用交叉联接和customDimension.

Try to give your tables and sub-tables names that are not part of the original table schema. Always tell to which table you're referring - when cross joining, you're basically adding new columns (here h.* - flattened) - but the old ones (hits.* - nested) still exist. I named ga_sessions_* t and use it to refer the cross-join and also the customDimension.

此外:您不再需要使用MAX()作为customDimensions的旧版SQL技巧.现在这是一个简单的子查询:)

Also: You don't need the legacy sql trick using MAX() for customDimensions anymore. It's a simple sub-query now :)

尝试:

SELECT
  (SELECT  value FROM t.customDimensions where index=80) AS is_app, -- use h.customDimensions if it is hit-scope
  eventInfo.eventAction AS ea
FROM  
  `projectid.dataset.ga_sessions_201711*` t, t.hits h
WHERE 
  totals.visits = 1
  AND _TABLE_SUFFIX BETWEEN '21' and '21'
  AND h.eventInfo.eventCategory is not null

这篇关于在BigQuery中查询点击量和自定义维度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！