SEO Uzmanları BigQuery İçin ChatGPT'yi Örneklerle Nasıl Kullanabilir?

Yapay zeka, beceriler (kodlama veya yazma gibi) üreterek her alanı şekillendiriyor.

Doğru komutları çalıştırabilen bir yapay zeka operatörü, düşük ve orta zorluktaki görevleri gerçekleştirerek stratejik karar almaya daha fazla odaklanılmasını sağlayabilir.

Bu kılavuzda, SEO raporlama ihtiyaçlarınız için karmaşık BigQuery sorgularını çalıştırmak üzere örnek olarak AI sohbet robotlarının ChatGPT ile nasıl kullanılacağını adım adım açıklayacağız.

İki örneği inceleyeceğiz:

Ayrıca, SEO raporlarını çalıştırırken yükü azaltmak için sohbet robotlarını nasıl kullanabileceğinize dair genel bir fikir de verecektir.

Neden BigQuery’yi Öğrenmeniz Gerekiyor?

Google Search Console veya Google Analytics 4 gibi SEO araçları, verilere erişmek için kullanabileceğiniz erişilebilir kullanıcı arayüzlerine sahiptir. Ancak çoğu zaman yapabileceklerinizi sınırlandırırlar ve eksik verileri gösterirler ki buna genellikle veri örnekleme adı verilir.

GSC’de bunun nedeni aracın şunları atlamasıdır: anonimleştirilmiş sorgular ve tablo satırlarını en fazla 1.000 satırla sınırlar.

Google Search Console’dan ekran görüntüsü, Mayıs 2024

BigQuery’yi kullanarak bu sorunu çözebilir ve istediğiniz karmaşık raporları çalıştırabilir, böylece büyük web siteleriyle çalışırken sıklıkla karşılaşılan veri örnekleme sorununu ortadan kaldırabilirsiniz.

(Alternatif olarak, kullanmayı deneyebilirsiniz, ancak bu makalenin amacı BigQuery için ChatGPT’yi nasıl çalıştırabileceğinizi göstermektir.)

Bu yazıda GSC ve GA4 hesaplarınızı BigQuery’ye zaten bağladığınızı varsayıyoruz. Henüz yapmadıysanız, nasıl yapılacağına ilişkin kılavuzlarımıza göz atmak isteyebilirsiniz:

SQL’in Temelleri

Yapılandırılmış Sorgu Dili’ni (SQL) biliyorsanız, bu bölümü atla. Ancak bilmeyenler için burada SQL ifadelerine hızlı bir referans verilmiştir:

İfade	Tanım
SELECT	Tablolardan veri alır
INSERT	Bir tabloya yeni veriler ekler
UNNEST	Bir diziyi bir dizi satır halinde düzleştirir
UPDATE	Bir tablodaki mevcut verileri günceller
DELETE	Bir tablodaki verileri siler
CREATE	Yeni bir tablo veya veritabanı oluşturur
ALTER	Mevcut bir tabloyu değiştirir
DROP	Bir tabloyu veya veritabanını siler.

Kendinizi tanıyabilmeniz için kullanacağımız koşullar:

Durum	Tanım
WHERE	Kayıtları belirli koşullara göre filtreler
AND	Tüm koşulların doğru olması gereken iki veya daha fazla koşulu birleştirir
OR	En az bir koşulun doğru olması gereken iki veya daha fazla koşulu birleştirir
NOT	Bir koşulu reddeder
LIKE	Bir sütunda belirtilen bir modeli arar.
IN	Bir değerin bir değerler kümesi dahilinde olup olmadığını kontrol eder
BETWEEN	Belirli bir aralıktaki değerleri seçin
IS NULL	Boş değerleri kontrol eder
IS NOT NULL	Boş olmayan değerleri kontrol eder
EXISTS	Bir alt sorgunun herhangi bir kayıt döndürüp döndürmediğini kontrol eder

Şimdi BigQuery’yi ChatGPT aracılığıyla nasıl kullanabileceğinizi gösteren örneklere bakalım.

1. Nasıl Yapılır Tra’yı analiz edinGoogle Algoritmasının Etkisi Nedeniyle ffic Düşüşü

Bir Google algoritma güncellemesinden etkilendiyseniz yapmanız gereken ilk şey, etkilenen sayfalar hakkında raporlar çalıştırmak ve neden etkilendiğinizi analiz etmektir.

Unutmayın, yapabileceğiniz en kötü şey panik modunda hemen web sitesinde bir şeyi değiştirmeye başlamaktır. Bu, arama trafiğinde dalgalanmalara neden olabilir ve etkinin analizini daha da zorlaştırabilir.

