23 полезные фишки «Google Таблиц» для PPC-специалистов

Специалисту по контекстной рекламе не обойтись без таблиц. Обрабатывать семантику, формировать объявления, работать с отчетами и чек-листами — все это удобно делать с помощью этого инструмента. Ловите подборку формул, функций и расширений, которые помогут сэкономить время и расширить спектр задач, которые вы можете решить в «Google Таблицах».

Незаменимые формулы

Инструменты работы с данными

Приемы условного форматирования

Полезные дополнения

Незаменимые формулы

Удаляем лишние пробелы

Формула:

=СЖПРОБЕЛЫ("text")

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

Функция удалила пробелы в начале и конце строки, а также двойной пробел перед «-бесплатно»

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

Считаем количество символов

Формула:

=ДЛСТР("text")

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

Считаем количество символов в объявлениях

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

Меняем один текст на другой

Формула:

=ПОДСТАВИТЬ(text_to_search;search_for;replace_with)

Как работает. Находит слово или фрагмент в выбранной строке или ячейке и заменяет на другое слово или фрагмент. Используется три обязательных аргумента:

• text_to_search — строка или адрес ячейки, в котором нужно заменить одно слово на другое;
• search_for — фрагмент или слово, которые нужно найти и заменить;
• replace_with — слово или фрагмент, на которые меняем search_for.

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

Меняем в объявлениях «пиццу» на «лемура»

Когда пригодится. Когда нужно произвести одинаковую замену во множестве ячеек — заменить минус-слова в семантике, продающие добавки или название бренда в объявлениях, убрать символы (например, «+» перед предлогами при копировании ключей из «Вордстата»).

Объединяем ячейки

Формула:

=СЦЕПИТЬ(строка1;строка2;строка3...)

Как работает. Объединяет строки текста. В качестве аргументов используется сам текст в кавычках или адреса ячеек с текстом. Обратите внимание, если между объединяемыми строками нужен пробел, нужно прописать его в кавычках.

Объединяем ключевые слова с продающей добавкой

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

Убираем дубликаты

Формула:

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

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

Убираем из списка ключей дубликаты

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

Добавляем UTM-метки

Формулы:

=CONCATENATE(строка1;строка2;строка3…) — объединяем ячейки
=IF(источник;значение_при_соблюдении_условия_значение_при_несоблюдении_условия)
— проверям, не пустые ли ячейки

Как работает. Объединяет ячейки, которые содержат ссылку и нужные метки. Функции CONCATENATE и IF комбинируются между собой и превращаются вот в такую большую формулу:

=CONCATENATE(A2;if(B2<>false;CONCATENATE("?utm_source=";B2););if(C2<>false;CONCATENATE("&utm_medium=";C2););if(D2<>false;CONCATENATE("&utm_campaign=";D2););if(E2<>false;CONCATENATE("&utm_content=";E2););if(F2<>false;CONCATENATE("&utm_term=";F2);))

На первой взгляд она может показаться монструозной и запутанной, но не все так страшно. Смотрите.

Разбираем формулу по частям

1 — объединяем ссылку со всеми UTM-метками
2 — проверяем, есть ли в ячейке B2 содержимое
3 — если есть, объединяем ярлык метки (utm_source) и значение из ячейки B2
4 — в начале метки добавляем «?»
5 — добавляем следующую метку по аналогии с первой — следующий аргумент нашей первой функции CONCATENATE (1).mark
По тому же принципу добавляются все остальные метки.

ВАЖНО: В неизменном виде (Ctrl+С — Ctrl+V) будет работать, если вставить ссылку в ячейку A2, а все нужные метки — в ячейки B2-F2 в правильном порядке. Если какие-то метки не нужны, просто оставьте ячейки пустыми.

Копируем формулу в ячейку G2, ссылку на сайт — в A2, а затем из G2 забираем готовую ссылку с UTM

Когда пригодится. При добавлении UTM-меток к ссылкам в объявлениях. Когда нужно проставить одни и те же метки во множестве ссылок, формулу удобно «протянуть» и получить список ссылок уже с метками.

Переводим буквы в нижний регистр

Формула:

=СТРОЧН("текст")

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

Приводим запросы к единому виду

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

Читайте также: Обзор 5 сервисов для сбора и расширения семантического ядра

Импортируем данные из одной таблицы в другую

