Vẽ user-path (Sankey) với tool chùa và SQL

Chào mừng mọi người đến với blog của mình, hôm nay mình sẽ hướng dẫn các bạn cách tự vẽ 1 cái chart y hệt thế này từ raw data. Chắc hẳn Google Analytics (GA) là một công cụ đã rất quen thuộc với chúng ta, nhất là đối với các bạn làm dữ liệu. User flow là công cụ đặc biệt đem lại góc nhìn overview về business của công ty/ website của bạn với hình ảnh trực quan dễ hiểu như sau.

Điểm trừ của việc sử dụng Google Analytics đó chính là việc trực quan hóa dữ liệu phải phụ thuộc hoàn toàn vào điều google mong muốn, và mục đích mà GA (cũng như mạnh nhất tới nay) chính là để phân tích web cho nên GA có các giới hạn:

• Chủ thế là người dùng (user): GA chỉ hỗ trợ phân tích đường đi của người dùng khi vào web (web user path) -> Trong thực tế đôi lúc chúng ta sẽ quan tâm đến các chủ thể khác ví dụ đơn hàng/ giao dịch/ requests..v…..v..
• Step (mỗi bước đi) chỉ là địa điểm truy cập: người dùng đi từ url nào tới url nào. -> Ví dụ : Đôi lúc chúng ta không quan tâm các step chính giữa giữa 2 giao dịch và.

-> Giải pháp là tự vẽ con chart y vậy với raw data , không cần google nữa :)). Chúng ta có thể làm điều này với Python, R, Excel hay bất cứ công cụ nào. Tuy nhiên, SQL là lựa chọn nhanh nhất nếu chúng ta query dữ liệu trực tiếp từ server, và đặc biệt là khối lượng dữ liệu lớn. Để làm được điều này bạn cần biết 1 lệnh sau:

• Lag: Để phân biệt dòng nào là souce (điểm khởi đầu của flow) , dòng nào là target (điểm kết thúc)
• Over Partition over : Để phân biệt theo từng chủ thể (ví dụ nếu chúng ta coi user là chủ thể -> partition theo user để đánh thứ tự các hành động theo thời gian)
• Group by: Để nhóm lại các nhóm souce/target thành 1 group ( ví dụ ta có 2 source và 2 target là A B và C D thì A-> B là 1 group /A -> C là 1 group/ B-> C là 1 group….)

Dữ liệu mẫu, có dạng như sau: cột Dịch vụ – Userid – Thời gian xảy ra giao dịch (datetime). Chúng ta đặt table này là giao_dich

Đầu tiên để biết được thứ tự các giao dịch nhưng theo từng userid (ví dụ user A dùng dịch vụ Net đầu tiên (1) sau đó là đến dịch vụ Elec (2) mặc dù 2 dòng dữ liệu này cách nhau nhưng vẫn được đánh số liên tục). Cụ thể trông nó như sau:

Ở dịch vụ Others, mặc dù cùng 1 table nhưng vẫn được đánh số lại từ đầu 1 2 3 khi đã được partition. Đây sẽ là target để chúng ta build dòng SQL. dDòng rn chính là số thứ tự của giao dịch

Vậy câu lệnh SQL cần phải phân biệt được userid để đánh số thứ tự theo thứ tự từ cũ tới mới (date) . Câu lệnh SQL có cấu trúc sau:

select date,appid,userid, 
       row_number() over ( partition by userid order by date) as rn 
       from giao_dich

Trong đó đoạn : row_number() over ( partition by userid order by date) as rn có nghĩa là SQL sẽ chia table ra thành nhiều bảng nhỏ theo từng userid, trong mỗi bảng nhỏ này lại được sắp xếp theo date và được đánh số thứ tự từ trên xuống dưới.

Như vậy ta đã hoàn thành bước 1 , tạo thứ tự cho dòng dữ liệu.

Bước 2: Gán tên cho target (và source)

Để phân biệt được target trong user-path có 2 điều cần có trong tên của target: thứ tự (xảy ra lần thứ mấy), tên sự kiện(payment/ access/…. ở đây đang là payment)

-> Gắn thứ tự và tên hành động bằng cột rn và appid

select *,concat('time_',cast(rn as varchar),'_',appid) as target  
from(
select date,appid,userid, 
       row_number() over ( partition by userid order by date) as rn 
       from giao_dich
)

Bước 3: Tạo source

Source tức là hành động trước đó 1 bước so với hành động hiện tại -> đối với mỗi userid chúng ta lại cho cột event lùi lên 1 dòng. Chúng ta sẽ dùng lag

select *,lag(target, 1) over(partition by userid order by date) as source 
from(
select *,concat('time_',cast(rn as varchar),'_',appid) as target  
from(
select date,appid,userid, 
       row_number() over ( partition by userid order by date) as rn 
       from giao_dich
))

Kết quả của bước 2,3 sẽ cho chúng ta dữ liệu tương tự thế này. Bây giờ chúng ta chỉ cần group lại theo target và source là đã có thể tạo ra chart user-path rồi

Bước 4: Group by và xem kết quả thôi

Chúng ta sẽ count distinct số lượng người dùng theo từng cặp source target, như vậy cần group by 2 lần, câu SQL sẽ như sau.

select source,target,count(distinct userid) as value
from(
select *,lag(target, 1) over(partition by userid order by date) as source 
from(
select *,concat('time_',cast(rn as varchar),'_',appid) as target  
from(
select date,appid,userid, 
       row_number() over ( partition by userid order by date) as rn 
       from giao_dich
))
-- Chú ý rn dưới 5 chỉ lấy 5 step cho dễ nhìn
where rn <= 5
) group by source,target

Kết quả của bảng dữ liệu cuối trông như thế này, đã đủ source (bước thứ mấy từ dịch vụ gì đến dịch vụ gì, bao nhiêu người dùng).

Ví dụ chúng ta có thể thấy có 315 người dùng Water tiếp tục tiến tới dùng TV ở lần thứ 3 ngay tại dòng đầu tiên. Tương tự vậy cho các dịch vụ khác

Sau khi đã có được kết quả cụ thể connection giữa từng source, target và value chúng ta có thể lên trang: Sankey Diagram Generator v1.2 (acquireprocure.com) để tạo sankey miễn phí. Nhấn vào load your own data và chọn CSV, copy toàn bộ CSV của mình vào và voila chúng ta đã có kết quả cuối cùng! Như đã note vì mình dừng lại ở rn <= 5 cho nên chart cũng chỉ dừng lại ở step 5. Vì data thật có chút nhạy cảm nên thông cảm cho mình sử dụng sensor nhé :D.

Ngoài ra còn có nhiều loại sankey từ các thư viện R, Python. Chỉ cần bạn có thể lấy được kết quả theo format trên, chúng ta đã có thể vẽ được user-path mà customized hơn Google Analytics rất nhiều.

Trong bài viết cách viết SQL của mình không tối ưu, tuy nhiên rất rõ ràng từng step để làm nổi bật ý tưởng hơn. Nếu bạn nào có cách viết tối ưu, ít nesting hơn vui lòng góp ý dưới comment để mọi người có thể sử dụng và tham khảo thêm nhé.

Cảm ơn các bạn.