نحوه استفاده از ChatGPT برای تحلیل داده‌ BigQuery

وب آنجل, بلاگ, سئو, سئو تکنیکال, هوش مصنوعی
دکتر حامد مهدی‌ زاده
1403-06-05

بلاگ, سئو, سئو تکنیکال, هوش مصنوعی
دکتر حامد مهدی‌ زاده

🔄 تاریخ آخرین به‌روزرسانی: ۱۷ بهمن ۱۴۰۳

درباره این مقاله سوال دارید؟

پاسخ سوالاتت پیش دکتر مهدی‌زاده است؛ همین حالا بپرس!

تماس: 09129175306

هوش مصنوعی، به‌ویژه ابزارهایی مانند ChatGPT، تحولی بزرگ در زمینه‌های مختلف ایجاد کرده است و اکنون مهارت‌های پیچیده را برای همه قابل دسترس‌تر کرده است. در حوزه سئو، استفاده از هوش مصنوعی برای تعامل با BigQuery می‌تواند تحلیل داده‌ها را به‌طور چشمگیری ساده‌تر کند و به شما امکان دهد که به جای انجام کارهای دستی، بر تصمیم‌گیری‌های استراتژیک تمرکز کنید. در این راهنما به شما نشان می‌دهم چگونه با استفاده از ChatGPT، کوئری‌های پیچیده BigQuery را برای گزارش‌های سئو اجرا کنید و مثال‌های عملی ارائه می‌دهم.

چرا باید BigQuery را یاد بگیرید؟

ابزارهایی مانند Google Search Console (GSC) و Google Analytics 4 (GA4) داده‌های ضروری را ارائه می‌دهند، اما اغلب با محدودیت‌هایی مانند نمونه‌برداری داده‌ها و محدودیت‌های تعداد ردیف‌ها روبرو هستند. با استفاده از BigQuery، می‌توانید از این محدودیت‌ها عبور کنید و به مجموعه داده‌های کامل دسترسی پیدا کنید و گزارش‌های دقیق و پیچیده را اجرا کنید.

منظور از BigQuery چیست؟

BigQuery یک سرویس کاملاً مدیریت‌شده و مقیاس‌پذیر از Google Cloud Platform (GCP) است که برای پردازش و تحلیل داده‌های بزرگ (Big Data) طراحی شده است. این سرویس به کاربران اجازه می‌دهد تا داده‌های بسیار حجیم خود را با استفاده از زبان SQL به‌صورت کارآمد و سریع تحلیل کنند.

ویژگی‌های کلیدی BigQuery:

تحلیل سریع و مقیاس‌پذیر: BigQuery قادر است حجم زیادی از داده‌ها را در زمان کوتاهی پردازش کند، که این ویژگی آن را برای شرکت‌ها و سازمان‌هایی که نیاز به تحلیل سریع داده‌های بزرگ دارند، بسیار مناسب می‌کند.
مدیریت بدون نیاز به سرور: BigQuery یک سرویس کاملاً مدیریت‌شده است، به این معنی که کاربران نیازی به مدیریت سرورها یا زیرساخت‌های محاسباتی ندارند. این کار باعث می‌شود که تمرکز بیشتر بر روی تحلیل داده‌ها باشد تا مدیریت زیرساخت.
پرداخت به ازای استفاده: BigQuery بر اساس میزان داده‌هایی که پردازش می‌کنید هزینه دریافت می‌کند، بنابراین شما تنها به اندازه استفاده‌تان پرداخت می‌کنید.
یکپارچگی با دیگر سرویس‌های گوگل: BigQuery به‌راحتی با دیگر ابزارها و سرویس‌های Google مانند Google Analytics، Google Search Console و Google Ads ادغام می‌شود. این یکپارچگی باعث می‌شود که تحلیل داده‌های مختلف از منابع مختلف به‌راحتی امکان‌پذیر باشد.

کاربردهای BigQuery:

تحلیل داده‌های وب‌سایت: با اتصال داده‌های Google Analytics و Google Search Console به BigQuery، می‌توانید تحلیل‌های دقیق‌تر و جامع‌تری را روی داده‌های وب‌سایت خود انجام دهید.
تحلیل داده‌های بازاریابی: با استفاده از BigQuery می‌توانید داده‌های تبلیغاتی خود را تحلیل کرده و بازدهی کمپین‌های بازاریابی خود را بهبود بخشید.
پیش‌بینی و مدل‌سازی: BigQuery به‌خوبی با ابزارهای یادگیری ماشین یکپارچه می‌شود و می‌توانید از آن برای پیش‌بینی روندها و الگوهای آینده استفاده کنید.

