在GA的BigQuery集成上选择不同的customDimensions

对于具体的问题抱歉,但我觉得我已经死了,因为我对SQL的知识并没有那么深入。

GoogleAnalytics原始数据的BigQuery实现中出现的数据如下所示:

|-visitId
|- date
|- (....)
+- hits
   |- time
   +- customDimensions
      |- index
      |- value
   +- customMetrics
      |- index
      |- value

我知道总有一些点击会向GA发送一些数据。 具体而言,我想customDimensions.index = 43,customDimensions.index = 24和customMetrics.index = 14。 要指定,维度43是被看见或出售的对象,维度24告诉我他们是否被看见和公制14,当它被卖了时,值是1。 我的最终结果应该是这样的:

customDimension.value( when index=43)    count(when customDimension.index=24 and customDimension.value=='ficha')      count(when customMetrics.index=14 and customMetrics.value ==1))

按customDimension.value分组(当index = 43时)我知道每次发送的命中customMetrics.index = 14,同样的命中有customDimensions.index = 43,同样的方式,customDimensions.index = 24总是有一个customDimensions的.index = 43。 我实际上设法创建了一个我想要的SQL,但花费了多少钱? 它很大,很慢,很丑。 我目前正在做的是:

  • 创建三个表,全部具有visitId,hit.time和index = 14,24,43时的值
  • 左连接43与24 ON 43.visitId == 24.visitId AND 43.hits.time == 24.hits.time as result
  • 左连接结果14 ON 14.visitId == result.visitId AND 14.hits.time == result.hits.time
  • 我对visitId或hits.time不感兴趣,它只是一种关联相同命中的方法(并且在customMetrics.index = 14和value = 1时知道他们购买了哪个产品。

    这是我的代码:

    SELECT Tviviendasvisitas.viviendaId as ViviendaID ,sum(Tviviendasvisitas.NumeroVisitas) as NumeroVisitas,sum(Ttransacciones.Transactions) as Transactions FROM (
    SELECT Tviviendas.visitId as visitId, Tviviendas.hits.time as visitTime, Tviviendas.ViviendaID as viviendaId,Tvisitas.visitas as NumeroVisitas FROM (
    SELECT  visitId,hits.time,hits.customDimensions.value as ViviendaID FROM ((TABLE_DATE_RANGE([-------.ga_sessions_], TIMESTAMP('2014-09-01'), TIMESTAMP('2014-09-30'))))
    WHERE hits.customDimensions.index = 43 
    GROUP EACH BY visitId,hits.time, ViviendaID)as Tviviendas
    
    LEFT JOIN EACH(
    SELECT  visitId,hits.time,count(*) as visitas FROM ((TABLE_DATE_RANGE([-------.ga_sessions_],  TIMESTAMP('2014-09-01'), TIMESTAMP('2014-09-30'))))
    WHERE hits.customDimensions.index = 24 AND hits.customDimensions.value=='ficha'
    GROUP EACH BY visitId,hits.time) as Tvisitas
    ON Tvisitas.visitId==Tviviendas.visitId AND Tvisitas.time==Tviviendas.time) as Tviviendasvisitas
    
    LEFT JOIN EACH (
    SELECT  visitId ,hits.time as transactionTime, sum(hits.customMetrics.value) as Transactions FROM(TABLE_DATE_RANGE([-------.ga_sessions_], TIMESTAMP('2014-09-01'), TIMESTAMP('2014-09-30')))
    WHERE hits.customMetrics.index = 14 AND hits.customMetrics.value=1
    GROUP BY visitId, transactionTime) as Ttransacciones
    ON Tviviendasvisitas.visitId==Ttransacciones.visitId AND Tviviendasvisitas.visitTime==Ttransacciones.transactionTime
    GROUP BY ViviendaID
    

    运行此查询花费了太多时间来创建带有结果的propper控制面板。

    所以,如果这是我的最终结果,请帮助我。 我觉得应该有一个更好的解决方案来解决这个问题,但我似乎无法亲自找到它。

    帮帮我?


    通过使用BigQuery的作用域聚合(WITHIN子句),您应该能够在没有连接的情况下构建此查询。 这里有一个小例子,它可能不完全是你想要的逻辑,但应该说明一些可能性:

    SELECT  visitId, hits.time,
            SOME(hits.customDimensions.index = 43) WITHIN RECORD AS has43,
            SUM(IF(hits.customDimensions.index = 24 AND hits.customDimensions.value = 'ficha', 1, 0)) WITHIN RECORD AS numFichas,
            SUM(IF(hits.customMetrics.index = 14, hits.customMetrics.value, 0)) WITHIN RECORD AS totalValues
    FROM ((TABLE_DATE_RANGE([-------.ga_sessions_], TIMESTAMP('2014-09-01'), TIMESTAMP('2014-09-30'))))
    HAVING has43
    

    该示例显示了三个WITHIN RECORD聚合,意味着它们将在单个记录的重复字段上进行计算。 SOME()采用布尔表达式,如果记录中的任何字段满足该表达式,则返回true。 因此,对于有customDimensions.index = 43的一个或多个匹配的访问,43有效.HAVING子句筛选出记录为false的记录。

    SUM(IF(...))表达式计算index = 24和value ='ficha'的customDimensions总数以及与index = 14的customMetrics关联的总值。


    如果您只想从hitLevel customDimension获取值并将其添加到其自己的列中,则这是一个巧妙的技巧:

    SELECT fullVisitorId, visitId, hits.hitNumber,
    MAX(IF(hits.customDimensions.index=43, 
           hits.customDimensions.value, 
           NULL)) WITHIN hits AS product,
    FROM [tableID.ga_sessions_20150305]
    LIMIT 100
    
    链接地址: http://www.djcxy.com/p/82205.html

    上一篇: Selecting different customDimensions on BigQuery integration of GA

    下一篇: Pandas, groupby and finding maximum in groups, returning value and count