Hướng dẫn hàm importhtml

Excel là một công cụ xây dựng công thức SEO tuyệt vời, nhưng hiện nay Google Sheet đang trở thành công cụ dần soán ngôi.

Tôi chắc rằng hầu hết các bạn đã quen thuộc với Trang tính (nếu không, về cơ bản nó giống như Excel, nhưng dựa trên đám mây và hoàn toàn miễn phí) và biết nó có thể mạnh mẽ như thế nào khi nói đến cộng tác.

Tuy nhiên, khả năng của nó vượt xa sự cộng tác.

Google Trang tính có thể được sử dụng để thu thập dữ liệu từ các trang web, tạo quy trình làm việc SEO bán tự động, thao tác các tập dữ liệu lớn (ví dụ: xuất Site Explorer), tự động theo dõi các chiến dịch tiếp cận và hơn thế nữa.

Trong bài đăng này, tôi sẽ giới thiệu với bạn 10 công thức Google Trang tính và chỉ ra cách bạn có thể sử dụng chúng cho các công việc SEO hàng ngày.

Các bài viết liên quan:

• Cách tạo và sử dụng nội dung theo hướng dữ liệu để xây dựng liên kết
• Schema là gì?
• Quản trị người dùng trên WordPress với UsersInsight
• Tự học html: Tài liệu HTML
• Cách chăm sóc website

Tóm tắt nội dung

• Hãy bắt đầu với những điều cơ bản…
  • 1. Sử dụng REGEXTRACT để trích xuất dữ liệu từ các chuỗi
  • 2. SPLIT chuỗi thành nhiều điểm dữ liệu
  • 3. Hợp nhất nhiều tập dữ liệu bằng hàm VLOOKUP
  • 5. TÌM KIẾM chuỗi cho các giá trị nhất định
  • 6. Nhập dữ liệu từ các bảng tính khác bằng IMPORTRANGE
  • 7. Tập dữ liệu QUERY sử dụng truy vấn SQL (cái này cực kỳ mạnh mẽ!)
• 1. Chuyển hướng xác thực triển khai
• 2. So sánh các chỉ số hiệu suất của Search Console giữa hai khoảng thời gian để xác định thứ hạng tăng hoặc giảm
• 3. Tích hợp số liệu hiệu suất tìm kiếm không phải trả tiền dọc theo dữ liệu liên kết ngược để xác định các trang nhắm mục tiêu các truy vấn phổ biến cần liên kết để cải thiện hiệu suất
  • 4. So sánh hiệu suất các trang và truy vấn được xếp hạng trên thiết bị di động với máy tính để bàn
  • 5. Tích hợp lưu lượng truy cập không phải trả tiền của Google Analytics cùng với hiệu suất tìm kiếm để xác định các cơ hội cải thiện mức độ tương tác và chuyển đổi trên các trang được xếp hạng hàng đầu
  • 6. Xác thực mức độ liên quan của siêu dữ liệu so với các truy vấn được xếp hạng của chúng và cấu hình lập chỉ mục của các trang hoạt động kém nhắm mục tiêu các truy vấn có tiềm năng cao
  • 7. Xác định các vấn đề ăn thịt nội dung giữa nhiều trang xếp hạng cho các truy vấn giống nhau
  • 8. Xác định xem các trang danh mục phù hợp đang xếp hạng cho các loại truy vấn có liên quan của chúng hay không
  • 9. Xác định các trang được xếp hạng tiềm ẩn các vấn đề về điều chỉnh sai lệch quốc tế
• Suy nghĩ cuối cùng

Hãy bắt đầu với những điều cơ bản…

Trong phần ngắn này, tôi sẽ chia sẻ ba công thức cơ bản phải biết.

Bất kể tôi đang làm loại công việc SEO nào trong Google Trang tính, tôi luôn thấy mình sử dụng ba công thức này (hầu như) mọi lúc:

• IF;
• IFERROR;
• ARRAYFORMULA

Hãy bắt đầu với câu lệnh IF.

Điều này là siêu đơn giản; nó được sử dụng để kiểm tra xem một điều kiện là đúng hay sai.

Cú pháp: = IF (điều kiện, value_if_true, value_if_false)

Dưới đây là một bảng tính mẫu có chứa danh sách các từ khóa với khối lượng tìm kiếm ước tính tương ứng (lưu ý: chúng được thu thập bằng cách sử dụng Keyword Explorer):

Giả sử, giả sử rằng chúng ta có một trang web mạnh có khả năng xếp hạng số 1 cho bất kỳ từ khóa nào trong số này. Tuy nhiên, chúng tôi chỉ muốn theo đuổi những từ khóa có khả năng mang lại hơn 500 khách truy cập mỗi tháng (giả sử chúng tôi có xếp hạng số 1).

Theo nghiên cứu này, xếp hạng số 1 ở Hoa Kỳ (chỉ tìm kiếm trên máy tính để bàn) có CTR là 29%.

SIDENOTE. Chúng tôi khuyên bạn không nên dựa vào phương pháp này để ước tính lưu lượng truy cập tìm kiếm vì CTR rất khác nhau trên các truy vấn, thiết bị khác nhau, v.v. Đó là lý do tại sao chúng tôi sử dụng dữ liệu luồng nhấp chuột để ước tính lưu lượng truy cập.

