Drupal Commerce is an amazing open source ecommerce solution; with its foundation in Drupal it can easily contend with the big cookie-cutter ecommerce solution companies. Except for one area - reporting. Drupal Commerce and reporting seem to have missed each other while its growth skyrocketed. I mean, there are ways to build reports and show your store's revenue...just nothing "perfect," in my mind. Let's start with this: Drupal commerce orders have a creation date and updated date. The first is obvious and the latter is based on the order's status changing (also line item changes, etc.) An order has payment transactions which are related to it, so toss in another captured time - an order's payment transactions. Here is where things get tricky.

The issue of "when"

There is Commerce Reporting which is a pretty awesome module all in its own, except for a major flaw (if you want better revenue reports): it bases its reports off of the order's creation date. Off the bat this does not seem like that big of an issue - until you realize an order's creation date is not always the same as when its checkout cycle is completed. I could add an item to my cart, thereby creating an order, and leave the site. I come back two days later and finish my order. What if I started on the 30th and finished on the 1st? Or, what about midnight shoppers? The payment records and order records will not match, and that's a big issue for accounting. The customer, product, and tax reports are great. But they do not mean much if your business's accounting is not right. On a recent project the problem with Commerce Reporting did not hit me until I realized our Google Analytics ecommerce conversion tracking made no goddamn sense. There is a module which extends the Google Analytics module for Drupal to push Commerce order information - Commerce Google Analytics. When an order completes the checkout process, this module will drop in the GA Push script to shoot off information such as order number, products, all that goodness. Well, needless to say, after a while I was scratching my head when the client was saying we had conflicting reports...until I realized that Analytics tracking fired on checkout complete whereas Reporting was based on the order's creation date. Sometimes it made sense, sometimes it didn't. So then I came up with a solution: why not build a View based on the order's updated date - that should work fine. Yeah, it didn't. The order's update timestamp will change when it completes the checkout cycle, but it will also be updated when the status changes. Basically come Monday when all of the orders from the weekend were processed there appeared to be no weekend sales. At this point I was pretty frustrated because of "when" for creating a decent report.

TL;DR Breakdown:

• Commerce Reporting is based off of an order's creation, not when completed.
• Commerce Google Analytics fires at order complete (oh yeah, this doesn't account for admin created orders.)
• View based on order's updated date ends up tracking when an order's status has been updated.

I do want to note that andyg5000 dropped a hint at utilizing the payment transactions records for the view's base table. I didn't try it because at the point I was a raging hell bent bastard.

Update: After a discussion on the blog post with Ryan Szrama (which apparently Disqus can't migrate comments platform to platform?!) I have opted to become a maintainer of Commerce Reports and bring it up to speed (see Commerce Reports 4.x). Ryan also commited a new (disabled by default) rule to Commerce that alters the order's created timestamp to the checkout timestamp, resolving issues stated before. Issues node: https://drupal.org/node/2044231

So the sandboxed module below is not getting any love.

Commerce Order Reports module (sandboxed)

So! I came up with my own solution: Commerce Order Reports. I wanted a module. I wanted something that could be forced to handle as much data as possible and not be constrained based on how well you could build a view. Like I said, I love Drupal Commerce and I fully believe a strong reporting solution can help it rise to the top. Whether this is the solution or not, hopefully it'll spark some direction.

  Right before writing this post I created a sandbox project for Commerce Order Reports on Drupal.org. Here's what the module does:

• Creates a new entity type called commerce_order_reports
• This entity has database fields to record an order's ID, number, base price, shipping costs, and tax amount.
• Since its an entity it can have fields! Which means customizable reporting!
• Utilizes Rules to create a report entity when an order is paid in full. When it triggers it'll copy information from the commerce order into the report entity.

This helps take care of the "when" situation. Generally speaking if an order completes the checkout cycle it has been paid in full. So this helped resolve the Google Analytics conversion tracking and sales reporting discrepancy. By also tracking when the order was paid in full it allows admin generated orders to be tracked. Commerce Order Reports also helps break apart gross earnings, revenue, shipping and tax. Commerce Order Reports cuts down the steps to get this information by generating it when the entity is created. As of right now the module adds two new tabs to the manage order screen: Daily Report and Monthly Overview. Daily Report is by far more flushed out than the monthly overview (see screenshot above.) The Monthly Overview will display a row for each day of that month, once I get it to aggregate/group by dates properly.