More brainstorming on stats: payment stats

[pauamma]

So I'm still chipping away at bug 124, and I've reached:

* Number of payments in last 1d/2d/5d/7d/1m/3m/1y
-- broken down by which payment level/payment item chosen
-- xxx and divided into new payments vs. renewals
-- xxx and expressed as a dollar amount taken in during that time

Now the first thing to figure out is where and how payments are stored in the database, and whether (and how) I can use the stored data for my purposes.

Since I need to split data by what was ordered, I need to use shopping carts (table shop_carts, module DW::Shop::Cart), since that's where that information is. However, shopping carts don't tell me when payment was received, only when the cart was opened/checked out/whatever. For payment by check or money order, the difference could be substantial (several days to over 1 week), esp. if you want to wait for checks to clear until you record the payment. Since this is longer than the periods over which we want stats, it won't work, so I either need to base the stats on the order date instead of the payment date, or to use something else in addition to cart data.

It's tempting to use pp_trans (see DW::Shop::Engine::PayPal), but that's only for PayPal payments (as far as I can tell), so it won't work for that purpose, since there are no equivalents for other payment methods (credit card, check, money order). Plus, I just care when the payment was received, not how, so I don't really want to get into that, with the likely maintenance nightmare that would come when another payment method is added, or worse, when payment for a given cart may be split between methods (hello, gift certs).

So it looks like I need to add some data to shopping carts. All I need at that stage is a single timestamp for the last change of state, which could be indexed, which is nice (I can retrieve carts for which payment was received (state is PAID or PROCESSED - see $STATE_* in DW::Shop) in the last (mumble) days. For existing carts that won't have that timestamp, I can either set it from the creation timestamp or use other means, such as scavenging through pp_trans. I don't really need to keep a complete history of state changes, and I can't think of a use case for one (but then, I'm not an accountant, not a lawyer, and not a developer of financial/accounts receivable/ecommerce software).

Anyone wants to laugh at/tear into the above, or suggest alternatives or stuff I overlooked?

Comments

[mark]

Hmmm, I haven't put a LOT of thought into yet, but wanted to toss this out here:

Have you considered just making the payment system have some hooks when it changes state? Every state change makes a call to the 'state' method on the DW::Shop::Cart object, just put in a hook 'shop_cart_state_changed' and then you can process it in the stats system fairly easily.

I think for the purposes of this statistic, the only thing you care about is transitions into the PAID state. All other transitions don't matter for this particular statistic, since if we refund something we want to have that count separately. Reversals are rare, so for now at least, let's only care about when something becomes PAID.

If you throw the hook in, you can probably implement this stat in a few minutes. It would be ideal if there's some way to go back and find all PAID carts and backdate the stats, but that may not be very possible. (I'm happy to write that script too, actually, since it's just a one-off.)

[pauamma]

Hmm, I hadn't thought of that option, but it would neatly avoid adding a state change timestamp to the cart. Also, now that I think of it, it would help deal with locales where sales tax (or VAT, or whatever) becomes due in the period when the sale money comes in, not the period the bill is issued. So yeah, I like that idea. Thanks.