Customizing sales reports
tmanchester
Registered Users Posts: 95 Big grins
Even though I'm now a full time photographer, for seventeen years I had a career in sales management and marketing. One of the most useful and informative parts of a sales report is tracking where the sales come from.
It would be very helpful if there were a way to do this via the down-loadable sales reports, but I have not been able to figure out how to do this. The CSV file that is available today has a column that shows the image, and that shows a link to the page on which that image appears--of course this lets you know the gallery from which that image originated, but not "directly".
Now using Excel you can sort the image column, of course, and this "groups" the galleries from which there have been orders. However, that is not very intuitive, since you then need to manually go to that gallery to figure out the name of it (and what event was shot). I have almost 1,000 rows in my CSV file (a number which will only continue to grow), and this makes for a lot of clicking around. I think some refer to this as "screen scraping", and it could take forever.
That field looks like this:
http://username.smugmug.com/gallery/1196806/42/55982898
The number after "gallery/" represents the actual gallery number, and the number after the next slash is the image number.
Using Excel, is there a way to strip the image number after the gallery number? Keep in mind that there are potentially hundreds or thousands of unique fields like the one above, and you only want to strip the numbers after the last slash. I'm not sure, but I think if you were able to do that, you could then sort them by gallery number more easily.
My way of thinking is that you might also create another worksheet where you manually put in the gallery names, along with the corresponding smugmug assigned gallery number. Then there could possibly be a look up to the cell with the gallery name that could then post totals (est profit, # of prints ordered, etc). Every time you create a new gallery, you add that name & number to this worksheet, and by pasting the newly downloaded sales report, you could somehow see the updated totals.
Has anyone been able to devise an intuitive way to figure out what galleries (or better yet, categories) are producing the most orders and profits? There are a number of reasons one would like to do this, e.g. tracking the profits for events so that a percentage rebate can be the associated sports leagues, or deciding whether or not to continue shooting certain leagues.
Does anyone have any suggestions on how I can massage the current sales report? BTW, my wife is a financial analyst, and much more accomplished at using Excel than I am, but she couldn't figure out a good way to do this either. She said it might be able to be done with Access, but we don't have it or know how to use it.
Thanks in advance!
It would be very helpful if there were a way to do this via the down-loadable sales reports, but I have not been able to figure out how to do this. The CSV file that is available today has a column that shows the image, and that shows a link to the page on which that image appears--of course this lets you know the gallery from which that image originated, but not "directly".
Now using Excel you can sort the image column, of course, and this "groups" the galleries from which there have been orders. However, that is not very intuitive, since you then need to manually go to that gallery to figure out the name of it (and what event was shot). I have almost 1,000 rows in my CSV file (a number which will only continue to grow), and this makes for a lot of clicking around. I think some refer to this as "screen scraping", and it could take forever.
That field looks like this:
http://username.smugmug.com/gallery/1196806/42/55982898
The number after "gallery/" represents the actual gallery number, and the number after the next slash is the image number.
Using Excel, is there a way to strip the image number after the gallery number? Keep in mind that there are potentially hundreds or thousands of unique fields like the one above, and you only want to strip the numbers after the last slash. I'm not sure, but I think if you were able to do that, you could then sort them by gallery number more easily.
My way of thinking is that you might also create another worksheet where you manually put in the gallery names, along with the corresponding smugmug assigned gallery number. Then there could possibly be a look up to the cell with the gallery name that could then post totals (est profit, # of prints ordered, etc). Every time you create a new gallery, you add that name & number to this worksheet, and by pasting the newly downloaded sales report, you could somehow see the updated totals.
Has anyone been able to devise an intuitive way to figure out what galleries (or better yet, categories) are producing the most orders and profits? There are a number of reasons one would like to do this, e.g. tracking the profits for events so that a percentage rebate can be the associated sports leagues, or deciding whether or not to continue shooting certain leagues.
Does anyone have any suggestions on how I can massage the current sales report? BTW, my wife is a financial analyst, and much more accomplished at using Excel than I am, but she couldn't figure out a good way to do this either. She said it might be able to be done with Access, but we don't have it or know how to use it.
Thanks in advance!
0
Comments
I asked Tom to post this here, in the hopes that one of you would be willing to look at this as a possible hack.
Portfolio • Workshops • Facebook • Twitter
For my sins (and there must have been a lot of them) I spent a while writing sophesticated Excel Macros to provide real time trading support for power stations. So unfortunately I know more about Visual Basic for Applications than I care to...
Try the nasty hack below. If it doesn't work, then if you can send me a CSV file I can through something together for you that will pre-process it in no time.
Right now I don't have time to write something that can do proper sales reports analysis, though it won't be hard. Excel can talk to .NET and .NET will be able to talk to Smugmug through the wonders of the SmugTools libraries (currently they talk, but it's more like a stuttering monkey).
So we can just ask the API for the information. We can either do it in an application or we can do it as a workbook in Excel. I would say that I'll be able to do that in ~July. Unfortunately right now I'm up to my eyes in work....
So, if someone can send me the CSV file you'll have a nasty hack by the end of the week and a proper solution by the middle of year. Or alternativly, use the bad hack below and let me know...
Would this help?
Luke
SmugSoftware: www.smugtools.com
Take the following piece of mystical Excel gibberish and put it in the cell next to the one containing the URL
=VALUE(LEFT(RIGHT(A1, LEN(A1) - (FIND("gallery/",A1,1)+7)), FIND("/", RIGHT(A1, LEN(A1) - (FIND("gallery/",A1,1)+7))) - 1))Then update all the values of A1 with the cell reference containing your URL and magically the number willl pop out. Then just fill down as usual.
Sample.
URL:
http://username.smugmug.com/gallery/1196806/42/55982898
Result:
1196806
(Copied and pasted from Excel 2003, no cheating has been done...)
Wow, that's a nasty hack....
Hope this helps,
Luke
PS. Andy, does this make me l33t?
SmugSoftware: www.smugtools.com
That'll be up to Tom to decide
Portfolio • Workshops • Facebook • Twitter
I don't find that nasty at all... That's a pretty standard usage of the Excel built-in functions. If you put all your URLs in column A, and put Luke's function in cell B1, then you click B1 with Luke's function and drag the little box in the lower right corner down, you'll find that Excel automatically puts A2s instead of A1s in the function in cell B2, and A3s in B3, etc.
Excel's functions are amazingly powerful. Change a single cell, and watch as your entire sheet recalculates instantly.
{digression}
If you drag the little box to the right, it uses B1 in cell C1 and C1 in cell D1 (not quite what you want). For a formula like this, you'd want to use $A instead of A to tell Excel to not change Column A when copying the formula. The same could apply to $1 instead of 1 if you were doing something involving a calculation that always went against something in row 1.
{/digression}
-Mike
I cut and pasted, but only got the dreaded "#VALUE!"
After I get that to work, how hard would it be to do a lookup of the actual gallery name and display that in addition to (or instead of) the gallery number?
Thanks again for all of the help!
Sure, I've replied to that with the fixed version. It was just the reference needing updating.
So there are two options here:
-> Build a lookup table in Excel and have it do the lookup on that table. This isn't very hard but requires you to manually keep the table in sync. Bit naff and requires maintence
-> Have Excel do the lookup via the Smugmug API and cache it. That's the right way to do it (IMHO) but requires a fair dose of technology developing. I can't get this done very quickly, not because of the difficulty, just a lack of time.
Might I suggest just doing it manually until summer when I have time to write you a proper version?
My pleasure.
Luke
SmugSoftware: www.smugtools.com
Yo Luke,
Can this be used by others now?
Portfolio • Workshops • Facebook • Twitter
You've got email Andy.
It could be something others could use without too much extra effort...
Luke
SmugSoftware: www.smugtools.com
For you:
But the Pro version of Star*Explorer allows you do way more than that, and without too much of a hassle. Some of those high volume pro photogs who used it told me a lot of nice things about that feature...
You can check the FAQ items on the S*E site, especially if you want to have something like this in a matter of a few clicks:
HTH
Nik, this is great info too. Thanks for that!
Portfolio • Workshops • Facebook • Twitter
I understand that's not enough to get you and the other high-class people switch to wintel, but at least for some of us less fortunate it can be handy
Cheers, my friend!