Сглаживание данных Google Analytics (с повторяющимися полями) больше не работает

Ответ 1

На самом деле это результат исправления, представленного мной на прошлой неделе, и препятствует получению неверных результатов.

BigQuery по умолчанию выравнивает все результаты запроса перед их возвратом, но мы хотим только сгладить одно независимо повторяющееся поле, чтобы избежать расширения данных по перекрестным данным. Ошибка заключалась в том, что наши проверки на несколько повторяющихся полей в некоторых случаях не учитывали повторяемость родительских записей, что заставило нас не сглаживать некоторые независимо повторяющиеся поля. Это означало, что мы могли бы возвращать плоские строки, где независимо повторяющиеся значения фактически "сплющивались" на заново повторяющиеся значения вместо генерации кросс-продукта, что на самом деле является неправильным результатом.

То, что вы видите здесь, является результатом более строгой проверки: у вас есть (по крайней мере) два повторяющихся поля в вашей выходной схеме, прежде чем мы попытаемся сгладить результаты.

Еще одна важная вещь: функция FLATTEN ([table-value], [field]) только выравнивает повторяемость поля, которое вы указываете как второй аргумент. Когда вы говорите "сгладить" ([xxxxxxxx.ga_sessions_20140829], хиты), вы сглаживаете только запись "хитов". Если вы также хотите сгладить повторяющиеся дети (продукт, продвижение по службе и т.д.), Вы должны явно добавить другую сглаживание для этих полей, например:

сплющить (сплющить ([xxxxxxxx.ga_sessions_20140829], попадает), hits.product)

-

У вас есть несколько вариантов, чтобы ваш пример работал:

1) Выберите меньшее количество полей. Если вы только заботитесь о выравнивании вывода нескольких полей, вы можете удалить независимо повторяющиеся поля из результатов запроса, явно выбрав только те поля, которые вам интересны.

2) Добавьте FLATTEN. Вам нужно будет сгладить каждое повторяющееся поле, которое будет включать, по крайней мере, хиты, hits.product и customDimensions. Вы можете обнаружить, что сообщение об ошибке будет жаловаться на разные повторяющиеся поля: добавьте еще FLATTENs в повторяющиеся поля в вашей схеме до тех пор, пока они не будут работать.

Ответ 2

Если вы используете веб-консоль BigQuery, выберите таблицу назначения, нажмите "Разрешить большие результаты" и снимите флажок "Сгладить результаты".

Если вы используете инструмент командной строки bq:

bq query --allow_large_results --noflatten --destination_table NAME_OF_TABLE  "SELECT * from FLATTEN( [dataset], hits)"

Ответ 3

Я подозреваю, что таблицы, созданные в Google Analytics Premium, были дополнены добавлением новых столбцов. Добавление столбцов не должно быть проблемой, если только ваши запросы не используют селектор *, а один из новых столбцов содержит вложенные значения.

Рекомендуемое решение: вместо использования * явно запросите нужные столбцы.

Ответ 4

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

SELECT * FROM 
flatten(flatten(flatten([xxxxxxxx.ga_sessions_20140829],hits),customDimensions_value),hits_product_productSKU),
flatten(flatten(flatten([xxxxxxxx.ga_sessions_20140828],hits),customDimensions_value),hits_product_productSKU),
flatten(flatten(flatten([xxxxxxxx.ga_sessions_20140827],hits),customDimensions_value),hits_product_productSKU),    flatten(flatten(flatten([xxxxxxxx.ga_sessions_20140826],hits),customDimensions_value),hits_product_productSKU)

Ответ 5

standardsql

Для тех, кто хочет импортировать данные GA в реляционную базу данных: Имейте в виду, что вложенная схема фактически помещает несколько реляционных таблиц в одну вложенную структуру - они эквивалентны, поэтому обычное выравнивание - это не лучшее решение здесь: вы хотите чтобы отделить таблицы снова! Таким образом, вы сохраняете много места в хранилище, и ваши запросы могут ускориться.

Вы можете думать, что данные GA являются 3 реляционными таблицами:

• таблица сеансов
• отображает таблицу
• таблица продуктов

Чтобы получить таблицу сеансов, вы можете использовать что-то вроде

-- session table 
SELECT
  * EXCEPT(hits,
    customDimensions),
  CONCAT('{',(
    SELECT
      STRING_AGG(CONCAT(CAST(index AS string),':"',value,'"') )
    FROM
      t.customdimensions),'}') as customDimensions
FROM
  `project.dataset.ga_sessions_20171031` AS t

customDimensions агрегируется в строку json.

Аналогично таблице обращений:

-- hits table
SELECT
 fullvisitorid,
 visitid,
 visitstarttime,
 h.* EXCEPT(product,
 customdimensions, customMetrics, customVariables, promotion, experiment),
 CONCAT('{',(
   SELECT
     STRING_AGG(CONCAT(CAST(index AS string),':"',value,'"') )
   FROM
     h.customdimensions),'}') AS hitsCustomDimensions,
 CONCAT('{',(
   SELECT STRING_AGG(CONCAT(CAST(index AS string),':',cast(value as string)) )
   FROM h.custommetrics),'}') AS hitsCustomMetrics
FROM
 `project.dataset.ga_sessions_20171031` AS t,
 t.hits AS h

Вы можете добавить продвижение и эксперимент, как я сделал с customDimensions, используя подвыбор.

Присоединяйтесь к сеансам с хитами, используя конкатенацию fullvisitorid + visitstarttime/visitid (сеансы GA включают полуночный раскол: используйте visitStartTime, а не visitid! Если вы хотите игнорировать разницу в полночь, используйте visitid - она ​​остается неизменной, несмотря на раскол)

Для продуктов вы должны добавить номер попадания в свой "идентификатор сеанса", чтобы получить уникальный идентификатор:

-- product table
SELECT
  fullvisitorid,
  visitid,
  visitstarttime,
  h.hitNumber,
  p.* EXCEPT(customdimensions, customMetrics),
  CONCAT('{',(
    SELECT STRING_AGG(CONCAT(CAST(index AS string),':"',value,'"') )
    FROM p.customdimensions),'}') AS productsCustomDimensions,
  CONCAT('{',(
    SELECT STRING_AGG(CONCAT(CAST(index AS string),':',cast(value as string)) )
    FROM p.custommetrics),'}') AS productsCustomMetrics
FROM
  `project.dataset.ga_sessions_20171031` AS t,
  t.hits AS h, h.product as p

Теперь у вас есть 3 реляционные/ "плоские" таблицы, которые вы можете присоединить по мере необходимости в любой реляционной db.