Что такое google bigquery
Что такое Google BigQuery и почему им стоит пользоваться
Большой объем данных требует широких возможностей для их хранения и обработки. Одним из наиболее полезных и востребованных сервисов в данной сфере является Google BigQuery. Что это за инструмент, какие его возможности и преимущества, с какими платформами его можно интегрировать?
Google BigQuery – что это?
BigQuery – это облачный сервис Google, предназначенный для работы с Big Data, запущен в 2011 году. Он предлагает онлайн-хранилище данных, позволяя надежно хранить и быстро обрабатывать большие массивы информации без необходимости задействовать для этих целей отдельный сервер.
Google BigQuery представляет собой PaaS-сервис («платформа как услуга»), который поддерживает большинство функций СУБД. Он входит в состав Google Cloud Platform, где есть еще несколько десятков приложений для анализа, хранения и вычисления данных.
По сути, BigQuery является облачной БД с неограниченным хранилищем и высокой скоростью обработки больших массивов данных. Он имеет обширный функционал, его пользователи могут оперативно загружать масштабный объем данных, хранить их в виде двумерных таблиц, обращаться к ним используя SQL-запросы, а также сохранять и выгружать их результаты.
Кроме того, возможности Google BigQuery можно расширить при помощи ряда сторонних инструментов. Например, интегрировав его с Google Таблицы, Microsoft Excel, QlikView, BIME Analytics, а также Microsoft Power BI.
Основные функции и возможности Google Big Query
Онлайн-сервис Google BigQuery поддерживает практически все основные функции СУБД, включая структурированное хранение данных, представления и табличные выражения, а также оконные функции. Среди инструментов сервиса имеются функции для работы с датами и строками, а еще для агрегирования данных.
Преимущества Google BigQuery
Облачная база данных Google BigQuery является более удобным и перспективным решением, по сравнению с традиционными СУБД. К числу ее основных преимуществ относятся:
Интеграции Google BigQuery
Онлайн-БД Google BigQuery можно интегрировать с рядом сторонних сервисов для расширения ее функционала. Например, довольно востребованными являются связки BigQuery с различными электронными таблицами, а также платформой Microsoft Power BI. В этом разделе статьи мы кратко расскажем о наиболее популярных из них.
Проще всего интегрировать с BigQuery сервис Google Таблицы – при помощи удобного коннектора от OWOX. Однако в Google Таблицы можно загружать данные только на рабочий лист, а объем одного документа ограничен 2 млн ячеек. Чуть сложнее проходит интеграция BigQuery и Excel, так как их коннектор требует ежемесячно обновлять ключ доступа. Также он позволяет загружать данные только в рабочий лист, а не в модель данных, из-за чего и здесь есть ограничения по объему информации.
Что касается интеграции облачной БД с сервисом QlikView, то для нее нужно создать аккаунт Google Client ID. При этом пользователи получат здесь обширный набор инструментов для визуализации данных. Кроме того, есть возможность для интеграции с Google BigQuery с сервисами BIME и Tableau: каждый из них обеспечивает достойную функциональность и имеет удобный коннектор.
Microsoft Power BI – это мощный профессиональный сервис для визуализации данных, интеграция с которым значительно увеличивает возможности Google BigQuery. Интегрировать их можно при помощи стандартного коннектора «из коробки», однако его возможности весьма ограничены. Лучше использовать для этих целей бесплатный драйвер Simba Drivers, который также подходит для связки BigQuery с электронными таблицами. Кроме того, подключить Microsoft Power BI можно при помощи R-коннектора, предварительно установив среду разработки RStudio.
Выводы
Итак, Google BigQuery – это мощная, удобная, функциональная и доступная по цене облачная база данных. С ее помощью можно загружать и всячески обрабатывать объемные массивы информации без необходимости аренды и администрирования сервера. Она поддерживает большинство ключевых опций современных СУБД, а также легко интегрируется со сторонними платформами для расширения ее функционала.
Google BigQuery для веб-аналитики
Интернет-маркетинг сегодня это огромное количество данных и цифр, и стандартных инструментов может быть недостаточно для их полного и глубокого анализа. В eLama.ru мы анализируем входящий трафик, эффективность многих маркетинговых каналов (контекстная и таргетированная реклама, email, выступления на конференциях и обучающие вебинары, соцсети, блог, PR), а также активность наших клиентов. Есть задачи, для которых нам не хватает возможностей Google Analytics, Яндекс.Метрики и Excel.
В таких случаях мы используем Google BigQuery — реляционную систему управления базами данных (СУБД), часть Google Cloud Platform, куда входит еще порядка 40-ка инструментов для вычисления, хранения и анализа данных.
В этом материале мы расскажем, как используем BigQuery в своей работе и расскажем в целом, какие возможности открывает инструмент.
Итак, рассмотрим одну типичную для нас задачу: определить эффективность обучающего вебинара. Для этого примера возьмем вебинар о ремаркетинге в Google AdWords, проведенный 28 марта. Нам нужно выяснить, сколько участников зарегистрировались в еЛаме после обучения и сколько из них подключили аккаунт AdWords.
Для этого в BigQuery мы сведем данные из трех источников:
Способы загрузки
Данные в BigQuery можно загружать с помощью:
Для обработки данных в BigQuery используется схожий с SQL собственный язык с очень высокой скоростью выполнения запросов.
Загрузка данных и выполнение запроса
Теперь опишем порядок действий и необходимый инструментарий для решения нашей задачи.
Интерфейс BigQuery, как и всего Cloud Platform, не доступен на русском языке, а рабочая среда выглядит так:
Рис. 1 Подготовка к загрузке данных: создание базы данных
Чтобы начать работу, задаем название проекта и базы данных (dataset). Остальные поля можно не редактировать.
Рис. 2 Создание базы данных
2.Дальше загружаем в BigQuery список пользователей, посетивших вебинар. Создадим таблицу в Google Sheets и импортируем ее в BigQuery с помощью бесплатного плагина для браузера OWOX BI BigQuery Reports.
Рис. 3 Загрузка таблицы с данными пользователей, зарегистрированных на вебинар
Таблица в нашем случае содержит данные о вебинаре, источнике перехода, информацию о пользователях, дату регистрации на вебинар и дату его проведения.
При импорте нужно указать имена нужного проекта и базы данных, название импортируемой таблицы и схему данных. Каждая колонка в нашей таблице соответствует определенному типу данных, который нужно указать. Их не так много, как в традиционных СУБД, и они интуитивно понятны. Названия колонок автоматически подставляются из первой строки таблицы Google Sheets.
Рис. 4 Загрузка данных через OWOX BI BigQuery Reports
3.Загружаем в BigQuery список пользователей еЛамы. Эти данные передаются в CSV-файле напрямую в BigQuery, так как Google Sheets не справляются с таким большим объемом данных:
Рис. 5 Загрузка данных о пользователях еЛамы в CSV-файле в интерфейсе BigQuery
Мы указываем имя и формат файла, название таблицы, куда будет загружаться информация. В схему данных заносим названия колонок и задаем некоторые настройки импорта: разделитель между колонками в импортируемом файле, количество первых строк, которые можно пропустить.
4.Настройка стриминга из Google Analytics. Для полного анализа нам нужно, чтобы в BigQuery хранились данные о хитах: просмотрах страниц и всех действиях пользователей, которые фиксирует Google Analytics. В данном примере нас интересуют события категории «BidderAdWords» (этой категорией мы обозначаем события, связанные с нашими инструментами для работы с AdWords) и «Указал логин», сигнализирующие о подключении пользователем аккаунта AdWords.
Cуществует инструмент, импортирующий данные из Google Analytics 360 (ранее Analytics Premium). Мы же используемOWOX BI Streaming. Для его настройки нужно установить на сайте дополнительные теги, и данные будут автоматически отправляться с фронтенда сайта на сервера BigQuery параллельно с отправкой данных на сервера Google Analytics.
5.Чтобы получить ответ на наш вопрос о регистрациях в еЛаме и подключении AdWords участниками вебинара, нужно выполнить такой запрос:
Так выглядит отправка запроса и результат в интерфейсе BigQuery:
Рис. 7 Построенный по нашему запросу отчет
В таблице представлены 32 пользователя, которые зарегистрировались в еЛаме после вебинара. Один из них спустя три часа после регистрации подключил себе аккаунт AdWords. Эту таблицу можно дополнить финансовыми показателями, например, пополнениями баланса еЛамы новыми клиентами. Для этого нужно загрузить в BigQuery таблицу с транзакциями и дополнить запрос еще одним JOIN.
Другие возможности BigQuery
BigQuery позволяет строить разнообразные отчеты любой сложности. Например, мы можем выяснить, на какую сумму клиенты еЛамы пополняли счет до посещения вебинара и сколько эти же клиенты заплатили после посещения вебинара за аналогичный период времени.
Можно составить список заинтересованных пользователей, например, тех, кто совершали какие-то действия на сайте, но так и не пополнили счет. И затем передать такой список в отдел продаж для звонков.
Еще одна возможность — создание списков ремаркетинга по определенным условиям. Например, мы можем выделить тех, кто подключил аккаунт AdWords, но так и не пополнил баланс. Написав и выполнив соответствующие запросы, мы получим список user_id, по которому можем создать аудиторию и использовать ее в рекламе.
Рис. 8 Создание аудитории ремаркетинга
user_id (здесь ID) — это пользовательский параметр в Google Analytics, который передается с каждым хитом.
В отличие от стандартного Google Analytics, BigQuery работает с полным объемом данных. Даже для небольших проектов Analytics сэмплирует данные, составляя обычные отчеты с периодом больше месяца. Думаю, многие видели такие предупреждения:
Рис. 9 Предупреждение в GA о сэмлировании данных
Под выборкой понимается выделение подмножества данных из трафика сайта для построения отчета. Такая методика часто используется в статистическом анализе: ее результаты близки к результатам анализа всех доступных сведений, но получаются с существенно меньшими затратами вычислительных ресурсов. Выборка ускоряет обработку данных, когда их объем настолько велик, что замедляет формирование отчета — этот процесс называется сэмплированием.
Для достоверных анализов, где используются конкретные user_id пользователей, сэмплирование неприемлемо. Стриминг всех данных из Google Analytics в BigQuery позволяет обойти это ограничение.
Также в BigQuery можно подключить инструменты визуализации данных, например,Tableau, QlikView и др. Они представляют информацию и изменения наглядно и обладают широким функционалом построения графических отчетов.
Мы хотели сравнить скорость обработки запросов в BigQuery и в MySQL на обычном хостинге. Но эксперимент потерпел неудачу. Мы сделали несколько попыток загрузить CSV-файл в MySQL, и каждый раз импорт прерывался из-за погрешностей, например, лишних кавычек в полях с данными. BigQuery корректно обрабатывает подобные ошибки. Кроме того, на мой взгляд, система MySQL сложнее в освоении, чем BigQuery, в ее использовании больше технических нюансов, и для нее нет готовых решений по стримингу данных из Google Analytics.
Заключение
Google BigQuery — универсальный инструмент для аналитики. Он несложный и интуитивно понятный в использовании. Поэтому, если вы подозреваете, что для необходимого анализа вам мало возможностей Analytics и Метрики, начинайте разбираться с BigQuery.
В чем сила Google BigQuery: как маркетологу извлечь максимум из данных
В своей работе я нередко сталкиваюсь с тем, что маркетологи упираются в ограничения Google Analytics и от этого страдает точность и качество их отчетов. При этом обойти эти ограничения можно, оставаясь в инфраструктуре Google. В статье расскажу, как с помощью Google BigQuery решить эту проблему и получить максимум от данных.
Анализ в маркетинге всегда начинается с формулировки конкретных вопросов и целей. Например, понять, какие процессы происходят в бизнесе, какая динамика его развития, какие каналы приносят деньги, а какие не стоят вложений.
Если на этапе подготовки отчета вы что-то упустили из виду или не учли, отчет уже нельзя будет назвать точным и эффективным. Как результат — риск принять неправильное решение, потерять деньги и, что даже более важно, время.
Для сбора данных из рекламных сервисов и построения отчетов маркетологи часто используют Google Analytics. Основная версия этого сервиса — бесплатна. Кроме того, сервис — часть инфраструктуры Google, что объясняет легкость интеграции с его другими продуктами.
Тем не менее, в Google Analytics существует ряд ограничений, которые не позволяют извлечь из данных максимум — то есть можно оценить лишь часть общей картины. Например:
В итоге, несмотря на то, что с помощью Google Analytics действительно можно собрать данные о рекламе и даже построить отчеты, многое остается «за кадром». Например, можно решить отключить рекламу, которая, согласно отчету, приносит мало конверсий. Но если данных недостаточно (например, вы не учли ROPO-продажи), действительно ли проблема в этом канале можно будет понять только со временем.
Чтобы обойти все эти ограничения, аналитики часто выбирают Google BigQuery, который обеспечивает свободу действий и гибкость в использовании. Это система управления базами данных и часть Google Cloud Platform, куда входит еще более 40 инструментов для работы с данными.
Основная ценность отчетов, построенных на данных GBQ состоит в их скорости и гибкости. Таким отчетам можно доверять. Если, конечно, настроить качественную передачу данных из всех необходимых источников — но и это BigQuery позволяет сделать.
Для наглядности, можно рассмотреть основные преимущества BigQuery в сравнении с Google Analytics:
Представьте себе картину – все данные о поведении пользователя с сайта, мобильного приложения, CRM, рекламных сервисов и многих других источников объединены вместе. Вы видите полную картину и строите абсолютно любые отчеты – performance-отчет, ROPO-отчет и что угодно другое. Приятно, не правда ли. Так вот все это можно сделать в Google BigQuery.
И если аналитикам Google BigQuery уже давно знаком, то маркетологам он часто кажется сложным. На самом деле, существует много инструментов, которые не только соберут данные в Google BigQuery, но еще и упорядочат и очистят их. Более того, этот процесс можно автоматизировать, что сократит время настройки до минимума. В своей предыдущей статье я рассказывал о 10 инструментах для маркетинговой отчетности. Вы можете выбрать тот из них, который интегрируется с нужными вам источниками и оптимален в соотношении цены и функционала. Например, для своих проектов я использую OWOX BI, несмотря на то, что у Funnel больше возможных интеграций. Я выбираю достаточный функционал и не хочу переплачивать за фичи, которыми не буду пользоваться.
Чтобы настроить импорт рекламных расходов в Google BigQuery, я просто выбираю рекламный сервис (например, Facebook, ВКонтакте, MyTarget, Яндекс.Маркет) в интерфейсе OWOX BI и даю нужные доступы. Таким же образом собираю сырые данные с сайта и, если надо, из мобильного приложения. На самом деле, путь несколько сложнее, но у сервиса есть подробная документация и, пройдя этот путь единожды, сможете делать дальше с закрытыми глазами.
На этом этапе начинается настоящая магия возможностей с Google BigQuery. В первую очередь, Google BigQuery теперь служит источником данных для отчета. А дальше можно фантазировать как угодно и строить все необходимые отчеты с любыми параметрами. Единственное ограничение — под разные отчеты надо создавать отдельные наборы данных, для которых часто нужна помощь или аналитика, или того же инструмента, с помощью которого мы передавали данные.
Имея на руках полные и структурированные данные, гораздо легче извлекать из них ценные инсайты. С этим помогут сервисы отчетности и визуализации, которые можно подобрать под любые потребности и бюджет. Собственно, еще одно преимущество BigQuery, которое облегчает жизнь маркетологам — данные оттуда можно легко передавать в другие продукты.
Самые популярные бесплатные сервисы – Google Data Studio и Google Sheets. Data Studio известен благодаря огромной галерее шаблонов, что позволяет здорово сэкономить время и визуализировать отчеты. В ситуации, если у вас нет аналитика и вам не нужны сложные когортные отчеты, то вы просто идете в галерею Data Studio, выбираете нужный отчет и подключаете данные из Google BigQuery.
Вряд ли найдутся маркетологи, которые бы не использовали о Google Sheets. Этот инструмент позволяет создавать диаграмы, визуализации и даже интерактивные отчеты. Данные из BigQuery можно бесплатно передавать в Google Sheets, например, с помощью аддона. Например, у меня таким образом настроены отчеты по активности пользователей, по сведению плановых и фактических расходов.
Эффективные маркетинговые отчеты позволяют понять, как повысить доход и на какие слабые места надо обратить внимание. Но для того, чтобы отчетам действительно можно было доверять, надо быть уверенным в качестве, точности и полноте данных.
Последних три пункта может обеспечить Google BigQuery. Также он позволяет хранить данные в удобной структуре и легко передавать их в другие продукты.
Если между маркетологом и данными не стоит аналитик, то у маркетолога есть постоянный доступ к данным в понятной форме. Так он может быстро найти и протестировать новые подходы, а также быть уверенными в объективности полученных результатов. Время — настоящая ценность в мире маркетинга. Чем раньше найти сильные и слабые места в маркетинговой стратегии, тем больше у компании преимуществ перед конкурентами.
Обзор основных функций Google BigQuery и примеры запросов для маркетинг-анализа
Google BigQuery – это быстрое, экономичное и масштабируемое хранилище для работы с Big Data, которое вы можете использовать, если у вас нет возможности или желания содержать собственные серверы. В нем можно писать запросы с помощью SQL-like синтаксиса, стандартных и пользовательских функций (User-defined function).
В статье я расскажу про основные функции BigQuery и покажу их возможности на конкретных примерах. Вы сможете писать базовые запросы, и опробовать их на demo данных.
Что такое SQL и какие у него диалекты
SQL (Structured Query Language) — язык структурированных запросов для работы с базами данных. С его помощью можно получать, добавлять в базу и изменять большие массивы данных. Google BigQuery поддерживает два диалекта: Standard SQL и устаревший Legacy SQL.
Какой диалект выбрать, зависит от ваших предпочтений, но Google рекомендует использовать Standard SQL из-за ряда преимуществ:
По умолчанию запросы в Google BigQuery запускаются на Legacy SQL.
Переключиться на Standard SQL можно несколькими способами:
С чего начать
Чтобы вы смогли потренироваться запускать запросы параллельно с чтением статьи, я подготовила для вас таблицу с demo-данными. Загрузите данные из таблицы в ваш проект Google BigQuery.
Если у вас еще нет проекта в GBQ, создайте его. Для этого понадобится активный биллинг-аккаунт в Google Cloud Platform. Понадобится привязать карту, но без вашего ведома деньги с нее списываться не будут, к тому же при регистрации вы получите 300$ на 12 месяцев, который сможете потратить на хранение и обработку данных.
Функции Google BigQuery
При построении запросов чаще всего используются следующие группы функций: Aggregate function, Date function, String function и Window function. Теперь подробнее о каждой из них.
Функции агрегирования данных (Aggregate function)
Функции агрегирования позволяют получить сводные значения по всей таблице. Например, рассчитать средний чек, суммарный доход за месяц или выделить сегмент пользователей, совершивших максимальное количество покупок.
Вот самые популярные функции из этого раздела:
| Legacy SQL | Standard SQL | Что делает функция |
|---|---|---|
| AVG(field) | AVG([DISTINCT] (field)) | Возвращает среднее значение столбца field.В Standard SQL при добавлении условия DISTINCT среднее считается только для строк с уникальными (не повторяющимися) значениями из столбца field |
| MAX(field) | MAX(field) | Возвращает максимальное значение из столбца field |
| MIN(field) | MIN(field) | Возвращает минимальное значение из столбца field |
| SUM(field) | SUM(field) | Возвращает сумму значений из столбца field |
| COUNT(field) | COUNT(field) | Возвращает количество строк в столбце field |
| EXACT_COUNT_DISTINCT(field) | COUNT([DISTINCT] (field)) | Возвращает количество уникальных строк в столбце field |
С перечнем всех функций вы можете ознакомиться в справке: Legacy SQL и Standard SQL.
Давайте посмотрим на примере demo данных, как работают перечисленные функции. Вычислим средний доход по транзакциям, покупки с наибольшей и наименьшей суммой, общий доход и количество всех транзакций. Чтобы проверить, не дублируются ли покупки, рассчитаем также количество уникальных транзакций. Для этого пишем запрос, в котором указываем название своего Google BigQuery проекта, набора данных и таблицы.
В итоге получаем такие результаты:
Проверить результаты расчетов вы можете в исходной таблице с demo данными, используя стандартные функции Google Sheets (SUM, AVG и другие) или сводные таблицы.
Как видим из скриншота выше, количество транзакций и уникальных транзакций отличается.
Это говорит о том, что в нашей таблице есть 2 транзакции, у которых дублируется transactionId:
Поэтому, если вас интересуют именно уникальные транзакции, используйте функцию, которая считает уникальные строки. Либо вы можете сгруппировать данные с помощью выражения GROUP BY, чтобы избавиться от дублей перед тем, как применять функцию агрегации.
Функции для работы с датами (Date function)
Эти функции позволяют обрабатывать даты: менять их формат, выбирать необходимую часть (день, месяц или год), смещать дату на определенный интервал.
Они могут вам пригодится в следующих случаях:
| Legacy SQL | Standard SQL | Что делает функция |
|---|---|---|
| CURRENT_DATE() | CURRENT_DATE() | Возвращает текущую дату в формате %ГГГГ-%ММ-%ДД |
| DATE(timestamp) | DATE(timestamp) | Преобразует дату из формата %ГГГГ-%ММ-%ДД %Ч:%M:%С. в формат %ГГГГ-%ММ-%ДД |
| DATE_ADD(timestamp, interval, interval_units) | DATE_ADD(timestamp, INTERVAL interval interval_units) | Возвращает дату timestamp, увеличивая ее на указанный интервал interval.interval_units. |
В Legacy SQL может принимать значения YEAR, MONTH, DAY, HOUR, MINUTE и SECOND, а в Standard SQL — YEAR, QUARTER, MONTH, WEEK, DAY
В Legacy SQL возвращает разницу в днях, а в Standard SQL — в зависимости от указанного значения date_part (день, неделя, месяц, квартал, год)
Список всех функций – в справке: Legacy SQL и Standard SQL.
Рассмотрим на demo данных, как работает каждая из приведенных функций. К примеру, получим текущую дату, приведем дату из исходной таблицы в формат %ГГГГ-%ММ-%ДД, отнимем и прибавим к ней по одному дню. Затем рассчитаем разницу между текущей датой и датой из исходной таблицы и разобьем текущую дату отдельно на год, месяц и день. Для этого вы можете скопировать примеры запросов ниже и заменить в них название проекта, набора данных и таблицы с данными на свои.
После применения запроса вы получите вот такой отчет:
Функции для работы со строками (String function)
Строковые функции позволяют формировать строку, выделять и заменять подстроки, рассчитывать длину строки и порядковый номер индекса подстроки в исходной строке.
Например, с их помощью можно:
| Legacy SQL | Standard SQL | Что делает функция |
|---|---|---|
| CONCAT(‘str1’, ‘str2’) или’str1’+ ‘str2’ | CONCAT(‘str1’, ‘str2’) | Объединяет несколько строк ‘str1’ и ‘str2’ в одну |
| ‘str1’ CONTAINS ‘str2’ | REGEXP_CONTAINS(‘str1’, ‘str2’) или ‘str1’ LIKE ‘%str2%’ | Возвращает true если строка ‘str1’ содержит строку ‘str2’. В Standard SQL строка ‘str2’ может быть записана в виде регулярного выражения с использованием библиотеки re2 |
| LENGTH(‘str’ ) | CHAR_LENGTH(‘str’ ) или CHARACTER_LENGTH(‘str’ ) | Возвращает длину строки ‘str’ (количество символов в строке) |
| SUBSTR(‘str’, index [, max_len]) | SUBSTR(‘str’, index [, max_len]) | Возвращает подстроку длиной max_len, начиная с символа с индексом index из строки ‘str’ |
| LOWER(‘str’) | LOWER(‘str’) | Приводит все символы строки ‘str’ к нижнему регистру |
| UPPER(str) | UPPER(str) | Приводит все символы строки ‘str’ к верхнему регистру |
| INSTR(‘str1’, ‘str2’) | STRPOS(‘str1’, ‘str2’) | Возвращает индекс первого вхождения строки ‘str2’ в строку ‘str1’, иначе — 0 |
| REPLACE(‘str1’, ‘str2’, ‘str3’) | REPLACE(‘str1’, ‘str2’, ‘str3’) | Заменяет в строке ‘str1’ подстроку ‘str2’ на подстроку ‘str3’ |
Разберем на примере demo данных, как использовать описанные функции. Предположим, у нас есть 3 отдельных столбца, которые содержат значения дня, месяца и года:
Работать с датой в таком формате не очень удобно, поэтому объединим ее в один столбец. Чтобы сделать это, используйте SQL-запросы, приведенные ниже, и не забудьте подставить в них название своего проекта, набора данных и таблицы в Google BigQuery.
После выполнения запроса мы получим дату в одном столбце:
Часто при загрузке определенной страницы на сайте в URL записываются значения переменных, которые выбрал пользователь. Это может быть способ оплаты или доставки, номер транзакции, индекс физического магазина, в котором покупатель хочет забрать товар и т. д. С помощью SQL-запроса можно выделить эти параметры из адреса страницы.
Рассмотрим два примера, как и зачем это делать.
Пример 1. Предположим, мы хотим узнать количество покупок, при которых пользователи забирают товар из физических магазинов. Для этого нужно посчитать количество транзакций, отправленных со страниц, в URL которых есть подстрока shop_id (индекс физического магазина). Делаем это с помощью следующих запросов:
Из полученной в результате таблицы мы видим, что со страниц, содержащих shop_id, отправлено 5502 транзакции (check = true):
Пример 2. Допустим, вы присвоили каждому способу доставки свой delivery_id и прописываете значение этого параметра в URL страницы. Чтобы узнать, какой способ доставки выбрал пользователь, нужно выделить delivery_id в отдельный столбец.
Используем для этого следующие запросы:
В результате получаем в Google BigQuery такую таблицу:
Функции для работы с подмножествами данных или оконные функции (Window function)
Эти функции похожи на функции агрегирования, которые мы рассмотрели выше. Их основное отличие заключается в том, что вычисления проводятся не на всем множестве данных, выбранных с помощью запроса, а на его части — подмножестве или окне.
С помощью оконных функций вы можете агрегировать данные в разрезе групп, не используя оператор JOIN для объединения нескольких запросов. Например, рассчитать средний доход в разрезе рекламных кампаний, количество транзакций в разрезе устройств. Добавив еще одно поле в отчет, вы сможете легко узнать, к примеру, долю дохода от рекламной кампании на Black Friday или долю транзакций, сделанных из мобильного приложения.
Вместе с каждой функцией в запросе необходимо прописывать выражение OVER, которое определяет границы окна. OVER содержит 3 компоненты, с которыми вы можете работать:
| Legacy SQL | Standard SQL | Что делает функция |
|---|---|---|
| AVG(field) COUNT(field) COUNT(DISTINCT field) MAX() MIN() SUM() | AVG([DISTINCT] (field)) COUNT(field) COUNT([DISTINCT] (field)) MAX(field) MIN(field) SUM(field) | Возвращает среднее значение, количество, максимальное, минимальное и суммарное значение из столбца field в рамках выбранного подмножества. |
DISTINCT используется, если нужно посчитать только уникальные (неповторяющиеся) значения
В Standard SQL строка ‘str2’ может быть записана в виде регулярного выражения с использованием библиотеки re2
По умолчанию строки с пустыми значениями из столбца field включаются в расчет. RESPECT или IGNORE NULLS определяет, включать или игнорировать строки со значением NULL
По умолчанию строки с пустыми значениями из столбца field включаются в расчет. RESPECT или IGNORE NULLS определяет, включать или игнорировать строки со значением NULL
Offset определяет количество строк, на которое необходимо смещаться вниз по отношению к текущей строке. Является целым числом.
Default_expression — значение, которое будет возвращать функция, если в рамках подмножества нет необходимой строки
Offset определяет количество строк, на которое необходимо смещаться вверх по отношению к текущей строке. Является целым числом.
Default_expression — значение, которое будет возвращать функция, если в рамках текущего подмножества нет необходимой строки
Список всех функций вы можете посмотреть в справке для Legacy SQL и для Standard SQL: Aggregate Analytic Functions, Navigation Functions.
Пример 1. Допустим, мы хотим проанализировать активность покупателей в рабочее и нерабочее время. Для этого необходимо разделить транзакции на 2 группы и рассчитать интересующие нас метрики:
| Подмножество (окно) | clientId | DayTime |
|---|---|---|
| 1 окно | clientId 1 | Рабочее время |
| 2 окно | clientId 2 | Нерабочее время |
| 3 окно | clientId 3 | Рабочее время |
| 4 окно | clientId 4 | Нерабочее время |
| N окно | clientId N | Рабочее время |
| N+1 окно | clientId N+1 | Нерабочее время |
Давайте на demo данных рассчитаем средний, максимальный, минимальный, и суммарный доход, количество транзакций и количество уникальных транзакций по каждому пользователю в рабочее и нерабочее время. Сделать это нам помогут запросы, приведенные ниже.
Посмотрим, что получилось в результате, на примере одного из пользователей с clientId=’102041117.1428132012′. В исходной таблице по этому пользователю у нас были следующие данные:
Применив запрос, мы получили отчет, который содержит средний, минимальный, максимальный и суммарный доход с этого пользователя, а также количество транзакций. Как видно на скриншоте ниже, обе транзакции пользователь совершил в рабочее время:
Пример 2. Теперь немного усложним задачу:
Результаты расчетов проверим на примере уже знакомого нам пользователя с clientId=’102041117.1428132012′:
Из скриншота выше мы видим, что:
Выводы
В этой статье мы рассмотрели самые популярные функции из разделов Aggregate function, Date function, String function, Window function. Однако в Google BigQuery есть еще много полезных функций, например: