Одна из самых трудоемких задач, от которой мечтают отказаться многие аккаунты, - это подготовка регулярной отчетности для клиентов. Однако этой работы не избежать, она появляется в любом случае и обременяется довольно жестким дедлайном. Зачастую клиенты просят предоставлять еще и ежедневные отчеты. Все эти факторы влияют на аккаунт-менеджера, отнимают время, отведенное на аналитику и оптимизацию рекламы.
А теперь давайте подумаем, часто ли Вы задумывались над тем, как можно сократить тайминг на подготовку отчетности? Мало кто знает, что с помощью довольно простых и доступных инструментов можно оптимизировать эти процессы: сбор аналитических данных будет происходить автоматически по тем параметрам, которые прописывает сам аккаунт-менеджер.

Например, возьмем клиента из сферы авто. Он размещается в Яндекс.Директ и Google AdWords, в каждом аккаунте работает реклама по направлениям «Продажи» и «Сервис». Также подключена система коллтрекинга (CallTouch) и сервис обратного звонка. Задача аккаунт-менеджера состоит в оптимизации рекламных кампаний, которая достигается при помощи сбора данных через специальные сервисы аналитики. Подготовка такого отчета вручную для одного клиента занимает около 30 минут ежедневно, а в понедельник в 3 раза дольше, поскольку необходимо собрать данные за выходные.
Для того, чтобы решить задачу автоматизации при формировании отчетов, есть огромное количество инструментов и сервисов. Мы расскажем Вам про один из них – сервис Owox.com.

По сути все, что требуется для выполнения задачи в отсутствие аккаунта в Директе, - связанные аккаунты Analytics или AdWords. Но поскольку задача немного сложнее, рекомендуем воспользоваться импортом расходов в Analytics из Директа с помощью Owox.com. Основная идея заключается в том, чтобы собрать все данные в одной системе (Google Analytics) и после этого обрабатывать их с помощью Google.Sheets в соответствии с поставленной задачей. Одним из приятных побочных эффектов такой таблицы является актуальная информация по расходам, где учитываются возвраты за недействительные клики. Когда мы сравнили к концу месяца расходы, которые собирались вручную каждый день, и те, что попадали в выгрузку автоматически, то заметили разницу более чем в 10% бюджета. Выяснилось, что эта разница образовалась как раз за счет недействительных кликов, которые при сборе отчета вручную ускользали от нашего внимания. В свою очередь, сервис Owox.com позволяет каждый раз выгружать данные за весь период, а не только за определенный промежуток времени.
 
Итак, перейдем к алгоритму. Шаг первый: импорт расходов в Analytics из Директа с помощью Owox.com.

В интернете можно встретить массу однотипных пошаговых инструкций, но мы расскажем вам о более простом алгоритме. В первую очередь, необходимо подготовить Analytics. Для этого потребуется зайти в нужный нам раздел настроек – представление, затем перейти в панель администратора, а далее – в «Импорт данных». Здесь создаем новый набор данных -> «Импорт сводных данных» -> «Данные о расходах» и совершенно произвольно переименовываем поле, например, в «Директ». Теперь выбираем нужное представление, собираем схему набора данных (стоимость, число кликов и показов рекомендуем выбрать сразу, остальные параметры – по желанию; это зависит от того, какие показатели нужно будет сводить. Экспериментируйте!).

Затем необходимо зарегистрироваться в Owox (если вы зашли на этот сервис впервые), после чего в личном кабинете создать новый поток, выбрать Директ и нажать кнопку «Далее». Обратите внимание, что вам потребуется предоставить доступ к Яндекс.Директу. Рекомендуем войти через клиентский логин. После этих действий вам потребуется выбрать аккаунт Analytics => набор данных без исключения НДС (если созданный аккаунт не появится, обновите окно соответствующей кнопкой). Затем создаем новый проект- так будет удобнее ориентироваться в структуре, когда у вас уже накопится несколько проектов. Готово! Теперь Owox будет периодически затягивать в Analytics данные из Директа.

Шаг второй: связь аккаунтов AdWords и Analytics.

Шаг третий: установка дополнения в Google.Sheets.
Для этого необходимо открыть любую таблицу в Google.Sheets, выбрать в иконках меню «Дополнения» и нажать «Установить дополнения». После этих действий откроется магазин, в котором вам предложат использовать сервис Google Analytics.

Существует еще одно дополнение – Supermetrics, однако мы расскажем о способах настройки отчетов на примере «Google Analytics».

Шаг четвертый: настройка отчета.
Теперь у нас есть все необходимые компоненты для того, чтобы выгрузить данные в таблицу. Для этого жмем на «Дополнения» -> Google Analytics -> «Создать новый отчет». Справа появится окно, в котором вам потребуется указать:
1) Имя отчета (можно придумать любое имя);
2) Аккаунт, ресурс и представление в Analytics;
3) Метрики и срезы (например, в качестве метрик выбираем количество кликов и расход, а в качестве срезов - источник, дату и кампанию).

Создаем отчет и получаем таблицу:

В этой таблице мы можем вручную менять параметры отчета: добавлять/удалять метрики и срезы, включать сортировку и фильтры (об этом в одной из следующих статей). Мы настоятельно рекомендуем в конфигурации «Max Results» прописать число «10 000», т.к. по умолчанию выгружается только тысяча строк, и при большем их количестве вы не увидите часть данных. К сожалению, на данный момент у Google.Sheets есть ограничение – максимально допустимое количество строк равно 10 000, поэтому, если вы пропишите более высокое значение, программа этого не учтет.

Стоит также взять в расчет, что дата старта и окончания сбора статистики и «Last N Days» противоречат друг другу. Иными словами, если в первой конфигурации начальным и конечным периодом будет выбран сентябрь прошлого года, а в «Last N Days» будет прописано значение 10, то все равно выгрузятся данные только за последние 10 дней.
Итак, после учета всех нюансов нажимаем на иконку «Дополнения» -> Google Analytics -> Run report. Через некоторое время Google выдаст окно, в котором появится информация, был сформирован отчет или нет.

Как следствие, получаем таблицу с большим количеством данных. С этой информацией можно работать вручную - фильтровать, сортировать и сводить.

Шаг пятый: сведение отчета.
Стоит отметить, что у данного дополнения в русифицированной версии есть небольшая погрешность: разделителем дробной части в России считается запятая, а на Западе и в Америке – точка, поэтому значение расхода в таблице выгружается по зарубежным стандартам и читается как текст. Это приводит к невозможности проводить математические расчеты. Чтобы привести данные к числовому виду, необходимо заменить точку на запятую, а затем добавить комиссию и при необходимости – НДС.

Т.к. при каждом создании отчета данные в листе будут очищаться, нам потребуется создать рабочую страницу для того, чтобы каждый раз не приходилось менять дробные знаки. Создание рабочей страницы напрямую связано с пугающей многих функцией query. Она позволяет работать с большим объемом данных и по функционалу напоминает SQL (некая формула, которая «забирает» данные из таблицы и на выходе предоставляет другую таблицу, являющуюся результатом работы этой самой формулы). Ниже мы подробно опишем все функции, которые можно использовать в этом дополнении.
Итак, в первую очередь, нам потребуется перенести на рабочий лист всю выгрузку из систем. Для этого создаем новый лист и в ячейке А1 прописываем следующую формулу:

=query(\'тест\'!A15:E10000;
"Select
A,B,C,D,E")

Обратите внимание, что переносы строк роли не играют, однако в таком порядке читать формулу намного удобнее. Суть этой формулы такова – забрать с листа «тест» из массива А15:Е10000 столбцы «A», «B», «C», «D», «E» и вставить их в рабочий лист.

На этом этапе нередко возникают проблемы с дробным разделителем, о котором уже говорилось выше. Поэтому мы предлагаем воспользоваться формулой =ПОДСТАВИТЬ(). Рекомендуем употребить ее дважды, поскольку информация о расходах переносится и с AdWords, и с Директ. Таким образом, мы забираем из выгрузки на новый лист пять столбцов. Для понимания назовем столбец «F» как «Расход Директ», а столбец «G» как «Расход AdWords». После этого в ячейке F2 пишем формулу:
=ПОДСТАВИТЬ(E2;".";",")*1

Благодаря умножению на 1 она сразу преобразовывает информацию в цифровой формат и заменяет точку на запятую в столбце E2. Если Ваша комиссия зависит от оборота, ее можно включить в эту же формулу, например, как в AdWords (на примере комиссии в 10%):
=ПОДСТАВИТЬ(E2;".";",")*1,298

Эту формулу потребуется вписать в ячейку G2. Она позволит заменить точку на запятую в столбце расхода, а также умножит данные на выведенный коэффициент, включающий в себя комиссию 10% и НДС. Эти две формулы можно свести в одну при использовании условия «ЕСЛИ» и регулярных выражений. Но об этом в наших следующих материалах.
Итак, мы получили данные, которые можно сводить с помощью функции query. По условию задачи нам необходимо собрать расходы и клики в разрезе сервиса и продаж за каждый день по Директу и AdWords. Для этого готовим шапку (исключительно для себя), например, «Директ продажи», «AdWords продажи», «Директ сервис», «AdWords сервис».

В ячейке H2 прописываем следующую формулу:
=query(A1:G10000;"
select
A, sum(D), sum(F)
where
C like \'yandex%\' AND (not B like \'Бренд%\' AND not B like \'Сервис%\')
GROUP BY A
ORDER BY A
LABEL
A \'Дата\',
sum(D) \'Клики\',
sum(F) \'Расход\'
")

Выглядит непонятно, правда? Но в этом нет ничего сложного. Ниже мы опишем каждую строку подробнее:

• A1:G10000 - объем ячеек в листе, из которого забираются данные
• select A, sum(D), sum(F) - столбцы D и F, которые суммируются с учетом фильтров (столбец А остается неизменным)
• where - описание фильтра
• C like \'yandex%\' AND (not B like \'Бренд%\' AND not B like \'Сервис%\') – фильтр, сообщающий о том, что необходимо взять только те строки, в которых текст в ячейке «C» начинается со слова «yandex», а строка в ячейке «B» не начинается со слов «Бренд» или «Сервис». Знак процента здесь играет роль звездочки (поиск неограниченного количества символов) в Excel
• GROUP BY A - сведение по столбцу А (дата),
• ORDER BY A - сортировка по столбцу А (дата),
• LABEL – служебное слово, предназначено для того, чтобы названия столбцов стали удобочитаемыми
• A \'Дата\', - заголовок столбца А
• sum(D) \'Клики\', - заголовок суммы по столбцу D,
• sum(F) \'Расход\' - заголовок суммы по столбцу F.

Согласитесь, все не так сложно, как кажется на первый взгляд? Теперь по такому же принципу создаем остальные таблицы и прописываем в той таблице, к которой у клиента имеется доступ, следующую формулу
=IMPORTRANGE("17mUp-pdwy8xGv5_Fp3p_H4ggfV-jBuJD1NJ9X-qZ3Ik"; "тест!I3:J32")
здесь 17mUp-pdwy8xGv5_Fp3p_H4ggfV-jBuJD1NJ9X-qZ3Ik - ключ таблицы, а именно:

тест!I3:J32 - лист и диапазон данных для импорта.

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

Вот и все! Теперь Owox будет ежедневно затягивать данные в Analytics, а дополнение к Таблицам – выкачивать и формировать отчет, необходимо лишь включить расписание.
Если у Вас правильно настроены конверсии в Analytics, их также можно вытягивать в таблицы и сводить с помощью функции query, достаточно лишь прикрепить эти конверсии к импорту из Analytics в настройках отчета. Итак, мы показали вам один из нескольких простых способов сбора информации о расходах. Единственное, что нужно учесть - таблицы могут начать вытягивать данные еще до того, как они попадут с помощью Owox в Analytics. Поэтому Вам потребуется настроить расписание на более позднее время или периодически проверять и запускать отчеты вручную.

Здорово, правда? Один раз настроил отчет и сэкономил своё время! И это далеко не единственная задача, которую можно автоматизировать с помощью современных инструментов. Но об этом в наших следующих кейсах.

Мы желаем вам чаще находить гибкие решения, которое способны оптимизировать ваше рабочее время! До встречи на страницах новых материалов!