|
Несомненно, анализ обратных ссылок конкурентов является одним из ключевых компонентов SEO стратегии. Анализ необходим для того, что бы понять, какие они используют методы оптимизации и продвижения, какое направление и стратегию выбрали и насколько активно действуют. Он помогает уменьшить затраты, выбрать правильное направление, чтобы обойти их в выдаче поисковых машин. В общем без анализа конкурентов, начинать продвигать сайт просто неразумно и может многого вам стоить, как в денежном, так и во временном отношении.
Анализировать сайты вручную, особенно в высококонкурентных нишах, когда на сайты ссылаются тысячи других сайтов, весьма проблематично. В этом случае, на помощь приходят специальные сервисы, например Moz Link Intersect tool. Но я решил разработать собственный инструмент на основе Excel, который обладает большей гибкостью в обработке данных.
В этой статье я пошагово расскажу, как работать с этой таблицей (шаблон прилагается).
Вы вероятно знаете о существовании сервиса Moz Link Intersect (известного как Competitive Link Research Tool или Competitive Link Finder), который находит домены, ссылающиеся на ваших конкурентов. В настоящее время он недоступен, но из достоверных источников мне известно, что в будущем его работа возобновится.
Моя Excel версия также позволяет находить ссылающиеся домены, конкретный URL, с которого ссылаются и некоторые дополнительные параметры.
Почему именно Excel?
Вот преимущества Excel перед другими инструментами:
Большинство сервисов, предоставляющие бэклинки конкурентов, обладают дополнительными функциями и требуют ежемесячной подписки. Excel бесплатен и скорее всего у вас уже есть.
Если вы работаете онлайн, Excel - одна из самых полезных программ в вашем распоряжении. Она просто незаменима при анализе и визуализации данных и имеет множество плагинов, которые значительно облегчат вашу ежедневную деятельность. Кроме того, большинство крупных онлайн сервисов предоставляют возможность экспортировать данные прямо в Excel.
Кроме того, Excel версия позволит вам:
Использовать данные о бэклинках любого провайдера.
Сортировать данные так, как вам это нужно.
Анализировать столько конкурентов, сколько необходимо.
Орудия труда.
Вот список того, что вам понадобится:
Как ни удивительно, вам потребуется Excel. Я использовал 32-битную версию 2010 в моем примере, но и другие версии должны работать также хорошо.
Данные об обратных ссылках. Вы можете получить их в Moz OSE, Ahrefs Site Explorer, MajesticSEO Site Explorer. У всех них есть возможность экспорта в Excel. Лично я использовал OSE. Альтернативным вариантом может быть использование API для получения данных, это зависит от вас.
От 6 до 8 часов вашего времени. Шучу. 
Шаг 1. Экспортируем данные об обратных ссылках.
Если вы уже использовали Excel для анализа обратных ссылок, то эта процедура вам знакома. Лично я на этом этапе я использовал мой Moz PRO аккаунт и OSE.
Поскольку я слишком ленив для поисков рандомного сайта, в качестве примера я возьму домен seomoz.org. Его основные конкуренты: distilled.net, seerinteractive.com и seogadget.co.uk.
Получить информацию об обратных ссылках по каждому сайту (включая seomoz) очень просто. Откройте OSE, введите домен и кликните «Поиск». Далее, вам нужно будет фильтровать результаты, чтобы они включали только внешние ссылки на страницы на корневом домене или поддомене (последнее нужно если сайт размещается на поддомене, который отделен от корневого домена, например, блог blogspot.com).
Примечание: для получение ссылок на корневой домен вам потребуется 2 дополнительные формулы, которые вы найдете в 3 пункте.
Вы можете продолжить фильтрацию для получения DoFollow ссылок. Далее вам нужно экспортировать эти ссылки. OSE предлагает два варианта. Либо использовать "Скачать CSV" и получить до 10k ссылок или использовать расширенный модуль отчетов, где у вас есть дневной лимит и вы может экспортировать до 100k ссылок.
В этом случае, вам нужно использовать опции "External linking page" и "Any page on this root domain". Все остальное можно оставить как есть, по желанию вы можете сделать фильтрацию ссылок с DA/PA для уменьшения результатов.
Когда обработка будет закончена, вы получите извещение по email.
Повторите все тоже самое для конкурентов. В результате у вас получится несколько CSV файлов (один с нашими обратными ссылками, остальные по конкурентам).
Шаг 2. Импортируем в Excel.
Самое время использовать Excel, о котором мы уже столько говорили. Для получения данных из файлов CSV, у нас есть два пути:
Открыть CSV файлы и скопировать интересующие нас столбцы , а затем вставить их в новый лист.
Либо использовать Excel Text Import для импорта данных на пустой лист не открывая CSV.
Оба варианта достаточны просты, хотя первый все-таки легче. Проблема в том, что первый вариант не будет работать, если ваша Windows была установлена в Европе. Это потому, что CSV содержит значения, разделенные запятой; запятая является разделителем списка по умолчанию в США. Для европейских стран, разделитель по умолчанию, как правило, точка с запятой. Это означает, что Excel не будет читать файлы CSV правильно.
Для решения этой проблемы вам нужно открыть «Язык и региональные стандарты» в панели управления Windows и либо установить его на английский (США), либо оставить страну по умолчанию, заменив при этом разделитель с запятой на точку в расширенных настройках. Более подробно об этом здесь.
Кроме того, вы всегда можете использовать второй вариант. Проблема в том, из-за способа, которым Excel импортирует данные, некоторые из них могут отображаться ошибочно, что может потребовать дополнительных мер по исправлению ошибок.
Независимо от вашего выбора, после включения данных с первого домена, скопируйте данные для других доменов, исключая при этом заголовок. Таким образом, у вас получится длинный список данных со всех доменов. Заголовок при этом будет только один (первая строка).
Примечание: если вы анализируете большое количество обратных ссылок (больше 50К), разбейте их на группы по (10-20К). Это ограничение зависит от вашей версии Excel.
Теперь, все ваши данные импортированы в Excel. Гораздо легче работать, если поместить их в таблицу. Для этого, выберите данные (выделите ячейку, например A1 и нажмите CTRL-A), а затем превратить их в таблицу (Нажмите Ctrl-L).
Помните, что без таблицы вам придется редактировать формулу для включения точных ссылок на ячейки (например, $ A2 вместо [@ URL]).
Шаг 3. Используем формулы.
Теперь, когда все наши данные помещены в Excel, мы можем применить формулы. С помощью первых 2 формул мы возьмем данные из колонок (source) URL и Target URL и отделим поддомены. Подробнее про применение этих формул написано в "Excel for SEO".
Нам потребуется создать два новых столбца для сохранения этих данных. Мы их назовем Source Subdomain"и "Target Subdomain". Так как у нас таблица, нужно всего лишь ввести имена в первых два столбца, а Excel будет добавлять их в таблицу автоматически.
Первая формула:
Source Subdomain
=MID([@URL],FIND("://",[@URL])+3,IFERROR(FIND("/",[@URL],9),LEN([@URL])+1)-(FIND("://", @URL])+3))
(где [URL] это столбец, содержащий исходный URL, может называться по-разному, если вы не используете OSE)
Target Subdomain
=MID([@[Target URL]],FIND("://",[@[Target URL]])+3,IFERROR(FIND("/",[@[Target URL]],9),LEN([@[Target URL]])+1)-(FIND("://",[@[Target URL]])+3))
(в основном то же самое, только для столбца Target URL).
Эти формулы позволяют получить то, что находится после "://" и перед "/" в URL. Часть IFERROR гарантирует, что вы получите правильный результат даже в случае, когда URL не заканчивается слешем, например как на домашней странице URL. (OSE всегда добавляет эту черту, а Majestic SEO этоне делает).
Вам нужно вставить эти формулы в первую клетку каждой колонки и Excel автоматически заполнит весь столбец.
Примечание: если вы выбрали опцию "pages to subdomain" вместо "to root domain" при сборе данных, вам не нужно использовать следующие две формулы, так как у вас только один поддомен для каждого сайта (например www.seomoz . для Moz, seogadget.co.uk для SEO Gadget....). В этом случае, переходите к Unique Domains формуле.
Как вы заметили, мы получили только поддомены целевого URL. Это означает, что и seomoz.org и www.seomoz.org будут засчитаны как разные сайты, что приведет к путанице (вы обнаружите домены, ссылающиеся на 4 или больше конкурентов, хотя вы анализируете только 3).
Чтобы исправить это, нам нужно отделить корневые домены от поддоменов. К сожалению, это будет не просто т.к. мы должны различать TLD (Top-Level Domains) и SLD (Second-Level Domains). Один из наших конкурентов - SLD (SEO Gadget) и если мы не хотим получить "co.uk" вместо "seogadget.co.uk" нам придется потрудиться.
Во-первых, мы должны составить список всех TLD и SLD урлов, где-то вне таблицы. Для этого я выбрал колонку Z. Наш список будет следующим:
.com
.net
.org
.co.uk
Всегда располагайте SLD под TLD (это принципиально). Например, если один из ваших конкурентов - блог размещенный на ".blogspot.com" вам следует разместить его под ".com" TLD для корректных расчетов.
Когда список готов, наша следующая формула извлечет TLD / SLD из колонки. Используем ее в соседнем с таблицей столбце. Назовите его "TLD". Формула следующая:
TLD
=LOOKUP(2^15,SEARCH($Z$1:$Z$4,[@[Target Subdomain]]),$Z$1:$Z$4)
Значение 2^15 в LOOKUP отвечает за поиск последнего вхождения TLD на целевой поддомен. Если вам интересно, в каком случае это было бы полезно, представьте себе поддомен "test.comparison.org". Вам хотелось бы получить часть ".org" т.к. это несомненно TLD. Однако, без 2^15 Excel дойдет до ".comparison" остановится и отнесет его к ".com", что будет ошибкой.
$Z$1:$Z$4 ссылки на диапазоны ячеек, содержащих TLD.
Теперь когда у нас есть TLD, можно получить корневой домен. Для этого мы берем Target Subdomain, отсекаем TLD, копируем все после последней точки, а затем снова склеиваем. Например, если у нас есть "some.thing.example.com", мы отсекаем TLD и получаем "some.thing.example", нас интересуют данные после последней точки. Это "example", затем вновь применим TLD и получим корневой домен "example.com".
Все вышеперечисленное делается в одной формуле, которую вы разместите в следующей колонке, названной "Target Root Domain":
Target Root Domain
=IFERROR(RIGHT([@[Target Subdomain]],LEN([@[Target Subdomain]])-FIND("|",SUBSTITUTE(LEFT([@[Target Subdomain]],LEN([@[Target Subdomain]])-LEN([@TLD])),".","|",LEN(LEFT([@[Target Subdomain]],LEN([@[Target Subdomain]])-LEN([@TLD])))-LEN(SUBSTITUTE(LEFT([@[Target Subdomain]],LEN([@[Target Subdomain]])-LEN([@TLD])),".",""))))),[@[Target Subdomain]])
Часть IFERROR в начале формулы нужна если Target Subdomain одновременно является Root Domain.
Теперь у нас есть корневые домены!
Следующая формула проверяет ссылается ли исходный домен на целевой хотя бы раз, что даст вам возможность увидеть, обратные ссылки ваших конкурентов.
В действительности, эти данные можно получить с помощью 3 разных формул. Две из них более простые, но очень велика вероятность получить предупреждение Excel об ошибке (по крайней мере так было в моей версии 2010) при анализе большого количества ссылок (30К+). Поэтому я решил использовать третью формулу, которая более корректно обрабатывает большие объемы данных.
Создайте новый столбец и назовите его "Unique Domains". Добавьте формулу:
Unique Domains
=IF(COUNTIFS(INDIRECT(ADDRESS(ROW(Table2[#Headers])+1,COLUMN([Source Subdomain]))&":"&ADDRESS(ROW([@[Source Subdomain]]),COLUMN([Source Subdomain]))), [@[Source Subdomain]],INDIRECT(ADDRESS(ROW(Table2[#Headers])+1,COLUMN([Target Root Domain]))&":"&ADDRESS(ROW([@[Target Root Domain]]),COLUMN([Target Root Domain]))), [@[Target Root Domain]])=1,1,0)
Функция COUNTIFS подсчитывает число связей между исследуемым доменом и доменами донорами.
Эта длинная формула на самом деле эквивалентна:
=IF(COUNTIFS($O$2:$O2,$O2,$R$2:$R2,$R2)=1,1,0)
В этом случае колонна O содержит первичные поддомены, а столбец R целевые корневые домены. Два - это номер строки, где вы впервые вводим формулу (строка прямо под заголовком).
К сожалению, функция COUNTIFS не работает в Excel 2003, поэтому вам потребуется другая формула, которую вы найдете здесь (версия SUMPRODUCT).
Последняя формула достаточно проста. Она проверяет, стоит ли ссылка на наш основной домен или нет. Создайте столбец "Link To Us” и введите следующую формулу ("seomoz.org" нужно заменить на название вашего домена):
Link To Us
=IF([@[Target Root Domain]]="seomoz.org",1,0)
Формула выдает 1 если это наш основной корневой домен или 0 если это конкурент.
Помните, если вы не использовали TLD/Root Domain, тогда вам нужно использовать Target Subdomain вместо Target Root Domain (в этом случае, у вас будет [@[Target Subdomain]]="www.seomoz.org").
Шаг 4. Создаем сводную таблицу.
Теперь, когда у нас есть таблица с обратными ссылками, сделать все остальное очень просто. Создайте новый лист, озаглавьте его "Pivot Table". Именно здесь данные будут наглядно представлены для последующего анализа.
Сам процесс будет выглядеть следующим образом:
Выберите вкладку Insert -> нажмите кнопку Pivot Table -> введите имя нашей таблицы, содержащей данные об обратных ссылках (обычно Table 1, если вы ее не переименовали) -> нажмите OK.
Теперь у вас есть пустая сводная таблица с сайдбаром Field List. Вот как можно ее настроить:
1. Перетащите Source Subdomain, Target Root Domain (или Target Subdomain соответственно) и URL fields в поле Row Labels (в этом порядке).
2. Перетащите Unique Domains, Link To Us и другие необходимые вам показатели (например, авторитетность домена) в поле Values (порядок не имеет значения). Все поля должны заполняться автоматически в виде суммы ("Sum of..."). Для получения усредненных значений повторите следующие действия: кликните на "Sum of Domain Authority (или любой другой показатель)" -> затем на Value Field Settings -> выберите Average вместо Sum, под "Summarize value field by" -> нажмите OK.
Вы должны получить примерно следующее (возможны незначительные вариации в зависимости от ваших обратных ссылок и выбранных параметров).
Полученные данные оказались весьма объемными и их нужно свернуть. Чтобы сделать это, выберите один из основных поддоменов в сводной таблице, -> убедитесь, что вы находитесь во вкладке Options -> нажмите кнопку Collapse Entire Field.
Вы можете повторить эти действия и для целевого корневого домена.
Шаг 5. Сортируем.
Наша основная задача на данном этапе — отсортировать полученные данные. Кликаем на выпадающее меню Row Labels -> Value Filters -> Equals... -> в новом окне выбираем "Sum of Link To Us" и вводим "0" в поле значений -> нажимаем OK.
Вы вероятно, хотите как можно скорее увидеть сайты, которые ссылаются на ваших конкурентов. Для этого:
Снова кликните Row Labels -> More Sort Options -> выберите Descending (Z to A) by -> выберите Sum of Unique Domains -> нажмите OK.
Вы получили сводную таблицу с доменами, ссылающимися на ваших конкурентов, но еще не отсортированные по количеству ссылок. Вы можете отсортировать их по авторитетности домена, но я нашел способ сортировать сразу по нескольким показателям.
Финальная версия будет выглядеть так:
Примечание: перед сохранением файла, для уменьшения его объема и времени, необходимого для его открытия можно заменить формулы, чтобы Excel заново не пересчитывал их. Для этого выделить все (CTRL-A) -> копировать (CTRL-C) -> вставить только значения (Paste Values).
Шаблон и инструкция.
Как и обещал, я прикладываю шаблон, содержащий все необходимые формулы и сводную таблицу, куда вам нужно только добавить данные об обратных ссылках.
Я создал 2 отдельных файла:
vertify-link-intersect-sample-SUBDOMAIN.xlsx – использовать, когда у вас есть данные об обратных ссылках с одного поддомена для каждого отдельного сайта (как вашего, так и конкурентов) и обратные ссылки ведут на страницы поддомена (в данном случае , domain.com и www.domain.com считаются разными сайтами/поддоменами, так что вам придется выбирать, какие данные вы хотите получить).
vertify-link-intersect-sample-ROOTDOMAIN.xlsx – использовать, когда у вас есть данные по обратным ссылкам на страницы корневого домена, по крайней мере для одного сайта. В этом случае появятся 2 дополнительных столбца для извлечения корневого домена. Это означает, вам придется настроить список TLD в листе конфигурации.
Оригинал статьи на английском языке: http://moz.com/ugc/competitive-link-analysis-link-intersect-in-excel |
|
|
|
|
