Standard SQL в Google BigQuery: преимущества и примеры использования в маркетинге

19
1226
Материалы для скачивания
590.48 Kb

В 2016 году в Google BigQuery появился новый способ общения с таблицами — Standard SQL. До этого времени у сервиса была собственная версия языка структурированных запросов — BigQuery SQL, которая сейчас называется Legacy.

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

В этой статье вы узнаете:

В чем преимущества Standard SQL перед Legacy SQL

Новые типы данных — массивы и вложенные поля

Стандартный SQL поддерживает новые типы данных — ARRAY и STRUCT (массивы и вложенные поля). Это означает, что в BigQuery стало проще работать с таблицами, загружаемыми из файлов JSON/Avro, данные в которых часто содержат многоуровневые вложения.

Вложенное поле — это мини-таблица внутри большой:

Вложенные поля

На приведенной схеме голубые и розовые полосы — это строки, в которые вложены мини-таблички. Каждая строка — это одна сессия. У сессии есть общие параметры: дата, уникальный номер, категория устройства пользователя, браузер, операционная система и т.д. Кроме общих параметров для каждой сессии, в строку вложена табличка hits.

В ней записаны параметры действий пользователя на сайте. К примеру, если пользователь кликнул по баннеру, полистал каталог, открыл карточку товара, положил товар в корзину или оформил заказ, то все эти действия попадают в таблицу hits.

Если пользователь в течение визита сделал заказ на сайте, в поле hits запишутся характеристики заказа:

  • transactionId — номер.
  • transactionRevenue — доход.
  • transactionShipping — стоимость доставки и др.

Похожую структуру имеют таблицы с данными о сессиях, собираемые с помощью OWOX BI.

Допустим, вы хотите узнать количество заказов от пользователей из Нью-Йорка за последний месяц. Для этого вам нужно посчитать количество уникальных transactionId, обратившись к полю hits. Чтобы добыть данные из таких полей, в Standard SQL есть функция UNNEST:

    
#standardSQL 
SELECT 
COUNT (DISTINCT hits.transaction.transactionId) --считаем количество уникальных номеров заказов, DISTINCT помогает избежать дублирования
FROM `project_name.dataset_name.owoxbi_sessions_*` --обращаемся к группе таблиц (wildcard tables)
WHERE 
  (
  _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(),   
INTERVAL 1 MONTHS)) --если мы не знаем, какие даты нам нужны, лучше пользоваться функциями FORMAT_DATE INTERVAL 
  AND
  FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) 
  ) 
AND geoNetwork.city = ‘New York’ --выбираем заказы, сделанные в Нью-Йорке
    

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

Больше вариантов подзапросов

Если у вас многоуровневые вложенные поля, чтобы извлечь из них данные, вы можете добавлять подзапросы в SELECT и WHERE. К примеру, в таблицах сессионного стриминга OWOX BI в подтаблицу hits записывается еще одна подтаблица — product. В ней собираются данные о продукте, которые передаются с массивом Enhanced Ecommerce. Если на сайте настроена расширенная электронная торговля и пользователь посмотрел карточку товара, в подтаблицу product запишутся характеристики этого товара.

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

    
SELECT 
  column_name1, --перечислите другие колонки, которые хотите получить
  column_name2,
  (SELECT productBrand FROM UNNEST(hits.product)) AS 
hits_product_productBrand,
  (SELECT productRevenue FROM UNNEST(hits.product)) AS 
hits_product_productRevenue, --перечислите характеристики продукта
  (SELECT localProductRevenue FROM UNNEST(hits.product)) AS 
hits_product_localProductRevenue,
  (SELECT productPrice FROM UNNEST(hits.product)) AS 
hits_product_productPrice,
FROM `project_name.dataset_name.owoxbi_sessions_YYYYMMDD`
    

Благодаря возможностям стандартного SQL проще выстроить логику запроса и написать код. Для сравнения — на Legacy SQL можно писать только вот такую «лесенку»:

    
SELECT 
  column_name1,
  column_name2, 
  column_name3 
FROM (
  SELECT table_name.some_column AS column1…
  FROM table_name
)
    

Запросы к внешним источникам

С помощью Standard SQL можно обращаться из BigQuery напрямую к таблицам Google Bigtable, Google Cloud Storage, Google Drive и Google Sheets.

То есть вместо того, чтобы загружать в BigQuery таблицу целиком, вы можете одним единственным запросом очистить данные, выбрать те параметры, которые вам нужны, и загрузить их в облачное хранилище.

