نحوه استفاده از BigQuery با کنسول جستجوی گوگل

بازاریابان می دانند که داده های زیادی برای سایت هایشان در دسترس است. این داده ها به راحتی از طریق چند ابزار Google در دسترس هستند: Google Analytics، Google Search Console و Looker Studio.

اکنون می‌توانیم BigQuery را به ترکیب اضافه کنیم. در ماه فوریه، گوگل اعلام کرد که کاربران می توانند داده های کنسول جستجوی گوگل خود را به BigQuery صادر کنند. گوگل همیشه کاربران را به استفاده از ویژگی های گوگل خود سوق داده است (Google+ را به خاطر دارید؟) و این تفاوتی ندارد.

در حالی که برخی ممکن است این بار چشمان خود را به سمت Google بچرخانند، BigQuery دارای تعدادی مزیت است که با سایر ابزارهای Google نمی توانید آنها را پیدا کنید (یا به راحتی می توانید آنها را پیدا کنید).

در این راهنما، به BigQuery، نحوه راه‌اندازی آن و نحوه استفاده از آن با کنسول جستجوی Google خواهیم پرداخت.

BigQuery چیست؟

اجازه دهید گوگل توضیح دهد که:

BigQuery یک انبار داده سازمانی کاملاً مدیریت شده است که به شما کمک می کند داده های خود را با ویژگی های داخلی مانند یادگیری ماشینی، تجزیه و تحلیل جغرافیایی و هوش تجاری مدیریت و تجزیه و تحلیل کنید. معماری بدون سرور BigQuery به شما امکان می دهد از پرس و جوهای SQL برای پاسخ به بزرگترین سؤالات سازمان خود با مدیریت زیرساخت صفر استفاده کنید. موتور تجزیه و تحلیل مقیاس پذیر و توزیع شده BigQuery به شما امکان می دهد ترابایت ها را در چند ثانیه و پتابایت ها را در چند دقیقه جستجو کنید.

نسخه TLDR آن BigQuery است که به شما امکان می دهد اطلاعات زیادی را به سرعت پردازش کنید. به‌علاوه، می‌تواند بخشی جدایی‌ناپذیر از نمای کلی تجزیه و تحلیل شما باشد و به شما کمک کند تا در معیارهای وب‌سایت و رفتار کاربر عمیق‌تر شوید.

آیا تا به حال در اکسل یا Google Sheets بوده اید و سعی کرده اید یک کاربرگ با صدها هزار ردیف را فیلتر کنید؟ پردازش این مقدار داده از طریق آن برنامه ها دشوار است و احتمالاً رایانه شما مسدود شده است.

BigQuery این مشکل را ندارد.

آنچه ممکن است چند دقیقه منتظر ماندن در کاربرگ‌نگار Google باشد، در BigQuery فقط چند ثانیه طول می‌کشد.

خوانندگان چشم عقابی احتمالاً این خط را نیز مشاهده کرده اند:

“معماری بدون سرور BigQuery به شما امکان می دهد از پرس و جوهای SQL برای پاسخ به بزرگترین سوالات سازمان خود با مدیریت زیرساخت صفر استفاده کنید.” بنابراین، SQL چیست و پرس و جوهای SQL چیست؟

SQL چیست؟

SQL (تلفظ ‘squel’) ‘زبان پرس و جو ساختاریافته’ است که برای پردازش، ذخیره و بازیابی داده ها از پایگاه داده رابطه ای استفاده می شود. به طور خلاصه، به شما امکان می دهد از یک پایگاه داده به آنچه می خواهید دسترسی داشته باشید.

در خارج، خواندن و درک SQL بسیار آسان است.

یک پرس و جو اولیه SQL از دستور SELECT FROM WHERE پیروی می کند.

اطلاعاتی را که می خواهید ببینید انتخاب کنید.
از جدولی که استفاده می کنید.
جایی که شرایط خاصی برآورده می شود.

در اینجا یک مثال سریع و بسیار اساسی آورده شده است:

تمام صفحات وب من را انتخاب کنید

از مجموعه داده/جدول mywebsite.table

صفحات WHERE پس از 1 مارس 2021 بیش از 5000 بازدید دریافت کردند.

عبارات و عبارات SQL بی‌شماری وجود دارد، اما زمان زیادی طول نمی‌کشد تا سر خود را در مورد اصول اولیه قرار دهید.

اتصال BigQuery و Google Search Console

اتصال دو ابزار گوگل بسیار آسان است، اما بسته به جایی که در این فرآیند هستید کمی زمان می برد.

شماره 1 صورتحساب را تنظیم کنید

