Case Studies

Making Data Insightful

Industry: Technology

Solution: Data Warehouse and Data Analytics

Client: Virtual Engagement and Mobile Application Company

The Situation

An organization focusing on virtual engagement provides cultural institutions with enhanced experiences for their visitors by turning mobile devices into personal concierges and expert tour guides and providing options for augmented reality and virtual reality experiences at zoos and parks. In the process, the mobile app collects invaluable usage data from visitors such as time spent in an exhibit, videos watched, paths taken through buildings, and other key indicators that can then be analyzed to make strategic decisions around marketing, value of exhibits, and areas of improvement. The company is setting a new standard for virtual and mobile experiences at cultural institutions and needs to ensure that the data it collects can be monetized and leveraged by its customers.

The Problem

The application collected large amounts of data from users but lacked an effective way to make that data insightful for clients. The organization was looking for help identifying analytical insights from its aggregate user data (such as exhibit engagement patterns, high traffic areas, visitor demographics, etc.) that would be powerful enough to support strategic decision-making and could be sold back to these cultural institutions.

The organization was also interested in understanding what the Business Intelligence (BI) landscape could offer, and what tools were available to help continue building out its analytical framework.  Specifically, they wanted to know more about:

• Ability to collect, analyze and visualize millions of rows of data
• End-to-end automated processing of source data to support dashboards
• Interactive dashboards that allow users to easily click into varying levels of information
• Intuitive visualizations that are easy to use
• Rich dashboards that can hold as much information as possible, while still being informative
• Visualization of traffic patterns

The Solution

Kenway provided a mix of services to build a solution that uniquely met the needs of this organization, including Vendor Assessment, Data Management, BI, Architecture and Design, and Custom Development. Ultimately, Kenway worked to retrieve the data collected through the app and load that information into a newly-built Redshift backend database.  They also wrote APIs to pull all data into staging tables, SQL scripts to pull that data out of the staging tables and into a normalized data model, and a Qlik Sense reporting tool to visualize the data.

 To determine the best BI tool on the market, Kenway performed a vendor assessment comparing different BI tools on the Gartner Magic Quadrant; Tableau, Qlik Sense, Power BI, and Amazon QuickSight (not on the quadrant) were all considered. Based on an assessment of the organization, Kenway knew the tool would need to provide an end-to-end process of source to dashboards, an ability to handle larger volumes of data/scale to support big data, and easy-to-use, intuitive visualizations.

As an aid to this assessment, Kenway’s BI expert also created a vendor assessment to weigh and analyze features offered by each of the options being considered. The paper highlighted key areas of importance such as stress test, strength and weakness deep dive, total cost, and logistics and implementation. After all comparisons were finished, the recommendation came down to Qlik Sense and Power BI, with both having similar features to meet the client’s needs.  Once cost and integration factors were considered, Qlik Sense was identified as the best tool to deliver on the client’s defined requirements.

To bring the application usage data into insightful visuals, Kenway provided a combination of Application Development, Data Management, and Analytics services to further expand the capabilities of its client’s existing Amazon Web Services (AWS) architecture. They used the AWS pipeline to execute SQL and take the data from a staging table to the production table. By providing the right technical skills, Kenway was able to develop a fully functional “Analytics Pipeline” to bring the data into a data warehouse and make it available for the analytics tool. This new data warehouse was built on Redshift. To help enrich the demographic data of the users, a third-party data source was brought in to merge with the client’s app usage data. The demographic data was provided monthly through an SFTP site that Kenway automated to retrieve, load and merge to its client’s data set. This additional data source provided more insightful analytics to the customers.

What We Delivered

Kenway delivered a fully automated, end-to-end process that pulled the app data already being stored on AWS using APIs, loaded it to a normalized data model on the newly-built Redshift data warehouse, and visualized it using Qlik Sense BI reports.  The end-to-end solution included the following:

• To retrieve the demographic file, created a Unix script on a Linux EC2 instance that pulled the files down from the third party’s FTP site, and loaded them into the client’s Amazon S3 bucket until loaded into the data warehouse
• A C# batch job that made API calls to the client’s API to retrieve the application usage data of the institution’s visitors and load it into the staging environment in the data warehouse
• Normalized data model on Redshift
• AWS Data Pipelines calling SQL scripts that moved the data from staging, applied business cleaning rules, and loaded the clean data to the live tables for reporting
• Archived scripts to keep the tables lean and efficient
• Full, end-to-end logging of the processes to alert in case of failures
• BI reports using Qlik Sense that used live data from the Redshift data warehouse
• Leveraged client’s API to bring beacon data into Redshift daily for locations that were active. Used an audit framework in SQL (tables) to see each run, the result of records loaded for each location, failures, etc. This process was also built so the daily batch jobs to pull data from the API into Redshift were completely rerunnable.
• Integrated “load history” tables to be able to view the detailed records for a load
• Set up a monthly load process from the vendor (LiveRamp) and built a new process to integrate via SSH File Transfer Protocol (SFTP). When a job was looking for that file, it took the file once loaded, moved the data into the Redshift warehouse, archived the file to wait for next month’s file, and sent an email notification to a distribution list when the file load was complete.

The Result

Kenway developed a fully automated, end-to-end process to support the visualizations needed to help the client’s customers understand the value of their data and make informed decisions. The reports that were created provided insight into who was visiting their institutions, where visitors were spending most of their time, most-visited areas of the property, etc.

If you’d like to learn more about how Kenway can help with your Analytics Pipeline or our custom development expertise, reach out to us at info@kenwayconsulting.com.

A few examples of the visuals that were created:

Visual 1 – demographics:

Visual 2 app usage summary:

Visual 3 – app openings:

Visual 4 – traffic patterns:

Visual 5 – favorites within the app:

Visual 6 – videos watched within the app: