Microsoft Excel для SEO специалистов

Каждый SEO специалист в процессе своей работы взаимодействует с различными инструментами, сервисами, плагинами, модулями и прочим. В данный статье мы опишем самый главный инструмент SEO специалиста — Microsoft Excel и его важные функции.

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

Урок 1:

1.1. Текст по столбцам:

В процессе работы над проектом приходится разбивать текст по столбцам который содержится в ячейке, например, если необходимо вывесть сайт из под фильтра за ссылки и требуется провести анализ ссылочной массы по выгрузкам наиболее часто ссылающихся доменов на сайт из Google Search Console.

Текст по столбцам на примере выгруженного файла из Google Search Console содержащим наиболее часто ссылающиеся домены на сайт

1.2. Удаление дубликатов:

Каждый раз составляя качественное семантическое ядро, необходимо собирать поисковые фразы из Яндекс Метрики и ключевые слова из бесплатного поискового трафика Google Analytics. После эти данные нужно свести в одну таблицу и удалить дубликаты.

Удаление дубликатов на примере отчета бесплатный поисковый трафик Google Analytics и отчета поисковые фразы из Яндекс Метрики

1.3. Фиксация первой строки / столбца:

1.3.1 Фиксируем первую строку:

Фиксация первой строки

1.3.2. Фиксируем первый столбец:

Фиксация первого столбца

1.3.3. Фиксируем первый столбец и строку одновременно:

Одновременная фиксация первой строки и первого столбца

1.3.4. Фиксируем >1 строки / столбца

Фиксация > 1 строки / столбца

1.4. Копирование значений из колонки, вычисленной по формуле:

Копирование значения ячейки, вычисленной по формуле

1.5. Условное форматирование:

Форматирование таблицы с обозначением следующих условий: если число меньше 11 (подсвечивать зеленой заливкой), если больше 10 (подсвечивать красной заливкой)

1.6. Настройки:

1.6.1. Автосохранение:

Работая в Microsoft Excel следует понимать, что ваш компьютер может выключиться, или же произойти нечто, что повлият на стабильность работы самого окна Excel, то в данном случае все ваши несохраненные данные могут быть утеряны. Поэтому настоятельно рекомендуем изменить стандартные настройки времени автосохранения.

Изменение стандартного времени автосохранения

1.6.2. Создание дополнительной вкладки:

Дополнительные вкладки помогают сгруппировать наиболее необходимые команды на одной вкладке.

Создание дополнительной вкладки

1.7. СЦЕПИТЬ:

=СЦЕПИТЬ("Redirect 301 ";A2;" ";B2)

Функция СЦЕПИТЬ на примере настройки 301 редиректов со старой структуры на новую

1.8. СЧЁТЗ:

=СЧЁТЗ(A2:A560) — подсчитывает количество непустых ячеек в диапазоне.

Функция СЧЁТЗ на примере подсчета непустых ячеек с условием что в диапазоне имеются пустые ячейки

1.9. СЧЁТЕСЛИ:

=СЧЁТЕСЛИ(B2:B18;"<11") — подсчитывает количество ячеек в диапазоне соответствующих заданному условию.

Функция СЧЁТЕСЛИ на примере подсчета количества ячеек в которых число меньше 11

1.10. Фиксация положения одной из ячеек в формуле:

=СЧЁТЗ(A2:A18) — подсчитывает количество непустых ячеек в диапазоне.

=СЧЁТЕСЛИ(B2:B18;"<11")/$A$19 — фиксирует положение ячейки (столбца и строки) в формуле.

Копирование формулы с фиксацией положения одной из ячеек

Статистика Excel для SEO и анализа данных

Команда SEOnews

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

У всех инструментов есть кое-что общее — это кнопка «Экспорт». Возможно, это самое важное свойство всех инструментов. Сэкспортировав данные в Excel, специалист может сортировать и фильтровать их так, как ему нужно. Большинство из нас регулярно пользуется Excel, но только его стандартными функциями. Однако Excel может гораздо больше!

Виргил Гик (Virgil Ghic) рассказал о наиболее распространенных приемах обработки статистических данных. И самое главное — вам не придется их запоминать, все они встроены в Excel.

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

В статистике можно выделить два главных направления: описательная статистика и логически выведенная статистика.

Описательная (дескриптивная) статистика используется в том случае, когда вам известны все значения в наборе данных. Например, вы задаете 1000 респондентов вопрос, любят ли они апельсины, и предоставляете им два варианта ответа: Да и Нет. Затем собираете данные и выясняете, что 900 человек ответили Да и 100 — Нет. Пропорция будет следующей: 90% составил ответ Да и 10% — ответ Нет. Достаточно легко, не правда ли?

Но как быть в том случае, когда у нас нет всех данных?

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

Давайте предположим, что вы хотите рассчитать количество просмотров email за последние два года, но вы располагаете данными только за последние шесть месяцев. Предположим, что из 1000 email-адресов письма открыли только 200 получателей, значит остальные 800 — не открывали. Следовательно мы имеем соотношение 20% открывших к 80% неоткрывших. Эти данные верны для периода в шесть месяцев, но они также могут быть верны и для периода в два года. Логически выведенная статистика поможет нам понять, насколько верно наше предположение.

