Learn how to migrate your analytics data to Mixpanel with Zapier. That means a no code (just a snippet), easy to set up, method to get your most important historic events in Mixpanel FAST.
- Mixpanel doc for the import API: https://mixpanel.com/help/reference/importing-old-events
- Zapier: https://zapier.com/apps
- Snippet to Base64 your event data: https://gitlab.com/snippets/1697971
Thanks for checking out this video. If you’d like to see more content on the topic, please like and subscribe. If you hate it and think I should be playing Mario Kart instead, leave a comment below. Transcribed by otter.ai
- Website: https://nicholasgarofalo.com/
- Instagram: https://www.instagram.com/eidolonnight/
- Twitter: https://twitter.com/EidolonNight
- Facebook: https://fb.me/thenicholasgarofalo
- Linkedin: https://www.linkedin.com/in/nicholasgarofalo
Nicholas Garofalo 0:00
Hey everyone, tonight, I’m going to show you how to migrate your analytics data to mixpanel with no code, maybe a little bit of code, copy and paste code. Alright, so we’ve all been there before, you’re using Google Analytics or some other analytics platform and you’ve outgrown it, you’re either hitting the data caps, or the entire platform is getting so slow, it’s no longer useful. Chances are, if you’re watching this video, then you’ve already discovered mixpanel. mixpanel is great, it’s super fast, the reports are awesome. And they’re auto track ability takes the headaches out of tracking any event that you could possibly dream up. The problem, of course, being that anytime you change analytic software, you’re constantly dealing with losing all of your old data. And that’s a real bummer, especially if you’re an e commerce shop, or maybe running a subscription service. Anytime you want to run a funnel or retention report or just sort of track user behavior, losing all that retroactive data, it just sucks. So luckily, mixpanel actually has an import API, their standard tracking only allows you to go back five days. But using this API, you can go back in time as far as you could possibly dream up. Although actually I suppose if you go back like too far, there be some issues.
Nicholas Garofalo 1:25
But anyways, using this, you’re able to send retroactive event tracking to mixpanel. And it’s fairly simple. They give you a little snippet right here that you can use to send an event. Those events essentially, when you break this out, come down to sending a JSON object with the event information over to mixpanel after it’s been base 64 encoded. Now, this is all well and good. But in actuality, using the API is not as easy as we’d like. First of all, the error reporting doesn’t really exist, it pretty much just always answers back. Okay, even if things didn’t go well. Also, when it comes to this, you’re probably going to want to send more than one event and there aren’t really any ready made scripts out there to just send all of your events for you, which is where Zapier comes in. Zapier is a great platform that integrates with 1000s of different web services, if you’re familiar with IFTTT, If This Then That. It’s this very similar service that works with the Internet of Things. You know, you can say when I get home, turn on my lights, that sort of thing. Zapier is a bit like that, but like for the entire internet, so you can say when someone joins my mailing list, add them to my CRM software, that sort of thing. The other great thing about Zapier is that they allow you to manipulate the data along the way in multi step zaps. And you can receive and send web hooks, which pretty much opens up the entire internet to your integration fantasies. So what we’re actually going to do is use a Google spreadsheet to send all of our retroactive events into mixpanel. Now the reason why I’m going to use a Google spreadsheet is that almost every analytics platform allows you to export data as a CSV, comma separated values if you’re not familiar with the nomenclature. By taking that and importing it into Google Sheets, we then have it online in a format that we can read in Zapier. So right here, I have a sample document. with just the bare minimum, now you can send event properties and some other cool information. But for the sake of this demo, we’re just going to go over the bare minimum that you need to send and that would be the event name, in which case, we’re just using test in all caps. You also need to send a distinct ID, the distinct ID is essentially the user ID. It’s the identifier in mixpanel for the user. So if the user performing multiple events, it’s able to tie them all together. One of the easiest things to use is email, but you can use, you know, unique IDs out of your database, if you’re using like WordPress or some sort of platform like that, whatever. It’s arbitrary, that’s up to you. And then of course, here we have the timestamp. This is when the event occurred in history. Now, it’s important to remember again, if these have to be outside that five day window. If they’re inside that five day window, then you should be using the regular, you know, track API and mixpanel. Once we have our spreadsheet spreadsheet set up, we want to head over to Zapier and create a new zap now the trigger for this zap is going to be Google Sheets and is going to be a new or updated spreadsheet row. This will make it so that as we paste our events into the Google Sheet, the zap will automatically fire and import those events into mixpanel. If we jump down here to our options, this is just where we select the spreadsheet, you can of course, select the individual worksheets within it if you have multiple worksheets, and we’re going to trigger based off of any column being updated or edit.
Nicholas Garofalo 5:29
Nicholas Garofalo 7:22
Essentially, all this snippet does is takes our input data here, and it’s going to base 64 encoded the output of that if we, if we jump ahead a little bit here, the output of that is just this big string of seemingly random characters right. Now, backing up just a second, after we base 64 encode our event data, what we need to do is fire off a web hook. Now you’ll remember that I said Zapier can actually receive and send web hooks, which is great, because pretty much everybody receives and accepts web books. It’s, it’s like called the internet, like
Unknown Speaker 8:04
talks to the monks itself, it’s great. We’re going to set up a custom request here. And jumping over to our template here, it’s going to be essentially just everything directly out of the documentation, we’re going to send a POST request to the api.mixpanel.com slash import URL. We’re going to give it our data here. And then of course, authorization is going to be using our, our our API key out of mixpanel. If you are looking for that and can’t find it, let’s hop over here to mixpanel. You all you need to actually do is click on the gear and click on Project Settings. There you will find your token as well as your API secret. Backing up a little bit. Once we have all of that set up, we just click Continue. We can actually then send a test to mixpanel. So I’m going to send a test right now. All right, cool. So now if I hop over into mixpanel, and I refreshed you can see I actually sent a test earlier already.
Nicholas Garofalo 9:17
We now have two times this event has fired in the past. Today is February 8, and as you can see this event fired for January 21. So there you have it that is sending events from a CSV over to mixpanel. Historically, with a little bit of copy and paste code and that’s it. There’s really no limits here. The one thing I will say is that Zapier will try to protect you if you do put in a few 100 events in a batch. Zapier will actually pause the zap and send you an email saying are you sure you want to fire this many zaps? Because billing is based on the number of times you zap things. So you are protected there in case like something horribly wrong happens. And you didn’t mean to do that. If you did mean to do that, you can go ahead, click their link and resume this app and it will send all of those events to mixpanel in small batches, which is really nice. Now, in terms of pricing, Zapier is pricing is very fair, it scales nicely. Depending on the number of events you want to send, this may or may not be feasible, if you’re sending an absolute like crazy amount of events, then you’re probably going to want to grab a developer, have them write up a script that’s going to run and send all these for you. I’ve done imports from kissmetrics with, you know, two years of data. And you know, it’s taken a couple of days in order to send all of those events. So it would be definitely worth at that point grabbing someone who knows what they’re doing to actually, you know, do that for you. But if all you’re doing is sending your critical events like purchases or sign ups or that sort of thing, then using Zapier, paying maybe a couple 100 bucks for your first month to send a large batch of events is probably going to end up being cheaper and less of a headache than trying to hire a developer to handle the migration for you. So that’s it hope you like the video. If you would like to see more videos on marketing automation, migrations, third party integrations, things like this, please like the video below. So I know that you liked it. Subscribe if you would like to make sure that you catch those videos in the future and until then, see you later.