Help SmugMug make sales history CSVs better
cabbey
Registered Users Posts: 1,053 Major grins
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.
SmugMug Sorcerer - Engineering Team Champion for Commerce, Finance, Security, and Data Support
http://wall-art.smugmug.com/
http://wall-art.smugmug.com/
0
Comments
Now anyone want to buy some of my pictures?
Pictures | Website | Blog | Twitter | Contact
Thanks for the thoughts on it Brad.
The coupon info will show up on the line items detail sheet. The packages are also broken down in there very similarly to how they're broken down today.
Events don't really look like their applicable to this... they're a way to get people TO images, but once they're at the image, they're not directly connected to the purchase as currently implemented... so there's no data there for me to work with. That's an interesting feature request though... if you're really interested in it you should file it over in uservoice.
http://wall-art.smugmug.com/
Pictures | Website | Blog | Twitter | Contact
It's quite difficult to gather the info needed to file quarterly sales taxes in TX from the existing csv that has one line per item, with the shipping and sales tax repeated for each item in the order.
I need the total sales amount (the total of what all the customers paid), the total shipping amount, and the total tax collected for the quarter. It should be no more difficult than summing a single column for each of these items.
It's the #2 item on my priority list right now. (but then when I posted this item it was the #1 item... then a fire came up I had to go fight.)
http://wall-art.smugmug.com/
Hey folks, just wanted to let everyone know that after a long visit with our QA team, and a bunch of little fixes, this went live tonight. You can see the full excruciating details of what's in the new CSVs in the wiki: http://wiki.smugmug.net/display/SmugMug/pro+sales+download+csv+format
And see them with your own data in your sales history:
https://secure.smugmug.com/sales/history.mg
http://wall-art.smugmug.com/
Portfolio • Workshops • Facebook • Twitter
Pictures | Website | Blog | Twitter | Contact
Thanks.
I can now get the info I need by summing a few columns.