Больше пользовательских функций (UDF)

Если вам нужно использовать формулу, которой нет в документации, вам помогут пользовательские функции (User Defined Functions). В нашей практике это очень редкий случай, поскольку документация Standard SQL покрывает почти все задачи digital-аналитики.

В стандартном диалекте пользовательские функции можно писать на SQL или JavaScript, а в Legacy поддерживается только JavaScript. В качестве аргументов функции используются колонки, а значения, которые она принимает — это результаты действий с колонками. На стандартном диалекте функции можно писать в том же окошке, что и запросы.

Больше условий в JOIN

В Legacy SQL в качестве условий JOIN можно задавать равенство или название колонок. Стандартный диалект поддерживает также JOIN по неравенству и по произвольному выражению.

Например, для выявления недобросовестных CPA-партнеров мы выбираем сессии, в которых была подмена источника в течение 60 секунд перед транзакцией. Для этого на стандартном диалекте можно добавить неравенство в условие JOIN:

    
#standardSQL
SELECT *
FROM 
  (
  SELECT
  traff.clientId AS clientId,
  traff.page_path AS pagePath,
  traff.traffic_source AS startSource,
  traff.traffic_medium AS startMedium,
  traff.time AS startTime,
  aff.evAction AS evAction,
  aff.evSource AS finishSource,
  aff.evMedium AS finishMedium,
  aff.evCampaign AS finishCampaign,
  aff.time AS finishTime,
  aff.isTransaction AS isTransaction,
  aff.pagePath AS link,
  traff.time-aff.time AS diff
  FROM
    (
    SELECT 
    fullVisitorID AS clientId,
    h.page.pagePath AS page_path,
    trafficSource.source AS traffic_source,
    trafficSource.medium AS traffic_medium,
    trafficSource.campaign AS traffic_campaign,
    date,
    SAFE_CAST(visitStartTime+h.time/1000 AS INT64) AS time
      FROM `demoproject.google_analytics_sample.ga_sessions_20190301`,
      UNNEST (hits) AS h
      WHERE trafficSource.medium != 'cpa'
      ) AS traff
JOIN (
  SELECT 
  total.date date,
  total.time time,
  total.clientId AS clientId,
  total.eventAction AS evAction,
  total.source AS evSource,
  total.medium AS evMedium,
  total.campaign AS evCampaign,
  tr.eventAction AS isTransaction,
  total.page_path AS pagePath
  FROM 
  (
  SELECT 
  fullVisitorID AS clientId,
  h.page.pagePath AS page_path,
  h.eventInfo.eventAction AS eventAction, 
  trafficSource.source AS source,
  trafficSource.medium AS medium,
  trafficSource.campaign AS campaign,
  date,
  SAFE_CAST(visitStartTime+h.time/1000 AS INT64) AS time
  FROM `demoproject.google_analytics_sample.ga_sessions_20190301`,
  UNNEST(hits) AS h
    WHERE
    trafficSource.medium ='cpa'
    ) AS total
LEFT JOIN 
  (
  SELECT
  fullVisitorID AS clientId,
  date,
  h.eventInfo.eventAction AS eventAction,
  h.page.pagePath pagePath,
  SAFE_CAST(visitStartTime+h.time/1000 AS INT64) AS time
  FROM `demoproject.google_analytics_sample.ga_sessions_20190301`,
  UNNEST(hits) AS h
  WHERE h.eventInfo.eventAction = 'typ_page'
  AND h.type = 'EVENT'
  GROUP BY 1, 2, 3, 4, 5
  ) AS tr
ON total.clientId=tr.clientId
AND total.date=tr.date
AND tr.time>total.time ---JOIN таблиц по неравенству. Обходим дополнительное условие WHERE, которое было нужно в Legacy-варианте запроса
WHERE tr.eventAction = 'typ_page'
  ) AS aff
ON traff.clientId = aff.clientId
)
WHERE diff> -60
AND diff<0
  GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13
  ORDER BY clientId, finishTime
    

Единственное ограничение стандартного диалекта в отношении JOIN в том, что он не разрешает «половинчатые джойны» (semi-join) с подзапросами вида WHERE column IN (SELECT...):

    
#legacySQL
SELECT
  mother_age,
  COUNT(mother_age) total
FROM
  [bigquery-public-data:samples.natality]
