20 можливостей Google Sheets, які дозволять заощадити час SEO-оптимізатору: функції, плагіни, макроси

Сьогодні Google Spreadsheets є гідною заміною для Microsoft Excel, такої необхідної програми для оптимізатора. Звичайно, багато функцій усічено і там, де в Excel було досить двох кліків, в Google таблицях необхідно встановити плагіни і писати скрипти. Однак, є основні функції та можливості, котрі можуть знадобитися. Раніше мій колега Євген Аралов вже розглядав основні прийоми та функції в Excel, що будуть  потрібні будь-якому SEO-спеціалісту. У цій статті ми розглянемо особливості Google Spreadsheets, які спростять та полегшать вашу роботу: плагіни, функції та кілька макросів.

1. Плагіни Google Sheets:

• Spreadsheet Google Analytics
• Search Analytics for Sheets
• Sort by Color
• Remove Duplicates
• Crop Sheet

2. Функції в Google Spreadsheets

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

3. Макроси для Google таблиць

• розфарбовуємо таблицю
• пошук дублікатів (так-так, ще один спосіб)

1. Google Sheets плагіни (Add-Ons) для роботи SEO-спеціалістів

1.1. Плагiн Spreadsheet Google Analytics

Оскільки GA є одним з основних інструментів, метрик SEO-оптимізатора, цей плагін буде найбільш корисним доповненням в таблицях.

За допомогою цього інструменту ви можете:

1. Отримувати дані з кількох подань.
2. Здійснювати користувацькі розрахунки.
3. Візуалізувати дані та впроваджувати ці діаграми, графіки на сторонні веб-сайти.
4. Запланувати звіти для автоматичного запуску та оновлення.
5. Контролювати, хто може переглядати ваші звіти, використовуючи налаштування конфіденційності Google Таблиць.

Як працювати з додатком:

1) Підключіть плагін до ваших таблиць.

Google Spreadsheets Add-On Google Analytics у магазинi Chrome

2) Перейдіть у “Додатки” → “Google Analytics” → “Create New Report”.
3) В полі «Name», заповніть назву звіту, який ви хочете створити.
4) В полях  «Select a view», виберіть аккаунт, ресурс і перегляд для майбутнього звіту.
5) В «Choose configuration options» налаштуйте потрібні параметри, метрики, сегменти.
6)Після натискання кнопки «Create a report», з’явиться окремий аркуш «Report Configuration»  з налаштуваннями майбутнього звіту.
7) Знову перейдіть у «Доповнення» → «Google Analytics», а потім виберіть вже «Run Reports».
8) Після завершення формування звіту з’явиться аркуш з назвою, котра була вказана на третьому етапі.

Щоб налаштувати автоматичне оновлення звіту, потрібно:

• перейти до «Додатки» → «Google Analytics» → «Schedule Reports»;
• поставити відмітку в чекбокс  «Enable reports to run automatically»;
• вибрати частоту оновлення звіту, в який час доби він експортуватиме дані з аналітики.

Давайте розглянемо приклад створення простого звіту про кількість відвідувачів з Google протягом тижня, згрупованого за днями і сторінками входу.

Крок 1. Налаштовуємо звіт, який ми хочемо створити.

Крок 2. У сформованих налаштуваннях змінюємо «30daysAgo» на «7daysAgo».

Крок 3. Запускаємо та отримуємо готовий звіт.

Детальніше про додаток можна дізнатися в довідці.

1.2. Search Analytics for Sheets

Інший досить важливий плагін для SEO-спеціаліста. За допомогою цього додатку можна вивантажити дані зі звіту “Ефективність” до Google Search Console за допомогою кількох кліків. При цьому, якщо перша тисяча рядків відображаються у самій панелі, то за допомогою цього плагіна ви можете вивантажити до 5 тис. запитів та URL .

Як вивантажити дані:

1) Встановіть плагін.

Посилання на плагін  в магазині додатків Google Chrome

2) Перейдіть до “Додатки” → «Search Analytics for Sheets» → «Open Sidebar».

3) У спливаючій бічній панелі на вкладці Requests  заповніть наступні поля:

