Để tiếp nối series Phân tích dữ liệu thương mại điện tử, nếu bạn là chủ doanh nghiệp - Phần 2, bài này mình sẽ nói chi tiết hơn một chút về khách hàng. Cụ thể là cách phân loại khách hàng theo xu hướng tiêu dùng để đưa ra các quyết định chăm sóc cũng như chương trình marketing hiệu quả đối với các tập khách hàng khác nhau.

Dữ liệu

  1. Dữ liệu mình tổng hợp được share trong phần Archive trên trang web này. Dữ liệu của series này mình share trong bài [SQLInUse] Ecommerce I Dataset
  2. Hoặc bạn có thể truy cập thẳng Redash để thực hành SQL và tải dữ liệu. Chọn [SQLInUse] - Ecommerce I - PostgreSQL data source nhé.

Điểm chính

Link to dashboard

  1. Về mặt biz
    1. Phân loại và định nghĩa tập khách hàng theo hành vi mua hàng, tuỳ theo mô hình kinh doanh, cách phân loại có thể sẽ khác nhau.
    2. Thảo luận những tham số ảnh hưởng đến kết quả phân tích và chọn tham số phù hợp để có thể đưa ra quyết định phù hợp cho hoạt động của doanh nghiệp.
  2. Về mặt kỹ thuật, vận dụng kiến thức SQL khác nhau để trích xuất dữ liệu
    1. Các kỹ thuật với Window Functions được sử dụng
      1. Aggregate Functions
      2. Ranking Functions
      3. Window Framing
    2. Filter khi sử dụng Aggregate Functions với Group By

Let's GO

Ở bài trước, mình đã chia khách hàng thành hai loại cơ bản, đó là khách hàng tiềm năng và khách hàng thực tế. Theo đó, khách hàng thực tế là loại khách hàng đã có trải nghiệm mua, sử dụng và đem lại doanh thu cho doanh nghiệp, như được vẽ màu đỏ trong hình 1.

Hình 1. Customers Vs Leads

Trong bài này, mình sẽ chia nhỏ phần khách hàng thực tế để tiếp tục phân tích cấu trúc của nó.

1. Định nghĩa

Về cơ bản khách hàng thực tế có thể được chia tiếp làm 2 loại chính như sau

  • khách hàng thường xuyên, active và
  • khách hàng đã rời đi, churned (không còn sử dụng dịch vụ).

Mỗi mô hình kinh doanh sẽ có định nghĩa khác nhau. Ví dụ,

  • đối với mô mình kinh doanh bán gói dịch vụ theo chu kỳ (subscription), active sẽ là khoảng thời gian khách hàng có trả phí sử dụng theo quy định của gói dịch vụ và churned nếu khách hàng không còn trả phí
  • đối với mô hình kinh doanh bán gói theo số lần sử dụng dịch vụ (v.d. gói 500k cho 5 lần thăm khám sức khoẻ), thời gian active có thể sẽ kéo đài rất lâu cho đến khi gói dịch vụ kết thúc và khách hàng trở thành churned

Dữ liệu mình đang làm là về thương mại điện tử, nơi mà khách hàng có thể đến mua hàng bất cứ lúc nào có nhu cầu, nên cần có cách tiếp cận khác.

Vì không có yếu tố ràng buộc để khách hàng quay lại và tiếp tục sử dụng dịch vụ, mình giới thiệu một biến số mới để theo dõi tình trạng active của khách, gọi là active_period. Active period là khoảng thời gian chờ để khách hàng quay lại từ lần mua hàng trước. Ví dụ, 7 hoặc 14 ngày, tuỳ vào sản phẩm và dịch vụ doanh nghiệp cung cấp. Cùng nhìn vào lịch sử giao dịch của khách hàng A với cố định active_period = 7 như sau

customer   | created_at
-----------------------
A          | 2024-07-01
A          | 2024-07-07
A          | 2024-07-16

Theo đó

  • A có trạng thái active từ Jul 1 đến Jul 14, và từ Jul 16 đến Jul 23, vì A có lịch sử mua hàng ở đầu mỗi chu kỳ trên. Hay nói cách khác, 7 ngày sau mỗi giao dịch, A có trạng thái active.
  • Từ ngày thứ 8 trở đi (sau bất kỳ giao dịch nào), A có trạng thái churned (khách hàng đã rời đi) nếu chưa phát sinh giao dịch tiếp theo.
