A nutritional supplement distributor with a Magento eCommerce website was looking to solve two business issues: (1) to improve their warehouse order fulfillment process by providing better access to customer order data, and (2) to obtain a better understanding of sales data and trends. The stock reports within Magento were not adequate and so they desired to have reports that would allow them to make better decisions based on current sales data. Since we have a unique team of developers with extensive experience with the Magento platform and it's MySQL database platform as well as Crystal Reports developers who specialize in custom reporting from a wide range of databases, ERPs and other applications, this client approached us to determine what could be done.
As many Magento developers know, the Magento database is not easy to report off of. Sales and customer order data crosses multiple database tables. We decided to start by pre-processing the Magento order data utilizing a variety of MySQL database views. Once those were in place, we then developed custom Crystal Reports to pull and present the desired data. Here are a few examples from the project:
Sales By Item Report – This report provides management with a total quantity ordered for each item based on a start and end date parameter. The report looks at the Magento sales and groups and sums the total amount purchased for each item and then ranks them.
Sales By State - This report provided their management with an understanding of sales amounts by geographic region, in this case" by state". The report utilizes a start and end date parameter and then groups and sums all of the sales for each state. A variation of this report was recently developed to also search by SKU, to determine which products are top sellers within a state for a given date range.
Open Order Report – This report streamlines their order fulfillment process by listing all open (un-shipped) orders on the website at any given time. The report prints a single sheet for each open order with the order number and customer information. This report also acts as a pick list by providing all of the order lines, the product SKU, product description, quantity ordered, unit price and total price. Bar codes can easily be added to the report to allow bar code scanning for inventory or shipping integration.
Top 100 Report - This report provides a list of the top 100 customers for a given date range. After entering a start and end date parameter, the report will list the top 100 customers along with their first name, last name, email, number of orders and total sales.
Do you have a unique reporting need you can't quite seem to solve? Click here to learn more about our Crystal Reports services. Better yet, contact SaberLogic right now to find out how we can help with your next reporting project!
