Data Lake Formation:DLF data exploration quick start: Taobao user behavior analysis

Preview and check data

Run the following statements to inspect the raw data and count records. SET spark.sql.legacy.timeParserPolicy=LEGACY ensures the time column parses correctly with Spark SQL's legacy date format parser.

SET spark.sql.legacy.timeParserPolicy=LEGACY;

-- Preview raw rows
SELECT * FROM `demo_db`.`user` LIMIT 10;
SELECT * FROM `demo_db`.`item` LIMIT 10;

-- Count distinct users
SELECT COUNT(DISTINCT user_id) FROM `demo_db`.`user`;

-- Count distinct items
SELECT COUNT(DISTINCT item_id) FROM `demo_db`.`item`;

-- Count total behavior records
SELECT COUNT(*) FROM `demo_db`.`user`;

The following figure shows the result.

Preprocess data

The raw user table stores behavior types as numeric codes (1–4) and combines date and time in a single column. The following statement transforms this raw data into a more analysis-friendly format:

• USING PARQUET stores the output in columnar Parquet format, which compresses data and speeds up analytical queries compared to CSV.

• PARTITIONED BY (date) lets Spark skip entire date partitions when a query filters on a specific day.

• The CASE WHEN block converts numeric behavior codes to readable labels (click, collect, cart, pay).

• date_format splits the time column into separate date, hour, and day_of_week columns, enabling date- and hour-level analysis in later queries.

CREATE TABLE `demo_db`.`user_log`
USING PARQUET
PARTITIONED BY (date)
AS SELECT
    user_id,
    item_id,
    -- Convert numeric behavior codes to readable labels
    CASE
      WHEN behavior_type = 1 THEN 'click'
      WHEN behavior_type = 2 THEN 'collect'
      WHEN behavior_type = 3 THEN 'cart'
      WHEN behavior_type = 4 THEN 'pay'
    END AS behavior,
    item_category,
    time,
    date_format(time, 'yyyy-MM-dd') AS date,      -- Used as partition key
    date_format(time, 'H')          AS hour,
    date_format(time, 'u')          AS day_of_week -- 1=Monday ... 7=Sunday
  FROM `dlf_demo`.`user`;

-- Verify the result
SELECT * FROM `demo_db`.`user_log` LIMIT 10;

The following figure shows the result.

Analyze user behavior

All three analyses below query the user_log table created in the previous step.

1. Funnel analysis: Count how many times users performed each behavior type across the entire dataset. This reveals where users drop off in the click > collect > cart > pay funnel.

   -- Takes approximately 13 seconds
   SELECT
     behavior, COUNT(*) AS total
   FROM `demo_db`.`user_log`
   GROUP BY behavior
   ORDER BY total DESC;

   The following figure shows the result.

   

2. Daily engagement: Break down unique visitors (UV) and behavior counts by date and day of week. This shows whether activity peaks on weekdays or weekends.

   Note

   Because the dataset is pruned for this tutorial, the day-of-week pattern may not reflect real Taobao traffic. The query structure is correct — apply it to the full dataset for meaningful trends.

   -- Takes approximately 14 seconds
   SELECT
     date, day_of_week,
     COUNT(DISTINCT(user_id)) AS uv,
     SUM(CASE WHEN behavior = 'click'   THEN 1 ELSE 0 END) AS click,
     SUM(CASE WHEN behavior = 'cart'    THEN 1 ELSE 0 END) AS cart,
     SUM(CASE WHEN behavior = 'collect' THEN 1 ELSE 0 END) AS collect,
     SUM(CASE WHEN behavior = 'pay'     THEN 1 ELSE 0 END) AS pay
   FROM `demo_db`.`user_log`
   GROUP BY date, day_of_week
   ORDER BY date;

3. Top item categories: Join the item table with user_log to find the 10 most purchased item categories. Filtering on behavior = 'pay' isolates actual purchases rather than browsing activity.

   -- Takes approximately 1 minute and 10 seconds
   SELECT item.item_category, COUNT(*) AS times
   FROM `demo_db`.`item` item
     JOIN `demo_db`.`user_log` log
     ON item.item_id = log.item_id
   WHERE log.behavior = 'pay'
   GROUP BY item.item_category
   ORDER BY times DESC
   LIMIT 10;

4. (Optional) Download results: Save query results as a CSV file.

   1. In the upper-right corner of the Data Exploration page, click Path Settings. In the Select OSS Path dialog box, set the Result Storage Path to an existing folder or create a new one.

   2. After running a query, go to the Running History tab and click Download in the Actions column to download the result file. The file is also accessible from the OSS console.

5. (Optional) Save SQL statements: Click Save to store the SQL statements. Saved queries are available on the Saved Query tab for future runs.