Доля открытых писем может составлять 20%, а может немного отличаться. Допустим, она варьируется +/-3%, тогда доля открытых писем будет составлять от 17% до 23%. Но насколько мы уверены в этих данных? Кроме того, какой процент случайной выборки из всего набора данных будет находиться в диапазоне от 17% −23%?

В статистике считается приемлемым уровень достоверности в 95%. Это означает, что 95% выборочных данных, взятых из всего набора данных, будет соответствовать 17-23%, оставшиеся 5% будут либо выше 23%, либо ниже 17%. Но мы уверены в том, что для 95% доля открытых писем составляет 20% +/- 3%.

Термин данные (data) предполагает любую величину, обозначающую объект или событие, например, посетители, исследования, письма.

Термин набор данных (data set) состоит из двух компонентов: Единица наблюдения (observation unit) может означать посетителей и переменные, представляющие демографические данные ваших посетителей (возраст, зарплата, образование). Совокупность (population) предполагает каждого члена вашей группы, а в веб-аналитике — всех посетителей. Предположим, посетителей 10 000.

Выборка (sample) — часть вашей совокупности, представленная на основе определенной даты или сконвертированных посетителей и т.д. В статистике наиболее ценной является случайная выборка (random sample).

Распределение данных (data distribution) определяется частотой, согласно которой представлены значения в наборе данных. Представив частоту на графике с диапазоном значений на горизонтальной оси и частотой по вертикальной оси, мы получим кривую распределения. Наиболее распространенным является нормальное распределение или колоколообразная кривая.

Самый простой способ понять это — представить на количестве посетителей веб-сайта. Например, в среднем ежедневное количество посещений сайта составляет 2000, иногда бывает больше — 3000 посещений или меньше — 1000.

Здесь пригодится теория вероятности (probability theory).

Вероятность

Самым распространенным примером вероятности, известным многим, является подбрасывание монеты. У монеты две стороны: орел и решка. Какова вероятность того, что монета ляжет той или другой стороной? Существует две возможности, таким образом 100%/2=50%

Достаточно теории, перейдет к практике.

Excel — прекрасный инструмент, который поможет нам в работе со статистикой. Отметим, что это не лучший инструмент, но зато все знают, как им пользоваться, поэтому рассмотрим именно Excel.

Во-первых, установите надсройку Analysis ToolPack.

Откройте Excel, перейдите в Опции -> Add-ins->внизу списка вы найдёте

Нажимайте Go ->выберите Analysis ToolPack->и нажимайте OK.

Теперь в панеле выберите опцию Данные и найдите там Анализ данных.

Инструмент Анализ данных может предоставить вам невероятную статистическую информацию, но давайте начнем с чего-нибудь попроще.

Среднее, Медиана и Мода

Среднее (mean) это статистическое значение среднего значения, например, средним для 4,5,6 будет 5. Как рассчитать среднее значение в Excel? =average(число1,число2 и т.д.)

Mean=AVERAGE(AC16:AC21)

Путем вычисления среднего мы определяем, сколько мы продали в среднем. Эта информация полезна, если нет экстремальных значений (или выбросов). Почему?

Например, мы продали в среднем товаров на $3000, но на самом деле нам повезло, т.к. 6 сентября покупатели потратили больше. В предыдущие шесть дней товара было куплено в среднем лишь на $618. Исключив крайние значения от среднего, можно получить более репрезентативные даные.

Медиана (median) это значение, которое делит набор данных на две равные части. Например, для набора данных 224, 298, 304 медианой является — 298. Для того чтобы вычислить среднее для большого набора данных, можно использовать следующую формулу =MEDIAN(224,298,304).

Когда может пригодиться медиана? Медиана полезна, когда у вас есть неравномерное распределение, например, цена ваших конфет варьируется от $3 до $15 за упаковку, но также у вас есть очень дорогие конфеты за $100, которые покупают редко. В конце месяца вы делаете отчет, и вы видите, что вы продали в основном дешевые конфеты и только пару упаковок за $100. В этом случае вам будет полезен расчет медианы.

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

Мода (mode) самое распространенное значение, например, мода для: 4,6,7,7,7,7,9,10 это 7.

Рассчитать моду в Excel вы можете с помощью формулы =MODE(4,6,7,7,7,7,9,10).

Но имейте в виду, что Excel выдает за моду наименьшее значение из возможных. Например, вы рассчитываете моду для следующего набора данных: 2,2,2,4,5,6,7,7,7,8,9, сразу отметим, что здесь две моды — 2 и 7, но Excel покажет вам только наименьшее значение — 2.

Когда можно использовать функцию моды? Расчет моды полезен только для целых чисел, например 1, 2 и 3. И нежелателен для дробных чисел, таких как 1,744; 2,443; 3,323, т.к. числа могут дублироваться.

Гистограммы

Предположим, недавно в вашем блоге была опубликована сотня гостевых постов, некоторые из них хорошего качества, другие не очень. Возможно, вы захотели узнать, какие из постов получили по 10, 20, 30 обратных ссылок или вам интересны твиты, лайки, расшаривания, а может и просто посещения.

Мы разделили все это на группы с помощью графического представления данных под названием гистограмма. Виргил Гик (Virgil Ghic) приводит пример с посещениями и постами, как один из менее сложных. Он настроил свой аккаунт в Google Analytics следующим образом: у него есть профиль, в который собирается статистика только по его блогу, ничего больше. Если у вас нет такого же профиля, тогда вы можете использовать сегменты.

