Làm thế nào để sử dụng BigQuery để mở rộng SEO

Diendanraovataz.net - Diễn đàn rao vặt - Đăng tin mua bán - Quảng cáo miễn phí - Hiệu quả - Làm thế nào để sử dụng BigQuery để mở rộng SEO? Bài viết này sẽ giúp bạn:

- Làm việc với một lượng dữ liệu lớn. Ví dụ: audit liên kết internal, backlink, phân tích file log
- Thực hiện các truy vấn phức tạp trên dữ liệu một cách nhanh chóng
- Cải thiện cách bạn làm việc với các vấn đề bằng cách buộc bạn phải xác định những gì bạn đang tìm kiếm thay vì lang thang không mục đích với dữ liệu.

Chúng ta sẽ làm gì khi các công cụ lỗi?

Bạn sẽ làm gì khi excel lỗi? Bạn đang cố gắng để lọc một tập dữ liệu lớn với một hàm VLOOKUP và excel đã nằm xuống và chết. Excel là một công cụ tuyệt vời nhưng điều đó không có nghĩa là chúng ta sẽ sử dụng nó cho tất cả mọi thứ.

Bạn nên sử dụng những gì?

Câu trả lời truyền thống là SQL. Giao diện miễn phí để làm việc với nó thường rất trực quan và không trực quan đối với những người tìm kiếm để bắt đầu nó một cách nhanh chóng.

BigQuery là gì?

Dưới đây là những lời giới thiệu từ Google

"Đó là một kho dữ liệu nhanh chóng, tiết kiệm và quản lý đầy đủ để phân tích dữ liệu với quy mô lớn". Nếu chúng ta làm nó cụ thể hơn, chúng ta sẽ nhận được:

Đó là một cơ sở dữ liệu SQL mà bạn có thể dựa vào và chạy các truy vấn trên đó. Nó sẽ làm việc với bất kể kích thước dữ liệu của bạn.

Tại sao sử dụng BigQuery để phân tích dữ liệu?

Thật sự là có hai lý do. Việc đầu tiên là: tại sao bạn nên sử dụng SQL để phân tích các tập dữ liệu lớn của bạn?

1. Nó được thiết kế cho các truy vấn khó hiểu phức tạp mà Excel không thể giải quyết được.

2. Nhiều tổ chức lưu trữ dữ liệu của họ trong cơ sở dữ liệu SQL. Được làm việc trong SQL là một kỹ năng nghề nghiệp có giá trị.

3. Nó rất hợp lý, mặc dù bạn sẽ cần phải tìm hiểu một số cú pháp; việc viết các truy vấn có ý nghĩa logic trực quan.

4. Nếu bạn chuẩn hóa tên bạn sử dụng, bạn có thể tái sử dụng các truy vấn phức tạp tương tự trên nhiều tập dữ liệu, cho phép bạn tạo để trả lời một câu hỏi phức tạp và tái sử dụng nó một cách dễ dàng.

5. Cá nhân tôi thấy nó rất tốt để làm việc. Bạn có thể biết những câu hỏi mà bạn muốn hỏi để nhận được câu trả lời.

Thứ hai, tại sao sử dụng BQ để phân tích các tập dữ liệu lớn?

1. Chi phí của nó khá là rẻ, trừ khi bạn đang làm một cái gì đó thực sự rất lớn.
2. Các thiết lập có thể được thực hiện mà không cần phải có kỹ năng lập trình
3. Giao diện cực kỳ đơn giản để sử dụng

Khi nào bạn nên sử dụng BigQuery để phân tích dữ liệu?

Như chúng tôi đã đề cập ở trên, trường hợp để sử dụng đó là khi Excel bị lỗi hoặc trở nên chậm chạp hoặc bạn cần phải liên tục trả lời một câu hỏi phức tạp trên các tập dữ liệu khác nhau và viết một truy vấn duy nhất là nhanh hơn nhiều so với việc thao tác trên tập dữ liệu Excel.

1. Đặt câu hỏi thông minh

