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

На помощь приходят Google Таблицы. Мы собрали 15 формул и показали их работу на практических примерах.

СЖПРОБЕЛЫ — удаляем пробелы в начале/конце ячейки

ПОДСТАВИТЬ — заменяем слова в текстах в ячейках

ВПР — сравниваем значения двух диапазонов данных и выводим несоответствия

СТРОЧН — переводим буквы из верхнего регистра в нижний

ЗАМЕНИТЬ — делаем из ключевого слова заголовок объявления

ДЛСТР — определяем количество символов в ячейке

ЕСЛИОШИБКА — находим ключевики в строках, чтобы разбить на группы

SPLIT — разбиваем ключевые фразы на составные части и находим минус-слова

СЦЕПИТЬ — объединяем текст в ячейках и генерируем UTM-метки

REGEXEXTRACT — извлекаем нужный текст из ячеек

GOOGLETRANSLATE — переводим ключевики и другие данные

IMPORTRANGE — импортируем данные из других таблиц

IMPORTXML — проверяем заполненность title и h1 на целевых страницах

СУММЕСЛИ — находим сумму содержимого ячеек, соответствующих определенному условию

ТРАНСП — меняем местами строки и столбцы

СЖПРОБЕЛЫ — удаляем пробелы в начале/конце ячейки

Часто после сбора данных из разных источников возникает проблема: они могут быть непригодны для сводных таблиц и анализа из-за лишних пробелов в начале и конце ячейки. Сперва их нужно привести в порядок. Для этого есть функция СЖПРОБЕЛЫ.

Синтаксис:

=СЖПРОБЕЛЫ(ячейка)

или

=TRIM(ячейка)

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

Для этого вводим в соседнем столбце формулу СЖПРОБЕЛЫ и протягиваем вниз до конца списка слов.

15 формул Google Таблиц для контекстной рекламы

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

ПОДСТАВИТЬ — заменяем буквы/символы в ячейках

После выгрузки из Wordstat слова имеют модификаторы «+», и их можно быстро заменить. Или же, наоборот, при подготовке списка ключевых слов для загрузки в Google Ads — добавить к словам модификатор широкого соответствия «+».

Синтаксис:

=ПОДСТАВИТЬ("текст"; "стар_текст"; "нов_текст"; [номер_вхождения])

или

=SUBSTITUTE("текст"; "стар_текст"; "нов_текст"; [номер_вхождения])

Пример. Выгружены ключевые слова из кампании в Яндекс.Директе. Мы хотим импортировать эти слова в Google Ads. Перед тем как начать работу с ними, почистим их от знака «+».

15 формул Google Таблиц для контекстной рекламы

Во втором столбце вводим формулу =ПОДСТАВИТЬ. Указываем номер ячейки, в тексте которой надо удалить плюсы. В кавычках прописываем «+», во вторых кавычках ничего не указываем (то есть менять будем плюс на пустой символ). После этого протягиваем формулу до конца списка, выделяем столбец, копируем и вставляем в исходный столбец «Специальной вставкой» только значения.

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

Для быстрой очистки семантики от спецсимволов, пробелов и дублирующихся фраз используйте бесплатный нормализатор ключевых слов от Click.ru. Там все просто: вводите список фраз — нажимаете одну кнопку и загружаете “очищенный” результат.

ВПР — сравниваем значения двух диапазонов данных и выводим несоответствия

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

Синтаксис:

=ВПР(запрос; диапазон поиска; индекс (столбец по счету из выделенного диапазона); [сортировка])

или

=VLOOKUP(запрос; диапазон поиска; индекс (столбец по счету из выделенного диапазона); [сортировка])

Пример 1. Выгружены ключевые слова из Яндекс.Директа и ключевые слова из кампании в Google Ads. Нам нужно определить, каких слов, которые есть в Google Ads (столбец А), нет в Директе (столбец Е).

В скобках функции =ВПР прописываем:

  • номер ячейки, которую будем сравнивать;
  • диапазон данных, с которым будем сравнивать;
  • номер столбца в диапазоне данных, с которым сравниваем;
  • указываем логическое значение: 0 — ЛОЖЬ, 1 — ИСТИНА.

15 формул Google Таблиц для контекстной рекламы

Все слова из столбца А со значением H/Д — это и есть упущенные слова, которые есть в Google Ads, но которых нет в Директе.

Пример 2. Нужно свести два разных отчета. В одном выгружена статистика по кампаниям из Яндекс.Директа, во втором — данные по конверсиям из Google Analytics.

Копируем данные с конверсиями на лист со статистикой Директа. Добавляем колонку G с конверсиями и прописываем в ячейке G3 функцию =ВПР. В скобках указываем первую ячейку в списке кампаний со статистикой из Директа — А3. Потом выделяем диапазон данных, из которого нужно подтянуть конверсии (это данные из Google Analytics A13:B20). После точки с запятой ставим номер столбца из выделенного диапазона (в данном случае он второй). И в завершение ставим 0 — указываем, что нам нужно только точное значение.

