בלוג שיווק דיגטלי

נוסחאות Excel ל-SEO

הסקת מסקנות ממחקרי SEO דורשת יכולת קריאת נתונים וטבלאות.
כלומר איסוף, סינון וסידור של הנתונים בצורה שתחשוף את ההזדמנויות הבאות.

ישנם מספר כלים שבאים לעזרתנו, המפורסמות שביניהן היום הן: Excel, Tableau ופייתון.

בכתבה זו אתמקד באקסל ובמספר נוסחאות פשוטות שיכולות לעזור עם ניתוחי נתונים, בנוסף נוסחאות אלה יכולות לעזור בחיסכון של זמן.

חילוץ שם הדומיין להשוואות רשימת מילות מפתח

חילוץ שם הדומיין ללא www ו-http:

=MID(SUBSTITUTE(A2,"www.",""),SEARCH(":",A2)+3,SEARCH("/",SUBSTITUTE(A2,"www.",""),9)-SEARCH(":",A2)-3)

חילוץ שם הדומיין עם www ו-http:

=LEFT(A2,FIND("/",A2,9)-1)

דוגמה:

אם ברשותכם רשימת מילות מפתח ו-10 תוצאות חיפוש ראשונות של כל אחת מהן, תוכלו להשוות כמה פעמים מופיע דומיין מסוים. מתוך בדיקה זו תוכלו להבין מה הכח האורגני של אותו דומיין לפי רשימת מילות המפתח שאותם אתם משווים.

חילוץ שם דומיין - נוסחאות אקסל ל-SEO

מה אנחנו מרוויחים מסינון דומיינים?

