Как добавить соответствующую платформу (hits.datasource) в таблицу в BigQuery?
Я использую BigQuery с данными, экспортированными из Google Analytics. Попытка добавить платформу в отчет на уровне сеанса (не знаю, как правильно вызвать это измерение, но в основном мне нужно разделить «приложение» и «Интернет», поэтому «типа устройства» недостаточно).
Я пытаюсь использовать hits.datasource, но есть одна проблема. На самом деле это не измерение уровня сеанса. Я имею в виду, что это так, но из-за специфики отслеживания в моей компании иногда есть несколько разных источников данных для одного сеанса.
Итак, вот общий сценарий, над которым я сейчас работаю:
#standardSQL
SELECT
'DE' as Country,
FORMAT_DATE("%d.%m.%Y", PARSE_DATE("%Y%m%d",date)) AS Date,
channelGrouping AS Channel,
trafficSource.medium as Medium,
trafficSource.source as Source,
trafficSource.campaign as Campaign,
hits[OFFSET(0)].datasource AS Platform, -- part I'm struggling with --
device.deviceCategory as Device,
CASE WHEN device.operatingSystem ='iOS' OR device.operatingSystem ='Android' then device.operatingSystem else 'Other' END as OS,
CASE WHEN SUM(totals.visits)>0 THEN SUM(totals.visits) ELSE 0 END AS Sessions,
CASE WHEN SUM(totals.totalTransactionRevenue)>0 THEN SUM(totals.totalTransactionRevenue)/POW(10,6) ELSE 0 END AS Revenue,
CASE WHEN SUM(totals.transactions)>0 THEN SUM(totals.transactions) ELSE 0 END AS Orders,
CASE WHEN SUM(totals.pageviews) >0 then SUM(totals.pageviews) ELSE 0 END as Pageviews,
CASE WHEN SUM(totals.screenviews) >0 then SUM(totals.screenviews) ELSE 0 END as Screenviews,
CASE WHEN SUM(totals.bounces) >0 then SUM(totals.bounces) ELSE 0 END as Bounces
FROM
`oval-unity-88908.97525772.ga_sessions_*` t
GROUP BY 1,2,3,4,5,6,7,8,9
Я оставил hits[OFFSET(0)].datasource в качестве временной замены, но я хотел бы поместить туда наиболее часто используемый источник данных в сеансе.
Вот пара моих попыток заменить эту строку кода. Пока ничего не работало.
(SELECT ht.datasource FROM (SELECT datasource, SUM(t.totals.hits) FROM t.hits GROUP BY 1 ORDER BY 2 DESC LIMIT 1) ht) AS Platfrom,
.
(SELECT ht.datasource FROM (SELECT datasource, COUNT(1) FROM t.hits GROUP BY 1 ORDER BY 2 DESC LIMIT 1) ht) AS Platfrom,
.
(SELECT hits.datasource FROM UNNEST(t.hits) ORDER BY hits.hitNumber LIMIT 1) AS Platform,
С нетерпением жду ваших советов о том, как улучшить этот сценарий или, может быть, даже другого подхода к тому, что я пытаюсь сделать. Ваше здоровье!
Ответы 2
Ваша последняя попытка правильно использовала UNNEST, но вам также необходимо выполнить JOIN с таблицей:
FROM UNNEST(t.hits) JOIN t
Вам как-то нужно решить, как агрегировать эту информацию. Обычно я использую наиболее частый источник данных:
SELECT
(SELECT datasource FROM t.hits GROUP BY 1 ORDER BY COUNT(1) DESC limit 1) mostFrequDatasource
,(SELECT STRING_AGG(DISTINCT datasource) FROM t.hits) allDataSources
,SUM(totals.visits) sessions
FROM
`ga_sessions` t
GROUP BY 1,2
Для тестирования я добавил allDataSources, показывающий все источники данных, появляющиеся в сеансе.