تحليل بيانات الموقع باستخدام BigQuery: دليل شامل للأوامر التحليلية

مقدمة

في عالم اليوم الرقمي، يعد تحليل بيانات الموقع أمرًا بالغ الأهمية لفهم كيفية تفاعل الزوار مع محتواك. Google Search Console يوفر مجموعة من البيانات القيمة التي يمكن تحليلها باستخدام BigQuery لتحسين أداء موقعك على محركات البحث. في هذه المقالة، سنستعرض الأوامر التحليلية الأساسية والمتقدمة التي يمكنك استخدامها في BigQuery لجمع وتحليل بيانات موقعك بالتفصيل.

1. إعداد BigQuery مع Google Search Console

قبل البدء في تحليل البيانات، تحتاج إلى إعداد BigQuery وتوصيله بـ Google Search Console. تأكد من أن لديك البيانات المطلوبة مخزنة في جدول BigQuery. في هذه المقالة، سنستخدم جدولًا يسمى searchdata_url_impression كمثال.

2. تحليل الصفحات ذات الأداء الضعيف في البحث

يعد تحديد الصفحات التي تحصل على عدد قليل من النقرات على الرغم من ظهورها في نتائج البحث خطوة أولى مهمة لتحسينها.

SELECT
  site_url,
  url,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks,
  ROUND(SUM(clicks) / SUM(impressions) * 100, 2) AS CTR
FROM
  `your_project_id.searchconsole.searchdata_url_impression`
GROUP BY
  site_url,
  url
HAVING
  SUM(clicks) < 10
ORDER BY
  CTR ASC;

SELECT
  query,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks
FROM
  `your_project_id.searchconsole.searchdata_url_impression`
GROUP BY
  query
HAVING
  SUM(clicks) = 0
ORDER BY
  total_impressions DESC;

SELECT
  device,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks,
  ROUND(SUM(clicks) / SUM(impressions) * 100, 2) AS CTR
FROM
  `your_project_id.searchconsole.searchdata_url_impression`
GROUP BY
  device
ORDER BY
  total_clicks DESC;

SELECT
  country,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks,
  ROUND(SUM(clicks) / SUM(impressions) * 100, 2) AS CTR
FROM
  `your_project_id.searchconsole.searchdata_url_impression`
GROUP BY
  country
ORDER BY
  total_clicks DESC;

SELECT
  search_type,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks,
  ROUND(SUM(clicks) / SUM(impressions) * 100, 2) AS CTR
FROM
  `your_project_id.searchconsole.searchdata_url_impression`
GROUP BY
  search_type
ORDER BY
  total_clicks DESC;

• search_type: نوع البحث (مثل ويب، صور، فيديو).
• CTR: معدل النقر إلى الظهور حسب نوع البحث.

7. تحليل الكلمات المفتاحية المفقودة

تحليل الكلمات المفتاحية التي قد تجلب زيارات إضافية إذا قمت بتحسين صفحات معينة بناءً عليها.

SELECT
  query,
  COUNT(DISTINCT site_url) AS unique_pages,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks,
  ROUND(SUM(clicks) / SUM(impressions) * 100, 2) AS CTR
FROM
  `your_project_id.searchconsole.searchdata_url_impression`
WHERE
  query NOT IN (SELECT DISTINCT keyword FROM `your_project_id.searchconsole.your_keywords_table`)
GROUP BY
  query
ORDER BY
  total_impressions DESC;

• unique_pages: عدد الصفحات الفريدة التي تظهر للكلمة المفتاحية.
• CTR: معدل النقر إلى الظهور للكلمات المفتاحية المفقودة.

8. تحليل أداء الصفحة حسب نوع المحتوى

يساعدك في معرفة أنواع المحتوى (مثل الفيديوهات، الوصفات) التي تحقق أداءً جيدًا على موقعك.

SELECT
  url,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks,
  ROUND(SUM(clicks) / SUM(impressions) * 100, 2) AS CTR,
  SUM(CASE WHEN is_video THEN impressions ELSE 0 END) AS video_impressions,
  SUM(CASE WHEN is_recipe_feature THEN impressions ELSE 0 END) AS recipe_impressions
FROM
  `your_project_id.searchconsole.searchdata_url_impression`
GROUP BY
  url
ORDER BY
  total_clicks DESC;

• video_impressions: عدد مرات الظهور للفيديوهات.
• recipe_impressions: عدد مرات الظهور للوصفات.

9. تحليل أداء الموقع على مدى الزمن

فهم كيفية تغير أداء موقعك بمرور الوقت يساعد في اكتشاف الاتجاهات والتغيرات الموسمية.

SELECT
  data_date,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks,
  ROUND(SUM(clicks) / SUM(impressions) * 100, 2) AS CTR
FROM
  `your_project_id.searchconsole.searchdata_url_impression`
GROUP BY
  data_date
ORDER BY
  data_date ASC;

• data_date: تاريخ جمع البيانات.
• CTR: معدل النقر إلى الظهور عبر الزمن.

10. تحليل تأثير تحسين تجربة الصفحة

تحليل أداء الصفحات التي تم تحسينها لتجربة المستخدم يمكن أن يساعدك في تقييم تأثير التحسينات.

SELECT
  url,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks,
  ROUND(SUM(clicks) / SUM(impressions) * 100, 2) AS CTR,
  SUM(CASE WHEN is_page_experience THEN impressions ELSE 0 END) AS page_experience_impressions
FROM
  `your_project_id.searchconsole.searchdata_url_impression`
GROUP BY
  url
ORDER BY
  total_clicks DESC;

• page_experience_impressions: عدد مرات الظهور للصفحات المحسنة لتجربة المستخدم.

الخاتمة

يتيح لك استخدام BigQuery مع بيانات Google Search Console إجراء تحليلات متقدمة لفهم أداء موقعك على محركات البحث بشكل أفضل. من خلال الاستفادة من الأوامر التحليلية الموضحة في هذه المقالة، يمكنك تحسين محتوى موقعك، استهداف الجماهير بشكل أكثر فعالية، وزيادة عدد الزيارات العضوية. إذا كنت بحاجة إلى مزيد من التحليلات المخصصة، يمكنك دائمًا تعديل هذه الاستعلامات أو إنشاء استعلامات جديدة بناءً على احتياجاتك الخاصة.