Это несложно.

Далее идем в экспорт ->CSV

Открываем Excel и создаем два столбца: Целевая страница и Посещения. Также создаем список, в соответствии с которым будем категоризировать данные. В данном случае мы определяем, сколько статей имеют 100, 300, 500 и т.д. посещений.

Данные -> Анализ данных->Гистограммы->OK

• Входной интервал (input range) будет столбец с посещениями.
• Интервал карманов (bin range) — это группы.
• Выходной интервал (output range), кликните на ячейку, где вы хотите создать гистограмму.
• Проверьте график выхода (chart output).
• Нажимаем OK.

Вы получили гистограмму, которая отражает количество статей, сгруппированных по посещениям. Чтобы лучше разобраться в гистограмме, нужно кликнуть на любую ячейку в столбцах Bin и Frequency и отфильтровать частоту от меньшего к большему.

Анализировать данные теперь еще проще. Возвращаемся и фильтруем все статьи от меньшего или равного 100 посещениям (Визиты, выпадающее меню->Числовые фильтры->Между…0-100->Ok) в прошлом месяце и обновляем.

Источники посещений

Насколько значим данный отчет для вас?

Он достаточно хорош, но не более того. Мы можем проанализировать рост и снижение посещений, но … какова доля посещений с YouTube в общей статистике посещений за февраль? Конечно, можно разбираться, но это дополнительная работа, и это очень неудобно, когда этот вопрос вам задает клиент по телефону. Чтобы ваши графики были максимально полезны, создавайте описательные отчеты.

В вышеупомянутом отчете просто разобраться, сложнее его создать. Но зато он вам очень пригодится.

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

Однако правильней будет провести хи-квадрат тест (Chi-Square Test), который позволит нам понять была ли это счастливая случайность или эффективная маркетинговая кампания.

Фактический столбец — количество посещений, Ожидаемый столбец — среднее из «фактического» столбца. Формула хи-квадрат теста следующая: =1-CHITEST(N10:N16,O10:O16), где N10:N16 — это значения из Актуального столбца, а O10:O16 — значения из Ожидаемого.

Результат в 100% является уровнем достоверности, свидетельствующий о вероятности того, что рост посещений с Facebook является результатом маркетинговых кампаний.

Создавая метрики, помните, они должны быть максимально понятными и релевантными вашей бизнес-модели.

В данном видео вы найдете еще один пример использования хи-квадрат теста.

Скользящее среднее (moving average) и линейная регрессия (linear regression) для прогнозирования

Мы часто встречаем такие графики, как расположенный выше. На них могут быть представлены продажи, посещения и т.д. И они всегда выглядят именно так: прямая, идущая вверх-вниз. В такой картине данных присутствует много шума, который мы хотим сгладить для лучшего понимания данных.

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

Давайте разберемся, как мы можем использовать данный метод.

Шаг 1:

Экспортируйте в Excel число посещений/продаж за долгий период времени, например, один-два года.

Шаг 2:

Данные-> Анализ данных -> Скользящее среднее ->OK

Входной интервал — это столбец с числом посещений.

Интервал — это количество дней для которых вычисляется среднее. Вам нужно создать одно скользящее среднее с большим числом, например, 30 и одно с меньшим числом, например, 7.

Выходной интервал — это столбец справа от столбца посещений.

Повторите данные шаги для интервала в 7 дней.

Теперь ваши данные выглядят примерно так:

Если вы выберете все столбцы и построите линейный график, вы получите следующее:

В таком представлении данных меньше шума, их легче анализировать и можно увидеть некоторые тренды. Зеленая линия визуально немного облегчает график, но она реагирует на почти каждое крупное событие. Тогда как красная линия является более стабильной, она отражает реальный тренд.

В конце линейного графика вы увидите такие значения, как Прогноз. Это прогнозируемые данные, выведенные на основе предыдущих трендов.

В Excel есть два способа создать линейную регрессию, используя формулу =FORECAST(x,known_y’s, known_x’s), где «x» означает дату, для которой вы создаете прогноз; «known_y’s» — это столбец посещений, «known_x’s» — столбец с датами. Данный метод не так уж сложен, но есть более простой способ сделать то же самое.

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

Убедитесь в том, что вы выбрали весь набор данных для того, чтобы результат был точный.

Существует теория при сравнении скользящего среднего для 7дней и 30дней. Как было сказано выше линия 7дней реагирует практически на все основные изменения, в то время как линии 30дней требуется больше времени, чтобы изменить свое направление. Как правило, когда скользящее среднее 7дней пересекает скользящее среднее 30дней, вы можете рассчитывать на существенное изменение, которое будет длиться дольше, чем день или два. Как можно увидеть выше, 6 апреля скользящее среднее 7дней пересекает скользящее среднее 30дней, число посещений снижается, у 6 июня линии снова пересекаются и тренды идут вверх. Этот метод полезен, когда вы теряете трафик и не уверены, тренд ли это или всего лишь суточные колебания.

Линия Тренда (trendline)

Те же результаты могут быть получены с помощью линии тренда в Excel: щелкните правой кнопкой мыши по движущейся линии -> выберите: Добавить линию тренда (Add Trendline).

Теперь вы можете выбрать Тип Регрессии (Regression Type) и использовать функцию Прогноз. Линия тренда, возможно, наиболее полезный инструмент, помогающий разобраться почему ваш трафик/продажи растут, сокращаются или остаются неизменными.

