خودکارسازی فرایند محاسبه SOV در سئو

سلام، من میثمم

اگه متخصص سئو هستید، احتمالا تا حالا SOV یا Share of Voice به گوشتون خورده؛ اگه به گوشتون خورده احتمالا رفتید سمتش و برای بیزینسی که توش کار سئو انجام میدید SOV رو محاسبه کردید؛ اگه هیچ آشنایی باهاش ندارید به نظرم قبل از خوندن این مقاله برید یکم در موردش سرچ کنید. قبلا حجت مسگری توی یه ویدیوی آموزشی روش محاسبه SOV رو توضیح داده که از طریق این لینک می‌تونید ببینید.

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

برای اتومیت کردن محاسبه SOV به چه چیزهای نیاز داریم؟

• یه ابزار Keyword Rank Tracker به همراه API
• گوگل شیت
• اسکریپت اتصال گوگل شیت به Rank Tracker
• اکستنشن استخراج دیتای سرچ کنسول در گوگل شیت
• لوکر استودیو

دریافت پوزیشن کیوردها از ابزار Rank Tracker

تو مرحله اول ما نیاز داریم که پوزیشن کیوردهای اون صنعت رو به دست بیاریم. پس به لیست کیوردهایی که قبلا از طریق کیورد ریسرچ به دست آوردیم نیاز داریم.

فرض کنید ما 2000 کیورد داریم؛ حالا باید با استفاده از ابزارهای Rank Tracker، پوزیشن این کیوردها برای سایت خودمون و رقبامون رو به دست بیاریم.

ابزارهای Rank Tracker ایرانی و خارجی زیادی وجود داره، مدل خارجی‌ای که من باهاش کار کردم و امکانات فوق‌العاده‌ای داشت Nightwatch بود، مدل ایرانی هم که استفاده کردم و توی این مقاله هم با اون پیش میریم KWrank هست که ابزار نسبتا خوبیه.

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

حالا برای ادامه راه نیاز داریم که لیست کلمات و پوزیشن‌هاشون برای هر سایت رو توی گوگل شیت داشته باشیم. اینجا وقت استفاده از APIهستش.

هر ابزار Rank Tracker ای که استفاده کنیم معمولا بهمون API هم اختصاص میده حالا ممکنه هزینه بیشتری دریافت کنن اما زیاد نیست.وقتی API رو دریافت کنیم وارد گوگل شیت میشیم و از قسمت Extensions به بخش Apps Script میریم.

چیزی که خواهیم دید یه همچین فضایی هستش:

کاری که باید انجام بدیم اینه که اسکریپتی بنویسیم که بتونیم با استفاده از API ای که ابزار Rank Tracker در اختیارمون قرار داده، کیوردها به همراه پوزیشن اون‌ها رو توی گوگل شیت دریافت کنیم.

اگه برنامه نویسی بلد نیستید الان ممکنه ادامه مقاله رو بیخیال شید و بگید سخته من که نمی‌تونم، ولی منم برنامه نویس نیستم! پس چیز وحشتناکی نیست.

شما تشریف می‌برید ChatGPT و یه پرامپت خیلی ساده و شفاف بهش میدید و توضیح میدید که دقیقا چه اسکریپتی می‌خواید.

خلاصه‌اش اینه که یه اسکریپت گوگل شیت می‌خوایم که با API ای که بهش می‌دیم کیوردها و پوزیشن اون‌ها رو توی گوگل شیت بهمون برگردونه.اسکریپت رو که نوشتیم یا به دست آوردیم Run رو می‌زنیم تا اجرا بشه.

اگه خواستید به من پیام بدید تا اسکریپت رو براتون بفرستم.

در نهایت باید دیتا رو به شکل زیر توی گوگل شیت دریافت کنیم:

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

خب حالا ما پوزیشن همه کیوردها رو داریم. اما یه سوال مهم:

ما اگه هر روز یا هر هفته بخوایم پوزیشن‌های جدید رو توی گوگل شیت داشته باشیم هر دفعه باید بیایم Run رو بزنیم؟

نخیر! :))

توی Apps Script از منوی سمت چپ به بخش Triggers میریم و اونجا می‌تونیم تنظیم کنیم که اسکریپت به صورت دوره‌ای، مثلا روزانه یا هفتگی Run و دیتا آپدیت بشه.

این قسمت رو دیگه زیاد توضیح نمیدم سوالی داشتید ازم بپرسید حتما.

خب تا اینجا ما کاری کردیم که پوزیشن کیوردهامون به صورت دوره ای و کاملا خودکار توی گوگل شیت بهمون نمایش داده بشه.

