12 корисних функцій в Excel та Google Sheets для роботи з рекламними оголошеннями

Працюєте з Google Ads і хочете оптимізувати свої кампанії якомога ефективніше? Тоді цей матеріал саме для вас. Зібрали корисні формули для Google Таблиць та Excel, які спростять роботу з рекламними оголошеннями та підвищать продуктивність. Разом з Владиславом Платоновим, Senior-фахівцем з контекстної реклами, розказуємо, як видаляти пробіли та знаки плюса, змінювати регістр тексту, об'єднувати ключові слова та візуалізувати отримані результати.

Основні формули для роботи зі звітністю Google Ads

IMPORTRANGE: імпорт даних з інших аркушів

Ця формула дозволяє імпортувати дані з інших таблиць за допомогою посилання на вихідну таблицю і фіксованого діапазону. Фіксований діапазон означає, що формула НЕ БУДЕ брати до уваги нові рядки або стовпчики, які можуть з’явитися в вихідній таблиці, і НЕ БУДЕ додавати їх до файлу, в який ми імпортуємо. Формула буде оновлювати тільки ті дані, які вже належать до заданого діапазону.

Для того, щоб здійснити імпорт статичних даних, необхідно: 

1. Викликати функцію IMPORTRANGE.
2. Вказати в дужках "_" посилання на файл, з якого ми хочемо імпортувати дані.
3. Вказати статичний діапазон даних для імпорту у форматі "'Назва аркуша' Діапазон даних для імпорту". 

‍

Наприклад, 

=IMPORTRANGE("посилання на файл";"'Лист1'!A1:E16")

‍

Щоб імпортувати динамічний діапазон з таблиці, який автоматично оновлюватиметься, варто спочатку задати йому ім'я та створити посилання на цей іменований діапазон при імпорті. Тоді нові дані будуть автоматично враховуватися у динамічному діапазоні, який імпортується.

‍

Детально показуємо цей процес на скрінах нижче: 

‍

Після цього ми можемо замість зазначення фіксованого діапазону у форматі "'Назва аркуша' Діапазон даних для імпорту", вказати лише назву іменованого діапазону і все. 

‍

Наприклад,

=IMPORTRANGE("посилання на файл";"'Діапазон3").

IMPORTXML: перевірити назву та зміст H1 на цільових сторінках

Функція IMPORTXML дає змогу витягувати дані з сайтів за допомогою Xpath запитів. 

‍

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

Стосовно нашого прикладу. Щоб витягнути дані з сайту, нам необхідно задати параметри пошуку необхідної інформації. У нас це тег <a>, який знаходиться в class з ім'ям "products-list-item__info"

Копіюємо це в нашу формулу, як зображено на скріні.

‍

