Sample Report 25. Customer based basket analysis

Report Description

This is one of the most advanced examples amoung sample reports. It consists of 4 pages and allows to understand how customers tend to form their combination of products they put in one busket (one order).

First page of the report allows to select a product that has the highest amount of related products: other products, bought by same customers.

Embedded interactive report

Open this report in Power BI

Report Content

First page of the report shows list of products with various filters, that could be used to limit the set of products to those that we want to analyze.

List of products has such measures as "Customers Count" and "Same Customers Products Count". One will show how much customers purchased the product. second - how much of other products those customers bought.

It allows to concentrate the further analysis on the products that are often bought together with other products.

Basket analysis report - first page

This page of the report includes:

Filters:

  • Website and store filters.
  • Product name search field allows to search product by entering its name (or part of its name).
  • SKU search filed allows to search by entering SKU number or its part.
  • List of categories where one or multiple categories could be selected for filtering the products list.
  • Price range filter will filter the list of products by price.

Table with products lists available products with some sales metrics and measures "Customers Count" and "Same Customers Products Count" explained before.

In order to continue the analysis, a product should be selected in the list. Righ-click and selection of Drillthrough -> Single Product Analysis will navigate user to the second page of the report.

Second page will show the data in relation to the selected product.

Basket analysis report - second page

This page has list of products that were purchased by the customers who purchased the analyzed product.

KPIs:

  • customers count - amount of customers who purchased the analyzed product
  • product revenue - total revenue, generated by the analyzed product
  • product customers total revenue - total revenue, generated by those customers who purchased the analyzed product

Relation between the KPIs may help to understand, how big impact the analyzed product makes on the revenue from the customers.

Table with products lists products purchased by the same customers who bought the analyzed product. The measures help to identify the most important products from that list:

  • Same Customer Related Product Row Total - shows the revenue from the product from customers who also bought the analyzed product;
  • Related Product Row Total Gross - total revenue that the product is generated;
  • Common Customers - amount of customers who bought both products: analyzed product and the product listed.

Relation between measues in the table may help to estimate, how significant the relation between two products was for the revenue.

To continue the analysis, user may select a single product from the related products list. This will form a pair of products for further analysis. To select related product, user needs to right-click on one of the rows and select Drillthrough -> Single Product Pair Analysis.

Third page of the report is dedicated for single products pair analysis.

Basket analysis report - third page

Main KPIs on this page:

  • Common Customers - amount of customers who boght both products;
  • Common Orders - amount of orders where both products were bought together;
  • Total Cross Sale Potential - the total potential of cross sale of products, calculated as revenue that will be received if all customers who bought one product will also buy another.

Rest of the KPIs are presented row by row for first analyzed product and for selected related product:

  • Product ID
  • Average Price incl Tax
  • Ordered Row Total - revenue from the product
  • Customers - amount of customers who bought the product
  • Average Qty Ordered - average qty of products ordered in single order
  • Cross sale potential for each product - the potential revenue that could be received if the product will be purchased by all customers who bought another product in analysis.

The table lists customers and their orders where the two products appear. Here user may select a particular customer from the list and switch to the next page of the report by doing right-click on a row in the table and selecting Drillthrough -> Single Customer View.

The last page of the report shows the orders, done by single customer.

Basket analysis report - forth page

This page includes:

  • KPIs: customer ID, orders count, grand total, total invoiced, total refunded, products ordered - all in relation to the selected customer.
  • Table with orders and products in those orders.

Please, note that the page does not list private information about the customer to stay compliant with possible privacy rules.

How it works?

This report is created based on data from Magento e-commerce platform. Any online store build with usage of Magento (1-st or 2-nd version) can have this report installed with Power BI integration extension for Magento that enables business intelligence functionality in Magento store.

The report is hosted by Power BI Service from Microsoft. It is connected to a dataset, created and published to Power BI Service by Power BI Sales Cube extension installed on Magento e-commerce platform.

The report automatically updates its data every hour. it is based on demo magento data where several new orders are generated every day.

The process of data update involves:

  • incremental latest data extraction
  • incremental update of sales cube data warehouse structure
  • incremental loading of latest data to Power BI via REST API

Such report may be accessible by different Power BI users of the same organization. Also, users may access it using Power BI Mobile App on Android, iOS or Windows Phone.

How to get this report for your Magento store?

The report is based on Sales Cube dataset provided with Power BI Sales Cube extension for Magento. After installing the extension, user may create and configure dataset with Magento data that will be avialable in Power BI Service. The extension comes with a report template for this report.

Easily edit or create more reports

This report is intended to provide out of the box functionlity together with Power BI Integration extension. And even though, this report could already bring value to the business, it is just an example of what could be created based on Sales Cube dataset. It could easily be edited and adjusted to comany needs.

From simple reporting to more advanced analytics, integration with Power BI opens lot of possibilities for Magento merchants.

Check out more examples of reports and read about Power BI Intergration with Magento platform to get more information on how this and other reports can help your business.