Bạn không thể chỉ thao tác và chơi xung quanh với các dữ liệu trong một bảng SQL như bạn có thể làm trong Excel. Vì vậy, hãy định nghĩa một vài câu hỏi chúng tôi có thể trả lời trong bài viết này:

- Thẻ title rộng hơn 512px trong dữ liệu thu thập của chúng tôi được ưu tiên theo số lượng khách truy cập tự nhiên?

- Có bao nhiêu session được bắt đầu trên các trang đích non-canonical?

- Những phần trang web hoàn thành mục tiêu của chúng tôi đến từ đâu?

Tôi đã thu thập trang web Distilled.net với Screaming Frog (SF) và sau đó tạo ra một số dữ liệu giả Google Analytics (GA) cùng với nó để trả lời những câu hỏi này. Tập dữ liệu trong trường hợp này là không lớn và có thể được thực hiện trong Excel nhưng nó có thể làm tốt hơn để chúng tôi cảm thấy thoải mái.

2. Nhận được một tài khoản BigQuery

Trước tiên, bạn sẽ cần một tài khoản Google.

Sau đó, bạn đăng ký dùng thử BQ miễn phí. Bạn không cần phải sử dụng bất kỳ các phần khác của phần mềm trong Google Cloud Platform, mặc dù sau này chúng tôi sẽ sử dụng Google Cloud Storage (GCS) trong hướng dẫn này.

(GCS là giống như một Dropbox/Google Drive được sử dụng trong các doanh nghiệp). Khi bạn đã hoàn tất việc đăng ký, bạn hãy nhìn vào một Dashboard.



Tôi đã làm mờ đi ID để bảo mật.

Từ đây chúng ta cần phải truy cập BigQuery. Điều này có thể được thực hiện từ menu trên cùng bên trái. Tại đây bạn cũng có thể tìm thấy dưới “Storage" mà chúng tôi đã đề cập trước đó.

3. Bảng điều khiển BQ



Đây là giao diện chính BQ. Bạn hãy nhìn trên màn hình:

- Đây là những tùy chọn menu chính

+ Trình soạn thảo

+ Query History là một bản ghi của các truy vấn gần đây bạn đã thực hiện.

+ Job History là một bản ghi của tất cả các hành động gần đây bạn đã thực hiện với các dữ liệu. Bạn phải tải và giải nén nó.

- Đây là nơi mà tất cả các tập dữ liệu được lưu trữ. Mỗi tập dữ liệu là một dự án. Mỗi tập dữ liệu chứa các bảng cá nhân có chứa dữ liệu.

- Đây là nơi bạn viết câu truy vấn

- Đây là Job History và Query History được hiển thị.

4. Làm thế nào để bạn tải dữ liệu vào BQ?

Đầu tiên chúng ta tạo ra một tập dữ liệu mới. Chúng tôi sẽ gọi chúng là distilled_test.

Chúng ta click vào mũi tên nhỏ ở phía trên và nhập dữ liệu chi tiết của chúng tôi.

- ID: distilled_test
- Data location: EU
- Data expiration: bạn có thể thiết lập Never nếu bạn nghĩ rằng bạn quên xóa nó.

Sau đó chúng tôi di chuột qua các số liệu và nhấp vào biểu tượng dấu cộng. Chúng tôi sẽ nhận được tùy chọn để tạo ra một bảng và tải dữ liệu của chúng tôi.


Dưới đây là form, bạn hãy điền đầy đủ.

Việc tải dữ liệu vào cơ sở dữ liệu SQL khác so với việc tải dữ liệu vào Excel - quy tắc nghiêm ngặt hơn. 4 điểm chính là:

4.1. Bạn phải có cùng số cột cho mỗi hàng. Lấy ví dụ như sau:

- Apple, potato, chocolate
- Apple, donut
- Apple, potato, chocolate
- Apple, donut

Cột đầu tiên là hợp lệ, cột thứ 2 không làm việc. Rất may là, hầu hết các export của chúng tôi giống như GA và SF là mặc định và bạn có thể kích hoạt tùy chọn “Allow jagged rows” để bỏ qua điều này.

4.2. Theo mặc định, bạn không thể tải dữ liệu với các dòng mới bên trong một trường. Đây không phải là vấn đề lớn nhưng bạn có thể chạy nó với crawls.

