Matching information from one’s credit card processor to your WooCommerce installation can be pretty dull — unless it’s done automatically.
This sort of problem is an example of a temporal, meaning time-related, application — a subject that I have become somewhat passionate about of late.
Time is it, at least for me.
Not products. Not sales. Not customers. Not fulfillments. Not subscriptions. Not SEO. Not the latest framework drama! Just time.
Time, pure and simple.
Imagine if a database were constructed entirely as a function of time.
It’s possible. Time dimensional databases are old hat. I am not talking about that. And I’m not only referring to notions of system time vs application time: so-called bitemporal data.
Instead, imagine a database where the primary unit of currency, somehow, is time itself.
Time is the one alluring resource that’s both infinite and non-renewable — depending on one’s perspective.
An e-commerce store, or better yet, a Wall Street issue can be thought of as clock automata that perform a linear traverse through a temporal state machine: a security is traded, then goes through the settlement process.
During this span, events may or may not happen. These events may or may not reach their ultimate conclusion. Time may even take on the characteristics of being a non-goal oriented phenomenon.
What is really being sold on Wall Street is in fact time. Today, the time left before the security matures is worth 10 dollars. Tomorrow, it may be worth only five.
A football game with only 3 mintues left could be worth a touchdown, rarely more.
But in the hands of a skilled quarterback, 60 seconds might be just enough to win the game. And the stakes get higher with each passing second, which, in effect is worth more than the previous one.
Time may remain suspended in a state of incompleteness, or even reverse course.
Or it may veer off into some other direction, expected or not. Computational linguistics has been abuzz of late on these issues.
Let’s come down from the clouds.
Ideally, a WP plugin that purports to do the job of matching sales and credit card payments correctly and efficiently would be able to retrieve data in real-time from, say, a Stripe server, and match it (or not) to existing WooCommerce Order information.
It should also be capable of doing the same with bank information, which also travels through time, but at a different velocity.
Meanwhile, the credit card processor may put a lock on a merchant’s funds, due to suspicious activity, thus lowering the monetary value of the store owner’s version of time.
Does Stripe’s Sigma product make this matching process easier, or faster?
I took a look today at Sigma, on a trial basis, and my verdict is… possibly.
However, it’s important to keep in mind that Sigma’s data is 2 days old; thus the product may not be ideal if a store owner needs for whatever reason to keep an accurate picture of his or her cash position on a near real-time basis.
Moreover, Sigma does not really give you a fully-enabled SQL tunnel into a store owner’s transaction portfolio.
Sigma supports a modified SQL shell that allows read-only access.
I tried doing a few standard things in SQL but they turned out to be pretty much unsupported. It kind of reminded me of the constrained version of CSS and HTML that WP.com makes available to its end users.
I’m going to finish this post the lazy way, simply copying and pasting my notes on the hour I spent earlier today trying out Sigma, while comparing its output to what you can get from downloading Stripe transaction data (which is more up to date).
In the regular Stripe dashboard (non-Sigma interface) you can point-and-click on a slick drop-down to customize the columns you wish to download.
The ALL columns default layout for the Stripe CSV payments export file is
3 Seller Message,
4 Created (UTC),
6 Amount Refunded,
8 Converted Amount,
9 Converted Amount Refunded,
12 Converted Currency,
15 Statement Descriptor,
16 Customer ID,
17 Customer Description,
18 Customer Email,
20 Card ID,
21 Card Last4,
22 Card Brand,
23 Card Funding, 24 Card Exp Month,
25 Card Exp Year,
26 Card Name, 27 Card Address Line1,
28 Card Address Line2, 29 Card Address City,
30 Card Address State,
31 Card Address Country,
32 Card Address Zip,
33 Card Issue Country,
34 Card Fingerprint,
35 Card CVC Status,
36 Card AVS Zip Status,
37 Card AVS Line1 Status,
38 Card Tokenization Method,
39 Disputed Amount,
40 Dispute Status,
41 Dispute Reason, 42 Dispute Date (UTC),
43 Dispute Evidence Due (UTC),
44 Invoice ID,
45 Payment Source Type, 46 Destination,
(a) Interchange Costs, — missing from csv file
(b) Merchant Service Charge, — missing from csv file
48 Transfer Group, 49 PaymentIntent ID
50 Customer Name (empty)
51 Customer Email (empty)
52 Customer Name
53 Customer Email
55 Payment Intent ID ( = order_id col on csv file = WC generated wp_post_ID)
If you check only the 7 cols below, Stripe silently adds a few columns extra such
as customer name and email.
Nevertheless, you can easily get a three-part match with WC generated trans data, which I showed in an earlier post.
1 Stripe ID Match criterion 1
5 Amount Match criterion 2
55 Payment Intent Id Match criterion 3
the above does not provide a “net” payment col (meaning from the free admin panel), but Sigma (which is not free) does
Sigma amt results format are quite funky
why is amt a bigint? so that payment can be persisted in the smallest currency unit — to make math and currency conversion easier?
Sigma’s sql editor does not change table context depending on which table you click on the table list in the left panel in the Sigma SQL “editor”
no live syntax checking of sql
if you type ‘xelect’ instead of select you get an error
err msg: Malformed query: line 2:3: no
viable alternative at input ‘xelect’
hmmm… Cassandra… is that you? a nosql back end for sql queries? i dont know Cassandra SQL, but this might explain a lot
including why queries are noticeably slow
very annoying subset of SQL (see screenshots)
cast of bigint to decimal does nothing
Tooltip also gets in the way, though it was a nifty data dictionary idea at first. I did spend the time trying to figure out if you could turn it off.
I basically played around with the transaction table, trying simple stuff like this
select id, cast(amount as decimal(13,2)), // did not work
cast(fee as decimal(13,2)), cast(net as decimal(13,2)), // nope
where MONTH(created) = 8;
// division not properly supported, depending on the operands
fee / amount returns 0
but 10 / 5 returns 2
so I could NOT get SQL to verify the haircut (what Stripe charges)
First look bottom-line conclusion: Sigma is definitely going to allow me to get a quick grasp of Stripe’s API. There’s also a useful manual here. But in view of some the considerations listed above, this is probably not the ideal tool for an SQL power user, yet paradoxically would be too much for a regular store owner to handle.
Moreover, the 2-day lag is a killer. I do think a non-technical user would initially find it a bit confusing to write legal SQL on the fly, then see error messages pop up. Finally, remember that Stripe sees only its own data (plus some geospatial stuff provided by outside vendors). For the whole picture, you would need to go elsewhere.
I am going to keep going with Sigma, read the manual, and see if some of my early conclusions need to be revised.
PS: reading manuals is nice, but I prefer to hit the catalog to see what I’m working with. Based on the Cassandra theory, I tried to run this, but the query was “rejected”:
SELECT columnfamily_name FROM system.schema_columnfamilies WHERE keyspace_name = 'keyspace name';
I am going to look right off the bat for a visual Sigma database schema in the manual. If an ERD of some sort does not exist, I shall lament the time I am about to waste creating one.