cabbey
Jun-11-2010, 03:12 PM
Howdy Folks, I'm seeking feedback from the heavy number crunching spreadsheet dwellers out there... you know who you are. ;)
Currently on the sales history page we offer a single "Download CSV" link that gives you a spreadsheet with info about every item you've sold through us.* Those of you familiar with that file know it's about 35 columns wide (or "AI" wide as most programs count it.) and has a LOT of redundant data. It's also missing data for a number of our recent features because, to be honest, we've had a hard time figuring out how to get the right data into the file without making the organization worse than it already is. It's also been missing data for administrative adjustments forever.**
All that is about to change. But the new stuff is a pretty radical change from what we have today, so I want to hear from my fellow 10-key jockeys about the new setup I'm about to unleash. Here's the big change: I'm splitting the spreadsheet into two CSVs.
Sheet 1: Order Summary
This sheet will have 1 row per order. The columns for this sheet will start with the OrderID and Date, then will list the customer details (columns E through U in the current version). Then it will list some summary of the order, number of items, aggregate base price, aggregate pro price, estimated profit, shipping, sales tax, profit payment status, date and details. (Same as I just added to the sales history details page this week.)
Sheet 2: Line Item Details
This sheet will have 1 row per "line item"... where "line item" is loosely defined to also include items that adjusted the profit on the order, such as coupons, branding charges, or administrative adjustments. The columns here will start the same with OrderID and Date for sorting and coordination purposes, then will cover the item by item quantity, base and pro prices, estimated profit, what type of line item it is with it's name, the details about the image used for it (AB through AG today), then finally the payment status and details as above.
So that's the plan. The advantage to this is that you can now much easier slice and dice data for a lot of the types of data mining that folks have asked for over time, plus summaries of things should be much easier. (And the files will be smaller to download/store too, even with BOTH of them and the added data, we're talking a noticable savings for almost all pros!)
Footnotes:
* Caveat, up until recently it was "every item you've sold through us at a profit." non-profit sales before that change are still not present... there's nothing I can do to change that.
** Administrative adjustments occur for example when an order placed on the 26th is paid out to you the next month, but arrives at the customer on the 5th and has unrecoverable quality issues that force us to credit the customer for that portion of their purchase. Since we've already paid you the profit for those items, we create an adjustment that will effectively take that profit back from your next payment.
Currently on the sales history page we offer a single "Download CSV" link that gives you a spreadsheet with info about every item you've sold through us.* Those of you familiar with that file know it's about 35 columns wide (or "AI" wide as most programs count it.) and has a LOT of redundant data. It's also missing data for a number of our recent features because, to be honest, we've had a hard time figuring out how to get the right data into the file without making the organization worse than it already is. It's also been missing data for administrative adjustments forever.**
All that is about to change. But the new stuff is a pretty radical change from what we have today, so I want to hear from my fellow 10-key jockeys about the new setup I'm about to unleash. Here's the big change: I'm splitting the spreadsheet into two CSVs.
Sheet 1: Order Summary
This sheet will have 1 row per order. The columns for this sheet will start with the OrderID and Date, then will list the customer details (columns E through U in the current version). Then it will list some summary of the order, number of items, aggregate base price, aggregate pro price, estimated profit, shipping, sales tax, profit payment status, date and details. (Same as I just added to the sales history details page this week.)
Sheet 2: Line Item Details
This sheet will have 1 row per "line item"... where "line item" is loosely defined to also include items that adjusted the profit on the order, such as coupons, branding charges, or administrative adjustments. The columns here will start the same with OrderID and Date for sorting and coordination purposes, then will cover the item by item quantity, base and pro prices, estimated profit, what type of line item it is with it's name, the details about the image used for it (AB through AG today), then finally the payment status and details as above.
So that's the plan. The advantage to this is that you can now much easier slice and dice data for a lot of the types of data mining that folks have asked for over time, plus summaries of things should be much easier. (And the files will be smaller to download/store too, even with BOTH of them and the added data, we're talking a noticable savings for almost all pros!)
Footnotes:
* Caveat, up until recently it was "every item you've sold through us at a profit." non-profit sales before that change are still not present... there's nothing I can do to change that.
** Administrative adjustments occur for example when an order placed on the 26th is paid out to you the next month, but arrives at the customer on the 5th and has unrecoverable quality issues that force us to credit the customer for that portion of their purchase. Since we've already paid you the profit for those items, we create an adjustment that will effectively take that profit back from your next payment.