Magento BI helps you answer your 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.

But what does that path from question to answer look like, exactly? We’re so happy you asked! We’ve mapped out that route below, shedding light on both how we approach a business intelligence question and some of the backend logistics required to get you the data you need.

Question

Business leaders are constantly asking questions to help grow and improve their business, from improving customer satisfaction to cutting supply costs.

Let’s say we want to answer the question “What is my company’s average time to conversion?”

Measurements

With our question in hand, it is time to identify a list of possible measurements to help answer our question. For this example, let’s focus on the following measure:

Average time from registration to first purchase date per user

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. Let’s locate:

1. The table that records a row of data every time someone registers
2. The table that records a data row every time someone makes a purchase
3. 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. MIN(`order`.`created_at`)

In addition to the native data columns outlined above, we will also need a set of calculated data fields to enable this analysis: 

Customer’s first purchase date

That will then be used to create:

Time between a customer’s registration date and first purchase date 

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).

You can leverage your Magento BI data warehouse to create the above metrics. 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.

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`

------------------------------------------------------------------------------------

Count 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. To build this metric in Magento BI:

• Go to Manage Data and select the Metrics tab
• Click Create 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 to report on the average time between registration and first purchase date by registration date.

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.