Использование функции query в Гугл Таблицах

Данная шпаргалка создана на основе материалов:

Справка по языку запросов API визуализации Google
Подробнейшее руководство от Netpeak (в этой статье есть практические задания)
Форматирование чисел и дат в query
Небольшая статья по query
Статьи по Гугл Докс

Возможные сложности в работе

1. Попробовал сделать себе базу данных, которая содержит 240 тысяч строк и 10 столбцов с данными — хром завис и закрылся. Скорее всего, всё зависит от мощности компа, но нужно учесть, что большие объемы хром может и не потянуть.
2. При обработке большого количества данных на разных вкладках — query может долго прогружаться. Скорость зависит от интернета и мощности компа.

Синтаксис QUERY

=QUERY(данные; запрос; [заголовки])

Данные — диапазон ячеек, для которого нужно выполнить запрос.
Запрос — запрос на выполнение, записанный на языке запросов API визуализации Google (упрощенный вариант SQL-запросов).

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

Заголовки (необязательный параметр) — указываем сколько первых строк будет считаться заголовками. В случае, если параметр опущен или равен -1, его значение вычисляется автоматически в зависимости от содержимого данных.

Примеры запросов

=query(DB!$A$1:$L$1143;
"select * limit 10")

На примере выше мы выбираем данные из указанного диапазона и в запросе указываем, что хотим получить все столбцы. «Limit» означает, что будет выведено всего 10 первых строк.

