Мы перевели для вас статью Нила Пателя. Читайте и применяйте.
Для многих маркетологов Excel – жизненно необходимая программа, их хлеб и соль, так сказать. Неважно, для чего она используется – для анализа поисковых данных или подсчета данных по продажам – в какой-то момент вы все равно откроете документ Excel.
Фишка Excel (или любого другого ПО с таблицами) в огромном количестве предоставляемых функций. Это означает, что при попытке ознакомиться с ними, шансы попасть в тупик велики. А уж если у вас есть необходимость использовать Excel ежедневно, вам точно не захочется делать все вручную, ведь это огромная потеря времени.
Большинство маркетологов-профессионалов знают нужные функции как свои пять пальцев, но все как один скажут вам, что это всего лишь вопрос времени.
Однажды овладев этими навыками, вам больше не придется возвращаться к этому вновь и вновь.
Зачем учиться пользоваться Excel для SEO
Если вы до сих пор не пользуетесь никаким ПО с таблицами, то пора бы начать. Упорядочивание метрики – одна из самых важных частей разработки маркетинговой стратегии, будь то план, еженедельный SEO отчет или годовой доклад.
Таблицы помогают организовывать все данные в одном месте,где они всегда под рукой. А когда речь идет о чем-то вроде SEO, дисциплина и организация – это основа вашего успеха, особенно если у вас сотни или даже тысячи ключевых слов.
Причина, по которой Excel является такой потрясающей программой для SEO, – это наличие расширений и плагинов, созданных специально для того, чтобы сделать процесс пользования максимально простым.
Например, SEOTools для Excel идет с уже встроенными функциями специально для маркетологов.
Такие утилиты - просто находка и необходимость для тех, кому приходится пользоваться Excel без особой любви к ней.
Но кроме специальных утилит, Excel имеет множество формул, помогающих с курированием ключевых слов, сегментацией списков и анализом данных. А ведь все это неотъемлемые части процесса поисковой оптимизации.
Но множество людей по-прежнему сторонятся Excel, потому как не понимают, как же использовать эти формулы. Мало кому хочется учиться и запоминать, как использовать специальные формулы.
Но все не обязательно должно быть именно так. Excel не обязан быть величайшей тайной, разгадки к которой нет. На самом деле, вы даже можете получать удовольствие от процесса работы с этой программой, когда разберетесь, что к чему.
7 способов применения встроенных функций Excel, которые непременно выведут вас на новый уровень пользования поисковой оптимизацией
1. Используйте формулу «=IF» для разбивки ключевых слов по категориям
Находясь в поиске ключевых слов, учишься спокойно относиться к длинным спискам. Привыкаешь к ним, так сказать. Даже пользуясь простой утилитой типа Google’s Keyword Planner, все равно получишь длинный список.
Другие утилиты вроде Ahrefs или Moz могут выдать вам списки с тысячами ключевых слов, показателей объема и данными о конкуренции. А это очень, очень много данных. Поместив эти данные в таблицу, мы сможете организовать их по рядам и колонкам. Но ведь нужно еще и как-то осмыслить все это.
Надо найти способ сегментировать эти ключевые слова в удобную для использования информацию.
Например, если использовать более одного семантического ядра в Keyword Planner,
то вам выдают колонку «Ad Group», куда можно экспортировать список запросов, которые отделят семантические ядра от остальных вариантов ключевых слов.
Предположим, я хочу разбить этот список только на ключевые запросы со словами «утилиты» в них. Если делать это вручную, придется продираться сквозь более чем 700 рядов. А мне не очень-то хочется это делать. Так что вместо этого я собираюсь использовать формулу Excel =IF (=ЕСЛИ), которая сделает все за меня.
Первым делом нужно удалить колонку «Ad Group», потому что я хочу искать среди всех запросов.
А потом я добавлю колонку «Category» (“Категория”) рядом с поисковым объёмом.
В первой свободной клетке я пишу слово «Tools» и затем использую формулу:
=if(isnumber(search(“tools,A12)),”Tools”)Когда ключевое слово «tools» найдется в первой колонке (мой список ключевых слов), то поместится в категорию «Tools».
Можно пользоваться этой формулой в таблицах, чтобы поместить все ключевые слова в определенные категории. Это переломный момент в игре под названием “организация поискового процесса”.
Если хотите только ключевые запросы со словом «local», можно создать категорию «Local». Удивительно нужная штука для организации всех ваших ключевых запросов для быстрого поиска.
Таким образом, если вы работаете с командой людей, каждому из которых нужен доступ к одному и тому же файлу Excel, но для разных целей, - никакие из ваших данных не перепутаются, а люди все равно найдут то, что им нужно.
У Moz есть хороший пример того, как можно сделать это для всех данных во всех таблицах.
Чтобы сделать это для всех рядов и колонок, используйте мульти-клеточную формулу ARRAY (МАССИВ).
Это освободит вас от необходимости вставлять одну и ту же сложную формулу в каждую клетку таблицы. И даже если у вас не 700+ клеток, с которыми нужно иметь дело, формула все равно пригодится.
2. Создавайте сводные таблицы для обнаружения выброса данных
Анализ данных в таблицах может быть довольно хаотичным, если данные не организованы.
Даже если вы фильтруете по категориям, не факт, что «неверные данные» и прочие лазутчики обнаружатся при одном взгляде на таблицу. Хорошие новости – у Excel есть легкий способ выявить положительные и отрицательные тренды в данных при помощи сводных таблиц.
Чтобы создать такую, выделите клетку таблицы и нажмите Insert > Pivot Table (Вставить > Сводная Таблица).
Затем вы увидите диалоговое окно, в котором вас попросят выбрать данные для анализа и куда поместить сводную таблицу.
Ваша таблица будет автоматически помещена в выделенную клетку, но также можно создать новую таблицу или поместить ее куда-то еще.
Когда нажмете «OK», то увидите поле, соответствующее вашим колонкам.
Можно перетаскивать поля, куда вам нужно, отфильтровать их, добавлять колонки и т.д. Полная кастомизация, и можно также добавлять или удалять элементы по необходимости.
Используйте эти таблицы для таких вещей как организация ссылок или URL, группировка внутренних ссылок по DA/PA, поиск ключевых слов в домене или создание всплывающих окон для определенных колонок.
Вот пример функциональной сводной таблицы:
Такая таблица не просто организует ваши данные в более читабельном виде, но и дает несколько опций для категоризации. В примере выше можно сразу заметить большие или маленькие цифровые значения CPA («cost-per-action», цена-за-действие) для определенных ключевых слов.
Это делается при помощи условного форматирования в рамках сводной таблицы (вы можете заметить цветное кодирование). Сводные таблицы типа этой могут оказаться полезными, если вы используете ключевые слова для транслирования своей стратегии Adwords, или если хотите быстро обнаружить высоко- и низкочастотные запросы и CPC («cost-per-click», цена-за-клик) для конкретных ключевых слов.
Вам не придется скроллить вверх и вниз по списку с сотней ключевых слов, чтобы обнаружить засланных казачков.
И выглядит такая таблица тоже неплохо.
3. Конвертируйте показатели объема при помощи SUBSTITUTE (ЗАМЕНИТЬ)
Существует множество разных способов сортировки данных в Excel. Тем не менее, ключ к продуктивности заключается в том, чтобы найти верный. Когда вы загружаете данные из утилиты для ключевых слов (типа Keyword Planner), CSV-файлы не всегда выглядят хорошо. Например, мои объемы ключевых слов выглядят так:
Справедливости ради, они выглядят так же при использовании Keyword Planner.
Но когда я пытаюсь провести поиск по этим объемам или организовать их от большего к меньшему, такой формат проблематичен.
Excel не может отсортировать мой список, основываясь на понятиях типа «10К» или «1М». Он не дружит с К и М. Ему нужны реальные цифры. К счастью, Excel имеет нечто вроде быстрой формулы, которая поможет вам отсортировать объемы должным образом.
Во-первых, замените К и М и преобразуйте их в «000» или «000000». Создайте новую колонку с названием «От меньшего к большему» или типа того.
Выберите клетку в новой колонке и вставьте =SUBSTITUTE (=ЗАМЕНИТЬ):
Ваша формула должна выглядеть примерно так:
=SUBSTITUTE(C2,”K”,”000”)Номер клетки изменится согласно ряду, который вы конвертируете.
Вот как это выглядит после вставки формулы:
И окончательные результаты:
Можно также заменить М на «000000» при помощи той же формулы. Выглядеть будет вот так:
=SUBSTITUTE(C2,”M”,”000000”)Так вы разберетесь со всеми К и М в таблице.
А еще можно выполнить оба действия одновременно (если у вас диапазон от 100К до 1М, например), используя следующую формулу (изменяя номер клетки):
=SUBSTITUTE(SUBSTITUTE(C2,”K”,”000”), “M”, “000000”)Есть еще несколько формул, чтобы найти минимум, максимум и среднее значение поисковых объемов в новой колонке.
Максимума:
И среднего показателя:
Помните, что вам, возможно, придется добавить дополнительные колонки, чтобы использовать эти формулы, так что удостоверьтесь, что озаглавили их должным образом.
Вы сможете быстро переключаться между колонками «от большего к меньшему» и «от меньшего к большему» без необходимости долго сортировать или переставлять вручную.
Эти формулы призваны экономить ваше время и энергию, в конце концов.
4. Извлекайте определенные данные при помощи «REGEXTRACT» (“ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ ТАБЛИЦЫ”)
Конечно, ключевые слова – это не только данные в таблицах SEO.
Иногда вам придется сортировать URL, названия доменов, заголовки блогов или email адреса.
Возможно, вам потребуется найти конкретные посты в блоге или лэндинговые страницы с HTTP versus или HTTPS.
Всегда можно использовать панель поиска Excel и найти их по отдельности, но если у вас длинный список URL, это займет время.
Вместо этого, примените REGEXTRACT (ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ), чтобы извлечь конкретные данные из списка.
REGEXTRACT не похожа на другие Excel формулы. Она требует специального синтакса (строка кода) для работы.
Полная формула с синтаксом для REGEXTRACT выглядит так:
=REGEXEXTRACT(A2,”^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)”)Смешной кусочек в конце и есть синтакс.
Можно копировать и вставить полную формулу в новую колонку (изменяя номер колонки), и в результате вы получите вот что:
Можно воспользоваться формулой ARRAY (МАССИВ), чтобы добавить это в несколько рядов сразу.
Все вместе выглядит так:
=IFERROR(ARRAYFORMULA(REGEXEXTRACT(A2:A,”^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)”)),“”)Громоздко, но работает.
Вот что получаем в нашем примере:
И в конечном итоге:
Да, разобраться с этим и применить в жизнь потребует немного времени, но впоследствии сэкономит вам бесчисленные часы, особенно если у вас очень много данных для сортировки.
Если вы все еще не уверены в процессе или в том, как использовать синтаксис вместе с REGEXTRACT, у Ahrefs есть таблица, которая показывает, как работает эта формула.
Это просто палочка-выручалочка для тех, кто не испытывает особой нежности к Excel формулам.
5. Форматируйте теги названий с помощью формулы «PROPER» (“ПРОПНАЧ”)
Еще одно утомительное занятие, с которым вы можете столкнуться при работе с таблицами для SEO, это переформатирование данных.
Возможно, теги заголовков или ключевые слова были импортированы в нижнем регистре, а вам надо, чтобы они были записаны заглавными буквами.
Вам не придется разбираться со всеми рядами и колонками, переписывая текст заглавными буквами.
Или, по крайней мере, я надеюсь, что вы не собираетесь это делать, потому что можно легко сменить строчные буквы на заглавные одним скопом, используя формулу PROPER, которая выглядит вот так:
=PROPER(C2)Просто, не так ли?
Создайте новую колонку и вставьте формулу туда. Вот так:
И конечный результат:
Если у вас много тегов, которые нужно записать заглавными буквами, это тоже сработает.
Можно искать отдельные теги, используя формулу HLOOKUP (ГПР) (вернемся к ней позже).
Использовать формулу PROPER для переформатирования названий можно даже в том случае, если буквы нижнего и верхнего регистров перемешаны в словах.
Если хотите изменить ключевое слово или тэн названия в нижнем регистре на верхний (например, акроним), воспользуйтесь формулой UPPER (ПРОПИСН). Вот так:
Вы можете мне не верить, но формула LOWER (СТРОЧН) совершит обратное действие :)
Несмотря на то, что формула очень проста, я включил ее в список потому, что много людей забывают о ней.
А маркетологи не всегда имеют в запасе достаточно времени, чтобы пройтись по всем ключевым словам, тегам названий или других частям текста с целью изменения букв на заглавные.
Так что вот вам быстрый и легкий способ решить этот вопрос.
6. Совершайте поиск в больших таблицах с помощью «VLOOKUP» (“ВПР”)
Некоторые маркетологи любят сегментировать данные по разным таблицам. Одна для исследования ключевых слов, одна для мейлов, одна для доменов и т.д. Но иногда вся эта информация находится в одном месте.
Классно иметь одно большое хранилище данных по SEO, но процесс сортировки и поиска нужной информации в разных колонках, рядах или чартах может быть очень раздражающим.
Как я уже упоминал ранее, поисковые функции Excel ограничены, если ваши данные уже не организованы каким-то образом.
И тем тяжелее, чем больше у вас рядов и колонок с данными. Есть у Excel формула и для этого, куда ж без нее.
Это VLOOKUP (ВПР), которая выглядит так:
=VLOOKUP(A4;’Lookup Table’!A2:D1000;4;FALSE)Если вы хотите найти специальное ключевое слово среди двух листов одной таблицы, то процесс таков:
Эта формула может показаться сложной на первый взгляд, но становится проще, когда вы ее освоите.
Первая секция – это название того, что вы ищете. Поставьте его в кавычки:
Затем добавьте наименование колонки.
После этого добавьте порядковый номер колонки. Первая колонка – под цифрой 1 и так далее.
Так как я пытаюсь найти ключевые слова в первой колонке, то напишу цифру 1, а за ней добавлю слово TRUE, так как мне нужно точное совпадение.
Можно также использовать FALSE, если точное совпадение необязательно.
Обратите внимание на то, что VLOOKUP (ВПР) работает только если необходимые вам данные находятся в первой колонке (крайняя левая), а данные расположены в алфавитном порядке.
Так что вам возможно придётся форматировать колонки перед поиском VLOOKUP (ВПР). Но формула все равно очень пригодится.
Ее также можно использовать для поиска цены продукта или специальной категории, как, например, здесь:
Если нужно искать по рядам, используйте формулу HLOOKUP (ГПР).
V (В) для вертикального поиска и H (Г) для горизонтального.
Пример формулы HLOOKUP:
=HLOOKUP(D3;’Lookup Table’!A1:D10;2;FALSE)Тут вы найдете пример этой функции:
Оба пути быстро приведут вас к тому, что вы ищете, особенно если найти нужно среди множества рядов, колонок или страниц в документе.
Также обе эти формулы можно использовать между двумя разными документами и искать в сворачивающихся окошках и сводных таблицах.
Есть множество видео, в которых подробно показывается, как использовать обе формулы для поиска и подсчета различных данных между страницами.
Немного тренировки, и вы освоите их на «отлично». Именно поэтому они во многом превосходят традиционное поисковое окно.
7. Быстро обнаруживайте дубликаты, используя «COUNTIF»(“СЧЁТЕСЛИ”)
Не все данные в таблице будут верными.
Вам понадобится быстрый и легкий способ отсортировать их, выявить реплики и заняться своими делами.
Формула Excel в данном случае – COUNTIF. И выглядит она так:
=COUNTIF(A:A,A2)Допустим, у вас есть список элементов в первой колонке (колонка А), которые вы хотите проверить на дубликаты.
Во-первых, создайте новую колонку и вставьте формулу с номером клетки из колонки А, вот так:
Затем перетащите ее и скопируйте в другие клетки. После этого увидите вот что:
TRUE означает, что совпадения есть, а FALSE – что стопроцентных дубликатов нет. Эта формула находит только точные совпадения.
В примере выше вы видите, что даже если слова «seo strategy» показаны несколько раз (например, seo marketing strategy), то формула не сочтет это дубликатом.
Если вы просто хотите выделить эти дубликаты, можно сделать это при помощи условного форматирования.
Нажмите Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values
Можно выбрать цвет для дубликатов, если хотите просто выделить их, а не удалить.
Если хотите их удалить, нажмите Data > Remove Duplicates (Данные > Удалить Дубликаты), а затем под колонками выделите или снимите выделение с тех, в которых хотите удалить дубликаты.
Весь процесс занимает пару минут, но при этом действительно помогает почистить данные быстро.
Некоторые из этих формул потребует времени, чтобы их освоить, но Excel предлагает множество кнопок и быстрых формул для помощи.
И формула COUNTIF (СЧЁТЕСЛИ) – одна из них.
Заключение
Многие люди не любят пользоваться таблицами или разбираться с формулами. Я это понимаю. Некоторые формулы слишком сложны и громоздки, чтобы их запомнить (вспомним REGEXTRACT).
Но если вы используете таблицы – особенно Excel таблицы – для SEO или маркетингового исследования, то вам просто необходимо овладеть некоторыми из них.
В особенности, присмотритесь к тем, которые помогают при поиске или очистке данных и делают это быстро, как VLOOKUP (ВПР) или COUNTIF (СЧЁТТЕСЛИ).
Умение конвертировать показатели объема с помощью SUBSTITUTE (ЗАМЕНИТЬ) просто жизненно необходимо для поиска ключевых слов.
А научившись создавать более сложные (но нужные) элементы типа сводных таблиц, вы в итоге сэкономите себе кучу времени и энергии. SEO и так непростая штука.
Сбросьте с себя часть бремени, изучив несколько приемов пользования Excel.
Если вы хотите научиться оптимизировать сайты и стать суперменом-сеошником, то можем предложить курс «SEO-оптимизация: продвижение сайтов в поисковых системах». После курсов проведете аудит сайта и создадите стратегию продвижения. Научитесь анализировать конкурентов, сформируете семантическое ядро. Прогнозируя результаты продвижения, сможете оптимизировать бюджет. Привлекательно? Записывайтесь!
Авторизуйтесь, чтобы оставлять комментарии