AnalyticDB for MySQL in combination with Quick BI provides a fine-grained advertisement management solution for Shenzhen Irobotbox Tech Co., Ltd. (hereinafter referred to as Irobotbox). This solution can improve advertisement creation efficiency and performance, and allocate advertising campaign budgets in an appropriate manner based on real-time business intelligence (BI) reports to maximize enterprise benefits.

Challenges

Irobotbox is a technical service company centered around high-tech innovation. As the number of users increases, Irobotbox has encountered the following challenges:

  • Requirements for high concurrency and timeliness

    When users search for products by keyword on a third-party e-commerce platform, highly concurrent real-time query requests are generated.

  • Storage requirements for large amounts of historical data

    Advertising data of each user is stored in an individual database. Single tables of some users store more than 0.1 billion rows of data per year. If historical data is retained for one year, 300 users will generate 18 TB of data.

  • Requirements for real-time quick query capability of databases

    Users can query the conversion rate and return on investment (ROI) of advertisements from dashboards in real time. During business peak hours, the number of complex queries per second (QPS) will be more than 100.

  • Requirements for query response time of databases
  • In actual business scenarios, the query response time of databases must be less than 20 seconds. However, after testing, the response time of some GROUP BY statements within third-party databases that run online transaction processing (OLTP) systems exceeds 40 seconds. This is insufficient to meet business requirements.

Solution

To tackle the preceding challenges, Alibaba Cloud has developed the following solution for Irobotbox through a combination of AnalyticDB for MySQL and Quick BI. For more information, see What is AnalyticDB for MySQL?

In the preceding solution, an AnalyticDB for MySQL cluster is used to store business data. This cluster is of the Cluster Edition and contains 16 C8 node groups and can support tens of thousands of transactions per seconds (QPS) as well as return responses for complex queries of terabytes of data within seconds. This cluster also allows you to scale nodes and storage space.

Benefits

The fine-grained advertisement management solution provides the following benefits for Irobotbox:

  • Manages keyword advertisements of multiple e-commerce sites and stores in a unified manner. This way, users do not need to switch between sites.
  • Identifies search terms with high conversion rates through real-time statistical analysis of large amounts of data. This helps users quickly launch advertising campaigns and improves advertisement creation efficiency and performance.
  • Allows users to adjust prices based on different time periods or business changes through statistical analysis of large amounts of data. This helps users avoid missing out on prime times for price adjustment.
  • Allows users to query each gross profit from advertisements in real time.
  • Quickly analyzes keywords with high and low efficiency and allocates advertising campaign budgets in an appropriate manner to maximize benefits.