1. Итак, что мы можем сделать с vlookup в логике SEO?
2. Но все же?
3. Тот, кто не перекрывает, делает SEO слепым
4. В начале, есть сканирование
5. Учебник шаг за шагом
6. Получение данных из Google Analytics
7. Восстановление URL-адресов
8. И вот тут vlookup (речерчев) вступает в игру ...
9. А сейчас?

В SEO мы часто говорим об основах. Функция Excel vlookup (чей французский эквивалент - recherchev ), что означает «вертикальный поиск», должна быть его частью, по крайней мере, когда речь идет об инструментах. И все же эта функция плохо приручена в нашем бизнесе, а ее потенциал просто огромен .

Итак, что мы можем сделать с vlookup в логике SEO?

Я делаю это коротко, благодаря vlookup вы можете измерить для вашего сайта:

• Какие самые мощные разделы,
• какие страницы неактивны (не посещаются),
• влияние дублирования контента на SEO,
• возможная корреляция между социальными голосами и органическим трафиком, генерируемым вашими страницами,
• что такое потерянные URL-адреса, сравнивая URL-адреса GA, crawl и sitemap.xml,
• какие страницы избегаются роботом Google,
• в какой степени плотность контента может влиять на ваших посетителей и движки,
• ... список бесконечен!

Но все же?

Поэтому Vlookup (recherchev) - это функция, встроенная в электронные таблицы (Excel) . Он также может работать с другими, такими как открытый офис, электронная таблица Google и т. Д., Но будьте осторожны, название функции может измениться. Тем не менее принцип остается прежним. Идея состоит в том, чтобы сгруппировать в одной таблице данные из нескольких разных таблиц, но имеющие общие данные (часто URL-адреса).
Это все еще немного нечетко? Вы поймете лучше позже ...

Тот, кто не перекрывает, делает SEO слепым

Сбор данных - это хорошо, это даже основа. Но, беседуя с некоторыми из моих коллег, даже со старшими, я удивляюсь, что перекрестные ссылки не являются частью их методологии. Hargh! Результат, работа выполняется без «перспективы»: она анализирует элементы «на странице», сетевые ссылки, задержки, контент ... все разбито на части, без обзора и без реального измерения воздействия, которое каждый элемент может иметь на других и органический трафик в частности.

В начале, есть сканирование

Некоторые инструменты, которые я опишу как «многомерные», изначально позволяют осуществлять перекрестную проверку путем объединения нескольких данных из разных инструментов. Я думаю, например, чтобы DeepCrawl или Botify , Их общая точка? Они оба сканеры (во фронтальной конкуренции), которые предлагают возможность пересекать данные, собранные с помощью аналитики Google.

Сканер должен напомнить мне, также является незаменимым инструментом для любого анализа и аудита. Именно он будет сканировать весь сайт и восстанавливать конфиденциальные данные SEO: URL, заголовок, мета, количество слов, вес, уровень, H1, количество исходящих ссылок, входящих и т. Д. Есть некоторые платежи, как два упомянутых выше, но также бесплатно, как Дикая Акула SEO Spider , Чтобы проиллюстрировать мою статью, я буду опираться на ту, которую я чаще всего использую в своих аудитах: Кричащая лягушка SEO Spider , Последние два, в отличие от Botify и Deepcrawl, не дают никакого совпадения с аналитикой Google ... Неважно, именно это мы и собираемся сделать!

Учебник шаг за шагом

Этот учебник будет посвящен перекрестной проверке URL-адресов, собранных через сканер, и аналитике Google. Когда вы понимаете хитрость, ограничить вас может только ваше воображение ;-) Требуются действительно базовые знания Excel (или другой таблицы) (я не буду объяснять, как удалить столбец или строку).

Первым шагом является сканирование сайта, чтобы получить все URL. Этот шаг является основополагающим, потому что только сканер может составить исчерпывающий список. Поэтому мы запускаем сканирование на сайте, а затем экспортируем (.csv / .xlsx) все URL-адреса, соответствующие HTML-страницам.

Экспорт URL (HTML) в Screaming Frog SEO Spider

Мы открываем экспортированный файл в Excel, где находим примерно ту же таблицу:

