One of the main ideas behind my StoreBoss™ project is explore in different, interesting and useful ways how to provide a store owner with the ability to keep forensic track of Woo orders coming in over the digital transom.
Unfortunately, Woo reports tend to be skimpy in that regard, and often do not have the sort of details you might be interested in knowing. Also, WooCommerce is not really ideally suited for ad hoc reporting or data analysis on the fly.
It always possible to write your own reports of course, but that means humping out the WP way of doing things, which entails learning PHP and mastering an elaborate gauntlet of hurdles that stand between you and your data.
It would be nice to simply be able to query the WP database directly, without having to go through lumbering and slow “REST APIs”, when all you want maybe is to quickly see what orders are coming in, how long it’s taking to ship product out the door, how long is it taking before you get paid, and determine precisely the haircut imposed by third-party payment processors like STRIPE. A store owner might also wish to verify that no sales records are being manipulated by inhouse staff.
It’s unreasonable to expect the small B&M store owner to have the time or technical knowledge or personal inclination to master the contortions required to query the WP/WOO database schema.
However, if one is interested in stripped down, just the facts information about, say, Orders, it is not really that hard to write an SQL query that does the job by directly querying the database.
Have you ever wondered how you can verify that the data you see in Woo reports (or the various third party Woo plugins) is accurate? Wouldn’t it be nice to be able to verify their accuracy for yourself?
Moreover, have you ever wanted to be able to look at a simple, up-to date report and see in a instant your cash flow position at any given moment?
That is what I am going to attempt to build in StoreBoss™. Please note that the ideas described here are somewhat experimental, and rely on MariaDB 10.3 or above as the WP Storage Engine. Your WP hosting provider may require a dedicated server for such a configuration. A StoreBoss™ plug-in would of course eliminate this dependency.
Please note, also, the WP in general frowns on directly querying its database. Since we are not touching any Woo or WP data directly (other than via the SELECT statement), we can safely ignore this directive — while recognizing that any query run against the DB will incur a performance cost.
Our goal therefore will be to create a staging table for StoreBoss in the WP database, then download this data to the user’s machine where it can be stored in SQLite and analyzed using a Python application. SQLite is a robust, single user DB engine does not require any technical knowledge to configure, and is free.
In this manner, security and privacy issues will be dealt with in a strict fashion: Woo and other data (such as Bank Statement information, which we will get to later on) will be controlled on the end user’s machine, and thus be far less susceptible to SQL injection and other hacker annoyances and malicious attacks that must be taken into account by WP and Woo in the cloud.
This will free StoreBoss™ from having to rely on tediously baroque and ineficient methods of calling the inordinately complex underlying Woo DB (using PDO etc) out of a constant paranoia of being hacked, a situation that also hamstrings the ability of the end user to query data on an ad hoc basis.
With all the needed data (which can be encrypted, if need be) updated on his or her private desktop on notebook machine, a store owner will NOT risk burdening the WOO prod env with an undue amount of extra CPU cycles that may exceed some hosting provider threshhold or slow down catalog displaying or transactional order processing.
Let’s get started with a simple example.
I’ll begin with 1 order that I will enter manually in a clean copy of WP/Woo that I’ve installed on my desktop. You can set up something like this too, without too much trouble — in my case, I’m running XAMPP on a refurbished 10 year old HP machine running 64-bit Windows 8.1. So there is nothing fancy whatsoever about my setup. Store owners of course would not have to do this; my setup is for dev purposes only.
Here is what my Woo Order panel looked like after I manually enered my first order.
To verify the accuracy of this data, I queried the underlying database using PhPMyAdmin. This is the query I wrote to retrieve the basic information I need to know about this order.
SELECT p.`ID` AS `Trans ID`, p.`post_status` AS `Trans Status`, p.`post_date` AS `Trans Date`, o.`meta_value` AS `Trans Amt` FROM `wp_posts` p INNER JOIN `wp_postmeta` o ON ( o.`meta_key`= "_order_total") WHERE `post_type` = "shop_order" AND p.`ID` = `post_id`;
This query produced a single row of Order data that’s in a Pending payment state.
I then changed the state of the Order to Processing – which meant that Payment was received (of course no actual money had changed hand, as this was just a test).
The Woo Orders panel now looked like this.
Notice how there was now a highighted “1” in a small red circle now in the WP Admin Panel.
I again used my query, which gave me the following result.
Note how there was still only one order row? It had changed state (check out the Trans Status column), but did not provide an audit trail that I could query as to how much time had elapsed between when the order was received and when the money had “come in.” (We will go with Woo’s loose concept of being in receipt of funds, for now.)
Luckily, I had previously created a custom table in the WP db, and written out the first Order row to it. Here is the DDL I used to create the table (the sb_ prefix stands for StoreBoss™, which will help reduce the chances of database-object naming conflicts in the WP/Woo persistence engine and its accompanying plug-in eco system.
CREATE TABLE IF NOT EXISTS `sb_trans_1`
`Trans_ID` bigint(20) NOT NULL,
`Trans_status` varchar(20) NOT NULL,
`Trans_date` datetime NOT NULL,
`Trans_amt` decimal(13,2) NOT NULL,
PRIMARY KEY (`Trans_ID`, `Trans_status`) )
ENGINE = ‘MyISAM’;
Note the Engine I’ve chosen: ISAM is faster for DSS or Analytical processing, which is what we will be doing here.
I then updated my table with the order row in this current state, using the following DML.
INSERT INTO `sb_trans_1` (`Trans_ID`, `Trans_status`, `Trans_date`, `Trans_amt`) SELECT p.`ID` AS `Trans ID`, p.`post_status` AS `Trans Status`, p.`post_date` AS `Trans Date`, o.`meta_value` AS `Trans Amt` FROM `wp_posts` p INNER JOIN `wp_postmeta` o ON ( o.`meta_key`= "_order_total") WHERE `post_type` = "shop_order" AND p.`ID` = `post_id`;
Now I had 2 rows of order data, but unfortunately I had a small problem: since the transaction date had not changed (which of course is correct), I had no way of knowing how much time had elapsed between these two events.
To remedy the situation, I added a timestamp to my table, using the following code.
ALTER TABLE `sb_trans_1` ADD `Trans_timestamp` TIMESTAMP NULL DEFAULT NULL AFTER `Trans_amt`;
Now I actually had temporal state information pertaining to the changes in status that my order had gone through, I now marked the Order as paid, and updated my custom table. It now looked like this.
Note the Trans_timestamp column on the far right. Now I finally have a way of adding valuable audit trail data to my Store Management toolkit. The best part is that all this ETL scrubbing can be automated and displayed on an unclutterd PHP-driven report that might look something like this.