Dizinde daha az sayfanız varsa, verilerinizi analiz etmek için GSC kullanıcı arayüzü verilerini kullanmayı tatmin edici bulabilirsiniz, ancak on binlerce sayfanız varsa, 1.000’den fazla satırı (sayfa veya sorgu) dışa aktarmanıza izin vermez. veri.

Algoritma güncellemesinin kullanıma sunulmasından bu yana bir haftalık verileriniz olduğunu ve bunu önceki haftanın verileriyle karşılaştırmak istediğinizi varsayalım. Bu raporu BigQuery’de çalıştırmak için şu basit istemle başlayabilirsiniz:

Imagine you are a data analyst experienced in Google Analytics 4 (GA4), Google Search Console, SQL, and BigQuery.Your task is to generate an SQL query to compare 'WEB' Search Console data for the periods '2024-05-08' to '2024-05-20' and '2024-04-18' to '2024-04-30'. Extract the total clicks, impressions, and average position for each URL for each period. Additionally, calculate the differences in these metrics between the periods for each URL (where average position should be calculated as the sum of positions divided by the sum of impressions).Details:BigQuery project name: use_your_bigquery_projectnameDataset name: searchconsoleTable name: searchdata_url_impressionPlease provide the SQL query that meets these requirements.

Bir SQL kodu aldıktan sonra, onu kopyalayıp BigQuery SQL düzenleyiciye yapıştırın, ancak alacağınız ilk kodda hatalar olacağına bahse girerim. Örneğin, tablo sütunu adları BigQuery veri kümenizdeki adlarla eşleşmeyebilir.

Sütun adı veri kümesi sütunuyla eşleşmediğinde BigQuery SQL’de hata oluştu.

ChatGPT aracılığıyla kodlama görevleri gerçekleştirilirken buna benzer şeyler oldukça sık yaşanıyor. Şimdi bu gibi sorunları hızlı bir şekilde nasıl çözebileceğinize bakalım.

BigQuery’de tablonun tüm sütunları nasıl seçilir?

Bunu aldıktan sonra, takip istemi olarak kopyalayıp yapıştırın ve tuşuna basın.

Generate SQL again by taking into account that my table schema is as follows : "fullname mode type descriptiondata_date DATE site_url STRING url STRING query STRING is_anonymized_query BOOLEAN is_anonymized_discover BOOLEAN country STRING search_type STRING device 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 impressions INTEGER clicks INTEGER sum_position INTEGER is_product_snippets BOOLEAN is_merchant_listings BOOLEAN is_learning_videos BOOLEAN "

Bu, oluşturulan SQL’i GSC tablo yapısına göre aşağıdaki gibi düzeltecektir:

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_diffFROM  comparisonORDER BY  url;

Şimdi çalıştırın ve verileri CSV’ye veya Google E-Tablolar’a aktararak keyfini çıkarın.

BigQuery’de SQL nasıl çalıştırılır?

Milyonlarca URL’niz varsa, veriler çok büyük olduğundan Google E-Tablolar’da veya CSV dışa aktarmada çalışamayabilirsiniz. Ayrıca, bu uygulamaların tek bir belgede kaç satıra sahip olabileceğiniz konusunda sınırlamaları vardır. Bu durumda sonuçları bir BigQuery tablosu olarak kaydedebilir ve verileri görüntülemek için Looker Studio veya Databox ile bu tabloya bağlanabilirsiniz.

Ancak BigQuery’nin ücretsiz bir hizmet olduğunu lütfen unutmayın. Ayda 1 TB’a kadar işlenmiş sorgu verisi ücretsizdir. Bu limiti aştığınızda kredi kartınızdan otomatik olarak çekim yapılır.

Bu, BigQuery’nizi şuraya bağlarsanız anlamına gelir: verilerinize orada göz atın; bu, Looker kontrol panelinizi her açışınızda faturalandırmanıza dahil edilecektir.

ChatGPT Plus’ınız varsa, bunu kullanabilirsiniz. GPT GA4 ve Search Console için tablo şemalarını dikkate alarak yaptım. Yukarıdaki kılavuzda, ücretsiz sürümü kullandığınızı varsaydım ve BigQuery’yi çalıştırmak için ChatGPT’yi genel olarak nasıl kullanabileceğinizi gösteriyordu.

Bu özel GPT’de ne olduğunu bilmek istiyorsanız arka ucun ekran görüntüsünü burada bulabilirsiniz.

BigQuery tablo şemalarına sahip özel GPT.

Karmaşık bir şey yok; yalnızca BigQuery’deki tabloları JSON olarak kopyalamanız yeterli. Yukarıda açıklanan adım ve tablo yapısına başvurabilmesi için bunları özel GPT’ye yükleyin. Ek olarak, GPT’den sorguları oluştururken eklenen JSON dosyalarına başvurmasını isteyen bir komut istemi vardır.

Bu, tekrarlanan görevleri ortadan kaldırarak görevleri daha etkili bir şekilde gerçekleştirmek için ChatGPT’yi nasıl kullanabileceğinizi gösteren başka bir örnektir.

Başka bir veri kümesiyle (GA4 veya GSC’den farklı) çalışmanız gerekiyorsa ve SQL bilmiyorsanız tablo şemasını BigQuery’den ChatGPT’ye yükleyebilir ve o tablo yapısına özel SQL’ler oluşturabilirsiniz. Kolay değil mi?

Ev ödevi olarak hangi sorguların etkilendiğini analiz etmenizi öneririm.

Google Search Console tablosunda bunu yapmak için herhangi bir farklılaştırıcı yoktur, ancak Google’ın AI Genel Bakış’ı kullanıma sunduğu 14 Mayıs 2024’ten sonra hangi sayfaların sıralamasını kaybetmediğini ancak TO’da önemli bir düşüş yaşadığını görmek için bir sorgu çalıştırabilirsiniz.

14 Mayıs’tan sonraki iki haftalık dönemi, önceki iki haftayla karşılaştırabilirsiniz. TO’daki düşüşün, bir rakibin Öne Çıkan Snippet alması gibi diğer arama özellikleri nedeniyle meydana gelmiş olma ihtimali hala mevcuttur, ancak tıklamalarınızın AI Genel Bakışlarından (önceki adıyla Arama Üretken Deneyimi veya “SGE”) etkilendiği yeterli sayıda geçerli durum bulmalısınız.

2. Nasıl Yapılır Aramayı Birleştirh GA4’ten Etkileşim Metriklerini İçeren Trafik Verileri

Arama trafiğini analiz ederken, kullanıcıların içerikle ne kadar etkileşimde bulunduğunu anlamak hayati önem taşıyor çünkü kullanıcı etkileşim sinyalleri sıralama faktörleridir. GA4’te tanımlanan tam metrikleri kastetmediğimi lütfen unutmayın.

Ancak GA4’ün etkileşim metrikleri (ör. web sitenizin bir kullanıcının tarayıcısında odakta olduğu ortalama süre olan “oturum başına ortalama etkileşim süresi”), makalelerinizin kullanıcıların okuması için yeterince iyi olup olmadığına dair ipucu verebilir.

Çok düşükse blog sayfalarınızda bir sorun olabilir ve kullanıcılar bunları okumuyor olabilir.

Bu metriği Search Console verileriyle birleştirirseniz sıralaması düşük olan sayfaların oturum başına ortalama etkileşim süresinin de düşük olduğunu görebilirsiniz.

GA4 ve GSC’nin farklı kaynak dağıtımı modellerine sahip olduğunu lütfen unutmayın. GA4, son tıklama ilişkilendirme modelini kullanır; bu, bir kişinin Google’dan bir makale sayfasını bir kez ziyaret edip ardından iki kez daha doğrudan geri dönmesi durumunda GA4’ün üç ziyareti de Google’a bağlayabileceği, GSC’nin ise yalnızca birini raporlayacağı anlamına gelir.

Dolayısıyla %100 doğru değildir ve kurumsal raporlama için uygun olmayabilir, ancak GSC verilerinin yanı sıra GA4’ten katılım metriklerine sahip olmak, sıralamanızın etkileşimle olan korelasyonunu analiz etmek için değerli bilgiler sağlar.

ChatGPT’yi BigQuery ile kullanmak biraz hazırlık gerektirir. Komuta geçmeden önce GA4 tablolarının nasıl yapılandırıldığını okumanızı öneririm çünkü GSC’nin tabloları kadar basit değil.

Bir kayıt türüne sahip olan ve aşağıdaki gibi boyutları içeren bir event_params sütununa sahiptir: page_location, ga_session_idVe nişan_zamanı_msec. Bir kullanıcının web sitenizle ne kadar süre aktif olarak etkileşimde bulunduğunu izler.

event_params anahtarı engagement_time_msec sitede geçirilen toplam süre değil, her etkileşim yeni bir etkileşim süresi eklediğinde belirli etkileşimler (tıklama veya kaydırma gibi) için harcanan süredir. Bu, kullanıcıların web sitenizi veya uygulamanızı aktif olarak kullandığı tüm küçük anları toplamak gibidir.

Bu nedenle, bu metriği toplayıp sayfalar için oturumların ortalamasını alırsak, oturum başına ortalama etkileşim süresini elde ederiz.

Şimdi, bir kere anladığında engagement_time_msec ChatGPT’den her URL için GA4 “oturum başına ortalama etkileşim süresini” alan ve bunu makalelerin GSC arama performansı verileriyle birleştiren bir sorgu oluşturmamıza yardımcı olmasını isteyelim.

