Recente Projetcs

ETL Process for Google Ads Data Ingestion

This project involves the ingestion of hundreds of files generated daily by Google Ads, containing detailed information on vehicle sales from dealerships, including data on campaigns, clicks, impressions, and costs. The objective is to process this data and load it into a staging area in the Data Warehouse, where it will later be used for detailed analysis and generating performance reports for the vehicle sales campaigns

The ETL process was divided into two distinct pipelines for better organization and control:

Pipeline 1 - Extraction and Preparation

The first pipeline is responsible for retrieving the CSV files from the FTP server, where the business team stores them after being generated by Google Ads. The retrieved files are filtered according to business requirements to ensure that only relevant data is processed. After filtering, the files are saved to Azure Blob Storage, ready to be transferred to the Data Warehouse.

Pipeline 2 - Loading and File Management

The second pipeline scans the files stored in Blob Storage and transfers the data to the Data Warehouse. After the data is successfully transferred, the files in Blob Storage are organized. Those that have been imported are moved to specific folders, facilitating auditing and process maintenance.
This pipeline also includes error handling. Files that encounter issues during the import process are automatically moved to an error folder, where they can be reviewed and corrected later.

Results

This automated process allows continuous ingestion of large volumes of advertising campaign data, ensuring that information is always available in the Data Warehouse for performance analysis. The processed data is sent to a staging area in the Data Warehouse, where it is integrated with data from other platforms, providing a unified view. This integrated data is then used by the business team to generate detailed performance reports on the ads, enabling more effective strategic decision-making.

Stack

  • Azure Blob Storage
  • Azure Data Factory
  • SQL Server

© Copyright 2024- All Rights Reserved