Overview
The real power and benefit from our Insights module comes from providing our customers with the ability to create their own custom reports.
This functionality is only available with our Pro Plan or above so if you wish to upgrade, then please raise a support ticket or contact our sales team.
How-To
Once you have subscribed to a Pro, Pro+ or Enterprise plan you will be issued a dedicated Insights Portal URL and login. This portal is powered by Metabase, which is a very powerful reporting front end tool. Have a read of our Metabase article to understand more about the underlying software that is powering your Insights Portal.
Understanding your Reporting Database
In order to make the most from your data you need to understand what and how data is stored.
You can find your Reporting Database by logging into your Metabase instance and navigating to Our Data > Your platform name
Click on the database and then select 'Reporting' from the list of folders.
You're then presented with your reporting database. You can see each of the tables that are available and you can click on any of them to view the data stored within.
These are the tables that you will then be using to build your custom reports.
For example, in the screenshot above you will see tables called 'order' and 'product' and these are where most of the order and product information is stored respectively.
Have a watch of our Quick Start Guide video below to get you up to speed:
Creating a Simple Question (Report)
Now you have had a look at how the Database is laid out and the data that is at your disposal, we will look at how you can use this data to create your own Custom Reports.
In Metabase this is called a Question and they offer three different methods of creating a question - through their simple question UI, Custom question UI or Native query editor.
We're going to focus on how to build a Simple question which is, as the name suggests, the simplest way and requires very little technical knowledge to do so.
First click on the 'Ask a question' button in the top right of your Metabase page.
Then you you will be presented with the three options for creating a question as we mentioned above - select 'Simple question.' Now select your database and the table you want to create your question on.
Check out this tutorial video by Metabase, which talks you through the steps of creating your custom reports or watch our video below:
Filtering your custom reports by Storefront ID
It is a common requirement for a report to be available across all storefronts but to be filtered by the storefront ID automatically so that each storefront admin will only be downloading their storefronts data.
Rather than having to create a report for each storefront individually and hardcoding the storefront filter into each (this would be very time consuming and unmanageable if you have lots of storefronts and lots of reports) we allow for this to be done dynamically.
To achieve this you simply need to create a custom column called 'storefront_id' as this is a reserved column name in Infigo that will inject the storefront Id into when accessing it from different storefronts.
If you are creating the report using the 'Custom Queston' builder then once you select your tables you need to click the 'add custom column' button
Then from the list of available data you need to select storefront_id.
And give it the name 'storefront_id'
Filtering your native SQL reports by Storefront ID
To achieve the same thing, if you have Insights Pro+, and are using the native SQL report building then you can do this by using the following placeholders in your SQL statement:
{{storefrontId}}
An example of this in a SQL report might look like this:
DECLARE @accountId as INT;
[[ SET @accountId = {{storefrontId}}; ]]
SELECT * FROM [reporting].[customer]
NOTE: All dates used in any reports, dashboards or subscriptions are in UTC
0 Comments