Thật ra, chúng tôi sẽ chạy với tập dữ liệu crawl Distilled của chúng tôi, vì chúng tôi có một số trang khá kinh khủng. Rất may, nó rất dễ dàng để giải quyết; chúng ta làm điều này bằng cách đánh dấu vào ô "Allow Quoted Newlines".

4.3. Bạn phải định nghĩa các kiểu dữ liệu cho mỗi cột. Điều này sẽ giới hạn những gì bạn có thể làm; Ví dụ, bạn không thể thực hiện bổ sung vào chuỗi. Bạn có thể làm dòng này bởi dòng trong một form hoặc chỉ trong một text box. Tôi đã cung cấp hai lược đồ dưới đây cho Google Landing Page Report & Screaming Frog, bạn có thể copy và paste vào text box. (SF có thể cần thay đổi nhỏ nếu bạn sử dụng nó cho các tập dữ liệu SF khác, như việc export SF trả về một con số động của các cột tùy thuộc vào những gì nó tìm thấy).

4.4. Bạn phải nói với BigQuery về số lượng lỗi bạn muốn cho phép trong dữ liệu - nếu không nó sẽ fail tại một lỗi duy nhất. Nếu tôi tải lên các tập tin cùng một lúc, tôi giả định rằng nó không có lỗi. Nếu tôi tự động hóa một cái gì đó lớn, tôi thường cho phép một số lỗi và theo dõi chúng để nó không bị hỏng khi tôi không xem.

Cả 2 tập dữ liệu chúng tôi cung cấp bạn chỉ cần tải thẳng vào Schema như bên dưới:

SF Standard Export Schema:

PHP:

address:STRING,content:STRING,status_code:INTEGER,status:STRING,title1:STRING,title_1_length:INTEGER,title_1_pixel:INTEGER,meta_description:STRING,meta_description_length:INTEGER,meta_description_pixel:INTEGER,meta_keyword:STRING,meta_keyword_length:INTEGER,h1_1:STRING,h1_1_length:INTEGER,h1_2:STRING,h1_2_length:INTEGER,h2_1:STRING,h2_1_length:INTEGER,h2_2:STRING,h2_2_length:INTEGER,meta_robots:STRING,meta_refresh:STRING,canonical:STRING,size:INTEGER,word_count:INTEGER,level:INTEGER,inlinks:INTEGER,outlinks:INTEGER,external_outlinks:INTEGER,hash:STRING,response:FLOAT,last_mod:STRING,redirect_url:STRING

GA Landing Pages Export Schema:

PHP:

landing_page:STRING,sessions:INTEGER,percent_new_sessions:FLOAT,new_users:INTEGER,bounce_rate:FLOAT,pages_per_sessions:FLOAT,avg_session_length:FLOAT,transactions:INTEGER,revenue:FLOAT,ecomm_conversion_rate:FLOAT

5. Thêm một vài trường bổ sung

Nó rất là hữu ích để làm một số xử lý bổ sung vào dữ liệu SF của chúng tôi. Cụ thể, chúng tôi muốn tách riêng các thông số URL của chúng tôi và giải nén mỗi thư mục vì vậy chúng tôi có thể làm việc với nó một cách dễ dàng.

Chúng tôi có thể thực hiện điều đó bằng cách chạy các truy vấn sau đây và lưu lại với một bảng mới.

PHP:

SELECT
*,
SUBSTR(path_and_host,INSTR(address,'.net')+4) as path,
NTH(3, SPLIT(path_and_host, '/')) page_path_1,
NTH(4, SPLIT(path_and_host, '/')) page_path_2,
NTH(5, SPLIT(path_and_host, '/')) page_path_3,
NTH(6, SPLIT(path_and_host, '/')) page_path_4
FROM (
SELECT
*,
NTH(2,SPLIT(address, '?')) as query,
NTH(1,SPLIT(address, '?')) as path_and_host,
FROM
[distilled_test.crawl_data]
)