Затем удалите первые 2 строки, затем примените «табуляцию». Главное, чтобы была возможность фильтровать / сортировать столбцы.

Там. Теперь у нас есть файл Excel, готовый к работе и хорошо представленный.

Получение данных из Google Analytics

Теперь мы восстановим посещения, созданные каждым URL в Google Analytics. Но будьте осторожны, не просто посещая! Нас интересуют визиты из органического исследования (естественные результаты двигателей), а точнее записи . Я настаиваю, потому что это очень важно: чтобы измерить производительность SEO страницы, мы должны сначала посмотреть на трафик, который она истощает со страниц результатов движка (SERP). Представьте, что посетитель (обычный) приходит на страницу A, а затем продолжает навигацию по странице B: мы запомним его точку входа на страницу A, другими словами, целевую страницу.

Кроме того, посещения будут проводиться в течение 30 дней до сканирования . Конечно, мы можем сделать больше, но с точки зрения SEO это было бы не очень интересно.

На этом этапе важно понимать, что Google Analytics знает только те страницы (URL), которые посетители показывали за выбранный период. GA отнюдь не гусеничный! Поэтому, если вы хотите получать удовольствие от сгенерированных посещений по сравнению с общим количеством URL-адресов, перекрестная проверка данных и инструментов становится обязательной.

Пошли! В Google Analytics перейдите в раздел «Поведение»> «Содержимое сайта»> «Все страницы» и убедитесь, что вы находитесь в течение 30 дней.

Некоторые люди задаются вопросом, почему я не буду искать сеансы целевой страницы. Ответ прост: он точно такой же, за исключением того, что со «всеми страницами» есть все известные URL-адреса GA, даже те, которые не генерировали посещений. Это позволяет позже обнаружить возможные страницы-сироты.

Теперь необходимо отфильтровать сгенерированный трафик, чтобы учесть только тот, который генерируется естественными результатами. По умолчанию у вас обязательно будет включен сегмент «все сеансы»:

Нажмите на него, затем снимите флажок «все сеансы», затем отметьте «Трафик, сгенерированный естественными результатами», затем подтвердите.

Перед выполнением экспорта необходимо отобразить наибольшее количество результатов, поскольку GA позволяет экспортировать только текущую страницу. В нижней части таблицы «Показать строки» мы выбираем 5000. Кто может сделать больше всего, тот может сделать меньше всего. В большинстве случаев количество URL-адресов не должно превышать 5000. Если это так, то необходимо выполнить операцию в 2 этапа, см. Подробнее.

Теперь, когда все в порядке и нет больше нумерации страниц, мы можем экспортировать данные, благодаря меню «Экспорт» в верхней части страницы.

Затем откройте файл в Excel. Только лист «Dataset1» представляет для нас интерес. Но если мы посмотрим на столбец «Страница» (A), то заметим, что URL-адреса являются ампутированными доменными именами. Мы должны это исправить.

Восстановление URL-адресов

Техника проста и быстра, если вы ее знаете.
Вставьте 2 новых столбца, желательно перед «Страница». В новом столбце A мы добавляем в A2 «http://www.domain-domain.com», чтобы заменить, конечно, на имя вашего домена. Важно скопировать URL-адреса на адреса для сканирования . Если на одной стороне есть "www", они должны присутствовать на другой. Не ставьте косую черту в конце! Затем мы «расширяем» значение на весь столбец до конца таблицы:

В столбце B мы теперь добавим небольшую формулу, чтобы сгруппировать значения столбцов A и C. Перейдите к B2, затем добавьте следующую формулу: = A2 & C2. Проверьте и распространите формулу на весь столбец.

Выберите столбец B (тот, который содержит полные URL-адреса), создайте «копию» и вставьте его, вставив только значения (вместо формул).

Если вам удалось вставить только значения (полные URL-адреса) в столбце B, теперь вы можете удалить столбцы A и C. Затем назовите новый столбец A с помощью URL-адресов (или как хотите), затем сохраните свой файл. с довольно коротким именем (например, GA.xlsx).

И вот тут vlookup (речерчев) вступает в игру ...

Преамбула: используя Excel на французском языке, теперь я буду говорить о «recherchev» вместо «vlookup», названия функций различаются в зависимости от языков интерфейса (я считаю, что это немного ноль, но хорошо ...).

