How do I easily see original download statistics?

jfriend · December 8, 2006

I just put up 15 galleries associated with a soccer season. I'm looking for a simple way to regularly see what originals are being downloaded from those 15 galleries. I can go to the download statistics page from my control panel, but that's a giant page that lists every single gallery in my account. To see what originals are being downloaded, I have to find each of the 15 galleries and then open each one and then scan each image in each gallery for a number by the originals downloaded line. It's next to impossible as there are nearly 1000 images in the 15 galleries.

I've tried the download statistics that I can get through Star Explorer, but I can't figure out how to make that useful. It's got album IDs and image IDs, but nothing that shows me a thumbnail next to an originals downloaded statistic.

My ideal solution would be a report that shows (for the last month) the number of originals downloaded for each image sorted by number of originals downloaded and including a thumbnail for each image. The report would look like this:

Image                     Filename         # Originals Downloaded
--------------            ---------        -----------------------
[Thumbnail 1]             JLF_8592.JPG               12
[Thumbnail 2]             JLF_8588.JPG                9
[Thumbnail 3]             JLF_8422.JPG                7

Is anything like this possible?

Andy · December 8, 2006

jfriend wrote:

I've tried the download statistics that I can get through Star Explorer, but I can't figure out how to make that useful. It's got album IDs and image IDs, but nothing that shows me a thumbnail next to an originals downloaded statistic.

This should be quite useful, you can use the image id's and just plug them into URLs like this:

http://jfriend.smugmug.com/photos/XXXXXX-Th.jpg

or

http://jfriend.smugmug.com/gallery/XXXXXXX/1/XXXXXXX/Small

I'm sure that a hacker could put something together pretty easily, with the data you get from Star Explorer. Perhaps a post in the hacks forum would be helpful.

jfriend · December 9, 2006

Andy wrote:

This should be quite useful, you can use the image id's and just plug them into URLs like this:

http://jfriend.smugmug.com/photos/XXXXXX-Th.jpg

or

http://jfriend.smugmug.com/gallery/XXXXXXX/1/XXXXXXX/Small

I'm sure that a hacker could put something together pretty easily, with the data you get from Star Explorer. Perhaps a post in the hacks forum would be helpful.

I can't figure it out. Star Explorer leaves you with some sort of multi-table Jet database. I can find a table with image downloads in it, but there are no image IDs in it that let me tell what image is what. That leads me to believe it would take some sort of multi-table query in some sort of database tool that I've never used before (which isn't what I was hoping the answer would be). I'll go over to the hacks forum and see if someone else has tried to solve this there.

Andy · December 9, 2006

jfriend wrote:

I can't figure it out. Star Explorer leaves you with some sort of multi-table Jet database. I can find a table with image downloads in it, but there are no image IDs in it that let me tell what image is what. That leads me to believe it would take some sort of multi-table query in some sort of database tool that I've never used before (which isn't what I was hoping the answer would be). I'll go over to the hacks forum and see if someone else has tried to solve this there.

Yeah and I'll make sure Nik sees this, too

Nikolai · December 9, 2006

Starts report

Andy wrote:

Yeah and I'll make sure Nik sees this, too

Andy, thanks for the tip!

John,
here's a query for you, feel free to modify as you wish:

SELECT 
ALB.F_ALBUM_NAME, 
IMG.F_IMAGE_NAME, 
IMG.F_FILE_NAME, 
Sum(ST_IMG.F_LARGE_HITS) AS LARGE_HITS, 
Sum(ST_IMG.F_ORIGINAL_HITS) AS ORIGINAL_HITS
FROM 
(T_ALBUMS AS ALB INNER JOIN T_IMAGES AS IMG ON ALB.F_ALBUM_ID = IMG.F_ALBUM_ID) 
INNER JOIN T_STATS_IMAGE AS ST_IMG ON IMG.F_IMAGE_ID = ST_IMG.F_IMAGE_ID
WHERE 
(((ST_IMG.F_YEAR)=2006) AND ((ST_IMG.F_MONTH)=12))
GROUP BY 
ALB.F_ALBUM_NAME, IMG.F_IMAGE_NAME, IMG.F_FILE_NAME;

If you have MS Access, you can create a new one yourself. Just start query wizard.
Names are in ALBUMS and IMAGES, stat data are in, well, STATS_ALBUM and STATS_IMAGE.
All the constraints are already in DB itself, so all you need to do is to pick up fields and some criteria maybe...

HTH

jfriend · December 9, 2006

Nikolai wrote:
Andy, thanks for the tip!

John,
here's a query for you, feel free to modify as you wish:
SELECT 
ALB.F_ALBUM_NAME, 
IMG.F_IMAGE_NAME, 
IMG.F_FILE_NAME, 
Sum(ST_IMG.F_LARGE_HITS) AS LARGE_HITS, 
Sum(ST_IMG.F_ORIGINAL_HITS) AS ORIGINAL_HITS
FROM 
(T_ALBUMS AS ALB INNER JOIN T_IMAGES AS IMG ON ALB.F_ALBUM_ID = IMG.F_ALBUM_ID) 
INNER JOIN T_STATS_IMAGE AS ST_IMG ON IMG.F_IMAGE_ID = ST_IMG.F_IMAGE_ID
WHERE 
(((ST_IMG.F_YEAR)=2006) AND ((ST_IMG.F_MONTH)=12))
GROUP BY 
ALB.F_ALBUM_NAME, IMG.F_IMAGE_NAME, IMG.F_FILE_NAME;
If you have MS Access, you can create a new one yourself. Just start query wizard.
Names are in ALBUMS and IMAGES, stat data are in, well, STATS_ALBUM and STATS_IMAGE.
All the constraints are already in DB itself, so all you need to do is to pick up fields and some criteria maybe...

HTH

Since I have never used MS Access (though my company puts it on my laptop as part of the office suite), I'm pretty lost as to what to do with this info. I'm not asking anyone to teach me how to use Access so I end up wishing there was an easier way to do this in Smugmug.

Nikolai · December 9, 2006

John,

jfriend wrote:

Since I have never used MS Access (though my company puts it on my laptop as part of the office suite), I'm pretty lost as to what to do with this info. I'm not asking anyone to teach me how to use Access so I end up wishing there was an easier way to do this in Smugmug.

Juts try it, worst case you'll lose 5 mins.

Download the stats.
At the end S*E will ask you if you want to open the DB. Say yes...
Select Queries in Objects
Click New
Click Close.
You will see a bold word SQL in the second toolbar just under File menu. Click it.
You will see an empty "Query1 : Select Query" window with "SELECT;" text inside.
Replace that text with the one I provided earlier.
In the middle of the same toolbar where the SQL word was you'll see a bright red exclamation mark [ ! ] button. Click it.
That's all. You can select and copy any part of the data, or go back to the Design amd modify your SQL.

HTH

Nikolai · December 9, 2006

Reports, etc.
If somebody would ask me to name one thing in the whole programming which is simultaneously very easy and very hard to do, my immediate answer would be "reports".

The main issue is called "variety". There are no two people in the whole wide world who would request identical report from the identical set of data. As they say, "the devil is in the details". Different fields, different criteria, different sorting and grouping options... Each particular report may be an extremely easy thing to do, but the problem is: nobody, except one person, needs that particular one, and what's worse - even the person who originally requested it will request a minor (or worse, major) change the very next minute s/he'll see the results.

Creating a report that would satisfy everybody is impossible. Hence developers came out with tools that allow the end users design and modify reports that suite their particular needs. Unfortunately, shifting the workload and responsibilities around did not eliminate the issues.
Simplistic - and relatively easy to learn - report building tools can't always satisfy some advanced requests, while the more powerful ones require a lot of time to master them. People make living out of mastering such tools.

While I personally am not aware of any silver bullet in reporting area, I'd say MS Access reporting capabilities are fairly close to it.
Yes, it does take some time to figure out how to link two or more tables, but I'd say that MS Access learning curve is way less steep and much shorter than Photoshop one.
Of course, it may differ from person to person, but here's my personal stats.

It took me about 3 months and 3 books to start feeling comfortable doing basic things in PS, and it took me 12 more months and 6 more books (including Dan's:-) to get to the level I'm now (at which I can accomplish about 95% of all the "tasks" mentioned in Scott Kelby books without even thinking hard).

It took me less than 15 minutes (long time ago) to run my first Access report, and I think it took me about 1 month and 1 or 2 decent online SQL guide to be able to run way more sophisticated ones. All you need to have is a common sense and some urge to use it.

Both post-processing and reporting have a couple things in common:
both are required for a professional work and both require either some education to do yourself or a thick wallet to pay somebody else to do it for you.

I apologize for the rambling, but I felt compelled to express my point of view on the subject.

HTH

jfriend · December 9, 2006

Thanks.

Nikolai wrote:

Juts try it, worst case you'll lose 5 mins.
Download the stats.

At the end S*E will ask you if you want to open the DB. Say yes...

Select Queries in Objects

Click New

Click Close.

You will see a bold word SQL in the second toolbar just under File menu. Click it.

You will see an empty "Query1 : Select Query" window with "SELECT;" text inside.

Replace that text with the one I provided earlier.

In the middle of the same toolbar where the SQL word was you'll see a bright red exclamation mark [ ! ] button. Click it.

That's all. You can select and copy any part of the data, or go back to the Design amd modify your SQL.

HTH

Thanks Nikolai. I got the basic query to run in Access so I can see a report that says how many originals were downloaded in each gallery. But, it doesn't show me which images they were and, even if it showed me the filenames, I'd need thumbnails in order to know which ones they were in any sort of efficient manner. This is already way more work for everyone than it's worth to me since mostly I was just trying to see which pictures the soccer parents thought were the best (guaged by which ones they downloaded originals for) - just a curiousity.

I wish I could offer zero price digital downloads and get the sales order view in Smugmug for them just like I do for print orders. That would solve my problem here - just wanting to know which originals people went for.

Andy · December 9, 2006

jfriend wrote:

I wish I could offer zero price digital downloads and get the sales order view in Smugmug for them just like I do for print orders. That would solve my problem here - just wanting to know which originals people went for.

But you don't get the print reporting unless you charge at least a penny over. You can do the same with Digital Downloads, if you wish?

jfriend · December 9, 2006

Andy wrote:

But you don't get the print reporting unless you charge at least a penny over. You can do the same with Digital Downloads, if you wish?

Yeah, I know - that's obviously why I can't do it now. I've long considered it a missing feature that prints have to be marked up in order to get sales reporting for pros. I have all mine marked up a penny just to get sales records so I can see what people buy. But, I usually make originals available so lots of my viewers go for the originals and do with them what they want. So, the real summary of what people like on my site needs to include the originals they download. It is currently next to impossible to track that.

The fact that prints have to be marked up in order to get sales tracking isn't the way one would conciously design a system to behave (requiring a markup in order to get reports) so it's probably a side effect of how your system works and how the feature can most easily be implemented, not a purposeful design decision. If that issue was fixed then the digital download request could work and we could get sales reporting on zero priced digital downloads.

Nikolai · December 9, 2006

John,

jfriend wrote:

Thanks Nikolai. I got the basic query to run in Access so I can see a report that says how many originals were downloaded in each gallery. But, it doesn't show me which images they were and, even if it showed me the filenames, I'd need thumbnails in order to know which ones they were in any sort of efficient manner. This is already way more work for everyone than it's worth to me since mostly I was just trying to see which pictures the soccer parents thought were the best (guaged by which ones they downloaded originals for) - just a curiousity.

I wish I could offer zero price digital downloads and get the sales order view in Smugmug for them just like I do for print orders. That would solve my problem here - just wanting to know which originals people went for.

if you look just one minute over the tables structure you'd see the image_smids, which are just one step away from the URLS. :-)...

HTH

jfriend · December 10, 2006

Nikolai wrote:

if you look just one minute over the tables structure you'd see the image_smids, which are just one step away from the URLS. :-)

I get that the data is in there. It's just more work to get out than it's worth to me in the first place. Thanks for trying.

How do I easily see original download statistics?

Comments