Сквозная статистика по показам в регионах для разных рекламных систем

Задача

Одному клиенту (крупной аптечной сети) мы ведем контекстную рекламу на платформе К50. Бизнес представлен в разных регионах РФ. Регионы по-разному бюджетируются, различаются проводимые стимулирующие акции, аудитория количественно непохожа, поэтому где-то рекламы оказывается больше, а где-то – меньше.

Для собственного удобства мы создаем кампании, каждая из которых таргетируется на отдельный регион. Поскольку количество товарных позиций достигает десятков тысяч в десятках товарных групп и в одну кампанию они не умещаются, для разных регионов создаются несколько кампаний.Заказчик попросил дополнить данные по расходам на рекламу данными из электронной торговли Google Analytics по прямым и ассоциированным конверсиям. Нет совершенно никакой проблемы объединить статистику по рекламе Google с данными Google Analytics, но есть Яндекс.Директ, Facebook, «ВКонтакте», MyTarget, кучка программатиков и много чего еще. Что с ними не так?

Вопрос простой и одновременно сложный.

География с точки зрения рекламных систем

«ВКонтакте»

В отчетах рекламных систем одни и те же географические регионы могут по разному называться и иметь разную детализацию. Например, такие демографические данные предоставляет «ВКонтакте».

То есть максимум вы получите статистику по 15 самым сильно «засветившимся» городам. Разумеется, если просуммировать все доли показов и кликов (в процентах от общего количества, которое нужно смотреть в другом отчете), то 100 % вы скорее всего не получите. Недостающие проценты забирают «другие». API также не предоставляет расширенную статистику.

MyTarget

Второй продукт mail.ru не ограничивается пятнадцатью городами и регионами и позволяет выгрузить вот такую статистику.

Здесь представлены регионы, города и даже Россия вперемешку (на скриншоте этого не видно, но это действительно так). Попробуйте теперь внутри одной рекламной системы статистику по Архангельску просуммировать со статистикой Архангельской области. Сначала нужно понять, что из них внутрь чего входит, а потом – реализовать понятое в общем отчете.

Реклама Google

Замечательны отчеты по рекламе Google! Тут можно одновременно увидеть город и регион, в котором он находится. Сначала выгружаем данные с максимальной детализацией (до города), а потом редуцируем их до региона в финальном отчете, если понадобится.

Яндекс.Директ

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

Обратите внимание на Алексеевский район на скриншоте. Как вы думаете, где в России есть Алексеевский район?

Варианты ответа:

1. Москва

2. Белгородская область

3. Волгоградская область

4. Самарская область

5. Все варианты неверны

6. Все варианты верны

Правильный ответ – F.

Что же имеет в виду Яндекс? По отчету из мастера отчетов ответить на этот вопрос невозможно, если не знать наверняка (но точно не Москву). В данном случае было бы лучше, если бы данные ушли по иерархии выше и попали в результаты всей области (как в myTarget), а не отдельного района. Подобное поведение часто приветствуется и для маленьких городов – излишняя детализация не всегда нужна.

А еще в России есть как минимум 3 города Советск!

Facebook и Instagram

«Буржуйский» Facebook (как и большинство programmatic) суммирует данные по всему региону.

Человеку понятно, что Костромская область из MyTarget и Google – то же самое, что Kostroma Oblast в Facebook. 

Вообще в последнее время я все реже сталкиваюсь с тем, что одни и те же субъекты называются по-разному (например, Республика Чувашия и Чувашская Республика). Раньше подобное встречалось сплошь и рядом: перестановка слов, замена частей речи, буквы е и ё и другие нюансы, легко распознаваемые человеком. Но не сводными таблицами, которые безжалостно покажут все варианты названий одного и того же субъекта.

Устройство государства Российского

Обратимся к истокам и посмотрим на Россию как на государство.

Те, кто постарше, вероятно застали и пользовались такой монструозной вещью, как КЛАДР – КЛассификатор АДРесов РФ. Его создавали, чтобы присвоить каждому объекту недвижимости (от региона до отдельной квартиры) собственный уникальный идентификатор. Попробуйте предположить, сколько в России квартир и какой это объем данных!

Заявлялось, что база не лишена недостатков и неоднозначностей, поэтому прямой наследницей КЛАДР стала база ФИАС – Федеральная Информационная Адресная Система. Но для решения заявленной в самом начале статьи задачи лучше всего подходит именно КЛАДР. Сейчас объясню!

Идентификатор КЛАДР может выглядеть вот так: 77000000000, 770000000003507, 030150000150003. Его минимальная длина – 11 знаков.

Читается он слева направо. Я записал коды примеров в таблицу и пронумеровал каждую цифру.

1. 1–2 цифры показывают код региона: сюда входят области, края, республики, автономные округа и т. д. (например, 77 – код Москвы, 03 – код Бурятии).  Да, регионы на автомобильных номерах совпадают с кодами регионов в КЛАДР. За исключением тех случаев, когда первых перестает хватать (тогда к номерам добавляют, например, единичку, и 50 становится 150).

