FMCG ETL SOLUTION
ORGANIZATIONOur client is the top FMCG Company operating in 50+ countries with consumers across the globe. A thorough analysis of customer behavior in Asian region was required for improving efficiency and effectiveness of campaigns, targeted marketing and product design. Relative poor quality of consumer data and lack of proper standardized processes to collect and validate the data in Asian region posed a serious problem for customer segmentation and future product strategies.
CHALLENGEThe consumer data from different Asian countries was collected by 3rd party agencies and sent to the client on a request basis. The data comes from heterogeneous data sources – different countries (Japan, Korea, China, Hong Kong, and Taiwan), different sources (websites, online retail, surveys, and other touch-points), and different database platforms (Oracle®, SQL Server®, and MySQL™). It was getting increasingly difficult to validate, cleanse and match the consumer data gathered through these sources. There was no standard process for collecting, standardizing and cleansing the data before using it in their CRM and BI applications. A data standardization & validation process and an automated solution was required to extract and load all the consumer data coming from various data sources in different countries into a central warehouse, called 1-Consumer Place, to be used for 360-degree consumer view.
SOLUTIONXTIVIA DEVELOPED A GOVERNANCE FRAMEWORK THAT FACILITATED:
- Registration and configuration of 3rd party data sources with the client
- Data source approval process from client
- Data profiling and data validation
- Mapping the data sources to the target warehouse data elements
- Landing source on ETL server in XML format (using PHP to extract from heterogeneous sources and output XML on ETL server)
- Automated backup of the landed source data
- Loading XML data to staging database using Teradata Parallel Transporter
- Cleansing and Data Standardization process (using BTEQ and SQL)
- Rejections from Data Cleansing and Standardization process sent to Data Stewards for review and correction
- Loading the data into Target data model Data sources are read once to minimize impact to the source. The data from different sources (Oracle, SQL Server, and MySQL) was read and stored in XML format, and backed up for regulatory compliance.