Без линейной функции мы не сможем уверенно говорить о том, что мы делаем правильно, а что нет. Добавляя линейный тренд мы видим, что наклон положительный, уравнение линии тренда объясняет, как движется наш тренд.

y=0.5516x-9541.2

X — это количество дней. Коэффициент Х — 0.5516 — положительное число. Это означает, что линия тренда идет вверх. Т.е. ежедневно мы увеличиваем количество посетителей на 0,5, это является трендом.

R^2 — это уровень точности модели. Наше число R^2 = 0,26 означает, что наша модель объясняет 26% вариаций. Проще говоря, мы уверены на 26%, что ежедневно количество посетителей увеличивается на одного.

Сезонное прогнозирование

Предположим, скоро Рождество. Прогнозирование на зимний сезон будет весьма полезно, особенно когда с этим периодом вы связываете большие надежды.

Если вы не попали под Google-фильтры Panda или Penguin и ваши продажи/посетители соответствуют сезонным тенденциям, вы можете спрогнозировать характер продаж или посещений.

Сезонное прогнозирование — это метод, который позволяем нам оценить будущие значения набора данных на основе сезонных колебаний. Сезонные наборы данных есть везде, например, магазин мороженого будет очень востребован во время летнего сезона, а сувенирный магазин может достичь максимальных продаж во время зимних праздников.

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

Следующий пример представляет собой базовую модель, но она может быть расширена до более сложных, чтобы отвечать вашей бизнес-модели.

Загрузите Пример прогнозирования в Excel

Для удобства восприятия я разобью весь процесс на этапы. Вам нужно загрузить таблицу Excel и выполнить следующие шаги:

• Экспортируйте ваши данные; чем больше данных, тем более точным будет прогноз! Укажите даты в столбце А, а продажи в столбце В.
• Рассчитайте индекс для каждого месяца и добавьте полученные данные в столбец С.

Для расчета индекса прокрутите вниз, справа вы найдете таблицу под названием Индекс (Index). Индекс за январь 2009 рассчитывается путем деления продаж за январь 2009 г. на среднее значение продаж за весь 2009 год.

Таким же образом рассчитайте индекс для каждого месяца каждого года.

В столбце S с 38 по 51 строки мы рассчитали средний индекс для каждого месяца.

Т.к. сезонность повторяется каждые 12 месяцев, мы скопировали значения индекса в столбец C, т.к. они остаются актуальными. Вы можете заметить, что индекс января 2009 такой же как и в январе 2010 и 2011 годов.

• В столбце D рассчитайте Скорректированные данные (Adjusted data) путем деления ежемесячных продаж на индекс =B10/C10.
• Выберите значения из столбцов A, B и D и постройте линейный график.
• Выберите скорректированную линию (в моем случае это красная линия) и добавьте линейный тренд, проверьте окошко «Показать уравнение на графике».

• Рассчитайте несезонные значения для прошлого периода путем умножения ежемесячных продаж на коэффициент из уравнения линии тренда и добавьте константу из уравнения (столбец Е).

После создания линии тренда и представления Уравнения на графике, мы принимаем во внимание Коэффициент — число, которое умножается на X, и константу — число, которое, как правило, является отрицательным.

Проставляем коэффициент в ячейку E2, а Константу — в ячейку F2.

• Рассчитайте Сезонные значения для прошлого периода путем умножения индекса (столбец С) на ранее рассчитанные данные (столбец Е).
• Рассчитайте средний процент ошибки (MPE — mean percentage error) путем деления продаж на Сезонные значения для прошлого периода минус 1 (=B10/F10-1).
• Рассчитайте средний абсолютный процент ошибки (MAPE — mean adjusted percentage error) путем возведения в квадрат данные в стобце MPE (=G10^2).

В моих ячейках F50 и F51 представлены спрогнизованные данные для ноября 2012 и декабря 2012. Ячейка H52 демонстрирует погрешность.

С помощью данного метода мы можем определить, что в декабре 2012 мы заработаем $22,022 ± 3.11%. Теперь идем к боссу и рассказываем о своих предположениях.

Стандартное отклонение

Стандартное отклонение (standard deviation) говорит о том, насколько наши значения отклоняться от среднего значения, мы можем назвать его уровнем доверия. Например, у вас есть данные по продажам за месяц и данные по ежедневным продажам, причем каждый день объем продаж разный. Вы можете использовать стандартное отклонение чтобы рассчитать, насколько вы отклонились от среднемесячного показателя.

Вот две формулы Стандартного отклонения в Excel, которые вы можете использовать

=stdev — когда у вас есть выборочные данные -> Авинаш Кошик подробно рассказывает, как работает выборка

или

=stdevp — когда у вас полная совокупность данных, т.е. когда вы анализируете каждого посетителя. Я предпочитаю именно =stdev, потому что бывают случаи, когда код отслеживания JS не работает.

Давайте посмотрим, как мы можем применить стандартное отклонение в нашей повседневной жизни.

Используя стандартное отклонение в Excel, данные можно представить более наглядным и понятным образом. Как вы видеть на графике выше, средняя ежедневная посещаемость равна 501 со стандартным отклонением 53. Но самое главное на таком графике вы видите, где вы превысили свои обычные показатели. Это позволит выделить те маркетинговые кампании, которые привели к такому всплеску, и применить/проверить их еще раз.

