10 فرمول Google Sheets که هر SEO باید بداند

مطمئنم اکثر شما  تا حدودی با Google Sheets آشنا هستید (اگر نه، اساساً مانند اکسل است، اما مبتنی بر ابر و کاملاً رایگان است) و می‌دانید که در هنگام همکاری چقدر می‌تواند قدرتمند باشد.

اما، قابلیت‌های آن بسیار فراتر از همکاری.

کاربرگ‌نگار Google می‌تواند برای حذف داده‌ها از وب‌سایت‌ها، ایجاد گردش‌های کاری نیمه خودکار SEO، دستکاری مجموعه‌های کلان داده (مانند Site Explorer)، پیگیری خودکار برای کمپین های اطلاع رسانی، و بسیاری موارد دیگر.

در این پست، 10 Google Sheets را به شما معرفی خواهم کرد. فرمول ها را نشان دهید و نشان دهید که چگونه می توانید از آنها برای کارهای سئوی روزمره استفاده کنید.

بیایید با اصول اولیه شروع کنیم…

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

مهم نیست که چه نوع کار سئو در Google Sheets انجام می‌دهم، می‌بینم که از این سه فرمول (تقریبا) هر بار استفاده می‌کنم. time:

بیایید با یک عبارت IF شروع کنیم.

این بسیار ساده است. از آن برای بررسی اگر یک شرط درست یا نادرست استفاده می شود.

نحو: =IF(condition, value_if_true, value_if_false)

در اینجا یک صفحه گسترده نمونه حاوی لیستی از کلمات کلیدی با حجم جستجوی تخمینی مربوطه است (توجه: این صفحه‌گسترده‌ها با استفاده از Keyword Explorer):

اجازه دهید فرض کنیم، به صورت فرضی، یک سایت قوی داریم که قادر به رتبه 1 برای هر یک از این کلمات کلیدی است. با این حال، ما فقط می‌خواهیم به دنبال کلمات کلیدی باشیم که احتمالاً بیش از 500 بازدیدکننده در ماه می‌آورند (با فرض اینکه رتبه اول را داریم).

طبق این مطالعه، رتبه‌بندی شماره 1 در ایالات متحده (فقط جستجوهای رومیزی) تقریباً 29٪ CTR دارد.

بنابراین، بیایید یک عبارت IF بنویسیم که برای کلمات کلیدی که احتمالاً بیش از 500 بازدیدکننده دارند (یعنی آنهایی که 29٪ از حجم جستجو بزرگتر یا مساوی 500 است) “GOOD” و “BAD” برای بقیه موارد.

این فرمول است:

=IF(B2*0.29>=500,"GOOD","BAD")

این کار این است (به انگلیسی ساده):

1. بررسی می کند به اگر B2*0.29 مراجعه کنید (یعنی 29٪ از حجم جستجو) بزرگتر یا مساوی 500 است؛
2. اگر شرط درست باشد، “خوب” را برمی گرداند. اگر نادرست باشد، “BAD” را برمی‌گرداند.

این برای مجموعه داده‌های فعلی ما بسیار خوب عمل می‌کند، اما ببینید وقتی چه اتفاقی می‌افتد مقداری غیر عددی را در ترکیب قرار می دهیم:

این یک خطا است.

این اتفاق می‌افتد زیرا ضرب یک مقدار غیرعددی در 0.29 غیرممکن است (بدیهی است).

این جایی است که IFERROR به کار می‌آید.

IFERROR به شما امکان می‌دهد یک مقدار پیش‌فرض را در صورتی که فرمول منجر به خطا شود، تنظیم کنید.

Syntax: =IFERROR(original_formula, value_if_error)

بیایید این را در مثال بالا بگنجانیم (اگر وجود داشته باشد سلول را خالی می‌گذاریم خطا) و ببینید چه اتفاقی می افتد:

عالی – این فرمول کامل است!

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

اما، با توجه به این واقعیت که این راهنما برای سئو است، من فرض می‌کنم که شما به طور منظم با مقادیر نسبتاً زیادی از داده‌ها کار می‌کنید.

