Шаблон анализа контентных пробелов для 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]

Именованные диапазоны для создания

Выберите ячейку порога и назовите её rank_threshold, чтобы формулы могли ссылаться на неё по имени.
Сделайте то же для min_volume и max_kd.
Добавьте однострочную заметку, напоминающую команде, из какого SEO-инструмента взяты экспорты, чтобы обновления оставались согласованными.

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

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

Цель: хранить чистый экспорт ваших собственных ранжируемых ключевых слов, с которым вкладка пробелов сможет сверяться.

Столбцы (A–E)

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

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

В E2 введите =IF(B2<=rank_threshold,"Covered","Weak") и протяните вниз.
Формула помечает ключевое слово значением Covered только тогда, когда ваша позиция превосходит порог из вкладки Setup.
Закрепите строку 1 и отсортируйте по убыванию объёма, чтобы самые сильные термины были вверху.

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

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

Цель: объединить экспорт ключевых слов каждого конкурента в одну вкладку с пометкой источника, чтобы ничто не анализировалось изолированно.

Столбцы (A–F)

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

Как это построить

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

Вкладка формулы сопоставления пробелов

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

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

Столбцы (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)

Примечания

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

Вкладка формулы оценки пробелов

Превратите сырые объём и сложность в единую оценку приоритета, по которой можно сортировать.

Цель: ранжировать пробелы по возможности, чтобы усилия шли на ключевые слова с лучшей отдачей, а не только с самым большим объёмом.

Добавьте столбец Score

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

Как работает оценка

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

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

Отфильтрованный, отсортированный дашборд пробелов для следующего брифинга, готовый передать авторам.

Цель: представить готовый короткий список, чтобы контент-лид мог распределять работу, не трогая вкладки с формулами.

Столбцы для показа

Ключевое слово, Тип пробела, Объём, Сложность, Оценка возможности
Назначено: [Ответственный]
Целевая дата публикации: [Дата]
Статус: [Идея/Бриф/В работе/Опубликовано]

Постройте представление

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

Как использовать этот шаблон

Продублируйте книгу и заполните вкладку Setup: ваш домен, до 3 конкурентов и ваши пороги.
Экспортируйте собственные ранжируемые ключевые слова из своего SEO-инструмента и вставьте их во вкладку Your Pages.
Экспортируйте ранжируемые ключевые слова каждого конкурента и сложите их во вкладку Competitor URLs, пометив доменом.
Дайте вкладке Gap Matching сверить два списка и пометить каждое ключевое слово как Missing, Weak или Covered.
Отфильтруйте строки Covered, чтобы в поле зрения остались только настоящие пробелы.
Примените формулу Gap Scoring, затем отсортируйте Priority View по оценке возможности.
Сократите короткий список до ключевых слов, соответствующих вашему намерению и бизнесу, затем назначьте ответственных и даты.
Ежемесячно повторно вставляйте свежие экспорты, чтобы формулы пересчитывались, а Priority View оставался актуальным.

Советы

Держите сырые экспорты на собственных входных вкладках и никогда не редактируйте их вручную; вся логика — во вкладках с формулами, чтобы обновление сводилось к простой замене вставкой.
Делите объём на сложность вместо сортировки только по объёму: ключевое слово с меньшим объёмом, по которому вы реально можете ранжироваться, часто лучше гигантского, по которому не можете.
Группируйте найденные пробелы в тематические кластеры перед брифингом, чтобы одна сильная опорная страница могла охватить несколько связанных ключевых слов сразу.
В Excel предпочитайте XLOOKUP вместо VLOOKUP; он не ломается при вставке столбцов и читается чётче, когда файл открывает кто-то другой.

Частые вопросы

Мне нужен Google Sheets, или это работает в Excel?

Работает в обоих. Структура вкладок и столбцы идентичны, и почти каждая формула общая. Единственные замены, которые стоит сделать, — это XLOOKUP вместо VLOOKUP и нативные FILTER/SORT, оба из которых ведут себя чуть чище в современных Excel и Sheets.

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

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

Откуда берутся экспорты ключевых слов?

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

Что на самом деле означает оценка возможности?

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

Почему некоторые ключевые слова показываются как ошибки?

Обычно поиск не может найти совпадение, часто из-за лишних пробелов или несогласованного регистра между вкладками. Оборачивание поисков в IFERROR это решает, а применение TRIM к вставленным ключевым словам убирает скрытые пробелы, которые тихо ломают совпадения.

Как часто перестраивать анализ?

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