ETL Big Data Refactoring

ETL Big Data
Refactoring


The customer

Our client is an important data company operating for 25 years in the marketing field in Italy and whose core business concerns the support of companies in the selected and qualitative acquisition of customers and partners.

The state of the art

The ETL (Extract, Transform and Load) procedures existing at the customer stored data on relational databases fed through data sources of various kinds, incrementally, updating the existing data with new information. This obsolescence caused significant problems, specifically:

  • slowness of existing ETL procedures, as they are developed with technologies that are difficult to scale and parallelize;
  • poor maintainability of the code, due to the technologies used now in disuse;
  • high costs and difficulties in monitoring the various steps of the procedures;
  • presence of bugs that generated anomalies and inconsistencies of the data and on which it was difficult to intervene;

The challenge

The customer then decided, supported by our consultants, to exploit Big Data technologies for rewriting procedures with the aim of:

  • speed up execution times by solving the anomalies introduced by the procedures in use;
  • apply transformations and integrate multiple data sources at each step of the computation by exploiting the existing logics;
  • schedule and monitor the execution of pipelines;
  • analyze the data while maintaining the software already in use in order to eliminate the training costs of the resources involved.

The solution

To meet the requirements it was therefore decided to adopt the following technologies:

  • Python and Apache Spark to develop and execute data pipelines, rewriting the integration, matching and transformation logic and exploiting the parallelization and scalability offered by the framework;
  • Amazon S3 and HDFS for distributed and replicated storage of data during all phases of computation (input, intermediate results and output);
  • Cloud infrastructure for processing, considering that pipelines are performed at regular intervals and for a limited time and therefore an on-premise infrastructure is not required which involves much higher management costs (e.g. maintenance, unavailability or inactivity of resources, potentially variable SLA times, obsolescence);
  • Apache Airflow for cloud infrastructure provisioning, scheduling and pipeline monitoring.

The benefits

By applying the above technologies, it has been possible to achieve significant increases in terms of speed in the execution of operations, data quality and infrastructure scalability and significant cost reductions.

Increased speed:

  • overall pipeline execution times have been reduced by 85%: steps previously performed in 3 hours now require less than 30 minutes;
  • the developed pipelines operate on the entire data set, with a throughput of 1000 lines / second; the ETL procedures previously used operated on a small subset of data, with a throughput of 3 rows / second;
  • the data are acquired in a few seconds: in fact, it is enough to load them on S3 to make them immediately available for computation.

Lower costs:

  • the use of a cloud solution, in “pay-as-you-go” mode, allows you to exploit the computing power of the cluster only when it is needed and deactivate it at the end of the computation, avoiding unwanted costs ;
  • It was not necessary to train the resources that analyze the data involved, since the storage takes place in parallel also on the already existing relational databases; in fact, according to the customer’s requirements, the software for the analysis remained the same as previously used.

Improved quality and scalability:

  • the data now no longer show the anomalies previously encountered: the quality of the information provided to customers has improved;
  • it is now possible to monitor the various steps of the pipeline in order to identify any bottlenecks;
  • thanks to the flexibility of cloud solutions, it would be easy to scale and optimize the infrastructure used, should it prove necessary to increase resources.


Marketing Automation

Marketing
Automation


The customer

The client company, a company operating in the energy market, has been developing a marketing campaign aimed at acquiring new customers for more than 2 years through the Google Ads and Facebook Ads platforms.

The problem

The marketing campaigns launched by the aforementioned company suffer from the lack of an analysis of the results, so it is difficult to evaluate their effectiveness.

The solution

The solution provided has a dual objective, namely to provide the client company with detailed reports on the impact of the major reference metrics in the Social Marketing field in relation to the campaigns already carried out and to equip the company with powerful forecasting tools in view of the new campaigns to be start in order to correctly target their promotional activities.

Planning and Implementation

The project implemented by Koros Consulting required access as an Analyst to the Google and Facebook Ads accounts of the client company, so as to be able to carry out a data mining activity followed by a data cleaning activity using tools such as Office and Power BI.

At a later stage, Power BI reports were created in the form of dashboards concerning the analysis of the aforementioned data relating to the time interval from 30/01/2017 to 25/10/2019.

The last phase involved the goal of designing machine learning algorithms using libraries developed ad-hoc in Python language to be implemented on a web interface using the Flask microframework for the backend and the Vuejs framework for the frontend.

Google Ads Analysis

In order to evaluate the implementation of the customer assistance service, Koros Consulting has analyzed the data relating to the telephone call log resulting from the results of the Google Ads campaigns in the period from 06/10/2017 to 04/10/2019 . In addition, the main Social Media Marketing metrics were analyzed to plan future marketing campaigns and establish the most suitable content to increase engagement, including: impressions, coverage, CTR, cost per conversion. The goal was to pay attention not only to the content, but also the time and date of publication on Google, in order to become aware of the variables that affect user feedback.

Facebook Insights Analysis

Finally, following a data mining and data cleaning activity aimed at evaluating the effectiveness of the posts published on Facebook, an analysis was carried out, through the Power BI software, of the data from the posts published on Facebook in the interval temporal from 13/10/2018 to 07/10/219, managing to obtain important information about times, days of the week, months and target people in which the posts were most effective in terms of impressions, engagement rate, coverage, results, clicks and other metrics as well as the results achieved by each type of post published, as well as possible future trends.

Facebook Ads Analysis

After carrying out an analysis of the posts in general, Koros Consulting focused on a specific analysis of the sponsored posts. Tracing the activities of the previous point, he was able to obtain detailed information on an hourly, daily and monthly basis of the main performance metrics, including: coverage, impressions, results, amount spent, cost per result and conversion rate. In this way it was possible to provide the client company with useful considerations to better target the next marketing strategies via Facebook.

Support application

The final phase of the project involved the creation of an application to support business decisions in the marketing field. After having developed and trained a machine learning algorithm, we proceeded to its web implementation through the Flask framework.