GA BigQuery:使用自定义维度计算综合浏览量 [英] GA BigQuery: Calculating pageviews with a custom dimension

问题描述

我正在使用GA BigQuery数据来计算3个维度的总综合浏览量:日期，设备类别和自定义维度(此处称为类型").

Using GA BigQuery data, I am trying to calculate the total pageviews across 3 dimensions: date, device category, and a custom dimension (called "type" here).

因此，所需的输出是:

So the desired output is:

因此，应该列出每个日期，设备和类型组合的总浏览量.

So the total pageviews should be listed for each date, device, and type combination.

我使用以下查询来获得此结果.我需要取消嵌套类型"维度，因为它是自定义维度.

I used the following query to get this result. I need to unnest the "type" dimension because it is a custom dimension.

#standardsql

SELECT date, device, cd6_type, SUM(pvs) AS pageviews    

FROM(
  SELECT 
        date,
        fullvisitorID, 
        visitID, 
        totals.pageviews AS pvs,
        device.deviceCategory AS device
       , MAX(IF(hcd.index = 6, hcd.value, NULL)) AS cd6_type

        FROM `ga360-173318.62903073.ga_sessions_*` AS t,
              UNNEST (t.hits) AS h,
              UNNEST (h.customDimensions) AS hcd

        WHERE _table_suffix BETWEEN (SELECT FORMAT_DATE('%Y%m%d', '2019-07-08'))
                                AND (SELECT FORMAT_DATE('%Y%m%d', '2019-07-08'))

        AND h.type = "PAGE"

        GROUP BY
              date,
              fullVisitorID,
              visitID,
              totals.pageviews,
              device
   )

GROUP BY date, device, cd6_type

问题是我的结果与GA中显示的结果不匹配；查询返回的结果较少.在GA中，以上结果是:

The problem is that my results do not match what appears in GA; the query returns fewer results. In GA, the above results are:

• 180,812移动版，A型网页浏览量(GBQ中为149,149)
• 30,949平板电脑，A型网页浏览量(GBQ中为16,863)

我不确定为什么它们在两个系统之间不匹配，我想知道其他人如何计算跨维度的总浏览量.

I'm not sure why they don't match across the 2 systems, and am wondering how others calculate total pageviews across dimensions.

推荐答案

您正在与 customdimensions 交叉加入，因此您不是在计算页面，而是在页面上自定义尺寸.只是不要执行这种交叉联接，如果使用子查询获得自定义维度，则不需要它.

You're cross joining with customdimensions, so you're not counting pages, but custom dimensions on pages. Just don't do this cross join, you don't need it if you get your custom dimension using a subquery.

#standardsql

SELECT  
  date,
  device.deviceCategory AS device
  ,(SELECT hcd.value FROM h.customdimensions AS hcd WHERE hcd.index = 6 ) AS cd6_type
  ,COUNT(1) as pageviews
FROM  `bigquery-public-data.google_analytics_sample.ga_sessions_*`  AS t,
      UNNEST(t.hits) AS h

WHERE _table_suffix between '20170801' and '20170801' 

AND h.type = "PAGE"

GROUP BY date, device, cd6_type

这篇关于GA BigQuery:使用自定义维度计算综合浏览量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！