7 эффективных способов использования Google Trends для анализа ключевых слов

Nick Churick

Ник — наш собственный «менеджер по коммуникациям» в Ahrefs (что бы это ни значило), и так совпало, что он еще и умелый писатель. Так что теперь он стал постоянным автором в нашем блоге.

Поделиться этой статьей

Содержание

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

Именно поэтому мы написали статью о Google Trends.

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

Начнем с представления  Google Trends.

Все что вам нужно знать о данных Google Trends (и

как они работают)…

Когда я начал писать эту статью, то понял, сколько в интернете неразберихи о Google Trends. Многие люди считают, что кривая тренда — это прямое представление о частоте запроса, выраженное во времени (наподобие той, которую вы можете видеть в инструменте планировщика ключевых слов Google).

Но это не совсем так.

Ниже приведены графики трендов по ключевому слову “цветы” (flowers) в Google Trends и инструменте ПКС Google:

Примечание.

Теперь подобные графики в планировщике ключевых слов Google доступны только, если вы тратите определенную сумму на рекламу в Adwords.

Как вы можете убедиться, они похожи, но не идентичны.

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

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

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

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

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

Ниже приведен график трендов по запросу “Facebook” за последние 12 месяцев (в США).

Чтобы продемонстрировать, как Google Trends строит свои графики “динамики популярности”, давайте представим, что у меня в распоряжении есть те же данные, что и у Google. (На самом деле нет, к сожалению).

Числа, которые указаны ниже, ни в коем случае не точны. Это допущения для объяснения, как это все работает.

Допущение 1: общее ежемесячное количество всех поисковых запросов в Google в США — около 10 миллиардов (источник).

Допущение 2: согласно анализу ключевых слов Ahrefs (Keywords Explorer), поисковая частота запроса “Facebook” в США составляет 83 миллиона.

Я сделал таблицу для моделирования:

Чтобы построить такой же график как в Google Trends, нужно проделать следующие шаги:

1. Подсчитайте относительную популярность как отношение частоты поискового запроса к общему количеству запросов.
2. Пропорционально пересчитайте эти значения так, чтобы максимальное было равно 100.
3. Разместите точки на графике.

Этот пример позволяет нам сделать два важных вывода:

1. Популярность поискового запроса изменяется при изменении его частоты (см. май 2017–июнь 2017). Довольно очевидно, правда?
2. Популярность поискового запроса также изменится, если изменится общее количество поисков, даже если поисковая частота запроса неизменна (см. июнь 2017–июль 2017 в моем примере выше).

Хоть в большинстве случаев популярность в Google Trends и коррелирует с частотой поискового запроса, теперь вы видите, что это не всегда так.

Например, если вы возьмете запрос “Star Wars” (Звездные войны), то заметите одинаковый всплеск (в декабре 2015) и в Google Trends и в анализе ключевых слов Ahrefs (Keywords Explorer).

И это несмотря на тот факт, что анализ ключевых слов показывает тренд поисковой частоты, в то время как Google Trends показывает тренд “популярности” (как обрисовано выше).

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

Предпочитаете видео? Посмотрите это.

1.

Установите сезонные тренды… затем создайте (и продвиньте) контент в ПРАВИЛЬНОЕ время!

Скорее всего вы понимаете, что поисковая частота некоторых ключевых слов подвержена сезонности.

Например, посмотрите на данные Google Trends по ключевому слову “зонтик” (umbrella) в США.

А вот такой же тренд в Австралии:

Здесь можно увидеть, что запрос “зонтик” наиболее популярен в США в июне, в то время как в Австралии пик припадает на декабрь. В эти месяцы в этих странах начинается сезон дождей, и люди понимают, что не хотят намокнуть.

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

Вы затем можете использовать полученные данные двумя способами:

1. Создайте релевантный контент и совместите его с пиком: например, если вы продаете зонтики в Австралии, составьте “Руководство по подготовке к периоду дождей” и опубликуйте его в декабре. В конце концов, именно в это время ваши потенциальные покупатели вероятнее всего будут искать подобную информацию.
2. Начните оптимизировать существующие релевантные страницы до пика: Предположим, у вас уже есть “Руководство по подготовке к периоду дождей” или, возможно, даже коммерческая страница по продаже зонтиков. Имеет смысл приоритизировать оптимизацию подобных страниц за пару месяцев до пика (т. е. за 2–3 месяца до декабря, если вы в Австралии). Например, начав кампанию по линкбилдингу в октябре, вы сможете рассчитывать на небольшой рост в ранжировании к периоду пикового поиска.

2. ИЗБЕГАЙТЕ “новомодных” запросов, проверяя неестественные скачки популярности, искажающие данные

Если вы пытаетесь определить хорошую тему для написания вашего следующего контента, не пишите ни единого слова, пока не проверите эту тему в Google Trends.

Вы не хотите потерять время, создавая контент на темы, которые уже теряют актуальность.

Например, согласно анализу ключевых слов Ahrefs, у слова “спиннер” (fidget spinner) средняя частота поисковых запросов более 900 тыс.

