On February we allowed visitors on the website to sign up for a 14-day trial. At that time we decided to get rid of Hubspot because:
We needed something simpler that we could extend so decided to use airtable for a backend to power the forms on the website (demo, contact form, trial form).
All forms on the website are now submitted to an /api/form endpoint. The most common form on the website is the request a trial flow:
The good part about creating the license first (Prezly Admin API) is that we receive a customer_id and a user_id which can later be used to link sales data back to the original form submission. In a way, you could say that Airtable has the mapping information to link visitor sessions (anonymousId, userId) to Prezly database information.
Turns out Airtable is super friendly to anyone that wants to organise those form submissions in a way that is useful for them. Take this view for example:
Which is an overview of expired customers that have healthy activity.
Without going into details on how we did it (Lambda functions, Airtable automation and webhooks) here is the information we additionally store into the form submissions base:
It would be nice to get some of that information back into segment.com (and mixpanel) so I create additional reports (already did some in day 7) answering questions:
The first thing we do is add the handlers
Notice how I add a GET and POST endpoint? They are both doing the same thing but the GET endpoint is easier to use to debug/test and backfill the sales data for the last X months of trials.
The code to fetch data from airtable + fire identify calls is wrapped in a method trackUserSales.
I also added some variables to the .env files so make sure to have a look (or check out main) to make sure you have all config variables. Using a quick 'sls deploy' should publish that function.
To overwrite/fill in the history of our website data I have copied the list of all userIds from airtable. Other ways to find userIds:
That gave me a list of a few thousand userIds (I went with 3 months) which I stored in 'trial_userids.csv'. Using some bash I called the GET endpoint to backfill that data.
The code to trigger the calls (one call for every line in CSV):
> cat trials_alltime.csv| while read line; do https --print=b --ignore-stdin [LAMBDA_ENDPOIN]/prod/segment/identify/user/$line/sales; done
After I ran that command I could see that sales attribution data in mixpanel:
With the data now being backfilled, I need a way for this to automatically be triggered. I was planning to use Segment.com Personas > Journey for that with the following criteria:
That combination will make sure that we get pretty accurate reporting on new trials while the continuous sync (based on lastSyncedSales) will ensure that even older trials get refreshed every 30 days.
So I set this up using a Journey that looks like this
I am recycling the webhook from Day 10 which I modified to support the new steps (by key). The code now looks like this:
I set up a similar journey based on the lastSyncedSales property to ensure old trials are kept up to date at least once every month.
Tomorrow I will have a look at the different questions in this post, and if they can now be answered in Mixpanel just using this new data.