Chúng ta có thể thấy ở đây tôi đã chạy các truy vấn, nó đã trả ra các trường mới và tôi lưu nó. Sau đó, chúng tôi xóa bảng cũ và tiếp tục đi. Nhìn trên bảng của chúng tôi, bây giờ chúng ta cần phải có:

6. Chạy các truy vấn với BQ

Tôi sẽ giải thích các truy vấn này. Ngoài ra còn có một số các khóa học miễn phí về SQL.

Câu hỏi 1: Những thẻ title rộng hơn 512px trong dữ liệu thu thập của chúng tôi được ưu tiên bởi số lượng khách truy cập tự nhiên?

Đầu tiên, chúng tôi muốn đề cập đến thẻ title với các trang từ các số liệu SF:

PHP:

SELECT
address, title1
FROM
[distilled_test.crawl_data_final]

Sau đó, chúng tôi muốn lọc nó cho tất cả các title với độ rộng lớn hơn 512px.

PHP:

SELECT
address, title1
FROM
[distilled_test.crawl_data_final]
WHERE
title_1_pixel > 512

Bây giờ nó trở nên phức tạp hơn một chút. Ưu tiên số lần truy cập tự nhiên, chúng ta cần phải mang theo ga_data của chúng tôi. Điều này có nghĩa là chúng ta cần phải nối hai bảng.

PHP:

SELECT
address, title1, sessions
FROM (
SELECT
address,
path,
title1
FROM
[distilled_test.crawl_data_final]
WHERE
title_1_pixel > 512 ) AS crawl_query
LEFT JOIN (
SELECT
landing_page,
sessions
FROM
[distilled_test.ga_data] ) AS ga_data
ON
crawl_query.path = ga_data.landing_page
ORDER BY
sessions desc

Hãy tìm các dấu ngoặc và các truy vấn được đánh tên. Chúng tôi đã chạy 2 truy vấn riêng biệt:

Đầu tiên, chúng ta có các truy vấn của chúng ta để lọc tất cả các title trên 512px:

PHP:

( SELECT
address,
path,
title1
FROM
[distilled_test.crawl_data_final]
WHERE
title_1_pixel > 512 ) AS crawl_query

Sau đó, chúng tôi có một truy vấn thứ hai trả về session cho mỗi trang đích:

PHP:

SELECT
landing_page,
sessions
FROM
[distilled_test.ga_data] ) AS ga_data

Sau đó, chúng tôi nối hai bảng đó với nhau.

Có rất nhiều loại JOINs khác nhau trong SQL. Lời giải thích mà tôi thấy tốt nhất là ở bên dưới. Và câu trả lời của chúng tôi, tôi đã liệt kê dưới đây:

PHP:

https://www.distilled.net/resources/videos/sl-bos-...
https://www.distilled.net/blog/distilled/the-diffe...
https://www.distilled.net/resources/everything-you...
https://www.distilled.net/resources/videos/sl-lond...
https://www.distilled.net/resources/competitor-res...

Câu hỏi 2: Có bao nhiêu session bắt đầu trên các trang đích non-canonical?

Đầu tiên chúng ta cần phải nhận được tổng số session trên trang:

PHP:

SELECT
landing_page,
sessions
FROM
[distilled_test.ga_data]) AS ga_data

Sau đó, chúng ta cần tránh một cái bẫy. Đó chính là những vấn đề có thể dễ dàng bị bỏ qua nếu bạn đã làm điều này trong Excel nhưng SQL sẽ buộc bạn phải suy nghĩ.

Tìm URL canonical chuẩn là rất khó. Nếu chúng ta chọn tất cả các URL non-canonical, hãy loại bỏ những URL đó và nối vào, sau đó chúng tôi giả định tất cả mọi thứ khác trong GA là URL canonical và giả sử tất cả mọi thứ khác là non-canonical.

Vì vậy, hãy chọn tất cả các URL canonical.

PHP:

SELECT
address
FROM
[distilled_test.crawl_data_final]
WHERE
address = canonical
Và sau đó JOIN hai bảng và SELECT nơi mà chúng không có URL canonical phù hợp và ORDER BY theo session và ưu tiên chúng.
Code:
SELECT
landing_page,
sessions
FROM (
SELECT
landing_page,
sessions
FROM
[distilled_test.ga_data]) AS ga_data
LEFT JOIN (
SELECT
path,
FROM
[distilled_test.crawl_data_final]
WHERE
address = canonical ) AS crawl_data
ON
ga_data.landing_page = crawl_data.path
WHERE
crawl_data.path IS NULL
ORDER BY
sessions DESC

