Data Lake Formation:DLF data exploration - Taobao user behavior analysis

Preview and check data

In the left-side navigation pane, click Data Exploration. In the SQL editor, enter the following statements to view the file data:

SET spark.sql.legacy.timeParserPolicy=LEGACY;
-- Preview data.
SELECT * FROM `demo_db`.`user` limit 10;
SELECT * FROM `demo_db`.`item` limit 10;

-- Number of users.
SELECT COUNT(DISTINCT user_id) FROM `demo_db`.`user`;

-- Number of items.
SELECT COUNT(DISTINCT item_id) FROM `demo_db`.`item`;

-- Number of behavior records.
SELECT COUNT(*) FROM `demo_db`.`user`;

The following figure shows the result.

Preprocess data

You can preprocess the raw data to improve data readability and analysis performance.

• Create the user_log table in the Parquet format and partition the table by date.

• Convert the values of behavior_type into strings that are easy to understand. Valid values: 1, 2, 3, and 4. 1 indicates clicks, 2 indicates add-to-favorites, 3 indicates add-to-cart, and 4 indicates payment.

• Divide the time column of the logs into the date and hour columns, and add a days of week column. This helps you analyze data at the date and hour levels.

• Filter out unnecessary fields. Then, store the required data in the new table named user_log.

User behavior will be analyzed based on the new table.

CREATE TABLE `demo_db`.`user_log` 
USING PARQUET
PARTITIONED BY (date)
AS SELECT 
    user_id, 
    item_id,
    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,
    date_format(time, 'H') AS hour,
    date_format(time, 'u') AS day_of_week
  FROM `dlf_demo`.`user`;
  
 -- View the result.  
SELECT * FROM `demo_db`.`user_log` limit 10; 

The following figure shows the result.

Analyze user behavior

1. Based on the funnel model, analyze the behavior of all users from clicks, add-to-carts, and add-to-favorites to purchase.

   -- The analysis takes 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. Analyze the user behavior on each day of a week.

   -- The analysis takes 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

   The following figure shows the result. (The analysis result is distorted because the dataset is pruned.)

3. Analyze the 10 most popular item categories in the dataset based on the item table.

   -- The analysis takes 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;

   The following figure shows the result.

4. (Optional) Download the analysis results.

   DLF allows you to download analysis results as a CSV file. Before the download, you must specify an OSS path for saving analysis results. The analysis results are saved to the specified path.

   1. In the upper-right corner of the Data Exploration page, click Path Settings. In the Select OSS Path dialog box, specify the Result Storage Path parameter. You can select an existing folder or create a folder.

      

   2. After the configuration is complete, run the SQL statements. On the Running History tab, you can click Download in the Actions column of the record to download the file. You can also access and download the file in the OSS console.

5. (Optional) Save the SQL statements.

   You can click Save to save the SQL statements that you execute in previous steps. Then, you can execute the saved SQL statements on the Saved Query tab if required.