Data warehouse, real-time personalisation and InsightOS

The Challenge

Being a leading camping specialist, ACSI operates multiple websites and platforms which generates lots of data. To help grow its online business, improve speed and cost-efficiency, ACSI needed a solution that could bring all the data into one place, real time.

ACSI’s data used to be spread across various systems and they were running their operations using the reporting facilities of each production application on its own. To make selections, their IT department would write queries from the CRM system to the marketing automation system manually. To retrieve a customer, it would take minutes. As data volumes grew and queries became more complex, it became difficult to scale.

Having no enterprise data warehouse or central reporting, they had information from all kinds of resources to rely on. It was hard to correlate responses from marketing campaigns and booking information with customer behaviour on the website. To format reports, ACSI used excel with data from different production applications. The results did not provide fast and useful insight to track and trace web behaviour accurately and to judge the effectiveness of their CPC advises.

InsightOS

ACSI team wanted to have quick access to (cost-per-click) CPC information and maximise their CPC revenue. To enable that we chose to implement InsightOS, our own web application built on Google BigQuery and Google Apps Engine, with bespoke viewers. The tool allows users to focus on specific data by just clicking a button to see, for example, CPC actuals for campings in a particular region or in a specific time frame.

InsightOS offers two viewers: arrival and booking viewer, which are specially designed for leisure operators who need insight into their profitability and portfolio. The arrival viewer shows when customers are arriving at the campsites, which, next to marketing targeting, helps ACSI to optimise their campsite staff planning. The booking viewer shows when people booked their trip and allows ACSI to predict the number of calls they receive in the call centre, for example. Reports that took hours to run now take seconds to get the same answer. 

“The most distinguishing feature of InsightOS is its speed,” explains Richard Verhoeff, CEO of Crystalloids. “Google's BigQuery service makes it possible to examine millions of records in seconds. Other business intelligence solutions could take eight minutes to come back with an answer. That’s way too long. Speed is an essential part of our application, and BigQuery Service gives us that."

Data warehouse

Since data is a big decision maker in ACSI’s business, they needed them all in one place, they needed a data warehouse. Information from their reservation system, Google Analytics and CPC were already integrated into InsightOS. But that was just a start. 

To build a well-designed data warehouse, we used various Google Cloud products where the customer information from all the touch-points could be combined real-time. These sources include web (what visitors searched for and clicked on), mailing system (how they engaged with the newsletter), mobile apps, surveys data and last but not least, the CRM system.

Each click or query that visitor makes on ACSI websites is represented in a JSON file that is being ingested using Cloud Functions and Pub/Sub. Via Cloud Dataflow the data is streamed to BigQuery and written to Cloud Storage. From this data, we take the key information about the customer (e.g. cookie from the website), hashed email information, normal email from a newsletter, booking ID (if they made a reservation) and they all go to a user’s table. 

When all the data is brought together in the data warehouse, it can be analysed and visualised using BI tools that are connected to BigQuery. From the marketing table to the websites, there is an endpoint that creates the basis for website personalisation, campaign selections and business analytics.