ETL and Data Integration Process

The ETL or the data integration process stands for Extraction, Transformation and Loading of the data. ETL is basic and essential process for any Master Data Management (MDM) or Data Ware House (DW) project.

The ETL Tools are used to save the time and make the whole process more accurate and free of error. The following ETL tools are used in the industry today.

Comercial ETL Tools:

  • IBM Infosphere DataStage
  • Informatica PowerCenter
  • Oracle Warehouse Builder (OWB)
  • Oracle Data Integrator (ODI)
  • SAS ETL Studio
  • Business Objects Data Integrator(BODI)
  • Microsoft SQL Server Integration Services(SSIS)
  • Ab Initio

Freeware / open source ETL tools:

  • Pentaho Data Integration (Kettle)
  • Talend Integrator Suite
  • CloverETL
  • Jasper ETL

MDM need of the hour for Indian Banks (Big or small)!

Master Data Management (MDM) why do we need one?

Indian Banks usually are satisfied that the CBS (Core Banking System) is sufficient in generating a unique customer identifier for every new customer. While this is true, consolidation of customer information by tying to a single unique id is dependent on manual process which needs to be compiled by the staff.

It is observed, that many a times when new product is sold to existing customer, a new customer identifier is assigned. For example an existing customer walks into a different branch of the bank. The staff treats him/ her as a new customer, unless the customer himself tells that he is an old customer.

This can happen due to lack of standardize processes throughout the bank, CBS system is not able to check for spelling mistakes, mistakes in addresses, only minimal data or incomplete data is entered, etc. It is pretty much impossible to ensure good quality data, only relying in CBS. Few banks have centralized account opening, which would minimize errors but still human errors cannot be avoided. Over the time, the data quality deteriorates and keeps on deteriorating until the point of no return.

The data quality in Indian Banks is an interesting topic of research. With the data quality a big question mark, it is not a surprise that not many banks use their valuable data to gain insight into their businesses nor do they use it to define their business strategy and marketing strategy. The problem with having dirty data is that, it is ever increasing exponentially and it is just going to sit there in unusable format sucking up resources. Instead of keeping piles of dirty data, why not leverage it to your own advantage? Organized, validated and clean data will give a fresh impetus to your marketing strategy. It is time for banks to look ‘Beyond Core Banking’, to gain a competitive edge over their competition.

MDM can help banks to achieve desired differentiation in the marketplace. MDM will also help banks with their growth and retention goals by giving quick access to Customer and also Household profiles. MDM will help establish a single version of truth across the Bank which can be leveraged by various channels (such as Branch, ATM, Internet, Mobile platforms). Since Marketing is done at a Household level, understanding the needs and profile of the complete household is necessary for targeted Marketing, and providing the Customers differentiated offerings.

An effective MDM strategy enables organizations to:

  • Get a 360 degree view of customers to better understand their needs and increase cross-sell and up-sell opportunities
  • Complete Household profile, and the wallet share across the household (not just an individual)
  • Regulatory compliance and reducing risk
  • Improve customer service, retention, and loyalty
  • Improve operational efficiencies and reduce costs
  • Single view of the truth to improve decision making and strategic agility

RBI guidelines for banks are getting stricter day by day. Recently RBI lashed hefty fines on some big banks for non-compliance with KYC norms. With fines running into crores, banks all over India, have suddenly waken up to the KYC compliance issues. Few banks have even started contacting customers who have opened account in/before the year 2008, to comply with the RBI’s requirement of re- submitting the KYC every 5 years. Similarly, the gate-keeper program of the MDM will ensure there are no KYC compliance lapses.

The banks are contacting all of their customers, who have opened accounts before 2008. Some customers might have changed their addresses in these five years; some might have already submitted the KYC documents. Such blanket approach, might strain banks relations with their customers. All of the above issues can be avoided if the bank has MDM (Master Data Management) in place. With MDM, it would be easy to determine, which customers to contact for resubmission of KYC documents, as MDM takes care of all the change management as well has an audit trail of the changed information.

Recently RBI has issued a circular under the KYC/AML/CFT Guidelines, to give unique customeridentification code (UCIC) to all of bank customers. This guideline is applicable to Scheduled Commercial Banks/ Local Area Banks / All India Financial Institutions/ UCB’s and NBFC’s. What that means, even if one customer has a 2-3 customer id’s with the bank, these records have to be identified and given one unique identification code. With MDM, this would also be taken care off, as it is the master data of the customer.

Conclusion: Indian Banks need to look ‘Beyond Core Banking’ systems. It is the need of the hour. For Banks to comply with the regulatory compliances and to gain insights into their business, Master Data Management (MDM) is not optional any more. It is a necessity!

Retail Analytics

Visit: Modak Analytics

Retail sector is the fastest blooming business enterprise in our country. This can be validated from the fact that it accounts for 14-15% of Indian GDP. With the economic and technological landscapes transforming itself to complete sophistication, retailing industry also needs customize its services according to their upgraded customers; this often stands out as a challenge for the retailers, often clinging to scalability and profitability.

The critical challenges that retailers’ face is usually price sensitivity, demand forecasting, inventory management, multi-channel marketing, cross-sell, etc. Retailing in this competitive era is the process of getting right products to the right customers at the right time at the right price. Gone are the days of ‘stack it high and fly high’, now it’s a predictable routine. This means that retailing now should be more efficient and organized. This effective organization can be pulled up with the help of analytics.

