Google Big Query: увеличьте количество посетителей с помощью специального параметра

Ниже приведен стандартный SQL BigQuery.

SELECT DATE 
  ,COUNT(DISTINCT(fullvisitorid)) AS User
  ,SUM( newVisits ) AS New_Visitors
  ,Orig
FROM (
  SELECT PARSE_DATE('%Y%m%d', t.date) AS DATE
    ,fullvisitorid
    ,totals.newVisits AS newVisits
    ,(IF(customDimensions.index=1, customDimensions.value,NULL))  AS Orig
  FROM `table` AS t
  CROSS JOIN UNNEST(hits) AS hit
  CROSS JOIN UNNEST(hit.customDimensions ) AS customDimensions  
  GROUP BY DATE, orig, fullvisitorid, newVisits
)
GROUP BY DATE, Orig

Лучший способ в вашем случае - удалить перекрестные соединения и вместо этого использовать подвыборки:

SELECT
  PARSE_DATE('%Y%m%d', t.date) AS Date
  ,(SELECT value FROM UNNEST(customDimensions) WHERE index=1) Orig
  ,COUNT(DISTINCT(fullvisitorid)) AS User
  ,SUM( totals.newVisits ) AS New_Visitors
FROM
  `table` t
GROUP BY Orig, Date

Если у вас есть измерение в области действия и вам действительно нужно сгладить таблицу, вам нужно создать идентификатор сеанса, который вы можете считать отдельным. Это потому, что вы повторяете все поля области сеанса в области обращения, применяя перекрестное соединение:

SELECT
  PARSE_DATE('%Y%m%d', t.date) AS Date
  ,(SELECT value FROM h.customDimensions WHERE index=2) justAHitCd
  ,h.page.pagePathLevel1
  ,COUNT(DISTINCT(fullvisitorid)) AS User

  -- create session id and count distinct
  ,COUNT(DISTINCT CONCAT(fullvisitorid, CAST(visitstarttime AS STRING)) ) AS all_sessions

  -- only count distinct session id of sessions where totals.newVisits = 1
  ,COUNT(DISTINCT 
    IF(totals.newVisits=1, 
      CONCAT(fullvisitorid, CAST(visitstarttime AS STRING)), 
      NULL )
   ) AS New_Visitors

FROM
  -- flatten table to hit scope (comma means cross-join in stnd sql)
  `table` t, t.hits h
GROUP BY 1,2,3

Поэтому в случае новых посетителей я предоставляю идентификатор сеанса только в том случае, если totals.newVisits = 1 - иначе оператор if предоставляет NULL, который не подлежит подсчету.

Если у вас есть что-то похожее по продукту, вам нужно будет создать идентификатор, который учитывает сеанс учетной записи и нажимает. Например. подсчет страниц для productSku:

SELECT
  PARSE_DATE('%Y%m%d', t.date) AS Date
  ,(SELECT value FROM h.customDimensions WHERE index=2) justAHitCd
  ,p.productSku
  ,COUNT(DISTINCT fullvisitorid) AS users
  ,COUNT(DISTINCT CONCAT(fullvisitorid, CAST(visitstarttime AS STRING))) AS sessions
  ,COUNT(DISTINCT 
    IF(h.type='PAGE',
      CONCAT(fullvisitorid, cast(visitstarttime AS STRING),CAST(hitNumber AS STRING)),
      NULL)  
  ) as pageviews
  ,COUNT(1) AS products
FROM
  `table` t, t.hits h LEFT JOIN h.product p
GROUP BY 1,2,3

Обратите внимание, что я остался присоединиться к массиву товаров. Поскольку иногда она пуста, перекрестное соединение уничтожит всю информацию о совпадениях: перекрестное соединение с пустой таблицей приводит к пустой таблице.

Надеюсь, это поможет!