How to build custom reports in Magento 2

Reporting is an essential part of any ecommerce business. As your business grows, the need for timely and high-quality reports only increases.

The concept of "Data Driven Decisions" has gained popularity in ecommerce from larger businesses, where the understanding the data is key to increasing efficiency and sales has been long established.

Making decisions based on data allows you, at the very least, to keep up with general trends and, in the long run, gain a significant advantage over your competitors. Moreover, the data necessary for most operational and tactical decisions is already available directly in your store's database, various analytics platforms, and advertising services.

With the Advanced Reports extension, you can use over 20 built-in reports, create custom reports for magento 2, export data in a convenient format, share reports with colleagues via direct links, and even set up automatic integration with Google Sheets.

Is Custom Reporting Possible in Magento 2?

Looking at the built-in Magento 2 reports, you can see many reports, but unfortunately, all reports are fixed and cannot be changed except for date filtering. This significantly limits their use, although they do cover the basic needs of ecommerce business owners.

In general, there are several typical approaches to building custom reports on Magento 2 data:

• Building reports by executing direct SQL queries to the database. Most entry-level to intermediate-level developers can write an SQL query that aggregates all the necessary information and displays it in a table in the admin panel. However, this approach requires developer time and does not provide the ability to interact with reports (sorting, filtering, exporting).

• Using third-party solutions for report building. Modern programs like Microsoft Power BI or Explaratory provide comprehensive solutions for building client reports of any complexity. However, they require deep knowledge of data relationships, programming languages, and are mostly designed for professional users.

• Using Magento extensions specifically designed for report building. For Magento 2, there are several extensions that implement the ability to build custom reports with varying degrees of quality.

Typically, it's the Magento extensions that serve as a base on which you can build your own reports. A fully functional solution, such as Mirasvit Advanced Reports, provides capabilities like:

• Creating, saving, and editing reports;
• Column management (Drag&Drop);
• Filtering reports based on any available fields;
• Export to various formats and integration with Google Sheets;
• Defining 300+ default fields covering key entities;
• Creating new columns or fields with custom calculation formulas;
• Connecting new tables (e.g., third-party extension tables);
• Additional configurations for Magento Cloud and Magento Commerce.

Combining these capabilities allows Magento 2 store owners to analyze specific sales metrics, customer behavior, evaluate inventory, optimize delivery, and cover both operational, tactical and strategic needs. This makes Advanced Reports an indispensable tool for gaining a comprehensive overview of your business activities.

Popular types of Custom reports

Delivery Reports

Delivery reports provide detailed information about the progress of order delivery. They are crucial for monitoring and managing the entire delivery process, from order placement to final customer delivery.

This group of reports can be divided into operational and analytical reports. Operational reports include information about orders awaiting delivery, delivered orders, and more. Analytical reports cover metrics such as average order time, refusal or return rates, deliveries by country or city, and so on.

When additional modules are used to select the date and time of delivery (on the checkout page), reports allow for a one-click retrieval of a list of orders to be processed today or tomorrow. Additionally, the report includes delivery addresses, products, and other necessary information.

Customer Segmentation

Every customer is a valuable asset, but they differ in terms of income, order frequency, and more. To allocate marketing resources more efficiently, customer segmentation based on parameters is often used. By adapting efforts to the specific needs and preferences of identified customer groups, it is possible to increase key metrics such as CLV (customer lifetime value) and AOV (average order value).

To identify different customer groups, an embedded customer report with additional fields such as Customer Lifetime Value (CLV), Number of orders, and Average Order Value can be utilized.

An extension of this approach is RFM analysis (Recency, Frequency, and Monetary), which significantly enhances the accuracy of customer segmentation. The RFM report evaluates the customer's last purchase date (R), order placement frequency (F), and total order amount (M).

By combining the values of these three RFM numbers, a summarizing class can be assigned to each customer, allowing for tailored marketing strategies for each segment. This helps optimize marketing efforts and maximize overall efficiency.

