Вводная часть

Осенью 2021 года я задумался о бесплатных инструментах аналитики и построения отчетности, доступных простым пользователям. В том или ином виде можно использовать Power BI или Tableau, но почему бы не попробовать что-то более простое?

Небольшой дисклеймер: датасет, о котором далее пойдет речь, был загружен осенью 2021 года. Сейчас датасет другой, возможно более чистый. Загружать новые данные счел нерациональным, поскольку серия постов будет про простейшие визуализации, а не про актуальные исследования или сложные диаграммы. И нет, это не подробная методичка по возможностям GDS, это только общий обзор решения и разбор одного кейса.

Нас интересует только сторона работы обычного аналитика, насколько это возможно (и насколько я себе это представляю), поэтому я буду стараться искать самые простые пути решения проблемы. Понимаю, что некоторые методы вроде использования промежуточной базы данных не выглядят простыми для кого-то, но с тем же успехом можно использовать таблицы от Google. У меня БД просто была под рукой, да и выстроить полноценный ETL-процесс без неё не выйдет.

Работа с БД

ETL-процесс и загрузка данных

Для упрощения, рассмотрим наш ETL-процесс как загрузку данных в хранилище, приведение к нужному нам виду данных и выгрузку в нашу систему аналитики Google Data Studio.

Чтобы не ходить далеко, датасет решено было взять с data.mos.ru В нем есть раздел “данные”, где есть раздел “дороги и транспорт”, где есть датасет “выданные разрешения… таксистам” (там очень длинное название, которое не пожелало копироваться). Почему именно этот вариант? Я люблю машины и было бы интересно узнать, автомобили какой марки наиболее популярны в такси. Кстати, весной 2022 года это исследование может быть довольно актуально в связи с ростом стоимости ЗЧ.

Первая проблема, с которой мы сталкиваемся - способа загрузить данные напрямую в Студию нет, нужно обязательно какое-то промежуточное хранилище. Ограничение логичное, способов решения 2: либо использовать какую-то БД, либо Google Таблицы. Я уже упоминал, что второй вариант мне кажется непростым, поэтому дальше я загружу CSV в свою БД. Ничего интересного: обычный Mysql, никакие его параметры не могут представлять интереса. Правда, залить в него 50-метровый CSV оказалось немного не так просто, как я рассчитывал, но что поделать, давно не держал шашки в руках.

Трансформируем данные

В тот момент, когда я попробовал выгрузить первые пару сотен строк в интерфейсе phpMyAdmin, я понял: просто тут ничего не будет. Кто и как заполняет эти документы - загадка, но артефактов обнаружилось приличное количество даже при том, что я не сильно углублялся в данные. В этот момент у меня родилась мысль, что можно даже сделать сопоставление: какое количество данных мы потеряем, если довольно грубо отрежем записи без попытки привести их к вменяемому виду; как изменятся данные, если мы немного их преобразуем к общему знаменателю. Поехали!

Поле Status. В исходных данных в нем помимо 5-ти статусов (Аннулировано, Истек срок действия, Действующее, Прекращено действие и Приостановлено) лежат какие-то даты. Тут все просто, мы берем только те поля, которые имеют не_цифры. Опционально можно проверять на цифру только первый символ, но на данном этапе я не вижу в этом никакой необходимости. Upper позволит нам исключить варианты различных способов написания статусов.
SELECT DISTINCT UPPER(status) FROM taxidata where status REGEXP '^[[:alpha:]]';
В поле VehicleBrand также лежит много мусора. Его можно разделить на несколько категорий: опечатки и ошибочно внесенные данные. Опечатки можно отследить только по какому-то мэппингу, но автоматизировать этот процесс я не придумал как. Поэтому просто попробуем отсечь случаи, когда в поле с маркой пишут ещё и модель. Для этого попробуем отрезать часть строки до первого пробела и использовать только её.
select distinct substring_index(VehicleBrand, ' ', 1), count(VehicleBrand) from taxidata group by substring_index(VehicleBrand, ‘ ’, 1);
Это позволило с 531 строки сократить до 355
Сразу видно, что есть записи вида МаркаНейм-, то есть лишний дефис. Его мы уберем с помощью ещё одного substring_index
Промежуточный результат.
CREATE VIEW taxidataclean AS SELECT LicenseNumber, global_id, VehicleNumber, INN, substring_index(VehicleBrand, ' ', 1), OGRN, BlankNumber, VehicleYear, FullName, ShortName, LicenseDate, LicenseEndDate, ValidityDate, EditDate, Info, Status, VehicleModel
FROM taxidata
where status REGEXP '^[[:alpha:]]'
После всех преобразований количество уникальных марок авто снизилось до 310.
CREATE VIEW taxidataclean AS SELECT LicenseNumber, global_id, VehicleNumber, INN, substring_index(substring_index(VehicleBrand, ' ', 1), '-', 1) AS VehicleBrand, OGRN, BlankNumber, VehicleYear, FullName, ShortName, LicenseDate, LicenseEndDate, ValidityDate, EditDate, Info, Status, VehicleModel
FROM taxidata
where status REGEXP '^[[:alpha:]]'