اگر اینطور باشد، من این کار را انجام می‌دهم. این احتمال وجود دارد که حدس بزنید که زمان زیادی را صرف کشیدن فرمول ها در صدها، احتمالاً حتی هزاران سلول می کنید.

وارد کنید: ARRAYFORMULA.

> نحو: =ARRAYFORMULA(array_formula)

در اصل، یک ARRAYFORMULA فرمول اصلی شما را به یک آرایه تبدیل می‌کند، بنابراین به شما امکان می‌دهد همان فرمول را در چندین ردیف توسط فقط یک فرمول را بنویسید.

بنابراین، بیایید همه فرمول‌های سلول B2 را به بعد حذف کنیم و کل فرمول موجود در سلول B1 را در یک ARRAYFORMULA بپیچیم، مانند :

=ARRAYFORMULA(IFERROR(IF(B2:B29*0.29>=500,"GOOD","BAD"),""))

Magic.

اینها اصول اولیه هستند. بیایید به چند فرمول مفیدتر نگاهی بیندازیم.

1. از REGEXTRACT برای استخراج داده ها از رشته ها استفاده کنید

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

Syntax: =REGEXEXTRACT(text, regular_expression)

در اینجا فقط تعدادی از موارد استفاده بالقوه برای این مورد آمده است:

• استخراج نام دامنه از لیست URL ها (برای دیدن نمونه به خواندن ادامه دهید)؛
• نشانی وب را استخراج کنید (یعنی بدون دامنه اصلی)؛
• بررسی کنید که آیا URL از HTTP یا HTTPS استفاده می‌کند یا نه؛
• آدرس‌های ایمیل را از یک متن بزرگ استخراج کنید؛
• نشانی‌های اینترنتی را با یا بدون کلمات خاص در آن‌ها از فهرستی از URLها شناسایی کنید (مثلاً نشانی‌های اینترنتی حاوی حلزون “/category/guest-post”).

بیایید فرض کنیم می‌خواهیم دامنه‌های ریشه را از فهرست URLهای صفحه «نوشتن برای ما» استخراج کنیم (یعنی فرصت‌های پست مهمان).

در ستون B، می‌توانیم یک فرمول REGEXTRACT برای انجام این کار بنویسیم.

در اینجا دستور regex مورد نیاز است: ^(?:https?://)?(? :[^@n][email protected])؟(?:www .)؟([^:/n]+)

فرمول نهایی ما اینجاست:

=REGEXEXTRACT(A2,"^(?:https?://)?(?:[^@n] [email protected])?(?:www.)?([^ :/n]+)")

این را در سلول B2 جای‌گذاری کنید و، از قبل، ما دامنه را استخراج کردیم.

بیایید این را در یک ARRAYFORMULA و IFERROR بپیچیم تا کل ستون کامل شود.

=IFERROR(ARRAYFORMULA(REGEXEXTRACT(A2:A,"^(?:https?://)?(?:[^@n][email protected])?(?:www.)?([^:/n] +)"))"")

2. رشته ها را به چند نقطه داده تقسیم می کند

SPLIT رشته ها را با استفاده از یک جداکننده به قطعات تقسیم می کند (یعنی تقسیم می کند).

نحو: =SPLIT(متن، جداکننده)

در اینجا فقط تعدادی از موارد استفاده بالقوه برای این مورد آمده است:

• Split نام کامل مشتری به ستون‌های «نام» و «نام خانوادگی»؛
• یک URL را به ۳ ستون برای پروتکل HTTP، دامنه ریشه تقسیم کنید، و URL Slug؛
• لیستی از مقادیر جدا شده با کاما را به چندین ستون تقسیم کنید؛
• یک دامنه ریشه را به ۲ ستون برای نام دامنه تقسیم کنید. و افزونه دامنه (به عنوان مثال .com، .org، و غیره)

من لیست خوبی از اعضای تیم Ahrefs (نام کامل) در یک صفحه گسترده.

در اینجا یک فرمول SPLIT ساده است که می‌توانیم از آن در سلول B2 برای تقسیم آنها به نام و نام خانوادگی استفاده کنیم:

=SPLIT(A2," ")

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

=IFERROR(ARRAYFORMULA(SPLIT (A2:A," "))"")

در اینجا فرمول مثال دیگری وجود دارد که دامنه‌های ریشه‌ای را به نام سایت و پسوند دامنه تقسیم می‌کند:

=SPLIT( A2,".")

3. ادغام چندین مجموعه داده با استفاده از VLOOKUP

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

Syntax: =VLOOKUP(search_key, range, index_key)

در اینجا فقط تعداد انگشت شماری از موارد استفاده بالقوه برای این مورد آمده است:

• ادغام داده‌ها از چندین منبع (به عنوان مثال ادغام فهرستی از دامنه‌ها با رتبه‌بندی‌های Ahrefs DR مربوطه از یک برگه جداگانه) ;
• بررسی وجود یک مقدار در مجموعه داده دیگری (مثلاً بررسی موارد تکراری در دو یا چند لیست از مشتریان احتمالی)؛
• کشیدن آدرس‌های ایمیل (از پایگاه داده اصلی مخاطبین) ) در کنار فهرستی از مشتریان بالقوه.

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

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

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