به‌طور کلی، BigQuery یک ابزار قدرتمند برای هر کسی است که نیاز به تحلیل داده‌های بزرگ و پیچیده دارد، و با استفاده از آن می‌توانید تصمیم‌گیری‌های داده‌محور بهتری انجام دهید.

مرحله 1: راه‌اندازی محیط

قبل از شروع مثال‌ها، اطمینان حاصل کنید که حساب‌های GSC و GA4 شما به BigQuery متصل شده‌اند. این تنظیمات به شما امکان می‌دهد داده‌های سئو خود را مستقیماً کوئری کنید.

مرحله 2: درک اصول SQL

اگر با SQL آشنا نیستید، در اینجا یک مرور سریع از برخی از دستورات کلیدی SQL که استفاده خواهید کرد، آمده است:

SELECT: داده‌ها را از جداول بازیابی می‌کند.
INSERT: داده‌های جدیدی را به یک جدول اضافه می‌کند.
UPDATE: داده‌های موجود را در یک جدول به‌روزرسانی می‌کند.
DELETE: داده‌ها را از یک جدول حذف می‌کند.
CREATE: یک جدول یا پایگاه داده جدید ایجاد می‌کند.
ALTER: یک جدول موجود را اصلاح می‌کند.
DROP: یک جدول یا پایگاه داده را حذف می‌کند.

# تعریف دوره‌های زمانی
WITH
pre_update AS (
  SELECT
    date,
    SUM(clicks) AS total_clicks
  FROM
    `project.dataset.table`
  WHERE
    date BETWEEN '2024-05-01' AND '2024-05-31'
  GROUP BY
    date
),
post_update AS (
  SELECT
    date,
    SUM(clicks) AS total_clicks
  FROM
    `project.dataset.table`
  WHERE
    date BETWEEN '2024-06-01' AND '2024-06-30'
  GROUP BY
    date
)

# تعریف دوره‌های زمانی

WITH