Для работы используйте данную ссылку: http://blog.instantcognition.com/wp-content/uploads/2007/01/controllimits_final.xls

Корреляция

Корреляция — это статистическая взаимосвязь двух или нескольких (случайных) переменных. Типичным примером корреляции в веб-аналитики может быть количество посетителей и количество продаж. Чем больше у вас целевых посетителей, тем больше будет продаж. У доктора Пита (Dr Pete) есть хорошая инфографика, посвященная корреляции vs. причинности.

В Excel мы используем следующую формулу для определения корреляции:

=correl(x,y)

Как вы видит на картинке выше, корреляция между Посещениями и Продажами равна 0.1. Что это значит?

• от 0 до 0,3 считается слабой корреляцией
• от 0.3 до 0,7 — нормальная
• более 0,7 — сильная

Заключение следующее: ежедневные посещения не влияют на ежедневные продажи, что также означает, что посетители, которых вы привлекли, не являются целевыми. При принятии решения полагайтесь на ваше деловое чутье, но не игнорируйте корреляцию в 0,1.

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

Данные->Анализ данных->Корреляция

Ваш результат будет похож на один из этих:

Здесь мы видим, что ни один из элементов не коррелируют друг с другом:

• Продажи и посетители = корреляция 0,1.
• Продажи и расшаривания = корреляция 0,23.
• Описательная статистика для быстрого анализа.

Теперь у вас есть довольно хорошее представление о среднем, стандартных отклонениях и т.д., но расчет каждого статистического элемента требует дополнительного времени. В разделе Анализ данных вы найдете краткий обзор наиболее распространенных элементов.

• Данные->Анализ данных-> Описательная статистика.
• Входной интервал — выбираем данные для анализа.
• Выходной интервал — выбираем ячейку, где отобразится таблица.
• Проверяем Сводную статистику.

Достаточно хороший результат:

Нам осталось разобраться с тем, что такое Эксцесс (kurtosis) и Асимметрия (skewness).

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

Асимметрия показывает, насколько искажены ваши данные — негативно или позитивно, по сравнению с нормальным распределением. Теперь представим асимметрию более наглядно:

Асимметрия: −0.28 (распределение, скорее всего, ориентировано на более высоких значений 2500 и 3000).

Эксцесс: −0.47 (небольшое пиковое отклонение от центра).

Все эти методы можно использовать при анализе данных. Самой большой сложностью со статистическими данными и Excel является возможность применения этих методов в самых различных ситуациях, не ограничиваясь посещениями или продажами. Отличный пример использования нескольких статистических подходов представил Том Энтони в своем посте об инструменте для определения ссылочного профиля (Link Profile Tool).

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

SEM & SEO макросы — !SEMTools для Excel

Раздел SEM & SEO содержит в себе функции необходимые для поискового маркетинга.

Чтобы не смешивать эти профессиональные функции с общими, он был выделен в отдельный блок. Раздел частично копирует структуру всей надстройки — содержит меню «извлечь», «удалить», и «изменить»
Этот раздел будет пополняться новыми функциями так как рынок поисковой рекламы динамично развивается и ему нужны новые инструменты.  

Изменить

Данный раздел содержит в себе возможности по изменению
— Типов соответствия ключевых фраз.
— Словоформ — лемматизация
— Файла загрузки рекламных кампаний — Яндекс.Директа в формат Google Ads.

Удалить

Меню удалить с функциями исключительно для поискового маркетинга содержит в себе
1. удаление стоп слов
2. удаление utm-меток и get-параметров
3. удаление модификаторов у стоп слов

Извлечь 

Меню извлечь содержит возможности по извлечению текстовой информации из URL. Сюда входит такая мета-информация, как Title, h2, коды ответа сервера.

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

Поисковые подсказки

Раздел содержит различные способы сбора поисковых подсказок из таких поисковых систем как Yandex, Google, YouTube, Amazon, Bing.

Семантический анализ

В этой группе макросов  три типа  возможностей . 

Частотный анализ n-грамм (последовательностей из n слов)

Анализ поисковых запросов

Поиск не релевантных слов в массиве фраз

Кластеризация

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

Сборник полезных функций Excel для работы SEO-специалиста ~ Страницы Интернета

+

Потому что, большинство веб-мастеров не знают о том, что множество наших проблем может решить программа Microsoft Excel (как и Google SpreadSheet, и LibreOffice). Далее — яркие тому доказательства: список 12 функций, знание которых заметно облегчить жизнь рядового SEO-специалиста.+

№1 функция: ДЛСТР (англ. LEN)+

№ 2 функция : СЖПРОБЕЛЫ (англ. TRIM)+

Очень полезная функция, когда в процессе работы при копировании всего объема текста — появляются пробелы до/после/между слов, мешающие и стопорящие дальнейшую работу над всем текстом.+

№ 3 функция :ПРОПИСН (англ. UPPER), СТРОЧН (англ. LOWER)+

№ 4 функция : ПРОПНАЧ (англ. PROPER)+

На первый взгляд может показаться, что данная функция бесполезна — казалось бы, зачем нужно заменять первую букву каждого слова? Но бывают случаи, когда есть необходимость проверить частотность группы ключей, содержащих например, названия компаний. 