Теперь настало время посчитать, сколько данных мы потеряли и что приобрели взамен. Все, что касается VehicleBrand никак не уменьшало количество строк в таблице. Мы лишь приводили данные к одному знаменателю. Но вместо 531 уникальной записи мы смогли добиться 310! Это уже хороший прогресс, но дальше мы немного подумаем, что ещё можно по этому поводу сделать.

Добавив условие по полю Status мы получили вместо 177425 строк 176885. То есть, всего 540 строк имели нечитаемый статус и по умолчанию мы решили, что они нам не нужны. Это порядка 0,3% от общего количества записей.

Ещё немного трансформации

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

Когда таблица создана, загрузим её в одноименную таблицу в БД и попробуем собрать некую итоговую витрину данных.

CREATE TABLE TaxiDataClean1 AS SELECT taxidata.LicenseNumber, taxidata.global_id, taxidata.VehicleNumber, taxidata.INN, taxidata.OGRN, taxidata.BlankNumber, taxidata.VehicleYear, taxidata.FullName, taxidata.ShortName, taxidata.LicenseDate, taxidata.LicenseEndDate, taxidata.ValidityDate, taxidata.EditDate, taxidata.Info, taxidata.Status, taxidata.VehicleModel, trim(mappingVehicleBrand.VehicleBrandNew) as VehicleBrand

FROM taxidata

LEFT JOIN mappingVehicleBrand ON trim(mappingVehicleBrand.VehicleBrand) = substring_index(substring_index(taxidata.VehicleBrand, ' ', 1), '-', 1)

where taxidata.status REGEXP '^[[:alpha:]]' ;

Итоги работы с БД

В итоге мы сократили количество марок с 310 до 92! С этим уже вполне можно работать, хотя некоторые артефакты и остались (почти все они лежат в “пустом” VehicleBrand).

Теперь настало время поговорить о том, зачем вообще нужно было столько мороки с БД. Загружать в BI-систему excel - это реалии наших дней, но точно не целевая картина. Во-первых, flat files неудобно перегружать каждый раз при изменении. Если речь идет о google docs, то возможно добиться хотя бы единого места расположения файла. Если говорить о файлах, загружаемых с АРМ пользователя - тут все может сломаться довольно быстро и просто из-за простого копирования, переименования или перемещения файла.

Во-вторых, работать с БД гораздо удобнее, поскольку, как мы выяснили, на её основе мы можем сделать витрину данных, которую можно загружать в отчет в формате SELECT * FROM и не заморачиваться по поводу фильтрации на стороне BI-решения. Да, в итоге мы сделали таблицу (из-за некоторых ограничений моего сервера с БД), но могли бы сделать и представление, данные в котором автоматически обновлялись бы при изменении данных исходных таблиц. Короче, с архитектурной точки зрения такое решение выглядит более целостным и корректным.

Создаем отчет в Google Data Studio

Теперь, когда у нас есть БД с более-менее очищенными данными, можно приступить к созданию отчета в нашей аналитической системе.

Создаем новый отчет, выбираем источник данных

Думаю, с созданием нового отчета и выбором листа никаких проблем не возникнет: интерфейс в этом плане очень простой, максимально заточенный под self service разработку, то есть полное отсутствие айтишника при создании отчета. Конечно, это только при условии построения отчетов на Google-источниках.

Для подключения к разным источникам есть великое множество коннекторов. Часть из них сделала сама Google, часть - сторонние разработчики. Из популярного - все популярные реляционные СУБД, instagram, все сервисы материнской компании и целая россыпь каких-то специализированных SEO и CRM систем. Но и рассчитывать на что-то полновесное и серьезное не стоит: SAP и систем Big Data в списке нет. Судя по скорости работы, даже пару миллионов строк переварить этой штуке будет довольно проблематично.

Описывать подключение не буду - все стандартно, порт, название БД, пароль-логин. Стоит ли напоминать, что не стоит делать это через root даже, если вы не понимаете отличия Рута от чего-либо ещё?

Новый лист как новая страница

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

