Case Study: alpharooms.com Improves Booking Performance Through Sophisticated Business Intelligence EffortsAugust 16, 2013 by Victoria Garment
It’s no secret that booking travel plans on the Internet is more popular than ever before—according to the most recent Adobe Digital Index report, online bookings in the U.S. are expected to hit an all-time high of $54 billion in Q3 2013; a 10.7 percent increase from 2012.
This continued increase in online reservations means online travel booking companies are amassing huge amounts of data, and now face the challenge of establishing a way to analyze and make sense of this information.
Lee Eckersley knows a thing or two about how daunting this challenge can be. As the head of business analysis at alpharooms.com, a UK-based provider of online travel booking services, Eckersley is responsible for overseeing all data accumulated through the company’s website—no easy feat given alpharooms’ one million-plus annual customers and the 250,000 hotels they partner with around the world.
The sheer quantity and breadth of this information requires a comprehensive business intelligence (BI) system in place to store, analyze and report on this data in order to make more informed business decisions. When Eckersley was first hired in 2012, however, he found that the BI system alpharooms was using at the time was inadequate at meeting their needs.
“My first project was to review the current reporting system and look into ways of improving it,” he says. “When I started to look at what was available, I had to agree that there were better ways of doing things.”
A search for a more comprehensive BI system was launched, and in April 2012, alpharooms implemented SiSense Prism software. As a result, the company has greatly improved their data analysis and reporting, helping them keep pace with the competition.
A Mountain of Data to Analyze
To operate efficiently and remain competitive, alpharooms must analyze a wide range of data on a daily basis, including booking and financial performance, online customer behavior and conversions and call center performance for sales and customer service. The company also tracks Google pay-per-click advertising performance, email marketing success rates and site traffic from affiliates and other sources.
The majority of alpharooms’ data is accumulated through their booking funnel, which is the process customers move through from the moment they land on the company’s homepage to when they book a flight and/or hotel stay. This process is illustrated below:
This funnel yields two main types of data: customer activity on the alpharooms.com site, which shows what a person searched for on the site and how far they made it through the funnel; and transactional data, which is generated when a customer successfully completes a booking.
Alpharooms uses two servers to manage this accumulated information. All data is initially stored on a cloud-based SQL Server, which is a data management server. The company then uses automated SQL scripts, or sets of saved commands, to perform a process known as log shipping.
This process copies log files that store transactional data (i.e. web transactions such as flight and hotel reservations) and recreates them within a structured data warehouse on a secondary reporting server. This data warehouse is essentially an organized, easily searchable data repository designed for the company’s reporting needs.
This process ensures that, in addition to real-time updates, the company can track retrospective updates—for example, if a customer booked a vacation a year in advance and then canceled their reservation eight months later.
Along with transactional data from alpharooms.com, the data warehouse stores booking and financial profit and loss data, which is captured through an older version of Microsoft Dynamics NAV, and email marketing data gathered via Emarsys, a marketing automation solution. The data from these sources is transferred into the data warehouse through a process known as Extract, Transfer, Load (ETL): information is extracted from the main source, transformed into a common format that fits with other data, then loaded into the warehouse for analysis.
A Previous System That Lacked Detailed Reporting
Prior to implementing Prism, alpharooms analyzed data in several ways. Web behavior was evaluated using a combination of Google Analytics and DC Storm, a Web-based measurement and reporting platform. Booking and financial data was analyzed using Excel and SSAS cubes, which are multidimensional structures created within a SQL server that provide different views of data through dimensions, or groups of data attributes. Examples of such dimensions include Hotel Location, Booking Type and Product Supplier.
This approach lacked the level of visualization needed to adequately analyze the accumulated data. Neither Excel nor the SSAS cubes provided a way to build customized dashboards, drill down into deeper levels of analysis or easily pull data out to share with employees or company stakeholders, and IT assistance and/or external consultants were frequently required.
To analyze data, staff spent hours and hours creating pivot tables, which automatically sort, count or give the average of data in Excel spreadsheets. According to Eckersley, without these tables, “there was no data visualization.”
Additionally, alpharoom’s multiple data sources were stored separately from one another, which hindered comprehensive analysis and reporting, as data from these sources couldn’t be compared against one another.
When the time came to search for new BI software, Eckersley knew he needed to address the old system’s lack of data accessibility so staff could obtain pertinent information and produce reports whenever they needed it, without requiring IT or external assistance. He also wanted a system that would be quick to implement and easy to use for those employees with little IT skills or SQL knowledge, such as those in the sales department.
“The biggest challenge for us was to get the data out of the system and in front of the people who need to see it,” he explains. “I wanted to empower users and not have so much reliance on individuals having to do scheduled reports for management when those reports should be generated automatically.”
An Advanced BI Solution That Stood Apart From the Rest
As someone who likes to roll up his sleeves and dive right into a new system, Eckersley downloaded and installed a number of modern BI tools on the market to test their performance. It didn’t take him long to discover SiSense Prism was much easier to use than the others. “It was the simplest to use BI tool I had ever come across,” he says. “SiSense won with flying colors on ease of use and visualization.”
Eckersley also cites SiSense’s customer service as instrumental in his decision. After downloading the free trial, he received a courtesy call, cost estimate, free hour long Web demo and online live training to help familiarize him with the system.“By the time I had agreed to buy I had already developed a number of test dashboards,” he says.
After alpharooms implemented Prism as an on-premise system in April 2012, Eckersley had the first reports and dashboards up and running within a month, and has been adding additional data sources on an ongoing basis since.
ElastiCubes Provide Greater Visibility of Data Sources
To view the performance of alpharooms’ booking funnel, Eckersley has set up the system so transactional data from flight or hotel reservations made online is automatically transferred into Prism overnight.
“Everything is now completely automated, so come 8 a.m., you can see exactly what happened the day before, how current performance is overall, and so forth,” he explains.
The process through which alpharooms' data is transferred into Prism
Once this data is in the system, it’s analyzed using Prism ElastiCubes, which are compressed data repositories (similar to SSAS cubes) created by importing data files from different sources, including the company’s SQL server databases, Google Analytics, Excel and CSV files. Once these ElastiCubes are created, users can design queries, dashboards and reports to make sense of the data.
Available data sources within Prism’s ElastiCube Manager
New data sources, tables or fields can be added to these cubes instantly to create “data mashups,” and ad hoc queries can then be run against these newly expanded data sets without having to reconfigure the data structure.
This is a considerable benefit, as this functionality isn’t possible with SSAS cubes—incorporating additional or changed data requires the entire data model to be reprocessed, which is both difficult and time consuming.
Prism’s ElastiCube Manager
Eckersley can also manipulate dimensions and filters to gain greater insight into customer booking behavior. For example, the number of times customers visit the site before booking varies considerably by destination, so the company uses filters to closely compare conversion rates by location.
“Conversion rates can vary anywhere from as low as 0.5 percent to six, seven percent depending on where you’re looking at,” Eckersley says. “Conversions for someone booking a one or two night stay within the country tend to be higher than someone who is going to spend a couple of thousand pounds on a holiday outside of the country. That’s why we use Prism to analyze data split by destination.”
Interactive Dashboards Improve Data Visualization
As someone familiar with the limitations of many other BI systems, Eckersley says he’s impressed with Prism’s ElastiCube technology, which lets alpharooms perform queries on tens of millions of rows of data in seconds, using an ordinary Windows 7 desktop computer with 8GB of RAM. “There are no delays even when end users drill down into queries that return millions of rows,” he reports.
Once queries have been run, alpharooms employees use Prism BI Studio, a user interface that allows users to easily illustrate data in dashboards that make it easy to visualize the results. Alpharooms has a set of pre-designed dashboards used to process common types of data, and users can quickly create new ones from scratch. Reports on this data can then be created with a click of a button.
Both dashboards and reports can also be sent to one or more employees, who can view them using Prism’s Interactive Viewer on their web browser, or export them to Excel or PDF files.
A sample interactive visualization dashboard within Prism
“I can be given information and have created a new cube, created a dashboard, and then emailed people a version of the dashboard all within an hour,” Eckersley says. “There’s no large IT overhead, it’s just a very easy, simple way to get data from the SQL server into an online dashboard.”
Eckersley has created a series of dashboards for the most common data scenarios they look at, such as yearly comparisons of bookings. “We then use the drilling and filtering features—destinations, resorts, hotels, suppliers, etc. to get into the nitty gritty of the data,” he says.
Alpharooms also looks closely at customer web behavior by creating dashboards that allow them to view how people are traveling through the booking funnel to see if information on certain pages needs to be revised in order to improve conversion rates.
Insight Into Booking Rates Improves Competitiveness
While alpharooms has wholesaler partnerships with hotel, flight and car rental providers, the company also employs direct contracting staff, who work to forge direct contracts with hotels at wholesale rates.
“One of the goals of implementing Prism was to provide a platform to analyze booking performance specifically for these contractors,” Eckersley says.“We need to be able to see how many bookings take place through our direct channel, how many take place through the wholesalers, and through which wholesalers. If one wholesaler is beating out another, then we have to go and try and get better deals.”
Alpharooms’ contractor dashboard in Prism
Alpharooms also uses Prism to improve their financial relationships with the hotels they partner with directly. “We can identify hotels with lower direct booking percentages than others and use that to negotiate for better rates,” Eckersley explains. “We can then change our pricing with our own direct contracts so it’s more competitive with our wholesaler contracts.”
Improved Marketing Efforts Increase Customer Bookings
On the marketing side, Prism helps alpharooms analyze their performance by marketing source. This allows them to monitor the effectiveness of their Google pay-per-click advertising efforts to see if they need to increase spending.
The system also highlights the success of alpharooms’ email marketing campaigns by linking data from their email system with their user session tracking database. All emails contain a tag that captures the device the user views the email on, when the email was opened and whether or not the user clicked through to the website.
When analyzed together, this information tells alpharooms if the emails they’re sending are helpful to customers. “We can gain insight into what email marketing campaigns work best for opening emails, clicking through to the site and ultimately, bookings generated from it,” says Eckersley.
Alpharooms’ Email Opens dashboard in Prism
Prism also allows alpharooms to market to existing customers in order to increase retention and encourage repeat bookings, such as birthday emails that offer customers discounts on their next purchase. They also sends targeted emails to customers that have searched for a certain destination, and send a series of retention emails to customers a year after their last booking.
A Solution with Unexpected Capabilities and Benefits
The range of Prism’s capabilities and the insight it provides has led alpharooms to use the software for functions well beyond what was initially anticipated. “We didn’t expect to use Prism for so many parts of the business as we are now, it’s just been getting built up more and more as the business is using it for different things,” Eckersley says.
“When we started to look into the performance of particular destinations in regard to flight and hotel searches, we saw that in some, bookings converted well on a hotel basis, but not on both a flight and hotel basis. This tells us something is wrong, and that we have to go adjust that.”
Prism’s effectiveness when it comes to handling alpharooms’ reporting needs, together with other contributing factors, led the company to eliminate unnecessary employees. This included staff members who used to spend hours creating highly labor intensive forms of reporting.
While Eckersley says the exact cost benefits from Prism are hard to quantify, he says one of the biggest benefits of the system is how it has empowered staff and given them data and insight into key areas of operations. “At the end of the day, it easily gives visualization of data to people who don’t have a lot of tech experience,” he explains.
Alpharooms is just one company to experience the considerable benefits of today’s business intelligence tools. BI software systems like Prism are making it easier than ever for organizations to make sense of accumulated information. With the ability to analyze vast quantities of multidimensional data in seconds, these solutions give average business users IT-level capabilities, enabling in-depth data visualization and reporting to generate smarter business decisions and forge new pathways for growth.
Thumbnail image created by puuikibeach.