Узнайте, как использовать чат-боты искусственного интеллекта, такие как ChatGPT, для выполнения сложных запросов BigQuery для создания отчетов о поисковой оптимизации. Улучшите процесс принятия стратегических решений и уменьшите ручные задачи.
ШИ формирует каждую сферу, делая доступными для всех навыки (например, программирование или визуализация данных), которые были недоступны в прошлом.
Оператор искусственного интеллекта, который может выполнять правильные подсказки, может выполнять задачи низкого и среднего уровня сложности, позволяя больше сосредоточиться на принятии стратегических решений.
В этом руководстве мы шаг за шагом расскажем вам, как использовать чат-боты искусственного интеллекта с 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:
<стол> <тела>
Условия, которые мы будем использовать, чтобы вы могли ознакомиться:
<таблица style="width: 99,5085%;"> <тела>
Теперь давайте посмотрим на примеры того, как можно использовать BigQuery через ChatGPT.
1. Как проанализировать снижение трафика через влияние алгоритма Google
Если на вас повлияло обновление алгоритма Google, первое, что вам следует сделать, это создать отчеты о затронутых страницах и проанализировать, почему это повлияло на вас.
Помните, что самое худшее, что вы можете сделать, это начать что-либо изменять на веб-сайте сразу в режиме паники. Это может привести к колебаниям поискового трафика и сделать анализ влияния еще сложнее.
Если у вас меньше страниц в индексе, использование данных интерфейса GSC может быть удовлетворительным для анализа ваших данных, но если у вас десятки тысяч страниц, вы не сможете экспортировать более 1000 строк (страниц или запросов). ) данных.
Скажем, у вас есть данные через неделю после того, как завершилось внедрение обновления алгоритма, и вы хотите сравнить их с данными за предыдущую неделю. Чтобы запустить этот отчет в BigQuery, вы можете начать с этой простой подсказки:
Представьте, что вы аналитик данных и имеете опыт работы с Google Analytics 4 (GA4), Google Search Console, SQL и BigQuery. Ваша задача – создать SQL-запрос для сравнения данных Search Console «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, скопируйте и вставьте его в редактор SQL BigQuery, но я уверен, что исходный код, который вы получите, будет иметь ошибки. Например, названия столбцов таблицы могут не совпадать с набором данных BigQuery.
Ошибка в BigQuery SQL, когда название столбца’не соответствует столбцу набора данных.
Подобные вещи встречаются довольно часто при выполнении задач кодирования через ChatGPT. Теперь рассмотрим, как можно быстро исправить подобные проблемы.
Просто нажмите свой набор данных на панели влево и вправо, выберите все столбцы вправо и нажмите Копировать как таблицу.
Как выбрать все столбцы таблицы в BigQuery.
Получив его, просто скопируйте и вставьте его в качестве подсказки и нажмите enter.
Снова сгенерируйте SQL, учитывая, что моя схема таблицы такова: описание типа полного имени режима дата_дата DATE site_url STRING URL-адрес STRING запрос STRING is_anonymized_query BOOLEAN is_anonymized_discover BOOLEAN страна STRING тип_поиска STRING устройство STRING is_amp_top_stories BOOLEAN is_amp_blue_link BOOLEAN is_job_listing BOOLEAN is_job_details BOOLEAN is_tpf_qa BOOLEAN 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 BOOLEAN is_amp_story BOOLEAN is_amp_image_result BOOLEAN is_video BOOLEAN is_organic_shopping BOOLEAN is_review_snippet BOOLEAN is_special_announcement BOOLEAN is_recipe_feature BOOLEAN is_recipe_rich_snippet BOOLEAN is_subscribed_content BOOLEAN is_page_experience BOOLEAN is_practice_problems BOOLEAN is_math_solvers BOOLEAN is_translated_result BOOLEAN is_edu_q_and_a BOOLEAN показов INTEGER щелкает INTEGER позиция суммы ЦЕЛОЕ ЧИСЛО is_product_nippets BOOLEAN is_merchant_listings BOOLEAN is_learning_videos BOOLEAN ”
Это исправит сгенерированный SQL в соответствии со структурой таблицы GSC следующим образом:
С периодом1 AS ( ВЫБРАТЬ url, SUM(кликов) как кликов, SUM(показы) AS показов, IFNULL(SUM(sum_position)/NULLIF(SUM(impressions), 0), 0) AS avg_position ОТ `use_your_bigquery_projectname.searchconsole.searchdata_url_impression` ГДЕ data_date МЕЖДУ “2024-05-08” И “2024-05-20” AND search_type = 'WEB' ГРУППИРОВАТЬ ЗА url ), период2 AS ( ВЫБРАТЬ url, SUM(кликов) как кликов, SUM(показы) AS показов, IFNULL(SUM(sum_position)/NULLIF(SUM(impressions), 0), 0) AS avg_position ОТ `use_your_bigquery_projectname.searchconsole.searchdata_url_impression` ГДЕ data_date МЕЖДУ «2024-04-18» ТА «2024-04-30» AND search_type = 'WEB' ГРУППИРОВАТЬ ЗА url ), сравнение AS ( ВЫБРАТЬ COALESCE(p1.url, p2.url) AS url, IFNULL(p1.clicks, 0) AS clicks_period1, IFNULL(p2.clicks, 0) AS clicks_period2, IFNULL(p1.показы, 0) AS показы_период1, IFNULL(p2.показы, 0) AS показы_период2, IFNULL(p1.средняя_позиция, 0) AS средняя_позиция_период1, IFNULL(p2.средняя_позиция, 0) AS средняя_позиция_период2, IFNULL(p1.clicks, 0) – IFNULL(p2.clicks, 0) AS clicks_diff, IFNULL(p1.impressions, 0) – IFNULL(p2.impressions, 0) AS разница_показов, IFNULL(p1.avg_position, 0) – IFNULL(p2.avg_position, 0) AS avg_position_diff ОТ период1 p1 ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ период2 p2 ВКЛЮЧЕНО p1.url = p2.url ) ВЫБРАТЬ url, clicks_period1, clicks_period2, clicks_diff, показы_період1, показов_період2, impressions_diff, avg_position_period1, avg_position_period2, avg_position_diff ОТ сравнение СОРТИРОВАТЬ ЗА url;
Теперь запустите его и наслаждайтесь данными, экспортировав их в CSV или Google Таблицы.
Как запустить SQL в BigQuery.
Если у вас есть миллионы URL, возможно, вы не сможете работать в Google Таблицах или экспортировать CSV, поскольку данные слишком велики. Кроме того, эти программы имеют ограничения по количеству строк в одном документе. В таком случае вы можете сохранить результаты как таблицу BigQuery и подключиться к ней с помощью Looker Studio, чтобы просмотреть данные.
Но помните, что BigQuery – это бесплатный сервис. Бесплатно до 1 ТВ обработанных данных запросов в месяц. Как только вы превысите этот предел, с вашей кредитной карты будет автоматически взыскана плата в зависимости от использования.
Это означает, что если вы подключите свой BigQuery к Looker Studio и просматриваете свои данные там, это будет засчитываться к вашему счету каждый раз, когда вы будете открывать информационную панель Looker.
Вот почему, когда экспорт имеет несколько десятков тысяч или сотен тысяч строк, мне нравится использовать Google Sheets. Я могу легко подключить его к 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, специфичные для этой структуры таблицы. Легко, не так ли ?
Как домашнее задание я предлагаю вам проанализировать, на какие запросы повлиял AI Overviews.
В таблице Google Search Console для этого нет различителя, но вы можете выполнить запрос, чтобы узнать, какие страницы не потеряли рейтинг, но имели значительное падение CTR после 14 мая 2024 года, когда Google представил AI Overviews.
Вы можете сравнить двухнедельный период после 14 мая с двумя неделями до этого. Все еще существует вероятность того, что падение CTR произошло из-за других функций поиска, таких как получение конкурентом Featured Snippet, но вы должны найти достаточно действительных случаев, когда на ваши клики повлиял AI Overviews (ранее Search Generative Experience или “SGE& amp;rdquo;).
2. Как объединить данные поискового трафика с показателями привлечения с GA4
При анализе поискового трафика важно понимать, насколько пользователи взаимодействуют с содержимым, поскольку сигналы привлечения пользователей являются факторами ранжирования. Заметьте, что я не имею в виду точные показатели, определенные в GA4.
Однако показатели вовлеченности GA4 &nd; таких как “среднее время взаимодействия за сеанс,” это среднее время, в течение которого ваш веб-сайт был в фокусе браузера пользователя – может намекнуть на то, достаточно ли хороши ваши статьи для чтения пользователями.
Если он слишком низкий, это означает, что ваши страницы блога могут иметь проблему, и пользователи их не читают.
Если вы соедините этот показатель с данными Search Console, вы можете обнаружить, что страницы с низким рейтингом также имеют низкое среднее время взаимодействия за сеанс.
Обратите внимание, что GA4 и GSC имеют разные модели атрибуции источника. GA4 использует модель атрибуции последнего клика, что означает, что если один раз посетить страницу со статьей из Google, а затем вернуться еще два раза, GA4 может отнести все три посещения Google, тогда как GSC сообщит только об одном.
Итак, он не является 100% точным и может не подходить для корпоративной отчетности, но наличие показателей вовлечённости с GA4 вместе с данными GSC предоставляет ценную информацию для анализа вашего рейтинга’ корреляции с привлечением.
Использование ChatGPT из BigQuery требует небольшой подготовки. Прежде чем перейти к подсказке, я предлагаю вам прочитать структуру таблиц GA4, поскольку она не так проста, как таблицы GSC&rsquo.
Он имеет столбец event_params, который имеет тип записи и содержит такие параметры, как page_location, ga_session_id и engagement_time_msec. Он отслеживает, как долго пользователь активно взаимодействует с вашим веб-сайтом.
event_params ключ engagement_time_msec — это не общее время на сайте, а время, затраченное на конкретные взаимодействия (например, нажатие или прокрутка), когда каждая взаимодействие добавляет новую часть времени взаимодействия. Это похоже на добавление всех маленьких моментов, когда пользователи активно используют ваш веб-сайт или приложение.
Итак, если мы суммируем этот показатель и усредняем его по сеансам для страниц, мы получаем среднее время взаимодействия за сеанс.
Теперь, когда вы поймете engagement_time_msec , попросим ChatGPT помочь нам создать запрос, который вытягивает “среднее значение GA4 время взаимодействия за сеанс” для каждого URL-адреса и сочетает его с данными GSC об эффективности поиска статей.
Подсказка, которую я бы использовал:
Представьте, что вы аналитик данных и имеете опыт работы с Google Analytics 4 (GA4), Google Search Console, SQL и BigQuery. Создайте SQL-запрос, который получает следующие данные из Google Search Console для каждого URL-адреса за предыдущие 7 дней, за исключением текущего дня: 1. Клики, 2. Впечатление, 3. Средняя позиция (рассчитывается как сумма позиций поделена на сумму показов). Из таблицы BigQuery GA4 unnest из event_params ga_session_id, engagement_time_msec и page_location. Выберите только те строки, для которых Engament_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 Search Console: [скопировать схему таблицы здесь]
Когда я скопировал и вставил в BigQuery, он дал мне результаты с “средним временем привлечения за сеанс” являясь всеми нулями. Итак, очевидно, ChatGPT требует больше контекста и указаний по работе GA4.
Я помог предоставить дополнительные знания в качестве дополнительного вопроса из официальной документации GA4 о том, как он вычисляет engagement_time_msec. Я скопировал и вставил документ в следующую подсказку и попросил ссылаться на эти знания при создании запроса, который помог. (Если вы получаете любую синтаксическую ошибку, просто скопируйте/вставьте ее как дополнительный вопрос и попросите ее исправить.)
В результате 15 минут усилий я получил правильный SQL:
С — Шаг 1: отфильтруйте данные GA4 за последние 7 дней и удалите event_params ga4_data AS ( ВЫБРАТЬ event_date, event_timestamp, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = &ga;session_id') AS ga_session_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = ŋengagement_time_msec') AS engagement_time_msec, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = &page;page_location') AS page_location ОТ `название_вашого_bq_проекта.ваш_bq_ga4_набор_данных.события_*` ГДЕ _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY)) AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') НЕ ЕСТЬ NULL ), — Шаг 2. Сведите данные GA4 по идентификатору сеанса и расположению страницы aggregated_ga4_data AS ( ВЫБРАТЬ page_location, SUM(engagement_time_msec/1000)/NULLIF(COUNT(DISTINCT ga_session_id), 0) AS avg_engagement_time_msec ОТ ga4_data ГРУППИРОВАТЬ ЗА page_location ), — Шаг 3: отфильтруйте данные GSC за последние 7 дней и выберите URL, которые имели щелчок gsc_data AS ( ВЫБРАТЬ url, SUM(кликов) как кликов, SUM(показы) AS показов, SUM(sum_position)/SUM(impressions) AS avg_position ОТ `ваш_bq_project_name.searchconsole.searchdata_url_impression` ГДЕ data_date МЕЖДУ DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY) И DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) и клики > 0 ГРУППИРОВАТЬ ЗА url ) — Объединение данных Google Search Console с данными GA4 по page_location и url ВЫБРАТЬ gsc.url, gsc.clicks, gsc.impressions, gsc.avg_position, ga4.avg_engagement_time_msec ОТ gsc_data AS gsc ЛЕВОГО ПРИСОЕДИНЕНИЯ aggregated_ga4_data AS ga4 ВКЛЮЧЕНО gsc.url = ga4.page_location СОРТИРОВАТЬ ЗА gsc.clicks DESC;
Это получает данные GSC с показателями привлечения из GA4.
Search Console объединила данные с GA4
Обратите внимание, что вы можете заметить расхождения между числами в интерфейсе GA4 и данными, полученными из таблиц BigQuery.
Это происходит потому, что GA4 сосредотачивается на “Активных пользователях” и группирует редкие точки данных в “(другие)” категория, а BigQuery отображает все необработанные данные. GA4 также использует смоделированные данные для пробелов, когда согласие не предоставляется, а BigQuery не включает.
Кроме того, GA4 может производить выборку данных для более быстрых отчетов, тогда как BigQuery включает все данные. Эти вариации означают, что GA4 предлагает быстрый обзор, в то время как BigQuery обеспечивает детальный анализ. Узнайте больше о том, почему это происходит, в этой статье.
Возможно, вы можете попытаться изменить запросы, чтобы они включали только активных пользователей, чтобы приблизить результаты к интерфейсу пользователя GA4.
В качестве альтернативы вы можете использовать Looker Studio для смешения данных, но она имеет ограничения относительно очень больших наборов данных. BigQuery обеспечивает масштабируемость благодаря эффективной обработке терабайтов данных, что делает его идеальным для крупномасштабных отчетов о поисковой оптимизации и детальном анализе.
Его расширенные возможности SQL позволяют создавать сложные запросы для более глубокой информации, с которыми не могут сравниться Looker Studio или другие инструменты приборной доски.
Вывод
Использование возможностей кодирования ChatGPT’ для создания запросов BigQuery для ваших потребностей в отчетах возвышает вас и открывает новые горизонты, где вы можете совмещать несколько источников данных.
Это демонстрирует, как ChatGPT может оптимизировать сложные задачи анализа данных, позволяя вам сосредоточиться на принятии стратегических решений.
В то же время эти примеры научили нас, что людям совершенно необходимо управлять чат-ботами ИИ, поскольку они могут галлюцинировать или давать неправильные ответы.