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

[pauamma]

Oh, it's a Thorfy!

In case you're interested in the cont-ext, here's some prior discussion and links: http://dw-dev.dreamwidth.org/24149.html http://dw-dev.dreamwidth.org/19745.html http://dw-dev-training.dreamwidth.org/8523.html

2 (and 3) are kinda applicable (meaning they would be, except I had no plan to do what you're advising me against).

1 is also sort of applicable, because I need more than what's currently collected, so it's a trade-off between the definite effort involved in collecting stuff I may not need and the uncertain effort involved in maybe kicking myself in 3 years because I didn't collect enough 3 years ago. :-)

[thorfinn]

Ah, good, re: 2 and 3. :-) It's the most common bad thing I see developers do when they try to aggregate statistics (do it in the database instead of outside), so I thought I'd mention it.

As for 1, yeah, that's pretty much why I recommend the approach of "log as much as possible". Disk is cheap, and you can always slice data for analysis if you have too much, but you can't make up for data you don't have.