Наступил новый 2016 год, а значит пора обновить инструменты для упрощения скучной механической работы. Отделы аналитики, маркетинга, продаж часто сталкиваются со следующими трудностями при обновлении отчетности:
1. Данные приходится собирать воедино из нескольких источников.
2. Отчеты составляются в Excel, что накладывает значительные ограничения на объем обрабатываемых данных.
3. Внесение изменений в заранее настроенные разработчиками выгрузки дело как правило не самое быстрое.
Если отчеты нужно обновлять еженедельно или даже ежедневно, то эта процедура становится весьма напряжной даже для самых терпеливых. С помощью надстройки Excel Power Query и записи данных в MySQL можно свести обновление большинства отчетов до простого нажатия кнопки «Обновить»:
1. Данные из любого количества источников импортируются через SQL-запросы в обычные таблицы Excel.
2. Даже из большой базы можно записывать в Excel только небольшую часть данных (например, итоговые суммы за нужный диапазон дат с группировкой только по нужным столбцам).
3. Изменения в отчет можно вносить просто поменяв SQL-запрос. Далее формируем нужный отчет стандартными средствами Excel.
В этой статье я покажу как настраивать и автоматически заполнять простые базы данных MySQL (на примере выгрузки статистики всех ключевых слов из Яндекс Метрики), а потом одной кнопкой обновлять отчеты в Excel, используя надстройку Power Query. Power Query имеет весьма странные особенности работы при составлении SQL-запросов (особенно динамических), которые мы разберем во второй части статьи.
Выбор MySQL (или любой другой популярной базы данных) вполне очевиден — бесплатно, относительно просто, возможность работать с довольно большими базами данных без технических хитростей. В качестве примера будем использовать Amazon Web Services: дешево (в большинстве случаев используемый инстанс будет бесплатен для вас в течение 12 месяцев).
Итак, начнем (если у вас уже есть базы данных с готовыми данными, то можно сразу переходить к разделу с Excel):
1. Регистрируемся на AWS (если еще нет учетки), запускаем самый простой инстанс t2.micro и заходим на него по SSH. Можно посмотреть краткую инструкцию в прошлом посте habrahabr.ru/post/265383. Обратите внимание, что нам потребуется первый в списке вариант инстанса на Amazon Linux AMI. Необходимо выставить правила, разрешающие обращение к инстансу по нужным портам:
В целях безопасности лучше выставлять ограничения на IP-адрес. Если у вас динамический IP, то это проблемная опция. Также иногда ограничение доступа к MYSQL по IP вызывает ошибку в Excel. Если выставить любой IP, то все работает.
2. Исполняем подряд команды, описанные в документацииdocs.aws.amazon.com/AWSEC2/latest/UserGuide/ec2-ug.pdf. Нам нужна глава «Tutorial: Installing a LAMP Web Server on Amazon Linux». Запомните пароль, который вводите при выполнении команды «sudo mysql_secure_installation». Для удобства установите phpMyAdmin как описано в конце этой главы. Если будете копипастить из документации строчку «sudo sed -i -e 's/127.0.0.1/your_ip_address/g' /etc/ht tpd/conf.d/phpMyAdmin.conf», то обратите внимание, что иногда при копировании в «httpd» появляется лишний пробел.
После этих действий на вашем инстансе должна открываться такая страница:
3. Заходим под пользователем root и паролем, который вводили при настройке. Для доступа к базе данных «извне» (т. е. из Excel) нам потребуется пользователь, отличный от root. Заводим его в интерфейсе phpMyAdmin в меню Пользователи --> Добавить пользователя. Добавим пользователя stats, зададим пароль и назначим ему привилегии SELECT и INSERT. Итого получим:
4. Теперь создадим базу данных data:
5. В данном примере будем наполнять базу статистикой посещений по ключевым словам из Яндекс Метрики. Для этого создадим таблицу seo (обратите внимание, что у столбца id надо отметить опцию A_I (auto increment)):
6. Для получения статистики по ключевым словам из Яндекс Метрики можно использовать следующий скрипт. В качестве параметров нужно указать начальную и конечную дату выгрузки (переменные $startDate и $endDate), авторизационный токен (в коде есть описание как его получить), номер счетчика, из которого нужно получить статистику, и параметры базы данных: ID инстанса, логин (у нас «stats»), пароль и название базы (у нас «data»). Скопируйте в корневую папку инстанса этот код и запустите командой «php seo.php».
Если возникнут ошибки при соединении с базой, то они отобразятся в консоли и выполнение будет прервано. В случае успешного выполнения получим статистику ключевых слов за выбранный период:
Отлично, данные получены. Посмотрим как получать их в Excel.
Использование Power Query для выгрузки данных в Excel
Power Query представляет собой надстройку, которая расширяет возможности Excel по выгрузке данных. Скачать можно тут www.microsoft.com/en-us/download/details.aspx?id=39379. Для работы с MySQL может потребоваться MySQL Connector и Visual Studio (предлагаются при установке из дистрибутива).
1. После установки выбираем MySQL:
2. В качестве базы указываем ID нашего инстанса (как было в скрипте) ec2-....compute.amazonaws.com. База данных data. Для ввода логина выбираем «База данных»:
3. В открывшемся окне дважды кликаем на таблицу seo и получаем:
В этом окне можно управлять запросами, изменяя столбцы и количество строчек. Когда база данных небольшая, то это работает. Однако если размер данных превышает даже 20MB, то Excel на большинстве компьютеров просто повиснет от такого запроса. К тому же неплохо бы менять даты запроса или другие параметры.
Динамические запросы в Power Query можно делать с помощью встроенного языка Mmsdn.microsoft.com/en-us/library/mt253322.aspx, однако запросы крайне неустойчивы в плане изменения каких-либо параметров в них. Чтобы запрос оставался «постоянным» сделаем следующий прием:
1. Сначала составляем таблицу, в которой указываем нужные нам параметры. В нашем примере это дата выгрузки. Формат ячеек со значениями лучше выставить как тестовый, т. к. Excel любит изменять формат ячеек по своему усмотрению:
2. Создадим запрос Power Query «Из таблицы», который будет просто дублировать эту таблицу:
3. В опциях запроса обязательно укажите формат второго столбца как Текст, иначе последующий SQL-запрос будет некорректным. Далее жмем «Закрыть и загрузить».
Итого мы получили запрос Power Query к обычной таблице, из которого будет брать значение начала и конца выгрузки.
Чтобы сделать SQL-запрос потребуется отключить одну опцию: заходим в Параметры и настройки --> Параметры запроса --> Конфиденциальность и выбираем «Игнорировать уровни конфиденциальности для возможного улучшения производительности». Жмем Ок.
4. Теперь делаем запрос к нашей базе данных, указывая в качестве начала и конца периода значения таблицы из пункта 3. Снова подключаемся к базе в Power Query и нажимаем «Расширенный редактор» в меню.
Например, мы хотим получить сумму визитов, которые принесли ключевые слова, содержащие «2015». На языке M запрос выглядит так:
В параметрах startDate и endDate указываются значения в таблице из пункта 3. При запросе «Для выполнения этого собственного запроса к базе данных необходимы разрешения» жмем «Редактировать разрешение», проверяем, что все параметры подтянулись корректно и выполняем запрос. Теперь полученный ответ от SQL-запроса можно обработать обычными формулами Excel в привычном вам виде.
5. Важно! Когда вы будете обновлять выгрузку в следующий раз, то это приходится делать следующим способом (другие почему-то дают ошибку):
— меняем даты в таблице из пункта 1
— заходим в меню Данные --> Подключения и нажимаем «Обновить все»:
В этом случае все запросы выполнятся корректно и ваши отчеты обновятся автоматически. Итого для обновления отчета вам потребуется только изменить параметры запроса и нажать «Обновить все».
1. Данные приходится собирать воедино из нескольких источников.
2. Отчеты составляются в Excel, что накладывает значительные ограничения на объем обрабатываемых данных.
3. Внесение изменений в заранее настроенные разработчиками выгрузки дело как правило не самое быстрое.
Если отчеты нужно обновлять еженедельно или даже ежедневно, то эта процедура становится весьма напряжной даже для самых терпеливых. С помощью надстройки Excel Power Query и записи данных в MySQL можно свести обновление большинства отчетов до простого нажатия кнопки «Обновить»:
1. Данные из любого количества источников импортируются через SQL-запросы в обычные таблицы Excel.
2. Даже из большой базы можно записывать в Excel только небольшую часть данных (например, итоговые суммы за нужный диапазон дат с группировкой только по нужным столбцам).
3. Изменения в отчет можно вносить просто поменяв SQL-запрос. Далее формируем нужный отчет стандартными средствами Excel.
В этой статье я покажу как настраивать и автоматически заполнять простые базы данных MySQL (на примере выгрузки статистики всех ключевых слов из Яндекс Метрики), а потом одной кнопкой обновлять отчеты в Excel, используя надстройку Power Query. Power Query имеет весьма странные особенности работы при составлении SQL-запросов (особенно динамических), которые мы разберем во второй части статьи.
Выбор MySQL (или любой другой популярной базы данных) вполне очевиден — бесплатно, относительно просто, возможность работать с довольно большими базами данных без технических хитростей. В качестве примера будем использовать Amazon Web Services: дешево (в большинстве случаев используемый инстанс будет бесплатен для вас в течение 12 месяцев).
Итак, начнем (если у вас уже есть базы данных с готовыми данными, то можно сразу переходить к разделу с Excel):
1. Регистрируемся на AWS (если еще нет учетки), запускаем самый простой инстанс t2.micro и заходим на него по SSH. Можно посмотреть краткую инструкцию в прошлом посте habrahabr.ru/post/265383. Обратите внимание, что нам потребуется первый в списке вариант инстанса на Amazon Linux AMI. Необходимо выставить правила, разрешающие обращение к инстансу по нужным портам:
В целях безопасности лучше выставлять ограничения на IP-адрес. Если у вас динамический IP, то это проблемная опция. Также иногда ограничение доступа к MYSQL по IP вызывает ошибку в Excel. Если выставить любой IP, то все работает.
2. Исполняем подряд команды, описанные в документацииdocs.aws.amazon.com/AWSEC2/latest/UserGuide/ec2-ug.pdf. Нам нужна глава «Tutorial: Installing a LAMP Web Server on Amazon Linux». Запомните пароль, который вводите при выполнении команды «sudo mysql_secure_installation». Для удобства установите phpMyAdmin как описано в конце этой главы. Если будете копипастить из документации строчку «sudo sed -i -e 's/127.0.0.1/your_ip_address/g' /etc/ht tpd/conf.d/phpMyAdmin.conf», то обратите внимание, что иногда при копировании в «httpd» появляется лишний пробел.
После этих действий на вашем инстансе должна открываться такая страница:
3. Заходим под пользователем root и паролем, который вводили при настройке. Для доступа к базе данных «извне» (т. е. из Excel) нам потребуется пользователь, отличный от root. Заводим его в интерфейсе phpMyAdmin в меню Пользователи --> Добавить пользователя. Добавим пользователя stats, зададим пароль и назначим ему привилегии SELECT и INSERT. Итого получим:
4. Теперь создадим базу данных data:
5. В данном примере будем наполнять базу статистикой посещений по ключевым словам из Яндекс Метрики. Для этого создадим таблицу seo (обратите внимание, что у столбца id надо отметить опцию A_I (auto increment)):
6. Для получения статистики по ключевым словам из Яндекс Метрики можно использовать следующий скрипт. В качестве параметров нужно указать начальную и конечную дату выгрузки (переменные $startDate и $endDate), авторизационный токен (в коде есть описание как его получить), номер счетчика, из которого нужно получить статистику, и параметры базы данных: ID инстанса, логин (у нас «stats»), пароль и название базы (у нас «data»). Скопируйте в корневую папку инстанса этот код и запустите командой «php seo.php».
Код PHP для выгрузки данных Яндекс Метрики
Если возникнут ошибки при соединении с базой, то они отобразятся в консоли и выполнение будет прервано. В случае успешного выполнения получим статистику ключевых слов за выбранный период:
Отлично, данные получены. Посмотрим как получать их в Excel.
Использование Power Query для выгрузки данных в Excel
Power Query представляет собой надстройку, которая расширяет возможности Excel по выгрузке данных. Скачать можно тут www.microsoft.com/en-us/download/details.aspx?id=39379. Для работы с MySQL может потребоваться MySQL Connector и Visual Studio (предлагаются при установке из дистрибутива).
1. После установки выбираем MySQL:
2. В качестве базы указываем ID нашего инстанса (как было в скрипте) ec2-....compute.amazonaws.com. База данных data. Для ввода логина выбираем «База данных»:
3. В открывшемся окне дважды кликаем на таблицу seo и получаем:
В этом окне можно управлять запросами, изменяя столбцы и количество строчек. Когда база данных небольшая, то это работает. Однако если размер данных превышает даже 20MB, то Excel на большинстве компьютеров просто повиснет от такого запроса. К тому же неплохо бы менять даты запроса или другие параметры.
Динамические запросы в Power Query можно делать с помощью встроенного языка Mmsdn.microsoft.com/en-us/library/mt253322.aspx, однако запросы крайне неустойчивы в плане изменения каких-либо параметров в них. Чтобы запрос оставался «постоянным» сделаем следующий прием:
1. Сначала составляем таблицу, в которой указываем нужные нам параметры. В нашем примере это дата выгрузки. Формат ячеек со значениями лучше выставить как тестовый, т. к. Excel любит изменять формат ячеек по своему усмотрению:
2. Создадим запрос Power Query «Из таблицы», который будет просто дублировать эту таблицу:
3. В опциях запроса обязательно укажите формат второго столбца как Текст, иначе последующий SQL-запрос будет некорректным. Далее жмем «Закрыть и загрузить».
Итого мы получили запрос Power Query к обычной таблице, из которого будет брать значение начала и конца выгрузки.
Чтобы сделать SQL-запрос потребуется отключить одну опцию: заходим в Параметры и настройки --> Параметры запроса --> Конфиденциальность и выбираем «Игнорировать уровни конфиденциальности для возможного улучшения производительности». Жмем Ок.
4. Теперь делаем запрос к нашей базе данных, указывая в качестве начала и конца периода значения таблицы из пункта 3. Снова подключаемся к базе в Power Query и нажимаем «Расширенный редактор» в меню.
Например, мы хотим получить сумму визитов, которые принесли ключевые слова, содержащие «2015». На языке M запрос выглядит так:
let Source = MySQL.Database("ec2-....compute.amazonaws.com", "data", [Query="select sum(visits) from seo where startDate>='"&Text.From(Таблица1{0}[Значение])&"' and endDate<='"&Text.From(Таблица1{1}[Значение])&"' and query like '%2015%';"]) in Source В параметрах startDate и endDate указываются значения в таблице из пункта 3. При запросе «Для выполнения этого собственного запроса к базе данных необходимы разрешения» жмем «Редактировать разрешение», проверяем, что все параметры подтянулись корректно и выполняем запрос. Теперь полученный ответ от SQL-запроса можно обработать обычными формулами Excel в привычном вам виде.
5. Важно! Когда вы будете обновлять выгрузку в следующий раз, то это приходится делать следующим способом (другие почему-то дают ошибку):
— меняем даты в таблице из пункта 1
— заходим в меню Данные --> Подключения и нажимаем «Обновить все»:
В этом случае все запросы выполнятся корректно и ваши отчеты обновятся автоматически. Итого для обновления отчета вам потребуется только изменить параметры запроса и нажать «Обновить все».