=QUERY(data!$A:$I;
"select A,sum(D)
where (B contains 'YRSY' or B contains 'YSEA')
group by A")

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

1. Выбрать столбец A.
2. Суммировать данные из столбца D.
3. Берём только данные, которые содержат «YRSY» или «YSEA» в столбце B.
4. Группировать по столбцу A.

Если в последнюю формулу не добавить группировку, то вылетит ошибка.

В большинстве случаев подобные «подсказки» дают нам представление о том, что пошло не так и где это нужно исправить.

Выбираем данные с помощью кляузы SELECT

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

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

В первой части формулы мы выбираем диапазон с данными.

Во второй части мы в кавычках уже начинаем писать запрос. Все запросы начинаются с кляузы select.

Если в файле для тренировки вы напишите формулу, то будут выведен список всех РК.

=query(data!$A:$I;"select B")

В кляузе select мы перечисляем столбцы, которые нужно выбрать и сделать с ними что-то — либо вывести всё, либо отфильтровать, либо суммировать, либо разделить и т.д.

Таким образом выглядит часть данных, которые находятся в файле для тестов.

Если мы применим запрос, который указан выше, то будут выведены все значения из столбца B.

Фильтруем данные с помощью WHERE

С помощью where можно фильтровать данные, которые содержит определенный столбец.

Например,

=QUERY(data!$A:$I;
"select A,sum(D)
where (B contains 'YRSY' or B contains 'YSEA')
group by A")

В данном случае, с помощью where мы фильтруем кампании, которые содержат «YRSY» или «YSEA».

Кляуза WHERE поддерживает логические операторы OR и AND и определенные ключевые слова с помощью которых можно фильтроваться.

Операторы =, <, > и != можно применять в том числе для строк. Но рекомендую использовать только = и !=, так как в случае с операторами «больше” или «меньше» — поведение не очень предсказуемое.

Сортируем с помощью ORDER BY

Для того, чтобы отсортировать данные по убыванию или возрастанию, то нужно добавить кляузу order by.

По умолчанию — порядок asc, то есть по возрастанию. Если укажете после названия поля параметр desc, запрос вернет результат в порядке убывания указанных в кляузе Order by полей.

Вот так мы выводим 5 самых конверсионных кампаний за весь период:

=query(data!$A:$I;
"select B, sum(G)
group by B
order by sum(G)
desc
limit 5")

Результат выглядит вот так:

Агрегирующие функции, группировка данных и переименование столбцов (Group by, Label)

Агрегирующие функции

В примерах выше очень часто используется функция sum(), которая отвечает за суммирование данных из указанного столбца.

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

Группировка данных с помощью GROUP BY

В случаях, когда используются агрегирующие функции, то указание group by обязательно. Иначе гугл выдаст ошибку.

=query(data!$A:$I;
"select B, sum(G)")

Обычно в ошибке указывается, что пошло не так. Если добавим группировку по кампаниям, то всё будет ок.

=query(data!$A:$I;
"select B, sum(G) group by B")

Переименование заголовков с помощью LABEL

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

Мы это можем сделать с помощью следующей формулы:

=query(data!$A:$I;
"select B, sum(D), sum(E), sum(E)/sum(D), sum(F), sum(G), sum(F)/sum(G)
group by B
order by sum(F)/sum(G)
desc")

В перечислении в кляузе select мы показываем какие столбцы хотим видеть. Здесь можно использовать, в том числе и арифметические операторы.

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

В случае со столбцом B, query взял в качестве заголовка 1 строку из нашей базы данных, где было указано «Название размещения». По остальным столбцам в заголовках добавилось указание первой строки из БД + название арифметической операции, которая использовалась для данного столбца.

Для того, чтобы это исправить и сделать более красиво, используется кляуза label.

Вот как выглядит формула с использованием этой кляузы:

=query(data!$A:$I;
"select B, sum(D), sum(E), sum(E)/sum(D), sum(F), sum(G), sum(F)/sum(G)
group by B order by sum(F)/sum(G)
desc
label B 'Кампания', sum(D) 'Показы', sum(E) 'Клики', sum(E)/sum(D) 'CTR, %', sum(F) 'Расход (без НДС)', sum(G) 'Конверсии', sum(F)/sum(G) 'CPA'
")

После слова label мы начинаем перечислять столбцы, которые указывали в «селекте» и задавать им значения, которые нам нужны.

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

Вот так теперь выглядят заголовки таблицы после использования label:

Форматирование с помощью кляузы Format

Форматировать данные можно с помощью двух способов:

1. Привычные методы Гугл таблиц.
2. С помощью кляузы Format.

На примере ниже видно, что в столбце, где мы рассчитали CTR, нужно данные форматировать в проценты:

И делаем мы это либо с помощью волшебной кнопки.

Либо меняем формулу из предыдущего примера на следующую:

=query(data!$A:$I;
"select B, sum(D), sum(E), sum(E)/sum(D), sum(F), sum(G), sum(F)/sum(G)
group by B order by sum(F)/sum(G)
desc
label B 'Кампания', sum(D) 'Показы', sum(E) 'Клики', sum(E)/sum(D) 'CTR, %', sum(F) 'Расход (без НДС)', sum(G) 'Конверсии', sum(F)/sum(G) 'CPA'
format sum(E)/sum(D) '0.00%'
")

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

format sum(E)/sum(D) '0.00%'

Аналогичные операции можно сделать и для других столбцов.

И итоговая формула будет выглядеть у нас следующим образом:

=query(data!$A:$I;
"select B, sum(D), sum(E), sum(E)/sum(D), sum(F), sum(G), sum(F)/sum(G)

group by B order by sum(F)/sum(G) desc

label B 'Кампания', sum(D) 'Показы', sum(E) 'Клики', sum(E)/sum(D) 'CTR, %', sum(F) 'Расход (без НДС)', sum(G) 'Конверсии', sum(F)/sum(G) 'CPA'

format sum(D) '### ### ###', sum(E) '### ### ###', sum(E)/sum(D) '0.00%', sum(F) '### ### ### руб', sum(G) '### ### ###0', sum(F)/sum(G) '##,## руб'
")

Теперь выглядит всё еще лучше:

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

Форматирование даты

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

Сделать мы это можем с помощью следующей формулы:

=query(data!$A:$I;
"select dayofweek(A), sum(G)
where B != ''
group by dayofweek(A) label dayofweek(A) 'День недели', sum(G) 'Конверсии'")

В итоге, получаем простую таблицу, где складываются все конверсии.

Неделя в данном случае начинается с воскресенья. Соответственно, у воскресенья порядковый номер = 1, а у субботы = 7.

Чтобы не запутать себя и окружающих, будет лучше отформатировать дни недели и привести их в привычный вид. Для этого в формулу нужно добавить кляузу format.

format dayofweek(A) 'dddd'

Теперь таблица выглядит понятнее:

Итоговая формула:

=query(data!$A:$I;
"select dayofweek(A), sum(G)
where B != ''
group by dayofweek(A)
label dayofweek(A) 'День недели', sum(G) 'Конверсии' format dayofweek(A) 'dddd'")

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

Помимо функции dayofweek(), существуют и другие, которые мы можем применять в наших запросах.

Скалярные функции

Перекрестные таблицы (PIVOT)

Чуть больше информации об этой кляузе есть в статье от netpeak, здесь приведу пример того, как мы можем использовать эту кляузу.

Задача:
Нужно по кампаниям построить таблицу, где по дням будет видна динамика конверсий, чтобы вовремя отслеживать проблемы.

Сделаем мы это с помощью следующего запроса:

=query(data!$A:$I;
"select B, sum(G)
where B != ''
group by B pivot A")

В итоге, получается матрица, которую можно использовать для анализа:

Что не вошло в шпаргалку

1. С помощью query можно тянуть данные из разных вкладок, из сторонних файлов и проводить с ними дальнейшие манипуляции.
2. В запросе query можно использовать динамические параметры. Например, сделать две ячейки, с помощью которых будет происходить фильтр по датам. Либо сделать фильтр по кампаниям. На эту тему можно почитать статьи https://shagabutdinov.ru/query_sheets/ и https://netpeak.net/ru/blog/vsemogushchaya-funktsiya-query-podrobnoe-rukovodstvo/ (здесь в конце страницы находится информация).
3. Использование IF в запросе https://infoinspired.com/google-docs/spreadsheet/if-function-in-google-sheets-query/
4. Подробно не расписаны кляузы offset и limit. Offset маловероятно, что пригодится.
5. Порядок кляуз в запросе имеет значение, если в неправильном порядке написать запрос, то гугл выдаст ошибку. К счастью, все ошибки можно расшифровать.