Как Вы наверное знаете, при проверке основными сервисами (как следствие — и программами) все буквы запроса автоматически приводятся в строчный вид. В итоге таблица на несколько тысяч строк вида ЗАПРОС + КОМПАНИЯ, где название компании указано с маленькой буквы. Для дальнейшего использования необходимо сделать соответствующую корректировку. В этом случае данная функция ПРОПНАЧ сократит массу времени. Для её использования нужно сначала расщепить массив по 2-м столбцам (запрос и название) с помощью функции Данные → Текст по столбцам. Затем применяя функцию ПРОПНАЧ к столбцу с названиями компаний — производим сцепку с первым столбцом. 

Хочется отметить, что данное решение не единственное из возможных. Но однозначно — самое простое.+

№ 5 функция : СЦЕПИТЬ (текст1;текст2;текст3…) (англ. CONCATENATE)+

К примеру, допустим, вам нужно отправить ссылки с 700 не совсем качественных доменов в инструмент Disavow Links. Синтаксис инструмента предполагает формат вида domain: ваш_домен.kz. Что делать? Прописывать все 700 строк вручную? Естественно, нет. Вам просто нужно ввести в строке:+

1. Обывательский (уровень юзера) +

   Мы просто делаем 2 дополнительных столбца или ячейки с данными: 

   Вместо первого текстового блока в формуле используем ссылку на первую ячейку, вместо второго — на вторую. В результате чего получается такая картинка: 

    

   В случае, если были указаны какие-либо определенные ячейки, а не столбцы например, не забудьте задать абсолютные адреса: 

   +

2. Мастерский (уровень профи)+
   1. Используем одинарные кавычки. Пишем: 

       

      Хотя синтаксис ссылок с одинарными кавычками будет являться валидным, его применение в этом случае все же не совсем канонично.+

   2. Используем символ кавычек (chr(34), символ(34)) 

      У двойных кавычек есть цифровой код и мы можем просто вывести их с помощью функции chr (в русской версии «символ»). 

      +

№ 6 функция: СЧЁТЕСЛИ (диапазон; критерий) (англ. COUNTIF)+

№ 7 функция : СУМЕСЛИ (диапазон; критерий; диапазон_для_сложения) (англ. SUMIF)+

№ 8 функции: ЛЕВСИМВ и ПРАВСИМВ (текст; количество знаков) (англ. LEFT и RIGHT)+

№ 9 функция: ПОИСК (искомый фрагмент, просматриваемый текст, начальная позиция) (англ. SEARCH)+

На практике использование связки ЛЕВСИМ + ПОИСК (или аналогичный ПРАВСИМВ + ПОИСК) встречается достаточно редко. И вспоминаются следующие слова — «нет ничего более беспомощного, безответственного и испорченного, чем сеошник, прибегнувший к функциям поиска по подстроке». 

Но тем не менее, давайте разберем на примере: у нас есть список URL-ов, и нам необходимо выделить из них непосредственно домен.+

Выстроим следующую логическую цепочку: нам надо «найти» точку непосредственно на слеше после домена, после этого убрать кусок строки слева — с нулевой точки до найденной нами точки конца домена. И сделаем разобивку задачи на несколько подзадач следующим образом:

1. Что мы ищем? Слеш. Где ищем? В ячейке с URL. С какой позиции ищем? Как минимум, с восьмой, чтобы исключить начальные слеши. 
   В итоге получаем: 
   

   +

2. Выделим подстроку с доменом: с начала строки до точки вхождения слеша: 
   

    
   При даже небольших навыках пользования текстовыми функциями программы Excel можно творить настоящие чудеса.+

№ 10 функция: ВПР (искомое_значение, таблица, номер_столбца, тип_совпадения) (англ. VLOOKUP)+

Мы видим, что порядок сайтов в этих двух таблицах разнится. Без использования функций перенести данные из второй таблицы в первую, кроме как самым известным нам «ручным» способом, невозможно. Но давайте попробуем использовать функцию ВПР.

• Первый параметр (А2), определяет, по какому значению происходит поиск совпадений. В нашем случае нам надо «состыковать» таблицу по отдельным доменам.
• Второй параметр ( F2:h21) — это таблица с «эталонами». То есть та, где именно мы ищем.
• Третий параметр (2) — номер столбца в этой «эталонной» таблице, из которого берется значения. Слева-направо, в случае с «ТИЦ», значение «2».
• Четвёртый параметр (ЛОЖЬ) — тип совпадения. Это самое важное. Здесь таится одна из самых больших сложностей этой функции.

Тут, лидерство по полезности и упрощению работы SEO-специалиста, поровну делят между собой 2 основные функции: очистка от дублей и разбивка данных по столбцам и по разделителю.+

№ 11 функция: Данные → Удаление дубликатов (Data → Remove Duplicates)+

К примеру, у нас есть список доменов на 1000 строк. Как вариант, можно попробовать найти и убрать все дубли вручную, можно отсортировать список в алфавитном порядке и удалить вручную с уже намного меньшими усилиями, можно так же использовать макрос для Excel, софт по работе с ключевыми словами (удаляет дубли — по умолчанию), паблик-скрипты или различные онлайн-сервисы. Понятно, что если количество строк большое (к примеру < 1 000 000 строк для Excel), то вариант со специализированным софтом или скриптами является единственно правильным. Но если строк меньше пограничного максимума, Excel справляется с задачей на отлично. 