• Verified Site – потрібний нам сайт.
• Date Range — діапазон дат, для яких потрібно вивантажити дані.
• Search Type – вибір типу пошуку: веб, зображення, відео.
• Group By – вибір критерію, за яким будуть згруповані дані (за запитами, сторінками, датою, пристроями тощо).
• Filter By (за замовчуванням без фільтрації)- можна відфільтрувати вивантаження за запитом, сторінкою, країною, пристроєм та видом в пошуку.
• Aggregation Type— вибір типу агрегування (об’єднання). Зазвичай залишаємо значення «Default (auto)». Якщо на вашому сайті відображається більше однієї URL-адреси, під час агрегації за властивостями Search Console буде підраховувати дані лише один раз (один показ, один клік, найвища позиція). Коли сторінки зведені, дані для кожної URL-адреси підраховуються індивідуально (тому, якщо ваш сайт відображається на першій сторінці з 7 URL-адресами для одного запиту, то єдиний запит буде рахувати 7 показів).
• Rows returned – кількість рядків: або 1 000 або 5 000. Якщо вибираєте “Everything” (всі рядки) все одно не буде вивантажено більше, ніж 5 тис. рядків.
• Result Sheet – це вибір аркуша, до якого експортуватимуться дані (можна створити новий або вивантажити його на існуючий).

4) Вивантажте звіт, натиснувши кнопку «Request Data».

Наприклад, вивантажимо дані про кліки, покази, CTR і середню позицію за датою та пристроєм.

Крок 1. Налаштовуємо проект.

Крок 2. На виході отримуємо таблицю з даними, згрупованими за датою та пристроєм.

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

1.3. Sort by Color

На відміну від Microsoft Excel, таблиці Google не забезпечують сортування за кольором тексту або клітинки. На допомогу приходить безкоштовний і корисний додаток Sort By Color.

Щоб відсортувати аркуш за кольором тексту або клітинки, потрібно:

1) Встановити плагін (посилання на додаток).
2) Виділити колонку, яку ви будете сортувати.
3) Перейти до “Додатки” → «Sort By Color»:

• Sort by Cell Color – за кольором клітинки;
• Sort by Font Color – за кольором тексту в комірці.

1.4. Remove Duplicates

Зручний плагін для пошуку унікальних рядків і дублікатів. Є один недолік – плагін платний ($20 на рік), але є безкоштовний період 30 днів для додатку, щоб “відчути”, щоб оцінити його необхідність.

Як користуватися додатком:

1) Встановити плагін (посилання на додаток у магазині Chrome).
2) Перейти до “Додатки” → «Remove Duplicates» → «Find duplicate or unique row».
3) Вибрати діапазон: Якщо таблиця була виділена раніше, діапазон вибирається автоматично . На цьому етапі можна зберегти резервну копію таблиці.
4) Вибрати тип даних для пошуку:

• Дублікати (знаходить повтори, виключаючи перший збіг).
• Дублікати  +1 (знаходить усі дублікати).
• Лише унікальні значення.
• Унікальні значення +1 (виділяє унікальні значення та перший збіг дублікатів).

5) Виберіть колонки, по яким будуть шукати даний критерій.
6) виберіть тип дії, яка буде застосовуватися до знайдених значень: Виділіть, скопіюйте або перенесіть значення, очистьте або видаліть рядки.
7) Після закінчення роботи плагіна буде наданий короткий звіт.

Давайте проаналізуємо роботу плагіна на прикладі знаходження неявних дублів з однаковою базовою, фразовою і точною частотою:

1.5. Crop Sheet

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

Посилання на Add-On у магазині Google Chrome

Crop to Data – обрізати таблицю до колонок та рядків з інформацією.
Crop to Selection – обрізати таблицю до виділеної області.

2. Функції в Google Sheets  для SEO оптимізаторів

Ми часто шукаємо способи прискорити конкретний процес нашої роботи. Часто виникає питання: “Чи можна це зробити/підрахувати в таблицях?” Відповідь завжди: Так, можна. Вам просто потрібно знати, як це зробити.

Далі опишемо кілька функцій, які ми використовуємо в нашій роботі. Ми не претендуємо на істину, і, можливо, деякі процеси можна зробити ще простіше і швидше. Тоді, як то кажуть, share your experience 🙂

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

2.1. =LEN – обчислює довжину рядка

