Тема кейса: Создание автоматизированного ETL-конвейера (Extract, Transform, Load) для трансформации данных из нестандартных Google Sheets таблиц в реляционную базу данных с последующей визуализацией в Yandex DataLens.
Краткое описание ситуации или проблемы
Заказчик использовал несколько сложноструктурированных Google Sheets таблиц для ежедневного внесения ключевых метрик менеджерами по продажам, квалификаторами и аккаунт-менеджерами. Исходная структура таблиц, оптимальная для ручного заполнения (с горизонтальной разбивкой по неделям и дням, и вертикальной — по менеджерам и метрикам), совершенно не подходила для анализа и построения дашбордов. Данные были неконсистентны, их автоматическая выгрузка и агрегация были нетривиальной задачей. Существовала острая потребность в едином источнике истины и автоматизированной системе визуализации для оперативного контроля показателей.
Цели проекта
- Автоматизировать ежедневный сбор данных из нескольких Google Sheets без изменения их исходной структуры (чтобы не ломать привычный процесс работы менеджеров).
- Преобразовать сложную матричную структуру таблиц в "плоскую" реляционную модель, пригодную для анализа.
- Обеспечить загрузку очищенных и структурированных данных в централизованное хранилище (БД PostgreSQL).
- Реализовать интуитивно понятные и производительные дашборды в Yandex DataLens для отслеживания KPI в реальном времени.
- Настроить полностью автоматический кроновый процесс, не требующий ручного вмешательства.
Какие данные использовались
Данные из трех основных Google Sheets таблиц:
- Отдел продаж: Проведено встреч, сделано звонков, Количество оплат, Выручка, Сумма допродаж и другие.
- Квалификаторы: Взято в работу, шт, Квалифицировано, шт, Назначено презентаций, Конверсии (с разделением на "Новые" и "Продления").
- Аккаунт-менеджеры: Аудиты, Встречи в Zoom, Допродажи, Подключение эквайринга, Удержание клиента.
Структура исходных данных: Вертикаль — строки с метриками и ФИО менеджеров; Горизонталь — временные периоды (недели месяца, разбитые на дни).
Какие инструменты и технологии применялись:
- Python 3: Основной язык для реализации ETL-логики.
- Google Sheets API: Доступ к данным таблиц через сервисный аккаунт.
- PostgreSQL: Реляционная база данных для хранения очищенных и структурированных данных.
- Yandex DataLens: Платформа для визуализации и построения дашбордов.
- Cron: Для автоматизации ежедневного запуска ETL-процесса на сервере.
- Docker: Для развертывания и контейнеризации базы данных на сервере.
Описание хода решения (архитектура и этапы):
Была реализована модульная архитектура Google Sheets → ETL (Python) → PostgreSQL → DataLens.
Была реализована модульная архитектура Google Sheets → ETL (Python) → PostgreSQL → DataLens.
Этап 1: Проектирование и настройка (Pre-ETL)
- Анализ исходных данных: Тщательно изучена структура каждой из таблиц, выявлены закономерности в расположении менеджеров, метрик и дат. Это был ключевой этап для написания корректных алгоритмов парсинга.
- Создание сервисного аккаунта Google: Настроен безопасный доступ к API.
- Развертывание БД PostgreSQL в Docker-контейнере: Обеспечена изолированная и легко воспроизводимая среда для хранения данных.
- Разработка схемы БД: Спроектированы и созданы таблицы для хранения фактических показателей (rnp_manager_sales_data, rnp_qualifier_data, rnp_account_data) и недельных планов (rnp_*_plans_weekly).
Этап 2: Разработка ETL-процесса (Extract, Transform, Load)
Процесс разделен на три независимых скрипта, управляемых главным скриптом run_etl.py.
Extract (googlesheets.py):
- Аутентификация: Использование файла сервисного аккаунта (service_account.json) для получения учетных данных.
- Извлечение: Получение сырых данных из всех указанных в конфигурации (config.ini) таблиц и диапазонов. Код универсален и может работать с любой из трех таблиц.
- Трансформация (частичная): Классы GoogleSheetsTransformer, QualifierDataTransformer и AccountDataTransformer отвечают за:
- Распознавание менеджеров: Поиск строк по шаблону "Фамилия Имя" с исключением служебных заголовков.
- Парсинг дат: Автоматическое определение временных периодов (недель и дней) из шапки таблицы и извлечение года/месяца из названия листа.
- Сопоставление метрик: Создание плоской таблицы, где каждой уникальной комбинации Дата + Менеджер + Метрика соответствует одно значение.
- Очистка данных: Приведение числовых значений из строковых форматов (10 000,00 ₽ -> 10000.0) к типу float.
Extract & Transform для планов (googlesheets_plan.py):
- Отдельная логика: Плановые показатели хранились в отдельном столбце и были месячными. Задача — разбить их равномерно по неделям месяца.
- Алгоритм: Определение количества недель в целевом месяце и равномерное распределение плана по ним для каждого менеджера и каждой метрики.
Load (postgres_loader.py):
- Подключение к БД: Параметры подключения (хост, пользователь, пароль) гибко настраиваются через файл конфигурации.
- Режимы загрузки: Код поддерживает два режима, управляемых параметром UPDATE_MODE:
- true (Обновление): Использует INSERT ... ON CONFLICT UPDATE для обновления только данных за текущий месяц. Экономит время и ресурсы.
- false (Полная перезагрузка): Полностью очищает таблицы от данных за текущий месяц и заливает их заново. Полезно для гарантированной консистентности после изменений в логике.
- Маппинг полей: Преобразование названий столбцов из DataFrame в названия колонок в БД.
- Пакетная вставка: Использование psycopg2.extras.execute_batch для быстрой загрузки больших объемов данных.
- Обновление представлений: Автоматическое обновление материализованных представлений в PostgreSQL после загрузки данных для их актуализации в DataLens.
Этап 3: Автоматизация и визуализация
- Автоматизация: С помощью cron на сервере был настроен ежедневный запуск скрипта run_etl.py в определенное время.
- Визуализация: В Yandex DataLens были подключены к базе PostgreSQL как к источнику. На основе актуальных данных построены дашборды, включающие:
- Сводные панели по выручке, конверсиям, выполнению плана.
- Детализацию по каждому менеджеру и продукту.
- Графики динамики показателей по дням и неделям.
- Индикаторы достижения целей.
Ключевые выводы и результаты:
- Полная автоматизация: Процесс сбора и загрузки данных не требует ручного вмешательства.
- Сохранение гибкости: Исходные таблицы Google Sheets остались неизменными для менеджеров, что минимизировало сопротивление внедрению.
- Качество данных: Данные автоматически очищаются, проверяются и структурируются, что резко снизило количество ошибок и время на подготовку отчетов.
- Масштабируемость решения: Архитектура позволяет легко добавлять новые метрики или таблицы для выгрузки.
Ценность для бизнеса
- Экономия времени: Ежедневный ручной сбор и агрегация данных из нескольких таблиц, занимавшие до 2-3 часов рабочего времени, были полностью устранены.
- Оперативность принятия решений: Руководство получило доступ к актуальным данным в режиме, близком к реальному времени, а не к устаревшим отчетам на конец дня.
- Повышение прозрачности: Четкая визуализация KPI каждого менеджера и отдела в целом мотивирует команду и помогает быстро выявлять "узкие места".
- Снижение операционных рисков: Автоматизация свела к нулю ошибки, связанные с человеческим фактором при ручном копировании данных.
- Масштабируемость: Появилась основа для внедрения более сложной аналитики, например, прогнозного моделирования.