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

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

Google Sheets

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

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

1.1. Плагін Spreadsheet Google Analytics

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

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

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

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

  1. Підключіть плагін до своїх таблиць. Google Spreadsheets Add-On Google Analytics в магазині 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) Встановити плагін.
2) Перейти в «Доповнення» → «Remove Duplicates» → «Find duplicate or unique row».
3) Вибрати діапазон: якщо таблиця була виділена раніше, діапазон вибирається автоматично. На цьому етапі можна зберегти бекап таблиці.
4) Вибрати тип даних для пошуку:

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

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

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

1.5. Crop Sheet

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

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

Crop to Selection - обрізати таблицю до виділеної області.

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

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

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

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

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

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, '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.");
  }
};
 

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

Фото: flickr.com
Обробка: Vinci
назад
далі