Data: Architecting a Data Warehouse


A large health-focused agency had been investing heavy resources into building and maintaining its own digital assets and assets for other offices.  Executive leadership needed a detailed view of the impact and value that each invested dollar gave to its internal and external stakeholders.  This agency sought to:

  1. Establish a comprehensive analytics database that securely houses historical Web traffic, engagement, quality assurance, SEO, process, and financial data for all digital assets including websites and social media platforms.
  2.  Ensure that the analytics warehouse structures all data sources in a common format that can later be leveraged for advanced business intelligence, including workflow process optimization analysis and trend prediction.
  3.  Develop and automate performance visualizations (dashboards) that report on Key Performance Indicators (KPIs) using data from the analytics warehouse, allowing leadership to make informed decisions based on the impact of each digital asset.
Illustration of a bar chart


CTAC architected an Analytics Data Warehouse (ADW) with AWS services and implemented processes for the assimilation of data from over 18 different data sources and ingested more than 230 million rows of data for analysis.  We created a streamlined data pipeline able to process data in multiple formats (CSV, JSON, SQL) and granularities at various frequencies (daily, weekly, monthly, etc.). CTAC also integrated the ADW with Tableau and Amazon Quicksight allowing the creation of standardized dashboards and visual displays of the KPIs for executives to quickly intake the volumes of data.

Illustration of a bar chart


CTAC’s solution architecture, data pipeline set-up, and ETL processing allow the client to collect and quickly analyze Terabytes of data.  CTAC architected the data warehouse using available AWS services as well as established the data model to store the terabytes of data.  The data is presented via Tableau dashboards and comprised of the data collected in the ADW.  The client leadership team utilizes the dashboard to make budgeting decisions, such as which digital assets should be funded and at what level, as well as to under the performance of their products and teams by tracking the impact and engagement resulting from each of their digital assets.  Client leadership is now able to shift resources to more effectively utilize the public tax dollars while tying dollar value directly to results.