אם דומיין מסויים מופיע בדף הראשון בהרבה חיפושים כנראה שעושים שם משהו טוב וכדאי לחקור את: היררכיית האתר, אסטרטגיית הקישורים הפנימיים שלהם, קישורים נכנסים (כלים כמו מג'סטיק או SEMRUSH או AHREF), מאפייני התכנים שהם בוחרים להעלות, והחלק האחרון הוא מה הם עושים שונה מהשאר מבחינת האתר, האם הם מציעים כלים שאחרים לא מציעים: כלי ניתוח נתונים\מחשבונים\טבלאות עם נתונים שימושיים וכו'…

חילוץ מילות מפתח המכילות שאלה

שאלות הן מקור לתנועה איכותית, אפילו אם מסתכלים מחוץ לגוגל, לדוגמה פורומים כמו Reddit ו-Quora.

את השאלות שמעניינות את הגולשים אנחנו צריכים למצוא בעצמנו, חלק מהן אפשר למצוא בפורומים, קבוצות פייסבוק, אנשים שאנחנו מכירים באותו תחום וכו'.

כשמבצעים מחקר מילות מפתח אנחנו נתקלים בשאלות שגולשים שואלים ואם יש לנו תשובה מקצועית וקלה להבנה, נוכל להשיג לעצמנו תנועה איכותית לאתר.

בדרך כלל בוחרים כלי מחקר (כמו כלי Google Keyword Planner) ומייצאים קובץ אקסל מלא במילות מפתח שמכיל את השאלות שאנחנו מחפשים. כשהרשימה ארוכה מאוד נוכל להשתמש בנוסחאות אקסל שתסנן את השאלות בשבילנו.

הנוסחה עובדת בעברית ובאנגלית.

איך מבצעים סינון שאלות מתוך רשימת מילות מפתח?

  1. פותחים גיליון חדש + ומכניסים רשימה של מילות שאלה שאותן נרצה לסנן כמו: למה, איך, כמה, מתי, מי, מה, איפה, היכן, האם וכו'.
  2. בגיליון עם מילות המפתח, העתיקו את הפורמולה הבאה לעמודה חדשה, בעמודה זו יתבצע הסיווג של מילות המפתח ל"שאלה" ו-"לא שאלה".
  3. החליפו את החלק [Range of Question Keywords] ברשימת מילות השאלה שרשמנו לפני כן וקבעו אותה בעזרת סימוני $ או בלחיצה על F4 (לדוגמה $A$2).

    נוסחה:

SUMPRODUCT(COUNTIFS(A2,"*"&[Range of Question Keywords]&"*"))>0

מה אנחנו מרוויחים מסינון שאלות?

  • יכולים להבין טוב יותר את הלקוחות – מה הבעיות שנתקלים בהן, איך הם מציגים את הבעיה
  • הזדמנות לעלות בתוצאות אפס בגוגל בעזרת הרחבת התכנים באתר לטובת תשובות לשאלות אלה
  • לעלות טוב יותר בתוצאות חיפוש קוליות – בחיפוש קולי המשתמשים מבצעים חיפושים ארוכים יותר ושאלות הן חלק מהחיפושים הללו

סינון דפים המסתיימים בקו נטוי

חילוץ URLs קטגוריאליים שנגמרים עם / וחילוץ URLs של דפי מוצר שנגמרים ללא / – בדיקת עקביות במבנה ה-URL באתר.

כשמדובר במבנה האתר, אתם רוצים לשמור על עקביות. עקביות תעזור למנועי חיפוש להבין טוב יותר את האתר ובכך יציגו אותו נכון יותר בתוצאות החיפוש לגולשים הרלבנטיים.

נוסחה:

=IF(RIGHT(A2,1)="/","Ends with /","-")

חילוץ פרוטוקול הדפים שמכילים כתובת לא מאובטחת HTTP

רשימת הדפים במפות אתר, רשימת דפים מתוך סריקה של האתר עם כלים כמו Search Console או כל כלי אחר מכילה מידע אודות רמת אבטחת האתר.

אבטחת האתר מייצרת חוויה בטוחה יותר למשתמשים וגם מהווה חלק חשוב ממדדי האיכות של גוגל.
במקרה הזה מדובר במציאת דפים שעדיין מכילים HTTP במקום HTTPS.

אפשר גם לבדוק קישורים פנימיים וחיצוניים שמכילים HTTP ולתקן אותם.

נוסחה:

=LEFT(A2,(FIND(":",A2)-1))

חילוץ הכתובות היחסיות לצורך הבנת מבנה האתר

הכתובות היחסיות עוזרות לנו להבין יותר טוב את מבנה האתר, אבל הן לבד לא יתנו לנו את כל המידע שצריך. אנחנו נרצה בנוסף לראות אילו דפים מקשרים אל כל קטגוריה או דף מוצר, כך נקבל תמונה מלאה יותר, ואם בא לכם ממש לחפור פנימה, תוכלו להשתמש גם ב-Anchor של כל אחד מהלינקים.

דרך נוספת היא לחלק כל אחת מספריות האתר בנפרד לפי עומק וליצור טבלת PIVOT שתעזור לנו להבין את מספר הקישורים הכוללת לכל קטגוריה ראשית.

קטגוריה

קישורים נכנסים

תתי קטגוריה

קישורים לתתי-קט'

/tools/

55

5

40

נוסחה:

=RIGHT(A2,LEN(A2)-FIND("/",A2,FIND("/",A2)+2)+1)

פרמטרים בכתובות URL

סינון הפרמטרים מכתובות URL

נוסחה:

=IFERROR(LEFT(A2,FIND("?",A2)-1),"-")

חילוץ הפרמטרים מכתובות URL – עוזר לפני מעבר לאתר חדש ועם ההחלטה להעביר אותו לכתובות URL ידידותיות

נוסחה:

=RIGHT(A2,LEN(A2)-FIND("?",A2))

עזרה בכתיבת קובץ רובוטס

התהליך מורכב משני שלבים:

  1. חילוץ כתובות יחסיות
  2. הוספת פקודת Dissallow

נוסחאות:

חילוץ כתובות יחסיות

=RIGHT(A2,LEN(A2)-FIND("/",A2,FIND("/",A2)+2)+1)

הוספת Dissallow לתחילת כל שורה

="Dissallow: "&[ Relative URL]

עזרה בכתיבת קובץ Robots txt - אקסל ל-SEO

בדוגמה שלי הנוסחה נראית כך:

="Dissallow: "&[B2]

נסיים את התהליך בהעתקת השורות לקובץ Unicode TXT.

מכירים מישהו שהמידע יעזור לו?
שיתוף ב facebook
שיתוף ב twitter
שיתוף ב linkedin
שיתוף ב whatsapp
שיתוף ב email
שיתוף ב print
צור קשר
דילוג לתוכן