<стр>Узнайте, как использовать чат-ботов с искусственным интеллектом, таких как ChatGPT, для выполнения сложных запросов BigQuery для SEO-отчетов. Повысьте эффективность принятия стратегических решений и сократите количество ручных задач.
ИИ формирует каждую область, делая доступными каждому навыки (такие как программирование или визуализация данных), которые были недоступны в прошлом.
<п>Оператор ИИ, умеющий использовать правильные подсказки, может выполнять задачи низкого и среднего уровня сложности, позволяя больше сосредоточиться на принятии стратегических решений. <стр>В этом руководстве мы шаг за шагом покажем вам, как использовать чат-ботов с искусственным интеллектом с ChatGPT в качестве примера для выполнения сложных запросов BigQuery для ваших нужд SEO-отчетов.
Мы рассмотрим два примера:
Это также даст вам общее представление о том, как можно использовать чат-ботов, чтобы снизить нагрузку при создании отчетов по SEO.
Зачем вам изучать BigQuery?
<п>Инструменты SEO, такие как Google Search Console или Google Analytics 4, имеют доступные пользовательские интерфейсы, которые вы можете использовать для доступа к данным. Но часто они ограничивают ваши возможности и показывают неполные данные, что обычно называется выборкой данных.
В GSC это происходит потому, что инструмент исключает анонимные запросы и ограничивает количество строк таблицы до 1000 строк.
Скриншот из Google Search Console, май 2024 г. <п>Используя BigQuery, вы можете решить эту проблему и создавать любые сложные отчеты, которые вам нужны, устраняя проблему выборки данных, которая довольно часто возникает при работе с большими веб-сайтами.
(В качестве альтернативы вы можете попробовать использовать Looker Studio, но цель этой статьи — проиллюстрировать, как вы можете использовать ChatGPT для BigQuery.)
В этой статье мы предполагаем, что вы уже подключили свои учетные записи GSC и GA4 к BigQuery. Если вы еще этого не сделали, возможно, вы захотите ознакомиться с нашими руководствами, как это сделать:
Основы SQL
Если вы знаете язык структурированных запросов (SQL), вы можете пропустить этот раздел. Но для тех, кто этого не делает, вот краткий справочник по операторам SQL:
<таблица> <тело> <тр>
Заявление
Описание
<тр>
SELECT
Извлекает данные из таблиц
<тр>
INSERT
<д>Вставляет новые данные в таблицу
<тр>
UNNEST
Сглаживает массив в набор строк
<тр>
ОБНОВЛЕНИЕ
Обновляет существующие данные в таблице
<тр>
DELETE
Удаляет данные из таблицы
<тр>
CREATE
Создает новую таблицу или базу данных
<тр> <д>ВОЗРАСТ
Изменяет существующую таблицу
<тр>
DROP
Удаляет таблицу или базу данных.
<стр>Условия, которые мы будем использовать, чтобы вы могли ознакомиться:
<тело> <тр>
Условие
Описание
<тр>
WHERE
Фильтрует записи по определенным условиям
<тр>
AND
Объединяет два или более условий, при этом все условия должны быть истинными
<тр>
OR
Объединяет два или более условий, из которых хотя бы одно условие должно быть истинным
<тр>
НЕ
Отменяет условие
<тр>
LIKE
Ищет указанный шаблон в столбце.
<тр>
IN
Проверяет, находится ли значение в пределах набора значений
<тр>
МЕЖДУ
Выбрать значения в заданном диапазоне
<тр>
IS NULL
Проверяет наличие нулевых значений
<тр>
IS NOT NULL
Проверяет ненулевые значения
<тр>
EXISTS
Проверяет, возвращает ли подзапрос какие-либо записи
Теперь давайте углубимся в примеры использования BigQuery через ChatGPT.
<х2>1. Как проанализировать снижение трафика из-за воздействия алгоритма Google
<п>Если на вас повлияло обновление алгоритма Google, первое, что вам следует сделать, — это создать отчеты по затронутым страницам и проанализировать, почему это повлияло на вас.
Помните, худшее, что вы можете сделать, это начать сразу что-то менять на сайте в панике. Это может вызвать колебания поискового трафика и еще больше усложнить анализ влияния.
<п>Если у вас меньше страниц в индексе, вы можете счесть использование данных пользовательского интерфейса GSC удовлетворительным для анализа ваших данных, но если у вас десятки тысяч страниц, это не позволит вам экспортировать более 1000 строк (страниц или запросов) данные.
Предположим, у вас есть данные за неделю, поскольку обновление алгоритма завершилось, и вы хотите сравнить их с данными за предыдущую неделю. Чтобы запустить этот отчет в BigQuery, вы можете начать с этой простой подсказки:
<п>Представьте, что вы аналитик данных с опытом работы с Google Analytics 4 (GA4), Google Search Console, SQL и BigQuery. Ваша задача — сгенерировать SQL-запрос для сравнения данных WEB-консоли поиска за периоды с «2024-05-08» по «2024-05-20» и с «2024-04-18» по «2024-04-30». . Извлеките общее количество кликов, показов и среднюю позицию для каждого URL-адреса за каждый период. Дополнительно посчитайте разницу этих показателей между периодами для каждого URL. (где средняя позиция рассчитывается как сумма позиций, деленная на сумму показов). Подробности: Название проекта BigQuery: use_your_bigquery_projectname. Имя набора данных: searchconsole Имя таблицы: searchdata_url_impression. Укажите SQL-запрос, соответствующий этим требованиям.
Как только вы получите код SQL, скопируйте и вставьте его в редактор BigQuery SQL, но я готов поспорить, что исходный код, который вы получите, будет содержать ошибки. Например, имена столбцов таблицы могут не совпадать с именами в вашем наборе данных BigQuery.
Ошибка в BigQuery SQL, когда имя столбца не соответствует столбцу набора данных.
Подобные вещи случаются довольно часто при выполнении задач по кодированию через ChatGPT. Теперь давайте углубимся в то, как можно быстро решить подобные проблемы.
Просто щелкните набор данных на левой и правой панели, выберите все столбцы справа и нажмите Копировать как таблицу.
Как выбрать все столбцы таблицы в BigQuery.
Как только он у вас появится, просто скопируйте и вставьте его в качестве дополнительной подсказки и нажмите enter.
Сгенерируйте SQL еще раз, принимая во внимание, что моя схема таблицы выглядит следующим образом: “описание типа режима полного имени data_date ДАТА URL_сайта STRING URL STRING Строка запроса is_anonymized_query Логическое значение is_anonymized_discover BOOLEAN страна STRING тип поиска STRING устройство STRING is_amp_top_stories Логическое значение is_amp_blue_link BOOLEAN is_job_listing BOOLEAN is_job_details BOOLEAN is_tpf_qa БУЛЕВОЕ значение is_tpf_faq BOOLEAN is_tpf_howto BOOLEAN is_weblite BOOLEAN is_action BOOLEAN is_events_listing BOOLEAN is_events_details BOOLEAN is_search_appearance_android_app Логическое значение is_amp_story БУЛЕВОЕ значение is_amp_image_result BOOLEAN is_video BOOLEAN is_organic_shopping BOOLEAN is_review_snippet Логическое значение is_special_announcement BOOLEAN is_recipe_feature Логическое значение is_recipe_rich_snippet BOOLEAN is_subscribed_content BOOLEAN is_page_experience Логическое выражение is_practice_problems BOOLEAN is_math_solvers BOOLEAN is_translated_result BOOLEAN is_edu_q_and_a BOOLEAN показы ЦЕЛОЕ ЧИСЛО нажимает ЦЕЛОЕ ЧИСЛО sum_position ЦЕЛОЕ ЧИСЛО is_product_snippets BOOLEAN is_merchant_listings BOOLEAN is_learning_videos БУЛЕВОЕ ”
Это исправит сгенерированный SQL в соответствии со структурой таблицы GSC следующим образом:
<п>С периодом1 AS ( ВЫБИРАТЬ URL, СУММА(кликов) КАК кликов, СУММА(показы) КАК показов, IFNULL(SUM(сумма_позиция)/NULLIF(SUM(показы), 0), 0) AS avg_position ОТ `use_your_bigquery_projectname.searchconsole.searchdata_url_impression` ГДЕ data_date МЕЖДУ '2024-05-08' И '2024-05-20' И тип поиска = 'ВЕБ' ГРУППА ПО URL ), период2 AS ( ВЫБИРАТЬ URL, СУММА(кликов) КАК кликов, СУММА(показов) КАК показов, IFNULL(SUM(сумма_позиция)/NULLIF(SUM(показы), 0), 0) AS avg_position ОТ `use_your_bigquery_projectname.searchconsole.searchdata_url_impression` ГДЕ data_date МЕЖДУ '2024-04-18' И '2024-04-30' И тип_поиска = 'ВЕБ' ГРУППА ПО URL ), сравнение AS ( ВЫБИРАТЬ COALESCE(p1.url, p2.url) КАК URL, IFNULL(p1.clicks, 0) AS clicks_ period1, IFNULL(p2.clicks, 0) AS clicks_ period2, IFNULL(p1.impressions, 0) AS показы_период1, IFNULL(p2.impressions, 0) AS показы_период2, IFNULL(p1.avg_position, 0) AS avg_position_ period1, IFNULL(p2.avg_position, 0) AS avg_position_ period2, IFNULL(p1.clicks, 0) – IFNULL(p2.clicks, 0) AS clicks_diff, IFNULL(p1.impressions, 0) – IFNULL(p2.impressions, 0) AS messages_diff, IFNULL(p1.avg_position, 0) – IFNULL(p2.avg_position, 0) AS avg_position_diff ОТ период1 п1 ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ период2 п2 НА p1.url = p2.url ) ВЫБИРАТЬ URL, клики_период1, клики_период2, клики_diff, показы_период1, показы_период2, показы_разница, avg_position_ period1, avg_position_ period2, avg_position_diff ОТ сравнение СОРТИРОВАТЬ ПО URL; <п>Теперь запустите его и наслаждайтесь данными, экспортировав их в CSV или Google Таблицы.
Как запустить SQL в BigQuery.
Если у вас миллионы URL-адресов, возможно, вы не сможете работать в Google Sheets или экспортировать CSV, поскольку данные слишком велики. Кроме того, эти приложения имеют ограничения на количество строк, которые можно иметь в одном документе. В этом случае вы можете сохранить результаты в виде таблицы BigQuery и подключиться к ней с помощью Looker Studio для просмотра данных.
Но помните, что BigQuery — это бесплатный сервис. Бесплатно предоставляется до 1 ТБ обработанных данных запросов в месяц. Как только вы превысите этот лимит, с вашей кредитной карты будет автоматически списана сумма в зависимости от вашего использования.
Это означает, что если вы подключите BigQuery к Looker Studio и просматриваете там свои данные, они будут учитываться при выставлении счетов каждый раз, когда вы открываете панель управления Looker.
Вот почему, когда экспорт содержит несколько десятков или сотен тысяч строк, мне нравится использовать Google Таблицы. Я могу легко подключить его к Looker Studio для визуализации и смешивания данных, и это не будет учитываться в моем счете.
<п>Если у вас есть ChatGPT Plus, вы можете просто использовать созданный мной собственный GPT, который учитывает схемы таблиц для GA4 и Search Console. В приведенном выше руководстве я предполагал, что вы используете бесплатную версию, и оно иллюстрировало, как вы можете использовать ChatGPT в целом для запуска BigQuery.
Если вы хотите узнать, что находится в этом пользовательском теге GPT, вот скриншот серверной части.
Пользовательский GPT со схемами таблиц BigQuery. <п>Ничего сложного – вам просто нужно скопировать таблицы из BigQuery в формате JSON на шаге, описанном выше, и загрузить их в пользовательский GPT, чтобы он мог ссылаться на структуру таблицы. Кроме того, существует подсказка, которая просит GPT ссылаться на прикрепленные файлы JSON при составлении запросов.
Это еще одна иллюстрация того, как можно использовать ChatGPT для более эффективного выполнения задач, устраняя повторяющиеся задачи.
Если вам нужно работать с другим набором данных (отличным от GA4 или GSC) и вы не знаете SQL, вы можете загрузить схему таблицы из BigQuery в ChatGPT и составить SQL-коды, специфичные для этой структуры таблицы. . Легко, не так ли?
<п>В качестве домашнего задания я предлагаю вам проанализировать, на какие запросы повлияли обзоры ИИ.
В таблице Google Search Console нет дифференциатора, позволяющего сделать это, но вы можете запустить запрос, чтобы узнать, какие страницы не потеряли рейтинг, но имели значительное снижение CTR после 14 мая 2024 г. когда Google представил обзоры ИИ.
Вы можете сравнить двухнедельный период после 14 мая с двумя неделями ранее. По-прежнему существует вероятность того, что падение CTR произошло из-за других функций поиска, например, когда конкурент получил избранный фрагмент, но вы должны найти достаточно достоверных случаев, когда на ваши клики влияли обзоры AI (ранее Search Generative Experience или “SGE&). ;rdquo;).
<х2>2. Как объединить данные поискового трафика с показателями вовлеченности из GA4
При анализе поискового трафика очень важно понимать, насколько пользователи взаимодействуют с контентом, поскольку сигналы вовлеченности пользователей являются факторами ранжирования. Обратите внимание: я не имею в виду точные показатели, определенные в GA4.
Однако показатели вовлеченности GA4 – например, «среднее время взаимодействия за сеанс», ” это среднее время, в течение которого ваш веб-сайт находился в фокусе внимания браузера пользователя – может намекать на то, достаточно ли хороши ваши статьи для чтения пользователями.
<п>Если оно слишком низкое, это означает, что на страницах вашего блога могут быть проблемы, и пользователи их не читают.
Если вы объедините этот показатель с данными Search Console, вы можете обнаружить, что страницы с низким рейтингом также имеют низкое среднее время взаимодействия за сеанс.
Обратите внимание, что у GA4 и GSC разные модели источников. GA4 использует модель атрибуции по последнему клику, что означает, что если кто-то заходит из Google на страницу статьи один раз, а затем возвращается напрямую еще два раза, GA4 может отнести все три посещения к Google, тогда как GSC сообщит только об одном.
Итак, он не на 100% точен и может не подходить для корпоративной отчетности, но наличие показателей вовлеченности из GA4 наряду с данными GSC дает ценную информацию для анализа вашего рейтинга. корреляции с вовлеченностью.
Использование ChatGPT с BigQuery требует небольшой подготовки. Прежде чем мы перейдем к подсказке, я предлагаю вам прочитать, как структурированы таблицы GA4, поскольку они не так просты, как таблицы GSC.
У него есть столбец event_params, который имеет тип записи и содержит такие измерения, как page_location, ga_session_id и Engagement_time_msec. Он отслеживает, как долго пользователь активно взаимодействует с вашим сайтом.
ключ_event_params Engagement_time_msec — это не общее время на сайте, а время, потраченное на определенные взаимодействия (например, нажатие или прокрутку), когда каждое взаимодействие добавляет новый фрагмент времени взаимодействия. Это похоже на сложение всех маленьких моментов, когда пользователи активно используют ваш сайт или приложение.
Следовательно, если мы суммируем этот показатель и усредняем его по сеансам для страниц, мы получаем среднее время взаимодействия за сеанс.
Теперь, когда вы поймете engagement_time_msec , давайте попросим ChatGPT помочь нам построить запрос, который извлекает GA4 “среднее время взаимодействия за сеанс& ;rdquo; для каждого URL-адреса и объединяет их с данными эффективности поиска статей GSC.
Подсказка, которую я бы использовал:
<п>Представьте, что вы аналитик данных с опытом работы с Google Analytics 4 (GA4), Google Search Console, SQL и BigQuery. Составьте SQL-запрос, который извлекает из консоли поиска Google следующие данные для каждого URL-адреса за предыдущие 7 дней, исключая текущий день: 1. Щелчки, 2. Впечатления, 3. Средняя позиция (рассчитывается как сумма позиций, деленная на сумму показов). Из таблицы BigQuery GA4 необходимо удалить вложение из event_params ga_session_id, Engagement_time_msec и page_location. Выбирайте только строки, для которых значение Engage_time_msec установлено как ненулевое, группируйте все сеансы с одинаковыми идентификаторами, page_location и SUM Engagement_time_msec и делите на SUM числа сеансов. Объедините данные GA4 и Google Search Console по URL-адресам за один и тот же период. Дополнительно оптимизируйте запрос, чтобы он извлекал данные из разделов таблицы GA4, а не запрашивал всю таблицу. Подробности: 1. Название проекта BigQuery: use_your_bigquery_projectname. 2. Имя набора данных GA4: use_your_ga4_bigquery_dataset_name. 3. Имя таблицы GA4: use_your_ga4_bigquery_table_name_under_dataset. 3. Имя набора данных Google Search Console: use_your_gsc_bigquery_table_name_under_dataset. 3. Имя таблицы Google Search Console: use_your_gsc_bigquery_table_name_under_dataset. 4. Вот схемы таблиц BigQuery для GA4: [скопируйте схему таблицы здесь] 5. Вот схемы таблиц BigQuery для консоли поиска Google: [скопируйте схему таблицы здесь]
Как только я скопировал и вставил в BigQuery, он дал мне результаты с “средним временем взаимодействия за сеанс” все нули. Итак, очевидно, что ChatGPT нуждается в большем контексте и руководствах по работе GA4.
<п>Я помог предоставить дополнительную информацию в качестве дополнительного вопроса из официальной документации GA4 о том, как она рассчитывает Engagement_time_msec. Я оперативно скопировал и вставил документ в ответ и попросил использовать эти знания при составлении запроса, что помогло. (Если вы получили какую-либо синтаксическую ошибку, просто скопируйте/вставьте ее в качестве дополнительного вопроса и попросите исправить ее.)
В результате 15 минут усилий я получил правильный SQL:
<п>С — Шаг 1. Отфильтруйте данные GA4 за последние 7 дней и отключите event_params. ga4_data AS ( ВЫБИРАТЬ Дата события, event_timestamp, (ВЫБЕРИТЕ значение.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id, (ВЫБЕРИТЕ значение.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS Engagement_time_msec, (ВЫБЕРИТЕ значение.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location ОТ `your_bq_project_name.your_bq_ga4_dataset.events_*` ГДЕ _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY)) И FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), ИНТЕРВАЛ 1 ДЕНЬ)) И (ВЫБЕРИТЕ значение.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') НЕ НУЛЕВОЕ ), — Шаг 2. Объедините данные GA4 по идентификатору сеанса и местоположению страницы. агрегированные_ga4_data AS ( ВЫБИРАТЬ расположение_страницы, SUM(engagement_time_msec/1000)/NULLIF(COUNT(DISTINCT ga_session_id), 0) AS avg_engagement_time_msec ОТ ga4_data ГРУППА ПО расположение_страницы ), — Шаг 3. Отфильтруйте данные GSC за последние 7 дней и выберите URL-адреса, по которым были клики. gsc_data AS ( ВЫБИРАТЬ URL, СУММА(кликов) КАК кликов, СУММА(показов) КАК показов, SUM(сумма_позиция)/SUM(показы) AS avg_position ОТ `your_bq_project_name.searchconsole.searchdata_url_impression` ГДЕ data_date МЕЖДУ DATE_SUB(CURRENT_DATE(), ИНТЕРВАЛ 8 ДНЕЙ) И DATE_SUB(CURRENT_DATE(), ИНТЕРВАЛ 1 ДЕНЬ) и нажимает > 0 ГРУППА ПО URL ) — Объединение данных Google Search Console с данными GA4 по page_location и URL. ВЫБИРАТЬ gsc.url, gsc.клики, gsc.показы, gsc.avg_position, ga4.avg_engagement_time_msec ОТ gsc_data AS gsc ЛЕВОЕ ПРИСОЕДИНЕНИЕ агрегированные_ga4_data AS ga4 НА gsc.url = ga4.page_location СОРТИРОВАТЬ ПО gsc.clicks DESC;
Это извлекает данные GSC с показателями взаимодействия из GA4.
Search Console объединила данные с GA4
Обратите внимание, что вы можете заметить расхождения между числами в пользовательском интерфейсе GA4 и данными, запрошенными из таблиц BigQuery.
Это происходит потому, что GA4 фокусируется на “Активных пользователях” и группирует редкие точки данных в “(other)” категория, а BigQuery показывает все необработанные данные. GA4 также использует смоделированные данные для выявления пробелов в случае отсутствия согласия, чего BigQuery не включает.
<п>Кроме того, GA4 может собирать данные для более быстрых отчетов, тогда как BigQuery включает все данные. Эти варианты означают, что GA4 предлагает краткий обзор, а BigQuery предоставляет подробный анализ. Более подробное объяснение того, почему это происходит, вы найдете в этой статье.
Возможно, вы можете попробовать изменить запросы, включив в них только активных пользователей, чтобы на шаг приблизить результаты к пользовательскому интерфейсу GA4.
В качестве альтернативы вы можете использовать Looker Studio для смешивания данных, но у него есть ограничения при работе с очень большими наборами данных. BigQuery предлагает масштабируемость за счет эффективной обработки терабайтов данных, что делает его идеальным для крупномасштабных SEO-отчетов и детального анализа.
Его расширенные возможности SQL позволяют выполнять сложные запросы для получения более глубокой информации, с которой не могут справиться Looker Studio или другие инструменты создания информационных панелей.
<ч2>Заключение
Использование возможностей кодирования ChatGPT для составления запросов BigQuery для ваших нужд отчетности поднимает вас на новый уровень и открывает новые горизонты, где вы можете комбинировать несколько источников данных.
<стр>Это демонстрирует, как ChatGPT может упростить сложные задачи анализа данных, позволяя вам сосредоточиться на принятии стратегических решений. <стр>В то же время эти примеры научили нас тому, что людям абсолютно необходимо управлять чат-ботами с искусственным интеллектом, потому что они могут галлюцинировать или давать неверные ответы.
