Connecting Optimizely to MySQL: 2 Easy Methods

Are you trying to derive deeper insights from your Optimizely experiments data by moving the experiments data into a larger Database like MySQL? Well, you have landed on the right article. Now, it has become easier to replicate data from Optimizely to MySQL.

This article will give you a brief overview of Optimizely and MySQL. You will also get to know how you can set up your Optimizely to MySQL integration using 2 methods. Moreover, the limitations in the case of the manual method will also be discussed in further sections. Read along to decide which method of connecting Optimizely to MySQL is best for you.

Prerequisites

You will have a much easier time understanding the ways for setting up the Optimizely to MySQL connection if you have gone through the following aspects:

• An active account on Optimizely.
• An active account on MySQL.
• Working knowledge of Web APIs (Application Programming Interface).
• Working knowledge of SQL and at least one scripting language.

Introduction to Optimizely

Optimizely is an A/B Experimenting platform where you can run your experiments before rolling out new features of your software applications. Optimizely allows customers to build A/B tests that enable them to take a data-driven approach to decide which landing page or product features are appreciated more by the customers. Optimizely comes with a wide range of features like A/B Testing, Feature Rollouts, Deep Technology Integration that allows running personalized experiments across devices. These have made Optimizely a popular experimentation platform among modern teams.

More information on Optimizely can be found here.

Introduction to MySQL

MySQL is an open-source Relational Database Management System based on SQL (Structured Query Language). With its proven Performance, Reliability, and Ease of Use, MySQL is the world’s most popular Database used by famous open source applications like WordPress, Magento, Open Cart, Joomla as well as top websites like Facebook, YouTube, and Twitter. MySQL is a platform-independent Database and thus enables you to use it on Windows, Mac, or Linux as well.  

Both Optimizely and MySQL have similarities in the arrangement of data as Optimizely maintains data in the form of relational entities like Projects, Experiments, Results, etc. while MySQL maintains these entities as separate tables and manages their relationships using foreign keys. 

More information on SQL can be found here.

Methods to Set up Optimizely to MySQL Integration

This article delves into both the manual and using Hevo methods in depth. You will also see some of the pros and cons of these approaches and would be able to pick the best method based on your use case. Below are the two methods:

• Method 1: Manual ETL Process to Set up Optimizely to MySQL Integration
• Method 2: Using Hevo Data to Set up Optimizely to MySQL Integration

Method 1: Manual ETL Process to Set up Optimizely to MySQL Integration

You can follow these steps to manually create a connection between Optimizely and MySQL:

• Step 1: Accessing Optimizely API to Extract Data
• Step 2: Preparing Data to transfer into MySQL

Step 1: Accessing Optimizely API to Extract Data

First of all, you need to get data from Optimizely using its Rest API. Using Optimizely APIs, you can extract data about entities like Projects, Experiments, Audiences, Events, etc. As an output, Optimizely returns a JSON file, which then needs to be converted into CSV and loaded to MySQL. 

For example, the following API endpoint allows you to list down all the projects in your Optmizely account.

To visit the API Endpoint, click here.

The API endpoint can be called by building a script using PHP or by simply using CURL commands on the terminal. Here is what the CURL code would look like: 

curl -H "Authorization: Bearer {personal_token}" "https://api.optimizely.com/v2/projects"

With every API call, you would need to supply your Optimizely personal token for authentication. Here is a sample JSON output: 

[

  {

    "name": "Project A,

    "confidence_threshold": 0.7,

    "dcp_service_id": 1875657,

    "description": "Test Project",

    "platform": "web",

    "sdks": [

      "android"

    ],

    "status": "active",

    "web_snippet": {

      "enable_force_variation": true,

      "exclude_disabled_experiments": true,

      "exclude_names": false,

      "include_jquery": true,

      "ip_anonymization": false,

      "ip_filter": "^156.25.123.([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": 66769

    },

    "account_id": 145667,

    "created": "2020-04-03T06:49:44.793Z",

    "id": 1344,

    "is_classic": true,

    "last_modified": "2020-05-11T17:59:51.753Z",

    "socket_token": "AABBCCDD~123456789"

  }

]

You can read more about the endpoints supported by Optimizely APIs here. 

Step 2: Preparing Data to transfer into MySQL

Based on the data you have extracted, first of all, you need to create tables for entities like Projects, Experiments, Audiences, Events, etc. Once, this is done, you would need to prepare the JSON data output from the previous step, parse it and convert it to CSV. MySQL cannot load JSON data, hence this step is necessary. You can do this using Linux command-line utility called JQ.

You can read more about this utility here.

Once you have the CSV file ready, use the below command to load the data to MySQL.

LOAD DATA INFILE 'optimizely.csv' INTO TABLE projects

FIELDS TERMINATED BY ',' 

ENCLOSED BY ' " '

LINES TERMINATED BY 'rn' 

After this step, your data will be successfully transferred from Optimizely to MySQL.

Challenges of Building a Custom Setup

Though you can efficiently connect Optimizely to MySQL manually, several limitations are associated with this method. Some of the limitations include:

• Developing, Testing, and Deploying using manual methods requires proper infrastructure and a great deal of effort.

• The above approach cannot give you data in real-time. You would need to write additional code and run cron jobs to achieve this. This is an additional hassle. 

• The above solution is brittle. Any changes in Optimizely API mean you have to rebuild the code otherwise, it may corrupt the data or even in the worst case, may lead to data loss.

• In case you need to transform your data before loading to MySQL programmatically, the above approach does not help. You would need to build custom code for that separately. 

Method 2: Using Hevo Data to Set up Optimizely to MySQL Integration

Hevo Data, a No-code Data Pipeline, helps you directly transfer data from Optimizely and 100+ other data sources to a Database such as MySQL, Data Warehouses, 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.

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. 

Sign up here for a 14-Day Free Trial!

Here are more reasons to try Hevo:

• Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
• Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
• Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
• Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
• Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
• Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.

Conclusion

This article provided an introduction to MySQL and Optimizely and explained their features. Moreover, it elaborated on the 2 methods which you can use to set up Optimizely to MySQL integration. The manual method although effective will require a lot of time and resources. Data Migration from Optimizely to MySQL is a painful and time taking process but using a data integration tool like Hevo can perform this process with no effort and no time.

Visit our Website to Explore Hevo

Businesses can use automated platforms like Hevo Data to set this integration and handle the ETL process. It helps you directly transfer data from a source of your choice to a Data Warehouse, Business Intelligence tools, or any other desired destination in a fully automated and secure manner without having to write any code and will provide you a hassle-free experience.

Want to take Hevo for a spin? Sign Up for the 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

Share your experience of connecting Optimizely and MySQL in the comments section below!