Шаблон аналізу контентних розривів для Google Sheets

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

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

6 готових варіантів

Вкладка налаштувань та інструкцій

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

Мета: налаштувати книгу один раз, щоб кожна інша вкладка читала з єдиного набору вхідних даних, а не з жорстко прописаних значень.

Клітинки для заповнення

  • Ваш домен: [yourdomain.com]
  • Домени конкурентів 1–3: [competitor-a.com], [competitor-b.com], [competitor-c.com]
  • Поріг ранжування (ви «покриваєте» ключове слово, якщо ранжуєтеся в топі): [20]
  • Мінімальний місячний обсяг для збереження: [50]
  • Макс. складність ключового слова, яку ви націлюєте: [40]

Іменовані діапазони для створення

  1. Виберіть клітинку порога й назвіть її rank_threshold, щоб формули могли посилатися на неї за назвою.
  2. Зробіть те саме для min_volume і max_kd.
  3. Додайте однорядкову примітку, що нагадує команді, з якого SEO-інструмента взято експорти, щоб оновлення залишалися узгодженими.

Вкладка ваших сторінок

Імпортуйте кожне ключове слово, за яким уже ранжується ваш сайт, щоб книга знала, що ви покриваєте.

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

Стовпці (A–E)

  • A – Ключове слово: [вставте експорт ключових слів]
  • B – Ваша позиція: [поточна позиція]
  • C – URL ранжування: [ваш URL]
  • D – Обсяг: [місячні пошуки]
  • E – Покрито?: формула нижче

Формула для стовпця E

  1. У E2 введіть =IF(B2<=rank_threshold,"Covered","Weak") і протягніть донизу.
  2. Формула позначає ключове слово значенням Covered лише тоді, коли ваша позиція перевершує поріг із вкладки Setup.
  3. Закріпіть рядок 1 і відсортуйте за спаданням обсягу, щоб найсильніші терміни були вгорі.

Вкладка URL конкурентів

Складіть ранжовані ключові слова конкурентів в один список, який формула розривів зможе просканувати.

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

Стовпці (A–F)

  • A – Ключове слово: [ключове слово конкурента]
  • B – Домен конкурента: [який конкурент]
  • C – Їхня позиція: [позиція]
  • D – Їхній URL ранжування: [URL конкурента]
  • E – Обсяг: [місячні пошуки]
  • F – Складність ключового слова: [KD]

Як це побудувати

  1. Вставляйте експорт кожного конкурента під попереднім, зберігаючи в стовпці B правильний домен.
  2. Додайте =COUNTIF(B:B,[competitor-a.com]) над таблицею, щоб перевірити кількість рядків для кожного конкурента.
  3. Видаліть очевидні брендові терміни й навігаційні запити перед оцінюванням; вони рідко є справжніми розривами.

Вкладка формули зіставлення розривів

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

Мета: позначити кожне ключове слово конкурента, за яким ви або не ранжуєтеся, або ранжуєтеся слабко. Це ядро аналізу.

Стовпці (A–E)

  • A – Ключове слово: витягніть унікальні ключові слова з вкладки Competitor URLs.
  • B – Ви ранжуєтеся?: =IFERROR(VLOOKUP(A2,'Your Pages'!A:B,2,FALSE),"No")
  • C – Тип розриву: =IF(B2="No","Missing",IF(B2>rank_threshold,"Weak","Covered"))
  • D – Обсяг: =IFERROR(VLOOKUP(A2,'Competitor URLs'!A:E,5,FALSE),0)
  • E – Складність: =IFERROR(VLOOKUP(A2,'Competitor URLs'!A:F,6,FALSE),0)

Примітки

  1. В Excel замініть VLOOKUP на XLOOKUP для чистішого зіставлення, стійкого до напрямку.
  2. Рядки «Missing» і «Weak» — це ваші розриви; рядки «Covered» можна відфільтрувати.
  3. Обгортайте пошуки в IFERROR, щоб незіставлені ключові слова повертали чисте значення, а не помилку.

Вкладка формули оцінювання розривів

Перетворіть сирі обсяг і складність на єдину оцінку пріоритету, за якою можна сортувати.

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