Формула:

=IMPORTRANGE(ключ_таблицы;диапазон)

• ключ_таблицы — ссылка на таблицу, из которой нужно импортировать данные, в кавычках или адрес ячейки, содержащей эту ссылку
• диапазон — диапазон ячеек, которые нужно импортировать. Указывается в кавычках. Если нужно импортировать данные не с первого листа, в начале указывается название листа в формате «Лист1!». Здесь также может использоваться адрес ячейки, в которой записаны название листа и диапазон.

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

Вводим формулу и в текущую таблицу подгружаются данные из другой

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

Добавляем изображения в ячейки

Формула:

=IMAGE(ссылка; [режим]; [высота]; [ширина])

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

Добавляем изображение в ячейку

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

Инструменты работы с данными

Проверяем данные

Инструмент:

Данные → Настроить проверку данных

Как работает. Проверяет данные в указанном диапазоне на выполнение заданных условий:

• значения из диапазона — данные в проверяемом диапазоне должны соответствовать значениям из другого диапазона;
• значения из списка — список значений для проверки вводится вручную через запятую;
• число — данные в диапазоне должны быть равны, не равны, больше, меньше и т. д. указанных значений;
• текст — содержит, не содержит либо равняется указанному тексту. Здесь же настраивается проверка допустимых URL и адресов электронной почты;
• дата — данные являются допустимой датой. Также можно проверить, указаны ли в проверяемом диапазоне более ранние или более поздние даты, чем заданная.
• своя формула — данные соответствуют результату вычисления формулы;
• флажок — в ячейках выбранного диапазона появляются чек-боксы, которые можно помечать галочками.

Последнее удобно использовать во всевозможных чек-листах.

Мы подсветили все запросы, в которых есть слово «цена»

Когда пригодится. При группировке поисковых запросов: задаем правило, при котором ячейка не должна содержать нужное слово, и все строки с этим словом подсветятся. Если нужно найти все словоформы, можно указывать часть слова. Так, чтобы найти строки, содержащие «красный», «красная», «красное», указываем в правиле "красн".mark

Создаем сводную таблицу

Инструмент:

Данные → Создать сводную таблицу

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

Собираем в сводную таблицу расходы по рекламе отдельных товаров

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

Разделяем на колонки

Инструмент:

Данные → Разделить текст на столбцы

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

Разносим дату и время по разным столбцам

Когда пригодится. Когда при импорте разные типы данных попадают в один столбец таблицы. Например, так делает Яндекс.Метрика при выгрузке расходов по времени суток — помещает период в один столбец.

Группируем столбцы и строки

Инструмент:

Данные → Сгруппировать столбцы/строки

Как работает. Объединяет столбцы и/или строки в группы, которые можно свернуть/развернуть.

Объединяем первые 5 строк в группу

Когда пригодится. При согласовании большого объема данных можно, например, сгруппировать семантику по кластерам и работать с каждым кластером отдельно, не отвлекаясь на остальные запросы.

Сортируем и фильтруем данные

Инструмент:

Данные → Сортировать диапазон

Данные → Создать фильтр

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

Сортируем запросы по частотности от меньшей к большей

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

Читайте также: Google Data Studio: шаблон отчета по контекстной рекламе для клиентов + инструкция

Находим и заменяем данные

Инструмент:

Изменить → Найти и заменить

Как работает. Находит в указанном диапазоне одно заданное значение и заменяет на другое.

Легким движением руки «самолет» превращается в самокат 😉

Когда пригодится. Когда нужно удалить символ «+» перед предлогами в собранной семантике. Заменяем символ «+» на пустое поле. Также может быть полезно, когда нужно заменить URL или часть текста во множестве объявлений.

Удаляем лишние пробелы и дубликаты

Инструмент:

Данные → Удалить пробелы

Данные → Удалить дубликаты

Как работает. Также, как формулы СЖПРОБЕЛЫ и UNIQUE соответственно. Разница в том, что результат удаления выдается в исходном столбце, а не в отдельном.

Удаляем дубли ключевых запросов

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

Приемы условного форматирования

Следим за количеством символов

Правило форматирования:

Условие: «Больше», значение — максимально допустимое количество символов.

Как работает. Выделяет ячейку выбранным стилем, если число в ней превышает заданное ограничение.