💡
Vậy nếu chọn active_period = 14, trạng thái của A sẽ thay đổi như thế nào? Bạn thử comment nhé.

Tuy nhiên, nếu để ý kỹ, trạng thái active của A ở cả 3 ngày có giao dịch xảy ra sẽ không hoàn toàn giống nhau, có những điểm khác nhau mấu chốt trong tâm lý tiêu dùng. Cụ thể,

  • ngày 2024-07-01, A là khách hàng lần đầu sử dụng, khi chưa có bất kỳ trải nghiệm về sản phẩm, có thể biết đến qua marketing hoặc bạn bè giới thiệu
  • ngày 2024-07-07, A là khách hàng quay lại, sau khi đã dùng ở lần đầu và có thể cảm thấy thích và muốn tiếp tục, hoặc có nhu cầu thử lại thêm lần nữa
  • ngày 2024-07-16, A là khách hàng quay lại sau khi đã rời đi (churned từ ngày 2024-07-15 theo cách chọn active period)

Cùng là trạng thái active nhưng tâm lý tiêu dùng khách nhau, nên có thể chia nhóm active thành 3 loại tương ứng như sau

  • new là khách hàng sử dụng sản phẩm, dịch vụ lần đầu tiên
  • retained là khách hàng quay trở lại và tiếp tục sử dụng, trong khoảng thời gian được giới hạn bởi active_period từ lần giao dịch ngay trước đó
  • reactive là khách hàng quay lại sử dụng sản phẩm ngoài khoảng thời gian giới hạn bởi active_period từ lần giao dịch ngay trước đó. Hay nói cách khác, quay lại sau khi đã churned.

Khách hàng A có thể được phân loại qua thời gian chi tiết như sau, active_period = 7

customer   | created_at | status
--------------------------------
A          | 2024-07-01 | new 
A          | 2024-07-02 | new 
....
A          | 2024-07-06 | new 
A          | 2024-07-07 | retained 
A          | 2024-07-08 | retained 
....
A          | 2024-07-14 | retained
A          | 2024-07-15 | churned 
A          | 2024-07-16 | reactive 
A          | 2024-07-17 | reactive
....
A          | 2024-07-23 | reactive
A          | 2024-07-24 | churned

Vậy sau khi mua hàng lần đầu, trạng thái của khách hàng có thể thay đổi giữa new, retained, reactive và churned. Trong đó, new chỉ dành cho khoảng thời gian ngay sau lần mua hàng đầu tiên. Mình có công thức của active customers ở bất cứ thời điểm nào như sau

\[ active_{0} = new_{0} + retained_{0} + reactive_{0} \]

và churned customers, là khách hàng active ở chu kỳ trước nhưng không retained ở chu kỳ hiện tại, ví dụ A ở ngày Jul. 15 theo chu kỳ 7 ngày

\[ churned_{0} = active_{-1} - retained_{0} \]

và tỷ lệ khách hàng quay lại ở chu kỳ hiện tại, retention_rate

\[ rrate_{0} = \frac{retained_{0}}{active_{-1}} \]

hiểu rõ các công thức trên sẽ giúp tối ưu câu lệnh SQL dễ dàng hơn.

2. Dữ liệu và insights

Mục đích của việc phân loại và theo dõi tình trạng khách hàng là để doanh nghiệp đưa ra quyết định cho các hoạt động marketing, chăm sóc khách hàng, tối ưu nhất cho từng nhóm, cũng như để tăng tỷ lệ khách hàng quay lại nhiều nhất có thể. Doanh thu 90% đến từ nhóm khách hàng quay lại này.

Dữ liệu dựa trên các khoảng active_period (7 đến 60 ngày) khác nhau cho thấy đối với loại hình sản phẩm doanh nghiệp đang kinh doanh

  • cần ít nhất 14-21 ngày để hơn 50% khách hàng sử dụng sản phẩm và bắt đầu quay lại, như bạn thấy ở hình 2a (active_period = 7), chỉ 8-10% là retained và hơn 80% là reactive, cho thấy khách hàng có quay lại nhưng cần nhiều thời gian hơn.
  • ở khoảng active_period = 28 hình 2b, cho thấy rõ xu hướng quay lại của khách hàng nhất khi tỷ lệ đạt 70-80%
  • tuy nhiên, sau khoảng thời gian này, khi active_period > 28 , gần như không có sự thay đổi (tăng) đáng kể tỷ lệ khách hàng quay lại. Hay nói cách khác, nếu khách hàng không có giao dịch trong vòng 28 ngày từ lần giao dịch trước đó, khả năng khách hàng không muốn tiếp cục gần như chắc chắn.

