google-analytics - 每个用户的(不同)日期范围之间的 VisitId

Question

对于每个 fullvisitorId ，我正在尝试获取所有 visitId在 date_1 和 date_2 之间。这对于每个用户来说当然是不同的。

谁能给我任何指示我该怎么做？

例如:

user_1:我想要 6 月 1 日至 20 日之间的所有访问 ID

user_2:我想要 6 月 12 日至 27 日之间的所有访问 ID
...等儿子

date_1 和 date_2 对应于他们在网站上执行的重要操作( Event 点击)。下载试用和购买

在此先感谢您提供任何线索。

Answer 1

解决此问题的一种可能方法是使用 analytical functions .举个例子:

#standardSQL
WITH data AS(
  select '1' as user, '1' as visitid, '20170520' as date, ARRAY<STRUCT<hitNumber INT64, eventInfo STRUCT<eventCategory STRING> >> [STRUCT(1 as hitNumber, STRUCT('event1' as eventCategory) as eventInfo)] hits UNION ALL
  select '1' as user, '2' as visitid, '20170521' as date, ARRAY<STRUCT<hitNumber INT64, eventInfo STRUCT<eventCategory STRING> >> [STRUCT(1 as hitNumber, STRUCT('' as eventCategory) as eventInfo)] hits UNION ALL
  select '1' as user, '3' as visitid, '20170522' as date, ARRAY<STRUCT<hitNumber INT64, eventInfo STRUCT<eventCategory STRING> >> [STRUCT(1 as hitNumber, STRUCT('event2' as eventCategory) as eventInfo)] hits UNION ALL
  select '1' as user, '4' as visitid, '20170523' as date, ARRAY<STRUCT<hitNumber INT64, eventInfo STRUCT<eventCategory STRING> >> [STRUCT(1 as hitNumber, STRUCT('' as eventCategory) as eventInfo)] hits UNION ALL

  select '2' as user, '1' as visitid, '20170520' as date, ARRAY<STRUCT<hitNumber INT64, eventInfo STRUCT<eventCategory STRING> >> [STRUCT(1 as hitNumber, STRUCT('event1' as eventCategory) as eventInfo)] hits UNION ALL
  select '2' as user, '2' as visitid, '20170521' as date, ARRAY<STRUCT<hitNumber INT64, eventInfo STRUCT<eventCategory STRING> >> [STRUCT(1 as hitNumber, STRUCT('event2' as eventCategory) as eventInfo)] hits UNION ALL
  select '2' as user, '3' as visitid, '20170522' as date, ARRAY<STRUCT<hitNumber INT64, eventInfo STRUCT<eventCategory STRING> >> [STRUCT(1 as hitNumber, STRUCT('' as eventCategory) as eventInfo)] hits union all

  select '3' as user, '1' as visitid, '20170520' as date, ARRAY<STRUCT<hitNumber INT64, eventInfo STRUCT<eventCategory STRING> >> [STRUCT(1 as hitNumber, STRUCT('event1' as eventCategory) as eventInfo)] hits UNION ALL
  select '3' as user, '2' as visitid, '20170521' as date, ARRAY<STRUCT<hitNumber INT64, eventInfo STRUCT<eventCategory STRING> >> [STRUCT(1 as hitNumber, STRUCT('' as eventCategory) as eventInfo)] hits UNION ALL
  select '3' as user, '3' as visitid, '20170522' as date, ARRAY<STRUCT<hitNumber INT64, eventInfo STRUCT<eventCategory STRING> >> [STRUCT(1 as hitNumber, STRUCT('' as eventCategory) as eventInfo)] hits
)

SELECT
  user,
  visitid,
  date
FROM(
  SELECT 
    user,
    visitid,
    date,
    MIN(CASE WHEN hits.eventInfo.eventCategory = 'event1' THEN date END) OVER(PARTITION BY user) min_date,
MAX(CASE WHEN hits.eventInfo.eventCategory = 'event2' THEN date END) OVER(PARTITION BY user) max_date
FROM data,
UNNEST(hits) hits
)
WHERE date BETWEEN min_date AND max_date

在哪里 data是您的 ga_sessions 数据的模拟(我将“fullvisitorid”命名为“用户”)。

这假设给定用户可以在日期 1 和日期 2 有不同的事件(因此它分别采用 MIN 和 MAX)，并假设您将事件保存在 eventCategory 中。字段(鉴于您的“下载”和“购买”事件是在 session 级别定义的，我建议您使用 customDimensions 字段而不是 hits.eventInfo.eventCategory 字段)。

除了分析函数，您还可以使用 ARRAYs and STRUCTs标准 SQL 版本:

SELECT
  user,
  ARRAY(SELECT AS STRUCT visitid, date FROM UNNEST(user_data) WHERE date BETWEEN min_date AND max_date) user_data
FROM(
  SELECT 
    user,
    ARRAY_AGG((SELECT AS STRUCT visitid, date)) user_data,
    MIN(CASE WHEN EXISTS(SELECT 1 FROM UNNEST(hits) hits WHERE hits.eventInfo.eventCategory = 'event1') then date END) min_date,
    MAX(CASE WHEN EXISTS(SELECT 1 FROM UNNEST(hits) hits WHERE hits.eventInfo.eventCategory = 'event2') THEN date END) max_date
FROM data
GROUP BY user
)
WHERE ARRAY_LENGTH(ARRAY(SELECT AS STRUCT visitid, date FROM UNNEST(user_data) WHERE date BETWEEN min_date AND max_date)) > 0

如果我所做的假设与您的数据不一致，您可以调整这些技术来查询您想要的内容。您还可以将模拟数据用于测试目的(以及对其进行调整以更好地适应您的数据集)。