Итак, на старте имеем 1266 доменов + aweb.kz:+

Переходим во вкладку «Данные» и находим пункт меню «Удалить дубликаты».+

№ 12 функция: Данные → Текст по столбцам (Data → Text to Columns)+

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

Мы рассмотрели основную связку Excel + SEO и надеемся, что эта информация окажется полезной для Вас, а так же спасёт не один десяток веб-мастеров от бессмысленной траты времени на рутинные задач

Используем EXCEL для SEO: полезные советы

Как пользоваться EXCEL для SEO, рассмотрим практические советы и примеры по оформлению документа. Обзор полезных формул.

SEO предполагает обработку больших массивов данных, чтобы делать это быстро и успевать следить за десятками страниц и сотнями запросов нужен надежный помощник. Оптимальный вариант использовать пакет EXCEL. Стандартного функционала программы с головой хватит для аналитики, учета и контроля процесса SEO продвижения.

Готовим базовый файл

Для начала работы необходимо создать базовые колонки:

• ключ;
• страница продвигаемого сайта;
• релевантная страница в Яндексе;
• результат выдачи в Яндексе;
• частотность;
• тайтл;
• результат выдачи в Google;
• данные для бюджета и аналитики.

Создаем сортировку

Для этого необходимо выбрать все столбцы с данными, перейти на вкладку «Вставка» и выбрать «Вставить таблицу». Первая строка станет заголовками колонок, а в ячейках добавиться треугольник. Нажав на него EXCEL, покажет возможные варианты сортировки.

Отсеиваем дубли и повторы

При составлении семантического ядра важно отработать только уникальные ключи. Если в файле их более 1000 вручную – это долго, сложно и есть вариант что-то просмотреть. EXCEL решает проблему в пару кликов:

• выберите колонку с ключами;
• перейдите на вкладку «Условное форматирование»;
• создайте правило «Форматировать повторяющиеся значения».

Применив правило, все неуникальные значения будут выделены оранжевым цветом. Далее остается отсортировать значения по цвету и удалить дубли.

Добавляем краски

При работе с большими массивами данных удобно выделять однородные группы значений при помощи цветовых меток. Это позволяет в разы быстрее ориентироваться в таблице и следить за динамическими показателями. Реализуется функционал с помощью «Условного форматирования». Выбираем нужный столбец, переходим на вкладку, задаем правило «Форматировать ячейки, которые содержат»:

Если вместо «Значения ячейки» выбрать «Текст», можно подсветить запросы по заданному слову:

Используем формулы для прогнозирования

Подготовив ключи и цифровые данные легко можно спрогнозировать бюджет по каждому запросу. Для этого нужно использовать простую формулу: ([@[заголовок_колонки_1]] + [@[заголовок_колонки_2]])/2. Набор заголовков и математических действий ничем не ограничен. Для наглядности:

Чтобы скопировать данные, рассчитанные по формуле в другую колонку или перенести на новый лист, необходимо:

• выделить диапазон;
• скопировать значения;
• для вставки воспользоваться специальными возможностями;
• выбрать «Вставить значения».

Сравнение значений

Для нужд SEO часто нужно сравнивать данные, например, совпадает или нет продвигаемая страница с релевантной. Процедура выполняется при помощи логической формулы «ЕСЛИ». Чтобы проанализировать значения двух колонок вставьте формулу: =ЕСЛИ([@[ заголовок_колонки_1]]=[@[ заголовок_колонки_2]];1;0). После ввода данных напротив совпадающих значений появится цифра 1, не совпадающих – 0. Для удобства восприятия формулу можно заменить на: =ЕСЛИ([@[заголовок_колонки_1]]=[@[ заголовок_колонки_2]];»ДА»;»НЕТ»).

Отображение данных

Перед тем, как переходить к рассмотрению следующих формул, нужно отметить, что EXCEL умеет по-разному смотреть на значения в ячейках и воспринимать информацию как текст, дату, время, проценты или целые значения. Это удобно, поскольку автоматизирует процесс ввода данных. Чтобы отформатировать весь столбец или его часть, нужно выделить область, нажать правой кнопкой мыши и выбрать вкладку «Формат ячеек». В открывшемся меню выбрать необходимое:

Расчет средних и общих показателей

Чтобы посчитать среднее значение столбца используется формула: =СРЗНАЧ(Таблица_1[заголовок_колонки_1]). Если в документе используется несколько таблиц, то их нужно назвать. Соответственно вместо «Таблица_1» нужно писать фактическое название, а вместо «заголовок_колонки_1» — название расчетной колонки. Поскольку формула не привязана к ячейкам, вставлять ее можно в любое место на текущем листе EXCEL. Для подсчета общей суммы используется формула: =СУММ(Таблица_1[заголовок_колонки_1]).

Иногда возникает необходимость посчитать значение отдельного диапазона данных, тогда формулы меняются на:

• =СРЗНАЧ(A1:A10)
• =СУММ(B4:B20)

Если формулу нужно дополнить каким-либо математическим действием с привязкой к конкретной ячейке, используется знак $. Пример: мы получили средние значения по 10 группам запросов, теперь нужно рассчитать примерный бюджет. Данные о средних значениях хранятся в диапазоне K1:L10, расчетная сумма бюджета на 1 запрос – E1. Чтобы получить общие бюджеты на каждую группу вставляем в L1 формулу: =К1*$E$1 и протягиваем ее вниз до L10.

