Using Swrve and Chartio to Visualize Your Userbase
Swrve customers know the power of our dashboards for understanding what their users are doing and supporting marketing activity on that basis.
Our product is about running campaigns to influence your users behavior inside your application, but sometimes you want to get down and dirty with your data and get a handle on what your users really look like. With our newly released UserDBs feature you can do just that - with the data display tool of your choice.
I’m going to show you how it’s done, using Chartio - a nice little product that allows for the fast and easy creation of charts. It automatically creates measures and dimensions by looking at the schema of your database and has an easy to use drag-and-drop UI that lets you get up-and-running quickly.
Let’s look at how easy it is to visualize all the data that Swrve collects from your user devices, plus any customer data (which is almost infinite in scope and variety) in a product like Chartio.
Uploading UserDBs
To make this happen we need to download the Swrve data into a datastore that Chartio can easily deal with such as a hosted mySQL or Amazon Redshift instance. The process for uploading this data to a mysql database is documented here. I used the manual process for downloading this data and uploaded the data to a hosted mySQL. I used my favorite mySQL client (Sequel Pro) for this.
This involved:
1. Opening the User DB Downloads Reports in the Swrve Dashboard:
2. Downloading the mySQL scheme for my Swrve App, creating a new database, and executing the mySQL scheme for my app:
3. Downloading the 6 data files for my app, and uploading those data files to the host mySQL database:
Running Queries using Chartio
Once I had this data uploaded to the hosted mySQL database, I connected to Chartio using the same username and password I used for uploading the data and started to explore the data.
When customers are getting started with this type of data exploration, they have often started by looking at revenue cohorted to the individual user’s install. In this case, I created a chart looking at the average spend of our users in USD, cohorted to the install week for those users by selecting, spend from the swrve_properties table as both a measure and a dimension in the Chartio dashboard. This gives a table of weekly average spends per user.
A more advanced version of this chart is to start to look at this data cross-referenced with the acquisition channel of the users. For this, I created a chart using the spend property from the swrve_properties table as a measure and Swrve.referrer_id also from the swrve_properties table as a dimension.
Joining Data
Of course, much more sophisticated usages extending on this are possible too. For example, I was interested in the percentage of users starting our tutorial cohorted to the install week of those users.
To do this, I set up foreign keys between two of our data tables using the chartio foreign key UI, marking the swrve_data_id in the swrve_properties table as a foreign key for the swrve_data_id column of the events_fired table.
With this I generated a chart showing me the average number of users starting our tutorial using the custom event Tutorial.Start from the events_fired table as a measure and date_joined from the swrve_properties table as a dimension. This gave me the graph you see below, showing the effectiveness of our Tutorial.Start over the last year.
