This topic describes how to use Realtime Compute to filter users who meet the conditions for issuing marketing coupons in a coupon-based marketing policy.

Background

A merchant uses a marketing policy of "refund coupons" at Double 11. After a user's consumption amount reaches a specified value, the merchant issues a refund coupon with a certain amount to the user to promote more consumption. Realtime Compute monitors the consumption amount of users in real time and filters users who meet the conditions for issuing refund coupons.

Solution

  • SQL structure

  • Source tables
    • Create a source table to store orders generated by the e-commerce system.
      Note To focus on the core logic, we simplify the order data format to retain only the attributes related to the use case.
      CREATE TABLE dwd_tb_trd_pay_ri(
              biz_order_id    varchar, // The ID of the order. 
              auction_id      varchar, // The ID of the product.
              auction_title   varchar, // The title of the product. 
              buyer_id        varchar, // The ID of the buyer.
              buyer_nick      varchar, // The nickname of the buyer.
              pay_time        varchar, // The payment time of the order.
              gmt_create      varchar, // The time when the order was created.
              gmt_modified    varchar, // The time when the order was modified.
              biz_type        VARCHAR, // The type of the transaction.
              pay_status      VARCHAR, // The payment status. 
              `attributes`    VARCHAR, // The flag of the order.
              from_group      VARCHAR, // The source of the order.
              div_idx_actual_total_fee   DOUBLE  // The deal amount.        
      ) WITH (
          type='datahub',
          endPoint='http://dh-cn-hangzhou.aliyun-inc.com',
          project='yourProjectName', // Your project name.
          topic='yourTopicName', // Your topic name.
          roleArn='yourRoleArn', // Your role ARN.
          batchReadSize='500'
      );
    • Create a source table of returns.
      Note To focus on the core logic, we simplify the order data format to retain only the attributes related to the use case.
      CREATE TABLE dwd_tb_trd_rfd_ri(
              biz_order_id    varchar, // The ID of the order. 
              auction_id      varchar, // The ID of the product.
              auction_title   varchar, // The title of the product. 
              buyer_id        varchar, // The ID of the buyer.
              buyer_nick      varchar, // The nickname of the buyer.
              pay_time        varchar, // The payment time of the order.
              gmt_create      varchar, // The time when the order was created.
              gmt_modified    varchar, // The time when the order was modified.
              biz_type        VARCHAR, // The type of the transaction.
              pay_status      VARCHAR, // The payment status. 
              `attributes`    VARCHAR, // The flag of the order.
              from_group      VARCHAR, // The source of the order. 
              refund_fee      DOUBLE  // The refund amount.     
      ) WITH (    
          type='datahub',
          endPoint='http://dh-cn-hangzhou.aliyun-inc.com',
          project='yourProjectName', // Your project name.
          topic='yourTopicName', // Your topic name.
          roleArn='yourRoleArn', // Your role ARN.
          batchReadSize='500'
      );
  • Result tables

    Execute the following statements to create an RDS result table:

    CREATE TABLE tddl_output(
        id           BIGINT, // The ID, which auto-increments.
        gmt_create   TIMESTAMP, // The time when the order was created.
        gmt_modified TIMESTAMP, // The time when the order was modified.
        buyer_id     BIGINT, // The ID of the buyer.
        cumulate_amount BIGINT, // The order amount.
        effect_time BIGINT, // The payment time of the order.
        primary key(buyer_id,effect_time)
    ) WITH ( 
            type= 'rds', 
            url = 'yourDatabaseURL', // Your database URL. 
            tableName = 'yourTableName', // Your table name. 
            userName = 'yourUserName', // Your username. 
            password = 'yourDatabasePassword' // Your password. 
        ); 
  • Business logic

    Perform the UNION ALL operation to join the order table and table of returns to obtain information about all purchased items and collect the actual consumption amount and details of users.

    CREATE VIEW  dwd_tb_trd_and_rfd_pay_ri
    AS
    SELECT
    *
    FROM(
        (SELECT 
            `a`.biz_order_id biz_order_id, 
            `a`.auction_id auction_id, 
            `a`.auction_title auction_title, 
            `a`.buyer_id buyer_id, 
            `a`.buyer_nick buyer_nick, 
            `a`.pay_time pay_time, 
            `a`.gmt_create gmt_create, 
            `a`.gmt_modified gmt_modified, 
            `a`.biz_type biz_type, 
            `a`.pay_status pay_status, 
            `a`.`attributes` `attributes`, 
            `a`.from_group,        
            `a`.div_idx_actual_total_fee div_idx_actual_total_fee
        FROM 
            dwd_tb_trd_pay_ri `a` 
        )
        UNION ALL
        (
        SELECT 
            `b`.biz_order_id biz_order_id, 
            `b`.auction_id auction_id, 
            `b`.auction_title auction_title, 
            `b`.buyer_id buyer_id, 
            `b`.buyer_nick buyer_nick, 
            `b`.pay_time pay_time, 
            `b`.gmt_create gmt_create, 
            `b`.gmt_modified gmt_modified, 
            `b`.biz_type biz_type, 
            `b`.pay_status pay_status, 
            `b`.`attributes` `attributes`, 
            `b`.from_group,     
            `b`.refund_fee div_idx_actual_total_fee // The refund amount, which is a negative value.
        FROM 
            dwd_tb_trd_rfd_ri `b` 
        )
    );
    					
  • Deduplication

    The order table and table of returns may contain a large amount of duplicate data, such as product IDs and product names. Use the MIN function to obtain parameter values only in the first generated record of each order and filter out other data. Then group data by order ID and payment status to obtain required product IDs and product names.

    
    CREATE VIEW filter_hxhb_dwd_tb_trd_and_rfd_pay_ri_distinct AS 
    SELECT 
       biz_order_id biz_order_id, 
       pay_status pay_status,
       MIN(auction_id) auction_id, 
       MIN(auction_title) auction_title, 
       MIN(buyer_id) buyer_id, 
       MIN(buyer_nick)  buyer_nick, 
       MIN(pay_time)  pay_time, 
       MIN(gmt_create)  gmt_create, 
       MIN(gmt_modified)  gmt_modified, 
       MIN(biz_type)  biz_type, 
       MIN(attributes)  attributes, 
       MIN(div_idx_actual_total_fee)  div_idx_actual_total_fee
    FROM 
        dwd_tb_trd_and_rfd_pay_ri
    GROUP BY biz_order_id ,pay_status;                
  • Data aggregation
    CREATE VIEW ads_tb_trd_and_rfd_pay_ri AS
    SELECT 
       MIN(gmt_create) gmt_create, // The time when the order was created.
       MAX(gmt_modified) gmt_modified, // The time when the order was last modified.
       CAST (buyer_id AS BIGINT) buyer_id, // The ID of the buyer.
       CAST (date_format(pay_time , 'yyyy-MM-dd HH:mm:ss' , 'yyyyMMdd') AS BIGINT) as effect_time, // The payment time.
       SUM(CAST(div_idx_actual_total_fee/100 AS BIGINT)) cumulate_amount // The total amount.
    FROM 
       filter_hxhb_dwd_tb_trd_and_rfd_pay_ri_distinct 
    GROUP BY 
       buyer_id,date_format(pay_time , 'yyyy-MM-dd HH:mm:ss' , 'yyyyMMdd');
    Q: Why are the MAX and MIN functions used?
     MIN(gmt_create) gmt_create, // The time when the order was created.
     MAX(gmt_modified) gmt_modified, // The time when the order was last modified.

    A: MIN(gmt_create) obtains the first time an order was recorded. MAX(gmt_modified) obtains the last time the order was modified. You can use MAX and MIN to obtain the corresponding time values based on business logic of orders.

    Note If the time field is not of the BIGINT type, use the corresponding built-in functions to convert the data type. For more information, see built-in functions.
  • Data insertion into RDS

    Insert the statistical data into the RDS result table. Then use appropriate push software to issue coupons with the correct refund amount to users who meet the specified conditions.

    INSERT INTO tddl_output
    SELECT
        gmt_create,
        gmt_modified,
        buyer_id,
        cumulate_amount,
        effect_time
    from ads_tb_trd_and_rfd_pay_ri
    where cumulate_amount>0;

    Q: Why is the "where cumulate_amount>0" clause used?

    cumulate_amount>0

    A: This clause filters out the amount of the returned products involved in the preceding UNION ALL operation.

Summary

Q: How do I get the data I need from a large number of records about orders and return orders?

A: In actual transaction situations, a large number of order records and return records are generated. You can perform the UNION ALL operation to join two or more tables as one large table. Then, deduplicate and aggregate the records based on your specific business logic. Finally, write the actual transaction amounts of all orders of each user into the result table to prepare for issuing coupons.