Unlocking WC Orders

free yourself

To regular store owners, the WC REST API is intimidatingly useless — a roadblock that does not allow a simple way to run customized or unique ad hoc queries they might wish to run.

To SQL-savvy data analysts who prefer to use straight-up relational declarations (that are not chopped up into little pieces by SQL Injection-driven paranoia), retrieving sales info through the REST API via some procedural intermediate language is an inelegant impedance mismatch hack: a baroque apparatus that is oppressive, particularly if an app does not expose the RDMBS to the outside world, and is only meant for inhouse consumption by trusted users who can be granted various levels of authorization.

Why not just get at the data directly?

sql query
Q1 and its result set, run using PHPMYADMIN

After testing various forms of Q1 (the query I’ve been playing around with during the last few posts), I arrived at a version that provides a succinct way of presenting Order-related information.

SELECT
    p.ID AS "Trans ID",
    p.post_date AS "Trans Datetime",
    c.comment_content AS "Event Status",
    c.comment_date AS "Event Datetime",
    o.meta_value AS "Order Total"
FROM
    wp_posts p
LEFT JOIN wp_comments c ON
    (
        p.post_type = "shop_order" AND p.ID = comment_post_ID AND c.comment_type = "order_note" AND c.comment_content NOT LIKE "Item%" AND c.comment_content NOT LIKE "Stock%"
    )
INNER JOIN wp_postmeta o ON
    (
        o.`meta_key` = "_order_total" AND p.ID = o.post_id
    )
INNER JOIN wp_postmeta x3 ON
    (
        x3.`meta_key` = "_completed_date" AND p.ID = x3.post_id
    )
ORDER BY p.post_date;

As shown up top, Q1 produces a result set that lists by ascending date the predetermined state transformations that every Order (aka transaction) goes through in the WC database.

I have added (to previous incarnations of this query) two LIKE operators to eliminate superfluous events from the Orders event-driven timeline.

This does not completely remove the return of all non-payment related events (such as inventory changes) that are recorded in the wp_comments table.

The mask format of these comments has changed over the years, and it would be inefficient to add too much string processing to Q1 to account for every variation; besides, WC has lately seemed to stabilize how it persists inventory change information, as of 2019, so this should not be too much of a problem, going forward.

Q1 results can be saved to a local file on the server (with a slight modification to the code), then loaded to a custom table that shows :

  1. when a Sale was recorded in WC;
  2. when STRIPE (which is the payment processor I am using for test purposes) processed the CC payment, and,
  3. when the order itself was marked as completed (indicating that it was fulfilled by the merchant).

As I’ll demo in the next post, this custom Orders table can then be queried by an end-user to produce temporal analytics.

The query also results in other information of interest, such as when a card was declined, or if a customer was sent a confirmation email.

I tested the query in a production WC environment with thousands of records in the wp_posts table.  It works correctly, and is also quite fast — while utilizing subseconds of CPU time.

wc stripe charge
Here a real-world result set. I ran Q1 against a production WC site that I manage. Here we can see a Stripe Charge event being recorded against a $503 sale that took place this past Sunday (on 8-11-19). Note the transaction time is captured down to the second.

However, I have not stress-tested the query in a huge database with millions of orders.

Any such merchant will have an in-house IT department that performs these sort of backend tasks; the idea with StoreBoss (which is the code name of the plug-in app I’m building) is to provide DSS functionality on a Windows or Mac desktop, using SQLite and Python — from data that is derived (or “unlocked,” if you will) from the WC database — hence the catchy Widgeteria tag line, “AI for the little guy.” (‘Guy’ here being generic, not gender-specific.)

Armed with this simple query, and a vanilla script front end that sits in the WP admin panel, store owners will have an additional way (meaning, in addition to what WC offers, free or pay, by way of downloadable Orders data) of downloading their order data to the desktop.

But even within the WP admin panel env itself, the data is presented such as to be easily readable.  It also permits the ad hoc querying, in a straight forward manner, of temporal state changes that an Order goes through in  WC — without causing locks on the WC OLTP env.

How cool is that?!

WC order panel
I manually entered this order using the WC admin order panel. I used the sample product db provided by WC; unfortunately, WC does not provide a simple way to upload a testbed transaction db. Testing has to be done manually unless you are willing to try your hand at this. Btw, note that transaction seconds are not displayed in the WC Order panel.

In production, the query will be implemented as a parameterized stored procedure, one that accepts sanitized date ranges.

It should also be possible to run this query as a MariaDB event (like a cron file), one that runs, say, at midnight + 1 min every 24 hours, in order to produce a running tally of the previous day’s order processing, which could be then downloaded to the desktop for DSS or tick-and-tie processing.

wc order analytics
It seems that the WC Order analytics report doe not completely love Orders that are manually entered!

But for now, I am still experimenting with how to extract the simplest, cleanest data set possible from the WP database without having to deal with the complex and resource-consuming WooCommerce REST API.

Q1 seems to be a good start.

WP set date panel
When doing temporal analytics, make sure you first set WP to the correct time. In this case, I set it to EST. This will save a lot of headaches later on, when we perform some basic date math. The subject of temporal databases, particularly distributed ones, is vast; I will touch upon relevant aspects in various posts to come.

We are not too far away now from figuring out how to match STRIPE payment data to the receipt of actual funds (some unit of time later, minus any haircut) in a real bank account for honest-to-goodness real-world transaction verification!

 

Advertisements