How to Recover Events That are Sent to The Wrong Mixpanel Account
Let’s say you have Mixpanel integrated into your product. Obviously, to test it you hook the test environment up to a separate Mixpanel account and you change the config in your deployment to make sure production and test end up at the proper environments.
But what if your deployment has a bug? Then it could be that production events end up in your test environment or vice versa. This exact scenario happened to me. In my case, the production events ended up in the test environment. This is bad because it breaks all the cohorts that overlap the period in which this was broken and in that sense screws with months of valuable data. It will leave your retention graph with a hole in it.
Recovery is in order and here’s how I did it:
First, you need to pull the relevant events out of the test environment. To do this you need to use the export API of Mixpanel. The API is reasonably well documented, but I still got stuck for a bit on the `sig` parameter. You need to strip all the other parameters from the URL, mash them together with the secret, and md5 the whole bunch. From the command line it goes like this:
md5 -s api_key=expire=1426714419from_date=2015-02-20to_date=2015-03-14 | pbcopy
If you don’t have pbcopy installed (it’s a mac thing) you just copy the output with Ctrl-C, which works fine as well.
Then you can assemble a curl command that pulls the data from Mixpanel.
curl 'https://data.mixpanel.com/api/2.0/export/?from_date=2015-02-20&expire=1426714419&sig=&to_date=2015-03-14&api_key=' > all-events
Now you have a file with an event on each line. To import it into another Mixpanel account you have a few more hoops to jump through. However, you may first want to select only the production events. I used a list of production id’s and some magic grep command to do the trick. You can do the same if you use to identify with an id that you can find in your production database. I ended up with something along these lines:
cat production_ids | xargs -I § grep § all-events
Once you’ve selected the relevant events, you have to add a “token” property in the filtered file. I used vi to replace
"token":""}} on each line of the file.
Then you have to base64 encode the events so they can be used in the import API.
cat filtered |while read line; do echo $line | base64 ; done > filtered-base64
The funny thing here is that instead of using a POST request, the guys over at Mixpanel decided that writing data with a GET is fine, OK, and not even the least bit, well, completely bonkers… never mind. Don’t argue, hack on!
cat filtered-base64 | sed "s/^/curl 'http:\/\/api.mixpanel.com\/import\/?data=/g" | sed "s/$/\&api_key=ae6bb01a49629edc96f6fc8c9a4b09e5'/g" | sh
The above line (apart from a useless use of cat for which I will never apologize) has a beautiful bunch of sed commands to duct tape the final command together, piping the whole thing to sh to shove it up Mixpanel’s, well, API. If all goes well, you’ll see a long list of 11111111’s telling you each request was swallowed by the Mixpanel API.
After that, you’ll have your events back, and that’s worth going through the whole ordeal.