=IMPORTXML(A1;”//div[@class=’products-list-item__info’]/a”)

За допомогою цієї функції ми можемо отримати різноманітну інформацію з сайту, наприклад, посилання, заголовки товарів, опис, ключові запити, які використовуються в SEO розмітці та інші, завдяки вивантаженню необхідних даних.

‍

Щоб було простіше почати, нижче ми продемонстрували таблицю з функціями, які використовуються найчастіше:

GOOGLETRANSLATE: переклад ключових слів та іншого тексту

Функція створена для перекладу тексту на іншу мову. Чим вона корисна при роботі з Google Ads? Коли ми працюємо з іноземними проєктами, то в будь-якому випадку необхідно перекладати рекламні оголошення. І зазвичай їх не 1-10, а 1000 або 10 000. Тому ця команда особливо корисна, бо вручну перекладати такий масив дуже виснажливо та супер довго.

‍

Показуємо, як вона працює на прикладі.

‍

=GOOGLETRANSLATE(A8;"uk";"en")

Для того, щоб перекласти необхідний текст, потрібно: 

1. Викликати функцію GOOGLETRANSLATE.
2. Вказати клітинку з текстом для перекладу; мову оригінального тексту; та мову тексту, на яку ми хочемо перекласти. 

‍

Аби спростити формулу ми можемо автоматично визначити оригінальну мову, вказуючи замість коду мови фразу "auto".

‍

=GOOGLETRANSLATE(A8;"auto";"uk")

Якщо за допомогою "auto" не можна визначити мову оригінального тексту, використовуємо функцію Detectlanguage.

‍

=GOOGLETRANSLATE(A2;DETECTLANGUAGE(A2);"uk")

Конвертування літер в інший регістр

Іноді назви брендів або інші слова написані великими літерами, або всі слова оголошення написані з маленької літери, а нам потрібно зробити одне слово з великої літери.

‍

Наприклад, для Google неприпустимо, аби в оголошеннях всі слова були написані у великому регістрі. Він зчитує це як помилку і відхиляє креатив (навіть якщо половина слів є правильними, а половина — у великому регістрі). Тому завдяки цим командам ми можемо виправити наявні похибки швидко та у великому обсязі. 

‍

Функція LOWER — призначена для зміни регістру тексту, зі значеннями, які були написані ВЕЛИКИМИ літерами, на значення малими літерами, наприклад:

‍

=LOWER(A6)

Є і дзеркальна для LOWER функція — UPPER, вона переводить регістр з малих літер на ВЕЛИКІ:

‍

=UPPER(A2)

Існує ще функція PROPER для переведення регістру в прописний вигляд (таким чином перша буква буде з великої, решта — з малої).

‍

=PROPER(A10)

LEN для підрахунку символів

Функція LEN призначена для швидкого підрахунку символів у клітинці. Це особливо корисно під час написання текстів оголошень, адже в заголовках та описі оголошень є обмеження за кількістю символів.

‍

=LEN(A2)

TRIM для видалення пробілів на початку або наприкінці клітинки

Функція TRIM призначена для видалення зайвих (більше одного поспіль) пропусків у тексті клітинки. Корисна функція, особливо коли маємо справу з великим обсягом рекламних оголошень.

‍

=TRIM(A2)

SUBSTITUTE для заміни ключових слів

Функція SUBSTITUTE призначена для заміни тексту. З її допомогою можливо замінити або слово, або фразу на необхідну для вас.

‍

Функція виглядає ось так:

=SUBSTITUTE(текст, у якому буде пошук і заміна; рядок, який потрібно знайти; на що буде проводитися заміна; НЕОБОВ'ЯЗКОВИЙ! порядковий номер входження).

‍

У нашому прикладі:

‍

=SUBSTITUTE(A2;”GoogleMerchantCenter”;”GMB”)

Ця функція використовується для групового редагування одразу великої кількості клітинок із заміною слів, окремих символів та їхніх комбінацій.

IF для порівняння

Функція IF призначена для базових логічних обчислень. Наприклад, задаємо умову, аби отримати відповідь: обрана вами клітинка більша за значення 500 чи ні.

‍

Для кращого розуміння розберемо на прикладі:

=IF(Значення>50; "Значення при дотриманні умови" ; "Значення при недотриманні умови");

‍

І отримаємо таку функцію: =IF(B2>50; "TRUE"; "FALSE")

‍

=IF(B2>50; "Так"; "Ні")

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

‍

Функцію можна використовувати для:

• Підготовки оголошень (наприклад відповідних за кількістю символів)
• Оцінки ефективності 
• Заміру динаміки і т.д.

VLOOKUP для швидкого пошуку результатів

Функція VLOOKUP призначена для пошуку значень у певному діапазоні, де проводиться пошук за одним стовпцем обраного діапазону.

‍

Формула має такий вигляд:

=VLOOKUP(потрібне значення; таблиця, у першому стовпчику, якої шукаємо потрібне значення; номер стовпчика, з якого вставляємо дані; точність збігу (TRUE/FALSE))

‍

Детальніше розберемо на прикладі.

Припустимо, у нас є таблиця з моделями телефону та їхніми параметрами:

Нам необхідно з усієї цієї таблиці зрозуміти наявність тільки певних моделей телефону і розташувати їх вибірково та в іншій послідовності.

Для того, щоб вручну не шукати потрібні нам значення і не вписувати їх наявність у нашу таблицю, нам і необхідна функція VLOOKUP.

‍

=VLOOKUP(A18;$B$2:$F14;3;FALSE)

Ця формула дозволяє знайти значення у вихідній таблиці за певним критерієм. Розглянемо її складові:

‍

=VLOOKUP(A18;$B$2:$F$14;3;FALSE)

• A18 — це клітинка, де ми вказуємо критерій пошуку. У нашому випадку це модель “Телевізор A6”.

‍

• $B$2:$F$14 — це діапазон вихідної таблиці, де ми шукаємо значення. ВАЖЛИВО! Діапазон повинен починатися з того стовпчика, де знаходиться критерій пошуку. У нашому випадку це стовпчик “Модель” (B). Щоб можна було застосувати формулу до всієї таблиці, ми фіксуємо діапазон знаками $.

‍

• 3 — це номер стовпчика вихідної таблиці, з якого ми хочемо отримати значення. Нумерація починається з першого стовпчика діапазону. У нашому випадку нам потрібен стовпчик “Наявність” (D), який є третім за рахунком.

‍

• FALSE — це параметр, який вказує, чи потрібен нам точний збіг критерію пошуку. Якщо ми пишемо FALSE або 0, то ми шукаємо лише точні збіги. Якщо ми пишемо TRUE або 1, то ми шукаємо схоже значення.

‍

Таким чином, ця формула шукає модель “Телевізор A6” у стовпчику B і повертає значення з третього стовпчика D (“Наявність”) для цієї моделі.

CONCATENATE для об'єднання клітинок разом

Функція CONCATENATE призначена для об'єднання двох клітинок в одну. Це може бути корисним під час підготовки рекламних оголошень. Наприклад, поєднуючи кілька УТП в один опис. Розглянемо цю функцію більш детально на прикладі:

‍

=CONCATENATE(A2;A3;A6)

У такий спосіб ми можемо буквально склеїти значення кількох клітинок, і в разі потреби між клітинками можна також вставляти текст:

=CONCATENATE(A2;"-";A5)

SUMIF для групування речей разом

Функція SUMIF призначена для підсумовування даних у клітинках за заданим правилом. Це вкрай корисна функція для формування звітності.

‍

Завдяки їй ми можемо витягнути з масиву даних потрібну нам інформацію в тому форматі, який нам необхідний. Припустимо, що у нас є вивантаження даних зі значеннями:

У цьому списку нам потрібно порахувати, скільки було продажів і повернень кожної моделі за підсумком представлених періодів. Для цього нам потрібно внести такі дані у функцію:

=SUMIF(Діапазон для пошуку моделі;Модель, яку ми шукаємо в масиві;Діапазон необхідних даних для підсумовування за відповідності попередніх двох умов). 

‍

Після чого отримуємо результат:

=SUMIF($B$1:$B$27;F2;$C$1:$C$27)

Форматування та візуалізація інформації

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

‍

У Google Sheets є можливість оформити дані у вигляді графіків і діаграм (кругових, стовпчастих, лінійних та інших). Для цього виберіть необхідний діапазон: в меню Google Sheets переходимо “Вставка” — “Діаграма”. І нам відкриється низка можливостей щодо кастомізації візуалізації:

У такий спосіб ми можемо перепакувати інформацію в легший для сприйняття формат графіків і діаграм.

Можливості для створення рекламних кампаній

За допомогою Google Sheets також можна створювати одразу готові шаблони для цілих рекламних кампаній. Для цього потрібно розуміти структуру побудови рекламних кампаній. Приміром, у Google Ads. Після заповнення потрібних параметрів у правильній послідовності необхідно експортувати все до рекламного акаунту. Задля полегшення формування такої структури, можна імпортувати вже готовий шаблон з рекламної кампанії та адаптувати його під свої вимоги. Виглядає це наступним чином:

Автоматизація для рекламних кампаній

Крім усіх перерахованих вище переваг у Google Sheets є і можливість автоматизувати інформацію за допомогою кастомних макросів або скриптів чи за допомогою додаткових сервісів. Розглянемо обидва варіанти.

‍

Почнемо з варіанту автоматизації даних у таблицях за допомогою сервісів. Найчастіше ми використовуємо сервіс Supermetrics. За допомогою нього ми можемо отримувати дані з різних джерел, агрегувати їх та автоматично оновлювати. До того ж цей сервіс знижує шанс їхнього семплювання і показує чистіші звіти.

‍

Семплювання даних – принцип, коли із загального масиву даних беруть частину і на її основі будують звітність.

‍

Цей інструмент може отримувати дані з різних джерел. Проте найчастіше використовуються:

• Google Ads
• Meta Ads (Facebook та Instagram)
• Google Analytics
• TikTok Ads
• Pinterest Ads
• Bing Ads 
• та інші рекламні інструменти, і не тільки.

‍

У підсумку, ми можемо зібрати вивантажені дані з різних джерел у одному аркуші Google Sheets, що може автоматично оновлюватися відповідно до наших налаштувань. Крім того, такий аркуш менш схильний до помилок, які можуть виникнути при ручному введенні даних. За фактом роботи звіт можна скласти один раз у потрібному нам вигляді і завжди мати актуальну інформацію. У сукупності це дуже спрощує роботу та дозволяє ефективніше використовувати час.

‍

Що стосується кастомних варіантів автоматизації, тут у нас більш гнучкі можливості і варіації, наприклад:

• макрос, який перетворює розгорнутий тип оголошень на адаптивний;
• скрипт, який перевіряє залишки бюджетних коштів на акаунті та надсилає сповіщення, коли кошти майже закінчуються для своєчасного поповнення;
• скрипт, який генерує список YouTube каналів або відео, які відповідають нашим параметрам. А потім використовувати ці дані для створення потрібних аудиторій у таргетингу.

Безумовно, автоматизація має безліч можливостей, але важливо зосередитися на своїх завданнях та знайти варіанти автоматизації, що підходять саме під них. Створення кастомних рішень майже не має обмежень, проте варто враховувати ліміти, які є у Google Sheets. А саме:

• До 10 мільйонів клітинок для електронних таблиць, створених або перетворених у Google Sheets;
• 40 000 нових рядків за один раз;
• Максимальна кількість стовпців: 18 278 стовпців;
• Кількість вкладок: 200 аркушів у книзі;
• Формули GoogleFinance: 1000 формул GoogleFinance;
• Формули ImportRange: 50 довідкових формул для різних книг;
• Формули ImportData, ImportHtml, ImportFeed або ImportXml: 50 функцій для зовнішніх даних;
• Максимальна довжина рядка — 50 000 символів.

‍