Подсвечиваем ячейки, значение которых больше 35

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

Анализируем показатели

Правило форматирования:

Условие: Больше/Меньше, значение — то, относительно которого мы хотим
проанализировать данные.

Как работает. Аналогично предыдущему пункту.

Подсвечиваем строки, расходы по которым превысили 5 тыс. руб.

Когда пригодится. Сценариев использования много: подсветить в отчете строки, где:

• CTR меньше нуля;
• стоимость перехода превышает искреннюю ставку
• показы и клики больше средних показателей по кампании;
• показы равны нулю;
• фразы, площадки, регионы с нулевыми лидами и т. д. mark

Ищем словоформы и ключи в разбавленном вхождении

Правило форматирования:

Условие: Текст содержит, значение — слово, фраза или фрагмент, которые нужно найти

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

Выделили запросы, которые содержат слово «частный»

Когда пригодится. Когда нужно в большом объеме данных найти все ключевые фразы, содержащие определенное слово, в том числе — со словоформами и запросами в разбавленном вхождении. Если мы зададим для поиска слово «бак», форматирование выделятся в том числе ячейки со словами «бензобак» и «бакенбарды».

Иллюстрируем разброс показателей

Правило форматирования:

Вкладка «Градиент», минимальное, средние и макисмальное значение — в зависимости от показателей кампании.

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

Белой остается ячейка с минимальными расходами, насыщенным красным подсвечивается ячейка с максимальными

Когда пригодится. Когда нужно наглядно отобразить, сколько фраз / объявлений / площадок РСЯ / регионов отклоняются по CTR, стоимости перехода, числу показов или кликов от среднего значения по кампании и на сколько.

Читайте также: Эксперимент: как сэкономить время при анализе поисковых запросов

Полезные дополнения

Загружаем расходы в Google Analytics

Инструмент:

OWOX BI Data Upload

Как работает. Загружает данные из «Google Таблиц» в Google Analytics. Для этого нужно:

1. Скачать и установить дополнение OWOX BI Data Upload.
2. Подготовить таблицу с данными по шаблону.

Пример заполненной по шаблону таблицы

3. Настроить экспорт — указать в какой аккаунт, представление, датасет Google Analytics нужно загрузить данные.

Указываем, куда нужно выгрузить данные

4. Отправить данные.

Когда пригодится. Когда нет возможности настроить автоматический импорт расходов в Google Analytics. Например, сейчас это невозможно сделать для кампаний МКБ в Директе.

Создаем систему веб-аналитики в «Google Таблицах»

Инструмент:

Supermetrics for Google Sheets

Как работает. Импортирует данные в «Google Таблицы» из разных источников — Facebook Ads, Google AdSense, Google Ads, Google Analytics, Instagram, MailChimp, «ВКонтакте», «Яндекс.Директ», «Яндекс.Метрика», YouTube и других. Созданный один раз отчет обновляется автоматически или по клику. Можно настроить автоматическую отправку отчетов по почте в форматах PDF, CSV, HTML и других.

Дополнение Supermetrics в каталоге

Инструмент платный. Доступен 14-дневный триал и бесплатная урезанная версия. Стоимость PRO — от 99 долл. в месяц при оплате за год.

Когда пригодится. Для работы с отчетами по контекстной рекламы и создания сквозной аналитики.

Импортируем данные из Google Analytics

Инструмент:

Google Analytics

Как работает. Подтягивает данные из Google Analytics в таблицу. Можно объединять данные из нескольких представлений и использовать их для разных расчетов, создания дашбордов. Построенные в таблицах отчеты обновляются автоматически — «Google Таблицы» обмениваются данными с Google Analytics по API.

Вот такой отчет можно сделать в таблицах с помощью официального дополнения Google Analytics

Когда пригодится. При создании внутренней или клиентской отчетности по контекстной рекламе, когда недостаточно встроенных возможностей Google Analytics, нужны кастомные формулы и визуализация.
На этом все. Однако, мы уверены, что это далеко не все полезные возможности и лайфхаки — «Google Таблицы» мощный и многофункциональный инструмент. Если вы знаете и используете в работе формулы, инструменты и дополнения, которых нет в этой статье — делитесь в комментариях. Давайте вместе сделаем самый большой в рунете справочник по «Google Таблицам» для специалистов по контекстной рекламе.