А у термина “йо-йо” (yoyo) — в среднем всего 47 тыс. в месяц.

Принимая во внимание, что конкурентность запроса (KD) для обоих ключевых слов одинаковая, казалось бы, имеет намного больший смысл создать контент под “спиннер”, правильно?

НЕТ.

Вот сравнение обоих запросов в Google Trends.

Синий – спиннер
Красный – йо-йо

Здесь видно, что у “спиннера” был огромный всплеск популярности в мае 2017.

Но с тех пор интерес совершенно угас и сейчас его уже почти не осталось.

С другой стороны, интерес к йо-йо (я обожаю йо-йо) остается стабильным.

Фактически, если посмотреть ближе, можно заметить, что “йо-йо” сейчас более популярны, чем “спиннеры”.

3. Найдите подходящие темы, которые в тренде ПРЯМО СЕЙЧАС… и извлеките из них выгоду

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

Но зачем бы вы стали это делать?

Что ж, предположим, что вы ведете блог о знаменитостях.

Вы проверяете популярные запросы за 4 марта 2018 года и видите такую картину.

Судя по всему в тренде Оскар!

Но разве тот факт, что эта тема сейчас в тренде, не означает, что вы несколько припозднились?

Необязательно.

Вот график поискового тренда по слову “Оскар” (Oscars) за семь дней, 2–9 марта:

Как видите, 4 марта — день, когда тема была трендовой в Google Trends — не было самим пиком. Пик пришелся на 5 марта.

И затем к 6 марта он затух.

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

Хоть это и не всегда так.

Иногда в Google Trends популярная тема освещается именно в день ее пика.

Например, фраза “День матери” (mother’s day) не становилась трендовой в поиске до 11 марта.

Воскресенье, 11 марта, 2018

Но поскольку 11 марта и был День матери (по крайней мере тут, в Великобритании), интерес к нему пропал уже на следующий день.

В этом случае, если вы не подготовили контент ко дню матери заранее, вы опоздали.

Но как узнать, трендовый запрос уже был на пике или еще нет?

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

Например, у Дня матери он пришелся на 26 марта 2017 года. Повторюсь, это день, когда был пик.

Глядя на эти исторические данные, вы можете предсказать, насколько вероятен интерес к теме в тренде.

4. Используйте данные Google Trends, чтобы спланировать свой календарь выпуска контента

Минуточку!

Давайте внимательно посмотрим на данные Google Trends для слова “Оскар” (Oscars).

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

Это происходит потому, что церемония вручения Оскара проводится в марте.

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

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

(Я знаю, о чем вы подумали… это вообще-то довольно очевидно, да? Я имею в виду, что если вы ведете сайт обозрения светской жизни, то скорее всего уже позаботились обо всем этом).

Вот менее очевидный пример:

В данном случае пик происходит примерно раз в месяц.

Как думаете, что это за запрос?

“Полная луна” (Full moon).

И как вам согласовать это с вашим контент-планом? Ведь нет смысла публиковать новый пост в каждое новолуние, правда?

Совершенно верно, но вы бы могли создать ОДИН пост и затем дополнять его свежей информацией каждый месяц. 🙂

Именно так поступают на Space.com с их календарем полнолуний.

Все что им нужно делать, это обновлять несколько фактов и цифр каждый месяц

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

ОТ РЕДАКТОРА

Хотите еще менее очевидный пример?

Проверьте данные Google Trends для запроса “страшный костюм” (scary outfit).

Каждый год происходит всплеск поисковой популярности в октябре — на Хэллоуин.

Что бы я порекомендовал в этом случае?

Публикуйте “Топ ХХ самых страшных костюмов на Хэллоуин в 20ХХ” каждый год в середине октября. (Или обновляйте уже существующий пост с прошлого года).

Просто, но эффективно. 🙂

5. Используйте “похожие запросы”, чтобы найти НОВЫЕ идеи ключевых слов (и даже УКРАСТЬ бизнес у ваших конкурентов)

Google Trends может помочь выявить дополнительные запросы, которые ищут люди, когда они ищут по вашему ключевому слову.

Например, люди, ищущие “кроссовки”, также ищут “Nike” и “Adidas.”

Примечание.

 В Google Trends для похожих запросов доступны две опции: “Самые популярные” и “Набирающие популярность”. Если вам стало любопытно, в чем разница между этими двумя опциями, кликните по иконке вопросительного знака напротив “Похожие запросы” в интерфейсе Google Trends (подсказка: эта иконка показана на снимке экрана выше).

Это не только великолепная возможность для поиска других ключевых слов (тех, о которых вы еще не думали), но и помощь в понимании нужд ваших потенциальных покупателей и их “пути к покупке”.

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

Например, вот похожие запросы для запроса “кроссовки Nike” (sneakers Nike), одного из похожих запросов из нашего предыдущего поиска.

Похожие запросы

Не останавливайтесь и на этом.

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

Применив фильтры поисковой частоты и конкурентности запроса (KD), можно обнаружить настоящий клад.

6. ТОЧНО узнайте, в каких городах и областях НУЖНЫ ваши товары или услуги (и нацельтесь на них в вашей SEO-стратегии)

Google Trends позволяет вам понять, где (в какой стране, городе, регионе или агломерации) поисковый запрос пользуется наибольшей популярностью.

Можно получить ответ на фундаментальный вопрос: где ваши товары или услуги наиболее востребованы?

Посмотрим, где в США наибольшим спросом пользуются обогреватели.

В северных штатах. Что довольно очевидно.

Что если сделать разбивку по городам? Google Trends может и это.

(Есть кто из Портленда? Как там у вас погодка?)

Но как можно использовать эти данные?

Вот пара идей.

1. Таргетируйте на эти регионы с помощью PPC (т. е. AdWords) — не тратьте зря деньги на рекламу в Google AdWords по всем США. Нацельте объявления только на те города или регионы, где проживают ваши потенциальные покупатели.
2. Создавайте полезный контент, который специально нацелен на людей в этих районах — напр., “Живете в Сиэтле? Вот как можно сэкономить деньги на отоплении этой зимой”.

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

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

На скриншоте ниже вы можете видеть пример концепции “популярности” в Google Trends:

Частота поискового запроса “Звездные войны” (Star Wars) очевидно намного больше в США, чем в Великобритании. Но графики почти идентичны. Что означает, что Звездные войны одинаково популярны в этих регионах.

7. Улучшите свою SEO-стратегию для видео, проанализировав поисковые запросы пользователей на YouTube

Google владеет YouTube, но это не значит, что популярность определенного поискового запроса одинаково равна в обоих поисковиках. Это не так.

Чтобы это проиллюстрировать, ниже приведены данные веб-поиска из Google Trends по запросу “обучение HTML” (HTML tutorial) с 2008 года.

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

А вот график трендов в YouTube.

(Да, в Google Trends можно отфильтровать по трендам YouTube — просто нажмите “Поиск YouTube” в выпадающем меню)

Интересно, мы нашли противоположность — популярность в YouTube растет.

Кажется, люди все чаще обращаются к YouTube, когда им нужно найти обучение HTML.

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

Заключение

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

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

А вы используете Google Trends в вашей маркетинговой работе? Надеюсь узнать о ваших советах и методах.

Trans­la­tion: Ole­sia Korob­ka, SEO in Fajela.

Как работать с Google Trends (Гугл Трендс)

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

Чем помогает Google Trends

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

• Определить популярные темы и тренды в выбранной категории.
• Выполнить анализ поисковых фраз по сезонам их актуальности.
• Построить географическую зависимость популярных искомых слов в выбранной тематике.

Самое популярное, или что сейчас в тренде

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

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

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

С чего начать?

Впервые данный инструмент от Google был запущен в американскую паутину в 2007 г., а для азиатских стран и Европы он стал доступен только с 2013 г. На данный момент он активно применяется более чем в 30 странах во всем мире.

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

При первом открытии инструментария любой пользователь сможет получить информацию о:

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

Настройка и первое использование

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

Анализатор состоит из следующих разделов:

• анализ;
• популярные запросы;
• год в поиске.

Раздел «Анализ»

Для определения популярности конкретного КС, необходимо перейти в раздел «Анализ». Откроется страница, где будет присутствовать строка для ввода слова, а снизу доступен выбор нескольких вариантов поиска. Например, по всему миру, за 12 месяцев (можно выбрать любой период от 1 часа до 7 лет), по категории и способу.

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

В результате проведения анализа получается определенное количество запросов. Его максимальное значение приравнивается к 100, остальные определяются по отношению к первому. Все полученные в результате проведения анализа данные можно выгрузить в формате CSV.

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

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

Раздел «Популярные…»

В этом разделе можно посмотреть популярные запросы в мире по отдельным регионам. Например, в России на момент 9 мая наиболее часто встречается фраза «Бессмертный полк». Сервис позволяет не только смотреть на текущий момент ключи, вводимые с ПК, но также и с мобильных устройств на вашем сайте. Для этого необходимо нажать кнопку «Встроить» и откроется дополнительное окно, в котором необходимо скопировать код для вставки на свой проект. Также в этом разделе можно открыть RSS-ленту, оформить подписку и открыть справку.

Раздел «Год в поиске»

В данном разделе пользователь может увидеть фразы и слова по популярным темам. Они все разделены на категории:

• события;
• утраты;
• люди;
• по вопросу «Что такое…?»;
• по вопросу «Как?»;
• в домашних условиях и т.д.

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

---

Изучите самую мощную функцию в Таблицах

Функция запроса Google Таблиц — это самая мощная и универсальная функция в Google Таблицах.

Он позволяет вам использовать команды данных для управления вашими данными в Google Таблицах, и это невероятно универсальный и мощный .

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

Это видео урок 14 из 30 моего бесплатного курса Google Sheets: Advanced Formulas 30 Day Challenge

Google Sheets QUERY Синтаксис функции

=QUERY(данные, запрос, [заголовки])

Требуется 3 аргумента:

1. диапазон данных, которые вы хотите проанализировать 90
2. запрос, который вы хотите выполнить, заключенный в кавычки
3. необязательный номер, чтобы сказать, сколько строк заголовка в ваших данных

