Are you looking to move data from Optimizely to Redshift for deeper analysis? If the answer is yes, then you are on the right blog. This article will explain in detail 2 methods of moving data from Optimizely to Redshift. Before that, the article will provide a brief understanding of the two applications. Read along to understand the 2 methods of setting up your Optimizely to Redshift integration and decide which method suits you more.
Prerequisites
- An Optimizely account.
- Required permissions to create and access Amazon S3.
- A successfully set up Amazon Redshift Data Warehouse.
Introduction to Optimizely
Image Source
Optimizely is an experience optimization platform that enables businesses to create and run personalized experiments across mobile apps, websites, and connected devices. Optimizely is a managed platform that is easy to set up, deploy and use.
The experiments created on Optimizely, in turn, deliver valuable insights on how the customers interact with these features, landing pages, and so on. This helps companies build better products and experiences for their customers.
Optimizely creates a lot of data on these experiments that can be exported using their rich REST APIs.
To learn more about Optimizely, visit here.
Introduction to Amazon Redshift
Image Source
AWS Redshift is a Data Warehouse built using MPP (massively parallel processing) architecture. It is owned and maintained by Amazon Web Services (AWS). It has the ability to handle a large volume of data and analytical workloads. It is completely managed, easy to set up, and has a high performance even for large data sets. These features combined with its pricing have made it one of the preferred data warehouses among the modern data teams.
To learn more about Amazon Redshift, visit here.
Method 1: Manual ETL Process to Set up Optimizely to Redshift Integration
Optimizely provides APIs that allow you to extract data programmatically in JSON Files. This data needs to be converted into CSV format because JSON format is not supported by Redshift. Next, you would need to prepare this data and load it to Amazon S3 and then to Redshift. This would need you to invest in deploying dev resources, who understand both Optimizely and Redshift infrastructures, and can set up the data migration from scratch. This would also be a time-consuming approach.
Method 2: Using Hevo Data to Set up Optimizely to Redshift Integration
Hevo Data provides a hassle-free solution and helps you directly transfer data from Optimizely to Redshift and numerous other Databases/Data Warehouses or destinations of your choice instantly without having to write any code. Hevo comes with a graphical interface that allows you to configure your Optimizely source and load data in real-time.
Get Started with Hevo for Free
Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Hevo’s pre-built with 100+ data sources (including 30+ free data sources) will take full charge of the data transfer process, allowing you to focus on key business activities.
Get started with Hevo today! Sign up for the 14-day free trial!
Methods to Set up Optimizely to Redshift Integration
You can implement the following 2 methods to set up your Optimizely to Redshift Integration:
Method 1: Manual ETL Process to Set up Optimizely to Redshift Integration
The following 3 steps will manually set up your Optimizely to Redshift integration:
Step 1: Extract Data from Optimizely Using APIs
Optimizely has a very mature set of API endpoints that allow you to extract data on experiments, projects, events, and more programmatically. Upon invoking the API, you will get a JSON file as output. This file would contain the data that you have requested.
Making an API call
In the example below, we will invoke the List Projects endpoint to obtain a list of projects. When using REST API, you must include a request header called Authorization. Set the authorization to specify a Bearer token. You will be able to find the token in your Optimizely account.
This is equivalent to authorizing any account with a secure password before loading the data.
Below is a REST API request. Replace {personal_token} with the access token generated above and execute the command.
curl -H "Authorization: Bearer {personal_token}" "https://api.optimizely.com/v2/projects"
A response listing all the projects will be returned from the above request.
[
{
"name": "Test Project",
"confidence_threshold": 0.9,
"dcp_service_id": 121234,
"description": "Project for user sign up flow",
"platform": "web",
"sdks": [
"android"
],
"status": "active",
"web_snippet": {
"enable_force_variation": false,
"exclude_disabled_experiments": false,
"exclude_names": true,
"include_jquery": true,
"ip_anonymization": false,
"ip_filter": "^206.23.100.([5-9][0-9]|1([0-4][0-9]|50))$",
"library": "jquery-1.11.3-trim",
"project_javascript": "alert("Active Experiment")",
"code_revision": 0,
"js_file_size": 63495
},
"account_id": 12345,
"created": "2018-05-11T17:59:51.753Z",
"id": 1000,
"is_classic": true,
"last_modified": "2018-05-11T17:59:51.753Z",
"socket_token": "AABBCCDD~123456789"
}
]
The response is a JSON format. Each JSON object may have more than one attribute. This should be parsed before loading your data into your Data warehouse.
Step 2: Convert Data into CSV Format
Next, you would need to convert the JSON data obtained in the previous step into a CSV file. The last piece of the puzzle would be to load data into Redshift.
You can use an online tool like the one shown in the below image to convert JSON Files to CSV FIles.
Image Source
Step 3: Load Data into Redshift
Use the CREATE TABLE command to create a table in the Redshift Data Warehouse. Ensure you have identified the columns you need to insert before creating the table. You would be able to determine this by looking at the JSON output from the previous step.
Inserting data directly into Redshift is generally not advised. This is because it inserts the data row by row and this is not optimal. So, the right way is to load data into Amazon S3 and load it into the Redshift using the COPY command.
You can read more about COPY commands here.
Challenges of Building a Custom Setup
- Accessing Optimizely Data in Real-time: By now, you have successfully created a program that loads Optimizely data into Redshift. The challenge is that your program does not load new and updated data in real-time. You may decide to replicate the data but this slows the process and makes the operation resource intensive.
- Infrastructure Maintenance: When you are moving data from Optimizely to Redshift, many things may go wrong. For example, the Optimizely APIs may be updated or sometimes the data in the warehouse might not be available. These are the issues that cause data flow to stop, leading to severe data loss. So, a team that continuously monitors and maintains the infrastructure would be needed.
Method 2: Using Hevo Data to Set up Optimizely to Redshift Integration
Image Source
Hevo Data, a No-code Data Pipeline, helps you directly transfer data from Optimizely and 100+ other data sources to Data Warehouses such as Redshift, Databases, BI tools, or a destination of your choice in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.
Sign up here for a 14-Day Free Trial!
Hevo Data takes care of all your Data Preprocessing needs and lets you focus on key business activities and draw a much powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination.
The Advantages of Using Hevo:
- No Data Loss – Hevo is a fault-tolerant architecture that ensures the data is moved from Optimizely to Redshift reliably without data loss.
- 100’s of Out of the Box Integrations – In addition to Optimizely, Hevo brings data from Databases, Cloud Applications, SDKs, and so on into Redshift. This ensures that you have a reliable partner to cater to your growing data needs.
- Minimal Setup: The data load from Optimizely to Redshift can be set up on a point and click interface, without any developer assistance.
- Automatic Schema Detection and Mapping: Hevo scans the schema of incoming Optimizely data automatically. When there are any detected changes, it handles seamlessly by incorporating the change on Redshift.
- Exceptional Support: Hevo provides live support over email and chat. This ensures that you always have a team that can solve your troubles promptly.
Conclusion
The article explained to you the two methods of connecting Optimizely to Redshift in a step-by-step manner. It also discussed the limitations of writing custom scripts to set up your ETL process. If you have no qualms about facing those limitations then you can try the manual ETL setup.
Visit our Website to Explore Hevo
Hevo Data on the other hand can simplify your task by eliminating the need to write any code. It will automate the process of data transfer from Optimizely to Redshift and provide you with a hassle-free experience. Hevo provides granular logs that allow you to monitor the health and flow of your data.
In addition to Optimizely, Hevo can load data from a multitude of other data sources including Databases, Cloud Applications, SDKs, and more. This allows you to scale up your data infrastructure on demand and start moving data from all the applications important for your business.
The ease of use, speed, reliability and excellent service – Hevo offers all-around performance. Sign Up for a 14-day free trial and experience the power of Hevo first hand.
What are your thoughts on moving data from Optimizely to Redshift? Let us know in the comments.