Financial Reports

Financial reports provide objective information about the business's financial activities, enabling informed decision-making regarding enterprise management.

Financial reports often include information such as Net Revenue, Net Profit, and Margin. Additional columns may cover metrics like Return on Investment (ROI), Customer Acquisition Cost (CAC), Average Transaction Value (ATV), and more.

RMA Reports

Return Merchandise Authorization (RMA) reports offer summarized information about return reasons and product conditions. Typically, RMA reports help identify recurring issues, such as product quality, description inaccuracies, delivery problems, and more. Determining products that are returned most frequently aids in better inventory management, helping avoid low-quality products.

RMA reports may include the following data columns:

• Return Date
• Time to Return
• Order Details
• Reason for Return
• Condition of the Item
• Quantity
• Customer Information

It's worth noting that by default, the Advanced Reports module already includes configuration for many RMA modules, including the native Magento RMA extension.

Inventory Reports

Inventory reports provide detailed information about the quantity, movement, and value of products available in store warehouses. These reports help identify the need for timely replenishment of inventory, avoid excessive stock on shelves, and optimize the financial resources of the store. They can also be used to forecast demand and ensure the efficient operation of the supply chain.

Inventory reports often include data such as Quantity, Cost, Location, Demand, Minimum Stock Indicator (MSI), and Reorder point.

Abandoned Cart Reports

Abandoned Carts Reports offer information about missed opportunities to sell products. This report analyzes the reasons and frequency of customer refusal to complete purchases, contributing to the improvement of marketing strategies and customer conversion from potential to actual buyers.

The Abandoned Carts Report typically includes: Customer ID, Abandonment Time, Cart Contents, Cart Value, and Promotions/Discounts offered.

How to build custom reports in Magento 2

The Advanced Reports extension allows the creation of custom reports by duplicating existing reports and configuring them to specific requirements. In general, the setup involves selecting columns for display, defining basic and additional filters, and sorting results. After this configuration, the report is available for regular use and does not require constant adjustments.

A separate task is defining custom columns and, if necessary, connecting additional tables. While the module already defines numerous columns with typical calculations, custom columns are useful when using unique formulas or non-typical data combinations.

Almost any value stored in the Magento database can be obtained in a custom report by combining custom tables and fields. The Config Builder interface allows adding necessary field and table definitions directly through the admin interface. This involves a combination of XML markup and SQL queries.

Note that Config Builder is only needed for defining new fields or tables. The actual construction of custom reports occurs entirely through the graphical interface of Report Builder using Drag&Drop. For more detailed examples of configuration, you can refer to the documentation or directly view sample reports in the demo version.

Recently implemented custom reports for Magento 2

Creating custom reports with various columns is a key feature of the Advanced Reports extension. Daily, hundreds of our clients create new reports either independently or with our team's support (it's a free service).

Here are a few examples of recently created custom reports:

• Linking orders with subscription data. This custom report displays created orders from the store's customers simultaneously with subscription data for a product identified by its SKU. The data is retrieved from the "subscription_profile" table, which is part of a third-party Recurring Payment Magento extension.
• Conversion report. Over a specific period, it shows the ratio of the number of orders to the number of abandoned carts.
• Product pricing report with a link to configured products. In this report, the store obtains information about prices for simple products that are part of each configured product.
• Channel report indicating the channel through which a customer passed. This report is built based on a custom field added to checkout (with values stored in the database as JSON). The report allows the store to group orders by channels and determine which ones are the most effective.

Final Thoughts

Full utilization of available data will help optimize business operations, enhance customer satisfaction, and make informed decisions that contribute to the overall success of your store.

Built-in reports often do not cover all business needs, and in such cases, the ability to create custom reports becomes crucial. Using custom reports with non-typical fields or calculations allows a Magento store to obtain accurate metrics for data-driven decision-making. On the other hand, it helps improve various aspects of operational activities.