Вот пример функции ЗАПРОС:

=ЗАПРОС(A1:D234,"ВЫБЕРИТЕ B, D",1)

Диапазон данных в этом примере — A1:D234

Оператор запроса — это строка в кавычках, выделенная зеленым цветом. В этом случае он сообщает функции выбрать столбцы B и D из данных.

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

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

ЗАПРОС Примечания к функциям

Ключевые слова не чувствительны к регистру, поэтому вы можете написать «ВЫБРАТЬ» или «выбрать», и оба варианта будут работать.

Однако буквы столбцов должны быть в верхнем регистре: A, B, C и т. д. иначе вы получите ошибку.

Ключевые слова должны стоять в следующем порядке (конечно, вам не обязательно использовать их все):

• выберите
• где
• группа по
• заказать по
• предел
• этикетка

Вы увидите примеры всех этих ключевых слов ниже.

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

Google Sheets QUERY Function Template

Щелкните здесь, чтобы открыть копию только для просмотра >>

Не стесняйтесь сделать копию: Файл > Создать копию…

Если вы не можете получить доступ к шаблону, он может быть из-за настроек Google Workspace вашей организации. Если вы щелкните ссылку правой кнопкой мыши и откроете ее в окне инкогнито, вы сможете ее увидеть.

Google Sheets QUERY Примеры функций

Если вы хотите следовать решениям, сделайте копию приведенного выше шаблона Google Sheet.

Вот как выглядят наши исходные данные:

В этом руководстве я использовал именованный диапазон для идентификации данных, что значительно упрощает и упрощает использование в функции ЗАПРОС. Не стесняйтесь также использовать именованный диапазон «страны», который уже существует в шаблоне.

Если вы не знакомы с именованными диапазонами, вот как их создавать:

Выберите диапазон данных и перейдите в меню:

Данные > Именованные диапазоны…

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

SELECT All

Оператор SELECT * извлекает все столбцы из нашей таблицы данных.

В правой части таблицы (я использовал ячейку G1) введите следующую функцию QUERY Google Sheets, используя обозначение именованного диапазона:

=ЗАПРОС(страны,"SELECT *",1)

Примечания: если вы не хотите использовать именованные диапазоны, то это не проблема. Ваша формула ЗАПРОС будет выглядеть так:

=ЗАПРОС(A1:D234,"ВЫБЕРИТЕ *",1)

В оставшейся части этой статьи я использовал именованный диапазон «страны», но вы можете продолжить используя вместо него ссылку на обычный диапазон A1:D234.

Результатом этого запроса снова является наша полная таблица, потому что SELECT * извлекает все столбцы из таблицы стран:

Вот это да! Вы написали свой первый ЗАПРОС! Похлопайте себя по спине.

SELECT Определенные столбцы

Что делать, если мы не хотим выбирать каждый столбец, а только определенные?

Измените функцию ЗАПРОС Google Таблиц, чтобы она читалась так:

= ЗАПРОС(страны,"ВЫБЕРИТЕ B, D",1)

На этот раз мы выбрали только столбцы B и D из исходного набора данных , поэтому наш вывод будет выглядеть следующим образом:

Важное примечание

Помните, наша функция ЗАПРОС находится в ячейке G1, поэтому вывод будет в столбцах G, H, I и т. д.

B и D внутри оператора выбора QUERY ссылаются на ссылки на столбцы в исходных данных.

Ключевое слово WHERE

Ключевое слово WHERE указывает условие, которое должно быть выполнено. Он фильтрует наши данные. Он идет после ключевого слова SELECT.

Измените функцию ЗАПРОС в Google Таблицах, чтобы выбирать только страны с населением более 100 миллионов человек:

= ЗАПРОС(страны,"ВЫБЕРИТЕ B, D, ГДЕ D > 100000000",1)

Наша выходная таблица:

Давайте посмотрим еще один пример ключевого слова WHERE, на этот раз выбрав только европейские страны. Измените формулу на:

=ЗАПРОС(страны,"ВЫБЕРИТЕ B, C, D, ГДЕ С = 'Европа'", 1)

Обратите внимание, что слово «Европа» заключено в одинарные кавычки. Сравните это с числовым примером, в котором число не заключалось в одинарные кавычки.

Теперь таблица результатов:

ORDER BY Keyword

Ключевое слово ORDER BY сортирует наши данные. Мы можем указать столбцы и направление (по возрастанию или по убыванию). Он идет после ключевых слов SELECT и WHERE.

Давайте отсортируем наши данные по населению от меньшего к большему. Измените формулу, добавив следующее ключевое слово ORDER BY, указав восходящее направление с помощью ASC:

=ЗАПРОС(страны,"SELECT B, C, D ORDER BY D ASC",1)

Выходная таблица:

Измените формулу ЗАПРОС, чтобы отсортировать данные по странам в порядке убывания, Z – A:

= ЗАПРОС(страны,"ВЫБЕРИТЕ B, C, Упорядочить по B DESC", 1)