Этот холст можно настраивать централизованно через шаблоны, в меню которых можно попасть через кнопку “Файл”. Стандартных хватит для большинства задач плюс каждый элемент внешне можно настраивать вручную. Разумеется, шаблоны можно редактировать.

Любой отчет состоит из объектов. Какие-то необходимы нам для визуализации данных (графики, таблицы, показометры), иные для UX или дизайна.

Красоту можно наводить стандартными объектами, знакомыми ещё с продуктов Office от мелкомягких: картинки, линии, формы, текст. Сомневаюсь, насколько красивое нечто можно сделать с помощью этих стандартных инструментов, но любое иное BI-решение не будет более разнообразным без дополнений (Extension).

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

Для каждого объекта можно указать источник данных, с которым он будет работать. К примеру, хотим мы создать фильтр - нет ничего проще! Выбираем источник, указываем признак, по которому будет работать фильтр - и вуаля!

Обратим внимание на странную штуку - откуда в фильтре показатель? Фактически это агрегат по признаку, используемому нами для фильтрации. В итоговом варианте это выглядит так:

Довольно удобно: сразу видно количество записей по каждому варианту, это снимает необходимость делать таблицу по такому признаку.

В некоторых системах понятия прямой таблицы и pivot разнесены, причем порой так, что в прямую нельзя добавить расчетный показатель никоим образом. Google решил выбрать иной принцип деления: в любой таблице вы можете считать что и как хотите, зато в качестве альтернативных таблиц у вас есть вариант со встроенной столбчатой диаграммой и тепловой картой! В дата-журналистике давно уже наметился тренд на использование тепловых карт везде и всюду, хотя в бизнес-запросах подобный механизм всё ещё не так популярен.

Работа с таблицами и любыми другими диаграммами одинакова: выбираем признак, который будет считаться осью X, выбираем показатель (один или несколько), которые будут откладываться по оси Y. Если вдруг потребуется сделать горизонтальную столбчатую (полосчатая) диаграмму - такое тоже можно соорудить. Цвет выбрать у графиков - пожалуйста. Нюансов много, а писать обучающий материал можно было бы только при наличии запроса от страждущих :)

Работа с показателями

Расчетный показатель - это то, ради чего мы вообще создаем отчетность. Как и в программировании, количество функций для работы с показателями обычно ограничено и условно делится на трансформацию данных и расчет агрегатов. К трансформации данных я могу отнести приведение типов, работу с датами, вычленение отдельных значений из большой строки и так далее. Агрегирование чаще всего сводится к подсчету суммы или количества.

Google Data Studio позволяет работать с помощью методов, стандартных в программировании и SQL-запросах. Посчитать количество - count, сумму - sum, арктангенс - atan. В справке и на форуме сообщества можно узнать чуть больше о различных функциях, но всегда проще отталкиваться от задачи, которую вы хотите решить.

Есть такая штука, как ad-hoc показатели, то есть показатели, отображаемые по запросу пользователя. К примеру, в таблице агрегатов у вас показана сумма продаж в разрезе товаров: хлеба вчера купили на 1560 рублей, молока на 4500. Но аналитику стало интересно узнать, какое количество молока приобрели, а не только сумму продаж - ему требуется показатель count. Мы можем вывести его в область дополнительных показателей:

Тогда на нашей визуализации появится новая пиктограмма “дополнительные показатели”, по щелчку выплевывающая контекстное меню с выбором:

Итоги

На 2-х листах я построил одинаковые диаграммы, по которым во-первых можно определить количество тех или иных автомобилей в сервисе такси; во-вторых прикинуть количество ошибочных записей, из-за которых мы потеряли бы достаточно большой пласт данных. Если прошерстить отдельные интересные записи, то можно найти некоторые артефакты. Например, как вам такое такси:

Вполне реальный кейс, запись о Lamborghini я увидел в данных, а с помощью сервиса “номерограм” смог обнаружить фотографию автомобиля, по документам зарегистрированного как такси.

Говорить о Google Data Studio как об инструменте, готовом для работы с большими данными вряд ли возможно, поскольку даже с такими простенькими датасетом и визуализацией эта штука регулярно выпадала в ошибку, которую лечило только обновление страницы. С другой стороны, гипотезы на небольших срезах можно проверять быстро и просто, особенно если данные уже лежат в БД или их можно закинуть в Гуглдок.

Сейчас у меня появилась мысль сравнить механизмы работы Google Data Studio с Yandex Datalens, который мне видится несколько более актуальным в наше непростое время. Посмотрим, что из этого выйдет.

Как собрать отчет в Google Data Studio быстро и просто