2. 3–5 цифры показывают код района области или другого места аналогичного уровня (условно назовем его ареалом). В Москве их 19 (например, 012 – п. Мосрентген, а 011 – п. Московский). В двух первых приведенных примерах стоят 000 – это значит, что объект не относится к ареалу, т. к. это некая «главная» агломерация. А 015 в Бурятии – это Окинский район.

3. 6–8 цифры показывают код города или аналогичного места (деревни, поселка и т. п.). Во всех примерах 000 – здесь снова подразумевается «главная» агломерация. В Москве на этом уровне детализации находятся, например, Щербинка, Троицк и Зеленоград, поэтому в адресе они записываются: г. Москва, г. Щербинка и т. д.

4. 9–11 цифры обозначают район города или более маленького населенного пункта. В первых двух примерах они отсутствуют, в третьем это 015. Улус Зун-Холба настолько небольшой, что Google и Яндекс на своих картах не показывают внутри ни одной улицы.

5. 12–15 цифры могут отсутствовать, если в коде КЛАДР указана детализация до города или аналогичной агломерации. То есть на этом разбор первого кода закончен. 3507 во втором коде – это ул. Фридриха Энгельса, на которой находится наш офис. В третьем случае 003 – это переулок Гадюкино. Да, деревни Гадюкино в России пока, судя по всему, нет.

В результате расшифровки кода КЛАДР получаем:

• 77000000000 – регион Москва (остальные нули означают, что имеется в виду именно регион);

• 770000000003507 – г. Москва, ул. Фридриха Энгельса;

• 030150000150003 – Республика Бурятия, Окинский район, улус Зун-Холба, переулок Гадюкино.

Появится новая улица, 11 цифр будут уже известны, а кодом улицы станет первое свободное число. По 15-значному коду сразу понятно, что это за улица, в каком городе, районе и регионе. Все задумано очень логично!

Допустим, Яндекс в отчете показывает регион Московская область. Ей можно присвоить «общий» идентификатор 50000000000. Если определится Ленинский район, то идентификатор станет 50014000000, а если Видное – 50014001000. Теперь, если у нас есть идентификатор 50014001000, то запросто можно отнести его к любому более высокому уровню иерархии (району или области). Разумеется, можно сразу добавить в отчет колонки «Регион», «Ареал» и «Поселение», заполняя каждую из них данными, насколько их хватает.

Алгоритм решения

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

Все скриншоты сделаны в Power Query из Excel, но вы можете воспользоваться любым подходящим инструментом.

Яндекс.Директ

Имеем выгрузку статистики из Яндекс.Директ с детализацией до региона.

Как видите, Крымская кампания показывается в Алуште, Армянске и Бахчисарае, поэтому кто-нибудь может заявить: «А почему реклама показывается непонятно где, если должна показываться в Крыму?».

Дополним данные по каждому региону информацией из КЛАДР:

Каждая строка таблицы соответствует отдельному региону. Названия регионов можно загрузить в сервис геокодирования (например, DaData) и получить расширенную информацию. 

Каждый регион теперь относится к какому-то уровню детализации («GeoRegionType»), в «ParentId» указана ссылка на материнский (родительский регион), в «КЛАДР» указан код местоположения, в «RegionID» – идентификатор региона (первые две цифры из КЛАДР), а в колонке «Регион» – человекочитаемое название региона. Колонки «БЕ» и «РК» – это классификация клиента, поэтому Крым у него относится к региону Ростов-на-Дону, но это нисколько не усложняет задачу.

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

Google Analytics

В Google Analytics по ассоциированным конверсиям можно получить следующую статистику.

Обратите внимание, что основным параметром выбрана кампания (куда в метках передается идентификатор кампании), а дополнительным – регион. Теперь можно извлечь из utm_campain сам идентификатор кампании, а название региона дополнить его идентификатором из КЛАДР (вот пример таблицы соответствий).

И последним шагом сцепляем идентификатор кампании с идентификатором региона. Аналогично поступаем со статистикой по прямым конверсиям.

Объединение Яндекс и Google

Как мы объединяли данные?

1. Составили объединенный из Яндекс и Google список уникальных идентификаторов (кампания-регион). Это нужно сделать, потому что часть сочетаний «кампания-регион» может отсутствовать в каком-то из источников, а объединенный список по определению будет полным (если остались вопросы, то дальше покажу пример).

1. Разделили список на идентификаторы региона и кампании.

1. Дополнили данные статистикой по прямым и ассоциированным конверсиям из Google и по расходам на рекламу из Яндекс.Директ. Связь происходила по колонке KEY.

Обратите внимание, что для некоторых идентификаторов KEY отсутствуют данные по транзакциям, а для других – по рекламным расходам. Шаг 1 был нужен, чтобы ничего не потерялось.

1. Теперь по идентификаторам кампании и региона данные можно дополнить нужной информацией по соответствующей сущности и получить примерно такую таблицу.

2. Дальше статистику можно представить в виде удобной для дальнейшего анализа сводной таблицы по регионам.

Заключение

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

Напишите в комментариях, приходилось ли вам сталкиваться с подобными задачами и как вы их решали!

Ваш, Digital Lab