15 формул Google Таблиц для контекстной рекламы

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

СТРОЧН — переводим буквы из верхнего регистра в нижний

Функция делает все буквы в заданных ячейках строчными.

Синтаксис:

=СТРОЧН(ячейка)

или

=LOWER(ячейка)

Пример. При сборе семантического ядра в список попали слова в разном регистре. Чтобы привести список слов к единому формату, воспользуемся функцией СТРОЧН.

Для этого во второй столбец вводим =СТРОЧН и указываем номер ячейки — в нашем примере это А1.

15 формул Google Таблиц для контекстной рекламы

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

ЗАМЕНИТЬ — делаем из ключевого слова заголовок объявления

Функция ЗАМЕНИТЬ поможет преобразовать первый символ в тексте в заглавную букву.

Синтаксис:

=ЗАМЕНИТЬ("стар_текст";начальная_позиция;число_знаков;"нов_текст")

или

=REPLACE("стар_текст";начальная_позиция;число_знаков;"нов_текст")

Пример. Подготовим из загруженного списка ключевых слов релевантные заголовки. Заголовки должны начинаться с заглавной буквы, поэтому применим к ключевым словам функцию ЗАМЕНИТЬ.

15 формул Google Таблиц для контекстной рекламы

Расшифруем:

  • А1 — ячейка, текст которой должен начинаться с большой буквы;
  • 1 — номер символа в ячейке А1, с которого начинается заменяемый отрезок текста (нас интересует первая буква, поэтому — 1);
  • 1 — количество символов в тексте, который необходимо заменить (нас интересует только одна буква, поэтому — 1);
  • ЛЕВСИМВ — функция, которая выводит левый символ в ячейке;
  • ПРОПНАЧ — функция, которая преобразует первые буквы слов в заглавные.

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

ДЛСТР — определяем количество символов в ячейке

Функция позволяет определить длину текста, содержащегося в указанной ячейке. Это полезно при составлении заголовков и текстов объявлений.

Синтаксис:

=ДЛСТР(ячейка)

или

=LEN(ячейка)

Пример. У нас есть заголовки, преобразованные из ключевых слов. Нам нужно их привести в соответствие с лимитами по количеству символов в рекламных системах.

Применим функцию =ДЛСТР. В скобках указываем ячейку, в которой нужно посчитать символы.

15 формул Google Таблиц для контекстной рекламы

Протягиваем функцию до конца списка. Находим несоответствия. Если количество символов в заголовке превышено, то исправляем его.

ЕСЛИОШИБКА — находим ключевики в строках, чтобы разбить на группы

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

Синтаксис:

=ЕСЛИОШИБКА(формула;"значение в случае ошибки")

или

=IFERROR(формула;"значение в случае ошибки")

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

15 формул Google Таблиц для контекстной рекламы

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

SPLIT — разбиваем ключевые фразы на составные части и находим минус-слова

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

Синтаксис:

SPLIT("текст"; "разделитель"; [тип_разделителя]; [удаление_пустых_ячеек])

Пример. Мы выгрузили маски слов из Wordstat. Теперь нужно найти минус-слова. Ускорить работу поможет функция SPLIT.

В соседний столбец прописываем функцию =SPLIT:

  • указываем номер ячейки, данные которой будем разбивать (в нашем примере — A1);
  • далее прописываем символ в кавычках, который разделяет слова в тексте ячейки (в нашем примере — пробел " ").

15 формул Google Таблиц для контекстной рекламы

Протягиваем функцию до конца списка. Как видите, все слова разбиты по столбцам. После этого удаляем дубликаты слов, сортируем их по алфавиту и оставляем в списке только те, которые будут в кампании минус-словами.

Читайте также:

Детально о подборе минус-слов для контекстной рекламы читайте в нашей статье.

СЦЕПИТЬ — объединяем текст в ячейках и генерируем UTM-метки

Функция позволяет объединить несколько текстовых элементов в одной строке.

Синтаксис:

=СЦЕПИТЬ("текст1";"текст2";…)

или

=CONCATENATE("текст1";"текст2";…)

Пример. Под каждое ключевое слово нам необходимо сделать ссылку с UTM-меткой.

Применим функцию СЦЕПИТЬ — объединим для каждого ключевого слова в столбце URL посадочной страницы, UTM-метки и слово в транслитерации в качестве параметра utm_term.

Для этого мы предварительно переведем в транслит исходные ключевые слова и вставим их в том же порядке в другом столбце. В соседнем столбце укажем посадочные страницы, в третьем — UTM-метки с параметрами. В столбце «Готовая ссылка» применим функцию СЦЕПИТЬ, в скобках указываем ячейки, содержимое которых будем объединять.

15 формул Google Таблиц для контекстной рекламы

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

REGEXEXTRACT — извлекаем нужный текст из ячеек

Эта формула извлекает определенную часть текста, соответствующую регулярному выражению.

Синтаксис:

=REGEXEXTRACT(где искать;"регулярное выражение")

Пример. Мы хотим собрать домены конкурентов для настройки таргетинга по «Особым аудиториям» в Google Ads. Найти конкурентов можно, например, среди результатов кластеризации семантики, полученных с помощью кластеризатора Click.ru. По каждому слову система выдаст топовую страницу в выдаче. Но целая страница нам не нужна — нам нужен только домен.

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

^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)

15 формул Google Таблиц для контекстной рекламы

Протянем формулу до конца списка. Отсортируем по алфавиту и удалим ненужные. Список доменов можно загружать в «Особые аудитории».

GOOGLETRANSLATE — переводим ключевики и другие данные

Функция переводит текст с одного языка на другой.

Синтаксис:

GOOGLETRANSLATE(текст; "язык_оригинала"; "язык_перевода")

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

Для перевода в соседнем с ключевыми словами столбце пропишем формулу:

=GOOGLETRANSLATE(A1;"ru";"en")

15 формул Google Таблиц для контекстной рекламы

Выделим полученный список слов, скопируем и заново вставим — только значения.

С помощью этой функции можно переводить на любые языки, поддерживаемые Google.

IMPORTRANGE — импортируем данные из других таблиц

Функция импортирует диапазон ячеек из одной электронной таблицы в другую.

Синтаксис:

=IMPORTRANGE("ссылка на документ";"ссылка на диапазон данных")

Пример. Создадим отчет, в котором соберем статистику по контекстной и таргетированной рекламе. Данные нам предоставляют разные специалисты. Чтобы все это объединить на одном листе, используем функцию IMPORTRANGE.

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

15 формул Google Таблиц для контекстной рекламы

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

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

IMPORTXML — проверяем заполненность title и h1 на целевых страницах

При запуске автотаргетинга в Яндекс.Директе или динамических объявлений в Google Ads важно, чтобы на посадочных страницах были прописаны релевантные теги title и h1. Для их проверки используется функция IMPORTXML.

Синтаксис:

=IMPORTXML("URL";"XPath-запрос")

Пример. Нам нужно проверить, заполнены ли теги title на страницах сайта. Вводим в первом столбце URL страниц, которые нужно проверить, а в соседнем — формулу IMPORTXML. Xpath-запрос для парсинга title выглядит так: "//title".

15 формул Google Таблиц для контекстной рекламы

С помощью функции IMPORTXML можно собрать практически любые данные со страницы. Вот примеры других запросов XPath:

  • сбор заголовков h1 (и по аналогии — h2-h6): //h1
  • сбор метатегов description: //meta[@name='description']/@content
  • спарсить мета-теги keywords: //meta[@name='keywords']/@content
  • извлечение e-mail адреса: //a[contains(href, 'mailTo:') or contains(href, 'mailto:')]/@href
  • извлечение ссылки на профили в соцсетях: //a[contains(href, 'vk.com/') or contains(href, 'twitter.com/') or contains(href, 'facebook.com/') or contains(href, 'instagram.com/') or contains(href, 'youtube.com/')]/@href

Для быстрого сбора метатегов и заголовков сразу по всему сайту используйте парсер метатегов и заголовков от Click.ru. Достаточно ввести ссылку на XML-карту сайта, и инструмент спарсит данные по всем страницам. Вот гайд по инструменту.

СУММЕСЛИ — находим сумму содержимого ячеек, соответствующих определенному условию

Функция находит сумму содержимого ячеек, соответствующих определенному условию.

Синтаксис:

СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])

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

Для этого применим функцию СУММЕСЛИ — введем в скобках диапазон, в котором указан тип кампании; после точки с запятой укажем условие (в нашем примере это «Поиск»); затем — диапазон, значения в котором будем суммировать.

15 формул Google Таблиц для контекстной рекламы

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

ТРАНСП — меняем местами строки и столбцы

Функция меняет местами строки и столбцы в массиве ячеек.

Синтаксис:

=ТРАНСП(массив_или_диапазон)

или

=TRANSPOSE(массив_или_диапазон)

Пример. У нас есть данные сводной таблицы (из предыдущего примера). Но нам не удобно анализировать и сравнивать типы кампаний между собой, когда они находятся в строках. Для того чтобы отобразить те же данные в столбцах, применим функцию ТРАНСП.

В свободную ячейку прописываем =ТРАНСП и указываем диапазон — в нашем примере это A1:F5.

15 формул Google Таблиц для контекстной рекламы

Теперь типы кампании отображаются в столбцах, и данные при этом не потерялись.

Зарабатывайте больше на контекстной и таргетированной рекламе! Подключите рекламные аккаунты ваших клиентов к Click.ru — получайте вознаграждение до 35% от их расходов на рекламу. Деньги выводите на электронные кошельки когда вам удобно.

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