Sybase IQ: Core Platform for Data Warehouse and Marketing
HMV opened its first store in Tokyo’s Shibuya district in 1990, and now, the company operates 66 stores around Japan which, together with an online service, boasts about 4 million+ registered members. Through its stores and online shopping services (HMV ONLINE and HMV MOBILE) the company supplies customers with over 2.8 million music, video and book titles.
HMV set out to improve the operating efficiency of its data warehouse, which houses an integrated analytics repository of all data from each individual store, the eCommerce sites, the business intelligence (BI) core of its eCommerce product recommendation system, and marketing database used for personalized email campaigns. In addition to this undertaking, HMV wanted to increase its per-customer revenue.
Achieving the Right Level of Functionality and Cost Reduction
HMV Japan launched a new product recommendation system about four years ago. This replaced its previous in-house developed system with the goal of expanding its eCommerce site operations. This previous system offered recommended products based on customer’s records of previous purchases. In order to make recommendations, however, a manager had to manually enter certain parameters such as, recommend products within so many days of a sales release, or which products the customer bought simultaneously.
“The purchase record-based system ran a batch process once a day. That took about 20 hours because the system was shared by our catalog database, and that resulted in frequent deadlocks,” recounts Hideki Ichikawa, information systems development manager in HMV Japan’s IT department. HMV Japan decided they needed a new solution. At first it considered building a system using vendors other than Sybase, but the company soon realized that the costs would be prohibitive and the functionality was less than satisfactory. So the problem became one of creating a design with the right functionality at lower cost, HMV Japan turned to Sybase.
Integrating Store Data and Providing Transaction Analysis
Another challenge HMV Japan faced was integrating the data that resided on two separate systems. “Our eCommerce sales were becoming a major operation, but because we were managing our eCommerce system and our store systems separately, trying to analyze data across both systems to develop effective sales strategies was a real challenge,” says Ichikawa.
With a vast array of entertainment assets and a broad customer base, HMV needed to ensure it could profile customer demands and supply key performance reports. Initially, HMV built a single database, integrating data from both source systems. This database was built using a standard relational database - not a dedicated analytics system. Data was extracted from the relational database, aggregated and loaded into a multi-dimensional database where OLAP tools could be applied for analysis.
“The multi-dimensional database was used mainly to do sales analysis, order analysis, and inventory analysis. In each case, multi-dimensional analysis could be performed while freely switching between 20 and 30 different kinds of analytic axes,” explains Ichikawa. However, the multi-dimensional database could only analyze aggregate values, while many end users really wanted to analyze individual transactions.
Optimizing Campaign Email – the Key to Increasing Purchase Rates
While this solution worked in some aspects, there was the challenge of optimizing HMV’s email campaign processes. The trigger for the start of an outbound email campaign was the ‘thank you’ email that was automatically sent to purchasers. Initially, this email was only sent to online customers, but when HMV started planning to send it to store customers as well, the idea was raised that it might be good to add a list of recommended products to the message. The online site already had the capability to recommend products, so when this feature was added to the thank you emails, customer purchase rates went up significantly.
Having confirmed the effectiveness of recommendation emails, HMV increased the number of campaigns offering recommended products with the aim of increasing revenue per customer. But, sending campaign information uniformly to all customers was not necessarily a good idea. Ichikawa explained that “Campaigns are very important to our business. But if we sent all our campaign emails to our customers, they would wind up receiving, together with our email magazine, a few dozen emails a day. Customers would start treating our emails as spam and the percentage of opened emails would drop.” To develop more effective campaigns and raise customer’s purchase rates, HMV needed to know its customer’s preferences, optimize the messages according to those preferences, and only send pertinent information to each particular customer.
Sybase IQ is Recommended
“HMV Japan sells tens of thousands of items, so if we try to manage one record for each customer, it won’t be long before the data on customers who make frequent purchases exceeds the maximum number of columns in an RDBMS. In other words, we needed a database with as few restrictions as possible to manage all our historical data,” says Ichikawa.
HMV Japan started searching for an effective solution to these growing challenges. First it dealt with the problem of the recommendation system. “To solve the problems of functionality and cost we first came up with KXEN, a product distributed in Japan by Brainpad Co., Ltd. Brainpad proposed a solution combining KXEN, RTmetrics, and Sybase IQ, which we accepted because it offered both the right functionality and low cost we needed,” recalls Ichikawa. Brainpad, which offers BI analytics outsourcing services, had partnered with Sybase to develop its analytics services, with Sybase IQ at the core of its systems.
To integrate the two separate systems, HMV copied its data warehouse data into its search engine database where it would set up a way to analyze itemized transaction data. Sybase IQ, which had been proven in the recommendation system, was also chosen to be the search engine database. However, Sybase IQ’s speed soon replaced the data warehouse system as well. “We were so surprised by Sybase IQ’s speed in the recommendation system that we decided to try using it for the data warehouse as well. When we saw how well it performed in extracting itemized transaction data – that settled everything,” says Ichikawa.
To optimize email campaigns, HMV built a marketing database that would store a data mart reflecting the company’s ideas and know-how in marketing theory. First it simulated campaigns and looked at automating personalized services. With regards to the requirements of the marketing database, Ichikawa explains, “We needed to achieve high-speed queries as part of aggregation processing, and decrease our overall maintenance burden.” In addition to approximately 60 million itemized transactions residing on the data warehouse, HMV needed to process data on over 2 million registered customers. Furthermore, the company also had to aggregate information that was not part of the data being analyzed by the data warehouse, such as sales related data and point-of-sale (POS) system raw data. HMV already knew from experience that an ordinary relational database management system would have real difficulty meeting these requirements, so HMV decided on Sybase IQ.
Unified Support for Recommendations, Data Integration, and Campaigns
Today, HMV Japan’s IT systems provide unified support for recommendation functions, store/website systems, data integration, and automated marketing based campaign emails.
The recommendation system analyzes customer click streams in near real-time, examines which products match the customer’s preferences, and then provides customized recommendations. This functionality increases the frequency of customer purchases and the revenue of each purchase.
Seconds Rather than Days
The Marketing Automation System, which uses Sybase IQ as its core database, combines the existing data warehouse and OLAP system with a BI server and other components. ETL tools are used to extract data from the inventory servers, online sales (order and shipping) databases, store POS databases and other master databases and load them into the data warehouse where daily batch processing occurs before reflecting the data to Sybase IQ. Managers who want to run campaign simulations use BI tools to access the OLAP cube generated by the data warehouse, and custom applications to access data on Sybase IQ.
“When analysts sent queries to the data warehouse built with a standard RDBMS, it took more than a day to get answers. But now, using Sybase IQ, we can complete queries on 60 million items of data in about 30 seconds,” stresses Ichikawa.
Now that analytic processing performance has been vastly improved, campaign email simulations can be run in advance, and optimized customer and product lists are sent for distribution to customers automatically. Now, HMV Japan customers receive both thank you emails after making a purchase, and personalized messages on their birthdays with links to information on products from their favorite music artists.
One of the biggest benefits of deploying Sybase IQ as part of the integration plan was vastly faster analysis processing.
“We were able to reduce the time it took to convert click stream data collected by the new system and load it into Sybase IQ by a huge margin. The processing that used to take over 20 hours is now done in about four hours. Overall performance has increased by about 40%,” Ichikawa explains.
The data warehouse contains about 60 million items of itemized transaction data, and parts of this are regularly loaded into Sybase IQ as necessary. Queries are handled by Sybase IQ in about 30 seconds. Reports and effectiveness evaluations can also be run in very short time. “Before the new system was deployed, different managers took care of the store system and website system, and analysts had to ask both sets of managers to extract data which then needed to be integrated before applying BI tools. This work used to take about two weeks, but once Sybase IQ was deployed, it only took 2 to 3 minutes,” says Ichikawa.
According to Ichikawa, “Before the new system we couldn’t run effectiveness evaluations even if we wanted to. With Sybase IQ, running effectiveness evaluations in a short time is easy, and we can easily spot which programs are hits. This allows us to reduce waste and offer our customers better discounts and point services.”
Purchases Have Grown up to 15 Times
The benefits of deploying the new marketing database were also immediately apparent. Ichikawa points out that, “The percentage of mobile device emails being opened rose to over 70% and even PC emails were being read over 50% of the time. Thanks the personalized email campaigns, customer purchasing rates grew between 3 and 15 times and per transaction revenue grew between 1.5 and 3 times. The real benefit of marketing automation has come from greater accuracy and optimization in our personalized emails, and in lower operation costs. What we used to do by painful manual adjustments is now done automatically, our entire campaign cycle runs much faster, and we can execute quick campaigns much, much more efficiently,” says Ichikawa.
HMV Japan has achieved significant results with a better understanding of customer preferences and optimizing email content accordingly. But HMV Japan is not satisfied— they are also looking to the future. As Ichikawa emphasizes, “We anticipate that the volume of our data and the number of campaigns will continue to expand significantly. But we also expect to continue leveraging Sybase IQ, which is indispensable now to our business, so that we can scale up from our present simplex configuration to a multiplex configuration in the future.”