=VLOOKUP(D2:D,'Master contact database'!A:C,2)

خوب، بیایید همین کار را برای ستون ایمیل انجام دهیم. ما همچنین هر دو فرمول را در یک IFERROR و ARRAYFORMULA قرار می دهیم.

=IFERROR(ARRAYFORMULA(VLOOKUP(D2:D,'Master contact database'!A:C,3)),"" )

4. خراش دادن داده ها از هر وب سایتی با استفاده از IMPORTXML

IMPORTXML به شما امکان می دهد داده ها را (با استفاده از پرس و جوی XPath) از تعدادی از انواع داده های ساختاریافته از جمله XML، HTML و RSS (در میان دیگر).

به عبارت دیگر، می توانید خراش دهید وب بدون خروج از Google Sheets!

Syntax: =IMPORTXML(url, xpath_query)

در اینجا فقط تعدادی از موارد استفاده بالقوه برای این مورد:

• خراش دادن ابرداده از فهرستی از نشانی‌های وب (مانند عنوان، توضیحات، تگ‌های h و غیره)؛
• خرد کردن آدرس‌های ایمیل از صفحات وب؛
• خراش دادن نمایه‌های اجتماعی (مانند فیس‌بوک) از صفحات وب؛
• خراش‌کردن lastBuildDate از فیدهای RSS (این یک روش واقعاً دزدانه برای مشاهده اینکه سایت اخیراً بدون نیاز به بارگیری به‌روزرسانی شده است. وب سایت!)

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

می‌توانیم در HTML ببینیم که عنوان متا به شرح زیر است: «چگونه می‌توان تحقیق کلمات کلیدی را در سال 2017 انجام داد – راهنمای Ahrefs».

پرس و جوی XPath که ما برای گرفتن عنوان متا استفاده می کنیم بسیار ساده است: “//title”

این فرمول است :

=IMPORTXML("https://ahrefs.com/blog/k eyword-research/","//title")

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

IMPORTXML به خراش دادن متا تگ های اساسی نیز محدود نمی شود. می توان از آن برای خراش دادن تقریباً هر چیزی استفاده کرد. این فقط یک مورد برای دانستن XPath است.

در اینجا چند فرمول بالقوه مفید XPath وجود دارد:

• همه پیوندها را در یک صفحه استخراج کنید: ؛
• استخراج همه پیوندهای داخلی در یک صفحه: "//a[contains(@href, 'domain .com')]/@href"؛
• استخراج همه پیوندهای خارجی در یک صفحه: "//a[not(contains(@href , 'domain.com'))]/@href"؛
• استخراج توضیحات متا: "//meta[@name='description' ]/@content"؛
• Extract H1: "//h1";
• استخراج آدرس(های) ایمیل از صفحه: "//a[contains(@href, 'mailTo:') or contain(@href, 'mailto:')]/@href";
• استخراج نمایه های اجتماعی (به عنوان مثال لینکدین، فیس بوک، توییتر): "//a[contains(@href، 'linkedin.com/in') یا حاوی(@href، 'twitter.com/') یا حاوی(@href, 'facebook.com/')]/@href"؛
• Extract lastBuildDate (از RSS خوراک) : "//lastBuildDate"

می‌توانید با انجام کارهای زیر (در کروم) XPath را برای هر عنصری پیدا کنید: blockquote readability=”5″>

راست کلیک > Inspect > راست کلیک > کپی > کپی XPath

5. رشته های SEARCH برای مقادیر خاص

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

Syntax: = SEARCH(search_query, text_to_search)

در اینجا چند مورد استفاده وجود دارد:

• • بررسی کنید که آیا یک زیر دامنه خاص در URL وجود دارد (این برای دسته‌بندی انبوه فهرست مفید است از آدرس‌های اینترنتی؛
  • کلمات کلیدی را در دسته‌های مختلف مبتنی بر هدف (مانند مارک، تجاری، و غیره) دسته‌بندی کنید؛
  • جستجوی نویسه‌های خاص و نامطلوب در یک URL؛

<. url></.>

بیایید نگاهی به نمونه ای از SEARCH در عمل.

در اینجا لیستی از 300+ صفحه برتر در Ahrefs.com آمده است (توجه: من از Site Explorer برای جمع آوری این داده ها):

همه صفحات دارای /blog/ در URL پست های وبلاگ هستند. فرض کنید می‌خواستم هر یک از این صفحات را در طول ممیزی محتوا به‌عنوان «پست وبلاگ» برچسب‌گذاری کنم.

SEARCH (همراه با یک عبارت IF – این قبلاً در راهنما مورد بحث قرار گرفت) می تواند این کار را در چند ثانیه انجام دهد. فرمول اینجاست:

=IF(SEARCH("/blog/",A2),"YES","")

بیایید آن را در یک IFERROR و ARRAYFORMULA بپیچیم تا همه چیز را مرتب کنیم.

در اینجا چند فرمول مفید دیگر وجود دارد:

• صفحات “نوشتن برای ما” را در لیستی از URLها بیابید: =IF(SEARCH("/write-for-us/",A2),"Write for us page","");
• صفحات منبع را در لیستی از URLها پیدا کنید: =IF(SEARCH("/resources.html",A2)،"صفحه منبع،"")؛
• یافتن عبارات جستجوی مارک دار (در لیستی از کلمات کلیدی) : =IF(SEARCH("brand_name",A2)،"کلید کلیدی مارکدار"،"")؛
• شناسایی پیوندهای داخلی/خارجی (از فهرستی از پیوندهای خروجی): =IF(SEARCH("yourdomain.com",A2),"Internal Link","External Link");

6. وارد کردن داده‌ها از صفحات گسترده دیگر با استفاده از IMPORTRANGE

IMPORTRANGE به شما امکان می‌دهد داده‌ها را از هر برگه Google دیگری وارد کنید.

لازم نیست این داده‌ها در Google Drive شما باشد. ممکن است به شخص دیگری تعلق داشته باشد (توجه داشته باشید: اگر چنین است به اجازه دسترسی به برگه نیاز دارید!)

Syntax: =IMPORTRANGE(spreadsheet_ID, range_to_import)

در اینجا چند مورد استفاده وجود دارد:

• برگ‌های رو به مشتری ایجاد کنید که از صفحه‌گسترده اصلی شما بازگردانده شوند؛
• جستجو و داده‌های ارجاع متقابل در چندین کاربرگ‌نگار Google (یعنی استفاده از IMPORTRANGE همراه با VLOOKUP)؛
• داده‌ها را از برگه دیگری برای استفاده در اعتبارسنجی داده وارد کنید؛
• اطلاعات تماس را از یک صفحه گسترده «مستر» با استفاده از VLOOKUP

بیایید نگاهی به نمونه ای از IMPORTRANGE در عمل بیاندازیم.

در اینجا یک برگه با لیستی از مشتریان سئوی فرضی آنها وجود دارد: >

بیایید فرض کنیم که من می‌خواستم از این فهرست مشتری در برگه Google دیگری استفاده کنم — می‌توانم کل این محدوده داده را با استفاده از فرمول زیر وارد کنم:

=IMPORTRANGE("SPREADSHEET_KEY",""SheetName"!A2:A" )

اجازه دهید همچنین فرض کنیم که در حال ضبط پیوندهای ساخته شده برای این مشتریان در یک صفحه گسترده اصلی هستید. در یک ستون، URL پیوند را دارید و در ستون دیگر، می‌خواهید ثبت کنید که پیوند برای کدام مشتری است.

می‌توانید از IMPORTRANGE برای ایجاد فهرست کشویی از همه مشتریان با استفاده از اعتبارسنجی داده استفاده کنید، مانند:

هر زمان که مشتریانی را از صفحه گسترده اصلی خود اضافه یا حذف کنید، این منوی کرکره ای به صورت خودکار به روز می شود.

7. مجموعه داده های QUERY با استفاده از پرس و جوهای SQL (این یکی بسیار قدرتمند است!)

QUERY مانند VLOOKUP در استروئیدها است. این به شما امکان می‌دهد با استفاده از SQL داده‌ها را پرس‌وجو کنید، که به شما امکان می‌دهد در هنگام پرس‌وجو/بازیابی داده‌ها، اطلاعات فوق‌العاده‌ای داشته باشید.

Syntax: =QUERY(محدوده، sql_query )

در اینجا چند مورد استفاده وجود دارد:

• از یک پایگاه داده بالقوه پیوند اصلی برای مشتریان بالقوه پرس و جو کنید (مثلاً فقط مشتریان بالقوه برچسب‌گذاری شده به عنوان فرصت‌های پست مهمان، با یک DR بالاتر از 50، و جزئیات تماس موجود)؛
• اسناد فوق‌العاده‌ای با مشتری ایجاد کنید که داده‌ها را از یک صفحه گسترده «مستر» دریافت می‌کند؛
• پرس و جو از یک صفحه گسترده ممیزی سایت برای حذف فقط صفحاتی که نیاز به توجه دارند.

بیایید به برگه برچسب‌گذاری شده «پست‌های وبلاگ» خود برگردیم.

اگر بخواهیم همه موارد را وارد کنیم نشانی‌های اینترنتی که با «پست وبلاگ» در یک صفحه گسترده کاملاً جدید برچسب‌گذاری شده‌اند، می‌توانیم از این تابع QUERY استفاده کنیم:

=QUERY(DATA!A:B,"انتخاب A Where B = 'پست وبلاگ '")

But let’s say that we had a bigger data set. An export file from Site Explorer, perhaps.

These export files can be quite data heavy, so let’s assume that we wanted to pull out a list of all referring pages with the following attributes:

• Dofollow link;
• DR > 50;
• Backlink status = active (i.e. not tagged as “removed”);
• External links count

Here’s the formula:

=QUERY('DATA - site explorer export'!A2:R,"SELECT E where D > 50 AND H 'REMOVED'")

NOTE: It’s also possible to incorporate IMPORTRANGE into a QUERY function; this allows you to QUERY data from other sheets.

Final thoughts

Google Sheets is insanely powerful; this post only scratches the surface of what you can do with it.

I’d recommend playing around with the formulas above and seeing what you can come up with. I also recommend checking out the full library of Google Sheets formulas.

But, that’s still just the beginning: Google Sheets also integrates with Zapier and IFTTT, which means you can connect with hundreds of other tools and services, too.

And if you want to get really advanced, look into Apps Script—it’s crazy powerful!

If you have any creative uses for Google Sheets of your own, please let me know in the comments. I’d love to hear them!