بریم سراغ ادامه کار؛

دریافت ایمپرشن (سرچ ماهانه) کیوردها از سرچ کنسول

ما برای محاسبه SOV به غیر از پوزیشن کیوردها به چیزهای دیگه ای هم نیاز داریم که یکیش میزان سرچ ماهانه کیوردها هست.

برای دریافت میزان سرچ ماهانه کیوردها هم میشه از ابزارهای کیورد ریسرچ استفاده کرد هم از خود سرچ کنسول. اگه می‌خواید عددهای دقیق داشته باشید خب قاعدتا بهتره این دیتا رو از سرچ کنسول بگیرید.

برای اینکه بتونیم دیتای دقیقی از سرچ کنسول روی گوگل شیت داشته باشیم باید از اکستنشن Search analytics for sheets توی گوگل شیت استفاده کنیم.

وقتی این اکستنشن رو نصب کنیم می‌تونیم توی گوگل شیت از طریق منوی Extensions بهش دسترسی داشته باشیم و اطلاعات رو از سرچ کنسول خودمون دریافت کنیم.
اکستنشن رو که باز کنیم چندتا گزینه می‌بینیم که ما با گزینه دوم یعنی Recurrent Requests کار داریم.

با استفاده از این امکان، ما می‌تونیم اکستنشن رو طوری تنظیم کنیم که به صورت روزانه یا ماهانه به سرچ کنسول ما ریکوئست بزنه و دیتا رو استخراج کنه.

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

دقت کنید که گزینه Results Sheet رو حتما روی یه شیت خاص بذاریم که هر سری دیتا خواست آپدیت بشه روی همون شیت آپدیت بشه، چون قراره فرمول بزنیم و از دیتا استفاده کنیم پس اگه هر سری که اکستنشن ریکوئست میزنه دیتا بره توی یه تب جدید، فرمول روش اعمال نمیشه و دیتا آپدیت نمیشه.

در نهایت ما توی گوگل شیت دیتای زیر رو خواهیم داشت، که با ایمپرشن و پوزیشن کار داریم.

احتمالا الان 2 تا سوال توی ذهنتون پیش اومده:

سوال اول: اصلا چرا ما برای دریافت پوزیشن از ابزار Rank Tracker استفاده کردیم؟ چرا از پوزیشنی که خود سرچ کنسول بهمون میده استفاده نکردیم؟

جواب خیلی ساده‌اس، سرچ کنسول پوزیشن رقبا رو بهمون نمیده و ما برای محاسبه و مقایسه SOV پوزیشن رقبا رو هم نیاز داریم.

سوال دوم: پس چرا الان گفتی از دیتای سرچ کنسول با ایمپرشن و پوزیشن کار داریم؟ پوزیشن رو چیکار داریم دیگه؟!

جواب این سوال رو یکم جلوتر میدم :)

خب یه مرور کنیم ببینیم تا اینجا چیکار کردیم؛

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

پس ما الان این 3تا رو داریم:

• کیوردها
• پوزیشن کیوردها
• ایمپرشن کیوردها

انجام محاسبات

توی همون گوگل شیتی که پوزیشن کیوردها و دیتای سرچ کنسول رو دریافت کردیم یه تب جدید باز میکنیم و اسمش رو می‌ذاریم SOV.

حالا ما توی گوگل شیت 3 تا تب داریم، تب SOV که می‌خوایم محاسبات رو توش انجام بدیم، تب Search Console Data که دیتایی که از سرچ کنسول گرفتیم داخلشه و تب KWrank Data که دیتای پوزیشن کیوردهای خودمون و رقبامون داخلشه.

میریم سراغ تب SOV که تازه ایجادش کردیم؛ توی ستون A کل کیوردهایی که داریم رو وارد می‌کنیم (دقیقا همون کیوردهایی که به ابزار KWrank دادیم تا پوزیشن خودمون و رقبامون رو توی اون ها بهمون بده).

توی ستون B، باید سرچ ماهانه این کیوردها رو وارد کنیم، حالا اینو از کجا میاریم؟ آفرین از تب Search Console Data.

اگه فکر کردید الان میریم دونه دونه سرچ ماهانه کیوردهارو برمیداریم میاریم می‌ذاریم جلوی کیوردها باید بگم که هنوز موضوع این مقاله رو درک نکردید.ما توی این فرایند تقریبا هیچ کاری رو دستی انجام نمیدیم و همه چی قراره اتومیت پیش بره.