Kullanacağım komut istemi:

Imagine you are a data analyst experienced in Google Analytics 4 (GA4), Google Search Console, SQL, and BigQuery.Compose a SQL query that pulls the following data from Google Search Console for each URL for the previous 7 days, excluding the current day:1. Clicks,2. Impressions,3. Average position (calculated as the sum of positions divided by the sum of impressions).From GA4 BigQuery table unnest from event_params ga_session_id, engagement_time_msec and page_location.Select only rows which have engagement_time_msec set as not null group all sessions with the same IDs and page_location and SUM engagement_time_msec and devides to SUM of sessions number Join GA4 and Google Search Console data by URLs for the same period. Additonally Optimize the query to pull from GA4's table partitions and not query the entire table.Details:1. BigQuery project name: use_your_bigquery_projectname 2. GA4 dataset name: use_your_ga4_bigquery_dataset_name3. GA4 table name: use_your_ga4_bigquery_table_name_under_dataset3. Google Search Console dataset name: use_your_gsc_bigquery_table_name_under_dataset3. Google Search Console table name: use_your_gsc_bigquery_table_name_under_dataset4. Here is BigQuery tables schemas for GA4: [copy table schema here]5. Here is BigQuery tables schemas for Google Search Console: [copy table schema here]

BigQuery’ye kopyalayıp yapıştırdığımda, “oturum başına ortalama etkileşim süresi”nin tamamen boş olduğu sonuçlar verdi. Görünüşe göre ChatGPT’nin GA4’ün nasıl çalıştığı konusunda daha fazla bağlama ve rehberliğe ihtiyacı var.

GA4’ün, nişan_zamanı_msn’yi nasıl hesapladığına ilişkin resmi dokümanlarından bir takip sorusu olarak. Belgeyi kopyalayıp takip istemine yapıştırdım ve sorguyu oluştururken bu bilgiye başvurmamı istedim, bu da yardımcı oldu. (Herhangi bir sözdizimi hatasıyla karşılaşırsanız, bunu takip sorusu olarak kopyalayıp yapıştırmanız ve düzeltmenizi istemeniz yeterlidir.)

15 dakikalık çalışma sonucunda doğru SQL’i elde ettim:

WITH-- Step 1: Filter GA4 data for the last 7 days and unnest event_paramsga4_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),-- Step 2: Aggregate GA4 data by session ID and page locationaggregated_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),-- Step 3: Filter GSC data for the last 7 days and select urls which had clicksgsc_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)-- Joining Google Search Console data with GA4 data by page_location and url SELECT  gsc.url,  gsc.clicks,  gsc.impressions,  gsc.avg_position,  ga4.avg_engagement_time_msecFROM  gsc_data AS gscLEFT JOIN  aggregated_ga4_data AS ga4ON  gsc.url = ga4.page_locationORDER BY  gsc.clicks DESC;

Bu, etkileşim metrikleriyle birlikte GSC verilerini GA4’ten alır.

Search Console verileri GA4 ile birleştirdi

GA4 kullanıcı arayüzündeki sayılar ile BigQuery tablolarından sorgulanan veriler arasında farklılıklar fark edebileceğinizi lütfen unutmayın.

Bunun nedeni GA4’ün “Etkin Kullanıcılar”a odaklanması ve nadir veri noktalarını “(diğer)” kategorisinde gruplandırması, BigQuery’nin ise tüm ham verileri göstermesidir. GA4 ayrıca izin verilmediğinde boşluklar için modellenmiş verileri kullanır ve BigQuery bunu içermez.

Ayrıca GA4, daha hızlı raporlar için verileri örnekleyebilirken BigQuery tüm verileri içerir. Bu varyasyonlar, GA4’ün hızlı bir genel bakış sunduğu, BigQuery’nin ise ayrıntılı analiz sağladığı anlamına gelir. Bunun neden olduğuna ilişkin daha ayrıntılı bir açıklama öğrenin Bu makalede.

Sonuçları GA4 kullanıcı arayüzüne bir adım daha yaklaştırmak için sorguları yalnızca etkin kullanıcıları içerecek şekilde değiştirmeyi deneyebilirsiniz.

Alternatif olarak verileri harmanlamak için Looker Studio’yu kullanabilirsiniz, ancak sınırlamaları var çok büyük veri kümeleriyle. BigQuery, terabaytlarca veriyi verimli bir şekilde işleyerek ölçeklenebilirlik sunar ve bu da onu büyük ölçekli SEO raporları ve ayrıntılı analizler için ideal kılar.

Gelişmiş SQL yetenekleri, Looker Studio’nun veya diğer kontrol paneli araçlarının eşleşemeyeceği daha derin içgörüler için karmaşık sorgulara olanak tanır.