Sales Reports Specifications

Last modified by Anton Makarenko on Sat, June 5, 2010 04:59
Source|Old Revisions  
This document is valid for Magento 1.4.1.0 or later.

General sales report requirements:

  • Period filter — definition of per what period to build report: per day, week, month, year. Required.
  • Date Range filter (date) — date limitations of report. Required. Entered and stored as date in the timezone of admin. If admin changes timezone, the lifetime report should be re-aggregated.
  • Order Status filter — order status limitations of report. Optional. By default report is not filtered by status.
  • Sales reports should not match orders that have no items processed or all items canceled, unless there is different logic defined.
  • Empty rows for selected date period may be omitted (by default). If not omitted, each period of the specified date range should appear in the report grid.
  • Sales reports may have “projected” and “actual” values. Projected is the value of the estimated amounts to be paid. If the report supports it, it should show filter option Show Actual Values: yes, no (no)
  • Totals row — row of sums per each aggregated column of current displayed report
  • Subtotal rows — rows of sums per each aggregated column, when there are 2 or more grouping columns

Use case for totals:

Period Qty Total Paid Total Shipped
...
Total $1000.00 $500.00

Use Case for subtotals:

Period Qty Tax Rate Total Paid Total Shipped
...
07/2009 13 20.00% $500.00 $500.00
2 8.50% $250.00 $200.00
Subtotal $750.00 $700.00
08/2009 8 20.00% $360.00 $150.00
11 8.50% $820.00 $530.00
Subtotal $1180.00 $680.00
Total 34 $1930.00 $1380.00

Sales Report Currency

All monetary amounts in the sales report are displayed in the base currency specified in the global configuration scope. The reason for it is that the amounts are aggregated, and they can be aggregated from different websites, while a website may have its own base currency. In order to make the aggregated values comparable, they are casted to the “global base currency” by the “base to global rate”, defined in the each order separately.

In example, the global base currency is USD and the website base currency is EUR:

Order Amount in Base Rate to Global Amount in Global
100001 EUR 150.00 1.3245 USD 198.6750
100002 EUR 108.30 1.5112 USD 163.6630
Total USD 362.34

Sales Report Types

Orders Report

Orders aggregation per periods. Period may match to:

  • Order creation date (default)
  • Order update date

Columns:

  • “Period” (grouped)
  • “Orders” — qty of orders that are actually matched in the report
  • “Sales Items” — qty of the matched order items ordered minus canceled qty (total qty ordered - canceled qty)
  • “Items”* — qty of the matched order order items processed (total qty invoiced)
  • “Sales Total” — the projected income: grand total amount minus total canceled amount (grand total amount - canceled total amount)
  • “Revenue”* — the actual income: total paid amount minus total refunded amount (total paid amount - total refunded amount)
  • “Profit”* — Revenue minus merchant expenses: tax (invoiced), shipping (invoiced), costs (invoiced). (profit can have negative value)
  • “Invoiced” — order invoiced amount (total invoiced amount)
  • “Paid”* — order paid amount (total paid amount)
  • “Refunded” — order refunded amount (total refunded amount)
  • “Sales Tax” — tax part of the sales total (tax total amount - tax total canceled amount)
  • “Tax”* — tax part of the revenue (tax total invoiced - tax total refunded)
  • “Sales Shipping” — shipping part of the Sales Total (shipping total amount - shipping total canceled)
  • “Shipping”* — shipping part of the Revenue (shipping total invoiced - shipping total refunded)
  • “Sales Discount” — order discount amount (discount amount - discount canceled)
  • “Discount”* — actual discount amount (discount invoiced - discount refunded)
  • “Canceled” — the order canceled items amount (canceled total amount)

* — values treated as “actual”, should appear only when “actual” filter is set to Yes.

Test case for sales report totals: when an order is completely processed and without canceled items, the “Total” will be equal to the “Revenue”.

Tax Report

Order tax totals with grouping by tax rates. Period may match to:

  • Order creation date (default)
  • Order update date

Columns:

  • “Period” (grouped)
  • “Tax Rate” — the tax rate name (grouped)
  • “Rate” — the actual tax rate in percents (grouped)
  • “Number of Orders” — the matched orders qty
  • “Sales Tax” — tax total amount - tax total canceled amount TODO
  • “Tax” — tax total invoiced - tax total refunded TODO
  • “Tax” — total order tax (”base_real_amount”, does not take into account canceled tax)

Invoiced Report

Order invoiced/paid totals. Period may match to:

  • Order creation date (default)
  • Last invoice creation date (meaning that only orders with invoices will be reflected)

Columns:

  • “Period” (grouped)
  • “Orders” — qty of all orders for this period that are processed :?:
  • “Invoiced Orders” — qty of orders for this period that have invoiced amount
  • “Invoiced” — invoiced total amount
  • “Paid” — paid total amount
  • “Invoiced not Paid” — Invoiced minus Paid

Shipping Report

Order shipped totals grouped by carriers. Period may match to:

  • Order creation date (default)
  • First invoice creation date (meaning that only orders with invoices will be reflected)

Note: invoice can be zero (fully covered by gift card etc); shipment can be zero (free shipping) — any kind of such shipments should be reflected.

Columns:

  • “Period” (grouped)
  • “Carrier/Method” — the shipping method code, reflected as the current shipping method name in configuration
  • “Orders” — qty of orders with shipping invoiced
  • “Sales Shipping” — shipping total amount - shipping canceled amount
  • “Shipping” — shipping invoiced total amount - shipping refunded total amount

Refunds Report

Order refunded totals. Period may match to:

  • Order creation date (default)
  • Last credit memo creation date (meaning that only orders with credit memos will be reflected)
  • “Period” (grouped)
  • “Refunded Orders” — qty of orders for this period that have refunded amount
  • “Refunded” — refunded total amount
  • “Online Refunded” — online refunded total amount
  • “Offline Refunded” — offline refunded total amount

Coupons Report

Order discount aggregation per periods and coupons. Period may match to:

  • Order creation date (default)
  • Order update date

This report matches all orders, regardless of whether they were processed/canceled, because the coupon usage is incremented as soon as an order is placed.

Columns:

  • “Period” (geouped)
  • “Coupon Code” — discount coupon code (grouped)
  • “Number of Uses” — qty of the matched orders (this coupon was used in)
  • “Sales Subtotal” — subtotal total amount - subtotal canceled amount
  • “Sales Discount” — discount total amount - discount canceled total amount
  • “Sales Total” — Sales Subtotal - Sales Discount
  • “Subtotal”* — subtotal invoiced total amount - subtotal refunded total amount
  • “Discount”* — discount invoiced total amount - discount refunded total amount
  • “Total”* — Subtotal - Discount

* — values treated as “actual”, should appear only when “actual” filter is set to Yes.

Sales Report in Admin Dashboard

This section is valid for later Magento versions, ETA is not known yet.

The “Lifetime Sales” should utilize the same collection that “Sales/Orders” (order creation date) report and render the following lifetime aggregated information (numbers and currency are provided as examples):

Sales Actual
Total $1000 $500
Tax $10 $5
Shipping $50 $25
Items $150 $75
Orders 10

Where values are sales (projected) and actual values — same as the columns in sales order report, but without grouping by period (where “Actual Total” is the Revenue from the sales order report).

“Average Orders” — same as “Lifetime Sales”, but instead of SUM() the AVG() aggregation function should be used

Sales graph — graphical representation of the “Sales/Orders” report. Total number should be displayed as X (Y) where X — projected and Y — actual values respectively. The following totals should be displayed (currency is in USD for example):

  • Total — $X ($Y) Where X = sales total, Y = revenue
  • Tax — $X ($Y) Where X = sales tax, Y = actual tax revenue part
  • Shipping — $X ($Y) Where X = sales shipping, Y = actual shipping revenue part
  • Qty — X (Y) Where X = sales items qty, Y = processed items qty

Graph report period presets:

  • Last 24 hrs. Period = hour.
  • Last 7 days. Period = day or hour if it is ⇐ 24 hrs.
  • Current month — starting from first day of current month, up to date. Period = day or hour if it is ⇐ 24 hrs.
  • YTD — “year to date” — starting from first day of current year, up to date. Period = month, or day of it is ⇐ 1 month, or hour if ⇐ 24 hrs.
  • 2YTD — “2 years to date”...



 

Magento 2 GitHub Repository

Magento Job Board - Some sort of tag line goes here

Latest Posts| View all Jobs