Здравствуйте!
При подготовке, анализе и оптимизации контекстной рекламы возникает масса рутинных операций. Удалить пробелы и плюсики, изменить строчные буквы на заглавные, объединить массивы ключевиков, найти расхождения в данных и мн. др.
На помощь приходят Google Таблицы. Мы собрали 15 формул и показали их работу на практических примерах.
СЖПРОБЕЛЫ — удаляем пробелы в начале/конце ячейки
Часто после сбора данных из разных источников возникает проблема: они могут быть непригодны для сводных таблиц и анализа из-за лишних пробелов в начале и конце ячейки. Сперва их нужно привести в порядок. Для этого есть функция СЖПРОБЕЛЫ.
Синтаксис:
=СЖПРОБЕЛЫ(ячейка)
или
=TRIM(ячейка)
Пример. Есть список ключевых слов для сайта по продаже полимеров и полимерного сырья, которые были собраны из разных источников. В них удалили лишние символы, но остались пробелы. Их легко убрать.
Для этого вводим в соседнем столбце формулу СЖПРОБЕЛЫ и протягиваем вниз до конца списка слов.
После этого выделяем столбец, копируем и вставляем в исходный столбец «Специальной вставкой» только значения.
ПОДСТАВИТЬ — заменяем буквы/символы в ячейках
После выгрузки из Wordstat слова имеют модификаторы «+», и их можно быстро заменить. Или же, наоборот, при подготовке списка ключевых слов для загрузки в Google Ads — добавить к словам модификатор широкого соответствия «+».
Синтаксис:
=ПОДСТАВИТЬ("текст"; "стар_текст"; "нов_текст"; [номер_вхождения])
или
=SUBSTITUTE("текст"; "стар_текст"; "нов_текст"; [номер_вхождения])
Пример. Выгружены ключевые слова из кампании в Яндекс.Директе. Мы хотим импортировать эти слова в Google Ads. Перед тем как начать работу с ними, почистим их от знака «+».
Во втором столбце вводим формулу =ПОДСТАВИТЬ. Указываем номер ячейки, в тексте которой надо удалить плюсы. В кавычках прописываем «+», во вторых кавычках ничего не указываем (то есть менять будем плюс на пустой символ). После этого протягиваем формулу до конца списка, выделяем столбец, копируем и вставляем в исходный столбец «Специальной вставкой» только значения.
По умолчанию функция заменяет все соответствия, найденные в тексте. Если вы хотите заменить определенное соответствие, укажите его порядковый номер в последней части формулы. Если это вам не нужно, ничего там не указывайте.
ВПР — сравниваем значения двух диапазонов данных и выводим несоответствия
Функция ВПР позволяет сравнить данные из одной таблицы с данными с другой и вывести в отдельный столбец все несоответствия.
Синтаксис:
=ВПР(запрос; диапазон поиска; индекс (столбец по счету из выделенного диапазона); [сортировка])
или
=VLOOKUP(запрос; диапазон поиска; индекс (столбец по счету из выделенного диапазона); [сортировка])
Пример 1. Выгружены ключевые слова из Яндекс.Директа и ключевые слова из кампании в Google Ads. Нам нужно определить, каких слов, которые есть в Google Ads (столбец А), нет в Директе (столбец Е).
В скобках функции =ВПР прописываем:
- номер ячейки, которую будем сравнивать;
- диапазон данных, с которым будем сравнивать;
- номер столбца в диапазоне данных, с которым сравниваем;
- указываем логическое значение: 0 — ЛОЖЬ, 1 — ИСТИНА.
Все слова из столбца А со значением H/Д — это и есть упущенные слова, которые есть в Google Ads, но которых нет в Директе.
Пример 2. Нужно свести два разных отчета. В одном выгружена статистика по кампаниям из Яндекс.Директа, во втором — данные по конверсиям из Google Analytics.
Копируем данные с конверсиями на лист со статистикой Директа. Добавляем колонку G с конверсиями и прописываем в ячейке G3 функцию =ВПР. В скобках указываем первую ячейку в списке кампаний со статистикой из Директа — А3. Потом выделяем диапазон данных, из которого нужно подтянуть конверсии (это данные из Google Analytics A13:B20). После точки с запятой ставим номер столбца из выделенного диапазона (в данном случае он второй). И в завершение ставим 0 — указываем, что нам нужно только точное значение.
Протягиваем функцию до конца столбца с данными. Выделяем полученный диапазон, копируем и вставляем в исходный столбец «Специальной вставкой» только значения.
СТРОЧН — переводим буквы из верхнего регистра в нижний
Функция делает все буквы в заданных ячейках строчными.
Синтаксис:
=СТРОЧН(ячейка)
или
=LOWER(ячейка)
Пример. При сборе семантического ядра в список попали слова в разном регистре. Чтобы привести список слов к единому формату, воспользуемся функцией СТРОЧН.
Для этого во второй столбец вводим =СТРОЧН и указываем номер ячейки — в нашем примере это А1.
После этого протягиваем формулу до конца списка, выделяем столбец, копируем и вставляем в исходный столбец «Специальной вставкой» только значения.
ЗАМЕНИТЬ — делаем из ключевого слова заголовок объявления
Функция ЗАМЕНИТЬ поможет преобразовать первый символ в тексте в заглавную букву.
Синтаксис:
=ЗАМЕНИТЬ("стар_текст";начальная_позиция;число_знаков;"нов_текст")
или
=REPLACE("стар_текст";начальная_позиция;число_знаков;"нов_текст")
Пример. Подготовим из загруженного списка ключевых слов релевантные заголовки. Заголовки должны начинаться с заглавной буквы, поэтому применим к ключевым словам функцию ЗАМЕНИТЬ.
Расшифруем:
- А1 — ячейка, текст которой должен начинаться с большой буквы;
- 1 — номер символа в ячейке А1, с которого начинается заменяемый отрезок текста (нас интересует первая буква, поэтому — 1);
- 1 — количество символов в тексте, который необходимо заменить (нас интересует только одна буква, поэтому — 1);
- ЛЕВСИМВ — функция, которая выводит левый символ в ячейке;
- ПРОПНАЧ — функция, которая преобразует первые буквы слов в заглавные.
После этого протягиваем всю формулу до конца списка, выделяем столбец, копируем и вставляем в этот же столбец «Специальной вставкой» только значения.
ДЛСТР — определяем количество символов в ячейке
Функция позволяет определить длину текста, содержащегося в указанной ячейке. Это полезно при составлении заголовков и текстов объявлений.
Синтаксис:
=ДЛСТР(ячейка)
или
=LEN(ячейка)
Пример. У нас есть заголовки, преобразованные из ключевых слов. Нам нужно их привести в соответствие с лимитами по количеству символов в рекламных системах.
Применим функцию =ДЛСТР. В скобках указываем ячейку, в которой нужно посчитать символы.
Протягиваем функцию до конца списка. Находим несоответствия. Если количество символов в заголовке превышено, то исправляем его.
ЕСЛИОШИБКА — находим ключевики в строках, чтобы разбить на группы
Функция используется для проверки формулы на наличие ошибок в первом аргументе и возвращает результат, если ошибки нет (или определенное значение, если она есть).
Синтаксис:
=ЕСЛИОШИБКА(формула;"значение в случае ошибки")
или
=IFERROR(формула;"значение в случае ошибки")
Пример. У нас есть список фраз и нам надо его разбить на группы. С помощью формулы IFERROR можно найти подходящие ключевые слова в списке фраз и в соседнем столбце указать название группы.
Протягиваем формулу до конца списка, копируем результат и вставляем в этот же столбец «Специальной вставкой» только значения. После этого отсортируем ключи по названию групп и дальше продолжим распределять их.
SPLIT — разбиваем ключевые фразы на составные части и находим минус-слова
Функция разносит текст в ячейке по разным столбцам.
Синтаксис:
SPLIT("текст"; "разделитель"; [тип_разделителя]; [удаление_пустых_ячеек])
Пример. Мы выгрузили маски слов из Wordstat. Теперь нужно найти минус-слова. Ускорить работу поможет функция SPLIT.
В соседний столбец прописываем функцию =SPLIT:
- указываем номер ячейки, данные которой будем разбивать (в нашем примере — A1);
- далее прописываем символ в кавычках, который разделяет слова в тексте ячейки (в нашем примере — пробел " ").
Протягиваем функцию до конца списка. Как видите, все слова разбиты по столбцам. После этого удаляем дубликаты слов, сортируем их по алфавиту и оставляем в списке только те, которые будут в кампании минус-словами.
СЦЕПИТЬ — объединяем текст в ячейках и генерируем UTM-метки
Функция позволяет объединить несколько текстовых элементов в одной строке.
Синтаксис:
=СЦЕПИТЬ("текст1";"текст2";…)
или
=CONCATENATE("текст1";"текст2";…)
Пример. Под каждое ключевое слово нам необходимо сделать ссылку с UTM-меткой.
Применим функцию СЦЕПИТЬ — объединим для каждого ключевого слова в столбце URL посадочной страницы, UTM-метки и слово в транслитерации в качестве параметра utm_term.
Для этого мы предварительно переведем в транслит исходные ключевые слова и вставим их в том же порядке в другом столбце. В соседнем столбце укажем посадочные страницы, в третьем — UTM-метки с параметрами. В столбце «Готовая ссылка» применим функцию СЦЕПИТЬ, в скобках указываем ячейки, содержимое которых будем объединять.
Протягиваем функцию до конца, копируем и вставляем в этот же столбец с помощью «Специальной вставки» только значения, чтобы данные не потерялись.
REGEXEXTRACT — извлекаем нужный текст из ячеек
Эта формула извлекает определенную часть текста, соответствующую регулярному выражению.
Синтаксис:
=REGEXEXTRACT(где искать;"регулярное выражение")
GOOGLETRANSLATE — переводим ключевики и другие данные
Функция переводит текст с одного языка на другой.
Синтаксис:
GOOGLETRANSLATE(текст; "язык_оригинала"; "язык_перевода")
Пример. Хотим запустить контекстную рекламу в другой стране, и нам нужно быстро перевести ключевые слова. Используем эту функцию.
Для перевода в соседнем с ключевыми словами столбце пропишем формулу:
=GOOGLETRANSLATE(A1;"ru";"en")
Выделим полученный список слов, скопируем и заново вставим — только значения.
С помощью этой функции можно переводить на любые языки, поддерживаемые Google.
IMPORTRANGE — импортируем данные из других таблиц
Функция импортирует диапазон ячеек из одной электронной таблицы в другую.
Синтаксис:
=IMPORTRANGE("ссылка на документ";"ссылка на диапазон данных")
Пример. Создадим отчет, в котором соберем статистику по контекстной и таргетированной рекламе. Данные нам предоставляют разные специалисты. Чтобы все это объединить на одном листе, используем функцию IMPORTRANGE.
Для этого пропишем в функции ссылку на таблицу. После точки с запятой указываем лист и диапазон данных.
Данные загрузятся и отобразятся в этом отчете. Единственное, учтите, что у вас должен быть открыт доступ к листу, с которого загружаете данные.
Таким образом, мы вывели данные, к которым можем дать доступ клиентам или другим специалистам. При этом исходные отчеты останутся для них недоступными.
IMPORTXML — проверяем заполненность title и h1 на целевых страницах
При запуске автотаргетинга в Яндекс.Директе или динамических объявлений в Google Ads важно, чтобы на посадочных страницах были прописаны релевантные теги title и h1. Для их проверки используется функция IMPORTXML.
Синтаксис:
=IMPORTXML("URL";"XPath-запрос")
Пример. Нам нужно проверить, заполнены ли теги title на страницах сайта. Вводим в первом столбце URL страниц, которые нужно проверить, а в соседнем — формулу IMPORTXML. Xpath-запрос для парсинга title выглядит так: "//title".
С помощью функции 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
СУММЕСЛИ — находим сумму содержимого ячеек, соответствующих определенному условию
Функция находит сумму содержимого ячеек, соответствующих определенному условию.
Синтаксис:
СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])
Пример. Мы выгрузили отчет из Google Analytics с разбивкой по типам кампаний и типам устройств. Нам нужно быстро подсчитать сумму регистраций с мобильных устройств и поисковых кампаний.
Для этого применим функцию СУММЕСЛИ — введем в скобках диапазон, в котором указан тип кампании; после точки с запятой укажем условие (в нашем примере это «Поиск»); затем — диапазон, значения в котором будем суммировать.
Таким образом, можно подсчитать по каждому пункту сумму регистраций или кликов.
ТРАНСП — меняем местами строки и столбцы
Функция меняет местами строки и столбцы в массиве ячеек.
Синтаксис:
=ТРАНСП(массив_или_диапазон)
или
=TRANSPOSE(массив_или_диапазон)
Пример. У нас есть данные сводной таблицы (из предыдущего примера). Но нам не удобно анализировать и сравнивать типы кампаний между собой, когда они находятся в строках. Для того чтобы отобразить те же данные в столбцах, применим функцию ТРАНСП.
В свободную ячейку прописываем =ТРАНСП и указываем диапазон — в нашем примере это A1:F5.
Теперь типы кампании отображаются в столбцах, и данные при этом не потерялись.
