Combine Product analytics with Subscription data - Part 2
Combine Product analytics with Subscription data - Part 2
Adding Dashboard Trees and behavioral analysis
Even after ten years of working in this field, one magic moment is always when it all comes together.
In this case, we have added new event data that shows up and modeled new events in our data warehouse, which is ready to be synced in our product analytics setup.
Now, you log into your product analytics tool, and everything is in front of you, just waiting for the insights to be unlocked. This is a magic moment but also sometimes a scary one. The obvious question is, “What should we do next with all this new data?”.
This post shows how I approach this as a first step before doing a deeper analysis.
This is part two of a series, and I recommend you start with part one to get the full picture of what we are building here.
This post, in comparison to former posts, is tool-specific. I want to show an end-to-end implementation that shows all the steps. Therefore, I need to show these steps in a product analytics tool. I currently collaborate closely with Mixpanel, especially on their Warehouse integration (since this has been my most important feature in product analytics in the last five years). Therefore, this implementation uses Mixpanel since I know all the steps best using this tool. But everything I do here can be implemented with any other product analytics tool (in slightly different ways).
Sync Data Warehouse data to Mixpanel
You might know Mixpanel classically. You used an SDK to send events directly into Mixpanel from a client or server environment. Since last year, we have had another option, which is even more powerful than the old way—a sync from the data warehouse.
There are two ways to sync the data into Mixpanel: a normal sync and the new mirror function.
The normal sync
The normal sync works like most reverse ETL products. You define a table with your event data or with user properties (more about the two a bit later), set this up in Mixpanel, and define how often you want to sync it:
What is important to know: This sync only works for new records. Usually, the updates are timestamp-based. So, the job looks for new event data based on the timestamps and loads them. This is no problem when the structure of your event does not need to be adapted for historical data. This means that when you introduce a property, it will only be available for new events.
Recommendation: Use one table per event. This enables you to introduce new events later (because you modeled them), and you can still sync the full history into Mixpanel.
The mirror sync
Mirror was introduced about five months ago. The big difference is the kind of data synced into Mixpanel. Instead of doing the sync based on the timestamp, Mirror checks for what kind of records have changed in the source table and then sync all these changes into Mixpanel. So, it is a bit like change data capture.
This enables you also to change event tables for all historical data. For example, you could introduce a new calculated property like account_num_orders that you want to use for all historical events. With the normal sync, this would only apply to the new events. But with Mirror, you could update all events.
And we can do even something more crazy - delete events or overwrite property values. Can you remember these data points where you have a peak in revenue? It was not because of your awesome marketing team but because someone released an issue with the revenue property. You can fix this now.
Mirror is still in beta. If you would like to test it, contact your Mixpanel Account manager.
What data can we sync?
Event data
All event data - when we have a timestamp, an identifier, and an event name- can be synced into Mixpanel even better when we have some property values. This also means you can sync historical event data from different tools like Segment or Google Analytics 4.
User properties
We can sync any user properties into Mixpanel. The only requirement is to have the user ID matching the Mixpanel one and a JSON field with the properties. This is an extremely powerful and easy way to enrich user profiles with data from additional sources like your production database, CRM, or customer support tool.
Lookup tables
We might have sensitive data in our data warehouse that we would never expose with a frontend SDK - like product margins in an online shop. Another use case for look-up tables is calculated metrics, like the number of users in an account.
Ad spent data
This is a powerful way to extend the existing campaign data with cost information for all marketing analyses. These can be obvious candidates like Google Ad, or Meta Ad spent, but they can also include costs like influencer costs or costs for SEO initiatives.
Combining all these cost items, you can support essential Marketing metrics like the Return on Ad Spent.
But Ad spent data already foreshadows what is possible beyond that. You can provide any aggregated data to Mixpanel that enables you to combine sequence based metrics with metrics calculated by aggregated data.
For our setup, we sync the events we have created before. Creating another user property table could also be interesting, but it is out of the scope of this post.
Creating a metrics dashboard in Mixpanel
Before I started working on this series, I talked with Abhi Sivasailam about metric trees. You can watch it here:
https://youtube.com/live/SLnvoOZ2vk0?feature=share
Naturally, Abhi is pretty inspirational, so before I got started with this project, I created this metric tree:
At the top, we have the classic MRR bridge (in a minimal version here). This can be built with the new Stripe data we sync into Mixpanel.
Below, we have the product usage data already collected in Mixpanel in this scenario.
As you can see, combining both data as event data enables us to calculate subscriptions at risk metrics based on the product usage data. This is where we want to get to.
But in step one, we want to create a dashboard tree.
A dashboard tree enables us to provide our company with a report that starts on a high-level, business-relevant view (The MRR) and then drills down the metrics tree to show the leading metrics to any input metric.
This is extremely helpful in review and planning meetings where you can perform these drill-downs to identify areas where the next work should be invested.
Conducting a Root Cause analysis in case of a metric performance change is also helpful. Ergest wrote a great article about that:
Our dashboard tree version 1 can look like this:
Let's get to work.
We build the new MRR first.
We use the "Subscription created" event to build this, which we are now syncing into Mixpanel. But for the MRR, we need to use the subscription_amount property as an aggregated SUM:
And we did the same for retained MRR and canceled MRR.
With that, we can build the Total MRR metric:
As you can see, here we use the formula function to combine the MRR metrics we have built before to calculate the total MRR.
Nice; with all this, we can build the first level of our dashboard tree:
With Mixpanel's Dashboard in Dashboard function, we can now build and link the next level:
Going one level down to the Accounts:
The dashboard tree is a great addition to our current setup, and it is great that we can now cover all metrics in Mixpanel. But we did not achieve something really new. We could have achieved a similar output by loading the Mixpanel into our Data Warehouse and combining it with the Stripe data. Then, do the dashboard tree in a BI tool.
So, we need to get a step further.
Do event analytics with product and subscription data.
As mentioned earlier, we combine both data sources in Mixpanel to analyze the sequences of accounts/users across product usage and subscription events.
We can start with a simple but powerful funnel. Because now we can actually map the whole customer journey and not just some parts.
Here, we have the first version of the customer journey funnel, starting at the board created and ending (so far) at the subscription created. As we can see, we have a serious monetarization problem (luckily, it is just a demo dataset). Over time, we can extend this to include subscription renewal or other user journey stages.
The next powerful insight is a report that we can prepare for the marketing team that checks the funnel performance based on the initial campaign that brought the user into our app:
Here, marketing can investigate which initial initiatives drive users into the application and have a higher chance of converting them into subscription accounts.
Since we have subscriptions, we don't want to lose them easily. Therefore, we want to create something that combines product and subscription data and immediately helps the customer success team.
We create a new cohort:
This cohort now includes all users with an active subscription but with no essential board activity in the last 60 days. These are our churn risk users. We need to get these to our customer success team:
For this, we use the cohort sync that I can use in Mixpanel to sync these users' emails, for example, to Braze, where our customer success team can create an outreach email campaign to learn more about what is missing for the users.
This brings us to the point where we can now analyze the customer journey and optimize the customer experience in the next step.
It might be just a small glance that I showed here, but the approach to bring all customer touchpoint data into one place and then use analytics to surface new patterns and use these insights to enable different communication and experience directly is the essence of the new way to do analytics.
In the comments, let me know what kind of follow-up questions you would like to get covered in a future post about this topic.
This post is sponsored by Mixpanel. For me, the simple reason is that I use Mixpanel's Data Warehouse sync in most of my projects. As described above, I want to combine data warehouse data with product data (and I even now sync the product events from the data warehouse), but I like my usual analyst experience. This is the reason why I use this combination so often.
You can learn more here (https://link.timodechau.com/mixpanel-s)
