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

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

В статье мы разобрали 16 формул Google Таблиц для решения разных задач, с которыми приходится сталкиваться при настройке и ведении контекстной рекламы.

СЖПРОБЕЛЫ – убираем лишние пробелы в начале и конце ключевых фраз

Сложность: низкая.

Функция СЖПРОБЕЛЫ (или TRIM) применяется для удаления лишних пробелов в ячейке.

Обратите внимание! Функция удаляет не все пробелы, а только в начале и конце ячейки. Пробелы между словами остаются.

Применение

При сборе ключевиков в список семантики попали фразы, у которых могут быть лишние пробелы в начале или конце фразы. Чтобы быстро очистить весь список от таких пробелов, используем формулу СЖПРОБЕЛЫ.

Затем выделяем столбец с примененной формулой, жмем Ctrl+C и вставляем в первый столбец сочетанием клавиш Ctrl+Shift+V (специальная вставка, только значения).

Обратите внимание! При копировании ячеек с формулой копируется именно формула, а не значения. Если вставить скопированные ячейки обычным способом (без специальной вставки – Ctrl+V), то будут вставлены формулы, а значения потеряются. Поэтому при копипасте значений в ячейках с формулами используйте именно специальную вставку.

Как убрать двойные пробелы между словами

Это можно сделать с помощью обычной замены текста. Выделите нужный диапазон ячеек. Нажмите Ctrl+H. Во всплывающем окне в поле «Найти» введите два пробела, в поле «Заменить на» – один пробел.

Затем нажмите «Заменить все».

Инструмент заменит все двойные пробелы между словами на одинарные.

О том, как в один клик удалить лишние пробелы, спецсимволы и дубли слов в СЯ, читайте здесь.

ПОДСТАВИТЬ – замена фрагментов текста

Сложность: низкая.

Функция ПОДСТАВИТЬ (или SUBSTITUTE) позволяет заменять фрагменты текста в ячейках.

Синтаксис:

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

• текст – исходный текст, в котором нужно выполнить замену. Как правило, здесь необходимо ссылаться на ячейку с текстом;
• искать – фрагмент текста, который нужно заменить;
• замена – текст, на который будет заменен найденный фрагмент;
• номер_соответствия – необязательный параметр. Номер соответствия, для которого выполняется замена. По умолчанию функция заменяет все соответствия, найденные в тексте.

Применение

С помощью функции ПОДСТАВИТЬ можно быстро убрать из списка фраз лишние символы. Например, вы спарсили семантику из Вордстата, и у части слов в списке есть модификатор « ». Для загрузки в Google Ads вы хотите почистить ключевые слова и загрузить их в чистом виде.

Поэтому напротив ячейки с ключевой фразой вводим формулу =ПОДСТАВИТЬ(А2; « »; «»;).

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

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

Затем копируем весь столбец и с помощью «Специальной вставки» вставляем только значения в свободный столбец. Теперь их можно загружать в Google Ads или использовать для других целей.

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

=ПОДСТАВИТЬ([номер ячейки]; «для»; » для»).

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

Сложность: низкая.

Функция СТРОЧН переводит все символы в исходной ячейке в нижний регистр.

Синтаксис:

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

Применение

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

Для этого собираем все ключевики в одном столбце Google Таблицы. В соседнем столбце используем функцию =СТРОЧН(А1).

Копируем формулу в остальные ячейки столбца. Затем копируем столбец с преобразованными ключевиками и вставляем его в первый столбец с помощью специальной вставки (напоминаем – Ctrl Shift V).

ЗАМЕНИТЬ – делаем первую букву заголовка заглавной

Сложность: низкая.

Функция ЗАМЕНИТЬ (или REPLACE) позволяет заменять фрагмент текста в строке, который начинается с определенного символа и имеет заданную длину.

Синтаксис:

=ЗАМЕНИТЬ(текст; начало; длина; замена)