Додайте стовпець Score

  • Спершу відфільтруйте: залиште лише рядки, де обсяг ≥ min_volume і складність ≤ max_kd.
  • Оцінка можливості: =ROUND((D2/(E2+1))*IF(C2="Missing",1.5,1),1)
  • Тег наміру: [Інформаційний/Комерційний/Транзакційний]
  • Кластер: [Тематичний кластер, до якого це належить]

Як працює оцінка

  1. Обсяг, поділений на складність, винагороджує ключові слова, які часто шукають, але за які легше конкурувати.
  2. Множник 1.5 підіймає повністю відсутні теми вище за ті, де ви вже ранжуєтеся слабко.
  3. Коригуйте ваги під свою нішу; формула — це відправна точка, а не вирок.

Вкладка пріоритетного подання

Відфільтрований, відсортований дашборд розривів для наступного брифування, готовий передати авторам.

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

Стовпці для показу

  • Ключове слово, Тип розриву, Обсяг, Складність, Оцінка можливості
  • Призначено: [Відповідальний]
  • Цільова дата публікації: [Дата]
  • Статус: [Ідея/Бриф/У роботі/Опубліковано]

Побудуйте подання

  1. Використайте =SORT(FILTER(...)) у Sheets або зведену таблицю, щоб автоматично витягувати розриви з найвищою оцінкою.
  2. Додайте умовне форматування, щоб рядки «Missing» і високі оцінки виділялися з першого погляду.
  3. Оновлюйте вхідні вкладки щомісяця; Priority View оновлюється сам, бо читає з формул.

Як використовувати цей шаблон

  1. Дублюйте книгу й заповніть вкладку Setup: ваш домен, до 3 конкурентів і ваші пороги.
  2. Експортуйте власні ранжовані ключові слова зі свого SEO-інструмента й вставте їх у вкладку Your Pages.
  3. Експортуйте ранжовані ключові слова кожного конкурента й складіть їх у вкладку Competitor URLs, позначивши доменом.
  4. Дайте вкладці Gap Matching звірити два списки й позначити кожне ключове слово як Missing, Weak або Covered.
  5. Відфільтруйте рядки Covered, щоб у полі зору залишилися лише справжні розриви.
  6. Застосуйте формулу Gap Scoring, потім відсортуйте Priority View за оцінкою можливості.
  7. Скоротіть короткий список до ключових слів, що відповідають вашому наміру й бізнесу, потім призначте відповідальних і дати.
  8. Щомісяця повторно вставляйте свіжі експорти, щоб формули перераховувалися, а Priority View залишався актуальним.

Поради

  • Тримайте сирі експорти на власних вхідних вкладках і ніколи не редагуйте їх вручну; уся логіка — у вкладках із формулами, щоб оновлення зводилося до простої заміни вставкою.
  • Ділíть обсяг на складність замість сортування лише за обсягом: ключове слово з меншим обсягом, за яке ви реально можете ранжуватися, часто краще за гігантське, за яке не можете.
  • Групуйте знайдені розриви в тематичні кластери перед брифуванням, щоб одна сильна стовпова сторінка могла охопити кілька пов'язаних ключових слів одразу.
  • В Excel надавайте перевагу XLOOKUP над VLOOKUP; він не ламається під час вставлення стовпців і читається чіткіше, коли файл відкриває хтось інший.

Поширені запитання

Мені потрібен Google Sheets, чи це працює в Excel?

Працює в обох. Структура вкладок і стовпці ідентичні, і майже кожна формула спільна. Єдині заміни, які варто зробити, — це XLOOKUP замість VLOOKUP і нативні FILTER/SORT, обидва з яких поводяться трохи чистіше в сучасних Excel і Sheets.

Скільки конкурентів завантажувати?

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

Звідки беруться експорти ключових слів?

Підійде будь-який SEO-інструмент, що експортує ключові слова, за якими ранжується домен; ви вставляєте цей експорт у вхідні вкладки. Шаблон навмисно не прив'язаний до інструмента, тож використовуйте будь-яку платформу, яка вже у вас є, і зберігайте те саме джерело щомісяця для узгоджених порівнянь.

Що насправді означає оцінка можливості?

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

Чому деякі ключові слова показуються як помилки?

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

Як часто перебудовувати аналіз?

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