Таблица вывода:

Ключевое слово LIMIT

Ключевое слово LIMIT ограничивает количество возвращаемых результатов. Он идет после ключевых слов SELECT, WHERE и ORDER BY.

Давайте добавим в нашу формулу ключевое слово LIMIT и вернем только 10 результатов:

=ЗАПРОС(страны,"ВЫБЕРИТЕ B, C, D ORDER BY D ASC LIMIT 10",1)

Теперь это возвращает только 10 результатов из наших данных:

Арифметические функции

Мы можем выполнять стандартные математические операции над числовыми столбцами.

Итак, давайте подсчитаем, какой процент от всего населения мира (7,16 миллиарда) приходится на каждую страну.

Мы собираемся разделить столбец населения на общее количество (7 162 119 434) и умножить на 100, чтобы вычислить проценты. Итак, изменим нашу формулу, чтобы она читалась:

=ЗАПРОС(страны,"ВЫБЕРИТЕ B, C, (D / 7162119434) * 100",1)

Я разделил значения в столбце D на общую численность населения (в скобках), а затем умножил на 100, чтобы получить процент.

Выходная таблица на этот раз:

Примечание. Я применил форматирование к выходному столбцу в Google Таблицах, чтобы отображались только 2 десятичных знака.

LABEL Ключевое слово

Этот заголовок для арифметического столбца довольно уродлив, верно? Ну, мы можем переименовать его, используя ключевое слово LABEL, которое стоит в конце оператора QUERY. Попробуйте это:

=ЗАПРОС(страны,"ВЫБЕРИТЕ B, C, (D / 7162119434) * 100 LABEL (D / 7162119434) * 100 'Процент'",1)

=ЗАПРОС(страны,"ВЫБЕРИТЕ B, C , (D / 7162119434) * 100 LABEL (D / 7162119434) * 100 'Процент' ",1)

Функции агрегирования

Мы можем использовать в наших расчетах и ​​другие функции, например, мин., макс. и среднее.

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

=ЗАПРОС(страны,"ВЫБЕРИТЕ максимальное(D), минимальное(D), среднее(D)",1)

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

GROUP BY Ключевое слово

Хорошо, сделайте глубокий вдох. Это самая сложная концепция для понимания. Однако, если вы когда-либо использовали сводные таблицы в Google Таблицах (или Excel), то вас это должно устраивать.

Ключевое слово GROUP BY используется с агрегатными функциями для суммирования данных в группы, как это делает сводная таблица.

Подведем итоги по континентам и посчитаем, сколько стран на континенте. Измените формулу запроса, включив в нее ключевое слово GROUP BY, и используйте агрегатную функцию COUNT для подсчета количества стран следующим образом:

=ЗАПРОС(countries,"SELECT C, count(B) GROUP BY C",1)

Обратите внимание: каждый столбец в операторе SELECT (т. е. перед GROUP BY) должен быть либо агрегирован (например, counted, min, max), либо стоять после ключевого слова GROUP BY (например, столбец C в данном случае).

Результат этого запроса:

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

=ЗАПРОС(страны,"ВЫБЕРИТЕ C, количество(B), мин.(D), макс.(D), среднее(D) СГРУППИРОВАТЬ ПО КОРДЕРУ ПО ср.(D) DESC LIMIT 3" ,1)

Это может быть легче читать, разбитое на несколько строк:

=ЗАПРОС(страны,
"ВЫБЕРИТЕ C, количество(B), мин.(D), макс.(D), среднее(D)
ГРУППА ПО C
ПОРЯДОК ПО avg(D) DESC
LIMIT 3",1)

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

Результат этого запроса:

Advanced Google Sheets QUERY Function Techniques

Как добавить итоговую строку в ваши формулы запроса

Как использовать даты в качестве фильтров в ваших формулах запроса

Есть еще 4 ключевых слова, которые не были рассмотрены в этой статье: PIVOT, СМЕЩЕНИЕ, ФОРМАТ И ОПЦИИ

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

Предположим, у вас есть столбец дат в столбце A набора данных, и вы хотите суммировать данные по годам. Вы можете свернуть его, используя скалярную функцию ГОД:

=ЗАПРОС(данные,"выбрать группу ГОД(А), СЧЕТЧИК(А) по ГОД(А)",1)

Для более продвинутых методов с Функция ЗАПРОС, посмотрите этот урок из курса Advanced 30:

Это видео является уроком 15 из 30 моего бесплатного курса Google Sheets: Advanced Formulas 30 Day Challenge .

Другие ресурсы

Функция ЗАПРОС рассматривается в дни 14 и 15 моего бесплатного курса Расширенные формулы для Google Sheets: Изучите 30 расширенных формул за 30 дней

Официальная документация Google по функции ЗАПРОС().

Официальная документация для языка запросов API визуализации Google.

Функция запроса в Google Таблицах — Полное руководство

1. Основы запросов Google Sheets

2. Написание сравнений дат в запросах

3. Отладка распространенных ошибок запросов

4. Как запрашивать несколько листов/диапазонов одновременно

5. Запрос по именам столбцов, а не по буквам

6. Создание сводных таблиц с помощью запросов

7. Использование выпадающих меню в качестве переменных

8. Разница между SQL и запросом

9. Вложение запросов вместе, как в SQL

Нужна помощь?

Функция Google Sheets Query заменяет так много других функций электронных таблиц, что это даже не смешно — ФИЛЬТРЫ, СРЕДНИЕ и СУММЫ исчезают из окна, когда они появляются на картинке.

Есть несколько нюансов в запросах, так что давайте пошагово. Этот пост содержит восемь видеороликов, под каждым из которых есть заметки об обрыве:

Погружаемся!

Кстати… если вы хотите перейти от Таблиц к BigQuery, ознакомьтесь с нашими рецептами быстрого запуска BigQuery на странице query.recipes.

 

1. Основы запросов Google Таблиц

ЗАПРОС сочетает в себе все возможности арифметических функций (СУММ, СЧЕТ, СРЗНАЧ) с фильтрующими возможностями такой функции, как ФИЛЬТР.

 

В этом примере мы рассмотрим несколько примеров QUERY, чтобы проанализировать выборку данных Twitter.

Основная структура ЗАПРОСА состоит в том, чтобы ввести:

• Диапазон данных – «вкладка данных»!A:C’
• И строка запроса — «выберите A, где C = ‘twitter’»

Мы объединяем их следующим образом:

=query( ‘вкладка данных’!A:C, «выберите A, где C = ‘twitter’»)

 

Пример функции QUERY для ответа на вопросы

Q: Каково было среднее количество ретвитов из выборки?

A: =запрос(D1:G, «выбрать среднее(G)»)

 

В: Каково среднее количество ретвитов в твитах, содержащих @упоминание, но не являющихся ретвитами?

A: =query(D1:G, «выберите avg(G), где E содержит ‘@’, а E не содержит ‘RT'»)

 

Q: Сколько ретвитов получил каждый пользователь в списке ?

A: =запрос(D1:G, «выберите F, sum(G) где E != » сгруппировать по метке F sum(G) ‘всего ретвитов'»)

2.

Запись сравнения дат в запросах

Почему такие сложные запросы в Таблицах — разве это не должно быть проще?

 

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

К сожалению, нигде в документации Google Sheets об этом не упоминается, так что мне пришлось разбираться самому.

Вы должны правильно сделать две вещи:

1. Написать строку «дата» перед фактической датой, чтобы заявить о своих намерениях
2. Введите дату в формате «гггг-мм-дд»

Если вы хотите динамически сравнить дату из другой ячейки, это немного другая история. Мне нравится сохранять диапазоны дат (последние 7 дней, последние 30 дней, текущий год и т. д.), а затем ссылаться на них в запросе, подобном этому:

Обратите внимание на синтаксис:

1. Чтобы объединить строку с внешним значением , закройте запрос в двойных кавычках и используйте ‘&’
2. Используйте функцию ТЕКСТ для преобразования даты в правильный формат «гггг-мм-дд»

Как только вы это освоите, вы больше никогда об этом не вспомните. Но если вы когда-нибудь забудете синтаксис, вам поможет шпаргалка CIFL.

3. Отладка распространенных ошибок запросов

Об отладке распространенных ошибок запросов:

 

Ошибки никогда не доставляют удовольствия, но сообщения об ошибках Таблиц всегда укажут вам правильное направление.

#REF

1) Результат массива не был расширен, поскольку это привело бы к перезаписи данных в ячейке D6

Данные в ячейке D6 блокируют расширение вашего запроса — удалите его!

 

2) Неразрешенное имя листа…

Лист, на который вы ссылаетесь в своем запросе, не существует — вероятно, из-за опечатки или из-за его удаления :(.

 

#VALUE

3) PARSE_ERROR

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

=query(tweets!A1:D,’select C, sum(D), где D >= 0 group by C limit 2′,0)

 

4) LABEL_COL_NOT_IN_SELECT

Вызвано несоответствием между столбцами в предложении «выбрать» и предложении «метка»:

=запрос(твиты!A1:D,‘выберите C, sum(D) где D >= 0 сгруппируйте по C limit 2 label sum© «,0)

Здесь sum(D) ошибочно помечен как sum©.

 

5) NO_COLUMN

Когда вы выбираете столбец, который находится за пределами вашего диапазона:

=запрос(твиты!A1:C,’выбрать C, сумма(D) где D >= 0 группа по пределу C 2′,0)

Исправлено путем расширения диапазона данных, включив в него столбец (в данном случае твиты!A1:D).

 

6) ADD_COL_TO_GROUP_BY_OR_AGG

Появляется, когда вы выбираете столбец (в данном случае B), которого нет в предложении «группировать по»:

=query(tweets!A1:D,’select C, B, sum(D ) где D >= 0 сгруппировать по C limit 2′,0)

Если вы не суммируете или не агрегируете столбец B, вам нужно сгруппировать по нему.

 

#Н/Д

7) Запрос завершен с пустым выводом.

Просто означает, что по вашему запросу нет результатов в том виде, в каком он написан в настоящее время, возможно, из-за слишком ограничительного условия:

=query(tweets!A2:D,‘выбрать C, D, где D = 100 предел 2’,0)

Попробуйте упростить запрос, чтобы сделать его менее строгим (удалив D = 100 в данном случае).

4. Как запрашивать несколько таблиц/диапазонов одновременно

При запросе нескольких таблиц Google с помощью importrange + {}:

 

В каждой жизни наступает время, когда вы хотите объединить два данных диапазоны в запросе Google Sheets.

Давайте поговорим о том, как объединить диапазоны данных из одной электронной таблицы (или из двух разных листов) для выполнения одного запроса — работая с примерами, используя образцы данных Twitter.

Чтобы запросить комбинированный диапазон из одного листа:

1. Заключите оба диапазона в { }, чтобы объединить их, и разделите знаком «;», чтобы расположить один поверх другого
2. Вместо того, чтобы ссылаться на столбцы с их буквой (A, B), ссылайтесь на них как Col1, Col2 в зависимости от их порядка

Чтобы запросить комбинированный диапазон из разных листов:

• Сначала запустите функцию importrange на внешнем листе, из которого вы хотите извлечь данные. Первый элемент — это идентификатор электронной таблицы, который содержится в URL-адресе таблицы между /d/ и /edit: (docs.google.com/spreadsheets/d/9).0308 1ekTIZ40VVT9p9kE80wNaiy6Y2xslo4K9KVmmg-Dx654 /изменить).
• Вам будет предложено разрешить доступ к листу.
• Вставьте диапазон из функции importrange в сам запрос.

Честно говоря, моя цель — никогда не объединять диапазоны внутри самого запроса (я предпочитаю выполнять такой тип агрегирования перед написанием запроса), но иногда это неизбежно.

5. Запрос по именам столбцов, а не по буквам

Это избавит вас от головной боли, если несколько коллег работают с вашими таблицами.

 

Запросы очень деликатны: они требуют, чтобы мы указывали точно  буквы столбца ( A, sum(B) ) или числа ( Col1, sum(Col2) ), которые мы хотим вернуть.

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

Объединяя два запроса вместе, мы можем гарантировать, что ваши запросы всегда будут соответствовать одним и тем же заголовкам столбцов:

1. Первый (внутренний) запрос использует функцию Match для поиска номеров столбцов — match(‘header name’, tab!:A1:Z1), предполагая, что ваши заголовки находятся в первой строке. Обратите внимание, что ниже мы хотим заключить наш диапазон {tweets!A2:D} в фигурные скобки, чтобы освободить нас для выбора номеров столбцов (Col1), а не букв (A).
2. Во втором (внешнем) запросе вы строите фактическую логику выбора (выберите Col1, суммируйте (Col2) группу по Col1…), потому что во внутреннем запросе вы уже выбрали нужные столбцы. Вы можете жестко запрограммировать Col1, Col2 и т. д., потому что вы всегда знаете порядок, в котором они будут отображаться, независимо от того, перемещаются ли столбцы на вашем фактическом листе.

Соедините все вместе, и это будет выглядеть так:

Первая строка (внутренний запрос) находит имена ваших столбцов – на самом деле она выполняет запрос «выберите Col3, Col4»

Вторая строка (внешний запрос) выполняет вашу фактическую математику и маркирует ваши столбцы. Помните, что метки столбцов запроса всегда будут отображаться в конце инструкции и в целом выглядят как инструкция SELECT (выберите Col1, sum(Col2) сопоставляется с меткой Col1 «дескриптор», sum(Col2) «всего ретвитов»).

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

6. Построение сводных таблиц с помощью запроса

Отлично подходит для создания отчетов о временных рядах в Таблицах:

 

Если вы работаете с данными временных рядов в Таблицах (как это делает большинство из нас), то сводка в запросах станет вашим новым любимым ходом.

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

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

= запрос (твиты! A1: D, ‘выбрать C, сумма (D) где D >= 0 группировать по C повороту A’, 0)

Как видите, это так же просто, как добавить в запросе «основу А» после предложения «группировать по».

Вы можете использовать раскрывающиеся списки для добавления входных переменных в свой запрос:

 

Когда вы создаете отчет в Google Sheets, главная цель — не заставить людей, *использующих* отчет, думать.

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

Проверка данных в Таблицах автоматически создает выпадающие меню, когда вы их создаете — очень удобно, верно?

И смешать выбранное значение из раскрывающегося меню с запросом очень просто:

= запрос (данные! A: B, ‘выберите A, где B = » & ‘расположение вкладки раскрывающегося списка’! C2 & «‘, 0)

Требуется всего несколько нажатий клавиш:

1. Заключите раскрывающееся значение в одинарные кавычки (‘), если это строка (нет, если это число)
2. Закройте (а затем снова откройте) ваш запрос в двойных кавычках («)
3. Используйте амперсанд (&), чтобы добавить раскрывающееся значение в строку запроса
4. Ссылка на фактическую ячейку раскрывающегося списка («данные»! A2), чтобы добавить ее в строку запроса