Функція LEN підійде, наприклад, для швидкого підрахунку кількості символів в Title, Description, будь-якому іншому тексті.

=LEN(A1)  #в клітинці А1 знаходиться текст, в якому потрібно зробити підрахунок
=LEN("текст, в якому потрібно розрахувати кількість символів")

2.2. =CONCATENATE – об’єднання комірок/тексту з коміркою

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

Кілька варіантів, як ви можете використовувати цю функцію:

• Генерація шаблонних метаданих/текстів;
• Підготовка директив для Disavow;
• Підготовка директив перенаправлення для .htaccess тощо.

=CONCATENATE(A1:C1)                   #об'єднати текст з  трьох комірок поспіль
=CONCATENATE(A1:B1;D1)                #об'єднати текст з  з трьох комірок через одну
=CONCATENATE(A1:B1;"текст")           #об'єднати текст з двох комірок та статичного тексту
=CONCATENATE(C21&" "&D21)             #об'єднати текст з двох комірок з додаванням пропуску
=CONCATENATE(A1;" ";C1)               #об'єднати текст з двох комірок з додаванням пропуску

2.3. =TRIM и =SUBSTITUTE – видалення пропусків

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

• Під час генерації метаданих з’явилися зайві (подвійні) пропуски – у такому разі звертаємося до =TRIM.
• Часто під час переносу даних може виникнути проблема, що число в комірці написане у текстовому форматі, ще й з пропусками для розділення на розряди. У цьому випадку ви не зможете застосувати до нього будь-які арифметичні функції. Щоб позбутися пропуску і далі перетворити текст у число, необхідна функція =SUBSTITUTE.

=TRIM(A1)
=TRIM("текст,  з якого потрібно видалити зайві пропуски ")
=SUBSTITUTE(A1;" ";"")

2.4. =LOWER и =PROPER – зміна регістрів

Також дві функції, які ми розглянемо разом, оскільки вони мають одне завдання-змінити регістр тексту в комірці.

Наприклад, під час підготовки тексту для генерації метаданих у головному слові (наприклад, назва категорії) потрібно змінити регістр першої літери з верхнього на нижній регістр або навпаки. Якщо хтось досі робить це своїми руками –  припиніть цей мазохізм.

Скористайтеся функцією =PROPER, щоб перетворити першу літеру слова з малої в велику, а =LOWER – з великої в малу.

=LOWER(A1)
=LOWER("текст, в якому потрібно змінити регістр літер")
=PROPER(A1)
=PROPER("текст, в якому потрібно змінити регістр літер")

2.5. =UNIQUE – унікальні значення діапазону

Оскільки в Google Sheets з якоїсь причини ще досі немає чарівної кнопку “видалити дублікати”, всі викручуються, як можуть. Коли є один список з даними, і потрібно виділити унікальні значення – ви можете сміливо використовувати функцію =UNIQUE.

Приклад, коли він стане в нагоді:
Нам потрібні всі URL сайту. Парсимо сайт, вивантажуємо дані з Яндекс.Вебмайстер, беремо адреси з Sitemap, об’єднуємо це все в один список. Природно, що багато URL будуть повторюватися. Для цього в сусідньому стовпчику ми прописуємо формулу  =UNIQUE,  вказавши потрібний діапазон.

=UNIQUE(A1:A1000)
=UNIQUE(A:A)

2.6. =SPLIT – поділ за колонками

У Google Таблицях є два способи розділення тексту у комірці по колонках: через функцію =SPLIT або через кнопку «Розділити на колонки».

Я прихильник першого способу, оскільки у нього є беззаперечний плюс – можливість вказати кілька роздільників одночасно.

=SPLIT(C3;"—") # розділити текст в комірці C3 по довгому тире
=SPLIT(C3;" ") # розділити текст в комірці C3 по пропуску
=SPLIT(C3;":"&"/") # розділити текст в комірці C3 по двокрапці та слешу

2.7. =VLOOKUP – пошук по першій  колонці діапазону і повернення значення зі знайденої комірки

Функція =VLOOKUP – незамінна,  заощаджує багато часу формула для об’єднання даних з двох таблиць.