Vì vậy, hãy viết câu lệnh IF sẽ trả về “TỐT” cho những từ khóa có khả năng mang lại hơn 500 khách truy cập (tức là những từ khóa có 29% khối lượng tìm kiếm lớn hơn hoặc bằng 500) và “XẤU” cho những từ khóa còn lại.

Đây là công thức:

= IF (B2 * 0,29> = 500, “TỐT”, “XẤU”)

Đây là những gì điều này thực hiện (bằng tiếng Anh đơn giản):

• Nó kiểm tra xem liệu B2 * 0,29 (tức là 29% khối lượng tìm kiếm) lớn hơn hoặc bằng 500;
• Nếu điều kiện là đúng, nó trả về “TỐT”. Nếu sai, nó trả về “BAD”.

Điều này hoạt động rất tốt cho tập dữ liệu hiện tại của chúng tôi, nhưng hãy xem điều gì sẽ xảy ra khi chúng tôi ném một số giá trị không phải số vào hỗn hợp:

Đó là một lỗi.

Điều này xảy ra vì không thể nhân một giá trị không phải số với 0,29 (hiển nhiên).

SIDENOTE. Tôi đã thêm một số định dạng có điều kiện để bất cứ nơi nào câu lệnh IF đánh giá là TRUE, các ô sẽ được đánh dấu màu xanh lục. Nếu câu lệnh đánh giá là FALSE, chúng sẽ được đánh dấu màu đỏ.

Đây là lúc IFERROR có ích.

IFERROR cho phép bạn đặt giá trị mặc định nếu công thức dẫn đến lỗi.

Cú pháp: = IFERROR (original_formula, value_if_error)

Hãy kết hợp điều này vào ví dụ trên (chúng tôi sẽ để trống ô nếu có lỗi) và xem điều gì sẽ xảy ra:

Hoàn hảo – đó là công thức hoàn chỉnh!

Được, vì vậy nếu bạn chỉ làm việc với một lượng nhỏ dữ liệu, vui lòng chuyển thẳng sang phần tiếp theo.

Tuy nhiên, với thực tế là hướng dẫn này dành cho SEO, tôi sẽ giả định rằng bạn đang làm việc với lượng dữ liệu lớn một cách thường xuyên.

Nếu đúng như vậy, tôi đoán chắc rằng bạn dành quá nhiều thời gian để kéo các công thức xuống hàng trăm, thậm chí có thể là hàng nghìn ô.

Nhập: ARRAYFORMULA.

Cú pháp: = ARRAYFORMULA (array_formula)

Về cơ bản, ARRAYFORMULA chuyển đổi công thức ban đầu của bạn thành một mảng, do đó cho phép bạn lặp lại cùng một công thức trên nhiều hàng bằng cách chỉ viết một công thức duy nhất.

Vì vậy, hãy xóa tất cả công thức trong ô B2 trở đi và bọc toàn bộ công thức hiện tại trong ô B1 trong ARRAYFORMULA, như sau:

= ARRAYFORMULA (IFERROR (IF (B2: B29 * 0,29> = 500, “TỐT”, “XẤU”), “”))

Ma thuật.

Đó là những điều cơ bản được đề cập; chúng ta hãy xem một số công thức hữu ích hơn.

SIDENOTE. Dưới đây là bảng tính hiển thị cách hoạt động của từng công thức này (lưu ý: các ô chứa công thức sẽ được đánh dấu màu vàng). Tôi sẽ bao gồm nhiều bảng tính hơn trong bài đăng.

1. Sử dụng REGEXTRACT để trích xuất dữ liệu từ các chuỗi

REGEXTRACT sử dụng các biểu thức chính quy để trích xuất các chuỗi con từ một chuỗi hoặc ô.

Cú pháp: = REGEXEXTRACT (văn bản, biểu_thức_hình_phục)

Đây chỉ là một số ít các trường hợp sử dụng tiềm năng cho việc này:

• Trích xuất tên miền từ danh sách URL (tiếp tục đọc để xem ví dụ!);
• Trích xuất URL (tức là không có miền gốc);
• Kiểm tra xem URL sử dụng HTTP hoặc HTTPS;
• Trích xuất địa chỉ email từ một lượng lớn văn bản;
• Xác định các URL có / không có các từ nhất định trong đó từ danh sách các URL (ví dụ: URL chứa slug “/ category / guest-post”).

Giả sử chúng tôi muốn trích xuất các miền gốc từ danh sách URL trang “viết cho chúng tôi” (tức là cơ hội đăng bài của khách).

Trong cột B, chúng ta có thể viết công thức ĐĂNG KÝ để thực hiện việc này.

Đây là cú pháp regex mà chúng ta cần: ^ (?: https?: \ / \ /)? (?: [^ @ \ N] + @)? (?: www \.)? ([^: \ / \ N ] +)

SIDENOTE. Nếu bạn không quen thuộc với regex (đừng lo lắng, tôi cũng không giỏi về nó), bạn có hai lựa chọn: (i) Tìm hiểu kiến thức cơ bản – hãy xem Regexr.com (ii) Google giải pháp cho bất cứ điều gì bạn cần – nghiêm túc đấy, thật đáng ngạc nhiên với những gì bạn có thể tìm thấy với một chút về Google!

Đây là công thức cuối cùng của chúng tôi:

= REGEXEXTRACT (A2, “^ (?: https?: \ / \ /)? (?: [^ @ \ N] + @)? (?: www \.)? ([^: \ / \ N] + ) “)

Dán cái này vào ô B2 và trước khi bắt đầu, chúng tôi đã trích xuất miền.

