Optimove's ETL process (Extract, Transform, Load) is the first task in Optimove's batch-data process.
The ETL process is instrumental to your ability to deliver relevant messages to customers. In some cases, the process can fail for various reasons including missing data, discrepancies, or invalid files or values. In these cases, Optimove's support team will send an automatic alert to the pre-defined distribution list (including your Customer Success Manager).
Optimove is not able to proceed in running the batch-data process until the data issue is fixed. That is, to make sure that customers will receive the most up-to-date communications.
In the following article, we will explore several key concepts about the ETL process, as well as scenarios in which the process might fail and how to solve them.
|| Click here to read our technical documentation
Optimove’s Data Transfer Methods
If you don't know which method is used to transfer your data, contact your manager or the technical person on your team. Understanding which method you are using is key to understanding the issues that can arise from each method.
Optimove provides three main data transfer methods: Files Delivery, Database Extraction, and API.
Files Delivery
If your site receives data via “Files Delivery,” the files of the batch-data process can be made available to Optimove in one of the following methods:
- Cloud Storage – Managed by you, via AWS S3, Google Cloud Storage, or Azure blob
- SFTP – Managed by either Optimove or your team
Optimove will extract, transform, and load the data from the above sources and into its data warehouse. Some files will only contain incremental batch data (transactional data and fact tables), while others will contain all the available data.
Database-to-Database Extraction
Optimove’s batch-data process may be performed over a database-to-database connection.
The connection is made available either by whitelisting Optimove's IP addresses on your side or over a site-to-site VPN. Optimove's daily ETL process will extract the data directly from your database.
API
Optimove’s batch-data process may be performed over an API connection. In order to establish the connection, you should send our team full documentation, including details, on the API calls as well as relevant credentials. The connection is made available either by whitelisting Optimove's IP addresses on your side or over a site-to-site VPN.
Retry Mechanism
A retry mechanism will, upon failure, send a notification and retry the process after a pre-defined interval. Setting up such a mechanism requires coordination with the Optimove team when configuring the batch-data process.
The retries will be performed until the issue is resolved or until all retries fail, which will result in a “batch-data process final failure.”
When a batch-data process final failure occurs, the process can be resumed manually after a notification from the client stakeholders to Optimove support declaring that the data is ready.
Data Transfer Failure Scenarios
In case of a failure, you'll receive an email notification, and a yellow clock sign will appear next to the Data Update in the burger menu.
Below is a list of possible failure scenarios. Some failure scenarios only apply to one of the data transfer methods, while others may apply to both. Please follow the steps according to the type of email notification you received.
Missing Files
* Relevant for Files Delivery only.
Optimove's ETL process checks that all the expected files are available in the SFTP (or any other storage option) before transferring the data to your Optimove site.
If any files are missing, the process fails and an email with the details will be sent to your team and Customer Success Manager. If some of the files should not be seen as process blockers, please notify us immediately.
What does this notification mean?
- The batch-data process didn't run
- The site isn't updated with the latest data
- No campaigns were executed
What should you do about it?
- Follow up with your data team to upload the missing files as stated in the email received from our Support team.
- Once the files have been uploaded, please reply to the email and let our team know to run your batch-data process again.
Missing data / Data Schema Changes / Data Duplication
* Relevant for any data delivery method.
Optimove's ETL goes through all tables that contain a date column and compares the amount of data to previous successful updates. This ensures that the amount of data is reliable.
If data appears to be missing on any table that is analyzed, the process fails, and an email with the details will be sent to your team and Customer Success Manager.
If there are any tables that shouldn't be included in these checks or should use different values, please notify us to avoid unnecessary failures.
What does this notification mean?
- The batch-data process didn't run
- The site isn't updated with the latest data
- No campaigns were executed
What should you do about it?
- Your data team needs to investigate the reason for the missing data and update the relevant tables. Once fixed, let our Support team know that they can run the batch-data process.
- If you wish to continue running the batch-data process despite the missing data, please confirm by replying to the email sent to you.
Invalid Files
*Relevant for any data delivery method.
To ensure the success of the ETL, the data should be provided in a file that we convert and later upload into our database.
The Invalid Files failure scenario happens when we receive data in the wrong format. For example:
- A format of a specific file or table has changed.
- There are too many spaces between characters or values are too long.
- A file contains an extra delimiter that caused the rest of the row to skip a column.
- An email with the exact reason will be sent to your team and Customer Success Manager. This has to be fixed for us to run your batch-data process.
What does this notification mean?
- The batch-data process didn't run
- The site isn't updated with the latest data
- No campaigns were executed
What should you do about it?
- Your data team needs to investigate the structural changes and fix them. Once the files or tables are fixed and updated, let our Support team know that they can run the batch-data process again.
Missing data in the Batch-Data Log table
* Relevant for Database Extraction or API connection.
The Batch-Data Log table is a table managed on your side. Every day, when the data is ready to be fetched by Optimove, a new record with the current date should be added to that table. Optimove's ETL checks if today's date is available in the log table.
If the data is not ready and the log table isn't updated, the process fails, and an email with the details will be sent to your team and Customer Success Manager.
What does this notification mean?
- The batch-data process didn't run
- The site isn't updated with the latest data
- No campaigns were executed
What should you do about it?
- Your data team needs to investigate why the log table has missing information and whether the data is ready to be extracted.
- Once the problem is fixed, let our Support team know that they can run the batch-data process again.
Connection Issue
* Relevant for Database Extraction or API connection.
In a direct connection to your database, Optimove is pulling the data directly from the tables you instructed us to use as part of your onboarding phase. This usually requires VPN permission or whitelisting the Optimove's IP addresses. Any change in the requirements mentioned can cause a failure of the ETL process.
Additional reasons for a failure can be due to an unusual load on your server, which in turn doesn't allow Optimove to connect properly or blocks Optimove from connecting altogether.
In case of a connection issue, a notification with the details will be sent to your team and Customer Success Manager.
What does this notification mean?
- The batch-data process didn't run
- The site isn't updated with the latest data
- No campaigns were executed
What should you do about it?
- Your data team needs to investigate why Optimove is unable to connect to your servers. Once the connection was fixed, let our Support team know that they can run the batch-data process again.
Data Discrepancies
During the ETL process, Optimove may detect discrepancies in the received data compared to historical data. These discrepancies often arise due to a misalignment in the value length of one or more fields sent during the daily batch process.
The following table outlines common data discrepancy scenarios, their implications, and recommended actions:
| Scenario | Example | Impact | Action |
|---|---|---|---|
| Value length exceeds historical norms | A field historically containing 10-character values now receives values up to 50 characters, exceeding the expected length by 1.5 times. | The ETL process continues, and campaigns are executed using the received data. However, unexpected values may affect campaign accuracy or reporting. |
|
What does this mean?
- The batch-data process does not fail, and campaigns are executed based on the received data.
- Data discrepancies may lead to inconsistencies in campaign targeting or reporting if not addressed.
What should you do about it?
- Review the warning notification stating where the discrepancy is.
- Investigate the cause of the discrepancy with your data team.
- Correct the data for the next day’s run if issues are identified.
- Contact Optimove support for assistance with historical updates if needed.
Making Changes to Your Data Delivery Method
Any changes to the data delivery method, data source, or structure, should be coordinated with the Optimove team as these changes would, in most cases, require changes in Optimove's batch-data process as well.
In most cases, making changes without coordinating with Optimove will result in the batch-data process failing. Contact your CSM or Optimove support and notify the team of any change.
Examples of changes that can lead to failures include (but are not limited to):
- Changing names of tables/file
- Removing tables/files
- Adding, removing, or changing column names
- Changing columns’ data type
- Changing the primary keys
- Changing the customer ID of existing customers
It is important to note that every data connection and setup is different. Changes are not automatically mapped, and therefore, you need to make sure to communicate planned changes to Optimove in advance.
In addition, make sure to communicate to your internal team(s) that the structure of files or database used for Optimove's batch-data process should not be changed without coordination, which should be done with proper notice to allow for the required configuration changes on the Optimove side.
FAQ
How can I know if my Optimove Site is up to date?
To monitor your process, click the burger menu at the top right corner of your screen. Within the burger menu, you can see the last time your site was updated and the status of each of the phases separately.
If you see yesterday's date and three green checkmarks, this indicates that your site is up to date – meaning all data has been imported to Optimove, campaigns have been executed, and all Optimove reports are updated.
When the batch-data process is running, you will see the icons in yellow and the date will not update. The icons will turn green as each phase completes. If a phase has not been completed, the icons will turn yellow and Optimove's support team will proactively reach out via email with the full info.
How can I prevent possible failures?
The success of this vital phase depends on both sides. We recommend setting up validations on your side as well. It is also important to communicate the importance of data readiness and the consequences of process failure.
Another way to prevent possible failures is to set the batch-data process scheduled time to the UTC zone. This will help you avoid failures due to daylight changes.
How can I tell if my campaigns ran smoothly after a failure?
Once Optimove re-runs your batch-data process, campaigns will be executed for your customers by default. The only case where Optimove will not execute campaigns is when a campaign’s Scheduled time had already passed. Since the campaign execution was already completed, we will not automatically execute these campaigns.
Any campaign that has the status "Not run," “Failed,” or "Partially failed" will be reported by Optimove's support team via email. To execute these campaigns, go to the Marketing Plan, hover over the campaign details, and click on the "run now" button.