Enhancement of marketing campaigns with Google BigQuery

Challenge

The customer, a leading company in the telecommunications sector, needs to improve the process of defining marketing campaigns aimed at its loyal customers: promotions are defined by retrieving data from various customer management applications in an unstructured way, with manual and manipulated processes directly from reporting applications, with a high risk of making mistakes or not using up-to-date information.

With this goal, the Customer has entrusted Qintesi with the creation of a marketing data mart in which to bring together, with fully automated loading and integration processes, the data of its customers, available in different sources, both in the Cloud and on premise.

Solution

In agreement with the Customer, it was decided to opt for a Cloud platform, using the services made available by the Google Cloud Platform. More specifically, the marketing data mart was created with Google BigQuery, a serverless, scalable and pay-per-use data warehouse service, therefore without installation costs, maintenance and service interruptions.

Google BigQuery has integrated data from:

• Customer database management, catalogs, subscriptions (Postgres on Google Cloud SQL)
• Order management
• Customer Service
• Operational marketing platform
• Navigation data of the e-commerce site (Google Analytics)

Data is loaded hourly, using federated queries for Cloud SQL and Rest API for other systems.

The updated data, after the necessary cleaning and normalization operations, is available for reporting after a few minutes.

Particular attention was paid to process monitoring and data quality assurance thanks to the design and implementation of a custom logging system with configurable alerting thresholds, whose indicators can be consulted in BigQuery via a dedicated dashboard.