Jaguar and Land Rover are world-renowned brands within the automotive
industry and have been operating under one group since 2013 in South Africa
as JLRSA. Both brands share a rich heritage and are pioneers of innovation,
striving to use digital solutions to address various business challenges
Automate the collection of company data and present it in a centralised reporting tool
Build data pipelines for each social platform and company division
Store and secure the data in a single database
Present the data with a fast, filterable dashboard
A fast, reliable database and intuitive and informative reporting tool
FROM MANUAL TO AUTOMATIC
The system that was in place was entirely manual with reports being gathered
from their various departments and combined into excel spreadsheets.
Generating monthly reports (often with well over a thousand rows of data)
was time-consuming and prone to human error and data corruption. Any insight
surfaced from this data was potentially outdated, irrelevant or inaccurate.
Without the assurance that the numbers in these reports were accurate, JLRSA
and its stakeholders identified the need to improve the quality of data and
reliability of their methods.
The need to understand and present data from every corner of the business in
a meaningful and informative manner lead JLRSA to seek a more centralised,
automated solution. The infrastructure had to overcome the challenge of
connecting, combining, activating and displaying insights from multiple data
sources and across multiple company verticals. DotModus partnered with JLRSA
to assist in building a pipeline that is able to handle the large amounts of
data, is easy to monitor and totally automated. The resulting datasets would
be visualised in real-time and presented as filterable dashboards in Data
A Google Cloud Composer instance, which runs a managed version of the
fantastic orchestration platform, Apache Airflow, would manage much of the
ETL process. Cloud Build and Cloud Registry. Custom data pipelines were put
together to gather data from various APIs and onsite SFTP servers. Each
pipeline in the ingestion process runs periodically through batch processes,
ingesting data in daily, weekly or monthly intervals depending on the
reporting needs of each department. The gathered data is stored in storage
buckets within Cloud Storage. Data would then be converted to JSON and used
downstream in BigQuery to generate more manageable datasets. In the instance
of a corrupt datafile, the archive data is used to backfill BigQuery with
all valid data.
All steps in the ETL process are closely monitored using Stackdriver. Any
failed import or discrepancy in the data triggers an alert that immediately
sends a notification to our internal messaging system and kicks our data
team into action. Stackdriver’s error/status aggregations and thorough
logging of events eases root-cause analysis and reduces mean time to
resolution (MTTR). Any problems in the pipeline are quickly picked up and
fixed before they are able to progress and cause greater problems down the
FILTERING AT YOUR FINGERTIPS
Having their data safely stored in BigQuery was only half the solution. To
truly harness insights, their data would need to be visualised. Data Studio
is Google’s dashboarding and reporting tool that connects seamlessly to
BigQuery and various other databases. It is totally free to use - no per
seat or per instance licenses, nothing! Any member of the JLRSA domain (with
the required level of permissions) can use and explore the dashboards with
impunity - easing onboarding and encouraging usage of the solution. Data
Studio was perfect for putting together the fast, interactive dashboards
JLRSA were needing.
The resulting solution provides JLRSA with 9 dashboards:
Social - user activity across YouTube and Facebook
Vehicle Services - service history metrics and dealership performance ranked by client feedback ratings.
Vehicle Sales - sales metrics for each vehicle model and variant (per dealership and against company targets)
Product Mix - sales metrics and comparisons between various models, colours and added features (and the production/delivery times linked to each)
Customers Leads - tracking of leads generated through customer events and other activities
Financial Services - tracking of vehicle financing
Lifestyle Products - tracking of sales of Jaguar and Land Rover lifestyle products (clothing, bags, accessories etc.)
Data and Billing - monitoring dashboard to show the cost and performance of each step in the data pipeline.
Executive Insights/overview - a summary dashboard for quick, to the point updates on each department’s KPIs.
Each dashboard has aggregated metrics, real-time filtering and instant
search, making exploring and comparing the data gathered from that division
quick and simple. In under 5 minutes, a user can pull up their own report
for the Gauteng dealership that sold the most Glacier White Jaguar F-PACEs
with an ebony trim in July 2018.
TIME SAVED, MONEY GAINED
Moving from manual, Excel based reporting to this new solution has allowed
JLRSA to transform their methods of analysis. Reports can be pulled and
presented to decision makers at a moment’s notice, allowing management to
change the course of their decisions in-time to have a meaningful business
impact. Decisions supported by data that is accurate, up-to-date and more
relevant than ever before.