На данный момент у нас есть 2 открытых файла Excel: сканирование и аналитика Google.
В файле сканирования мы теперь получим количество органических записей (если они есть) для каждого URL.

Вставьте новый столбец, например, между «Адрес» и «Содержимое», затем назовите его «Записи GA». Встаньте на первую клетку (B2). Внимание, будьте внимательны, теперь все играется!

В B2 начните с написания следующей формулы: = searchv ( не забывая открывать круглые скобки в конце, не проверяйте ничего в этой точке, затем нажмите «Fx», чтобы отобразить окно аргументов функции:

В «search_value» мы указываем, что мы ищем URL-адреса, потому что они совпадают в наших двух файлах. Удерживая диалог открытым и нажимая на первый URL (A2), нужно получить это:

Будьте осторожны в том, что вы делаете с помощью мыши, щелчок в любом месте может сломать все! Если случайно вы получили несоответствующие результаты или не соответствуют скриншотам, сделайте «Отмена» и начните снова.

Затем перейдите к «Matrix_Table» (это важно). Затем отобразите (Alt + вкладка или панель задач) файл, содержащий данные аналитики Google (GA.xlsx). Выберите диапазон (нажатие) столбцов, идущих от A до E, и не отпускайте кнопку мыши! Обратите внимание на номер столбца, который появляется во всплывающей подсказке при выборе столбцов: вы должны запомнить номер столбца, из которого вы хотите извлечь данные. В данном случае, вот интересующий нас столбец «Входы», который соответствует столбцу 5, если наш диапазон начинается с 1-го столбца. Vu?

Теперь нажмите «No_index_col», чтобы автоматически отобразить таблицу сканирования в фоновом режиме. Затем укажите номер интересующей нас колонки: 5 .

Наконец, для value_proche всегда указывайте «false» .

Нажмите Ok

И это все! Вы только что связали данные из одной таблицы в другую! На первый взгляд, вы теперь видите, какие страницы генерируют органический трафик, особенно те, которые не истощают (активные и неактивные страницы) ... И это, SEO это фон-да-мужчины тал!

Это еще не закончено: поскольку файл сканирования теперь зависит от файла GA.xlsx для отображения количества записей, предпочтительно всегда копировать / вставлять значения в себя, как обсуждалось выше.

1: чтобы быстро выделить все ячейки (не пустые) столбца, перейдите к 1-й ячейке, содержащей значение, затем нажмите Ctrl + Пробел.
2: Вы можете заменить значения # N / A ничем, чтобы получить более чистый (необязательный) массив.
3: для большей точности и для получения надежной статистики (я собираюсь здесь), отфильтруйте столбец D «Код состояния», чтобы отобразить только 200 кодов.

А сейчас?

И теперь возможности безграничны. В той же таблице вы сможете сгруппировать данные из различных источников для 360-градусного анализа вашего сайта. В дополнение к статистике Google Analytics вы можете связать количество обращений, записанных GoogleBot в течение определенного периода, я объясняю в статье как восстановить (и визуализировать с помощью Gephi) журналы доступа Apache , Аналогично, после сканирования вашего сайта с помощью службы вроде siteliner.com, вы получите ответ на очень точные, но, тем не менее, очень интересные вопросы:

• Как дублирование внутреннего контента может повлиять на частоту сканирования? А в гостях?
• Насколько глубина URL-адресов влияет на аппетит и видимость GoogleBot в поисковой выдаче?
• Влияет ли размер статей с содержанием дубликата менее 30% на объем посещений? ...

И представьте, если вы соберете социальные голоса для каждого URL, например, благодаря SeoTools для Excel, а также количеству входящих ссылок, идентифицированных в Majestic или Ahrefs ... Это ваш карбид воображения на полной скорости? Тем лучше :)

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

Короче говоря, вы видите, что это может принести вам, и если вы все еще не видите, вашим последним средством будет научить лабрадора работать SEO.

На SEOcamp в Женеве 22 мая я также расскажу, как мы можем использовать все эти данные, как составить критическую статистику в профилактическом или лечебном процессе с помощью фильтра Google panda.

PS: два года назад Excel был для меня программным обеспечением для секретарей на шпильках ...

Похожие

Комментарии