WHERE --такую конструкцию нельзя использовать на стандартном диалекте
  state IN (SELECT
              state
            FROM
              (SELECT
                 state,
                 COUNT(state) total
               FROM
                 [bigquery-public-data:samples.natality]
               GROUP BY
                 state
               ORDER BY
                 total DESC
               LIMIT 10))
  AND mother_age > 50
GROUP BY
  mother_age
ORDER BY
  mother_age DESC
    

Меньше шансов ошибиться

Некоторые функции в Legacy SQL возвращают NULL, если условие задано неверно. Например, если в ваши вычисления закралось деление на ноль, запрос выполнится, а в результирующих строках таблицы появятся записи NULL. Это может маскировать проблемы в запросе или в данных.

Логика стандартного SQL более прямолинейна. Если условие или входящие данные неверны, запрос выдаст ошибку, например «division by zero», и вы сможете быстро поправить текст запроса. В стандарт SQL встроены:

  • Проверка допустимых значений для +, −, ×, SUM, AVG, STDEV.
  • Проверка деления на ноль.

Запросы выполняются быстрее

Запросы с JOIN, написанные на стандартном SQL, выполняются быстрее, чем написанные на Legacy, благодаря предварительной фильтрации входящих данных. Сначала запрос выбирает строки, которые соответствуют условиям JOIN, а затем обрабатывает их.

В дальнейшем Google BigQuery будет работать над повышением скорости и производительности запросов только для Standard SQL.

Таблицы можно редактировать: вставлять и удалять строки, обновлять

Для стандартного диалекта доступны функции Data Manipulation Language (DML). Это означает, что обновлять таблицы, добавлять или удалять из них строки можно через то же окно, через которое вы пишете запросы. Например, с помощью DML можно объединить данные двух таблиц в одну так, чтобы они дополняли друг друга:

    
#standardSQL
MERGE dataset.Inventory AS T
USING dataset.NewArrivals AS S
ON T.ProductID = S.ProductID
WHEN MATCHED THEN
  UPDATE SET quantity = T.quantity + S.quantity
WHEN NOT MATCHED THEN
  INSERT (ProductID, quantity) VALUES (ProductID, quantity)
    

Код удобнее читать и править

Теперь сложные запросы можно начинать не только с SELECT, но и с WITH. Такой код проще читать, комментировать и понимать, что хотел сказать автор. А значит, проще предотвратить собственные и исправить чужие ошибки.

    
#standardSQL
WITH total_1 AS ( --первый подзапрос, в котором будет рассчитан промежуточный показатель 
    SELECT
        id,
        metric1,
       SUM(metric2) AS total_sum1
    FROM `project_name.dataset_name.owoxbi_sessions_YYYYMMDD`
    GROUP BY
        id, metric
),
total_2 AS ( --второй подзапрос
    SELECT
        id,
        metric1,
        SUM(metric2) AS total_sum2
    FROM `project_name.dataset_name.owoxbi_sessions_YYYYMMDD`
           GROUP BY
        id, metric1
),
total_3 AS ( --третий подзапрос 
    SELECT
        id,
        metric,
       SUM(metric2) AS total_sum3
       FROM `project_name.dataset_name.owoxbi_sessions_YYYYMMDD`
    GROUP BY
        id, metric
)
SELECT *,
ROUND(100*( total_2.total_sum2 - total_3.total_sum3) / total_3.total_sum3, 3) AS difference --получаем показатель difference: отнимаем сумму из второго подзапроса от суммы из третьего; делим на сумму из третьего 
FROM total_1
ORDER  BY 1, 2
    

С оператором WITH удобно работать, если у вас есть вычисления, которые делаются в несколько этапов. Сначала вы собираете в подзапросах промежуточные метрики, а затем делаете финальные расчеты.

Google Cloud Platform (GCP), в которую входит BigQuery — это платформа полного цикла работы с большими данными, от организации Data Warehouse или Data Cloud до научных экспериментов, предиктивной и прескриптивной аналитики. За счет внедрения стандартного SQL BigQuery расширяет свою аудиторию. Работать с GCP становится интереснее маркетинг-аналитикам, продуктовым аналитикам, Data Scientists и другим командам.

Возможности и примеры использования Standard SQL

Мы в OWOX BI часто работаем с таблицами, собранными при помощи стандартного экспорта Google Analytics 360 в Google BigQuery или через OWOX BI Pipeline. Поэтому в примерах ниже мы рассмотрим особенности SQL-запросов именно к таким данным.