Можливих варіантів ситуацій, коли може знадобитися формула – безліч. Наведемо приклад використання функції =VLOOKUP у зборі семантики.

Той, хто використовує сервіс Just-Magic для кластеризації семантики, знає, що під час вивантаження ми отримуємо список ключових фраз і номер кластера. Для гуманізації ім’я кластера, буде корисне =VLOOKUP.

Крок 1. Щоб замінити номер групи на назву, в першу чергу, нам потрібна таблиця з номером групи (отриманим в JM), запитами і базовою частотою. Сортуємо її за номеру групи і частотою, щоб перший запит в кластері був самим частотним.

Крок 2. Копіюємо колонки з номером групи та запитами та ставимо на інший аркуш.

Крок 3. Видаляємо дублікати за колонкою з номером кластера. Тобто після цієї маніпуляції ви повинні отримати такий масив даних, де для кожного номеру групи буде один самий частотний запит.

Крок 4. Повертаємося на перший аркуш і в вибраній комірці прописуємо =VLOOKUP (запит; діапазон; номер_колонки; [відсортовано]).

Для більш наочного прикладу, формула може виглядати наступним чином:

=VLOOKUP(A1; 'Лист2'!$A$1:$B$22; 2; 0)

Крок 5. Простягаємо функцію вниз (або використовуємо =ARRAYFORMULA, докладніше опишемо нижче) і, вуаля, для кожного запиту у вас є читабельна назва кластеру.

Таким же чином можна додати в свою таблицю з семантикою дані про сторінку, що  ранжирується, і позиції за запитом із SeoLib.

2.8. =ARRAYFORMULA – протягуємо формулу вниз

Щоб формулу з однієї комірки поширити по всій колонці, можна її протягнути або копіпаснути. А можна використовувати функцію = ARRAYFORMULA, яка за кілька секунд поширить формулу по колонці або потрібному діапазону.

На прикладі функції =VLOOKUP, =ARRAYFORMULA матиме такий вигляд:

=ARRAYFORMULA(VLOOKUP(A2:A23;'Лист2'!$A$2:$B$11;2;0))

Для тих, хто не любить читати довідку: під час форматування багатьох функцій, затиснувши Ctrl+Shift+Enter, ви автоматично додасте =ARRAYFORMULA).

2.9. =REGEXEXTRACT – витягаємо потрібний фрагмент тексту з комірки

Зручна функція, якщо ви хочете отримати певну частину тексту, що відповідає регулярному виразу.

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

=REGEXEXTRACT(A1;"^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)")

Ви можете отримати будь-який текст з комірки, лише потрібно знати, як 🙂

2.10. =IF – відображає результат логічної перевірки

Варіацій формул з =IF і способів їх застосування безліч. Наприклад, щоб не користуватися умовним форматуванням «Текст містить», розберемо використання функції =IF спільно з =REGEXMATCH для виділення геонезалежних запитів з топонімом «спб».

=IF(REGEXMATCH(B12;"спб");"1";"0") # значення 1 в прикладі – true, 2 – false

Таким чином, у наступній колонці у вас буде маркер, який ідентифікує запити з “SPb”.

2.11. =IMPORTXML – імпортувати дані з сайту в таблицю

Одна з найважливіших функцій, якою просто зобов’язаний користуватися кожен SEOшник, який цінує свій час. Наприклад, ви аналізуєте конкурентів (10 штук), і вам потрібно порівняти Title вашої сторінки, що просувається, з їхніми.

Як ви можете це зробити:
1) руками копіювати <title> кожної сторінки і вставляти в таблицю – 20 хвилин вашого часу;
2) спарсити Title якимось краулером – 10 хвилин вашого часу;
3) використовувати =IMPORTXML – 10 секунд і все готово.

Кілька запитів XPath, які можуть стати в нагоді:

XPath	Для чого
"//title"	вивантажити Title
"//h1"	вивантажити H1
"//meta[@name='description']/@content"	вивантажити Description
"//meta[@name='keywords']/@content"	вивантажити Keywords
"//link[@rel='canonical']/@href"	вивантажити Canonical
"//link[@rel='alternate']/@href"	вивантажити Alternate
"//@href"	вивантажити всі посилання на сторінці
"//a[contains(@href, 'mailTo:') or contains(@href, 'mailto:')]/@href"	вивантажити email зі сторінки
"//a[contains(@href, 'ok.ru/') or contains(@href, 'vk.com/feed') or contains(@href, 'twitter.com/') or contains(@href, 'facebook.com/')]/@href"	вивантажити соціальні мережі

 

