Возможности Excel, без которых не обойтись специалисту по контекстной рекламе

Эта статья для тех, кто пользуется Excel на продвинутом уровне. Здесь вы не найдёте информацию об использовании стандартных фильтров и сортировок, о форматировании и видах разбивки по столбцам. Кирилл Муратов, Performance Manager в Adventum, рассказывает про использование Excel для решения реальных задач в кампаниях: условия, формулы внутри формул, продвинутые замены, регулярные выражения, формулу =ВПР.

Чаще всего в работе с одним или несколькими условиями я сталкиваюсь с суммированием:

Реже в моей практике встречаются ситуации, где с учётом выполнения одного или нескольких условий необходимо получить среднее значение : =СРЗНАЧЕСЛИ или =СРЗНАЧЕСЛИМН.

Эти действия — суммирование и получение среднего значения — работают по одному принципу. Покажу решение на примере суммирования.

=СУММЕСЛИ(Диапазон;Условие;Диапазон суммирования);

=СУММЕСЛИМН(Диапазон суммирования;Диапазон условия1;условие1;Диапазон условия2;условие2;…).

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

Условие — условие, которое может быть представлено в любом формате данных. При выполнении условия происходит суммирование значений из диапазона суммирования.

Диапазон суммирования — область таблицы для вычисления суммы по заданному условию.

Диапазон условия — область таблицы для задания условия, по которому будет происходить суммирование.

Представьте, что при заливке кампании в Google AdWords мы забыли разбить расписание показов на дни недели и часы. При этом наша задача — расставить корректировки по временным сегментам.

Сначала заходим в отчёт и выгружаем его.

Далее работаем с выгрузкой. С помощью формулы =СУММЕСЛИМН мы можем по трём условиям свести данные по дням недели, где каждый день разбит на несколько сегментов времени. Красной линией подчёркнут диапазон условий, зелёной — условия и диапазоны условий.

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

NB Не забывайте использовать знак «$» для фиксации строк и столбцов. При такой фиксации достаточно будет обозначить все диапазоны абсолютными ссылками один раз. Потом вы сможете протягивать и копировать формулы, не опасаясь, что у вас поедут диапазоны или условия.

Для решения большинства реальных задач я часто использую сложные составные формулы. Например:

=ПОДСТАВИТЬ(ЗАМЕНИТЬ(C3;ПОИСК(D3;C3)—1;ДЛСТР(C3)—ПОИСК(D3;C3)+2;«»);«http://»;«http://»&D3&«.»)

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

=ЕСЛИ(Условие;Значение, если условие выполняется;Значение, если условие не выполняется);

=ЕСЛИОШИБКА(Значение;Значение, если ошибка);

=ПОИСК(Подстрока, которую ищем;Строка, в которой ищем).

Перед нами стоит задача — выгрузить все url, которые есть в аккаунте, и провести проверку определённой их части на наличие конкретного символа. Это позволит клиенту заменить искомый символ на другой и регулировать замену лендинга, если она необходима. Как выделить определенную часть url я расскажу далее в статье, а пока предположим, что она у нас уже есть:

Что мы делаем дальше:

Разумеется, задачу можно решить иначе: разбить url на сегменты по знаку «/» и поместить каждый сегмент в отдельный столбец. Однако надо учитывать, что этих сегментов может быть три, а может — двадцать. Представьте, сколько времени это займёт и как будет выглядеть итоговая таблица.

NB Символ тильда «~» является служебным в Excel. Он используется перед служебными знаками «?», «*» или «~» для поиска соответственно вопросительных знаков, звездочек и других знаков тильды. Поэтому в формуле мы используем выражение «~~».

Отмечу, что речь идёт о решении для Google-таблиц, так как в Excel вы можете решить задачу через макросы VBA.

В Google-таблицах есть 3 формулы, которые позволяют работать с регулярными выражениями:

=REGEXREPLACE(Исходная строка;Регулярное Выражение;Выражение для замены);

=REGEXEXTRACT(Исходная строка;Регулярное Выражение);

=REGEXMATCH(Исходная строка;Регулярное Выражение).

Исходная строка — текст, на который применяется регулярное выражение.

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

Выражение для замены — текст, на который необходимо заменить найденное регулярное выражение.

Представим, что мы выгрузили чистые url и нужно выделить из них подстроку. Для этого подходят первые две формулы. Сначала я покажу как в одно действие операцию можно сделать с =REGEXREPLACE.

Строим шаблон на весь url «(.+)/(.+)/(.+)+$». Фактически получается, что мы его разделяем на 3 группы:

У каждой такой группы на выходе будет соответственно свой уникальный номер $1, $2, $3. Так как нам нужна последняя группа, мы заменяем всю строку на третью группу, то есть на «$3»:

И проверяем, соответствует ли url регулярному выражению, например, с помощью =REGEXMATCH:

Если же мы хотим решить задачу с помощью формулы =REGEXEXTRACT, то действуем иначе. Строим шаблон на часть url, которая идет с конца строки до последнего одинарного «/»: «[\w.+\-\~]+$».

Проверяем url на соответствие регулярному выражению:

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

NB Не увлекайтесь регулярными выражениями, если не уверены в знании их синтаксиса и функционала. Искать и исправлять ошибки в неработающих формулах с такими выражениями может быть сложнее, чем работать с таблицей вручную.

Заменять значения можно с помощью двух формул: =ЗАМЕНИТЬ и =ПОДСТАВИТЬ.

Формула =ЗАМЕНИТЬ позволяет делать любые замены указанного числа знаков на новую подстроку. Формула =ПОДСТАВИТЬ используется, когда нужно заменить заранее известную подстроку в строке.

=ЗАМЕНИТЬ(исходный текст;начальная позиция;число знаков;текст для замены);

=ПОДСТАВИТЬ(исходный текст;текст, который заменяем;текст для замены).

Исходный текст — строка, в которой будем производить замену.

Начальная позиция — позиция в исходном тексте, начиная с которой производится замена.

Число знаков — общее число знаков, которое подвергается замене.

Текст для замены — текст, на который заменяем выбранную подстроку.

Текст, который заменяем — подстрока, которую заменяем.

Задача — разместить часть url в области субдомена, а из самого url убрать её.

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

Решение с регулярными выражениями выглядит следующим образом:

Манипуляции с url бывают разными и зависят от задачи, которую надо решить в рекламной кампании. Например, полученные здесь субдомены мы использовали в объявлениях. Наше решение однозначно ускорило работу, так как в выборке было более 5000 url. 

Формула =ВПР выполняет вертикальный поиск в таблице, позволяет найти и забрать данные из неё и переставить значения в соответствующие поля другой таблицы.

=ВПР(искомое значение;таблица для поиска;номер столбца;интервальный просмотр).

Исходное значение — уникальное значение (ключ), по которому осуществляется поиск в таблице для поиска.

Таблица для поиска — таблица, по которой осуществляется поиск и из которой забираем данные в исходную таблицу.

Номер столбца — порядковый номер столбца,из которого мы забираем данные в таблице для поиска.

Интервальный просмотр — точное или приблизительное соответствие исходного значения значениям из таблицы для поиска.

Предположим, есть задача — свести онлайн-данные с площадки с офлайн-данными в разрезе регионов. На входе мы имеем две таблицы. В таблице №1 представлены онлайн-данные: показы, клики, расход, лиды. В таблице №2 представлены офлайн-данные: заключённые договоры, отказы.

Используем формулу =ВПР и переставляем данные из таблицы №2 в таблицу №1. Сначала забираем в первую таблицу значения из столбца «Заключённый договор»:

Затем меняем в формуле номер столбца и забираем данные по «Отказам»:

Благодаря формуле =ВПР данные переносятся в соответствии с регионами, даже если порядок значений в этом столбце или точное соответствие в написании нарушены.

Важно помнить, что делать такую интеграцию данных вручную на большом проекте сложно и неэффективно. В этом случае лучше один раз выстроить систему аналитики на базе Google BigQuery, например. Затем автоматизированно в одном месте собирать данные из Google Analytics, «Яндекс.Метрики», CRM-системы и других источников. Наше решение удобно для локальных задач, когда нет возможности быстро настроить полноценную систему аналитики, а данные нужны здесь и сейчас.

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