Practical Taobao User‑Behavior Analysis with AARRR and RFM Models

1. Problem definition and analytical models – The goal is to explain and improve five business questions: (1) identify common e‑commerce metrics and stage‑wise churn rates, (2) use hypothesis testing to find churn causes, (3) study user behavior at different time scales, (4) discover product preferences and marketing strategies, and (5) segment users by value.

2. Data source – The dataset comes from Alibaba Cloud Tianchi (tianchi.aliyun.com/data) and contains 9 days of Taobao mobile app logs (2017‑11‑25 00:00 to 2017‑12‑04 00:00). A representative sample of 2 million rows covering 19 544 users is used.

3. Data preparation

Import CSV with import pandas as pd and preview the first five rows.

Create a MySQL table user with columns id, item, behavior, category, times and a primary key on (id,item,times).

Convert the Unix timestamp times to a datetime column, then split it into date (YYYY‑MM‑DD) and time (hour).

Delete records outside the 9‑day window.

4. AARRR funnel analysis

Daily new users (DNU) : rank each user’s first login per id, then count users with rank = 1 per date. The peak of 13 927 new users occurs on 11‑25, suggesting a promotion.

User retention : retention on day N = (users who log in again on day N) / (new users on the first day). Retention stays above 75 % for the 8 days after 11‑25, with >98 % on 12‑02/12‑03, indicating stable engagement.

Behavior by day : a view user_behavior aggregates daily counts of pv, fav, cart, and buy. Browsing volume is flat (180‑200 k) from 11‑25 to 12‑01, then spikes on 12‑02/12‑03, likely due to a Double‑12 pre‑heat campaign.

Behavior by hour : a view time_behavior shows low activity 1‑6 am, gradual rise 6‑10 am, stable 10‑18 am, a surge 18‑21 pm, and a peak 21‑23 pm. The 22‑23 pm window is identified as a “add‑to‑cart” peak, suggesting targeted incentives.

Churn analysis : total users = 9 969; users who only view once = 1, giving a bounce rate of 0.01 % – the app retains almost all visitors.

Conversion funnel : overall buy/pv conversion is 2.25 %. Detailed path counts show higher conversion for pv‑fav‑buy, pv‑cart‑buy, and pv‑fav‑cart‑buy compared with direct pv‑buy.

5. Hypothesis testing

Hypothesis 1 – Missing fav or cart steps increase search cost and lower purchase intent. Verification uses a 购买流程 view that sums each behavior per user‑item. Conversion rates for paths that include fav or cart are significantly higher, confirming the hypothesis.

Hypothesis 2 – Poor match between hot‑search and hot‑sale items reduces conversion. The top‑50 hot‑sale items (by buy) and top‑50 hot‑search items (by pv, fav, cart) overlap in only 5 items (10 % match), supporting the hypothesis.

6. RFM segmentation

Recency (R) : compute days from the last purchase to 2017‑12‑04, bucket into five scores (1–5).

Frequency (F) : count purchases per user, bucket into five scores (1–5).

Money (M) is omitted because monetary values are unavailable.

Combine R and F scores in view RR_FF and compare each score to the overall averages (R ≈ 3.55, F ≈ 1.32) to classify users into four groups: “Important value customers”, “Important development customers”, “Important retention customers”, and “Important churn‑risk customers”.

7. Conversion rate analysis

Daily conversion (pay/visit) stays between 2 % and 2.6 % over the 9 days, peaking at 2.53 % on 11‑27.

Hourly conversion peaks at 10‑11 am (≈ 3 %) and 15‑17 pm, while 3‑4 am is the lowest.

Category‑level conversion shows categories 2735466 and 2885642 achieving >3 % conversion, whereas category 154040 is only 0.31 %.

8. Conclusions and recommendations

Peak user activity occurs at 21‑23 pm; concentrate marketing (live streams, flash sales) in this window.

Retention above 75 % suggests stable user base; reinforce with tiered rewards and first‑order coupons.

Paths that include fav or cart dramatically improve conversion; encourage these actions with coupons or discounts.

Low overlap between hot‑search and hot‑sale items indicates a recommendation mismatch; improve algorithmic relevance and increase discounts on hot‑search items.

RFM results show most users fall into “important development” and “important churn‑risk” segments; tailor incentives (frequency‑boosting coupons, proactive outreach) accordingly.