اگه قرار باشه سرچ ماهانه رو به صورت دستی برداریم و بذاریم جلوی کیوردها، سری بعدی که اکستنشن به سرچ کنسول ریکوسئت میزنه و دیتای جدید رو میگیریه، دیتای تب SOV دیگه آپدیت نمیشه چون ما دستی واردش کردیم!

پس فکر اینکه چیزی رو دستی وارد کنیم رو بذارید کنار و بیاید ادامه بدیم.

برای اینکه سرچ ماهانه کیوردهارو از تب اطلاعات سرچ کنسول دریافت کنیم باید از VLOOKUP استفاده کنیم.

حالا یه سوالی اینجا پیش میاد:

ما داریم سرچ ماهانه رو از سرچ کنسول میگیریم، اگه یه سری از کیوردها صفحه 2 باشن یعنی ایمپرشن دقیقشون رو نداریم و این باعث میشه دیتا اشتباه باشه، باید چیکار کنیم؟

ما روی این موضوع فکر کردیم و دیدیم بهترین کار اینه که بیایم چندتا از کیوردهایی که الان صفحه 2 هستن و قبل صفحه 1 بودن رو انتخاب کنیم و ایمپرشن وقتی که صفحه 2 هستن رو با ایمپرشن وقتی که صفحه 1 بودن مقایسه کنیم.در نهایت با این مقایسه می‌فهمیم که باید یه ضریبی به ایمپرشن کیوردهایی که پوزیشنشون 10 و بالاتر هست بدیم تا دیتا به واقعیت نزدیک‌تر بشه؛ این پیشنهاد حجت مسگری بود و دمش گرم.

پس ما با استفاده از VLOOKUP توی اولین سلول ستون B به گوگل شیت میگیم:

برو توی تب Search Console data، کیوردهایی که برات تعیین کردم رو پیدا کن، ایمپرشن کیوردهارو از ستون های جلوییش بردار، بعد نگاه کن ببین پوزیشن کیوردها زیر 10 هستش یا بالای 10، اگه زیر 10 بود همون ایمپرشن رو برام بیار اما اگه بالای 10 بود ضربدر فلان عدد کن و عدد نهایی رو برام بیار و بنداز اینجا.

یادتونه بالاتر گفتم بعدا میگم چرا از پوزیشن سرچ کنسول هم کار داریم؟ دقیقا همینجا بود.

در نهایت فرمولش میشه یه همچین چیزی:

=IF('GSC Data'!E4>10,VLOOKUP(A4,'GSC Data'!A:E,3,false)*10,VLOOKUP(A4,'GSC Data'!A:E,3,false))

سعی کنید کار با VLOOKUP رو یاد بگیرید چون خیلی خفنه و راحتم هست،ولی اگه براتون سخته اصلا از فرمول بالا نترسید کافیه بازم به ChatGPT بگید همچین کاری می‌خواید کنید و فرمول رو براتون بنویسه و توضیح بده.

وقتی فرمول رو اجرا کنیم سرچ ماهانه کیورد رو دریافت می‌کنیم و جلوی کیوردمون داریم، و همونطور که احتمالا می‌دونید با 2 تا کلیک سمت راست پایین سلول اول می‌تونیم این فرمول رو برای همه کیوردها تا آخرین کیورد اجرا کنیم.
حالا با استفاده از همین فرمول VLOOKUP میریم و پوزیشن کیوردهارو هم، از تب KWrank برمیداریم و میاریم توی تب SOV توی ستون C قرار میدیم.اول از سایت خودمون شروع می‌کنیم، اسم ستون C رو می‌ذاریم مثلا Bimebazar Rank

در نهایت یه همچین شیتی خواهیم داشت:

حالا ما با استفاده از ایمپرشن و پوزیشن هر کیورد، سهم سایتمون از سرچ اون کیورد رو به دست خواهیم آورد، اما قبلش باید یه چیز دیگه هم داشته باشیم، اونم CTR هر پوزیشن هستش. باید بدونیم هر پوزیشن توی صنعت ما داره چند درصد کلیک رو به خودش اختصاص میده تا بتونیم SOV رو محاسبه کنیم.

یکی از راه‌ها برای به دست آوردن CTR حوزه خودمون اینه که بریم چندتا کیورد رو توی سرچ کنسول بررسی کنیم و میانگین CTRشون رو به دست بیاریم.

ما میانگین CTR از پوزیشن 1 تا 12 رو به دست آوردیم.نظرمون این بود که پوزیشن 12 به بعد عملا کلیک خاصی نمی‌گیره یا اگه می‌گیره انقدری ناچیز هست که نبودنش دیتا رو خراب نمی‌کنه.

