计算BigQuery中的Google Analytics(分析)唯一事件 [英] Counting google analytics unique events in BigQuery

问题描述

我已经设法通过ISOweek计算了总事件，但是没有使用BigQuery计算给定Google Analytics(分析)事件的唯一事件.检查GA时，total_events与点上的GA接口匹配，但unique_events已关闭.你知道我该怎么解决吗?

I have managed to calculate total events by ISOweek but not unique events for a given Google Analytics Event using BigQuery. When checking GA, total_events matches the GA interface on the dot but unique_events are off. Do you know how I can solve this?

查询:

SELECT INTEGER(STRFTIME_UTC_USEC(PARSE_UTC_USEC(date),"%V")) iso8601_week_number,
hits.eventInfo.eventCategory,
hits.eventInfo.eventAction,
COUNT(hits.eventInfo.eventCategory) AS total_events,
EXACT_COUNT_DISTINCT(fullVisitorId) AS unique_events
FROM
    TABLE_DATE_RANGE([XXXXXX.ga_sessions_], TIMESTAMP('2017-05-01'), TIMESTAMP('2017-05-07'))
WHERE
  hits.type = 'EVENT' AND hits.eventInfo.eventCategory = 'BIG_Transaction'
GROUP BY
iso8601_week_number, hits.eventInfo.eventCategory, hits.eventInfo.eventAction

推荐答案

根据范围，您需要count(distinct )不同的东西，但始终需要满足以下条件:

Depending on the scope you need to count(distinct ) different things, but you always need to fulfill these conditions:

• 独特事件是指类别，操作和标签的组合
• 确保eventAction不是NULL
• 确保eventLabel不是NULL
• eventCategory被允许为NULL

• unique events refer to the combination of category, action and label
• make sure eventAction is not NULL
• make sure eventLabel is not NULL
• eventCategory is allowed be NULL

我正在使用COALESCE()来避免NULL s

I'm using COALESCE() to avoid NULLs

SELECT
  SUM( (SELECT COUNT(h.eventInfo.eventCategory) FROM t.hits h) ) events,
  SUM( (SELECT COUNT(DISTINCT 
    CONCAT( h.eventInfo.eventCategory,
      COALESCE(h.eventinfo.eventaction,''),
      COALESCE(h.eventinfo.eventlabel, ''))
      )
    FROM
      t.hits h ) ) uniqueEvents
FROM
  `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910` t

命中范围示例

SELECT
  h.eventInfo.eventCategory,
  COUNT(1) events,
  -- we need to take sessions into account, so we add fullvisitorid and visitstarttime
  COUNT(DISTINCT CONCAT(fullvisitorid, CAST(visitstarttime AS string), 
    COALESCE(h.eventinfo.eventaction,''), 
    COALESCE(h.eventinfo.eventlabel, ''))) uniqueEvents
FROM
  `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910` t,
  t.hits h
WHERE
  h.type='EVENT'
GROUP BY
  1
ORDER BY
  2 DESC

hth！

这篇关于计算BigQuery中的Google Analytics(分析)唯一事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！