Hãy gói nó trong ARRAYFORMULA và IFERROR để hoàn thành toàn bộ cột.

= IFERROR (ARRAYFORMULA (REGEXEXTRACT (A2: A, “^ (?: https?: \ / \ /)? (?: [^ @ \ N] + @)? (?: www \.)? ([^: \ / \ n] +) “)),” “)

2. SPLIT chuỗi thành nhiều điểm dữ liệu

SPLIT chia (tức là chia) các chuỗi thành các đoạn bằng cách sử dụng dấu phân cách.

Cú pháp: = SPLIT (văn bản, dấu phân cách)

Đây chỉ là một số ít các trường hợp sử dụng tiềm năng cho việc này:

• Chia tên đầy đủ của khách hàng tiềm năng thành các cột “tên” và “họ”;
• Chia một URL thành 3 cột cho giao thức HTTP, tên miền gốc và URL slug;
• Chia danh sách các giá trị được phân tách bằng dấu phẩy thành nhiều cột;
• Chia miền gốc thành 2 cột cho tên miền và phần mở rộng miền (ví dụ: .com, .org, v.v.)

Tôi có một danh sách đẹp các thành viên trong nhóm websitehcm (họ tên) trong một bảng tính.

Dưới đây là một công thức SPLIT đơn giản mà chúng tôi có thể sử dụng trong ô B2 để chia chúng thành tên và họ:

= SPLIT (A2, “”)

SIDENOTE. Chúng tôi đang sử dụng một khoảng trắng (tức là ””) làm dấu phân cách vì điều này cho công thức SPLIT biết vị trí cần chia chuỗi.

Một lần nữa, hãy gói điều này trong IFERROR và ARRAYFORMULA để chia toàn bộ danh sách bằng một công thức duy nhất.

= IFERROR (ARRAYFORMULA (SPLIT (A2: A, “”)), “”)

Dưới đây là một công thức ví dụ khác sẽ chia các miền gốc thành tên trang web và phần mở rộng miền:

= SPLIT (A2, “.”)

3. Hợp nhất nhiều tập dữ liệu bằng hàm VLOOKUP

VLOOKUP cho phép bạn tìm kiếm một dải ô bằng cách sử dụng khóa tìm kiếm — sau đó, bạn có thể trả về các giá trị phù hợp từ một ô cụ thể trong dải ô đã nói.

Cú pháp: = VLOOKUP (khóa_ tìm kiếm, dải_ô ,_phí_mục)

Đây chỉ là một số ít các trường hợp sử dụng tiềm năng cho việc này:

• Hợp nhất dữ liệu từ nhiều nguồn (ví dụ: hợp nhất danh sách tên miền có xếp hạng websitehcm DR tương ứng từ một trang tính riêng biệt);
• Kiểm tra xem một giá trị có tồn tại trong một tập dữ liệu khác hay không (ví dụ: kiểm tra các bản sao trên hai hoặc nhiều danh sách khách hàng tiềm năng tiếp cận);
• Lấy địa chỉ email (từ cơ sở dữ liệu tổng thể về các liên hệ) cùng với danh sách các khách hàng tiềm năng.

Giả sử chúng ta có một danh sách các khách hàng tiềm năng tiếp cận (tức là một nhóm người liên kết đến trang web của đối thủ cạnh tranh, được lấy từ Site Explorer). Chúng tôi cũng có một cơ sở dữ liệu tổng thể về thông tin liên hệ (tức là địa chỉ email) trong một bảng tính khác.

Xuất Site Explorer (lưu ý: Tôi đã xóa nhiều cột ở đây, vì nhiều dữ liệu không cần thiết cho ví dụ này).

Cơ sở dữ liệu liên hệ chính – đây là cơ sở dữ liệu chúng tôi sẽ truy vấn bằng hàm VLOOKUP.

SIDENOTE. Tôi đã thêm hai cột mới (trống) cho dữ liệu VLOOKUP (tức là tên đầy đủ và email) vào trang xuất Site Explorer. Điều này sẽ được hiển thị trong một vài ảnh chụp màn hình tiếp theo.

Chúng tôi không muốn lãng phí thời gian tìm kiếm thông tin liên hệ mà chúng tôi đã có, vì vậy hãy sử dụng hàm VLOOKUP để truy vấn cơ sở dữ liệu chính và xem liệu chúng tôi đã có thông tin liên hệ cho bất kỳ khách hàng tiềm năng nào trong số này chưa.

Đây là công thức chúng tôi sẽ sử dụng:

= VLOOKUP (D2: D, ‘Cơ sở dữ liệu liên hệ chính’! A: C, 2)

OK, hãy làm tương tự cho cột email; chúng tôi cũng sẽ đưa cả hai công thức vào IFERROR và ARRAYFORMULA.

= IFERROR (ARRAYFORMULA (VLOOKUP (D2: D, ‘Cơ sở dữ liệu liên hệ chính’! A: C, 3)), “”)

4. Cạo dữ liệu từ bất kỳ trang web nào bằng IMPORTXML

IMPORTXML cho phép bạn nhập dữ liệu (sử dụng truy vấn XPath) từ một số kiểu dữ liệu có cấu trúc bao gồm XML, HTML và RSS (trong số những kiểu khác).

Nói cách khác, bạn có thể duyệt web mà không cần rời khỏi Google Trang tính!

Cú pháp: = IMPORTXML (url, xpath_query)

