В этом уроке я подготовила для вас еще один практический и подробный урок по шагам по работе с DataLens. Мы попробуем сделать отчет по платежам. А в конце вас ждет маленький лайфхак!
В прошлом уроке мы с вами попробовали сделать отчет по продажам с отображением этих продаж на карте, рейтингом категорий и динамикой продаж. В этом же уроке давайте попробуем сделать отчет по платежам. А в конце вас ждет маленький лайфхак!
Вы научитесь делать
- Рейтинги
- Динамику показателей
- Метки для строк по условию
Задача
Представим, что у нас имеется небольшая компания, выплачивающая роялти авторам книг, которая ведет свой учет в 1С. 1С, несмотря на свой богатый функционал, все же не обладает развернутыми аналитическими инструментами, позволяющими всесторонне проанализировать выплаты. Однако менеджмент компании хочет наглядно и доступно видеть расширенную информацию о платежах. Например, быстро и просто увидеть, сколько роялти было выплачено, а сколько авансов, с какими банками чаще всего приходится взаимодействовать, в какие страны чаще всего совершаются платежи и в какой валюте. Вся эта информация поможет бизнесу оптимизировать и улучшить денежные поток, тем самым, уменьшив расходы и высвободив ресурсы для других задач.
Что у нас есть?
Представим, что у нас есть некая выгрузка из 1С компании с необходимыми данными. DataLens позволяет создавать прямые подключения к базам данных, но все условные примеры мы будем рассматривать на базе условных выгрузок из Excel, суть это не изменит.
Итак, вот так выглядит наша выгрузка. С ней мы и будем работать.
Немного теории
Для начала (для тех, кто не знает) давайте разберемся вкратце, что такое DataLens.
Yandex DataLens – это специальный бесплатный BI инструмент для анализа данных. Он позволяет строить красивые отчеты и графики на основе каких-либо данных.
Анализировать можно абсолютно все, что будет в вашем источнике данных. Это могут быть продажи, доходы, расходы, платежи, частота каких-то событий – словом, все, что можно проанализировать. Его можно использовать как для разового анализа, так и на постоянной основе. По мере обновления данных в источнике, отчет будет обновляться вместе с ним. Что очень удобно для долгосрочного использования. Например, для анализа продаж из месяца в месяц. Что начать пользоваться DataLens достаточно иметь учетную запись Yandex. Данный сервис автоматически станет доступен для использования.
На скриншоте ниже видно, как выглядит пустой кабинет данного инструмента.
Там есть 4 типа объектов, которые создаются в определенной последовательности.
Подключения
Данный тип создается в самом начале, здесь мы указываем системе, откуда нужно брать данные и какие. На данный момент доступно несколько вариантов подключения.
В наших условных примерах мы будем использовать Excel файл в формате .csv.
Датасеты
Данный тип объектов описывает набор данных, здесь можно настроить связи между источниками, добавить параметры и прочее. Этот этап особенно важен, так как именно на базе датасета и будут строиться все графики и таблицы.
Чарты
В данном разделе мы будем создавать таблицы и графики. Эти элементы создаются отдельно и независимо друг от друга, чтобы впоследствии их можно было использоваться для разных других целей. Например, один и тот же график можно будет добавить в разные отчеты.
Дашборды
Это финальный этап. Дашборд – это некая область, на которую мы будем собирать все наши созданные ранее таблицы и графики в единый отчет.
Реализация
С общими понятиями разобрались, теперь можно перейти к практике. Вернемся к нашему Эксель файлу с оплатами. Прежде, чем начать работать с ним, нам нужно добавить его в качестве источника данных.
ПОДКЛЮЧЕНИЕ
Для этого заходим в раздел Источники и нажимаем Создать подключение.
Выбираем файл в формате CSV. Чтобы создать такой формат, достаточно просто сохранить обычный Эксель файл в данном формате.
Далее нажимаем Загрузить файл и выбираем нашу выгрузку.
Если все сделано верно, то в рабочей области появится предварительный просмотр полей файла. Тут следует проверить, что заголовки в таблице используются также в качестве заголовков и тут. Это видно справа вверху. Также на всякий случай следует проверить, что типы данных в колонках соответствуют нужным. Например, что число – это число, а текстовое поле – это текст.
Если все верно, нажимаем Создать подключение, задаем имя и нажимаем Сохранить. На этом первый шаг выполнен. Источник данных добавлен.
ДАТАСЕТ
Теперь нам нужно добавить Датасет, или набор данных. Это то, на основе чего мы и будем строить наши будущие графики. Заходим в раздел Датасеты и выбираем Создать датасет.
Выбираем наше подключение, которые мы создали во втором шаге. Таблица с оплатами также автоматически подтянулась.
Теперь нам нужно создать для размерности даты.
Размерность даты
Теперь давайте создадим параметр с размерностью даты. Так мы сможем анализировать данные в разрезе квартала и года.
Для этого идем в раздел Параметры и жмем Добавить.
Заполняем имя параметра и значение по умолчанию. Пусть по умолчанию в отчетах данные будут отображаться по дням.
Параметр готов. Теперь создадим еще одно вычисляемое поле с датой, которое будет чувствительно к размерности.
Снова идем в раздел Поля и жмем Добавить. Задаем имя нового поля и прописываем формулу.
DATETRUNC([Date],[DateTrunc])Данная функция говорит новому полю, что нужно отображать дату согласно выбранной размерности. По умолчанию в параметре стоит день, поэтому по умолчанию дата будет разбита по дням. Но в будущем в отчете мы сможем выбирать другие периоды, тогда дата будет меняться в зависимости от этого.
Жмем создать и видим внизу новое поле с датой, чувствительной к размерности. Пока она совпадает с датой из источника.
На этом наш датасет готов. Можем сохранять и переходить к непосредственной визуализации наших данных.
ЧАРТЫ
Итак, вспоминая нашу изначальную задачу по анализу платежей, для начала нужно подумать, а какие именно показатели и графики нам нужно создать. Что именно будет информативно и показательно. Чтобы охватить разный функционал данного инструмента, давайте сделаем следующие чарты, которые потом добавим в один общий отчет:
- Общий показатель выплат с разбивкой по типам платежа: роялти, авансы и возвраты. Так менеджмент сможет увидеть общие сведения о том, сколько всего было выплачено авансов или сколько денег вернулось.
- Динамика платежей с условным форматированием. Еще проанализируем динамику наших выплат. В какой период их было больше, а в какой меньше. Это поможет спланировать платежи на будущее и не допустить кассовый разрыв.
- Распределение по каталогам. Добавим круговую диаграмму с данными о распределении по каталогам. Посмотрим, на какой каталог сколько выплат приходится.
- Несколько диаграмм с рейтингом. Например, ТОП-3 банка и ТОП-3 автора. Так мы оценим, с какими банками нам приходится работать чаще всего, и кто из авторов получает больше всего выплат.
- Также добавим небольшую сводную таблицу с данным о платежах в валюте, так как все основные показатели будут в рублях.
- И в конце добавим общий реестр выплат с визуальным оформлением. К авансам и возвратам добавим визуальные метки, чтобы их было сразу видно.
Общие показатели
Начнем с общего показателя продаж. Заходим в раздел Чарты и жмем Создать чарт.
Далее выбираем наш Датасет и тип чарта Индикатор.
Для начала создадим общий индикатор выплаченной суммы. Перетаскиваем поле с суммой как на скриншоте ниже.
Жмем на решетку «#» и задаем заголовок поля и округление. Сохраняем.
Первый показатель готов. А теперь создадим еще три таких показателя, которые будут показывать только сумму выплаченных авансов, только роялти и только возвраты.
Создаем новый Индикатор и перетаскиваем сумму также, как и в предыдущем случае. Теперь нам нужно отфильтровать часть сумм по типу платежа, отобрав только авансы. Для этого в раздел Фильтр перетаскиваем поле PayType. Система попросит указать принцип фильтрации значений. Перетаскиваем значение Аванс вправо и жмем Применить фильтр.
Общая сумма в 23 млн. меняется на 6 млн. Теперь мы видим только те выплаты, где тип платежа стоит Аванс.
Для индикатора также доступно редактирование цветового оформления. Для более удобного восприятия, давайте изменим цвет суммы для авансов на зеленый. Жмем шестеренку около заголовка Показатель и выбираем цвет.
Теперь сумма стала зеленой.
Аналогичным образом создаем еще два индикатора для типов платежа Роялти и Возврат.
Динамика платежей
Теперь давайте посмотрим график с динамикой платежей. Для этого создаем Чарт и выбираем Столбчатую диаграмму.
Перетаскиваем поля, как на скриншоте.
Напомню, что поле PeriodTrunc зависимо от параметра DateTrunc. Размерность дат будет меняться в зависимости от выбранной группировки. Но увидим мы это только на финальном отчете. Пока данные будут отображаться по дням. Также переименовываем заголовки полей для красивого отображения. Наш отчет начинает выглядеть вот так.
Но давайте добавим красок и разобьем эти столбики на цвета в зависимости от типа платежа. Чтобы было визуально видно сколько было, например, авансов из общей суммы выплаты. Для этого нам потребуется создать несколько пользовательских полей в отчете, которые будут содержать только авансы или только роялти. Сделать это получится через формулы. Для этого создаем Новое поле.
Задаем имя нового поля и прописываем формулу, как на скриншоте ниже.
SUM_IF([Amount],[PayType]="Аванс")Логика формулы проста – если тип платежа Аванс, то сумма берется в копилочку этого поля, а если нет, то нет. Так мы отсекаем лишнее и собираем только авансы. Сохраняем поле. Аналогичным образом создаем еще два поля для Возвратов и Роялти. Формула будет точно такая же, лишь меняем в ней типы платежей. И теперь перетягиваем эти три новых поля на ось Y вместо общего поля Amount.
Таким образом, наша общая колонка будет разбиваться на несколько блоков, визуально отображая долю тех или иных типов платежа.
Готово. Сохраняем динамику платежей и идем дальше.
Каталоги
Теперь давайте создадим круговую диаграмму с процентным распределением платежей по каталогам. В нашем условном примере есть два типа каталога Классик и Модерн. Давайте посмотрим, по какому каталогу сколько платежей проходит. Создаем чарт с типом Круговая диаграмма. Перетягиваем поля согласно скриншота.
Диаграмма почти готова. Пока мы видим распределение по каталогам, но вместо процентов отчет показывает общие суммы. Давайте сделаем так, чтобы были проценты. Сделать это просто. Заходим в настройки поля (значок решетки) и выбираем тип отображения В процентах.
Нажимаем Применить и видим, как суммы превратились в проценты.
Из данного отчета видно, что по каталогу Классик выплат прошло больше. Сохраняем этот чарт и идем дальше.
Рейтинг
Далее по списку у нас идут рейтинги. Давайте составим ТОП-3 банка, автора и стран, в которые мы платим. Начнем с банков.
Выбираем наш датасет и Линейчатую диаграмму.
Но прежде, чем мы начнем перетягивать данные на график, нам потребуется создать новое поле с формулой, которая будет отбирать для нас те самые ТОП-3 позиции. Нажимаем «+» и Поле.
Задаем ему имя и прописываем формулу. Данная функция ранжирует сумму по убыванию. От большего к меньшему.
RANK(Sum([Amount]))Теперь перетягиваем поля на диаграмму, согласно скриншоту и настраиваем красивое отображение полей.
Для того, чтобы в график попало только первые три самые большие позиции, нужно настроить фильтр. Нажимаем на решетку и просим систему выводить нам только три самые крупные позиции. Их порядковый номер будет 1, 2 и 3.
И ставим сортировку по сумме по убыванию. Для этого достаточно просто нажать на полоски.
В итоге получается вот такой красивый отчет.
Райффайзен Банк оказался самым первым по сумме выплат на него.
Сохраняем первый чарт. Теперь нам нужно создать еще два таких же отчета. Можно либо также с нуля создавать их по новой, либо можно скопировать уже имеющийся отчет и просто поменять там одно поле. Это существенно сэкономит время. Для этого заходим в список всех чартов и ищем наш вновь созданный рейтинг банков. Нажимаем на три точки справа и выбираем Дублировать.
Сервис попросит присвоить имя новому отчету, после чего в этом же списке появится дубликат отчета с новым именем. Заходим в него. Теперь нам достаточно лишь заменить поле Банки на поле Supplier.
И наш отчет тут же показывает нам всю необходимую информацию, но уже в разрезе авторов.
Второй отчет готов. Теперь осталось сделать третий точно такой же отчет, но вместо авторов поставим страны.
На этом с рейтингами все.
Валюта
Теперь давайте потренируется использовать таблицы и сводные таблицы в DataLens. Они тоже очень полезны и могут пригодиться при анализе необходимых показателей. И начнем с валюты. Мы с вами уже построили немало графиков, но все они содержат информацию о платежах в рублях. Однако менеджменту компании может понадобится и валютная информация, например, какая валюта используется чаще. Поэтому в данном уроке давайте сделаем для управленцев небольшую сводную таблицу по платежам в валюте. В какой валюте, какой тип платежей и сколько мы заплатили.
Снова создаем новый чарт и выбираем Сводная таблица. Перетаскиваем поля, как на скриншоте.
Затем нажимаем на шестеренку и включаем опцию Показывать итоги. Так в нашем отчете будут считаться общие итоги по колонкам и столбцам.
Таблица почти готова, но вы можете заметить, что в нее попадают и рубли тоже. Чуть ранее мы решили, что в данном отчете мы хотим видеть только валютные суммы, без рублей.
Давайте уберем их через фильтр. Перетягиваем поле Currency в раздел с фильтрами и выбираем Не принадлежит множеству и RUB.
Готово. Теперь наша таблица готова. Из нее видно, что в валюте мы платили только авансы в евро.
Реестр платежей
И теперь нам осталось создать последний и самый интересный отчет с реестром платежей. Это будет некая таблица с конкретным списком выплат. По сути это будет наша же выгрузка, но в красивом виде. Такая таблица будет интересна и полезна менеджерам компании, если нужно, например, посмотреть детали платежа. Очень часто у менеджеров не бывает доступа в бухгалтерские и финансовые системы. В этом же случае они смогут полноценно работать с только нужными финансовыми данными и иметь к ним доступ в любое время.
Снова заходим в чарты и создаем отчет с типом Таблица.
Перетаскиваем поля, как на скриншоте ниже.
В итоге у нас получилась таблица-реестр с платежами. Добавляем итоги и красивые заголовки для колонок.
Чтобы итоги отображались, поля с суммами должны иметь агрегацию. Для этого жмем на решетку и выбираем тип агрегации.
В целом готово. Подробный реестр платежей готов. Но давайте пойдем чуть дальше и добавим метки около названия автора для тех строк, где были авансы или возвраты. Чтобы эти строки сразу бросались в глаза.
Для этого нам нужно будет создать новое пользовательское поле в отчете.
Назовем его Автор1. И пропишем следующую формулу.
Функция CONCAT() означает сцепить/слепить вместе два или более значений.
IF [PayType]="Аванс" Then CONCAT("🅰 ",[Supplier])
ELSEIF [PayType]="Возврат" THEN CONCAT("❌ ",[Supplier])
ELSE [Supplier]
ENDДанная формула означает, что если тип платежа Аванс, то к названию автора вначале нужно добавить эмодзи с буквой А, а если возврат, то крестик, иначе выводить просто название автора. Чтобы вставить в формулу такую картинку, просто скопируйте нужные эмоджи. Их можно найти в Интернете.
Создаем новое поле и перетягивает его вместо изначального поля Supplier (Автор).
Готово. Теперь для платежей с авансом или возвратом мы видим соответствующую отметку.
На этом с таблицей мы закончили. И теперь остался последний шаг – создать общий отчет, где мы соберем все созданные чарты в одном месте.
ДАШБОРД
Для создания общего сводного отчета-дашборда нам потребуется последний раздел с одноименным названием. Заходим в раздел Дашборд и наживаем Создать.
После этого у нас открывается пустое поле, на которое нужно добавить все те графики, что мы делали до этого. Но прежде давайте сначала добавим на рабочую область фильтры (или Селекторы), с помощью которых мы будем фильтровать данные.
Для этого нажимаем Добавить – Селектор.
Но прежде давайте подумаем, какие фильтры и разрезы может захотеть менеджмент компании? Конечно, это, как минимум, период, автор, тип платежа, статус автора и валюта. Давайте эти фильтры и добавим.
Начнем с периода. Для этого в настройках селектора выбираем наш Датасет и поле с Датой.
И добавим размерность, в каком разрезе мы будем анализировать данные.
Для этого создадим еще один Селектор, но на этот раз выберем Ручной ввод и зададим параметры вручную.
Имя поля должно соответствовать имени параметра, который мы создавали в Датасете. Вот он.
Таким образом, при выборе какого-то значения в этом селекторе значение day будет замещаться на другое. Например, месяц или год.
Список этих значений также настроим вручную.
На этом второй селектор готов, добавляем и его тоже.
Теперь добавим еще три селектора с автором, типом платежа, валютой и статусом.
Делается это по аналогии с датой.
В шапке у нас должно оказаться 6 селекторов.
Теперь добавим все нашли графики.
Вместо селектора выбираем Чарт.
Добавим общие суммы платежей.
Дабы не загромождать место, их лучше будет разделить на вкладки. И в этом же окне нажимаем добавить и выбираем второй отчет. Звездочной отмечает отчет для отображения по умолчанию.
Таким образом, этот отчет будет занимать одну позицию, но разделен на четыре вкладки.
Аналогичным образом добавим рейтинг, разделив его на вкладки, динамику платежей, круговую диаграмму с каталогами и две таблицы.
Размещаем все отчеты любым удобным способом. Например, так.
А теперь давайте попробуем наши фильтры. Для начала проанализируем динамику платежей не по дням, а по месяцам. Выбираем month.
Мы видим, что цифры остались прежние, но график платежей изменился и стал помесячным. И теперь мы наглядно видим, что пик оплат пришелся на Ноябрь и Декабрь.
А теперь давайте посмотрим отдельно Ноябрь и Декабрь. Выбираем нужные периоды в фильтре Период.
Из данного отчета мы видим, что в ноябре в основном платились роялти, а в декабре большая часть была авансами. 5000 евро из них были в валюте.
По типу платежа можно отобраться только авансы.
И только юр. лица.
Так мы увидим все авансы за этот период, выплаченные только юр. лицам. Причем, создать такой отчет можно всего один раз.
В дальнейшем вам будет достаточно лишь добавлять исходный Эксель файл новым продажами и перезагружать его в Источниках.
После обновления дашборд отобразит отчеты с учетом уже новых продаж.
Как поделиться отчетом
Для того, чтобы поделиться отчетом, нужно либо настроить доступ к нему конкретным пользователям, либо просто сделать его общедоступным. Его смогут видеть все, у кого есть ссылка.
На этом все! Надеюсь, что данный урок был вам полезен.
Также данный урок можно посмотреть или распечатать в формате PDF.
Наши курсы
Если вам понравился урок, то можете поддержать проект по кнопке ниже. Это очень поможет делать для вас больше интересных и полезных выпусков. Спасибо!
Задать вопросы и обсудить волнующие темы про аналитику данных теперь можно на нашем форуме.
Если вам понравился урок, подписывайтесь на канал или группу и ставьте лайки. А также пишите в комментариях свои кейсы или вопросы. Самые интересные мы обязательно разберем.