در نهایت وقتی CTR هارو به دست آوردیم یه تب جدید توی شیت ایجاد می‌کنیم و اسمش رو می‌ذاریم CTR و تو ستون اول پوزیشن 1 تا 12 و تو ستون دوم CTRشون رو وارد می‌کنیم.

الان دیگه وقتشه SOV رو برای هر کیورد محاسبه کنیم.

توی تب SOV یه ستون جدید اضافه می‌کنیم و اسمش رو می‌ذاریم Bimebazar Traffic

توی ردیف 2 از این ستون شروع می‌کنیم به فرمول نوشتن و دوباره از VLOOKUP استفاده می‌کنیم.

مثل فرمول قبلی اول بگم دقیقا با فرمول چیکار می‌کنیم بعد خود فرمول رو ببینیم.

ما توی این فرمول به گوگل شیت میگیم برو از ستون C که من توش پوزیشن کیوردهام رو وارد کردم اولین عدد رو بردار، بعدش برو به تب CTR و اون عدد رو پیدا کن و از ستون جلوییش ببین چند درصد CTR داره؛ بعد عدد CTRاش رو بردار و برگرد به شیت SOV و از ستون سرچ ماهانه، ضربدر اولین عددی کن که اونجاس.

توضیح خیلی ساده‌اش اینه که CTR رو در ایمپرشن ضرب می‌کنیم و ترافیک رو به دست میاریم.

فرمولی که استفاده کردیم:

=IFERROR(VLOOKUP(C2,CTR!A:B,2,false)*$B2,0)

اون کلمه IFERROR اول و عدد 0 آخر فرمول واسه اینه که اگه یه کیورد هیچ ترافیکی برامون نداشت عدد 0 رو براش بندازه و ارور نده.

در نهایت شیت به این شکل میشه:

حالا ما ترافیکی که از هر کیورد داریم می‌گیریم رو به دست آوردیم.مرحله آخر اینه که به درصد محاسبه اش کنیم و بفهمیم ما چند درصد از ترافیک رو داریم برای هر کیورد دریافت می‌کنیم. پس یه ستون دیگه به شیت اضافه می‌کنیم و اسمش رو می‌ذاریم Bimebazar SOV

توی ردیف دوم از این ستون یه فرمول خیلی ساده می‌زنیم. توی فرمول میگیم ترافیک رو تقسیم بر ایمپرشن کن. و فرمت اون ستون رو Percent میذاریم.

فرمول ساده:

=D2/B2

در واقع D ستون ترافیک هست و B ستون ایمپرشن یا سرچ ماهانه

نتیجه نهایی:

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

کار آخر اینه که همین ستون هایی که برای بیمه بازار اضافه کردیم و فرمول هایی که زدیم رو برای سایت های رقیب هم انجام بدیم و SOV اونها رو هم محاسبه می‌کنیم. فقط کافیه ستون هارو بسازید و فرمول هارو کپی کنید.

داکیومنت مربوط به SOV در نهایت این ستون هارو خواهد داشت:

حالا طبق زمانبدی که روی اسکریپت و APIهامون مشخص کردیم هروقت دیتای جدید دریافت بشه همه این اعداد طبق فرمول هایی که زدیم عوض میشه و SOV کاملا اتومیت محاسبه میشه و سهممون از بازار رو می‌تونیم به صورت آنلاین ببینیم.

یه سری دیتای دیگه هم میشه به دست آورد که من دیگه چون مقاله طولانی میشه توضیح ندادم. اما مثلا می‌تونید فرصت ها و پتانسیل رشد رو برای هر کیورد محاسبه کنید. به این صورت که ایمپرشن کلش رو برمیدارید و ترفیک فعلی رو ازش کم میکنید.

32.1% از عدد نهایی میشه پتانسیل رشد یا ترافیکی که شما در بهترین حالت می‌تونید از اون کیورد دریافت کنید. 32.1% هم که همون CTR پوزیشن 1 توی این صنعت هستش.

مرحله فینال اینه که برای این دیتا یه داشبورد لوکر استودیو داشته باشیم که خیلی شیک و خوشگل دیتا رو به صورت آنلاین و آپدیت ببینیم.

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

3تا عکس زیر نمونه‌ای از بخش‌های داشبوردی هست که میشه توی لوکر استودیو ساخت و SOV رو دید. (نمودار و درصدها نمایشی هستند)

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

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

در کل سعی کردم طوری مقاله رو بنویسم که واقعا اگر کسی خواست بتونه طبق توضیحات پیش بره و محاسبه SOV رو اتومیت کنه.

هر سوال یا نظری داشتید خوشحال میشم بشنوم.

فعلا :)