Batch Data Process

This page contains a comprehensive overview of the daily data extraction process in Optimove.

Intro

After Optimove receives all historical data, a batch-data process is needed to update Optimove’s data warehouse daily. This data extraction process varies by the chosen transfer method—File Delivery, Database Extraction, or API—which must be determined at the start of onboarding.

Data Delivery

The data delivery methods supported by Optimove are file delivery, database connection, and API connection.
In this page you can find an explanation of the different data delivery methods, including an explanation of each method and what is needed to use it.

Data Readiness

Optimove’s batch-data ETL process is the first task in the batch-data update phase on
Optimove’s side so data readiness time is the daily process start time.

Data readiness time must be provided and must include:

• The time that all data is ready to be transferred
  For files: time of the day that the files are received in SFTP
  For DB to DB: Time of the day that the DB that Optimove ingests the data from is updated

• The Data readiness time must be provided in UTC time zone. In case you are working in a different timezone, please convert the readiness time from your timezone to UTC and then provide it to Optimove

• The data readiness time must be provided to Optimove by the client before setting up the daily batch process in Optimove

Data Readiness – Testing and Protection

To ensure batch data readiness, Optimove’s ETL is equipped with both standard and customizable testing and protection features. In the event of a failure in any of these mechanisms, an automated email notification is sent to a predefined list of recipients designated by the client. The notification details the issue encountered and includes actionable steps required to address the problem, ensuring minimal disruption to the data processing workflow.

Default Mechanisms

Missing Files Procedure (only for file data delivery):

Optimove’s ETL checks that all the files that are expected to be received, are available in the SFTP / storage before transferring the data.

📌In the 'Check Missing FIles' page you can find an explanation of the missing files check and how it is being used in the daily batch process.

Check Data Exists Procedure:

At the end of almost every ETL in Optimove, the procedure goes through all the
tables that contain a date column and checks if Optimove got a reliable amount of data (similar to the amount of data received in the previous days).

If the Check Data Exists (CDE) Procedure has failed

1. Fail daily process – the site will not be updated, and campaigns will not be executed until the issue is fixed.
   Notification will be sent to the client.
2. Don’t fail daily process – The site will be updated, and campaigns executed. Notification will be sent to the client.

📌In theData Volume ValidationData Volume Validationpage you can find an explanation of theprocedure ans what is needed in order to use ut in the daily process

Non-Default Mechanisms

Success files check (for files only)

An optional check relies on a SUCCESS file with the current date – which will also be
uploaded to the cloud storage or the SFTP every day.

• The SUCCESS file should be loaded once all the batch data is available to be transferred to Optimove.
• Before starting the ETL, Optimove’s process will check if the success
  file is available.
• If a file is not available – the batch-data process on Optimove’s side will fail and an email notification with the failure details will be sent to the stakeholders.

Daily Log table check (for database/API connections only).

An optional check relies on a Daily Log table, which is managed by the client.

• The table can contain only one date column every day when the data is ready to be collected by Optimove on the source side.
• Each day, a new record should be added to the table - with the current date.
• Before starting the ETL, Optimove’s process will check if today’s date is
  available in the table.
• If today’s date is not available - the process will fail and a notification with the failure details will be sent to the stakeholders.

Data Pull Process

In the daily batch process, Optimove will pull the client’s data on a daily basis.
Data can be provided in 2 ways:

1. Incremental: Optimove will pull/receive data only of the prior day.

   Relevant for transactional / players data that has an unstable trend (can have lower or higher amount of data each day).

   For files: Each daily file will contain only the data of the prior day. The file size can change from daily file to another.

   Example of incremental files:

   

Each daily file contains only the delta data and therefore, the amount of data in the file can be higher or lower than the day before.

For Database: Each day, Optimove will pull only the data that was updated since the prior day based on the LastUpdated column.

Example of incremental data pull query from the client’s DB:

select *
from Transactions
where LastUpdated >= {Yesterday's_date}

2.Full: Optimove will pull/receive a full table every day.
For example mapping tables such as GameTypesAndCategories and BetDetails.

For files: Each daily file will contain all data for the table including old data and delta data.

Example of full files:

Each daily file contains the full table data and therefore, the amount of data in the file will be identical or higher than the day before.

For Database: Each day, Optimove will pull the whole data that exists in the table.

Example of full data pull query from the client’s DB:

select *
from GameTypesAndCategories

ETL Form

In order to decide what tables will be part of the daily process in Optimove and how each table should be pulled or handled, the client will fill out an ETL form.

In this Link you can find an example of an ETL form with an explanation of each section.