Just the Headlines

Short on time? Here are the key facts.

  • A national automotive sales and service company needed help optimizing their modern data and analytics platform to create user friendly dashboards for their sales team
  • Aptitive modeled and integrated their data into Amazon Redshift before building robust Sales Dashboards in Looker
  • The new Sales Dashboards provided near real-time sales reporting in Looker and enabled the sales team to drill into the metrics they needed and track their performance against their budget

Overview

When a national automotive sales and service company decided to modernize and transform their analytics, they chose to use Amazon Redshift and Looker for their modern data and analytics platform. They knew this combination would enable the scalability, performance, and insights they needed as their company grew.

Beyond this powerful technology combination, the CIO knew that for long term success, they would need a team to help them model their source data and design custom dashboards based on their unique goals and KPIs. Additionally, he wanted to ensure that his internal team received training and documentation for ongoing success. They turned to Aptitive, an experienced Data, Analytics, and Data Science consulting partner to:

  • Model their source data to support advanced analytics
  • Determine a performant, near real-time data update processes
  • Consolidate and optimize multiple reports
  • Modernize their dashboards and fully leverage Looker
  • Provide technical training and documentation

Industry

Automotive Sales and Service

Featured Technologies

Amazon Redshift
Looker

The Goal

The end goal of this project was to create a user friendly sales dashboard in Looker powered by near real-time data from sources throughout the organization. To make this happen, the Aptitive team needed to:

  • Create a data model that would yield performant and scalable analytics in Looker
  • Build out the data model in Amazon Redshift
  • Develop a dashboard in Looker that highlights KPIs for the company’s sales funnel and tracks how they are performing against their budget
  • Provide training and support to the reporting and analytics team to ensure long term success and increase user adoption for both Redshift and Looker

The Solution

Step 1: Data Modeling and Restructuring
Aptitive began by creating a dimensional model to restructure their data into a form that supports advanced analytics. They then built that model, and implemented complicated business logic using materialized views in AWS Redshift.

Step 2: Data Ingestion
Once the data was restructured, it was brought into Looker using LookML with the goal of reimagining the client’s sales funnel reports. To combine sales data with budget data, Aptitive created a Persistent Derived Table (PDT) that would merge data at different levels of granularities.

Step 3: Custom Sales Dashboard in Looker
Aptitive created a customized Looker dashboard based on business user requirements that incorporated liquid parameters, customized drill in fields, and built out measures with logic for KPIs. Additionally, Aptitive implemented Row Level Security (RLS) in Looker so end users could only view data associated with their franchise.

In addition to the steps above, training was provided throughout to ensure the internal team would be able to easily manage the tech stack moving forward and create additional dashboards for other functional areas throughout the company.

The Outcome

The end product provided a single dashboard that communicates the entire story of the sales funnel to end users. It enables business users to take actionable steps by clearly visualizing their performance against budgeted goals and previous years.

The underlying data model enabled the company to easily filter to the correct information in Looker.

By implementing liquid parameters and adding drill in fields, Aptitive was able to communicate the big picture, while also empowering users to dive into the data further when a more granular level of detail was needed.

In addition to the near real-time sales reporting in Looker, the Aptitive provided training and documentation around best practices to support future development and enable autonomy.

Below are sample sales dashboards created in Looker to measure and key sales performance indicators.  

Sales v. Budget Summary Looker Sales Dashboard

Looker Sales Dashboard Tier Variance Summary

Byrider