Overview

Our client, a major American music corporation, is a production house of media and entertainment data. It receives regular feeds of data on digital and streaming sales from its distribution partners Spotify, Apple, Pandora, Rhapsody, You Tube and so on. More than 50 % of its sales is obtained through online channels and other digital content in the music industry.

This data is further used by the marketing and finance departments, to analyse customer usage patterns and gain further insights from country-wise sales of albums and recording labels, downloads and shares of popular tracks and so on.

Challenges

While trying to achieve its business needs, the following challenges placed an impact on the performance and scalability of the existing system:

• The Lack of a Unified Data Warehouse System: The system’s siloed data sources caused a hindrance in storing the incoming data on-premises. The current system uses Informatica for ETL support, which couldn’t scale up to the increase in data volume from the client’s streaming distribution partners.
• Complexities in the Legacy Infrastructure: The data latencies of over 24 hours in the existing system could not support Deep Dive Analyses and the LYSD (Last year same day) sales details was difficult. Besides, it could also not perform ad-hoc analysis on the incoming 500 TB data, due to increased query latency. This complexity in query processing further delayed the decision-making process.
• Increased Downtime Hours and Costs: The existing on-prem database faced significant overheads. The MS SQL Server database required at least 1 day (24 hours) of down time for its high cost maintenance activities, with increased overall Total Cost of Ownership.
• Security Risks in the Existing On-Prem Database: The data usage across countries, categories and streaming partners is being stored in a single on-prem database, which caused an impending security risk of data mismanagement and overlaps.

Solution Highlights

To address the above challenges, Agile GCP Labs, a Google Cloud Partner, functioned as a suitable offshore partner for the client, to support, architect and implement an effective on-prem migration operation from MS/Oracle on-prem database to cloud-based Google Cloud Platform (GCP). The key components of the implemented solution are as follows:

a) Data Warehouse Solution using Google Big Query:

The data from the distribution partners was obtained via API support and transferred to Redshift, using Amazon S3 Data Lake. From here, it was further migrated to Google Big Query and stored into GCP’s cloud-based enterprise data lake, Google Cloud Storage.

• Google Compute Engine, an Infrastructure as a Service (IaaS) solution which runs enormous workloads on the physical hardware, with a scalable number of virtual machines (VMs) serving as large compute clusters, to store the massive Media and Entertainment Data.
• Google Cloud Storage which offers faster data lake solution, for storage and archival of the unstructured data files.

Through Google Big Query, a Unified Source of Truth was established for the users, to combine the enormous incoming data from multiple data sources and provide seamless integration onto a single platform. The ETL tool, Informatica ETL, was replaced by GCP’s Google Cloud Dataflow, to process the unbounded raw data obtained from the client’s streaming partners.

This new cloud-based system comprises a flexible and scalable data architecture, which supports powerful visual analytics and offers near real time data usage updates.

b) Reporting Support using Micro strategy:

The sales data from the physical and digital world were consolidated to provide a single view of product sales. A centralized enterprise dashboard was created to handle and create over 30,000+ Enterprise level reports and 50+ dashboards into one reporting platform, while using end user information for reporting and visualization operations. With the newly established system, the client can now obtain deeper insights on music sales from over 100 classifications such as Artists, Geography and Partners, for its music consumers.

Through this On-prem to Cloud Migration (SQL Server to Google Big Query) the data loading & processing times have been reduced significantly and businesses can now share insights freely. As GCP offers Cost-Effective features such as free usage limits, up to the second billing, consumption-based charges and pay as you go pricing, the customized reporting operations on Brand interests, ad recall lifts, unique users and view through rates for global music genres have been improved.

Results

• Reduced Data Latency: The migrated cloud-based system offers reduced data latency, leading to an overall Total Cost of Ownership of 56-88%. The minimal down time also required lesser maintenance hours, i.e., 1-2 hours in GCP implementation.
• Quicker Results: An immediate reduction in query time from 1 day to 10 seconds, with over 50 TB of data processed in less than a minute.
• Significant Cost Reduction and Improvement in Key Business Operations: There has been an increase in subscription and promotional sales via streaming partners, reduction in royalty management costs, 20% reduction in infrastructure costs, ease in availability of customer insights on Products (Music) by market share, reduction in yearly marketing budget, growth profile, partner distribution, engagement rates and so on, due to pay-as-you-go pricing options with the new platform.
• Speedier Answers to Business Questions: With Google Big Query, the client could now perform granular analysis of over 500+ data points, providing a better understanding of customer behaviour via predictive modelling. Micro Strategy integrated with Google Big Query also allows Business users/Decision makers to generate business answers in minutes. Through secured data storage systems, these users can share key business insights securely.

Related Case Studies

Agile GCP Labs helps Universal Music Group improve Analytics performance by 60%

Agile GCP Labs built Marketing Analytics for Kids Atelier on Google Cloud Platform

Agile GCP Labs helps Call Tracking company scale the VMs with Google Cloud Platform

Agile GCP Labs helps SocialHi5 with migration of Digital Marketing Analytics to Google Cloud

Agile GCP Labs helps Digital Marketing Firm with migration of 150 websites to Google Cloud Platform

Agile GCP Labs quickens Pharma sales insights for AMGEN by building a AI-powered Enterprise BI Bot

Agile GCP Labs enables CI-CD DevOps for Global M&E Conglomerate