Jumping Ahead – Handling Daylight Savings Time

StopreBoss Data Flows
Figure 1: StoreBoss Data Flows

StoreBoss™ relies on three important temporal data sources:

  1. the WooCommerce (WC) DB;
  2. Stripe transactional data; and
  3. a WC store owner’s bank Statement of Account.

StoreBoss performs a “tick-and-tie” matching operation between these sources of transactional data. Discrepancies may arise due to differences in time settings.*

DST adds a further complication, by causing transactional time to be shifted on recurring intervals, depending on which TZ a store owner has set for his or her WP installation.

Per Stripe’s documentation,

 

TIME ZONE DIFFERENCE

All payments and payouts are processed according to UTC time. As a result, the processed date may not be the same as your local time zone.

 

A desirable solution is to normalize the Stripe charge date by extending MariaDB (or MySQL) with a stored function that automatically and consistently performs a correction in all use cases.

By applying this function to produce a new temporal value (which is persisted in a custom descriptor during file loads), StoreBoss can calculate normalized lag times between the various servers that produce transactional data.

Thus, StoreBoss will be able to more accurately verify a store owner’s Cash Balance Ledger position.

Let’s get into some technical details.

Assume you have downloaded from Stripe the previous day’s transactions, and manually load it to a work table whose structure matches the file’s.  While creating the necessary DDL, you might notice that a field named “Created(UTC)” is used by Stripe to indicate when news of the transaction was conveyed to your bank.

After uploading the CSV file (which happens to contain 1 record) to a work table on Maria whose structure matches this file, you decide to create a view that contains a column that shows the time of the event in local units. The code for such a view that produces the local time translation for any global time zone would look something like this.

CREATE VIEW IF NOT EXISTS
`sb_etrans_vw`AS
SELECT 
...
Created_UTC AS 'CreateD UTC',
CONVERT_TZ(Created_UTC,'+00:00', LEFT(timediff( now(), utc_timestamp() ), 6) )
AS 'Event instance',
...
FROM sb_etrans;

As you can see below, this code does its job perfectly well — except for two fatal flaws… it does not check to see if a transaction falls within the DST interval for your timezone, whose start and end date (expressed as a number, not as a weekday) changes yearly.

For example,  the second Sunday of March in 2019 was March 10, and the first Sunday of November will be the 3rd. In 2018, these dates were March 11th, and 4th.

storeboss utc testing

 

To facilitate its use, the modified code could be encapsulated, as follows:**

CREATE VIEW IF NOT EXISTS
`sb_etrans_vw`AS
SELECT
...
Created_UTC AS 'CreateD UTC',
CONVERT_UTC_2_LOCALT(Created_UTC)
AS 'Event instance',
...
FROM sb_etrans;

We shall be exploring in some depth CONVERT_UTC_2_LOCALT.

In future posts, I will explore Stripe’s Sigma product, which allows WC store owners) to perform extensive data analysis.

Sigma may be a preferred way for SQL-savvy end users to obtain needed data from Stripe — without having to go through Stripe’s REST API and JSON file decoding and OAuth authorizations.

To be sure, Sigma is not free; to many SMB WC storeowners, its price point — particularly in view of Sigma’s significant data lag — may not be worth $500 a year.

Moreover, the hurdle of its SQL interface can be quite intimidating to a nontechnical end-user.

Having said all that, let’s write the UTC conversion function for our manually-downloaded CSV STRIPE payments file.

*UTC has been in the news of late!

**Realistically you would only have to account for the last 2 years of transactions, on a rolling basis, as this is typically a banking archive limit

Advertisements

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!