Enterprise Data Warehouse & BI Platform Development for National Publishing Conglomerate

Business Objective

A billion-dollar publishing conglomerate based in Chicago came to eSage Group looking for help with the design, development and production support of a cloud based integrated enterprise sales and marketing data warehouse and BI platform to allow tracking and analysis of sales and marketing performance across the entire customer journey, for nine company owned newspapers.

The client wanted eSage Group to design and develop a user friendly reporting infrastructure that would allow for rapid development of various cross-channel sales and marketing performance reports and dashboards. The client also wanted the ability for members of their internal Analytics team to have self-service capabilities to quickly develop their own ad-hoc reports on the fly.

The data warehouse would need to be designed to contain information on the entire customer journey from the time a prospect was presented with a marketing offer, through order subscription and the balance of the customer lifecycle.

Solution
A new multi-dimensional data warehouse and reporting cube was designed and developed to combine digital marketing data from the following sources:

  • Marketing G2 (MG2) platform
  • Omniture Website analytics
  • Subscription Management database containing info on all subscriptions, subscription types, pricing and terms
  • Editorial content management platform for the 9 papers
  • Social engagement feeds from various social platforms like Facebook and Twitter
  • DoubleClick ad management information

Information from the different data sources was combined and linked within a new data warehouse to allow the ability to track all customer touchpoints across sales, marketing, content, social and customer subscription data sources.

The solution was created using a combination of Microsoft and Hadoop related SQL and Big Data components. A Hortonworks data platform (HDP) warehouse was designed and developed to run on a Microsoft Azure cloud based solution. Data was pulled into the HDP platform on a daily basis from the various data sources using ETL’s developed in Python and scheduled via Oozie. Data was processed via SSIS, ETL’s and Hive to import into the new SQL Server reporting warehouse and cube. Connections into the HDP data warehouse were established from Power BI and the new in-house SQL Server reporting database we developed.

The Microsoft Power BI solution was connected to the Azure HDP Data Warehouse on the cloud for reporting and analytics. A daily data refresh built by eSage Group supplied all Power BI reports the most current performance information available for analysis. Users are able to view various canned and ad-hoc reports via the Power BI reporting interface. Power users can query directly against the data warehouse for detailed analysis and drill downs.

Results

With the new Azure cloud based enterprise data and BI solution designed and developed by the eSage Group, the client was now able to view the integrated data contained in the new enterprise caliber data warehouse to understand the following types of performance metrics:

  • Content consumption behavior by customer segment over time (business reader, sports enthusiast, entertainment fan, etc)
  • Marketing performance of a given promotional offer by region and publication
  • Social media platform performance at driving content consumption and engagement
  • Customer lifetime value by publication
  • Marketing performance by channel/property and device type
  • Marketing-mix analysis
  • Content engagement analysis by device type (via social and other channels)
  • Revenue optimization by channel source and customer segment
  • Content pathing analysis by customer segment
  • Customer subscription performance by marketing source/property

The new enterprise data warehouse was designed and deployed to the cloud to offer a level of speed and flexibility not previously available at the company.

The new solution offered the client a flexible set of previously unavailable cross-channel reports and dashboards to monitor the performance of key drivers of customer engagement, new subscriptions and revenue across the customer journey. Reports were easily tailored to the specific needs of the various business units within the company; such as sales, marketing/social and editorial.

The solution was flexible and easily expandable, making it efficient to add additional data sources, processing power and reports as needed.

Deliverables
  • Designed and developed enterprise data warehouse on Microsoft Azure Cloud Platform & HDInsight (Hortonworks Hadoop)
  • Microsoft SQL Server/SSIS Reporting Cube
  • Microsoft Power BI
  • Ad hoc Hive data access