برای اتصال BigQuery و Google Search Console، باید با اطمینان از انجام صورت‌حساب در حساب BigQuery خود شروع کنید.

برای انجام این کار، به BigQuery بروید و از منوی کناری گزینه Billing را انتخاب کنید.

از آنجا، می‌توانید پیوند حساب صورت‌حساب یا مدیریت حساب صورت‌حساب را انتخاب کنید. اگر شخصی در آژانس یا سازمان شما قبلاً یک حساب صورت‌حساب دارد، با او تماس بگیرید و از گزینه پیوند حساب صورت‌حساب بروید.

اگر نه، مدیریت حساب صورت‌حساب را انتخاب کنید و مراحل را طی کنید.

صبر کن، باید پرداخت کنم؟

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

در اینجا در Go Fish Digital، ما ردیابی کلمات کلیدی را از طریق یک ابزار شخص ثالث در BigQuery برای بیش از 100 سایت اجرا می کنیم، تعداد انگشت شماری از وب سایت ها را از GSC متصل می کنیم، و روزانه بیش از 5 گیگابایت درخواست را پردازش می کنیم.

کل هزینه ما؟

اکنون، این به شدت به شرایط شما بستگی دارد. اما در حال حاضر، قیمت BigQuery نسبتاً ارزان است. پردازش 5 ترابایت پرس و جو در ماه فقط 5 دلار است.

برای ذخیره سازی، حدود 0.02 دلار در هر گیگابایت است.

در اینجا می توانید اطلاعات بیشتری در مورد قیمت BigQuery بخوانید.

شماره 2 API را تنظیم کنید

به منوی کناری خود برگردید و به APIS and Services→ Enabled APIs and Services بروید.

برای اتصال به GSC باید مطمئن شوید که BigQuery فعال است.

می‌توانید به پایین اسکرول کنید و اگر BigQuery API را در لیست مشاهده کردید، می‌توانید ادامه دهید.

اگر نه، روی Enable APIS AND SERVICES در بالا کلیک کنید. BigQuery API را جستجو کنید، آن را انتخاب کنید و سپس آن را فعال کنید.

شماره 3 مجوزهای لازم را تنظیم کنید

برای اینکه GSC و BigQuery یکدیگر را تشخیص دهند، باید به BigQuery اجازه دسترسی به داده های GSC را بدهید.

به منوی کناری خود برگردید و روی IAM و Admin→ IAM کلیک کنید.

در بالا، روی GRANT ACCESS کلیک کنید.

در اینجا، New Principals را انتخاب کنید و نام حساب را در آن قرار دهید:

[email protected].

باید به آن دو نقش جدید بدهید، BigQuery Job User و BigQuery Data Editor. پس از انجام این کار، ذخیره را بزنید.

شماره 4 کنسول جستجوی گوگل را راه اندازی کنید

تقریباً به پایان!

به تنظیمات → صادرات انبوه داده بروید.

در اینجا، شما می خواهید ID پروژه خود را وارد کنید که می توانید در اینجا پیدا کنید. این به حروف کوچک و بزرگ حساس است، بنابراین آن را همانطور که دارید کپی کنید.

برای نام مجموعه داده، می توانید آن را به عنوان کنسول جستجو بگذارید. بسیاری از آموزش ها و پرس و جوهای آنلاین آن را به همین شکل رها کرده اند و پیگیری آن را آسان کرده اند.

در نهایت روی Continue کلیک کنید، منتظر بمانید تا تایید شود و کارتان تمام شد!

تا 48 ساعت طول می کشد تا در BigQuery نمایش داده شود، اما من دیده ام که برخی از داده ها پس از تنها 12 ساعت شروع به پر شدن می کنند.

پیمایش در داشبورد SQL Workspace

وقتی شروع به جمع آوری داده ها کردید، حالا چه؟

قبل از اینکه بخواهیم در مورد برخی از پرسش‌هایی که می‌توانیم از آن‌ها استفاده کنیم غوطه‌ور شویم، بیایید یک بررسی سریع از داشبورد فضای کاری SQL انجام دهیم.

در سمت چپ، نام پروژه خود را خواهید دید و در آنجا نام پروژه خود را پیدا خواهید کرد. در زیر نام پروژه خود، مجموعه داده ها و جداول خود را خواهید دید.

برای شروع پرس و جو، باید روی یکی از جداول کلیک کنید. وقتی این کار را انجام دادید، طرح جدول را خواهید دید.

می‌توانید پیش‌نمایش را انتخاب کنید و برخی از اطلاعاتی را که می‌خواهید شروع به پرس‌وجو کنید ببینید.