Hình 2. Active Customer Structure

Vậy chiến thuật có thể là gì để giữ chân khách hàng và tối ưu hoá hoạt động của doanh nghiệp, tập trung nhiều vào những điểm mang về doanh thu cao và ít tốn chi phí

  • cần quan tâm đối với nhóm khách hàng mới, đây là tập có khả năng churned cao nhất
  • cần có cách để hạn chế rủi ro khách hàng rời đi (prevent churn risk) đối với nhóm retained. Cụ thể, doanh nghiệp có thể target sale trực tiếp hoặc promotion nếu lần mua hàng trước đó sắp vượt qua ngưỡng tối ưu active_period mà doanh nghiệp định sẵn, bởi vì nhu cầu về sản phẩm chắc chắn sẽ cao ở cuối chu kỳ
  • có thể xem xét reactivate tập đã churned để tăng doanh số, tuy nghiên ở một ngưỡng nhất định, việc làm này có thể sẽ không đem lại hiệu quả so với chi phí có thể cần bỏ ra

Có thể sử dụng tỷ lệ khách hàng quay lại cho KPI hoạt động của công ty, qua đó các chiến lược hoạt động nên tập trung để nâng cao tỷ lệ này. Hoặc tập trung tăng trưởng số lượng khách hàng trong từng nhóm cụ thể, ví dụ new hoặc retained.

Hình 3. Retention Rate & Customer Counts

SQL

Câu truy vấn duy nhất của bài này trên Redash ở đây. 

with order_rank as (
  select
    tmp1.*,
    tmp2.first_date,
    row_number() over (
      partition by tmp1.customer_id
      order by
        date asc
    ) as order_rank
  from
    (
      select
        customer_id,
        date_trunc('day', created_at) as date
      from
        orders
      group by
        1,
        2
    ) tmp1
    join (
      select
        customer_id,
        date_trunc('day', min(created_at)) as first_date
      from
        orders
      group by
        1
    ) tmp2 on tmp1.customer_id = tmp2.customer_id
),
statuses as (
  select
    or1.first_date,
    date_trunc('{{period}}', or1.date) as date,
    or1.customer_id,
    case
      when date_trunc('day', or1.date) = or1.first_date then 'new'
      when date_trunc('day', or1.date) != or1.first_date
      and or2.date is not null then 'retained'
      when or2.date is null
      and date_trunc('day', or1.date) != or1.first_date then 'reactive'
    end as status
  from
    order_rank or1
    left join order_rank or2 on or1.customer_id = or2.customer_id
    and or1.order_rank = or2.order_rank + 1
    and or1.date - '{{active_period}}days' :: interval <= or2.date
),
active as (
  select
    *,
    new + retained + reactive as active
  from
    (
      select
        date,
        count(distinct customer_id) filter (
          where
            status = 'new'
        ) as new,
        count(distinct customer_id) filter (
          where
            status = 'retained'
        ) as retained,
        count(distinct customer_id) filter (
          where
            status = 'reactive'
        ) as reactive
      from
        statuses
      group by
        1
    )
)
select
  *,
  rolling - active - retained as churned,
  case
    when (rolling - active) = 0 then 0
    when (retained > rolling - active)
    and (rolling - active != 0) then 1
    else retained * 1.0 / (rolling - active)
  end as retention_rate
from
  (
    select
      *,
      SUM(active) OVER (
        ORDER BY
          date ROWS BETWEEN 1 PRECEDING
          AND CURRENT ROW
      ) as rolling
    from
      active
  )

Trong đó mình sử dụng nhiều kỹ thuật khác nhau như

  • joins, left/right, inner và self
  • sử dụng ranking như là một window function (sql line 5)
  • sử dụng aggregate như là một window function (sql line 88)
  • và cũng sử dụng framing options kết hợp window function cùng với công thức để tìm ra churned (sql line 90)

Kết

Bức tranh chung về tình trạng khách hàng sẽ trở nên cụ thể hơn khi được nhìn theo chiều dài hoạt động của doanh nghiệp và chia nhóm phù hợp. Việc này sẽ giúp doanh nghiệp dễ nhìn ra điểm chưa tối ưu trong quá trình hoạt động và đưa ra cách cải thiện.

Cảm ơn bạn đã xem bài, bạn có góp ý hoặc thắc mắc thì để lại câu hỏi dưới bình luận nhé.