Tất cả đã hoàn thành. Dưới đây là top 5 URL:

- /resources/videos/sl-bos-16-marcustober
- /company/people/tom-critchlow.html/
- /resources/videos/sl-sandiego-15-scottedwards
- /resources/videos/community
- /tone-of-voice//

Câu hỏi 3: Những phần trang web hoàn thành mục tiêu của chúng tôi đến từ đâu?

Chúng ta có thể làm điều này bằng cách nối hai bảng như chúng ta đã làm trong hai truy vấn trước đó, hoặc chúng ta có thể xử lý dữ liệu như chúng ta đã làm với dữ liệu SF và truy vấn nó trực tiếp. Điều đó thực sự sẽ dễ dàng hơn và tránh được một số cạm bẫy khác có thể.

PHP:

SELECT
*,
NTH(1, SPLIT(path, '/')) page_path_1,
NTH(2, SPLIT(path, '/')) page_path_2,
NTH(3, SPLIT(path, '/')) page_path_3,
NTH(4, SPLIT(path, '/')) page_path_4
FROM (
SELECT
*,
NTH(1,SPLIT(landing_page, '?')) as path,
NTH(2,SPLIT(landing_page, '?')) as query
FROM
[distilled_test.ga_data]
)

Chúng tôi đã có một truy vấn nằm bên trong một truy vấn khác.

PHP:

SELECT
*,
NTH(1,SPLIT(landing_page, '?')) as path,
NTH(2,SPLIT(landing_page, '?')) as query
FROM
[distilled_test.ga_data]

Câu lệnh thứ 2:

PHP:

SELECT
*,
NTH(1, SPLIT(path, '/')) page_path_1,
NTH(2, SPLIT(path, '/')) page_path_2,
NTH(3, SPLIT(path, '/')) page_path_3,
NTH(4, SPLIT(path, '/')) page_path_4
FROM (
Query_1
)

Khi chúng tôi có bảng mới, chúng tôi chỉ có thể hỏi một câu hỏi rất cơ bản để có được câu trả lời:

PHP:

SELECT
page_path_1, SUM(transactions) as total_transactions
FROM
[distilled_test.ga_data_final]
GROUP BY
page_path_1
ORDER BY
total_transactions DESC

Điều mới mẻ duy nhất ở đây là GROUP BY. Chúng tôi muốn tổng của tất cả các giao dịch, vì vậy chúng tôi thêm một trường SUM transactions. Chúng tôi làm điều đó với GROUP BY.

Xin chúc mừng, bạn đã kết thúc! Chúng ta không chỉ nhận được một số câu trả lời hữu ích, nếu bây giờ bạn được hỏi những câu hỏi tương tự trên một dự án khác hoặc một tập tin được cập nhật, bạn chỉ cần copy và paste cùng một truy vấn và nhận được kết quả mới. Chúng tôi đã thực hiện các công việc khó khăn một lần và sẽ không bao giờ phải làm điều đó một lần nữa.

Một điều cuối cùng về việc export kết quả

Muốn export kết quả thì bạn sẽ phải làm như nào? Bạn có thể tải về các kết quả từ truy vấn nhỏ trực tiếp như CSV nhưng để nhận nhiều kết quả hơn, bạn sẽ cần phải lưu chúng như một bảng và sau đó export bảng vào GCS và tải về.

Ở đây tôi đang tải các kết quả vào một test_distilled trong GCS.


Đó là những cách mà tôi đã làm. Bạn có cách nào khác tôi, vui lòng comment bên dưới bài viết này. Lưu ý rằng đây là một bài viết nghiên cứu chuyên sâu về Seo nên cần bạn có một kiến thức Seo tốt để hiểu được điều mà tôi muốn nói trong bài viết này.

Ghi nguồn

PHP:

www.thegioiseo.com