Forecasting has long been important to retailers across a variety of functions. In many cases in the past, however, different groups and functions created their own forecasts to inform ordering, staffing, merchandising, and budgeting. Stores and regions created bottom-up forecasts; corporate created top-down forecasts. Whatever be the type of forecast, a firm needs to analyze the data it has collected through different ways, like transaction history, surveys, competitions, etc. having a good fore-view of demand structure will add up to the bonus point for a retailer. Retail Analytics can be a great asset for a business organization leading it competitively ahead among others. With the proper application of analytics a retailer can.

  • Develop close relationships with customers based on a deep understanding of their behaviors and needs;
  • Deliver the targeted advertising, promotions and product offers to customers that will motivate them to buy;
  • Balance inventory with demand so you’re never out of stock or carrying excess inventory;
  • Charge exactly the price that customers are willing to pay at any moment;
  • Determine the best use of marketing investments; locate stores, distribution centers, and other facilities in optimal locations.

The main question lingering around would be regarding the actual turf where this analytics provide support. Analytics mainly cater to:

  • Demand forecasting
  • Price and promotion modelling
  • Price rules and performance targets management
  • Price and promotion optimization
  • Markdowns optimization
  • Category management
  • Product assortment selection
  • Store clustering and price zone definition
  • Competition analysis
  • Market basket analysis
  • Customer segmentation

Some of the most successful retailers are using analytics for reducing stock outs at retail locations. Retailers are starting to understand how analytics can be leveraged to provide several quantifiable benefits: reduced stock outs, reduced inventory levels, optimized delivery schedules, and more efficient ordering process.

The prerequisite for an analytical process is the availability of the data. Analytics is impossible without clear, fine quality, integrated and accessible data, which retailers have in plenty comprised from – point of sales transaction, from websites, from credit programs, from current loyalty programs, from Enterprise Resource Planning (ERP), and other such business applications. Once a retailer has such vast and accessible amount of data then it can apply any analytical process among so many of them. The few analytical processes widely used are:

  • Assortment Optimization and Shelf Space Allocation
  • Customer-Driven Marketing
  • Fraud Detection and Prevention
  • Integrated Forecasting
  • Localization and Clustering
  • Marketing Mix Modeling
  • Pricing Optimization
  • Product Recommendation
  • Real Estate Optimization
  • Supply Chain Analytics
  • Test and Learn
  • Workforce Analytics
  • Adoption and Use of Analytics
  • Analytical Ecosystems
  • Centralizing Analytics

These are few of the many trends of Retail Analytics. Different retailers can use different analytical process that will benefit them the most.

Image courtesy: Sacha Orloff Group News

Media Mix Optimization

Most of the businesses pour in a lot of money into advertising their product or firm, which does have positive returns if planned well. An important problem which media planners face with is media allocation including budget allocation for an advertising campaign in an optimal frame. And therefore marketers are increasingly cautious about the money spent on advertising and demand more accountability from their communication partners about their marketing spending, making it imperative for media planners to invest their marketing money in the best possible manner to maximize returns in terms of incremental sales, seeking to justify their marketing spending.

Many firms choose a wise blend of offline and online media to reach their prospects; but it has become quite difficult to know which channel it credit to as a result of the cross-over of the different channels. Consumers can be engaged with multiple channels at the same time. A direct contact can lead the customer to a website, then to a social media site to read other customers’ reviews. Firms want to invest more in media that works and cut media that is not working. But this means more than comparing the results of one channel to another. It requires insight into how the various channels cross over, interact and mix with each other and how consumers influence each other.

Media Mix Optimization (MMO) helps marketers sort out the confusion and put the money where they can expect rich returns. In simpler words, MMO is a comprehensive and dynamic strategic capability that can assist marketers to quantitatively measure, plan, organize and optimize the media resources and investments. Many companies are turning to the media mix optimization, delivering high performance audience and media management. Designing to optimize the performance of media mix investments, this technique takes the guesswork out of planning — helping marketers to better orchestrate and execute their marketing strategy.

Media Mix Optimization can be achieved by carrying out a Marketing Mix Model. “Marketing Mix Modelling” is a subset of the overall “Media Optimization” problem, which is encompassed by the over-arching question, “If I am a marketing manager and I have Rs.1,00,000/- to spend – where and how should I spend it?” This model helps at larger scope to solve the various questions of marketers regarding the investment. For designing this model, an analysis is required on the historical data of a firm. Media spending and sales information are required for media mix analysis. Data on advertising effectiveness, pricing, sponsorships, events, and competition can be incorporated in the analysis and models if they are available. Retail, Communications, Entertainment, Pharmaceuticals, etc. are the industries that suit best for this type of approach, since they have got vast amount of historical data available.

Media mix optimization seeks to develop a causal relationship between consumers, segments to response, and to drill down into which media mix actually drives consumer behavior for firms’ high value audience. It reduces the guess work through better attribution; quantifying media mix decisions through causal channel contributions to sales. The solution is designed to eliminate misleading performance measures and align the marketing organization through common goals. It is only as helpful as the validity of their predictions. Consider the need for a system of self checking the accuracy of your optimization model and its’ recommendations by testing and measuring initial results against objectives. Incremental adjustments will increase visibility, accuracy and ROI.

Image courtesy: 'suite101'

Copyright: Modak Analytics