Google Big Query使用自定义维度获取新用户数量和用户数量 [英] Google Big Query using Custom Dimension to get new user count and user count

问题描述

我试图通过自定义维度值和日期获取新用户和用户数，合格的访问者。这是代码。但是我无法获得与Google Analytics的数据关联。我认为问题是UNNEST创建重复和total.newVisits是在不同的粒度。谢谢！

  SELECT 
 PARSE_DATE（'％Y％m％d'，t.date）as Date 
，count（distinct（FullvisitorID））as visitor_count 
，sum（totals.newVisits）AS New_Visitors 
，if（customDimensions.index = 2，customDimensions.value，null）as orig 
 FROM`table` as t 
 CROSS JOIN UNNEST（hits）AS hit 
 CROSS JOIN UNNEST（hit.customDimensions）as customDimensions 
 WHERE 
 date ='20170101'
 
 GROUP BY DATE，if（customDimensions.index = 2，customDimensions.value，null）
  

解决方案

试试这个：

  SELECT 
 PARSE_DATE（'％Y％m％d'，date）AS日期，
 COUNT（DISTINCT fullvisitorid）visitor_count，
 SUM（totals.newVisits）AS New_Visitors， 
（SELECT value FROM UNNEST（hits），UNNEST（customDimensions）WHERE index = 2 LIMIT 1）orig 
 FROM`dataset_id.ga_sessions_20170101` 
 GROUP BY Date，orig 
  

它基本上是一样的，但不是在外层执行 UNNEST 查询此解决方案仅在命中级别应用此操作，避免在查询中观察到的 totals.newVisits 重复。

I am trying to get the new user and user count, qualified visitors by custom dimension value and date. Here is the code. But I couldn't get the data tie with Google Analytics. I think the problem is the UNNEST creates duplicate and total.newVisits is on different granularity. Thank you!

SELECT
      PARSE_DATE('%Y%m%d', t.date) as Date
      ,count(distinct(FullvisitorID)) as visitor_count
      ,sum( totals.newVisits ) AS New_Visitors
      ,if(customDimensions.index=2, customDimensions.value,null)  as orig
    FROM `table` as t
      CROSS JOIN UNNEST(hits) AS hit
      CROSS JOIN UNNEST(hit.customDimensions) AS customDimensions
    WHERE
      date='20170101'

GROUP BY DATE,if(customDimensions.index=2, customDimensions.value,null) 

解决方案

Try this instead:

SELECT 
  PARSE_DATE('%Y%m%d', date) AS Date,
  COUNT(DISTINCT fullvisitorid) visitor_count,
  SUM(totals.newVisits) AS New_Visitors,
  (SELECT value FROM UNNEST(hits), UNNEST(customDimensions) WHERE index = 2 LIMIT 1) orig
FROM `dataset_id.ga_sessions_20170101`
GROUP BY Date, orig

It's basically the same thing but instead of doing the UNNEST in the outer query this solution only applies this operation at the hit level which avoids the duplication of totals.newVisits you observed in your query.

这篇关于Google Big Query使用自定义维度获取新用户数量和用户数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！