Насправді, є безліч функцій і варіантів їх використання в роботі SEO-фахівця. Головне,  поставити собі питання: чи можна те, на що я витрачаю багато часу, зробити швидше? Пошукати трохи в інтернеті (відразу скажу, що шукати інформацію краще англійською мовою, об’єктивно її більше), поцікавитися у колег, як роблять вони. І, швидше за все, ви знайдете рішення.

3. Макроси для Google Таблиць, які можуть стати в нагоді під час роботи.

Якщо у Microsoft Excel є VBA, Google Таблиць – редактор скриптів. Код пишеться на JS, інформації в мережі дуже багато, і навіть не програмісту неважко написати маленький легкий скрипт для своєї таблиці.

Я використовую скрипти тільки для розфарбовування таблиць і як ще один спосіб підсвічування рядків з дублікатами.

3.1. Розфарбовуємо таблицю

Якщо у вас є таблиця зі статусами завдань, вам знадобиться цей скрипт. Так, можна використовувати умовне форматування замість нього, але буде зафарбована лише одна комірка. За допомогою скрипта – зафарбовується увесь рядок.

Як він працює:

Скрипт:

function setRowColors() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var statusColumnOffset = getStatusColumnOffset();

  for (var i = range.getRow(); i < range.getLastRow(); i++) {
    rowRange = range.offset(i, 0, 1);
    status = rowRange.offset(0, statusColumnOffset).getValue();
    if (status == 'Completed') {
      rowRange.setBackgroundColor("#99CC99");
    } else if (status == 'In Progress') {
      rowRange.setBackgroundColor("#FFDD88");    
    } else if (status == 'Not Started') {
      rowRange.setBackgroundColor("#CC6666");          
    }
  }
}

function getStatusColumnOffset() {
  lastColumn = SpreadsheetApp.getActiveSheet().getLastColumn();
  var range = SpreadsheetApp.getActiveSheet().getRange(1,1,1,lastColumn);

  for (var i = 0; i < range.getLastColumn(); i++) {
    if (range.offset(0, i, 1, 1).getValue() == "Status") {
      return i;
    } 
  }
}

3.2. Пошук дублікатів (так-так, ще один спосіб)

Якщо ви не хочете купувати плагін Remove Duplicates, можете використовувати урізаний варіант у вигляді скрипта. Він хороший тим, що підсвічує весь рядок. Якщо його дописати, можна налаштувати пошук дублів за кількома параметрами.

Скрипт:

function findDuplicates() {
  var CHECK_COLUMNS = [2];

  var sourceSheet = SpreadsheetApp.getActiveSheet();
  var numRows = sourceSheet.getLastRow();
  var numCols = sourceSheet.getLastColumn();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var newSheet = ss.insertSheet("FindDupes");

  for (var i = 0; i < CHECK_COLUMNS.length; i++) {
    var sourceRange = sourceSheet.getRange(1,CHECK_COLUMNS[i],numRows);
    var nextCol = newSheet.getLastColumn() + 1;
    sourceRange.copyTo(newSheet.getRange(1,nextCol,numRows));
  }

  var dupes = false;
  var data = newSheet.getDataRange().getValues();
  for (i = 1; i < data.length - 1; i++) {
    for (j = i+1; j < data.length; j++) {
      if  (data[i].join() == data[j].join()) {
        dupes = true;
        sourceSheet.getRange(i+1,1,1,numCols).setBackground("red");
        sourceSheet.getRange(j+1,1,1,numCols).setBackground("red");
      }
    }
  }

  ss.deleteSheet(newSheet);

  if (dupes) {
    Browser.msgBox("Possible duplicate(s) found and colored red.");
  } else {
    Browser.msgBox("No duplicates found.");
  }
};

 

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

Еще по теме:

• Excel для SEO. Основи з прикладами
• Як знайти биті посилання на сайті: огляд 4 зручних інструментів
• Як користуватися операторами Яндекс Wordstat