Quan điểm của tác giả là hoàn toàn độc lập (trừ trường hợp không thể tưởng tượng được của sự mê hoặc) và có thể không luôn phản ánh quan điểm của Moz.
[Thời gian đọc dự kiến: 13 phút]
Bài viết này sẽ giúp bạn làm gì?
Khi công cụ cốt yếu, chúng ta làm gì?
Bạn làm gì khi Excel không hoạt động? Có thể bạn có hơn một triệu dòng dữ liệu. Có thể bạn đã cố gắng lọc một tập dữ liệu lớn bằng VLOOKUP và Excel đã quyết định bị treo. Excel là một công cụ tuyệt vời, nhưng điều đó không có nghĩa là chúng ta nên sử dụng nó cho mọi thứ.
Bạn nên sử dụng gì?
Câu trả lời truyền thống là SQL, nhưng thiết lập máy chủ SQL nếu bạn chưa làm và bạn không phải là người kỹ thuật là một thách thức. Giao diện miễn phí để làm việc với nó thường rất phức tạp và không thân thiện với những người muốn bắt đầu nhanh chóng.
Hãy đến với BigQuery.
BigQuery là gì?
Theo mô tả của Google: “Một kho dữ liệu quản lý toàn diện, kinh tế và nhanh chóng cho phân tích dữ liệu quy mô lớn.”
Nếu chúng ta mô tả cụ thể hơn, chúng ta có được:
Đó là một cơ sở dữ liệu SQL (giống SQL) mà bạn có thể đưa dữ liệu vào và chạy các truy vấn trên. Nó sẽ hoạt động bất kể kích thước của dữ liệu của bạn.
Tại sao sử dụng BigQuery để phân tích dữ liệu?
Có thực sự hai phần trong điều này. Phần đầ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 mình?
Thứ hai, tại sao sử dụng BQ để phân tích các tập dữ liệu lớn?
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, các trường hợp sử dụng chính cho điều này là khi Excel không hoạt động/quá chậm hoặc khi bạn cần trả lời một câu hỏi phức tạp một cách lặp đi lặp lại trên các tập dữ liệu khác nhau và việc viết một truy vấn duy nhất nhanh hơn nhiều so với xử lý nhiều tập dữ liệu trong Excel.
Có những ví dụ tốt nào về các vấn đề này?
Cá nhân tôi thấy máy tính của tôi bắt đầu gặp khó khăn ngay khi có hơn 600.000 dòng, vì vậy thông thường tại thời điểm đó tôi sẽ chuyển sang BQ.
Hy vọng bây giờ bạn xem đây là một lựa chọn khi Excel bị sập, vì vậy chúng ta hãy bắt đầu.
1. Đặt những câu hỏi thông minh
Bạn không thể chỉ đơn giản là thao tác và chơi với dữ liệu trong một bảng SQL như bạn làm trong Excel. Đôi khi một nhiệm vụ yêu cầu loại phân tích và khám phá thủ công đó (mà bảng tổng hợp là tuyệt vời); tuy nhiên, tôi đã thấy rằng nhiều vấn đề tôi gặp phải hàng ngày không cần phân tích đó. Chúng yêu cầu tôi xác định chính xác câu hỏi mà tôi muốn trả lời.
Vì vậy, hãy xác định một số câu hỏi chúng ta có thể trả lời với bài viết này:
Tôi đã lấy dữ liệu của trang Distilled.net bằng Screaming Frog (SF) và sau đó tạo một số dữ liệu Google Analytics (GA) giả để đi kèm với nó để trả lời các câu hỏi này. Tập dữ liệu trong trường hợp này không lớn và có thể làm được trong Excel, nhưng nó sẽ hoạt động tốt để làm cho chúng ta thoải mái.
2. Tạo một tài khoản BigQuery
Đầu tiên, bạn cần một tài khoản Google.
Sau đó, bạn đăng ký dùng thử miễn phí BigQuery, mặc dù thực tế bạn đang nhận là một tài khoản Google Cloud Platform. Bạn không cần sử dụng các phần mềm khác trong Google Cloud Platform, mặc dù chúng ta sẽ sử dụng Google Cloud Storage (GCS) sau này trong hướng dẫn này.
(GCS tương tự như Dropbox/Google Drive dành cho doanh nghiệp, nhưng ít thân thiện với người dùng.)
Sau khi bạn đã đăng ký, bạn sẽ nhìn thấy Bảng điều khiển.
Tôi đã che đi ID để bảo mật.
Từ đây, chúng ta cần truy cập vào BigQuery. Điều này có thể được thực hiện từ menu trên cùng bên trái. Ở đây, bạn cũng có thể tìm thấy lưu trữ đám mây trong mục “Lưu trữ”, như chúng tôi đã đề cập trước đó.
3. Giao diện BQ
Đây là giao diện chính của BQ. Một lượt qua màn hình:
Đủ nhàm chán. Hãy học bằng cách thực hành và tải lên các tập dữ liệu ví dụ.
4. Làm thế nào để bạn tải dữ liệu vào BQ?
Đầu tiên, chúng ta tạo một tập dữ liệu mới. Chúng ta sẽ gọi tập dữ liệu của chúng ta là “distilled_test”.
Chúng ta nhấp vào mũi tên nhỏ ở trên số 2 (trong hình ảnh ở trên) và nhập thông tin của chúng ta.
Sau đó, chúng ta di chuột qua tập dữ liệu và nhấp vào biểu tượng cộng. Chúng tôi sẽ có tùy chọn để tạo bảng và tải lên dữ liệu của chúng tôi.
Đây là mẫu, đã được điền đầy đủ.
Việc tải dữ liệu vào cơ sở dữ liệu kiểu SQL khác so với việc tải dữ liệu vào Excel – các quy tắc nghiêm ngặt hơn trừ khi bạn nói cho nó biết điều ngược lại. 4 quy tắc chính là:
1. Bạn phải có cùng số cột cho mỗi dòng. Hãy xem ví dụ CSV sau:
Câu đầu tiên hợp lệ; câu thứ hai sẽ không hoạt động. May mắn thay, hầu hết các tệp xuất thông thường như GA & SF đã làm điều này theo mặc định, và bạn có thể bật tùy chọn “Cho phép các dòng rời rạc” để bỏ qua điều này.
2. Theo mặc định, bạn không thể tải dữ liệu có dòng mới trong một trường. Một lần nữa, điều này không phải là một vấn đề trong hầu hết các trường hợp, nhưng bạn có thể gặp phải nó với các quét.
Thực tế, chúng ta sẽ gặp phải nó với tập dữ liệu quét của chúng tôi, vì chúng ta có một số trang tồi tệ khá tồi tệ nơi chúng ta có nhiều đoạn văn trong H2. May mắn, việc giải quyết nó rất dễ dàng; chúng ta làm điều này bằng cách chọn hộp “Cho phép các dòng mới được trích dẫn”.
3. Bạn phải xác định 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 phép cộng trên chuỗi. Bạn có thể làm điều này dòng sau dòng trong một biểu mẫu hoặc chỉ trong một hộp văn bản. Tôi đã cung cấp hai lược đồ dưới đây cho Báo cáo Trang đích Google & Xuất cơ bản của Screaming Frog để bạn bắt đầu, bạn có thể sao chép và dán vào hộp văn bản. (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, vì xuất SF trả về một số cột động tùy thuộc vào những gì nó tìm thấy).
4. Bạn phải cho biết BigQuery bao nhiêu lỗi bạn muốn cho phép trong dữ liệu – nếu không, nó sẽ thất bại với một lỗi duy nhất. Nếu tôi tải lên các tệp một cách riêng lẻ, tôi thường cho rằng không có lỗi trừ khi được chứng minh 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à giám sát chúng, để tránh hỏng khi tôi không xem.
Chỉ còn một điều khác cần đề cập từ màn hình tải lên không tự giải thích là vị trí. Bạn có thể tải lên các tập dữ liệu bằng tay, sử dụng Google Drive hoặc Google Cloud Storage (GCS). Vì tôi thường sử dụng BQ với các tập dữ liệu rất lớn, tôi sử dụng GCS (GCS thực sự rất đơn giản nên tôi sẽ không đề cập đến nó ở đây), nhưng bạn có thể sử dụng bất cứ cái gì phù hợp với bạn.
Cả hai tập dữ liệu ví dụ mà tôi cung cấp đều nên tải trực tiếp vào bằng các lược đồ dưới đây:
Lược đồ Xuất SF tiêu chuẩn:
Lược đồ Xuất Trang đích GA:
Một lưu ý nhỏ: Screaming Frog thêm một hàng nữa ở đầu CSV gọi là “Internal All”. Tôi đã xóa nó trước đó.
5. Thêm một vài trường bổ sung
Trước khi chúng ta đặt bất kỳ câu hỏi nào, việc xử lý thêm một số dữ liệu của chúng ta từ SF rất hữu ích. Cụ thể, chúng tôi muốn tách riêng các tham số URL của chúng tôi và trích xuất từng thư mục để làm việc với nó một cách dễ dàng.
Chúng ta có thể làm điều đó bằng cách chạy truy vấn sau và lưu kết quả dưới dạng một bảng mới. (Vì SQL này hơi phức tạp và bạn cũng sẽ không sử dụng các chức năng này rất thường xuyên [nếu có], chúng tôi sẽ giải thích điều này ở cuối sau khi bạn đã xem tất cả các truy vấn khác.)
Ở đây, chúng ta có thể thấy tôi đã chạy truy vấn, nó đã trả về các trường mới và tôi đang chuẩn bị Lưu thành Bảng.
Sau đó, chúng ta xóa bảng cũ và chúng ta đã hoàn thành. Nhìn sang các bảng của chúng ta, giờ chúng ta có:
6. Chạy các truy vấn với BQ
Đến phần thú vị: trả lời những câu hỏi đó.
Tôi sẽ giải thích các truy vấn này, nhưng chúng tôi sẽ nhanh chóng để bài viết này có chiều dài hợp lý. Cũng có một số khóa học miễn phí tuyệt vời về học SQL để điền vào những khoảng trống này. Code Academy có một khóa học bạn có thể tìm thấy ở đây, tôi rất khuyến khích bạn nên tham gia.
C1: Các thẻ tiêu đề nào có chiều rộng lớn hơn 512px trong dữ liệu truy cập của tôi, ưu tiên theo số lượng truy cập tự nhiên?
Hãy xử lý từng phần. Đầu tiên, chúng ta muốn trả về các thẻ tiêu đề với trang chúng thuộc về từ tập dữ liệu SF:
Sau đó, chúng ta muốn lọc đi tất cả các tiêu đề có chiều rộng lớn hơn 512px.
Cho đến nay, mọi thứ đều tốt!
Bây giờ nó trở nên phức tạp hơn một chút. Để ưu tiên theo số lượng truy cập tự nhiên, chúng ta cần đưa ga_data của chúng ta vào. Điều này có nghĩa là chúng ta cần kết hợp hai bảng.
Để hiểu được điều này, hãy tìm kiếm các dấu ngoặc và các truy vấn được đặt tên. Chúng tôi đã chạy hai truy vấn riêng biệt:
Thứ nhất, chúng tôi có truy vấn của chúng tôi lọc tất cả các tiêu đề trên 512px:
Sau đó, chúng tôi có một truy vấn thứ hai trả về các phiên cho mỗi trang đích:
Sau đó, chúng tôi kết hợp cả hai. Chúng tôi nói với BQ: nếu trang từ truy vấn đầu tiên khớp với trang đích từ truy vấn thứ hai, hãy kết hợp những hàng đó lại. GA theo mặc định chỉ cung cấp cho bạn đường dẫn (tức là nó không lưu trữ www.distilled.net/page, chỉ /page), vì vậy chúng tôi đang kết hợp nó với đường dẫn mà chúng tôi đã tạo trong bước trước.
Có nhiều loại JOIN khác nhau trong SQL. Giải thích tốt nhất mà tôi tìm thấy về chúng là ở đây, nếu bạn quan tâm.
Và đây là câu trả lời của chúng tôi, tôi đã liệt kê 5 URL hàng đầu dưới đây.
C2: Bao nhiêu phiên bắt đầu trên các trang đích phi chuẩn?
Đầu tiên, chúng ta cần tính tổng số phiên cho mỗi trang:
Sau đó, chúng tôi cần tránh một sai lầm. Đó là chính vấn đề mà dễ bị bỏ qua nếu bạn làm điều này trong Excel, nhưng SQL sẽ buộc bạn suy nghĩ về điều này. Tìm URL chuẩn thực sự là một chút phiền hà. Nếu chúng tôi chọn tất cả các URL phi chuẩn từ quét của chúng tôi, xóa chúng và kết hợp dữ liệu trên đó, sau đó chúng tôi đang giả định tất cả những gì khác trong GA là chuẩn và điều đó chắc chắn sẽ không đúng. Vì vậy, chúng tôi cần làm ngược lại: tìm tất cả các URL chuẩn và giả định rằng mọi thứ khác là phi chuẩn (một giả định ít tồi, mặc dù điều này sẽ bỏ qua các trang chuẩn không có thuộc tính cha).
(Điều này cũng giả định rằng một trang không có thuộc tính chuẩn là không chuẩn, nhưng chúng ta không thể làm gì về điều đó. Nếu đây là một cuộc kiểm tra, chúng tôi có thể tìm tất cả các trang không có thuộc tính chuẩn và yêu cầu khách hàng thiết lập chúng.)
Vì vậy, hãy chọn tất cả các URL chuẩn.
Sau đó, chúng tôi kết hợp hai bảng và chọn chỉ các mục không khớp với URL chuẩn và SẮP XẾP THEO phiên để ưu tiên chúng.
Hoàn thành rồi! Đây là 5 URL hàng đầu.
C3: Các phần của trang web nào tạo ra các hoàn thành mục tiêu của chúng tôi?
Chúng tôi có thể làm điều này bằng cách kết hợp hai bảng như chúng tôi đã làm trong hai truy vấn trước, hoặc chúng ta có thể xử lý dữ liệu như ban đầu chúng tôi đã làm với dữ liệu SF và truy vấn trực tiếp. Điều này sẽ thực sự dễ dàng hơn và tránh một số rủi ro khác có thể xảy ra. Đôi khi quay lại là một cách tốt để tránh vấn đề “khi bạn có một cái búa thì tất cả những gì bạn nhìn thấy là móng tay” (đôi khi bạn có một công cụ và tất cả những gì bạn nhìn thấy là ốc vít).
Đến thời điểm này, giờ đây đáng giá để giải thích truy vấn này. Chúng ta có một truy vấn được gói trong một truy vấn khác. Truy vấn đầu tiên nói “thêm hai cột bổ sung bằng cách tách trang đích bằng dấu ‘?’ làm dấu phân cách.”
Truy vấn thứ hai nói “sau đó PHÂN TÁN đường dẫn bằng dấu gạch chéo như một dấu phân cách và lưu 4 page_paths đầu tiên như các cột riêng biệt.”
Sau khi có bảng mới của chúng tôi, chúng ta chỉ cần đặt một truy vấn rất cơ bản để có câu trả lời của chúng ta.
Duy nhất điều mới trong đây là GROUP BY. Chúng ta muốn tổng số các giao dịch, vì vậy chúng ta thêm một trường tính toán để SUM các giao dịch, nhưng chúng ta cũng phải nói cho nó biết SUM qua cái gì. Chúng tôi làm điều đó bằng GROUP BY. Chúng tôi nói GROUP tất cả các trường có cùng page_path_1 và điều đó cung cấp cho trường tính toán của chúng tôi một cái gì đó để SUM qua.
Chúc mừng, bạn đã đến cuối cùng! Bạn không chỉ nhận được một số câu trả lời hữu ích, mà nếu bạn đặt cùng những câu hỏi này trên một dự án khác hoặc một tập dữ liệu được cập nhật, bạn chỉ cần sao chép và dán cùng một truy vấn và nhận kết quả mới. Chúng tôi đã làm công việc khó một lần và sẽ không bao giờ phải làm lại nó.
Một điều cuối cùng về xuất kết quả
Muốn xuất kết quả này? Bạn có thể tải xuống kết quả từ các truy vấn nhỏ dưới dạng CSV, nhưng đối với các kết quả lớn hơn, bạn cần lưu chúng dưới dạng một bảng khác và sau đó xuất bảng đó ra GCS và tải xuống từ đó.
Ở đây, tôi đang tải kết quả xuống một thùng thử nghiệm_distilled trong GCS.
Tìm hiểu thêm về chúng tôi tại: TRANHUNG Digital
Điều này hơi không thuận tiện, nhưng cho đến nay tôi đã phải làm vậy. Nếu bạn có cách nào để làm cho nó mượt mà hơn, hãy cho tôi biết!
Nguồn tham khảo: https://moz.com/blog/how-to-bigquery-large-scale-seo