Вот оно! Пользователи вашего отчета будут вам благодарны.

8. Разница между SQL и Query

В чем разница между Sheets Query и SQL?

 

Если вы привыкли писать SQL-запросы, поначалу вам может быть неприятно работать с запросами Google Sheets (и наоборот).

Если вы еще не знаете SQL, сначала изучите Query. Это сделает работу с базами данных *намного* проще, когда вы туда доберетесь.

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

 

Базовые запросы

SQL:  SELECT * FROM table WHERE имя_столбца = ‘xyz’ ORDER BY имя_столбца desc LIMIT 10

= ‘xyz’ ORDER BY A desc LIMIT 10′)

Базовый синтаксис запроса примерно такой же. Запросы Google Sheets используют один и тот же оператор SELECT для выбора столбцов, WHERE / AND / OR для установки логики, ORDER BY для упорядочения результатов и LIMIT для извлечения только определенного количества результатов (см. полный список языковых предложений в документах Google ). ).

Ключевое отличие состоит в том, что в запросе Sheets нет инструкции FROM — вместо ссылки на таблицу вы ссылаетесь на диапазон ячеек (в данном примере — tab!A:D).

Кроме того, вы заметите, что вместо ссылок на имена столбцов (как в SQL) в запросах Таблиц вы ссылаетесь на столбцы по их букве (A, B, C и т. д.) или по их порядку (Col1, Col2, Col3) .

 

Выполнение математических операций со столбцами

SQL: SELECT имя_столбца, сумма(имя_другого_столбца) ИЗ таблицы GROUP BY имя_столбца

) GROUP BY A’)

Те же функции агрегации avg(), count(), sum(), max(), min(), которые вы привыкли использовать в SQL, доступны в запросах Sheets, как и базовые + , -, *, / арифметические функции.

 

Маркировка столбцов

SQL:  SELECT column_name AS ‘label’ FROM table

Sheets:  =query( ‘tab’!A:D, ​​’SELECT A, B LABEL A ‘label1’, B ‘label2’ ‘)

В SQL, для метки столбец, вы просто добавляете «AS» к оператору «SELECT»: имя_столбца AS «метка».

В Sheets это делается в конце запроса с помощью оператора «метка».

Пометка столбца sum(A) как пустого приводит к удалению автоматического заголовка sum() из отображения, возвращая чистую единственную метрику.

 

Сравнение дат

SQL:         SELECT column_name FROM table WHERE date_column > ’22/8/2016′

Sheets:  =query( ‘tab’!A:D, ​​’SELECT A WHERE -08-22′ ‘)

Даты в гугл-запросах сложно понять с первого раза, если вы привыкли работать в SQL.

В отличие от SQL, который может обрабатывать даты в нескольких форматах («20160826», «26.08.2016», «2016-08-26»), Таблицы могут принимать даты только в формате «гггг-мм-дд». иногда приходится использовать функцию ТЕКСТ, чтобы преобразовать дату, например 26.08.2016, в 2016-08-26.

В Таблицах вы также должны указать, что вы сравниваете дату, добавив фразу «дата» перед строкой даты.

 

Нечеткое совпадение фраз

SQL: SELECT column_name FROM table WHERE column_name LIKE ‘%phrase%’ ‘ ‘)

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

Однако добавлено одно замечание: если вы хотите добавить параметр «не содержит», вам нужно поместить «не» перед буквой столбца.

 

Объединение таблиц

SQL:  ВЫБЕРИТЕ a.column_name, b.column_name FROM table1 a, table2 b WHERE a.column_name = b.column_name

Листы: 9000 ???4

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

Команда Google Sheets, если вы читаете это — помогите!

 

Соответствие из списка вариантов

SQL: SELECT column_name FROM table WHERE column_name IN (‘match2’, ‘match3’, ‘match4’)

Sheets: ‘t=ab’A!A :D, ‘ВЫБЕРИТЕ ГДЕ A = ‘match2’ ИЛИ ​​A = ‘match3’ ИЛИ ​​A = ‘match4’ ‘)

К сожалению, в Google Sheets нет такого понятия, как фраза IN в SQL, поэтому вам нужно перечислите значения, которые вы хотите сопоставить, одно за другим.

9. Вложение запросов вместе, как в SQL

При некотором хитром подходе вы можете аппроксимировать функцию вложения SQL в Sheets:

 

недостаток.

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

Я думал, что это невозможно. Я ошибался.

Это хакерский способ имитации вложенности SQL-запросов, но он работает.

Вы объединяете формулу массива и конкатенацию, чтобы получить длинную строку в вашем запросе (ИЛИ A = «значение1» ИЛИ A = «значение2» и т. д.).

Окончательная формула выглядит так:

=query(tab1!A:Z,’выберите A,sum(B) где A != ‘ and (B = ‘blah’ ‘ & concatenate(arrayformula(‘ OR B = “ & tab2!C2:C & “’)) & ‘) group by B’,0)

Это берет все значения, присутствующие на вкладке 2, столбце C, и ищет их в столбце B на вкладке 1.

This entry was posted in Ключевые слова