• текст – исходный текст (ячейка), в котором нужно произвести замену;
• начало – номер символа, с которого начинается заменяемый отрезок текста. Номер первого символа в строке – 1;
• длина – количество символов в отрезке, который нужно заменить;
• замена – текст, который нужно поместить вместо заменяемого отрезка.

Применение

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

Вот что содержит эта формула:

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

ДЛСТР – считаем длину текста в ячейке

Сложность: низкая.

Функция ДЛСТР (или LEN) определяет длину строки текста в ячейке.

Синтаксис:

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

Применение

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

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

UNIQUE – избавляемся от дублей в списке

Сложность: низкая.

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

Синтаксис:

=UNIQUE(диапазон).

Применение

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

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

=UNIQUE(A1:A).

Во втором столбце функция выведет список уникальных ключевиков, убрав все дубли.

Рекомендация! Даже если вы думаете, что в вашем списке нет дублей, все равно проверьте это с помощью функции UNIQUE. Это займет не больше минуты, зато вы будете на 100% уверены в чистоте списка.

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

ВПР – сравниваем два списка ключевиков и находим упущенные фразы

Сложность: средняя.

Функция ВПР (или VLOOKUP) применяется для сравнения данных из двух диапазонов и вывода нужных значений (или поиска несоответствий).

Синтаксис:

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

Применение

У нас запущены рекламные кампании в Яндекс.Директе и Google Ads. Задача – найти ключевые слова, которые используются в Google Ads, но не добавлены в кампании Директа.

Для этого выгружаем в Google Таблицу ключевики с Google Ads (столбец А) и Директа (столбец Е).

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

• ячейку, содержимое которой будем искать во втором столбце;
• диапазон, по которому будем производить поиск;
• номер столбца в диапазоне данных, по которому производим поиск. С ячеек этого столбца функция будет возвращать значения при совпадении поиска;
• отсортирован ли диапазон поиска (этот параметр необязателен, но мы указываем значение «0» – не отсортирован).

Функция будет последовательно брать значение ячейки в первом столбце и искать совпадение с ним в столбце Е. Если совпадение найдено, функция выведет это значение в ячейке столбца С. Если совпадений нет, отобразит «#Н/Д». Это значит, что данное ключевое слово используется в Google Ads, но отсутствует в Яндекс.Директе.

После этого нам останется лишь отфильтровать данные по значению «#Н/Д» в столбце С и добавить все полученные ключевые слова в Яндекс.Директ.

ЕСЛИОШИБКА – разбиваем ключевые слова на группы (ищем определенные слова в фразах)

Сложность: средняя.

Функция ЕСЛИОШИБКА (или IFERROR) возвращает значение первого аргумента, если в нем нет ошибки. Если в первом аргументе ошибка – возвращает значение второго аргумента (или пустое значение, если второй аргумент не указан).

Звучит сложно, но сейчас покажем, чем полезна функция на практике.

Синтаксис:

=ЕСЛИОШИБКА(значение; [значение_при_ошибке])

Применение

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

=ЕСЛИОШИБКА(ЕСЛИ(ПОИСК(«полимер»; A1)>0;»в группу с полимерами»; «0»))

Разберемся, что происходит внутри этой конструкции:

• ПОИСК(«полимер»; A1) – функция ПОИСК ищет, есть ли в ячейке А1 слово «полимер». Если слово есть, функция возвращает значение TRUE (1), если слова нет – значение FALSE (0).
• Функция ЕСЛИ проверяет результат функции ПОИСК. Если функция ПОИСК возвращает любое значение больше 0, то функция ЕСЛИ выводит текст «в группу с полимерами». В противном случае возвращает значение «0».
• Функция ЕСЛИОШИБКА проверяет результат функции ЕСЛИ. Если результат этой функции не является ошибкой (то есть не равен FALSE, 0) – выводится текст «в группу с полимерами».

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

