Всем привет.
Наступил новый 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».

Код PHP для выгрузки данных Яндекс Метрики

<?php

// начало и конец периода выгрузки
$startDate = '2015-10-01';
$endDate = '2015-12-31';

// токен для запросов к API Яндекс Метрики
// для получения токена создаем приложение на https://oauth.yandex.ru/
// даем приложению разрешение на чтение статистики Яндекс Метрики
// после создания берем параметр ID приложения и подставляем в конец https://oauth.yandex.ru/authorize?response_type=token&client_id=
// жмем Разрешить и получаем токен
$atoken = '';

// номер счетчика
$project = '';

// соединяемся с базой данных и проверяем все ли в порядке
$con = mysqli_connect("ec2-....compute.amazonaws.com","stats","здесь пароль", "data");

if (mysqli_connect_errno()) {
	echo "Failed to connect to MySQL: " . mysqli_connect_error();
	exit();
} else {
	echo "Connection successfull \n";
}

if ($con->query("SET NAMES 'utf8'")) {
	echo "Names set \n";
} else {
	printf("Error: %s\n", $con->error);
	exit();
}

// файл, в который записываем результаты выгрузки (в случае ошибки записи в базу данных по нему можно увидеть какая строчка записалась последней и соответственно дала ошибку)
$fname = "data.txt";
$fp = fopen($fname, "w");
fclose($fp);

function getBatch($startDate, $endDate, $project, $offset, $limit, $atoken, $fname, $con) {
	// делаем запрос к API Яндекс Метрики
	// документация https://tech.yandex.ru/metrika/doc/api2/api_v1/attributes/visitssource_/search-docpage/
	$ch = curl_init();
	$options = array(
		CURLOPT_URL => 'https://api-metrika.yandex.ru/stat/v1/data?oauth_token='.$atoken.'&id='.$project.'&accuracy=full&date1='.$startDate.'&date2='.$endDate.'&dimensions=ym:s:<attribution>SearchPhrase&metrics=ym:s:visits&limit='.$limit.'&offset='.$offset,
		CURLOPT_RETURNTRANSFER => TRUE
	);

	curl_setopt_array($ch, $options);
	$data = curl_exec($ch);
	curl_close($ch);

	// переводим ответ запроса формата JSON в массив
	$data = json_decode($data, true);

	// смотрим какой ответ получили от Яндекс Метрики
	//var_dump($data);

	// проходим по массиву $data['data'] и записываем период выгрузки, ключевую фразу и количество визитов
	for ($i=0; $i < count($data['data']); $i++) {
		file_put_contents($fname, $startDate."\t".$endDate."\t".$data['data'][$i]['dimensions'][0]['name']."\t".$data['data'][$i]['metrics'][0]."\n", FILE_APPEND);

		if ($con->query("INSERT INTO seo (startDate, endDate, query, visits) VALUES ('".$startDate."', '".$endDate."', '".mysqli_real_escape_string($con, substr($data['data'][$i]['dimensions'][0]['name'], 0, 255))."', ".$data['data'][$i]['metrics'][0].")")) {
			//echo "Record done \n";
	    } else {
	    	printf("Error: %s\n", $con->error);
	    	exit();
	    }
	}

	if (count($data['data']) == 0) {
		return 'done';
	} else {
		return 'more';
	}
}

// https://tech.yandex.ru/metrika/doc/api2/api_v1/data-docpage/
// максимальное количество строк в одной таблице - 10 000
// в цикле выгружается по $limit строк, увеличивая при этом первую строчку выгрузки $offset на значение $limit
$offset = 1;
$limit = 10000;

$res = 'more';
do {
	$res = getBatch($startDate, $endDate, $project, $offset, $limit, $atoken, $fname, $con);
	$offset += $limit;
} while ($res == 'more');

mysqli_close($con);

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

Отлично, данные получены. Посмотрим как получать их в 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 можно делать с помощью встроенного языка M msdn.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
— заходим в меню Данные --> Подключения и нажимаем «Обновить все»:

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

Подводные камни использования Excel Power Query и MySQL для автоматизации отчетности