Đây chỉ là một số ít các trường hợp sử dụng tiềm năng cho việc này:

• Cóp nhặt siêu dữ liệu từ danh sách URL (ví dụ: tiêu đề, mô tả, thẻ h ‑, v.v.);
• Cóp nhặt địa chỉ email từ các trang web;
• Lừa lọc hồ sơ xã hội (ví dụ: Facebook) từ các trang web;
• Tìm kiếm lastBuildDate từ nguồn cấp dữ liệu RSS (đây là một cách thực sự lén lút để xem trang web đã được cập nhật gần đây như thế nào mà không cần phải tải trang web!)

Giả sử rằng chúng tôi muốn lấy tiêu đề meta cho bài đăng của chúng tôi về nghiên cứu từ khóa.

Truy vấn XPath mà chúng tôi sử dụng để lấy tiêu đề meta khá đơn giản: “// title”

Đây là công thức:

= IMPORTXML (“https://websitehcm.com/blog/keyword-research/”, “// title”)

Cũng có thể sử dụng tham chiếu ô trong công thức; điều này làm cho việc thu thập dữ liệu cho một loạt các URL trở nên cực kỳ đơn giản.

SIDENOTE. Rất tiếc, IMPORTXML không hoạt động với ARRAYFORMULA, vì vậy, sẽ là trường hợp kéo cái này xuống theo cách thủ công.

IMPORTXML cũng không giới hạn trong việc tìm kiếm các thẻ meta cơ bản; nó có thể được sử dụng để cạo hầu như bất cứ thứ gì. Đó chỉ là một trường hợp biết XPath.

Dưới đây là một số công thức XPath có thể hữu ích:

• Trích xuất tất cả các liên kết trên một trang: “// @ href”;
• Trích xuất tất cả các liên kết nội bộ trên một trang: “// a [chứa (@href, ‘domain.com’)] / @ href”;
• Trích xuất tất cả các liên kết bên ngoài trên một trang: “// a [not (chứa (@href, ‘domain.com’))] / @ href”;
• Trích xuất mô tả meta: “// meta [@ name = ‘description’] / @ content”;
• Trích xuất H1: “// h1”;
• Trích xuất (các) địa chỉ email từ trang: “// a [chứa (@href, ‘mailTo:’) hoặc chứa (@href, ‘mailto:’)] / @ href”;
• Trích xuất hồ sơ xã hội (tức là LinkedIn, Facebook, Twitter): “// a [chứa (@href, ‘linkedin.com/in’) hoặc chứa (@href, ‘twitter.com/’) hoặc chứa (@href, ‘ facebook.com/’)]/@href “;
• Trích xuất lastBuildDate (từ nguồn cấp dữ liệu RSS): “// lastBuildDate”

Bạn có thể tìm XPath cho bất kỳ phần tử nào bằng cách thực hiện như sau (trong Chrome):

Nhấp chuột phải> Kiểm tra> Nhấp chuột phải> Sao chép> Sao chép XPath

5. TÌM KIẾM chuỗi cho các giá trị nhất định

TÌM KIẾM cho phép bạn kiểm tra xem một giá trị có tồn tại trong một chuỗi hay không; sau đó nó trả về vị trí mà tại đó giá trị được tìm thấy đầu tiên trong chuỗi.

Cú pháp: = SEARCH (search_query, text_to_search)

Dưới đây là một số trường hợp sử dụng:

• Kiểm tra xem một miền phụ cụ thể có tồn tại trong URL hay không (điều này hữu ích cho việc phân loại hàng loạt danh sách URL);
• Phân loại từ khóa thành các danh mục dựa trên mục đích khác nhau (ví dụ: có thương hiệu, thương mại, v.v.);
• Tìm kiếm các ký tự cụ thể, không mong muốn trong một URL;
• Tìm kiếm các từ / cụm từ nhất định trong URL để phân loại các liên kết triển vọng (ví dụ: “/ category / guest-post”, “resources.html”, v.v.)

Hãy xem ví dụ về TÌM KIẾM đang hoạt động.

Đây là danh sách hơn 300 trang hàng đầu trên websitehcm.com (lưu ý: Tôi đã sử dụng Site Explorer để thu thập dữ liệu này):

SIDENOTE. Tôi đã xóa dữ liệu trong ảnh chụp màn hình ở trên bằng cách xóa một vài cột; Site Explorer thực sự cung cấp cho bạn nhiều thông tin hơn thế này (ví dụ: từ khóa hàng đầu cho mỗi URL, khối lượng lưu lượng truy cập, khối lượng tìm kiếm, vị trí, v.v.)

Tất cả các trang có / blog / trong URL đều là các bài đăng trên blog. Giả sử tôi muốn gắn thẻ mỗi trang này là “Bài đăng trên blog” trong quá trình kiểm tra nội dung.

TÌM KIẾM (kết hợp với câu lệnh IF – điều này đã được thảo luận trước đó trong hướng dẫn) có thể thực hiện việc này trong vài giây; đây là công thức:

= IF (TÌM KIẾM (“/ blog /”, A2), “CÓ”, “”)

Hãy bọc nó trong IFERROR và ARRAYFORMULA để thu gọn mọi thứ.

Dưới đây là một số công thức hữu ích khác:

• Tìm các trang “viết cho chúng tôi” trong danh sách các URL: = IF (TÌM KIẾM (“/ write-for-us /”, A2), “Trang viết cho chúng tôi”, “”);
• Tìm các trang tài nguyên trong danh sách URL: = IF (SEARCH (“/ resources.html”, A2), “Trang tài nguyên”, “”);
• Tìm các cụm từ tìm kiếm có thương hiệu (trong danh sách các từ khóa): = IF (SEARCH (“brand_name”, A2), “Từ khóa được gắn thương hiệu”, “”);
• Xác định các liên kết nội bộ / bên ngoài (từ danh sách các liên kết ngoài): = IF (TÌM KIẾM (“yourdomain.com”, A2), “Liên kết Nội bộ”, “Liên kết Bên ngoài”);

6. Nhập dữ liệu từ các bảng tính khác bằng IMPORTRANGE

IMPORTRANGE cho phép bạn nhập dữ liệu từ bất kỳ Google Trang tính nào khác.

Nó cũng không nhất thiết phải có trên Google Drive của bạn; nó có thể thuộc về người khác (lưu ý: bạn sẽ cần quyền truy cập trang tính nếu trường hợp này xảy ra!)

Cú pháp: = IMPORTRANGE (table_ID, range_to_import)

Dưới đây là một số trường hợp sử dụng:

• Tạo các trang tính dành cho khách hàng dựa trên bảng tính “chính” của bạn;
• Tìm kiếm và dữ liệu tham chiếu chéo trên nhiều Google Trang tính (tức là sử dụng IMPORTRANGE kết hợp với VLOOKUP);
• Lấy dữ liệu từ một trang tính khác để sử dụng trong quá trình xác thực dữ liệu;
• Lấy dữ liệu liên hệ từ bảng tính “chính” bằng cách sử dụng hàm VLOOKUP

Hãy xem một ví dụ về IMPORTRANGE đang hoạt động.

Dưới đây là một trang với danh sách các khách hàng SEO giả định + ngân sách của họ:

Giả sử rằng tôi muốn sử dụng danh sách khách hàng này trong một Google Trang tính khác – Tôi có thể nhập toàn bộ phạm vi dữ liệu này bằng công thức sau:

= IMPORTRANGE (“SPREADSHEET_KEY”, “‘Tên trang tính’! A2: A”)

Cũng giả sử rằng bạn đang ghi lại các liên kết được tạo cho những khách hàng này trong một bảng tính chính; trong một cột, bạn có URL liên kết và trong cột kia, bạn muốn ghi lại liên kết dành cho khách hàng nào.

Bạn có thể sử dụng IMPORTRANGE để tạo danh sách thả xuống của tất cả các ứng dụng khách bằng cách sử dụng xác thực dữ liệu, như sau:

Menu thả xuống này sẽ tự cập nhật bất cứ khi nào bạn thêm / xóa ứng dụng khách khỏi bảng tính chính của mình.

7. Tập dữ liệu QUERY sử dụng truy vấn SQL (cái này cực kỳ mạnh mẽ!)

QUERY giống như VLOOKUP trên steroid. Nó cho phép bạn truy vấn dữ liệu bằng cách sử dụng SQL, cho phép bạn có được siêu chi tiết khi nói đến truy vấn / truy xuất dữ liệu.

Cú pháp: = QUERY (range, sql_query)

Dưới đây là một số trường hợp sử dụng:

• Truy vấn cơ sở dữ liệu khách hàng tiềm năng liên kết tổng thể cho các khách hàng tiềm năng cụ thể (ví dụ: chỉ tìm các khách hàng tiềm năng được gắn thẻ là cơ hội đăng bài của khách, với DR trên 50 và có các chi tiết liên hệ);
• Tạo tài liệu giao diện khách hàng siêu chi tiết lấy dữ liệu từ bảng tính “chính”;
• Yêu cầu kiểm tra tại chỗ lớn để chỉ lấy ra những trang cần chú ý.

Hãy quay lại trang của chúng tôi về “bài đăng trên blog” được gắn thẻ.

Nếu chúng tôi muốn kéo tất cả các URL được gắn thẻ “bài đăng trên blog” vào một bảng tính hoàn toàn mới, chúng tôi có thể sử dụng hàm QUERY này:

= QUERY (DỮ LIỆU! A: B, “chọn A trong đó B = ‘Bài đăng trên blog'”)

SIDENOTE. Điều này yêu cầu bảng tính chọn tất cả các giá trị trong cột A trong đó cột B = “Bài đăng trên blog”.

Nhưng giả sử chúng tôi có một tập dữ liệu lớn hơn. Có lẽ là một tệp xuất từ Site Explorer.

Các tệp xuất này có thể khá nặng dữ liệu, vì vậy, giả sử rằng chúng tôi muốn lấy ra danh sách tất cả các trang giới thiệu có các thuộc tính sau:

• Liên kết dofollow;
• DR> 50;
• Trạng thái liên kết ngược = hoạt động (tức là không được gắn thẻ là “đã loại bỏ”);
• Số liên kết ngoài <50;

Đây là công thức:

= QUERY (‘DỮ LIỆU – xuất trình khám phá trang web’! A2: R, “CHỌN E trong đó D> 50 VÀ H <50 VÀ M = ‘Dofollow’ VÀ N <> ‘ĐÃ LOẠI BỎ'”)

LƯU Ý: Cũng có thể kết hợp IMPORTRANGE vào một hàm QUERY; điều này cho phép bạn QUERY dữ liệu từ các trang tính khác.

Tôi thích Google trang tính. Có thể là do tôi là người dùng Mac nhưng tôi thấy mình sử dụng nó ngày càng nhiều hơn Excel cho các tác vụ liên quan đến SEO trong những năm gần đây, thậm chí đôi khi thay thế một số tác vụ mà nếu không tôi đã sử dụng công cụ SEO: từ phân tích nhanh chóng, xác nhận và thậm chí điều phối dự án cho các hoạt động nhanh chóng hoặc tạm thời.

Nó có thể linh hoạt để dễ dàng nhập, tích hợp, hợp nhất và xác thực bất kỳ loại dữ liệu nào với các chức năng tích hợp dễ sử dụng và tính sẵn có của các tiện ích bổ sung và mẫu miễn phí, cũng như khả năng phát triển các tập lệnh tùy chỉnh của riêng bạn, đồng thời tạo điều kiện “ hợp tác thời gian thực với các thành viên khác trong nhóm và khách hàng.

Từ xác thực chuyển hướng đến phân tích giảm thứ hạng, hãy xem 9 nhiệm vụ SEO phổ biến mà bạn có thể dễ dàng sử dụng Google trang tính cho:

1. Chuyển hướng xác thực triển khai

Khi thực hiện thiết kế lại, di chuyển Web hoặc bất kỳ bản cập nhật Web nào có thay đổi nhiều URL, bạn sẽ xác thực việc triển khai chuyển hướng 301 bằng cách thực hiện thu thập thông tin danh sách của phiên bản URL cũ để xác minh xem chúng có chuyển hướng đến những cái mới tuân theo các quy tắc ánh xạ đã chỉ định.

Bạn có thể sử dụng hàm IF của Google trang tính để so sánh trạng thái HTTP được triển khai, trang đích chuyển hướng, cũng như siêu dữ liệu trang đích mà bạn nhận được từ thu thập thông tin danh sách so với siêu dữ liệu mà bạn đã đề xuất, như sau:

• HTTP Status: =IF(D2=301, “correct”, “incorrect”)
• Redirect Destination: =IF(B2=E2, “correct”, “incorrect”)
• New Title Implementation: =IF(C2=F2, “correct”, “incorrect”)

2. So sánh các chỉ số hiệu suất của Search Console giữa hai khoảng thời gian để xác định thứ hạng tăng hoặc giảm

Bạn có thể muốn so sánh các chỉ số hiệu suất của Google Search Console từ hai khoảng thời gian khác nhau, ví dụ: trước và sau khi Google cập nhật, một bản phát hành Web quan trọng hoặc triển khai liên quan đến SEO, để xác định khả năng tăng hoặc giảm ở các vị trí trên trang web và các truy vấn bị ảnh hưởng. Mặc dù giao diện người dùng báo cáo Hiệu suất của Google Search Console cho phép so sánh hai khoảng thời gian, nhưng rất tiếc là không thể thấy trực tiếp sự khác biệt giữa chúng ở đó.

Tuy nhiên, chúng tôi có thể thực hiện việc này dễ dàng trong Google trang tính bằng cách sử dụng tùy chọn xuất “Google Trang tính” được cung cấp trực tiếp trong báo cáo và thêm cột so sánh sau mỗi hai khoảng thời gian cho các chỉ số về nhấp chuột, hiển thị, CTR trung bình và vị trí, bằng cách sử dụng hàm IFS để so sánh chúng và bao gồm “tăng”, “giảm” hoặc “không thay đổi” trong các ô của cột so sánh như một kết quả, như có thể thấy bên dưới để so sánh số lần nhấp:

• =IFS(B2>C2, “Increased”, B2

Để dễ dàng phát hiện những từ khóa đã giảm so với những từ khóa đã tăng lên, bạn cũng có thể muốn sử dụng chức năng “định dạng có điều kiện” để thay đổi màu ô dựa trên văn bản của chúng, như được hiển thị ở đây:

Cuối cùng, bạn có thể muốn điều chỉnh công thức so sánh để hỗ trợ chính xác việc xác nhận các thay đổi vị trí, bằng cách bao gồm một số hàm AND lồng nhau:

• =IFS(AND(K2=0,L2>K2), “Decreased”, AND(L2=0,L2L2, “Decreased”, K2
•  

Bạn có thể xem và sao chép Google sheet với ví dụ về các hàm tại đây.

3. Tích hợp số liệu hiệu suất tìm kiếm không phải trả tiền dọc theo dữ liệu liên kết ngược để xác định các trang nhắm mục tiêu các truy vấn phổ biến cần liên kết để cải thiện hiệu suất

Bạn cũng có thể sử dụng Tiện ích bổ sung Search Analytics cho Trang tính miễn phí và hữu ích để nhận trực tiếp các truy vấn được xếp hạng hàng đầu dọc theo các trang được xếp hạng của chúng, nhằm tạo điều kiện thuận lợi cho việc phân tích của chúng (cũng như để tạo bản sao lưu tự động).

Sau đó, bạn có thể dễ dàng tổng hợp các chỉ số SEO khác, chẳng hạn như dữ liệu liên kết mà bạn có thể có trong một trang tính khác từ bản xuất SEMRush bằng cách sử dụng hàm VLOOKUP và đưa dữ liệu liên kết ngược vào một cột bổ sung dọc theo các trang và truy vấn được xếp hạng của Google Search Console:

• =IFERROR(VLOOKUP(C2,‘SEMrush Backlinks’!$A$2:$J$2830,4,false),“”)

Bạn có thể thực hiện kiểu tổng hợp này để xác định các trang nhắm mục tiêu đến các truy vấn phổ biến, chưa xếp hạng cũng như chưa thu hút nhiều liên kết, để xác định cơ hội của các trang có thể cần cải thiện mức độ phổ biến liên kết của chúng.

4. So sánh hiệu suất các trang và truy vấn được xếp hạng trên thiết bị di động với máy tính để bàn

Tiện ích bổ sung Search Analytics cũng cho phép bao gồm dữ liệu được phân đoạn với các bộ lọc của Google Search Console, chẳng hạn như thiết bị cho mọi trang và truy vấn được xếp hạng, điều này cùng với định dạng có điều kiện có thể tạo điều kiện thuận lợi cao cho việc phân tích khoảng cách hiệu suất trên thiết bị di động và máy tính để bàn, để xác định các cơ hội tối ưu hóa .

5. Tích hợp lưu lượng truy cập không phải trả tiền của Google Analytics cùng với hiệu suất tìm kiếm để xác định các cơ hội cải thiện mức độ tương tác và chuyển đổi trên các trang được xếp hạng hàng đầu

Bạn cũng có thể tích hợp dữ liệu lưu lượng truy cập không phải trả tiền từ Google Analytics vào phân tích hiệu suất tìm kiếm hiện tại của mình để xác định các cải tiến tối ưu hóa hơn nữa, ví dụ: đối với những trang đã xếp hạng tốt nhưng có tỷ lệ tương tác hoặc chuyển đổi kém.

Để tạo điều kiện tích hợp dữ liệu Google Analytics, bạn có thể sử dụng tiện ích bổ sung Google Analytics miễn phí, bạn có thể sử dụng hàm CONCATENATE để tạo URL đầy đủ của mọi trang và sau đó sử dụng hàm Vlookup để bao gồm trực tiếp Người dùng, Phiên, Tỷ lệ thoát và bất kỳ chỉ số Google Analytics nào khác dưới dạng các cột của trang tính đã tồn tại với dữ liệu Google Search Console và Backlinks.

6. Xác thực mức độ liên quan của siêu dữ liệu so với các truy vấn được xếp hạng của chúng và cấu hình lập chỉ mục của các trang hoạt động kém nhắm mục tiêu các truy vấn có tiềm năng cao

Để tạo điều kiện thuận lợi cho việc phân tích mức độ liên quan của các trang được xếp hạng so với các truy vấn được nhắm mục tiêu của chúng, bạn cũng có thể tổng hợp siêu dữ liệu dọc theo meta rô bốt và cấu hình thẻ chuẩn cho từng trang. Bạn có thể thực hiện việc này bằng cách thu thập thông tin danh sách chúng và nhập dữ liệu vào trang tính của Google dưới dạng các cột mới, tuy nhiên, điều này có thể được thực hiện nếu bạn đang xác thực một số lượng nhỏ trang bằng cách sử dụng hàm IMPORTXML để trích xuất trực tiếp (bằng cách sử dụng xpath ) và nhập siêu dữ liệu của các trang vào các cột bổ sung. Ví dụ: nếu URL của chúng tôi nằm trong cột C, nó sẽ là:

• =IMPORTXML(C2,“//title”)
• =IMPORTXML(C2,“//meta[@name=’description’]/@content”)
• =IMPORTXML(C2,“//h1”)
• =IMPORTXML(C2,“//meta[@name=’robots’]/@content”)
• =IMPORTXML(C2,“//link[@rel=’canonical’]/@href”)

Như vậy, sẽ dễ dàng hơn nhiều để lọc và chỉ xem những trang chưa ở vị trí tốt nhất và xác minh trực tiếp xem chúng có thể lập chỉ mục hay không, tiêu đề, mô tả và H1 của chúng được tối ưu hóa như thế nào đối với các truy vấn được nhắm mục tiêu, cũng như số lượng liên kết mà họ đang thu hút… tất cả ở một nơi duy nhất.

7. Xác định các vấn đề ăn thịt nội dung giữa nhiều trang xếp hạng cho các truy vấn giống nhau

Một cách khác để tận dụng tích hợp siêu dữ liệu dọc theo các truy vấn được xếp hạng và các trang thông tin Google Search Console, là xác định các vấn đề ăn thịt nội dung bằng cách sắp xếp hoặc lọc theo từng truy vấn và đồng thời xác định xem trang nào đang xếp hạng cho chúng, xác minh xem trang nào thực sự có liên quan trang được xếp hạng đối với họ đang làm như vậy so với phần còn lại và những lý do tiềm ẩn khiến điều này có thể không đúng, bằng cách xem xét mức độ liên quan của siêu dữ liệu, số lượng liên kết ngược và cấu hình lập chỉ mục của các trang “cạnh tranh”.

8. Xác định xem các trang danh mục phù hợp đang xếp hạng cho các loại truy vấn có liên quan của chúng hay không

Chúng tôi có thể thực hiện thêm truy vấn “mức độ liên quan” trước đó so với phân tích đối sánh trang để xác định xem các danh mục phù hợp có thực sự xếp hạng cho các loại truy vấn của chúng hay không. Chúng tôi có thể làm điều này bằng cách:

• Sử dụng các hàm FIND, IFS và IFERROR để xem và xác thực việc sử dụng các chủ đề / tên danh mục cụ thể trong URL của trang.
• Thêm ARRAYFORMULA để tránh phải bao gồm lặp đi lặp lại cùng một công thức trong mỗi ô (!)

Tạo điều này, để tìm các tên khu vực và danh mục khác nhau của trang Remoters (liên kết, sự kiện, blog, công cụ, tiếng Tây Ban Nha, các tên khác):

• = arrayformula (iferror (ifs (not (iserror (find (“job”, C2: C))),

“Việc làm”, không phải (lỗi (tìm (“colivings”, C2: C))),

“Colivings”, không phải (iserror (tìm (“sự kiện”, C2: C))),

“Sự kiện”, không phải (iserror (find (“blog”, C2: C))), “Blog”,

not (iserror (find (“/ es /”, C2: C))), “Spanish”,

not (iserror (find (“tools”, C2: C))), “Tools”,

not (iserror (find (“remoters.net”, C2: C))), “Other”)))

Sau đó, chúng ta nên làm điều gì đó tương tự để phân loại và xác thực các chủ đề truy vấn:

• = arrayformula (iferror (ifs (not (iserror (find (“job”, A2: A))),

“Việc làm”, không phải (lỗi (find (“coliving”, A2: A))),

“Colivings”, không phải (iserror (tìm (“sự kiện”, A2: A))),

“Sự kiện”, không phải (lỗi sai (tìm (“công cụ”, A2: A))), “Công cụ”,

not (iserror (find (“conference”, A2: A))), “Events”,

not (iserror (find (“remote work”, A2: A))), “Jobs”,

not (iserror (find (“remoters”, A2: A))), “Branded”)))

Cuối cùng, chúng tôi cũng có thể thêm các quy tắc định dạng có điều kiện để giúp phát hiện dễ dàng hơn khi các loại truy vấn và khu vực trang web không thuộc cùng một chủ đề, để giúp dễ dàng hơn trong việc xác định xếp hạng có thể bị lệch trên các danh mục khác nhau:

Sau đó, chúng tôi có thể sử dụng thêm định dạng có điều kiện để tô màu đỏ cho các thứ hạng bị lệch để giúp phát hiện chúng dễ dàng hơn.

9. Xác định các trang được xếp hạng tiềm ẩn các vấn đề về điều chỉnh sai lệch quốc tế

Chúng tôi cũng có thể sử dụng cách tiếp cận tương tự khi chúng tôi có các trang web đa quốc gia hoặc đa ngôn ngữ để xác định các vấn đề lệch thứ hạng trên Web quốc tế bằng cách nhập quốc gia của bảng xếp hạng thông qua Tiện ích phân tích tìm kiếm dọc theo các truy vấn và trang. Bằng cách này, chúng tôi có thể phát hiện các trang thuộc xếp hạng phiên bản tiếng Tây Ban Nha ở các quốc gia nói tiếng Anh mà đã có nội dung phù hợp bằng tiếng Anh hoặc các trang thuộc bảng xếp hạng phiên bản Mexico ở Tây Ban Nha, có các trang khác được xếp hạng, v.v. chúng tôi sẽ muốn ưu tiên triển khai các chú thích hreflang.

Trong trường hợp này, tôi sử dụng nó để xác thực tình huống đầu tiên, các trang thuộc phiên bản tiếng Tây Ban Nha không được xếp hạng ở các quốc gia nói tiếng Tây Ban Nha bằng cách sử dụng hàm IFS và hàm IF lồng nhau để xác định xem trang thuộc về phiên bản tiếng Tây Ban Nha hay tiếng Anh trước tiên hay không:

• = arrayformula (iferror (ifs (not (iserror

(find (“/ es /”, B2: B))), “tiếng Tây Ban Nha”,

not (iserror (find (“remoters.net”, B2: B))),

“Tiếng Anh”)))

và sau đó xác thực xem có sự sai lệch so với quốc gia được xác định của thứ hạng được cung cấp thông qua Tiện ích phân tích tìm kiếm hay không, gắn thẻ là “căn chỉnh” nếu đó là quốc gia nói tiếng Tây Ban Nha và “bị lệch” nếu không:

• = IF (AND (C2 = “Tiếng Tây Ban Nha”, HOẶC (D2 = “esp”,

D2 = “arg”, D2 = “mex”, D2 = “ury”,

D2 = “ven”, D2 = “per”, D2 = “col”, D2 = “dom”,

D2 = “bol”, D2 = “pan”)), “Căn chỉnh”, “Sai lệch”)

Suy nghĩ cuối cùng

Google Trang tính cực kỳ mạnh mẽ; bài đăng này chỉ làm xước bề mặt của những gì bạn có thể làm với nó.

Tôi khuyên bạn nên thử với các công thức ở trên và xem bạn có thể nghĩ ra điều gì. Tôi cũng khuyên bạn nên xem toàn bộ thư viện công thức của Google Trang tính.

Tuy nhiên, đó vẫn chỉ là bước khởi đầu: Google Trang tính cũng tích hợp với Zapier và IFTTT, có nghĩa là bạn cũng có thể kết nối với hàng trăm công cụ và dịch vụ khác.

Và nếu bạn muốn thực sự nâng cao, hãy xem Apps Script — nó cực kỳ mạnh mẽ!

Nếu bạn có bất kỳ cách sử dụng sáng tạo nào cho Google Trang tính của riêng mình, vui lòng cho tôi biết trong phần nhận xét. Tôi rất thích nghe chúng!