Если вы еще не собираете данные с сайта в BigQuery, вы можете попробовать это бесплатно в trial-версии от OWOX BI.

ПОЛУЧИТЬ TRIAL

1. Выбрать данные за интервал времени

В Google BigQuery данные о поведении пользователей на сайте хранятся в wildcard tables (таблицах со звездочкой): за каждый день формируется отдельная таблица. Таблицы по дням называются одинаково — отличается только суффикс в имени. В суффикс записывается дата в формате ГГГГММДД. Например, в таблице owoxbi_sessions_20190301 лежат данные о сессиях за 1 марта 2019 года.

Мы можем в одном запросе обратиться сразу к группе таких таблиц, чтобы получить данные, к примеру, с 1 по 28 февраля 2019 года. Для этого в FROM нужно указать вместо ГГГГММДД значок *, а в WHERE — назвать, какие табличные суффиксы должны войти в интервал времени:

    
#standardSQL
SELECT sessionId, 
FROM `project_name.dataset_name.owoxbi_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN ’20190201′ AND ’20190228′
    

Не всегда конкретные даты, за которые мы хотим собрать данные, нам известны. Например, каждую неделю вам нужно анализировать данные за последние три месяца. Для этого воспользуемся функцией FORMAT_DATE:

    
#standardSQL
SELECT
 <перечисляем названия полей>
FROM `project_name.dataset_name.owoxbi_sessions_*`
WHERE 
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTHS))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    

После BETWEEN записан первый табличный суффикс. Формулировка CURRENT_DATE(), INTERVAL 3 MONTHS означает «выбери данные за последние 3 месяца от текущей даты». Второй табличный суффикс записан после AND. Он нужен, чтобы обозначить окончание интервала — вчерашний день: CURRENT_DATE(), INTERVAL 1 DAY.

2. Извлечь пользовательские параметры и показатели

Пользовательские параметры и показатели в таблицах Google Analytics Export записываются во вложенное поле hits, в подтаблицы сustomDimensions и customMetrics. Все custom dimensions записываются в две колонки: в одной — номера параметров, которые собираются на сайте, во второй — их значения. Вот как выглядят все параметры, которые передались с одним хитом:

Пользовательские параметры и показатели

Чтобы их «распаковать» и записать нужные параметры в отдельные колонки, мы пользуемся следующими формулировками в SQL-запросе:

    
-- Custom Dimensions (в строке ниже index -- номер пользовательской переменной, который задается в интерфейсе Google Analytics; dimension1 -- название кастомного параметра, его можно поменять, как вам удобно. Для каждого следующего параметра нужно прописывать аналогичную строку):

  (SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension1, 

  -- Custom Metrics (ниже index -- номер пользовательской метрики, задается в интерфейсе Google Analytics; metric1 -- название метрики, можно поменять, как вам удобно. Для каждой следующей метрики нужно прописывать аналогичную строку): 

  (SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metric1
    

Вот как это выглядит в запросе:

    
#standardSQL
SELECT <column name1>,
<column_name2>, -- перечисляем названия колонок
(SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customDimensions)) AS page_type,
(SELECT MAX(IF(index=2, value, NULL)) FROM UNNEST(hits.customDimensions)) AS visitor_type, -- добываем нужные custom dimensions
(SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metric1 -- добываем нужные custom metrics
<column_name3> -- если нужны еще колонки, продолжаем перечислять
FROM `project_name.dataset_name.owoxbi_sessions_20190201`
    

На скриншоте ниже мы выбрали параметры № 1 и № 2 из демо-данных экспорта Google Analytics 360 в Google BigQuery, назвали их page_type и client_id. Каждый параметр записался в отдельную колонку:

3. Посчитать количество сессий в разрезе источника трафика, канала, кампании, города и категории устройства

Такие расчеты полезны, если вы планируете визуализировать данные в Google Data Studio и пользоваться фильтрами по городам и категориям устройств. Это очень просто сделать с оконной функцией COUNT:

    
#standardSQL
SELECT
<column_name 1>, --выбираем любые нужные колонки 
COUNT (DISTINCT sessionId) AS total_sessions, --суммируем идентификаторы сессий, чтобы узнать общее количество сессий
COUNT(DISTINCT sessionId) OVER(PARTITION BY date, geoNetwork.city, session.device.deviceCategory, trafficSource.source, trafficSource.medium, trafficSource.campaign) AS part_sessions --суммируем количество сессий в разрезе: кампании, канала, источника трафика, города и категории устройства 
FROM `project_name.dataset_name.owoxbi_sessions_20190201`
    

4. Объединить одинаковые данные из нескольких таблиц

Предположим, вы собираете данные о выполненных заказах в нескольких таблицах BigQuery: в одной собираются все заказы из магазина А, в другой — из магазина B. Вы хотите сделать из них одну таблицу с такими колонками:

  • client_id — идентификатор покупателя, который сделал заказ.
  • transaction_created — время создания заказа в формате TIMESTAMP.
  • transaction_id — номер заказа.
  • is_approved — подтвержден ли заказ.
  • transaction_revenue — сумма заказа.

В таблицу должны попасть заказы с 1 января 2018 по вчерашний день. Для этого из каждой группы таблиц нужно выбрать подходящие колонки, присвоить им одинаковые имена и объединить результаты с помощью UNION ALL:

    
#standardSQL
SELECT 
cid AS client_id, 
order_time AS transaction_created,
order_status AS is_approved,
order_number AS transaction_id
FROM `project_name.dataset_name.table1_*`
WHERE (
_TABLE_SUFFIX BETWEEN ’20180101′
AND
FORMAT_DATE(’%Y%m%d’,DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
)

UNION ALL 
SELECT
userId AS client_id,
created_timestamp AS transaction_created,
operator_mark AS is_approved,
transactionId AS transaction_id
FROM `project_name.dataset_name.table1_*`
WHERE (
_TABLE_SUFFIX BETWEEN ’20180101′
AND
FORMAT_DATE(’%Y%m%d’,DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
)
ORDER BY transaction_created DESC
    

5. Создать словарь групп каналов трафика

Когда данные попадают в Google Analytics, система автоматически определяет группу, к которой относится тот или иной переход: Direct (прямой трафик), Organic Search, Paid Search и так далее. Для определения группы каналов Google Analytics «смотрит» на utm-метки переходов, а именно utm_source и utm_medium. Подробнее о группах каналов и о правилах определения, можно почитать в справке Google Analytics.

Если клиенты OWOX BI хотят присвоить собственные названия группам каналов, мы создаем для него словарь, куда какой переход относится. Для этого используем условный оператор CASE и функцию REGEXP_CONTAINS. Эта функция выбирает значения, в которых встречается заданное регулярное выражение.

Пример, как добавить такие условия в тело запроса (названия рекомендуем брать из вашего списка источников в GA):

    
#standardSQL
SELECT 
CASE 
WHEN (REGEXP_CONTAINS (source, 'yandex') AND medium = 'referral' THEN 'Organic Search' 
WHEN (REGEXP_CONTAINS (source, 'yandex.market')) AND medium = 'referral' THEN 'Referral'
WHEN (REGEXP_CONTAINS (source, '^(go.mail.ru|google.com)$') AND medium = 'referral') THEN 'Organic Search'
WHEN medium = 'organic' THEN 'Organic Search'
WHEN (medium = 'cpc') THEN 'Paid Search'
WHEN REGEXP_CONTAINS (medium, '^(sending|email|mail)$') THEN 'Email'
    WHEN REGEXP_CONTAINS (source, '(mail|email|Mail)') THEN 'Email'
    WHEN REGEXP_CONTAINS (medium, '^(cpa)$') THEN 'Affiliate'
    WHEN medium = 'social' THEN 'Social'
    WHEN source = '(direct)' THEN 'Direct'
 WHEN REGEXP_CONTAINS (medium, 'banner|cpm') THEN 'Display'
    ELSE 'Other'
  END channel_group -- как будет называться столбец, в который запишутся группы каналов
FROM `project_name.dataset_name.owoxbi_sessions_20190201`
    

Как перейти на Standard SQL

Если вы еще не перешли на стандартный SQL, то можете сделать это в любой момент. Главное — не смешивать диалекты в одном запросе.

Вариант 1. Переключатель в интерфейсе Google BigQuery

В старом интерфейсе BigQuery по умолчанию используется Legacy SQL. Для переключения между диалектами нажмите «Show Options» под полем ввода запроса и снимите галочку «Use Legacy SQL» возле пункта «SQL Dialect»:

Use Legacy SQL

В новом интерфейсе по умолчанию используется Standard SQL. Здесь переключение диалектов находится во вкладке «More»:

Вариант 2. Написать префикс в начале запроса

Если вы не поставили галочку в настройках запроса, можно начать его с нужного префикса (#standardSQL или #legacySQL):

    
#standardSQL
SELECT
  weight_pounds, state, year, gestation_weeks
FROM
  `bigquery-public-data.samples.natality`
ORDER BY weight_pounds DESC
LIMIT 10;
    

В этом случае Google BigQuery проигнорирует настройки в интерфейсе и запустит запрос так, как вы написали в префиксе.

Если у вас есть представления или сохраненные запросы, которые запускаются с помощью Apps Script по расписанию, не забудьте поменять в скрипте значение useLegacySql на false:

    
var job = {
configuration: {
  query: {
    query: 'INSERT INTO MyDataSet.MyFooBarTable (Id, Foo, Date) VALUES (1, \'bar\', current_Date);',
    useLegacySql: false
    }
    

Вариант 3. Переход на Standard SQL для представлений (Views)

Если вы работаете в Google BigQuery не с таблицами, а с представлениями, к ним нельзя обращаться на разных диалектах. То есть, если ваше представление написано на Legacy SQL, к нему нельзя писать запросы на Standard.

Чтобы перевести представление на стандартный SQL, нужно вручную переписать запрос, которым оно создано. Проще всего это сделать через интерфейс BigQuery.

1. Откройте нужное представление:

Представление в Google BigQuery

2. Нажмите «Details». Когда откроется текст запроса, внизу появится кнопка «Edit Query»:

Теперь запрос можно редактировать по правилам стандартного диалекта. Если вы планируете дальше пользоваться им как представлением, после того, как закончите редактировать, нажмите «Save View».

Совместимость, особенности синтаксиса, операторы, функции

Совместимость

Благодаря внедрению стандартного SQL, непосредственно из BigQuery можно обращаться к данным, которые хранятся в других сервисах:

  • Файлам логов в Google Cloud Storage.
  • Транзакционным записям в Google Bigtable.
  • Другим источникам.

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

Синтаксис запроса

Структура запроса в стандартном диалекте — почти такая же, как и в Legacy:

    
WITH <алиас запроса> AS  <выражение>
SELECT <поля или выражения, которые хотите получить в результате>
FROM <название таблицы или представления>,
  UNNEST <название вложенного поля, если в вашем запросе есть обращения к вложенным полям> AS <алиас вложенного поля> 
WHERE <условия, по которому нужно отфильтровать результат>
GROUP BY <колонки, по которым нужно сгруппировать результат> 
HAVING <условие, аналогичное WHERE для функций агрегирования>
JOIN <название таблицы или представления №2> ON <условия>  | USING <название колонок, по которым нужно объединить таблицы>
ORDER BY <по каким колонкам отсортировать результат> DESC | ASC
LIMIT <сколько строк вывести> OFFSET <сколько строк пропустить с начала>
    

Названия таблиц и View пишутся через точку и отбиваются обратным машинописным апострофом: `название_проекта.название_набора данных.название_таблицы`. Например: `bigquery-public-data.samples.natality`.

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

Особенности синтаксиса стандартного SQL:

  • Запятые нужны для перечисления полей в операторе SELECT.
  • Если вы используете оператор UNNEST после FROM <название таблицы>, перед UNNEST ставится запятая или JOIN.
  • Нельзя ставить запятую перед FROM.
  • Запятая между двумя запросами приравнивается к CROSS JOIN, поэтому будьте с ней осторожны.
  • JOIN можно делать не только по колонкам или равенствам, но и по произвольным выражениям и неравенствам.
  • Можно писать сложные подзапросы в любой части SQL-выражения (в SELECT, FROM, WHERE). На практике пока что нельзя использовать выражения вида WHERE column_name IN (SELECT...), как это работает в других базах данных.

Операторы

В стандартном SQL операторы определяют тип данных. Например, массив всегда записывается в стандартных скобках [ ]. Операторы используются для сравнения, поиска соответствий логическому выражению (NOT, OR, AND) и арифметических вычислений.

Функции

Standard SQL поддерживает больше функций, чем Legacy — от традиционных функций агрегирования (сумма, количество, минимум, максимум), математических, строковых и статистических функций до редких форматов — например, HyperLogLog++.

В стандартном диалекте больше функций для работы с датами и TIMESTAMP. Полный список функций приведен в справке Google. Чаще всего используются функции для работы с датами, строками, функции агрегирования и оконные.

1. Функции агрегирования

COUNT(DISTINCT column_name) считает количество уникальных значений в столбце. Например, нам нужно посчитать количество сессий, сделанных с мобильных устройств, за 1 марта 2019 года. Поскольку номер сессии может повторяться в разных строках, мы хотим посчитать только уникальные значения номера сессии:

    
#standardSQL
SELECT 
COUNT (DISTINCT sessionId) AS sessions
FROM  `project_name.dataset_name.owoxbi_sessions_20190301`
WHERE device.deviceCategory = 'mobile'
    

SUM (column_name) — сумма значений в колонке:

    
#standardSQL
SELECT 
SUM (hits.transaction.transactionRevenue) AS revenue
FROM  `project_name.dataset_name.owoxbi_sessions_20190301`,
UNNEST (hits) AS hits --распаковываем вложенное поле hits
WHERE device.deviceCategory = 'mobile'
    

MIN (column_name) | MAX (column_name) — минимальное и максимальное значение в колонке. Эти функции очень удобны для проверки разброса данных в таблице.

2. Оконные (аналитические) функции

Аналитические функции считают значения не по всей таблице, а по определенному «окну» — набору строк, который вам интересен. То есть, сделать внутри таблицы сегменты. Например, вы можете посчитать доход SUM (Revenue) не по всем строкам, а по городам, категориям устройств и так далее. Вы можете превратить в аналитическую функцию SUM, COUNT, AVG и другие функции агрегирования, если добавите к ним условие OVER (PARTITION BY column_name).

К примеру, вам нужно посчитать количество сессий в разрезе источников трафика, каналов и кампаний, городов и категорий устройств. В таком случае используйте выражение:

    
SELECT
        date,
        geoNetwork.city,
        t.device.deviceCategory,
        trafficSource.source,
        trafficSource.medium,
        trafficSource.campaign,
COUNT(DISTINCT sessionId) OVER(PARTITION BY date, geoNetwork.city, session.device.deviceCategory, trafficSource.source, trafficSource.medium, trafficSource.campaign) AS segmented_sessions
FROM  `project_name.dataset_name.owoxbi_sessions_20190301` t
    

OVER определяет окно, в котором будут производиться вычисления. PARTITION BY указывает, какие строки нужно сгруппировать для расчета. В некоторых функциях обязательно нужно указывать порядок группировки — ORDER BY.

Полный список оконных функций вы найдете в документации BigQuery.

3. Функции работы со строками

Они полезны, когда вам нужно изменить, отформатировать текст в строке, склеить значения колонок. Например, если вы хотите сформировать уникальный идентификатор сессии из данных стандартного экспорта Google Analytics 360. Рассмотрим самые популярные из них.

SUBSTR вырезает часть строки. В запросе эта функция записывается так: SUBSTR(string_name, 0,4). Первое число обозначает, сколько символов нужно пропустить с начала строки, а второе — сколько цифр вырезать. Например, у вас есть столбец date, куда записываются даты в формате STRING. При этом даты выглядят так: 20190103. Если вы хотите извлечь из этой строки год, вам поможет SUBSTR:

    
#standardSQL
SELECT
SUBSTR(date,0,4) AS year
FROM `project_name.dataset_name.owoxbi_sessions_20190301`
    

CONCAT (column_name, etc.) — склейка значений. Воспользуемся столбцом date из предыдущего примера. Предположим, вы хотите, чтобы все даты записывались так: 2019-03-01. Для этого используются две строковые функции: сначала вы вырезаете нужные куски строки с помощью SUBSTR, а затем склеиваете их через черточку:

    
#standardSQL
SELECT
CONCAT(SUBSTR(date,0,4),"-",SUBSTR(date,5,2),"-",SUBSTR(date,7,2)) AS date
FROM `project_name.dataset_name.owoxbi_sessions_20190301`
    

REGEXP_CONTAINS возвращает те значения столбцов, в которых встречается регулярное выражение:

    
#standardSQL
SELECT 
CASE
WHEN REGEXP_CONTAINS (medium, '^(sending|email|mail)$') THEN 'Email'
    WHEN REGEXP_CONTAINS (source, '(mail|email|Mail)') THEN 'Email'
    WHEN REGEXP_CONTAINS (medium, '^(cpa)$') THEN 'Affiliate'
ELSE 'Other'
END Channel_groups
FROM `project_name.dataset_name.owoxbi_sessions_20190301`
    

Эту функцию можно использовать и в SELECT, и в WHERE. Например, в WHERE по ней можно выбрать конкретные страницы:

    
WHERE REGEXP_CONTAINS(hits.page.pagePath, 'land[123]/|/product-order')
    

4. Функции работы с датами

Часто даты в таблицах записываются в формате STRING (строка). Если вы планируете визуализировать результаты в Google Data Studio, даты в таблице нужно перевести в формат DATE c помощью функции PARSE_DATE.

PARSE_DATE преобразует строку (STRING) вида 1900-01-01 в формат даты (DATE).
Если в ваших таблицах даты выглядят по-другому (например, 19000101 или 01_01_1900), сначала нужно привести их к указанному виду.

    
#standardSQL
SELECT 
PARSE_DATE('%Y-%m-%d', date)  AS date_new
FROM `project_name.dataset_name.owoxbi_sessions_20190301`
    

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

    
#standardSQL 
SELECT DATE_DIFF( 
PARSE_DATE('%Y%m%d', date1), PARSE_DATE('%Y%m%d', date2), DAY 
) days --превращаем строки date1, date2 в формат DATE, выбираем, в каких единицах показать разницу (DAY, WEEK, MONTH и т. д.)
FROM `project_name.dataset_name.owoxbi_sessions_20190301`
    

Если вы хотите узнать больше о перечисленных функциях, почитайте нашу статью «Обзор основных функций Google BigQuery — тренируемся писать запросы для маркетинг-анализа».

SQL-запросы для маркетинговых отчетов

Стандартный диалект позволяет бизнесу извлечь максимум информации из данных — от глубокой сегментации, технического аудита, анализа KPI маркетинга до выявления недобросовестных подрядчиков в CPA-сетях. Вот примеры бизнес-задач, в которых вам помогут SQL-запросы к данным, собранным в Google BigQuery.

1. ROPO-анализ: оцените вклад онлайн-кампаний в офлайн-продажи. Для этого нужно объединить данные о поведении пользователей в онлайне с данными из CRM, системы колл-трекинга, мобильного приложения.

Если в одной и второй базе есть ключ — общий параметр, который уникален для каждого пользователя (например, User ID), вы можете:

  • Проследить, какие пользователи заходили на сайт перед тем, как купить товар в магазине.
  • Как они вели себя на сайте.
  • Как долго принимали решение о покупке.
  • Какие кампании дают наибольший прирост офлайн-покупок.

2. Сделайте сегментацию клиентов по любому сочетанию параметров — от особенностей поведения на сайте (на какие страницы заходили, какие товары смотрели, сколько раз посещали сайт до покупки и т. д.) до номера карты лояльности, купленных товаров и т.д.

3. Узнайте, кто из CPA-партнеров недобросовестно работает и подменяет UTM-метки.

4. Анализируйте продвижение пользователя по воронке продаж.

Мы подготовили подборку запросов на стандартном диалекте SQL. Если вы уже собираете в Google BigQuery данные с вашего сайта, из рекламных источников и CRM-системы, то сможете использовать эти шаблоны для решения своих бизнес-задач. Просто замените в запросе название проекта, набора данных и таблицы в BigQuery на свои. В подборке вы получите 11 SQL-запросов. 

Запросы к данным, собранным с помощью стандартного экспорта из Google Analytics 360 в Google BigQuery:

  • Действия пользователей в разрезе любых параметров.
  • Статистика по ключевым действиям пользователей.
  • Выбор пользователей, которые просмотрели определенные страницы товаров.
  • Действия пользователей, которые купили определенный товар.
  • Настройка воронки с любыми необходимыми шагами.
  • Эффективность работы внутреннего поиска на сайте.

Запросы к данным, собранным в Google BigQuery с помощью OWOX BI:

  • Как изменялся атрибутированный расход по источникам и каналам.
  • Как средняя стоимость привлечения посетителя зависела от города.
  • Как ROAS по валовой прибыли зависел от источника и канала.
  • Как количество заказов в CRM зависело от способа оплаты и способа доставки.
  • Как среднее время доставки зависело от города.

Если у вас есть вопросы к данным Google BigQuery, ответов на которые вы не нашли в наших кейсах, напишите об этом в комментариях. Мы постараемся вам помочь.

Вас также могут заинтересовать