How Kenway Consulting Helped A Client with their Strategic Decision Making by Providing Business Intelligence and Analytical Insight on User Data
Industry: Technology
Solution: Data Warehouse and Data Analytics
Client: Virtual Engagement and Mobile Application Company
An organization who focuses on virtual engagement and provides cultural institutions with an enhanced experience for visitors by turning a mobile device into a personal concierge, an expert tour guide, a map, and more collects invaluable information for the organizations they work with. While providing a great experience for visitors through museum tours, augmented reality, and virtual reality experiences at zoos, parks, etc., the mobile app collects usage data such as time spent in an exhibit, videos watched, path taken through building, and other key indicators which can be analyzed to make strategic decisions around marketing, value of exhibits, and areas to focus for improvement. They are setting the new standard for virtual and mobile experiences at cultural institutions and need to ensure that the data they collect can be leveraged by their customers. Read the full business intelligence case study below.
The applications developed collected large amounts of data from users but lacked an effective way to make this data insightful for their clients. They were 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 landscape had to offer them, and what tools would provide them with the abilities they needed to continue building out their 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 Ability to collect, analyze, and visualize millions of rows of data
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 applications developed collected large amounts of data from users but lacked an effective way to make this data insightful for their clients. They were 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 landscape had to offer them, and what tools would provide them with the abilities they needed to continue building out their 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 Ability to collect, analyze, and visualize millions of rows of data
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
Kenway provided a mix of services to build a solution that uniquely met the needs of this organization, including Vendor Assessment, Data Management, Business Intelligence (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. We wrote APIs to pull all data into staging tables and wrote SQL scripts to pull that data out of the staging tables into a normalized data model. We then used a QlikView reporting tool to visualize the data.
To determine the best BI tool on the market for our client, we 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 end to end process of source to dashboards, an ability to handle larger volumes of data/scale to support big data, and intuitive visualizations that are easy to use. As an aid to this assessment Kenway’s BI expert created a white paper to weigh and analyze features offered by each of the vendors being considered. The paper highlighted key areas of importance such as stress test, strength and weakness deep-dive, total cost, and logistics an 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 requirements our client had defined.
To bring the application usage data into insightful visuals, we provided a combination of Application Development, Data Management, and Analytics services to further expand the capabilities of our client’s existing Amazon Web Services (AWS) architecture. We used the AWS pipeline to execute SQL to 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 through an SFTP site on a monthly basis which we automated to retrieve, load, and merge to our client’s data set. This additional data source provided more insightful analytics to the customers.
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:
A Unix script on a Linux EC2 instance that pulled the demographic files down from a third party’s FTP site and loaded it into our client’s Amazon S3 bucket
A C# batch job that called our client’s API to retrieve the application usage data of the institution’s visitors and loaded it into a staging environment in the data warehouse.
An audit framework in SQL showing 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 re-runnable.
AWS Data Pipelines calling SQL scripts that moved the data from staging, applied business & cleansing rules, then loaded the clean data into live tables for reporting.
A monthly load and integration process of vendor data (LiveRamp) via SSH File Transfer Protocol (SFTP). Using a file watcher to trigger a job to move data into the Redshift warehouse, archive the file, and send an email notification when complete.
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.
A normalized data model on Redshift.
Integrated “load history” tables to be able to view the detailed records for a load.
Archiving scripts to keep the tables lean and efficient.
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:
A Unix script on a Linux EC2 instance that pulled the demographic files down from a third party’s FTP site and loaded it into our client’s Amazon S3 bucket
A C# batch job that called our client’s API to retrieve the application usage data of the institution’s visitors and loaded it into a staging environment in the data warehouse.
An audit framework in SQL showing 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 re-runnable.
AWS Data Pipelines calling SQL scripts that moved the data from staging, applied business & cleansing rules, then loaded the clean data into live tables for reporting.
A monthly load and integration process of vendor data (LiveRamp) via SSH File Transfer Protocol (SFTP). Using a file watcher to trigger a job to move data into the Redshift warehouse, archive the file, and send an email notification when complete.
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.
A normalized data model on Redshift.
Integrated “load history” tables to be able to view the detailed records for a load.
Archiving scripts to keep the tables lean and efficient.
Kenway developed a fully automated end-to-end process to support the visualizations needed to help our client’s customers understand the value of their data and make informed decisions. The reports that were created provide insight who is visiting their institutions, where visitors are spending most of their time, most-visited areas of the property, etc.
Artificial intelligence (AI) is rapidly transforming industries, offering exciting opportunities to optimize processes, gain valuable insights, and unlock new levels of efficiency. However, navigating the…
Introduction Throughout the past year, Kenway Consultants have been deeply involved in orchestrating the buildout of a Contact Center as a Service (CCaaS) implementation at…
With the official announcement of Microsoft Fabric, Kenway has explored how this new toolset will impact data-driven organizations across industries. As data professionals, we continually…
