Child pages
  • Advanced Reports
Skip to end of metadata
Go to start of metadata
Icon

Having hard time finding an answer to your question?

Check out our Knowledge Base.

Extension page: http://ecommerce.aheadworks.com/magento-extensions/advanced-reports.html


Improve the functionality of native Magento reports and create a complete picture of your business situation with Advanced Reports!

Perfect reporting solution with 13 customizable report types, which can be further expanded with additional units.

Installation

1. Backup your web directory and store database.

 Click to view details

You can make backup copies with any tool you find appropriate

If you are going to use the native Magento backup function, navigate to System -> Tools -> Backups and perform System and Database backups

 2. Log in to the Magento backend

 3. Disable compilation

 Click to view details

Navigate to System -> Tools -> Compilation.

If Compiler status is Disabled, you can skip to the next step

If Compiler is enabled, disable it.

IMPORTANT: after the extension is installed, you can enable the compilation again; IT IS CRUCIAL that you use "Run Compilation Process" function, not just "Enable button"

Icon

Installing an extension with the Compilation enabled will result in store downtime.

 4. Flush store cache

 Click to view details

You can flush the store cache in 2 ways:

  • via the backend:

Navigate to System -> Cache Management menu, and click Flush Magento Cache button

  • via filesystem:

On your server, navigate to Magento root folder, then proceed to /var/cache/; delete all the content there.

 5. Download the extension package from your account and extract the downloaded archive

 6. Copy the content of /Step_1/ folder to your store's root directory

 7. Copy the content of /Step_2/ folder to your store's root directory

 8. Flush store cache again; log out from the backend and log in again.

 

Set up cron

In order to automatically generated reports the extension requires to be launched with cron. If you have already configured cron jobs for your Magento installation then you can skip this step. Read the following instructions on setting cron job for your Magento store: Magento user guide

Generally it would be enough to run in SSH console of your server:

 

 

And insert the following line:  

 

 

Don't forget to confirm saving request when exit.

Once cronjob is set up and running, the extension will be saving every action in the log, which can be viewed in System->Configuration->aheadWorks Extensions->Info->aheadWorks Extensions logging->View log. If you think that the extension does not work as expected, it is recommended to check the log first. If it is empty or contains very few records, it may indicate that your cronjob is not set or set improperly. Normally, every cron launch should add 1 or more entries. Actual quantity of the entries depends on the number of processed events per given cronjob.


 

General configuration

The extension's general settings are located under the System -> Configuration -> aheadWorks Extensions -> Advanced Reports backend page. 

Charts options section

Charts Options section allows defining the charts parameters (lines color, font color and size, background color, and the chart height) or disabling the charts at the report pages.

If enabled the charts are generated automatically and can be found atop the report's table.

Configuration section

This section defines the general reports settings and allows adjusting default report look.

  • Process Orders - defines what orders statuses the extension will take into account when generating reports;
  • Select Orders By - defines if orders should be included in the Date Range by the Created At or Updated At date;
  • Save date range when navigating between reports and Number of latest custom date ranges - these two options control how the module saves the custom report periods for quick access;
  • Include taxes in profit calculation - defines if taxes should be included in the profit calculation.

Scheduled Email Reports section

This section allows enabling and configuring automatic reports generation.

How does it work?

Icon

Since 2.6.0 version, the extension allows the reports to be automatically generated and sent to the specified recipient(s) via email. Providing the functionality is enabled, it is also possible to save the copies of auto-generated reports to your server.

Note, cronjob required to be set up and running to send the emails with the reports generated automatically.

  • Reports to Export - allows selecting the report types to be exported and emailed (except Sales by Product, Sales by Product Attributes, Sales by Category); leave empty to disable;
  • Email Recipients - defines the list of the recipients' email addresses. Multiple addresses are allowed. Leaving this field empty will disable the auto-generation functionality;
  • Period - defines the time period covered by an auto-generated reports;
  • Group By - defines minimal period unit in the report table;
  • Frequency - defines the reports generation frequency;
  • File Format - defines the output file format: CSV or XML;
  • At (time) - defines the time of reports generation;
  • Copy all sent reports to this folder - defines the server folder for saving the reports generated (the folder is Magento root folder related); leave empty to disable the reports backup;
  • Email Sender - defines email sender;
  • Email Template - defines the report email template.
Icon

Note, Period option is related to the sending date.

For example:

If Period = Today

And Frequency = Weekly

The report will be generated only for the last day of the week every week (on the mailing date).

Icon

To DISABLE Scheduled Email Reports functionality leave either Reports to Export or Email Recepients field empty.

Units section

In the Units section additional aheadWorks reporting units are displayed (if installed).

If the unit is installed it's version will be displayed on the right of it's name instead of 'Not installed' message.

Icon

Additional Report Units are purchased separately and require Advanced Reports extension installed.

Dashboard section

When navigating to Reports -> Advanced Reports user is presented with the reports Dashboard.

The Dashboard comes with the following functionality:

  • Store view selector - allows defining the store view for which you want the reports to be generated to;
  • Reports menu - provides a user with a handy report navigation block.

Under the Dashboard user is introduced to sales charts and pre-defined quick reports generated for the last 30 days to represent generic sales overview.

Charts:

  • Sales Overview;
  • Average Order Value;
  • Items per Order;
  • New Signups.

Quick Reports:

  • Bestseller;
  • Sales by Country.
Icon

The Dashboard comes as is. No additional charts or reports can be added to the Dashboard.


 

Reports Overview

Report Screen

All the extension's reports are listed under Reports-> Advanced Reports backend section.

Icon

Every report type represents data in a different way, with a different set of parameters included in a report.

Sorting options differ per report.

Pre-defined date ranges may not be available for particular reports depending on the report's purpose and peculiarities.

Once you access any of the reports, there are some common options:

  • Sorting Options - allows sorting the generated report by the most relevant criteria;
  • Expandable Calendar with a reports date range - allows defining the report's date range and apply the changes made;
  • Report Chart - represents the generated report's data in a diagram;
  • Export Button - allows exporting the report generated to CSV/Excel file;
  • Report Customization - allows customizing particular report (overrides the general Advanced Reports settings).

Expandable Calendar

Expandable calendar allows setting up required report date range.

  • Month blocks - these blocks allow manually specifying necessary date range by clicking required dates;
  • Date range - this section allows selecting either custom date range or pre-defined date ranges: 
    • Today;
    • Yesterday;
    • Last 7 days;
    • Last week (Sun - Sat);
    • Last business week (Mon - Fri);
    • This month;
    • Last month.
  • From / To - this fields allow adjusting date rage selected manually.


 

Customization screen

By clicking Customize button at the report's page user gets to the Customization screen. Here can be found additional report parameters along with the column management options.

General section of the Customization screen is represented by the following options:

  • Process orders - allows overriding the general settings of the Advanced Reports extension and specifying the statutes of the orders that should be processed when generating a report;
  • Select orders by - defines if the orders should be selected by either Created At date or by Updated At date;
  • Number of latest custom date ranges - defines how many custom date ranges the extension should preserve for the quick access from the Expandable Calendar at the report's page;
  • Use Default Value - this checkbox, if ticked, restores the General Configuration settings for the options necessary.
Icon

These are the general report settings available for all the reports. Some of the reports have additional settings available under the Customization screen.

These, report specific, settings can be found under appropriate Reports articles in this very readme.

Please click the report name to go to related article and look through the report specific settings:

Columns section allows enabling/disabling columns available for a particular report.

Icon

Each report comes with a fixed set of columns enabled by default, some being report specific. No additional columns can be added to a particular report.


 

Report Types

There are various report types, each having a particular role in covering the most important aspects of the store productiveness.

Icon

Some of the reports listed are 'Additional Units' that are purchased separately and require basic version of the Advanced Reports extension installed, and are not available otherwise.

Sales reports

 Sales Overview

Sales Overview represents the native Magento Sales report enhanced with the Advanced Reports functionality with charts available for illustrative purposes.

The report comes with the following columns:

  • Period;
  • Number of Orders;
  • Items Ordered;
  • Subtotal;
  • Tax;
  • Shipping;
  • Discounts;
  • Total;
  • Invoiced;
  • Refunded.

 Bestsellers

Bestsellers displays the information about the most frequently bought products.

Customization section of the report has two additional settings:

  • Products to show - allows defining the number of products to be considered as bestsellers;
  • SKU usage - defines if the displayed SKUs of the products should be grouped (parent) or simple (child) product related.

The report comes with the following columns:

  • N (line number);
  • SKU;
  • Product Name;
  • Quantity;
  • Total;
  • Total Cost;
  • Total Revenue (excl. tax);
  • Total Revenue;
  • Total Profit;
  • Total Margin;
  • Action.

 Sales By Product

Sales By Product is a product specific report. It allows displaying the sales of any particular product (by SKU) for any period of time considering quantity and total number of products.

The report's SKU field (SKU Advisor) provides user with suggestions as the product's SKU is being entered, and allows clicking the suggested SKUs.

Icon

It is also possible to generate a Sales By Product report for several products using an asterisk symbol * to substitute a part of an SKU.

For example, we have the products with the following SKUs: SKU-10, SKU-11, SKU-12 and SKU-13.

If at the Sales By Product screen in the SKU field we enter: sku-1*

The detailed Sales By Product report will be generated for all the SKUs listed above.

 Customization section of the extension has an additional setting:

  • The number of records in the SKU Advisor - allows defining maximum numbers of the SKUs suggested in the SKU field.

The report comes with the following columns:

  • Period;
  • Product Name;
  • Total Cost;
  • Total Revenue (excl. tax);
  • Total Revenue;
  • Total Profit;
  • Total Margin.
Icon

For Sales by Product report all columns are always enabled and cannot be hidden.

 Orders Detailed

Orders Detailed report provides a thorough insight on the orders: customer information, shipping and stock status changes; at any given period of time.

Icon

This is a per order report - no procut attributes information available as it is an order item related info.

The report comes with the following columns:

  • Order #;
  • Order Status;
  • Order Date;
  • Shipment Date (the date of the latest shipment in case there are more than one);
  • Customer Email;
  • Customer Name;
  • Customer Group;
  • Country;
  • Region;
  • City;
  • Zip Code;
  • Address;
  • Phone
  • Qty. Ordered;
  • Qty. Invoiced;
  • Qty. Shipped;
  • Qty. Refunded;
  • Subtotal;
  • Discounts;
  • Shipping;
  • Tax;
  • Total;
  • Invoiced;
  • Refunded;
  • Total Cost;
  • Total Revenue (excl. tax);
  • Total Revenue;
  • Total Profit;
  • Total Margin;
  • View Order.

 Sales Detailed

Sales Detailed provides a thorough insight on the products purchased: customer information, product details and stock status changes; at any given period of time.

Icon

This is a per order item report - no shipping information available as it is an order related info. It should be considered when calculating totals.

Unlike the Sales Overview, Sales Detailed report lists the products purchased within an order, not just an order ID.

Customization section of the extension has an additional setting:

  • Include refunded items - defines if the items refunded should be included in the report.

The report comes with the following columns:

  • Order #;
  • Order Status;
  • Order Date;
  • SKU;
  • Customer Email;
  • Customer Name;
  • Customer Group;
  • Country;
  • Region;
  • City;
  • Zip Code;
  • Product Name;
  • Manufacturer;
  • Qty. Ordered;
  • Qty. Invoiced;
  • Qty. Shipped;
  • Qty. Refunded;
  • Price;
  • Original Price;
  • Phone;
  • Subtotal;
  • Discounts;
  • Tax;
  • Total;
  • Total Incl. Tax;
  • Invoiced;
  • Tax Invoiced;
  • Invoiced Incl. Tax;
  • Refunded;
  • Tax Refunded;
  • Refunded Incl. Tax;
  • Total Cost;
  • Total Revenue (excl. tax);
  • Total Revenue;
  • Total Profit;
  • Total Margin;
  • View Order;
  • View Product;
  • Address

 Abandoned Carts

Abandoned Carts report allows monitoring the cart conversion rate.

The report comes with the following columns:

  • Period;
  • Total Carts;
  • Completed Carts;
  • Abandoned Carts;
  • Abandoned Carts Total;
  • Abandonment Rate.
 Stock vs. Sold

Stock vs. Sold provides stock related sales report basing on the price/cost difference.

The columns of the report are sortable and active allowing user to generate a report typing in any values required.

Customization section of the extension has an additional setting:

  • Out of Stock Estimation Threshold - defines how many of days passed are considered for providing estimates for the Out of Stock Estimate column.
Icon

Out of Stock Estimate column considers the sales record for providing the estimates of when one should expect the product to come out of stock.

The report comes with the following columns:

  • Product Name;
  • SKU;
  • Price;
  • Items Ordered;
  • Total;
  • Invoiced;
  • Refunded;
  • Product Cost;
  • Stock Qty;
  • Out of Stock Estimate.

 

 

 Sales by Category (Additional Unit)

Sales by Category introduces category related sales report.

The report's (Sub)category field (Category Advisor) provides user with suggestions as the category is being entered, and allows clicking the suggested categories.

The report comes with the following columns:

  • Period;
  • Items Ordered;
  • Tax invoiced;
  • Invoiced;
  • Subtotal;
  • Total;
  • Refunded;
  • Total Cost;
  • Total Revenue (excl. tax);
  • Total Revenue;
  • Total profit;
  • Total Margin.

 Sales by Product Attributes (Additional Unit)

Sales by Product Attributes allows specifying particular product attributes to get the related general sales report.

Report Settings block allows applying necessary product attributes available and managing attribute values.

It is also possible to specify particular conditions for precise reporting.

The report comes with the following columns:

  • Period;
  • Items Ordered;
  • Subtotal;
  • Tax;
  • Total;
  • Invoiced;
  • Refunded.

 Sales by Country

Sales by Country allows to view the list of the countries the items were bought from at any specific period of time.

The report comes with the following columns:

  • N (line number);
  • Country;
  • Quantity;
  • Total.
 Sales by Customer Group

Sales by Customer Group reflects the customer group based sales.

The report comes with the following columns:

  • Customer Group;
  • Quantity;
  • Total.
 Sales by Day of Week

Sales by Day of Week report represents general sales information considering the day of the product purchase.

The report comes with the following columns:

  • Day of Week;
  • Quantity;
  • Total.
 Sales by Hour

Sales by Hour represents general sales information considering the time of the product purchase.

The report comes with the following columns:

  • Hour;
  • Quantity;
  • Total.

 Sales by Manufacturer (Additional Unit)

Sales by Manufacturer represents sales insight on the product sales by manufacturer.

Customization section of the extension has an additional setting:

  • Product Attribute - defines the attribute that should be considered 'Manufacturer' by default.
Icon

The report can be set to use any attribute with the Catalog Input Type for Store Owner set as Dropdown.

The report comes with the following columns:

  • Attribute options;
  • Quantity;
  • Subtotal;
  • Discounts;
  • Total;
  • Tax;
  • Total Incl. Tax;
  • Invoiced;
  • Tax Invoiced;
  • Invoiced Incl.Tax;
  • Refunded;
  • Tax Refunded;
  • Refunded Incl. Tax.
Icon

Sales by Manufacturer 1.0.9 unit is only compatible with Advanced Reports extension version 2.6.3 or later.

 Sales by Payment Type (Additional Unit)

Sales by Payment Type allows generating a report basing on the payment method customer(s) used to checkout.

The report supports all payment Methods supported by Magento by default.

The report comes with the following columns:

  • Payment Type;
  • Quantity;
  • Subtotal;
  • Shipping;
  • Tax;
  • Discounts;
  • Total;
  • Invoiced;
  • Refunded.
 Sales by Coupon Code (Additional Unit)

Sales by Coupon Code allows tracking down the sales made with the use of a particular coupon code.

The columns of the report are sortable and active allowing user to generate a report typing in any values required.

Icon
The 'Coupon Code' field is case INsensitive making it easier to find the necessary coupon code.

The report comes with the following columns:

  • Coupon Code;
  • Orders;
  • Items;
  • Subtotal;
  • Tax;
  • Shipping;
  • Discounts;
  • Total;
  • Invoiced;
  • Refunded.
Icon

Sales by Coupon Code 1.0.7 unit is only compatible with Advanced Reports extension version 2.6.3 or later.

 Sales by ZIP Code (Additional Unit)

Sales by ZIP Code allows tracking the amount sold to a customers with a particular ZIP/Postal code.

The report comes with the following columns:

  • ZIP Code;
  • Orders;
  • Items;
  • Subtotal;
  • Tax;
  • Shipping;
  • Discounts;
  • Total;
  • Invoiced;
  • Refunded.
 Sales Statistics (Additional Unit)

Sales Statistics generates overall sales report.

The report comes with the following columns:

  • Period;
  • Orders;
  • Items;
  • Subtotal;
  • Tax;
  • Discounts;
  • Total;
  • Invoiced;
  • Refunded;
  • Order Amount(Avg.);
  • Item Final price(Avg).

Customers reports

 Users Activity report

Users Activity allows to get the relevant information on the customers activity: new accounts, reviews and orders for a set period of time.

The report comes with the following columns:

  • Period;
  • New Accounts;
  • Orders;
  • Reviews.
 Products by Customer

Products by Customer represents how many products customers have purchased on average at a given time.

The report comes with the following columns:

  • Products Purchased;
  • Number of Customers;
  • Total;
  • Invoiced;
  • Refunded.
 New vs Returning Customers (Additional Unit)

New vs. Returning Customers represents comparative breakdown of the customers making their first purchase vs returning customers in reference with the specified date range.

The report comes with the following columns:

  • Period;
  • New Customers;
  • Returning Customers;
  • Percent of New;
  • Percent of Returning.
 Customers by Country (Additional Unit)

Customers by Country represents customer's country based report.

The report comes with the following columns:

  • Country;
  • Users;
  • Customers;
  • Orders;
  • Items;
  • Subtotal;
  • Tax;
  • Discounts;
  • Total;
  • Invoiced;
  • Refunded.
 Users Wishlists (Additional Unit)

Users Wishlists represents responsive report on the customers' wishlists items.

The report comes with the following columns:

  • Country;
  • Users;
  • Customers;
  • Orders;
  • Items;
  • Subtotal;
  • Tax;
  • Discounts;
  • Total;
  • Invoiced;
  • Refunded.

 

Tips and Tricks

Price and Cost

Since Advanced Reports version 2.8.0 both item Price and Cost are caught from the order details, meaning that the Profit calculation will always stay relevant, disregarding of whether cost or price of the item has been changed.

Icon
 Cost in Advanced Reports 2.6.0-2.7.3

Since version 2.6.0 of the extension the Cost, Profit and Margin columns have been introduced to some of the reports available.

This article covers the columns behavior and the concept behind the new columns in reference with the Price column.

  • Price - the price value is caught from the order details that are stored in the Magento database;
  • Cost - the cost value is caught from the product details as orders do not have any reference to the products cost in the Magento database.

What does it mean for the reports generated?

It means that price value will always be order relevant whereas cost will always be product relevant. Thus affecting the Margin and Profit values.

E.g.: 

Before January the 1st 2015 item A had had a price of $100.

On January the 1st 2015 the price of item A has been changed to $150.

Now two reports are generated:

1) All the reports generated for a period before January the 1st of 2015 will list the product price as $100;

2) All the reports generated for a period starting from January the 1st and forth will list the product price as $150.

Icon

As price is the order related value, the results differ for the two reports generated for different time periods.

 

Before January the 1st 2015 item A had had a cost of $50.

On January the 1st 2015 the cost of item A has been changed to $100.

Now two reports are generated:

1) All the reports generated for a period before January the 1st of 2015 will list the product cost as $100;

2) All the reports generated for a period starting from January the 1st and forth will list the product cost as $100.

Icon

As cost is the product related value (Magento does not account changes made to products in the database), the results are same for the two reports generated for different time periods.

Revenue

For the report purposes Revenue is calculated using the following formula:

Total revenue (excl.tax) = sum for each item (Item Final Price * Qty. Sold - Total Discount)
 
Total revenue = sum for each item (Item Final Price * Qty. Sold - Total Discount + Total Tax)

Profit and Margin calculation for Advanced Reports 2.6.0-2.7.3

Since version 2.6.0 of the extension Profit column has been added to the following reports: Bestseller, Sales Detailed, Sales by Product, Sales by Category.

This article explains how Profit and Margin are calculated for the reports purposes.

Profit - represents flat difference between revenue and product cost.

For the report purposes it is calculated using the following formula:

Total Profit ($) = Total Revenue - Total Cost
Icon

Profit is affected by the cost and price values that come from different sources.

Please read carefully about how the extension handles price and cost values to get a complete understanding of how these may affect the profit value depending on the period the report is generated for.

 

Margin - represents percent ratio of profit to revenue.

For the report purposes it is calculated using the following formula:

Total Margin (%) = (Total Profit / Total Revenue) * 100
Icon

Total Margin values are rounded to the nearest ten.


Uninstallation

  1. Disable compilation, in case it is enabled.

  2. Login to your FTP, navigate to app/etc/modules/

  3. Open the file AW_Advancedreports.xml and change the following line:    

     

     

    <active>true</active>

     

    to

     

    <active>false</active>

     

    Now your Magento is unaware of the existence of the extension

  4. Clear the cache under var/cache

  5. Make sure that the site is working properly, otherwise roll back the changes and apply to our technical support.

  6. If everything works fine, it is safe to delete the files of the extension.

  7. In case you need to clean the database, make a backup and then run the following query in MySQL: 

     

     

    Note, if you are using the table with prefixes, you must specify them in all table names. 
    If you are not sure how to do that or expect any troubles with it, please contact your server administrator regarding the matter.
    If you remove the tables as it is described above, you will need to configure the extension again after it is reinstalled.

    Icon

    If you want to completely remove the extension's entries from the data base, make sure to remove all cached tables manually or by cleaning Magento cache.

Troubleshooting

After the extension installation the store gives an error, or blank page, or suggests to start Magento installation procedure.
Change the owner of the extracted extension files to the web server user and set 775 permissions on them. Clear the store cache and try again.
There is no aheadWorks extensions under my configuration section, or having the extension tab clicked I get a blank page, or Access Denied error.
Clear the store cache, browser cookies, logout and login again.
I've set up everything correctly, inserted the HTML code but there is nothing on that page.
Clear the store cache, clear your browser cache and domain cookies and refresh the page.
My configuration changes do not appear on the store.
Clear the store cache, clear your browser cache and domain cookies and refresh the page.

 


You can always find the latest version of the software, full documentation, demos, screenshots and reviews at http://ecommerce.aheadworks.com
License agreement: http://ecommerce.aheadworks.com/LICENSE-M1.txt
Contact Us: http://ecommerce.aheadworks.com/contacts/
Copyright © 2015 aheadWorks Co. http://www.aheadworks.com

  • No labels