pre_update AS (

SELECT

date,

SUM(clicks) AS total_clicks

FROM

`project.dataset.table`

WHERE

date BETWEEN '2024-05-01' AND '2024-05-31'

GROUP BY

date

post_update AS (

SELECT

date,

SUM(clicks) AS total_clicks

FROM

`project.dataset.table`

WHERE

date BETWEEN '2024-06-01' AND '2024-06-30'

GROUP BY

date

)

شرایطی که استفاده خواهید کرد:

WHERE: رکوردها را برای شرایط خاص فیلتر می‌کند.
AND: دو یا چند شرط را ترکیب می‌کند که همه باید درست باشند.
OR: دو یا چند شرط را ترکیب می‌کند که حداقل یکی باید درست باشد.
NOT: یک شرط را نفی می‌کند.
LIKE: یک الگوی مشخص را در یک ستون جستجو می‌کند.
IN: بررسی می‌کند که آیا یک مقدار درون یک مجموعه از مقادیر قرار دارد یا نه.
BETWEEN: مقادیر را در یک بازه مشخص انتخاب می‌کند.
IS NULL: مقادیر خالی را بررسی می‌کند.
IS NOT NULL: مقادیر غیرخالی را بررسی می‌کند.
EXISTS: بررسی می‌کند که آیا یک زیرکوئری رکوردی را برمی‌گرداند یا خیر.

مرحله 3: مثال‌های عملی

حالا به مثال‌هایی از نحوه استفاده از BigQuery از طریق ChatGPT بپردازیم.

مثال 1: تحلیل کاهش ترافیک به دلیل تأثیر الگوریتم گوگل

در این مثال، می‌توانید از ChatGPT برای نوشتن یک کوئری استفاده کنید که به شما کمک می‌کند کاهش ترافیک وب‌سایت خود را به تغییرات الگوریتم گوگل مرتبط کنید. ChatGPT می‌تواند به شما کمک کند تا کوئری‌ای بنویسید که ترافیک قبل و بعد از به‌روزرسانی الگوریتم را مقایسه کند.

SELECT 
    DATE,
    SUM(sessions) AS total_sessions
FROM 
    `project_id.dataset_id.ga_sessions_*`
WHERE 
    _TABLE_SUFFIX BETWEEN '20240501' AND '20240630'
GROUP BY 
    DATE
ORDER BY 
    DATE;

SELECT

DATE,

SUM(sessions) AS total_sessions

FROM

`project_id.dataset_id.ga_sessions_*`

WHERE

_TABLE_SUFFIX BETWEEN '20240501' AND '20240630'

GROUP BY

DATE

ORDER BY

DATE;

این کوئری تعداد جلسات (sessions) را برای هر روز از 1 می 2024 تا 30 ژوئن 2024 بر اساس داده‌های Google Analytics که در BigQuery ذخیره شده است، جمع‌آوری می‌کند. شما می‌توانید این داده‌ها را تجزیه و تحلیل کرده و تأثیر به‌روزرسانی الگوریتم را بر ترافیک خود مشاهده کنید.

مثال 2: ترکیب داده‌های ترافیک جستجو با متریک‌های تعامل از GA4

در این مثال، می‌توانید از ChatGPT برای نوشتن کوئری‌ای استفاده کنید که داده‌های ترافیک جستجو از GSC را با داده‌های تعامل کاربران از GA4 ترکیب می‌کند. این کوئری به شما امکان می‌دهد تا ببینید کاربران از کدام صفحات بازدید کرده‌اند و چگونه با آن‌ها تعامل داشته‌اند.

فرض کنید می‌خواهید بفهمید که کاربران از طریق جستجوی ارگانیک وارد کدام صفحات وب‌سایت شما شده‌اند و سپس چگونه با این صفحات تعامل داشته‌اند (مثلاً مدت زمان حضور در صفحه). برای این کار می‌توانید داده‌های Google Search Console و GA4 را ترکیب کنید.

WITH search_traffic AS (
    SELECT
        page_path,
        SUM(clicks) AS total_clicks
    FROM
        `project_id.dataset_id.search_console_data`
    WHERE
        DATE BETWEEN '2024-06-01' AND '2024-06-30'
    GROUP BY
        page_path
),
engagement_data AS (
    SELECT
        page_path,
        AVG(session_duration) AS avg_session_duration
    FROM
        `project_id.dataset_id.ga4_data`
    WHERE
        DATE BETWEEN '2024-06-01' AND '2024-06-30'
    GROUP BY
        page_path
)
SELECT
    s.page_path,
    s.total_clicks,
    e.avg_session_duration
FROM
    search_traffic s
JOIN
    engagement_data e
ON
    s.page_path = e.page_path
ORDER BY
    s.total_clicks DESC;

WITH search_traffic AS (

SELECT

page_path,

SUM(clicks) AS total_clicks

FROM

`project_id.dataset_id.search_console_data`

WHERE

DATE BETWEEN '2024-06-01' AND '2024-06-30'

GROUP BY

page_path

engagement_data AS (

SELECT

page_path,

AVG(session_duration) AS avg_session_duration

FROM

`project_id.dataset_id.ga4_data`

WHERE

DATE BETWEEN '2024-06-01' AND '2024-06-30'

GROUP BY

page_path

)

SELECT

s.page_path,

s.total_clicks,

e.avg_session_duration

FROM

search_traffic s

JOIN

engagement_data e

s.page_path = e.page_path

ORDER BY

s.total_clicks DESC;

این کوئری تعداد کلیک‌های دریافتی از جستجو (از Google Search Console) و میانگین مدت زمان حضور در صفحات (از GA4) را برای هر مسیر صفحه (page_path) ترکیب می‌کند. شما می‌توانید از این اطلاعات برای تحلیل کیفیت و اثربخشی صفحات فرود خود استفاده کنید.

مرحله 4: کاهش بار کارهای دستی

استفاده از ChatGPT برای نوشتن و اجرای کوئری‌های BigQuery می‌تواند به‌طور چشمگیری بار کارهای دستی شما را کاهش دهد و به شما امکان دهد که بر تحلیل‌های استراتژیک‌تر تمرکز کنید.

نحوه تحلیل کاهش ترافیک به دلیل تأثیر الگوریتم گوگل

اگر متوجه شدید که سایت شما تحت تأثیر یک به‌روزرسانی الگوریتم گوگل قرار گرفته، اولین گام مهم، اجرای گزارش‌هایی برای بررسی صفحات تحت تأثیر و تحلیل دلیل این تغییرات است.

به یاد داشته باشید که بدترین کاری که می‌توانید انجام دهید این است که بلافاصله در حالت اضطراب و نگرانی تغییراتی در وب‌سایت خود ایجاد کنید. این کار ممکن است منجر به نوسانات غیرضروری در ترافیک جستجو شود و تحلیل دقیق تأثیرات را دشوارتر کند.

استفاده از Google Search Console (GSC) برای تحلیل داده‌ها: اگر تعداد محدودی صفحه در ایندکس دارید، استفاده از داده‌های رابط کاربری Google Search Console (GSC) ممکن است برای تحلیل داده‌ها کافی باشد. اما اگر سایت شما دارای ده‌ها هزار صفحه است، این ابزار محدودیت‌هایی دارد و بیش از 1000 ردیف داده را برای صفحات یا کوئری‌ها نمی‌تواند استخراج کند.

تحلیل ترافیک با استفاده از BigQuery: فرض کنید یک هفته از داده‌ها پس از پایان به‌روزرسانی الگوریتم در اختیار دارید و قصد دارید این داده‌ها را با هفته قبلی مقایسه کنید. برای اجرای این گزارش در BigQuery، می‌توانید از یک کوئری ساده شروع کنید:

درخواست پیشنهادی:

تصور کنید که شما یک تحلیلگر داده با تجربه در Google Analytics 4 (GA4)، Google Search Console، SQL و BigQuery هستید. وظیفه شما تولید یک کوئری SQL است که داده‌های ‘WEB’ Search Console را برای دوره‌های ‘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 شما مطابقت نداشته باشد، وجود دارد.

برای حل این مشکل، می‌توانید ساختار جدول خود را بررسی کنید:

بر روی دیتاست خود در پنل سمت چپ کلیک کنید.
تمام ستون‌ها را در سمت راست انتخاب کنید.
سپس بر روی “Copy as Table” کلیک کنید.

درخواست جدید:

پس از بررسی ساختار جدول، از ChatGPT بخواهید کوئری SQL جدیدی تولید کند که با ساختار جدول شما سازگار باشد.

این فرآیند به شما کمک می‌کند تا به سرعت کوئری صحیح را تولید و اجرا کنید، و بدین ترتیب تأثیر الگوریتم گوگل بر سایت خود را بهتر تحلیل نمایید.

با اجرای این کوئری و تحلیل نتایج، می‌توانید به شکلی دقیق‌تر دلایل کاهش ترافیک را شناسایی و تصمیمات استراتژیکی برای بهبود وضعیت سایت خود اتخاذ کنید.

این کار کوئری SQL تولید شده را بر اساس ساختار جدول GSC اصلاح می‌کند به این صورت:

WITH period1 AS (
 SELECT 
   url,
   SUM(clicks) AS clicks,
   SUM(impressions) AS impressions,
   IFNULL(SUM(sum_position) / NULLIF(SUM(impressions), 0), 0) AS avg_position
 FROM 
   `use_your_bigquery_projectname.searchconsole.searchdata_url_impression`
 WHERE 
   data_date BETWEEN '2024-05-08' AND '2024-05-20' 
 AND search_type = 'WEB'
 GROUP BY 
   url
), 
period2 AS (
 SELECT 
   url,
   SUM(clicks) AS clicks,
   SUM(impressions) AS impressions,
   IFNULL(SUM(sum_position) / NULLIF(SUM(impressions), 0), 0) AS avg_position
 FROM 
   `use_your_bigquery_projectname.searchconsole.searchdata_url_impression`
 WHERE 
   data_date BETWEEN '2024-04-18' AND '2024-04-30'
 AND search_type = 'WEB'
 GROUP BY 
   url
),
comparison AS (
 SELECT
   COALESCE(p1.url, p2.url) AS url,
   IFNULL(p1.clicks, 0) AS clicks_period1,
   IFNULL(p2.clicks, 0) AS clicks_period2,
   IFNULL(p1.impressions, 0) AS impressions_period1,
   IFNULL(p2.impressions, 0) AS impressions_period2,
   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 impressions_diff,
   IFNULL(p1.avg_position, 0) - IFNULL(p2.avg_position, 0) AS avg_position_diff
 FROM
   period1 p1
 FULL OUTER JOIN
   period2 p2
 ON
   p1.url = p2.url
)
SELECT 
 url,
 clicks_period1,
 clicks_period2,
 clicks_diff,
 impressions_period1,
 impressions_period2,
 impressions_diff,
 avg_position_period1,
 avg_position_period2,
 avg_position_diff
FROM 
 comparison
ORDER BY 
 url;

WITH period1 AS (

SELECT

url,

SUM(clicks) AS clicks,

SUM(impressions) AS impressions,

IFNULL(SUM(sum_position) / NULLIF(SUM(impressions), 0), 0) AS avg_position

FROM

`use_your_bigquery_projectname.searchconsole.searchdata_url_impression`

WHERE

data_date BETWEEN '2024-05-08' AND '2024-05-20'

AND search_type = 'WEB'

GROUP BY

url

period2 AS (

SELECT

url,

SUM(clicks) AS clicks,

SUM(impressions) AS impressions,

IFNULL(SUM(sum_position) / NULLIF(SUM(impressions), 0), 0) AS avg_position

FROM

`use_your_bigquery_projectname.searchconsole.searchdata_url_impression`

WHERE

data_date BETWEEN '2024-04-18' AND '2024-04-30'

AND search_type = 'WEB'

GROUP BY

url

comparison AS (

SELECT

COALESCE(p1.url, p2.url) AS url,

IFNULL(p1.clicks, 0) AS clicks_period1,

IFNULL(p2.clicks, 0) AS clicks_period2,

IFNULL(p1.impressions, 0) AS impressions_period1,

IFNULL(p2.impressions, 0) AS impressions_period2,

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 impressions_diff,

IFNULL(p1.avg_position, 0) - IFNULL(p2.avg_position, 0) AS avg_position_diff

FROM

period1 p1

FULL OUTER JOIN

period2 p2

p1.url = p2.url

)

SELECT

url,

clicks_period1,

clicks_period2,

clicks_diff,

impressions_period1,

impressions_period2,

impressions_diff,

avg_position_period1,

avg_position_period2,

avg_position_diff

FROM

comparison

ORDER BY

url;

اگر میلیون‌ها URL داشته باشید، ممکن است استفاده از Google Sheets یا خروجی CSV مناسب نباشد، زیرا داده‌ها بسیار حجیم هستند. این برنامه‌ها همچنین محدودیت‌هایی در تعداد ردیف‌هایی که می‌توانید در یک سند داشته باشید، دارند. در چنین حالتی، می‌توانید نتایج را به عنوان یک جدول در BigQuery ذخیره کنید و سپس با استفاده از ابزارهایی مانند Looker Studio یا Databox به داده‌ها دسترسی پیدا کنید.

اما به یاد داشته باشید که BigQuery یک سرویس فریمیوم است و تا 1 ترابایت از داده‌های کوئری شده در ماه رایگان است. اگر این حد را رد کنید، هزینه‌ای بر اساس استفاده به صورت خودکار از کارت اعتباری شما کسر می‌شود.

این بدان معناست که اگر BigQuery را به Looker Studio متصل کنید و داده‌های خود را در آنجا مرور کنید، هر بار که داشبورد Looker خود را باز می‌کنید، ممکن است هزینه‌هایی برای شما داشته باشد.

به همین دلیل، زمانی که خروجی داده‌ها فقط چند ده هزار یا حتی صدها هزار ردیف دارد، من از Google Sheets استفاده می‌کنم. به راحتی می‌توانم آن را به Looker Studio متصل کنم تا داده‌ها را تجسم کنم، بدون اینکه هزینه‌ای به من اضافه شود.

اگر از ChatGPT Plus استفاده می‌کنید، می‌توانید از یک GPT سفارشی که ساخته‌ام استفاده کنید که ساختار جدول‌ها را برای GA4 و Search Console در نظر می‌گیرد. در راهنمای بالا، فرض بر این بوده که شما از نسخه رایگان استفاده می‌کنید و توضیح داده شده که چگونه می‌توانید به طور کلی از ChatGPT برای اجرای BigQuery استفاده کنید.

مثال:

Custom GPT با ساختار جدول‌های BigQuery.

هیچ چیز پیچیده‌ای وجود ندارد – شما فقط باید جدول‌ها را از BigQuery به عنوان JSON کپی کنید و آن‌ها را به ChatGPT Plus بارگذاری کنید تا بتواند به ساختار جدول‌ها ارجاع دهد. همچنین، یک درخواست وجود دارد که از GPT می‌خواهد هنگام نوشتن کوئری‌ها به فایل‌های JSON پیوست شده ارجاع دهد.

این یک نمونه دیگر است که نشان می‌دهد چگونه می‌توانید از ChatGPT برای انجام کارها به صورت مؤثرتر استفاده کنید و وظایف تکراری را از بین ببرید.

اگر نیاز دارید با دیتاست دیگری (متفاوت از GA4 یا GSC) کار کنید و SQL را نمی‌دانید، می‌توانید ساختار جدول را از BigQuery به ChatGPT بارگذاری کنید و SQL‌های مخصوص آن ساختار جدول را بنویسید. آسان است، اینطور نیست؟

برای اجرای کوئری‌های BigQuery و مدیریت داده‌های حاصل، می‌توانید این مراحل را دنبال کنید:

اجرای کوئری BigQuery: پس از نوشتن و اصلاح کوئری SQL با استفاده از ChatGPT یا هر ابزار دیگری، باید آن را در محیط BigQuery اجرا کنید. مراحل زیر را دنبال کنید:

وارد حساب کاربری Google Cloud خود شوید و به BigQuery Console بروید.
کد SQL خود را در ویرایشگر SQL BigQuery کپی کنید.
برای اجرای کوئری، روی دکمه “Run” کلیک کنید. نتایج کوئری پس از اجرا نمایش داده می‌شود.

صادرات داده‌ها: پس از اجرای کوئری، می‌توانید داده‌های حاصل را به روش‌های مختلف صادر کنید:

Google Sheets: اگر داده‌هایتان در حد مجاز Google Sheets هستند (چند ده هزار ردیف)، می‌توانید نتایج کوئری را مستقیماً به Google Sheets صادر کنید. برای این کار، روی “Save Results” کلیک کرده و “Google Sheets” را انتخاب کنید.
CSV: اگر داده‌های شما در محدوده مجاز است، می‌توانید آن‌ها را به صورت CSV دانلود کنید. برای این کار، پس از اجرای کوئری، بر روی “Save Results” کلیک کرده و “CSV” را انتخاب کنید.

برای ارائه اطلاعات اضافی، از مستندات رسمی GA4 در مورد نحوه محاسبه engagement_time_msec کمک گرفتم. سند مربوطه را در پیام پیگیری کپی و پیست کردم و از ChatGPT خواستم که هنگام نوشتن کوئری به این اطلاعات مراجعه کند، که بسیار کمک‌کننده بود. (اگر خطای نحوی دریافت کردید، کافی است کد خطا را کپی کنید و به عنوان سوال پیگیری وارد کنید و از ChatGPT بخواهید که آن را برطرف کند.)

SQL صحیحی که به دست آوردم:

WITH
-- مرحله 1: فیلتر کردن داده‌های GA4 برای ۷ روز گذشته و استخراج پارامترهای رویداد
ga4_data AS (
  SELECT
    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_location') AS page_location
  FROM
    `your_bq_project_name.your_bq_ga4_dataset.events_*`
  WHERE
     _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') IS NOT NULL
),

-- مرحله 2: تجمیع داده‌های GA4 بر اساس شناسه جلسه و مکان صفحه
aggregated_ga4_data AS (
  SELECT
    page_location,
    SUM(engagement_time_msec/1000) / NULLIF(COUNT(DISTINCT ga_session_id), 0) AS avg_engagement_time_msec
  FROM
    ga4_data
  GROUP BY
    page_location
),

-- مرحله 3: فیلتر کردن داده‌های GSC برای ۷ روز گذشته و انتخاب URLهایی که کلیک داشته‌اند
gsc_data AS (
  SELECT
    url,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    SUM(sum_position) / SUM(impressions) AS avg_position
  FROM
    `your_bq_project_name.searchconsole.searchdata_url_impression`
  WHERE
    data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
    AND clicks > 0
  GROUP BY
    url
)

-- پیوستن داده‌های Google Search Console به داده‌های GA4 بر اساس page_location و url 
SELECT
  gsc.url,
  gsc.clicks,
  gsc.impressions,
  gsc.avg_position,
  ga4.avg_engagement_time_msec
FROM
  gsc_data AS gsc
LEFT JOIN
  aggregated_ga4_data AS ga4
ON
  gsc.url = ga4.page_location
ORDER BY
  gsc.clicks DESC;

WITH

-- مرحله 1: فیلتر کردن داده‌های GA4 برای ۷ روز گذشته و استخراج پارامترهای رویداد

ga4_data AS (

SELECT

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_location') AS page_location

FROM

`your_bq_project_name.your_bq_ga4_dataset.events_*`

WHERE

_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') IS NOT NULL

-- مرحله 2: تجمیع داده‌های GA4 بر اساس شناسه جلسه و مکان صفحه

aggregated_ga4_data AS (

SELECT

page_location,

SUM(engagement_time_msec/1000) / NULLIF(COUNT(DISTINCT ga_session_id), 0) AS avg_engagement_time_msec

FROM

ga4_data

GROUP BY

page_location

-- مرحله 3: فیلتر کردن داده‌های GSC برای ۷ روز گذشته و انتخاب URLهایی که کلیک داشته‌اند

gsc_data AS (

SELECT

url,

SUM(clicks) AS clicks,

SUM(impressions) AS impressions,

SUM(sum_position) / SUM(impressions) AS avg_position

FROM

`your_bq_project_name.searchconsole.searchdata_url_impression`

WHERE

data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)

AND clicks > 0

GROUP BY

url

)

-- پیوستن داده‌های Google Search Console به داده‌های GA4 بر اساس page_location و url

SELECT

gsc.url,

gsc.clicks,

gsc.impressions,

gsc.avg_position,

ga4.avg_engagement_time_msec

FROM

gsc_data AS gsc

LEFT JOIN

aggregated_ga4_data AS ga4

gsc.url = ga4.page_location

ORDER BY

gsc.clicks DESC;

حالا می‌توانید این کوئری را اجرا کنید و داده‌ها را به صورت CSV یا Google Sheets صادر کنید.

مدیریت داده‌های بزرگ: اگر داده‌های شما بسیار بزرگ هستند و از محدودیت‌های Google Sheets یا CSV تجاوز می‌کنند، می‌توانید از روش‌های زیر استفاده کنید:

ذخیره در جدول BigQuery: به جای صادرات مستقیم، نتایج را به عنوان یک جدول جدید در BigQuery ذخیره کنید. برای این کار، پس از اجرای کوئری، بر روی “Save Results” کلیک کرده و گزینه “Table” را انتخاب کنید.
استفاده از Looker Studio یا Databox: می‌توانید جدول‌های ذخیره شده در BigQuery را به ابزارهایی مانند Looker Studio یا Databox متصل کنید تا داده‌ها را تجزیه و تحلیل کرده و داشبوردهای بصری ایجاد کنید.

مدیریت هزینه‌ها: به دلیل اینکه استفاده از BigQuery و ابزارهای مرتبط مانند Looker Studio ممکن است هزینه‌هایی را به دنبال داشته باشد، می‌توانید اقدامات زیر را برای مدیریت هزینه‌ها انجام دهید:

استفاده از Google Sheets: برای داده‌هایی که حجم آن‌ها کمتر از حد مجاز است، Google Sheets بهترین گزینه است زیرا باز کردن آن در Looker Studio هزینه‌ای نخواهد داشت.
پایش استفاده از BigQuery: استفاده از BigQuery خود را به‌طور منظم بررسی کنید و از عبور از حد مجاز ماهانه خودداری کنید.

استفاده از GPT سفارشی: اگر از ChatGPT Plus استفاده می‌کنید، می‌توانید از یک GPT سفارشی برای مدیریت ساختار جدول‌ها و کوئری‌های BigQuery استفاده کنید. این GPT سفارشی به شما کمک می‌کند تا SQL‌های مناسب با ساختار جدول‌های BigQuery خود را به‌راحتی بنویسید. برای این کار، می‌توانید جدول‌های BigQuery خود را به صورت JSON در GPT سفارشی بارگذاری کنید تا به ساختار جدول‌ها ارجاع دهد و کوئری‌های متناسب با آن‌ها را ایجاد کند.

با دنبال کردن این مراحل، می‌توانید به طور مؤثر از BigQuery برای مدیریت و تجزیه و تحلیل داده‌های بزرگ استفاده کنید و نتایج دقیق‌تری از داده‌های SEO خود به دست آورید.

نتیجه‌گیری

استفاده از توانایی‌های کدنویسی ChatGPT برای نوشتن کوئری‌های BigQuery می‌تواند افق‌های جدیدی را برای گزارش‌دهی و تحلیل داده‌ها باز کند. این قابلیت به شما اجازه می‌دهد که منابع مختلف داده را با هم ترکیب کرده و وظایف پیچیده تحلیل داده‌ها را ساده‌تر کنید. با این حال، این مثال‌ها به ما یادآور می‌شوند که در کنار استفاده از چت‌بات‌های هوش مصنوعی، نظارت و بررسی انسانی همچنان ضروری است، چرا که این ابزارها ممکن است دچار اشتباه شوند یا پاسخ‌های نادرستی ارائه دهند.

بهبود رتبه سایت خود را با خدمات سئو وب آنجل تجربه کنید

خدمات سئـو سایت در وب آنجل به شما کمک می‌کند تا با استفاده از بهترین روش‌ها و استراتژی‌ها، رتبه سایت خود را در موتورهای جستجو بهبود بخشید و ترافیک ارگانیک بیشتری جذب کنید.

خدمات سئو سایت
خدمات سئو داخلی

دکتر حامد مهدی‌ زاده

فوق دکترای مدیریت کسب‌وکار، اولین متخصص الگوریتم‌های گوگل در ایران و کوچ حرفه‌ای هستم که بیش از ۱۵ سال در حوزه مدیریت، مشاوره و دیجیتال مارکتینگ فعالیت داشته‌ام. در این مسیر با ارائه راهکارهای خلاقانه و اجرای پروژه‌های موفق در زمینه سئو و برندینگ، توانسته‌ام مسیر رشد برندهای بزرگی مثل خبرگزاری صدا و سیما، افق کوروش و سایر سازمان‌های مطرح را هموار کنم. تجربیاتم شامل مدیریت پروژه‌های پیچیده، طراحی استراتژی‌های بازاریابی، بهینه‌سازی منابع مالی، توسعه تیم‌های کارآمد و بهبود فرآیندهای کسب‌وکار است که توانایی مرا برای ایفای نقش‌های کلیدی مدیریتی تقویت کرده‌اند. مدیریت وب‌سایت وب‌آنجل، به‌عنوان مرجع دیجیتال مارکتینگ، بخشی از تعهد من به انتقال دانش و تجربه به جامعه حرفه‌ای ایران است. هدف من کمک به کسب‌وکارها برای دستیابی به موفقیت پایدار از طریق ارائه راهکارهای استراتژیک، افزایش نرخ بازگشت سرمایه، بهبود جایگاه در نتایج گوگل و ایجاد ارزش افزوده ماندگار است. باور دارم هر چالش فرصتی برای رشد است و هر کسب‌وکار شایسته راه‌حل‌هایی منحصربه‌فرد و خلاقانه است.

دیگر مقالات من

🎉 ۲۰٪ تخفیف ویژه برای هم‌میهنان عزیز

وب آنجل با بیش از ۱۶ سال تجربه تخصصی در سئو، طراحی وب و بازاریابی دیجیتال، همیشه یک هدف داشته است: رضایت کامل مشتریان.

صدها پروژه موفق و بازخوردهایی که یک پیام مشترک دارند: «وب آنجل، فرشته نجات کسب‌وکار ماست»

این فرصت را از دست ندهید؛ همین امروز کسب‌وکار خود را وارد مسیر رشد سریع و پایدار کنید.

۱. بررسی رایگان وب‌سایت

ارزیابی سئو تکنیکال و تجربه کاربری
گزارش دقیق با فرصت‌های رشد
پیشنهادهای کاربردی و اختصاصی

۲. خدمات سئو

استراتژی‌های پیشرفته کلمات کلیدی و محتوا
بهینه‌سازی سئو تکنیکال و داخلی
ساخت بک‌لینک و لینک‌سازی داخلی

۳. تبلیغات گوگل و SEM

راه‌اندازی و بهینه‌سازی کمپین‌ها
افزایش CTR و مدیریت بودجه
هدف‌گیری تبلیغاتی با نرخ تبدیل بالا

۴. بهبود CRO و تجربه کاربری

تست A/B و تحلیل نقشه حرارتی
بهینه‌سازی CTAها و صفحات فرود
ایجاد تجربه کاربری روان و بدون مانع

۵. طراحی و توسعه وب

طراحی واکنش‌گرا و اولویت با موبایل
طراحی مدرن و با بارگذاری سریع
توسعه یکپارچه با سئو

۶. بازاریابی محتوایی و ایمیل

تولید محتوا و استراتژی بلاگ سئو
ایمیل مارکتینگ و طراحی قیف‌های ارتباطی
محتوای شخصی‌سازی‌شده و متمرکز بر تبدیل

همین حالا خدمات وب آنجل را ببینید و با تخفیف ویژه شروع کنید

دیدگاهتان را بنویسید لغو پاسخ

Shop Web Angel