SPLIT – раскладываем фразы на отдельные слова для удобного подбора минус-слов

Сложность: низкая.

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

Синтаксис:

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

Обязательных параметров всего 2:

• текст – конкретный текст или ссылка на ячейку с фразой, которую нужно разделить;
• разделитель – символ, разделяющий фрагменты текста (обычно – пробел).

Применение

Допустим, мы спарсили ключевые слова по нашей тематике из Вордстата. Чтобы удобнее было найти и выбрать минус-слова, разделим все фразы на отдельные слова. Для этого как раз понадобится функция SPLIT.

Выгружаем все фразы в Google Таблицу, в один столбец. В соседнем столбце прописываем функцию SPLIT в таком виде: =SPLIT(A1; » «).

Протягиваем функцию до конца списка. Дальше алгоритм действий следующий:

• чистим список слов от дублей (для этого к каждому столбцу можно применить функцию UNIQUE);
• сортируем уникальные слова по алфавиту;
• отбираем слова, которые будем использовать в качестве минус-слов.

Вот подробный гайд по работе с минус-словами в Яндексе и Google.

СЦЕПИТЬ – используем функцию объединения строк для генерации UTM-меток

Сложность: низкая.

Функция СЦЕПИТЬ предназначена для объединения нескольких строк.

Синтаксис:

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

Применение

С помощью функции СЦЕПИТЬ можно перемножать списки слов, создавая всевозможные комбинации фраз для семантического ядра. А также создавать шаблоны для автоматического добавления параметров к URL. Например, UTM-меток.

Допустим, в кампании есть несколько ключевых слов, под каждое из которых мы хотим подготовить ссылку с уникальной UTM-меткой. Для всех ссылок метки utm_source, utm_medium и utm_campaign будут одинаковыми. Отличаться будет только метка utm_term – в нее необходимо подставить транслитерацию исходного ключевого слова.

Готовим таблицу:

• в первом столбце – транслит ключевиков;
• во втором столбце – URL посадочной страницы;
• в третьем – шаблон utm-меток.

Прописываем формулу СЦЕПИТЬ таким образом: сначала к URL страницы добавляем шаблон utm-меток, затем – транслит ключевого слова (подставится как значение метки utm_term):

Более простой способ перемножать списки слов – с помощью Комбинатора ключевых слов от Click.ru. Задаете слова, которые нужно перемножить, и система выдает все возможные их комбинации. Инструмент бесплатный для всех.

REGEXEXTRACT – извлекаем нужный фрагмент текста с помощью регулярных выражений

Сложность: для продвинутых пользователей.

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

Синтаксис:

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

Применение

У нас есть список посадочных страниц конкурентов. Для настройки таргетинга по «Особым аудиториям» в Google Ads нам нужны домены конкурентов (то есть не полные адреса страниц, а лишь домены).

Если конкурентов много, вручную собирать домены будет проблематично.

Поэтому проще выгрузить список URL в таблицу и извлечь из них домены с помощью REGEXEXTRACT. Для этого в формуле нужно указать такое регулярное выражение:

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

Протягиваем формулу для всего списка URL. Затем с помощью функции UNIQUE получаем уникальные имена доменов (без дублей). Итоговый список можем использовать для настройки таргетинга в Google Ads.

IMPORTRANGE – подтягиваем данные из других таблиц или листов

Сложность: средняя.

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

Синтаксис:

=IMPORTRANGE(url_таблицы; диапазон )

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

Применение

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

Чтобы подтянуть данные из второй таблицы, используем функцию IMPORTRANGE. Первым аргументом функции указываем url таблицы, с которой будем брать данные. Вторым аргументом – название листа в этой таблице и диапазон данных.

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

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

IMPORTXML – парсим title и h1 с посадочных страниц для проверки корректной заполненности

Сложность: средняя.

Функция IMPORTXML – функция-парсер, с помощью которой можно парсить содержимое практически любых тегов html-страниц.

