At its core, Magento BI is here to help you answer business questions - whether you simply want to see this month’s revenue compared to last year or understand your acquisition costs for your latest AdWords campaign.
What does that path from question to answer look like, exactly? We’re so happy you asked!
To help you visualize this process, we’ve mapped out that route below. This article will shed some light on both how we approach an analytical question, and the backend logistics required to get you the data you need.
Starting with the question
We know that you are constantly asking questions to improve your business, from increasing customer satisfaction to cutting supply costs. We will focus on how to translate your questions into analyses that help you drive decisions.
For our example, let’s assume that we want to answer the following question:
- “How fast do my new registrants convert?”
Identifying a measurement
With our question in hand, it is time to identify a list of possible analyses and measurements to help answer the question. For this example, let’s focus on the following metric:
- Average time from registration to first purchase date per user
This will reveal the average time that lapses between registration date and the users' first purchase date and give an idea on how users behave at this final step in the conversion funnel.
Finding the data
Understanding what to measure only gets us part of the way there. In order to assess the average time from registration to first purchase date per user, we need to identify all the data points that our measure is comprised of.
Let’s break down our measure into its core components: we need to know the count, or number, of people that registered; the count of people that made a purchase; and the time that elapsed between those two events.
At a higher level, we need to know where to find this data in the database, specifically:
- The table that records a row of data every time someone registers
- The table that records a data row every time someone makes a purchase
- The column that can be used to join or reference the `purchase` table to the `customer` table - this will allow us to know who made a purchase
At a more granular level, we need to identify the exact data fields that will be used for this analysis:
- The data table and column that contain a customer’s registration date: e.g. `user`.`created_at`
- The data table and column that contain a purchase date: e.g. `order`.`created_at`
Creating data columns for analysis
In addition to the native data columns outlined above, we will also need a set of calculated data fields to enable this analysis, including:
- Customer’s first purchase date which returns a specific user's MIN(`order`.`created_at`)
That will then be used to create:
- Time between a customer’s registration date and first purchase date which returns a specific user's time lapsed between registration and 1st purchase date. This will be the basis for our metric later.
Both of these fields need to be created at the user level (i.e. on the `user` table), so that the average analysis can be normalized by users (i.e. the denominator in this average calculation will be the count of users).
This is where Magento BI steps in! You can leverage your Magento BI data warehouse to create the above columns. Simply contact our analyst team and provide us with the specific definition of your new columns and we'll create them. You can also leverage our Column Editor.
It is a best practice to avoid creating these calculated data fields in your database directly as it puts an unnecessary burden on your production servers.
Creating the metric
Now that we have the required data fields for our analysis, it’s time to find or create the relevant metric to construct our analysis.
Here we know that, mathematically, we want to perform the following calculation:
- [SUM of `Time between a customer’s registration date and first purchase date`] / [Total number of customers that registered and purchased]
And we want to see this calculation plotted over time, or trending, according to a customer’s registration date. And here's how to create this metric in Magento BI:
- Go to Data and select the Metrics tab
- Click “Add New Metric” and select the `user` table - that’s where we created the dimensions above
- From the drop-down menu select Average on the `Time between a customer’s registration date and first purchase date` column in the `user` table ordered by the `Customer’s registration date` column
- Add any relevant filters or filter sets
This metric is now ready.
Creating the report
Now the fun begins.
With the new metric set up, we can use it to report on the average time between registration and first purchase date by registration date.
Simply go to any dashboard and create a new report using the metric created above.