Helping ChartMogul Understand Your Unorthodox Stripe Trial Data
At Cast we’ve been using ChartMogul to get a better handle on our customer and business data (despite my well-known apostasy towards the Gospel of the Funnel™). Because Cast uses Stripe for our payment processing, setting up ChartMogul was as simple as authorizing it with Stripe and waiting while it imported and processed our historical data.
The Problem
Once the data import was complete, ChartMogul was immediately populated, and it was full of interesting insights into our data — with one exception: trial and free-to-paid customer conversion was completely blank. I had a hunch about why we were missing this: Stripe is set up to support free trials on paid plans out of the box, however for a bunch of reasons we opted to make Cast’s trial a completely separate plan. When a customer signs up for the Cast trial, we put them on the trial plan at $0/mo and set it to expire after a month.
Unfortunately, ChartMogul expects trial data on Stripe to be in the form of a trial period on a paid plan. That said, you can manually set a trial start date on any customer in ChartMogul (using the free_trial_started_at field on each customer) to resolve this — going forward that’s easy to automate, but not so much for our historical data.
Solution 1: Write Some Code (probably don’t do this)
Everything looks like a nail to me, so I started taking a look at the ChartMogul API, and, unsurprisingly, they have an article about how to use their API to manually track leads and free trials. I started speccing out a script to churn through our customer data on Stripe, pull the relevant info on each customer, and add it to each customer object on ChartMogul. Querying Stripe for customer.subscription.created events seemed like a great way to pull just this data, but I quickly ran into a roadblock: Stripe’s events search only goes back 30 days.
I took this as a sign of bad idea smell and went back to the drawing board.
Solution 2: Bulk Data Import with Google Sheets
ChartMogul includes a bunch of different ways to get your data into the platform, including Google Sheets, which got me wondering whether there’s a way to export just the data I’m looking for from Stripe in one go. After a quick chat with someone on the Stripe team, we had a potential way to do this.
On the Stripe Dashboard’s Subscriptions page, selecting our Trial plan only filtered for people currently in Cast’s trial period, which doesn’t help us with historical data. The trick here is to also filter for Status “is any of the above”, which will include both current and former Cast trial customers:
Once we learned that, we were able to easily export a CSV file from Stripe of all customers who signed up for the Cast trial plan, their sign-up date, and a bunch of additional (extraneous) data.
With that in hand, I went looking to see whether ChartMogul’s Google Sheets support covered what we were trying to do here, or whether it would be back into the API mines to process this CSV file.
Fortunately, I was in luck: ChartMogul has a helpful article on how to use Google Sheets to import customer attributes for existing customers, and in our case we wanted to import data into the free_trial_started_at field on each customer. That was the final piece of the puzzle. We removed all of the columns from our Stripe CSV file except for Customer Email and Created (UTC) and we were all set.
Some Wrinkles to Watch Out For
ChartMogul’s Google Sheets add-on seems to wrap up an import script that runs row-by-row on your sheet, and in my testing it takes a couple of seconds per row. Buckle up for a long sitting if you have a large customer list to import. (You can do the math, but we’re talking hours to days depending on how large your customer list is.) Stopping and restarting the process seems to simply restart the importer at the top of your sheet, but the importer marks rows that it’s imported in green (or red if they’ve failed), so it’s easy to delete previously-imported rows before restarting the import process.
There are also a few bugs around input validation with ChartMogul’s Google Sheets add-on itself. ChartMogul’s API assumes that dates without a timezone are UTC, and while Stripe’s data (which is exported in UTC with no indicated timezone) conforms to this, the Google Sheets add-on seems to treat these dates as your local time for the purposes of validation, so it rejects times that it (wrongly) believes are in the future. In my timezone ( EST), that means it rejects any data from the last 5 hours. Once the data gets into ChartMogul, however, the timezone interpretation is correct.
Further to that, ChartMogul’s Google Sheets add-on doesn’t seem to properly validate email addresses with a + character in them, which are fairly common for people who want to differentiate signups for different services. We’ve raised a bug report with ChartMogul about the above issues.
Finally, Google Sheets itself seems to choke if you paste too much data at once, so we had to segment our customer data into groups of a few thousand rows at a time, and import it into the sheet bit by bit. (Importing it all at once appears to work at first, but Google Sheets quickly pops up an error stating that your permissions to the sheet have changed, and it reloads without your saved data.)
Up and To the Right… Right? 📈
Those glitches aside, the import process was easy and quite painless, and our customer data on ChartMogul is now properly populated with the missing trial info. Now I just have to figure out what kind of funnel I’m supposed to be buying, and what all the fuss is about…
I’m on Twitter at @JulianLepinski, and when I’m not writing this article I’m working on Cast — a product that makes it easy and fun to record, edit and publish a podcast.