У функции относительно простой синтаксис:

=IMPORTXML(ссылка; запрос_xpath)

Самое сложное здесь – XPath-запрос (если вы раньше с ними не сталкивались). Однако работать с XPath-запросами не так сложно, как кажется на первый взгляд, и после небольшой практики вы будете использовать их комфортно и привычно.

Применение

Функция IMPORTXML незаменима, если нужно быстро проверить корректность заполнения тегов title и h1 на посадочных страницах (это важно, если вы запускаете, например, автотаргетинг или динамические объявления).

Для проверки нужно спарсить содержимое тегов в таблицу. Это делается просто:

1. Выгружаем в таблицу список URL посадочных страниц (1 ячейка – 1 URL).
2. В первой ячейке соседнего столбца прописываем формулу IMPORTXML: =IMPORTXML(A2; «//title»).
3. Затем копируем ячейку с формулой и вставляем во все остальные ячейки.
4. Функция подтянет содержимое тегов title по каждой странице.

О том, как массово парсить метатеги и заголовки с любого сайта, читайте здесь.

СУММЕСЛИ – считаем сумму по ячейкам, которые соответствуют заданным условиям

Сложность: низкая.

Функция СУММЕСЛИ позволяет суммировать данные в ячейках, которые соответствуют необходимым условиям.

Синтаксис:

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

• диапазон – область для поиска нужных ячеек;
• условие – условие, по которому будут отбираться ячейки для суммирования;
• сумма_диапазона – необязательный параметр. С его помощью можно указать отдельный диапазон ячеек, которые необходимо просуммировать. Если не указывать этот параметр, функция суммирует ячейки из первого диапазона.

Применение

Функция СУММЕСЛИ полезна при работе со статистическими данными с определенными разбивками. Например, у нас есть выгрузка по количеству регистраций с разбивкой по типам устройств и рекламных кампаний. Наша задача – быстро узнать:

• сколько пользователей зарегистрировались с мобильных устройств;
• какое количество регистраций принесли поисковые кампании.

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

С использованием СУММЕСЛИ необходимые расчеты можно сделать быстрее и без лишних действий.

Для этого прописываем такую формулу: =СУММЕСЛИ(А1:А17; «Поиск»; D1:D17). Здесь:

• A1:A17 — столбец, в которому указан тип кампании. По нему будет происходить проверка условия.
• «Поиск» – тип кампании, который функция будет искать;
• D1:D17 – диапазон, в котором функция будет суммировать значения, соответствующие типу кампании «Поиск».

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

GOOGLETRANSLATE – переводим ключевики с русского на английский (или любой другой язык)

Сложность: низкая.

Функция GOOGLETRANSLATE – Google Переводчик внутри Google Таблиц.

Синтаксис:

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

Применение

Мы хотим протестировать контекстную рекламу на англоязычном рынке. Чтобы не собирать отдельно англоязычную семантику, можно перевести текущую семантику из кампаний для Рунета с помощью функции GOOGLETRANSLATE.

Для этого выгружаем в Google Таблицу список ключевых слов. В соседнем столбце прописываем формулу для перевода с русского на английский:

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

Затем выделяем столбец с переводом, копируем и вставляем в отдельный столбец как значения (Ctrl Shift V).

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

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

Сложность: низкая.

Функция =ТРАНСП (или =TRANSPOSE) работает с массивами ячеек и меняет местами строки и столбцы.

Синтаксис:

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

Применение

Функция ТРАНСП полезна при работе со сводными таблицами. В некоторых случаях смена отображения данных в таблице улучшает представление данных и с таблицей работать удобнее.

Например, у нас есть таблица с данными по количеству регистраций. Данные разбиты по устройствам и типам кампаний. В строках – устройства, в столбцах – типы кампаний.

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

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

Используйте рассмотренные формулы Google Таблиц, и вы значительно упростите работу с семантикой.