Saving time with automated CRM reporting

Hiscox had lots of different types of data to visualise from a variety of sources but accurately reporting the exact performance metrics was proving difficult. Cookie blockers are very common in Germany making Google Analytics (GA) tracking very difficult. In addition, as they get sales from users offline, these do not show up in GA. Both of these, plus the general discrepancy between the back end data and GA, made using GA for accurate reporting of Hiscox performance impossible. A better solution was required.

Objective

To tackle this problem, we aimed to create a fully automated solution that would grab the data from the CRM that Hiscox would export to a SFTP server, transform it and then upload to BigQuery. Once all this was done we could use Google Sheets to visualise this data using the script function that can connect to BigQuery. This set-up would allow Hiscox’ reports and emails to be automatically updated and shared daily so that they could gain full visibility of the performance of their sales. This level of insight was something previously very difficult to get from Hiscox’s architecture, previously relying on manual excel manipulations of raw data exports from their back end, taking up a lot of time every week.

Approach

We utilised our in house automation platform, built using Airflow, to do the data extraction, transforming, and loading (ETL). We utilised BigQuery to carry out some data transformation and data storage, and Google Sheets to visualise this data alongside information pulled from Google Analytics via its reporting API.

Airflow:

Airflow is an automated, programmatic data pipelining framework that is very good at running “tasks” at scheduled intervals using Python. Airflow is also the underlying technology behind Google Cloud Composer. The airflow tasks were broken down in to the following steps:

  1. Go to Hiscox’s SFTP server collect the reports for that day.

  2. Do some basic cleaning and sanitising.

  3. Upload the table into a BigQuery staging table.

  4. Run a query in BigQuery to further clean the data and make the relevant tables for use in Google Sheets.

BigQuery

As stated above in the airflow process we utilised BigQuery’s cheap but powerful processing power to do the data cleaning, sorting and storage for us. AS the data volumes increase, BigQuery auto scales it’s resources to ensure the workload is handled consistently. As Hiscox is a GA360 customer they get $500 of BigQuery services for free each month.

Google Sheets

Google sheets is the main front end tool where the data is visualised. It has a very useful in built scripting functionality that is coded in Google’s App Script. This allows us to use the inbuilt connector to BigQuery and Google Analytics to collect the data and populate reports. It also enables the creation of scripts which can interface with GMail, allowing us to email dynamically populated performance reports to Hiscox stakeholders on a daily basis.

Tech Stack

Complexity

There were a few complexities with this project outlined in the points below:

  • Two different types of reports with different data.
    The solution had to deal with the different reports types.

  • Allow the possibility to “Backfill” data so that we can cover data from previous dates and reprocess missing data or data that has mistakes in it.

  • Build queries that can filter, transform and classify the raw data to produce an output that adheres to the counting and classification logic used internally by the Hiscox team.
    This logic is not represented in the data itself, and as a result this step of the project took up the largest share of the time.

  • Configure reports and functionality that satisfies the needs of key stakeholders
    KPIs (quotes and sales) against absolute and pro-rata targets, compared with previous performance, broken down by product, sales channel and specific time-spans.
    Giving users the ability to pull data and populate reports for custom date ranges.
    Providing a rolling historical log of past performance.
    Visualising details of offline interactions via their call centre.
    Reporting on the role of digital in sales performance with data imported from media platforms and Google Analytics.

Results

Previously the Hiscox team had to devote a significant amount of time to manually reporting on their business. Now the client has daily, automated visibility of how they are performing and can now make quick changes based on the data. The Google Sheets report and daily emails have become their primary method of reporting on their business.

Quote from the client

With the new Dashboard we have a fully automated reporting process providing a comprehensive set of tailored business metrics on a daily basis. It allows us to analyze and adapt much faster in a data-driven way so we can focus on improving our business rather than consolidating inputs from different sources.

Claudia Schneider - Manager Operations Direct Germany - Hiscox Ltd.