Big Data Analytics Solution Developed for SEM

Case Study: Data and Analytics

SITUATION & BUSINESS CHALLENGE

In 2010, a multi-billion dollar online travel company was starting to think about how to leverage Big Data for search engine marketing (SEM). At the time, Big Data was a hot concept, but its application was largely experimental with few best practices in place.

The company had previously hired a boutique consulting firm specializing in search engine data to create a system for managing its Pay-Per-Click advertising practice. This system captured up to one year’s worth of search engine data from Google, Bing, and Yahoo for the company’s SEM business group, and although it was successful in its basic intent, the data was often incomplete or missing, the tool was expensive to use, and management felt there was much more it could do. All in all, the company was appropriating nearly $3 million a year for the system, including paying for travel and accommodation for consultants flown in from the boutique firm’s out-of-state headquarters.

One approach for harnessing more of the system’s potential was to build a robust reporting system and data warehouse to store and better analyze the captured data to improve returns on search engine advertising (Pay-Per-Click). No competitors at the time were analyzing data in this fashion, so taking on such a challenge was venturing into new territory. With a limited understanding of how Big Data could even be leveraged to optimize SEM and no clear roadmap in place, specific projects and goals for the system’s future were difficult to define.

Unfortunately, the boutique consulting firm did not have the breadth of expertise to turn the SEM system into a more robust solution. The company needed a new consulting firm, one local to their offices for better value, to provide a more complete suite of skills and a fresh perspective. The company found the data and analytics expertise it needed in AIM Consulting.

SOLUTION

AIM provided a consulting team with the right combination of expertise and soft skills to communicate with stakeholders, establish business requirements, and create a project plan to evolve the existing system into an intelligent and robust Big Data SEM solution—the first of its kind in the online travel industry.

Our solution was a complete data warehousing, analytics and reporting system that stores more than 2TB of search engine data. The solution incorporates data pipelines, data visualization and analytics tools that thoroughly analyze marketing KPIs like impressions, clicks, and transactions from the major search engines used in 20 countries.

The solution was delivered in three phases:

Phase 1: Discovery

During this phase, the AIM consultants analyzed the current system and aligned a wide variety of goals from management into an initial framework—later to be developed into a more comprehensive roadmap—and built the team.

Phase 2: Building an evolved solution

As the original system only had one year’s worth of search engine data, with some missing or incomplete, the first goal was to capture and store three years of data reliably. This would enable historical analysis and set the stage for predictive analytics. AIM was able to do this more efficiently than the boutique consulting company, which used a tool capable of only loading one row at a time, in part by building a data tool that could load 100,000 rows at a time.

Next, AIM collaborated with operations specialists on the business side to marry transaction data with search engine data to come up with measurements that analyzed keyword efficiency. This led to discoveries of stronger relationships within the data that would yield more powerful results. As part of this phase, stronger practices for data governance and storage were put into place, and deeper analytics and reporting measures were implemented.

Phase 3: Optimization

Once the data warehouse was stabilized, the team focused on getting stronger data feeds. They migrated from the original solution to Kenshoo, a more robust and less costly solution that helps with bidding strategies with keywords, similar to Google AdWords.

Next, the team was able to help the company derive a more detailed roadmap for collecting and analyzing up to five years of search engine data to generate more robust KPIs for more targeted advertising on the various search engines.

AIM constructed the solution to align with the company’s existing Microsoft-based technology stack. The resulting Big Data solution included the following components:

• SQL Server as the data warehouse
• SQL Server Integration Services (SSIS — data migration services)
• SQL Server Analysis Services (SSAS — data mining tool)
• SQL Server Reporting Services (SSRS — report generating services)
• Qlikview (BI — dashboard and data visualization)
• Custom API code written in C# and .NET

The solution was built using agile processes with two-week sprints, working internally with project managers and the business on an iterative approach and delivering small wins at first, then applying more focus toward the long-term roadmap.

RESULTS

The SEM system was a first-of-its-kind data warehousing, analytics, and reporting solution for the travel industry and provided a competitive advantage for the company by allowing it to advertise to consumers more effectively on search engines.

The effectiveness of the solution led to approximately $7 million in annual savings for the company:

• Hardware — savings of $15,000 per month by bringing the data warehouse in-house.
• The former consulting firm costs of about $2.5 million per year.
• Keyword efficiency improvements that saved about $3.5 million per year in advertising.

These cost savings were diverted for other uses, including expanding the audience to eight different languages and additional countries based on transaction numbers. Increased intelligence and ROI on advertising furthered the company’s competitive advantage in the marketplace.

AIM Consulting delivered strategy and end-to-end implementation during a time when Big Data was just emerging in the travel space. The solution conceptualized by AIM is now six years old and still the main SEM solution at the company.