در بالای این، تعدادی گزینه از Query تا Export را مشاهده خواهید کرد. برای شروع یک پرس و جو، ادامه دهید و روی Query کلیک کنید. می توانید «در برگه جدید» یا «در برگه تقسیم» را انتخاب کنید.

پرسش‌های مفید SQL برای داده‌های کنسول جستجوی Google

اکنون به بخش پرسش‌های نوشتن می‌رسیم. همانطور که در بالا گفته شد، برای استفاده بهینه از BigQuery، نیازی به درک زیر و بم SQL ندارید.

Google قبلاً چند سؤال مفید منتشر کرده است که شما را شروع می کند. این پرس و جوها ساده هستند و به شما کمک می کنند تا داده های خود را به سرعت تجزیه و تحلیل کنید.

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

همه آمار وب برای آخرین دوره X

SELECT
data_date AS date,
sum(impressions) AS impressions,
sum(clicks) as clicks,
sum(clicks) / sum(impressions) AS ctr,
((sum(sum_top_position) / sum(impressions)) + 1.0) AS avg_position
FROM searchconsole.searchdata_site_impression
WHERE search_type="WEB"
AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY data_date
ORDER BY date desc;

این پرس و جو تمام آمار وب شما را در دو هفته گذشته انتخاب می کند. تاریخ، برداشت‌ها، کلیک‌ها، نرخ کلیک و موقعیت متوسط ​​را نمایش می‌دهد.

این یک نسخه اصلاح شده از اولین پرس و جو از لیست بالا است زیرا همان درخواستی که گوگل دارد در واقع خواهد بود یک خطا پرتاب کنید در BigQuery

من همچنین LIMIT را حذف کردم تا بتوانیم بیش از 1000 نتیجه دریافت کنیم.

اگر می‌خواهید حتی بیشتر به عقب برگردید، می‌توانید عدد را بعد از INTERVAL به چند روز که می‌خواهید تغییر دهید. INTERVAL تعداد روزها را از CURRENT_DATE تنظیم می کند. بنابراین اگر می‌خواهید 30 روز به عقب برگردید، آن را به “INTERVAL 30 روز” تغییر دهید. اگر می‌خواهید 300 روز به عقب برگردید، آن را به “INTERVAL 300 روز” تغییر دهید.

همه ترافیک بر اساس URL

SELECT
url,
sum(impressions) as impressions,
sum(clicks) as clicks
FROM searchconsole.searchdata_url_impression
WHERE data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1
ORDER BY 3 desc;

این یکی دیگر از پرس و جوهای پیشنهادی گوگل است که اصلاح شده است. این یک راه آسان برای مشاهده کلیک‌ها و نمایش‌های خود از طریق URL است.

این گزارش به شرح زیر است:

در حالی که به نظر می رسد تقریباً با آنچه GSC برای شما صادر می کند یکسان است، به چند دلیل مفید است:

• شما به راحتی می توانید محدودیت 1000 ردیفی را که GSC اغلب روی صادرات شما می گذارد دور بزنید.
• با ایجاد این پرس و جو، می توانید آن را به استودیوی Looker “پیش فیلتر شده” ارسال کنید. فیلترهای زیاد می توانند گزارش های Looker Studio را کاهش دهند.

ترافیک بر اساس URL و پرس و جو

SELECT
query,
url,
sum(impressions) AS impressions,
sum(clicks) AS clicks
FROM searchconsole.searchdata_url_impression
WHERE data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE() /*can change the interval number and type for more or less data*/
AND query IS NOT null
GROUP BY 1,2
ORDER BY 2,3 desc;

یکی از ناراحتی های اصلی کنسول جستجوی گوگل این است که نمی توانید همه پرس و جوها و URL های آنها را با هم صادر کنید. اگر می خواهید عملکرد کلمه کلیدی را بر اساس صفحه بررسی کنید، باید بارها و بارها صفحه جداگانه را انتخاب کنید. بیشتر نه! در اینجا پرس و جو برای انتخاب همه کلمات کلیدی و URL آنها برای کلیک و نمایش وجود دارد.

این به راحتی به شما امکان می دهد به جای فیلتر کردن برای هر URL جداگانه، عملکرد URL را به صورت دسته ای مشاهده کنید.

مقایسه ترافیک URL و جستجو در طول زمان