Используя этот нехитрый инструментарий можно в разы упростить работы и сэкономить время.

Автор: София Лупол

Как найти иголку в стоге сена? Формула для работы с данными в Excel

Джереми Готтлиб (Jeremy Gottlieb) — сотрудник агентства Distilled, эксперт в области технического SEO и РРС, разрабатывает стратегии контент-маркетинга, курирует создание продающих рассылок по email.

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

Несколько месяцев назад автор статьи начал использовать специальную формулу, которая позволяет группировать данные в таблицах Excel, не совсем стандартным образом. Такой подход позволил отыскать дополнительные возможности группировки данных.

Потребуется некоторое время, чтобы приспособиться к работе с формулой и запомнить её. Однако эти усилия обязательно принесут плоды. Общий вид формулы с использованием функций: ЕСЛИ и ЕЧИСЛО таков:

=if(isnumber(search(“string 1”, [beginning cell])),”Category 1”, if(isnumber(search(“string 2”, [beginning cell])),”Category 2”, “Other”)

Примечание: функция ЕЧИСЛО() в MS EXCEL, английский вариант ISNUMBER(), даёт возможность проверить, являются ли значения числами или нет. В статье приводятся варианты формул на языке оригинала.

В каких же случаях целесообразно применять её, рассмотрим далее.

Пример 1. Анализ ключевых слов

Если сбор и анализ семантики для клиентского сайта проводится вручную, то специалисту приходится вручную прорабатывать списки, состоящие из сотен и даже тысяч ключевых слов и фраз. Формула позволяет упростить работу со списком, сгруппировав слова и фразы с высокой степенью сходства. Для сбора семантики в данном случае могут применяться любые инструменты. К примеру, сервисы, разработанные Brightedge и SEMrush. Полезно использовать и «Планировщик ключевых слов от Google», с его помощью можно оценить объём поисковых запросов по каждому интересующему ключевому слову или фразе.

После того, как семантика собрана, важно сформировать файл CSV и загрузить результаты для дальнейшей обработки. Это позволит оценить эффективность ключевых слов из существующего списка и просмотреть статистику по числу запросов. Также можно объединить ключевые слова в группы. Тот, кто давно работает с «Планировщиком ключевых слов», знает, что отчёт содержит специальную колонку «Группы объявлений». Однако и здесь не всё так просто: данные отчёта без дополнительной обработки не всегда пригодны для дальнейшего использования.

Проблема заключается в том, что результат зачастую зависит от грамотного распределения ключевых слов по группам. В то время как одни ключевые слова в обязательном порядке должны присутствовать в запросе, другие служат лишь своеобразными «подсказками» при подборе новых актуальных запросов. На скриншоте выше показан срез ключевых фраз по запросам, связанным с тематикой «Спортивное питание». В частности, с поисковым запросом «workout supplements». Чтобы не исказить смысла, в переводе статьи будут приводиться англоязычные варианты.

Итак, количество строк в оригинальном списке — 681. Специалисту требуется узнать, какое число запросов содержит ключевое слово «supplement», а в каких запросах присутствует слово «muscle».

Первое, что требуется сделать — это удалить колонку А («Группы объявлений»), поскольку она в данном случае будет абсолютно бесполезной. Следующим шагом справа от столбца поисковых запросов можно добавить колонку «Категория». И далее, используя формулу, присвоить запросам категории «supplement» и «muscle». Для этого в ячейку C2 вводим формулу вида:

=if(isnumber(search(“supplement”,A2)),”Supplement”, if(isnumber(search(“muscle”,A2)),”Muscle”,”Other”))

Описание формулы будет следующим: «Если в ячейке A2 обнаружено ключевое слово «supplement»,то строке запроса должна быть присвоена категория Supplement. Если слово «supplement» не найдено, нужно искать ключевое слово «muscle», и в случае его обнаружения — присвоить строке категорию Мuscle. Если слова «supplement» и «muscle» не обнаружены, строке нужно присвоить категорию Other».

Аналогичным образом можно добавить в формулу и другие категории для распределения запросов. При этом в категорию «Other» («Другое») всякий раз будут попадать запросы, не вошедшие ни в одну из поименованных категорий.

Применение формулы для всего массива данных позволит с лёгкостью распределить ключевые слова по категориям, избавляя специалиста от необходимости делать это вручную. Двойной клик в правом нижнем углу ячейки С2 позволит применить формулу для всех ячеек в столбце С, которым присвоено численное значение в ячейке В. На скриншоте ниже показан результат применения формулы по отношению ко всем категориям с числовыми значениями. Здесь важно обратить внимание на то, как применение формулы изменило значения в ячейке С 19. На это повлиял анализ данных в ячейках A2 и A19.

Хотя на скриншоте слову «muscle» изначально не была присвоена категория, впоследствии она будет присвоена этому термину.

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

Если же в ячейке с запросом слова «supplement» и «muscle» содержаться не будут, формула присвоит сроке значение «Other».

Следующим шагом, используя формулу, можно проводить фильтрацию внутри категории «Other», постепенно углубляясь в исследование запросов:

Базовые функции excel для работы сео специалиста

=ЕСЛИ([@[ ПРОДВИГАЕМЫЙ URL]]=[@[ РЕЛЕВАНТНЫЙ В Я]];1;0)

Значение=$C$36+F13*2,2