Client Challenge

Weekly TRP assessment is a critical and accurate indicator of the viewership of the client’s various programs and service providers. Two distinct data sources are involved in the process. TRP data extracted from the Broadcast Audience Research Council (BARC) India application is collated and reconciled with spot data from Markdata. The reconciled data is injected into ETL and then it goes into the analytics server for deriving market insights.

Owing to high human intervention in the process, accuracy was taking a hit, leading to the wrong assessment of program and channel popularity, which ultimately caused business errors. Attempts to streamline the manual way of data extraction, aggregation, and reconciliation didn’t bear fruit and finally, the client decided to opt for complete automation of the process.

Solution

Our team worked on building a robot that executed the process through the following steps

Data Extraction from BARC

The Bot authenticates the BARC Media Works application and updates the database to extract the current weekly report. It then downloads and saves the TRP data for Channel, Week Target, Rat%, etc. and derives critical readings such as GRPs, Channel Group.

MAFRAS Authentication

Bot authenticates the MAFRAS application, navigates to Manage Clients, and for each client provided in the configuration file, it adds and removes channels in subscriptions.

Download Spot Data

Spot Data related to the channel name, market data, sum duration, television rating, sort type, etc. is exported from the MAFRAS application in an Excel format.

Data Validation and Reconciliation

With the two data sets, the bot performs the below set of reconciliation to ensure zero errors.

  • Channel Wise GRP (Gross Rating Point) – BARC GRP is reconciled with Markdata GRP for each channel provided in the predefined template.
  • Channel Group Wise GRP – BARC GRP is reconciled with Markdata GRP for each channel group provided in the predefined template.
  • Channel-Wise Free Commercial Time – BARC FCT (Free Commercial Time) is reconciled with corresponding information from Markdata for each channel provided in the predefined template.

Loading ETL

The reconciled file is pushed into the ETL engine using the FileZilla FTP and processed for market insights.

Share Process Status via Email

The bot sends process status emails to configured stakeholders.

Benefits of the Automated TRP process

  • Elimination of Human Involvement - Automated data extraction frees up staff time and allows them to focus on other critical fronts.
  • Increased Productivity - A typical data entry bot does the work of 4 to 8 regular employees while maintaining high accuracy through zero data processing error and faster anomaly detection.
  • Reduced Costs - Bots are much quicker in executing tasks, thus ensuring faster delivery and lower overheads.

Operational Pointers

Before Automation Automated Process
FTE 5 1
Transactions per day 5 50
Time per transaction 120 minutes 10 minutes

Process Flow Diagram

Technology Stack

  • UiPath
  • Bark Media Works Desktop
  • MAFRAS (Mark Data as Framework)
  • FileZilla FTP

How to start the journey?

To choose the right AS400 support for your business, we would like to understand your needs better. The form below is a great way to start that!