Моя первая статья из серии «Работа с Query Explorer» была посвящена обзору инструмента Query Explorer, позволяющего сделать выгрузку отчетов, недоступных в классическом веб-интерфейсе GA. С его помощью можно выгружать большие объемы данных (10 000 строк за один запрос), создавать отчеты, содержащие более двух параметров, а также сохранять эти отчеты в формате TSV.

В предыдущей статье я лишь упомянул Excel в качестве одного из мощнейших инструментов для аналитики. В этой статье я поделюсь множеством способов анализа данных Google Analytics за пределами веб-интерфейса, а конкретно в Microsoft Excel. Приведенные примеры, в первую очередь, связаны с анализом данных бесплатного поискового трафика, так как эти отчеты зачастую содержат огромное количество ключевых слов и целевых страниц, среди которых необходимо искать общности и анализировать их, затрачивая минимум времени.

1. Отчет по месяцам

Наверняка многие сталкивались с необходимостью построить таблицу динамики различных параметров из месяца в месяц за определенный период. Примером такого отчета может быть динамика посещаемости целевых страниц из поиска за 3 месяца. Попробуем построить данный отчет.

Для начала нам необходимо сформировать запрос к API с помощью Query Explorer:

В поле dimensions мы указываем два измерения: ga:month и ga:landingPagePath, где month отвечает за разбиение данных по месяцам, а landingPagePath указывает на URL-адрес посадочной страницы. Поле metrics будет содержать в себе только метрику посещений – ga:visits. Для сегментации неоплачиваемого поискового трафика я указал соответствующий сегмент. Неизвестно, сколько строк будет содержать наш отчет, поэтому в max-results указываем максимально допустимое значение – 10 000 строк.

После того как запрос сформирован, нажимаем «Get Data» и API возвратит нам данные, соответствующие нашему запросу.

Чтобы выгрузить данные в формате TSV, нужно нажать на кнопку «Excel TSV», после чего будет осуществлена загрузка отчета «query_explorer.tsv».

Данный файл открываем в Excel:

TSV-отчет, сгенерированный с помощью Query Explorer, состоит из двух частей: описания структуры запроса (данные об использованном профиле и остальных параметрах) и таблицы ответа API.

Сначала скопируем таблицу ответа API на новый лист.

Выделив всю таблицу, внесем ее данные в сводную таблицу. Для этого на вкладке «Вставка» необходимо выбрать «Сводная таблица»:

Поместим сводную таблицу на новый лист, после чего будет предложено распределить поля таблицы по областям сводной таблицы. Поле ga:month перенесем в «Названия столбцов», ga:landingPagePath – в «Названия строк», а ga:visits – в «Значения»:

Итогом будет создание сводной таблицы, в столбцах которой представлены месяцы, а в строках – целевые страницы, на пересечении будет находиться количество посещений страницы из поиска в определенный месяц:

Сводные таблицы являются мощнейшим инструментом аналитики, т.к. позволяют производить множество быстрых манипуляций с данными. Например:

Сортировка по общему итогу. Для этого нужно поставить курсор на первое значение столбца «Общий итог» и выбрать «Сортировка» на панели «Параметры», после чего можно отсортировать сводную таблицу по убыванию или возрастанию значений в выбранном столбце:

Выполнение дополнительных вычислений. Выбрав опцию «Дополнительные вычисления» на вкладке «Параметры», можно быстро производить различные вычисления. Например, можно рассчитывать значения в процентных долях от общей посещаемости или от посещаемости в текущем месяце, как в примере ниже:

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

После этого выделяем необходимые страницы и нажимаем «Группа по выделенному» на вкладке «Параметры»:

Как уже говорилось ранее, сводные таблицы являются мощным инструментом анализа табличных данных, поэтому они хорошо подходят для анализа ответов API GA. Подробнее с другими приемами работы со сводными таблицами можно ознакомиться в справке Microsoft Excel.

2. Распределение по месяцам и поисковым системам

Сформируем новый запрос, для того чтобы добавить распределение по поисковым системам в наш предыдущий отчет:

В поле dimensions мы добавляем измерение ga:source (источник). Стоит обратить внимание, что в поле filters указано ga:medium==organic, что означает фактически сегмент бесплатного поискового трафика, так как задает условие, что канал трафика должен точно соответствовать organic.

После того как API возвратит данные, необходимо снова сформировать сводную таблицу, которая включала бы измерение ga:source как строку. Далее можно создать так называемый drill-down (раскрывающийся) отчет:

3. Сегментация данных с помощью формулы =СУММЕСЛИМН

С помощью формулы =СУММЕСЛИМН в Excel можно осуществлять достаточно любопытную сегментацию данных GA. Она позволяет нам создать таблицу, которая будет отражать сразу несколько сегментов данных. На формирование аналогичного отчета в интерфейсе GA требуется гораздо больше времени, т.к. необходимо формировать ряд расширенных сегментов с помощью нескольких формул. В качестве примера рассмотрим создание вот такого отчета:

Чтобы сформировать подобный отчет в интерфейсе GA, нам бы понадобилось создать 6 пользовательских сегментов. С помощью Query Explorer и Excel составить такой отчет можно немного быстрее.

Снова формируем запрос к API:

В поле dimensions включаем три измерения: ga:source, ga:landingPagePath (путь к посадочной странице), ga:city (город).

Выгружаем полученные данные в TSV и открываем файл в Excel.

На другом листе Excel файла выгрузки начнем формирование необходимого отчета.

Для начала оформим шапку таблицы и добавим в первую колонку посадочные страницы, для этого скопируем их из ответа API (колонка ga:landingPagePath). После вставки в новый лист нужно будет удалить дубликаты (вкладка «данные» > «удалить дубликаты»):

После того как дубликаты удалены, приступим к написанию формул для подсчета. Итак, формула =СУММЕСЛИМН суммирует в таблице определенные значения в том случае, если они соответствуют заданным условиям.

Для столбца «Посещения из Google Москва» формула будет иметь следующую структуру:

=СУММЕСЛИМН (Столбец Visits; Столбец City; “Moscow”; Столбец Source; “Google”; Столбец LandingPagePath; Посадочная страница

Первый аргумент функции – диапазон суммирования значений, в нашем случае это столбец, содержащий данные о посещениях. Следующий аргумент – диапазон условия 1, здесь мы указываем столбец City, содержащий данные о городах посещений. Затем указываем условие 1 – «Moscow», то есть то условие, при котором значения будут суммироваться (суммироваться будут только значения Visits в тех строках, где City соответствует Moscow). Еще одной парой диапазон-условие будет Source-Google (диапазон: столбец «Source», условие: «Google»). Последней парой будут столбец «LandingPagePath» и строка «Посадочная страница» в нашем отчете. Это условие необходимо, чтобы суммировать данные только по определенной посадочной странице.

В столбце «Посещения из Яндекс Москва» нужно указать такую же формулу с той лишь разницей, что вместо Google должна быть указана ПС Yandex:

=СУММЕСЛИМН (Столбец Visits; Столбец City; “Moscow”; Столбец Source; “Yandex”; Столбец LandingPagePath; Посадочная страница)

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

=СУММЕСЛИМН (Столбец Visits; Столбец City; “Moscow”; Столбец LandingPagePath; Посадочная страница) – СУММ («Посещения из Google Москва»; «Посещения из Яндекс Москва»)

Таким образом, мы задали формулы вычисления разности всего трафика из ПС по Москве и трафика по Москве из поисковых систем Google и Яндекс. Как видите, из формулы СУММЕСЛИMН исчезло условие соответствия поисковой системы.

Формула в столбце «Посещения из Google из остальных регионов» должна просчитывать сумму трафика при условии, что он из ПС Google, за вычетом трафика из Москвы:

=СУММЕСЛИМН (Столбец Visits; Столбец Source; “Google”; Столбец LandingPagePath; Посадочная страница) – «Посещения из Google Москва»

Аналогичную формулу добавляем в столбец «Посещения из Яндекс из остальных регионов», но заменяем Google на Яндекс:

=СУММЕСЛИМН (Столбец Visits; Столбец Source; “Yandex”; Столбец LandingPagePath; Посадочная страница) – «Посещения из Яндекс Москва»

Чтобы просчитать оставшуюся часть трафика, которая должна быть в столбце «Посещения из остальных ПС из остальных регионов», необходимо просуммировать посещения по посадочным страницам и вычесть из этой суммы остальные столбцы:

=СУММЕСЛИМН (Столбец Visits; Посадочная страница) – СУММ (Столбцы 2-5);

Итак, наша таблица готова:

Ссылка на excel файл.

4. Подсчет количества ключевых слов с указанной посещаемостью

Любопытные данные можно получить, используя формулу =СЧЁТЕСЛИ для подсчета количества ключевых слов, обладающих определенной посещаемостью. Например, можно посчитать количество ключевых слов, обладающих посещаемостью меньше или равной 5:

=СЧЁТЕСЛИ (Столбец Visits;”<=5”)

Или количество ключевых слов с одним посещением:

=СЧЁТЕСЛИ (Столбец Visits;1)

5. Подсчет количества слов в запросе

С помощью формулы подсчета количества слов можно определить количество слов в запросе. Но прежде чем это сделать, необходимо удалить стоп-слова. Определить, какие стоп-слова встречаются в списке наших запросов, можно с помощью сервиса семантического анализа текста от Advego. Добавляем туда весь список запросов, и в графе «стоп-слова» будет представлен их полный список:

Затем, используя функцию «Заменить», удаляем эти стоп-слова, заменяя их на знак пробела. При этом важно, чтобы перед и после стоп-слова стоял знак пробела:

Следующим шагом будет использование специальной функции для подсчета количества слов в ячейке:

=ЕСЛИ(ДЛСТР(СЖПРОБЕЛЫ(A1))=0;0;ДЛСТР(СЖПРОБЕЛЫ(A1))-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))+1)

