Mixpanel is one of the more widely known and used analytics platforms. As such, there’s a lot of data locked away inside the platform. If you’re like us, you like to keep your data in a central location, likely a SQL database. Mixpanel understand this and offer a raw data export API to help the process along. You’ll probably need to do this on a regular basis if you have an app with reasonable use.

In this scenario you’re left with a number of questions.

  1. How can you export your Mixpanel data with minimal hassle?
  2. Is there some way you can have the data pre-defined into SQL syntax including TABLE and INSERT statements so you don’t need to manually do it?
  3. Can you somehow set up an automated pipeline for all future data exports?

The answer to those questions lies with APIs.

​

Part one: MixPanel API

Mixpanel has a raw data export API that exports JSON data. There are two pieces of documentation that outline their raw data export protocols:

Everything you need to know to export data from Mixpanel is in the above two links. The TL;DR is:

  • You can use one of the various client libraries
  • The endpoint is data.mixpanel.com
  • The URI is https://data.mixpanel.com/api/2.0/export/ which can be used with the following:

Mixpanel URIs

  • An example from Mixpanel’s docs: https://data.mixpanel.com/api/2.0/export/?from_date=2012-02-14&to_date=2012-02-14&where=properties%5B%22%24os%22%5D+%3D%3D+%22Linux%22&event=%5B%22Viewed+report%22%5D

And that’s how you export JSON from Mixpanel.

​

Part two: SQLizer API

Once you’ve navigated through the Mixpanel export process, the hard part’s over. Now you need to convert JSON to SQL. That’s where the SQLizer API comes in.

The Sqlizer API is a simple REST interface that gives you programmatic access to our file conversion algorithm. The processes of converting a file with the API follows four steps:

  1. Initiate the file conversion
  2. Upload your file
  3. Monitor the conversion progress
  4. Download your converted file

See full docs for more detail.

​

Step 1: Initiate the file conversion

A JSON to SQL file conversion can be initiated with a POST request to http://sqlizer.io/api/files/ and later accessed and updated at the endpoint http://sqlizer.io/api/files/{ID}/.

If the request is successful the server will respond with a HTTP 200 code and a JSON representation of the file conversion. The ID field is especially important as it is used in the url of all future requests for this file conversion.

​

Step 2: Upload your file

Once you have a file ID you can begin uploading your file data by sending POST requests to http://sqlizer.io/api/files/{ID}/data/.

The Sqlizer API allows you to upload your data in a single request or multiple requests. The latter is achieved by splitting the file into parts and sending each individually. Most files can be uploaded in a single request but if your file is larger than 100Mb or you have an unreliable connection an upload in multiple parts is recommended.

Check out the full API documentation for in-depth details on single and multiple requests.

​

Step 3: Monitor the conversion progress

Once your file upload is finalised it will be placed in a queue for processing. You can check on its progress with a GET request to http://sqlizer.io/api/files/{ID}/. curl http://sqlizer.io/api/files/7EVHQlVpq6YrRGLxNyjJdZ-b7DN3hcKpbqwK215IyPpE8ZeddSK4GVe_q0LNdCZnNCwOi1ewyTSEVMy6rkpi8g==/ \-H "Authorization: Bearer {API-KEY}"

You can see sample responses in full API documentation.

​

Step 4: Download your converted file

Once the file conversion process is complete you can download the converted file from the url specified in the returned ResultUrl parameter.

🎊 Success! 🎉

​

Part three: SQL file into SQL database

Now all that’s left to do is simply import/upload your new SQL file into your database. Because SQLizer has pre-defined TABLE and INSERT statements this step is simply waiting for your file to import/upload.

​

Automate and be done with it

With this setup you could potentially automate the entire process of exporting Mixpanel data and getting it into your own database. You can even set up a large-scale pipeline with the SQLizer API, convert files en-masse.

Happy automating!

SQLizer converts files into SQL databases. With an API to help automate conversion tasks, it’s one of the best ways to transfer data between databases. Convert a file now.