Press "Enter" to skip to content

Optimizely to MySQL: Move Data in Minutes

Are you trying to derive deeper insights from your Optimizely experiments by moving experiments data into a larger database like MySQL? Well, you have landed on the right post. This post talks about two methods to move data from Optimizely to MySQL. Before we dive in, let us briefly understand these individual systems.

Understanding Optimizely

Optimizely is an A/B Experimenting platform where you 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 – A/B testing, feature rollouts, deep technology integration that allows running personalized experiments across devices. These have made Optimizely a popular experimentation platform among the modern teams.

Understanding 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 so you can use it on Windows, Mac or Linux as well.  

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

Methods to Move data from Optimizely to MySQL

Method 1: Write a Custom Script

This would need you to understand the Optimizely Rest API, build a code using PHP or another server-side scripting language to extract data. Next, you would need to clean and prepare the data and finally, load it to MySQL. This is a time-consuming exercise and would need you to invest in engineering bandwidth. 

Method 2: Using Hevo Automated Data Pipeline Platform

Hevo is an automated data pipeline platform that can move your data from Optimizely to MySQL very quickly without writing a single line of code. In comparison to the first approach, this is a lot easier, hassle-free, and reliable. 

This article explores both the methods in detail one by one. 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. 

Optimizely to MySQL:  Writing Custom Code

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 the 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: https://api.optimizely.com/v2/projects

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 that you extract, first of all, you need to create tables for entities like projects, experiments, audiences, events, etc. Once, this has been done, you would need to prepare the JSON data output from the previous step, parse it and convert 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 '\r\n' 

Optimizely to MySQL: Disadvantages of Moving data Using Custom Code

There are several disadvantages using this method which are the following:

  1. Developing, testing and deploying this requires proper infrastructure and a great deal of effort.
  2. 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. 
  3. The above solution is brittle. Any changes in Optimizely API means you have to rebuild the code otherwise it may corrupt the data or even worse, lead to data loss.
  4. 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. 

Easier Approach: Optimizely to MySQL

Hevo is an automated data pipeline platform using which you move data from Optimizely to MySQL instantly, without having to write any code. Using Hevo you can move data from Optimizely to MySQL in 2 simple steps: 

  • Authorize and configure your Optimizely data source.
  • Configure the MySQL database where you want to move your data.

Hevo will take all groundwork of moving data from Optimizely to MySQL in a secure, consistent and reliable fashion. Sign up for a 14-day free trial to try Hevo today.

Here are more reasons to try Hevo:

  1. Simple Implementation:  With a few clicks of a mouse you can move data from Optimizely to MySQL. This will save you precious time and enable you to focus on deriving insights from Optimizely data.
  2. Complete and Consistent Data Transfer:  As compared to the custom script method, migration using Hevo is reliable and consistent. In case of any errors, Hevo’s fault-tolerant technology automatically handles it and ensure that your data flow never breaks.
  3. End to End Management: After configuring Optimizely and MySQL Hevo will take care of complete monitoring and management without any human intervention.
  4. Comprehensive List of Data Sources: Hevo can connect many other data sources, in addition to Optimizely, making it a suitable data pipeline partner for your growing data infrastructure.
  5. Extensive: Hevo provides a team of product experts, ready to assist 24 hours a day, 7 days a week over email and chat. Thi

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.

Still not sure that Hevo is right for you? Then try our risk-free, expense-free 14-day trial, and experience for yourself the ease and efficiency offered by Hevo. 

ETL Data to Redshift, Bigquery, Snowflake

Move Data from any Source to Warehouse in Real-time

Sign up today to get $500 Free Credits to try Hevo!
Start Free Trial