Далее мы можем определить, каких ключевых слов больше, и какова посещаемость с них. Сначала скопируем данные из столбца «Количество слов» и удалим дубликаты, для того чтобы оставить уникальные показатели количества слов в запросе:

Затем с помощью формулы =СЧЁТЕСЛИ посчитаем количество запросов, соответствующих определенному количеству слов:

=СЧЁТЕСЛИ (столбец «Количество запросов»; строка «Количество слов»)

А затем аналогично формуле =СЧЁТЕСЛИ применим формулу =СУММЕСЛИ для столбца «Посещения»:

=СУММЕСЛИ (столбец «Количество запросов»; строка «Количество слов»; столбец «ga:visits»)

В итоге получаем вот такое распределение:

Также посчитать количество слов можно, разбив каждый запрос на ячейки по словам, с помощью функции «Текст по столбцам», доступной на вкладке «Данные».

Ссылка на excel файл.

6. Добавление данных Xenu

Воспользовавшись программой Xenu’s Link Sleuth, мы можем собрать массу данных, которые можем связать с данными ответа API. Например, можно узнать, на страницы с какой глубиной вложенности приходится больше всего трафика, или поставить в соответствие каждой посадочной странице мета-тег Description, полученный с помощью Xenu.

Выводы

В первой статье из серии «Работа с Query Explorer» мы рассмотрели принципы работы инструмента выгрузки данных с помощью API Google Analytics. В этой статье мы провели обзор вариантов использования выгруженных данных, рассмотрели несколько примеров того, как связка Google Analytics API и Microsoft Excel может помочь в анализе данных о посещаемости веб-сайта. Некоторые из представленных примеров подходят для формирования нестандартной отчетности, другие могут позволить сделать аналитические выводы, которые приблизят вас к успеху. Экспериментируйте, осваивайте новые функции MS Excel, интегрируйте данные Google Analytics с собственными данными, выходите за рамки веб-интерфейса. Удачи!

Автор: Алексей Макаров, ведущий специалист по аналитике, Russian Promo.