SELECT
query,
url,
SUM(CASE WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND CURRENT_DATE() THEN impressions ELSE 0 END) AS impressions_14d,
SUM(CASE WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND CURRENT_DATE() THEN clicks ELSE 0 END) AS clicks_14d,
SUM(CASE WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) THEN impressions ELSE 0 END) AS impressions_14_28d,
SUM(CASE WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) THEN clicks ELSE 0 END) AS clicks_14_28d
FROM searchconsole.searchdata_url_impression
WHERE query IS NOT NULL
AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
GROUP BY 1, 2
ORDER BY 2, 3 DESC;

این پرس و جو همانند پرس و جو فوق انجام می دهد و داده های مقایسه را در دوره قبل جمع آوری می کند.

در این مثال خاص، داده های 14 روز گذشته و دوره 14 روزه قبل را استخراج می کند.

اگر می خواهید زمان را تنظیم کنید، باید شماره INTERVAL DAY را تغییر دهید. به عنوان مثال، اگر می خواهید به عقب برگردید و عملکرد 50 روز گذشته و دوره 50 روزه قبل از آن را بررسی کنید.

SELECT
query,
url,
SUM(CASE WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 50 DAY) AND CURRENT_DATE() THEN impressions ELSE 0 END) AS impressions_14d,
SUM(CASE WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 50 DAY) AND CURRENT_DATE() THEN clicks ELSE 0 END) AS clicks_14d,
SUM(CASE WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 100 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 50 DAY) THEN impressions ELSE 0 END) AS impressions_14_28d,
SUM(CASE WHEN data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 100 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 50 DAY) THEN clicks ELSE 0 END) AS clicks_14_28d
FROM searchconsole.searchdata_url_impression
WHERE query IS NOT NULL
AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 100 DAY) AND CURRENT_DATE()
GROUP BY 1, 2
ORDER BY 2, 3 DESC;

داستان های برتر

SELECT
COUNT(CASE
WHEN is_amp_top_stories=TRUE THEN 1
END
) AS top_stories,
data_date
FROM
`searchconsole.searchdata_url_impression`
WHERE
url IS NOT NULL
GROUP BY
data_date
HAVING COUNT(CASE WHEN is_amp_top_stories=TRUE THEN 1 END) > 0
ORDER BY
data_date DESC;

اگر یک سایت خبری راه اندازی می کنید، احتمالاً به داستان های برتر علاقه مند هستید. متأسفانه، GSC راه آسانی برای نگاه کردن به داستان های برتر ارائه نمی دهد.

با BigQuery، می‌توانید به عملکرد داستان‌های برتر خود نگاه کنید. این پرس و جو به شما امکان می دهد ببینید چند بار یک داستان در داستان های برتر ظاهر شده است.

یکی از اشکالات اینجا این است که فقط داستان های برتر AMP را ردیابی می کند، اما همچنان می تواند نگاهی به عملکرد کلی سایت شما ارائه دهد!

در اینجا یک نمونه از نتایج است:

همچنین می‌توانید این عبارت را تغییر دهید تا ببینید کدام نشانی‌های وب بیشتر در داستان‌های برتر ظاهر می‌شوند.

SELECT
url,
COUNT(CASE
WHEN is_amp_top_stories=TRUE THEN 1
END
) AS top_stories
FROM
`brobiblegsc.searchconsole.searchdata_url_impression`
WHERE
url IS NOT NULL
GROUP BY
data_date,
url
HAVING top_stories>1
ORDER BY
top_stories DESC;

اگر می‌خواهید داده‌های داستان‌های برتر خود را ببینید، می‌توانید از عبارت زیر استفاده کنید:

SELECT
url,
sum(impressions) AS impressions,
sum(clicks) AS clicks,
sum(clicks) / sum(impressions) AS click_through_rate
FROM searchconsole.searchdata_url_impression
WHERE search_type="WEB"
AND is_amp_top_stories = true
AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1
ORDER BY impressions DESC;

این کار، کلیک‌ها، نمایش‌ها و نرخ کلیک مقالات شما را که در داستان‌های برتر ظاهر می‌شوند به شما نشان می‌دهد.

در واقع، شما می توانید پرس و جوهای مشابه را با استفاده از هر یک از گزینه های قطعه ای که BigQuery دارد اجرا کنید.

نتیجه

همانطور که می بینید، گزینه های زیادی برای استفاده از BigQuery و GSC وجود دارد. شما می توانید داده های زیادی را تنها با چند کلیک به دست آورید، آن ها را به گزارش های خود صادر کنید و بینش بهتری در مورد عملکرد ارگانیک خود به دست آورید.

هر سوالی دارید؟ حتما تماس بگیرید تا بتوانیم در مورد استراتژی و اهداف دیجیتال مارکتینگ شما صحبت کنیم.