Usage statistics?

[Paul Hoffman]

I'm hoping that there's a way to get more granular download statistics
without having to visit each and every item in a repository. Can anyone
suggest an SQL query or some other way of getting at these numbers?

Thanks in advance,

Paul.

--
Paul Hoffman <pa...@flo.org>
Systems Librarian
Fenway Libraries Online
c/o Wentworth Institute of Technology
550 Huntington Ave.
Boston, MA 02115
(617) 445-2914
(617) 442-2384 (FLO main number)

[Nate Sarr]

Hi Paul,

 

    What kind of numbers are you looking for - list of downloads per item - do you need date ranges?

 

Best Wishes,

-Nate

--
You received this message because you are subscribed to the Google Groups "irplus" group.
To post to this group, send email to irp...@googlegroups.com.
To unsubscribe from this group, send email to irplus+un...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/irplus?hl=en.

[Paul Hoffman]

Nate,

Yes, we're hoping for the number of downloads per item for a particular
date range. Lacking that, the total number of downloads per item (all
items in one fell swoop).

Paul.

On Wed, May 16, 2012 at 10:58:20AM -0400, Nate Sarr wrote:
> Hi Paul,
>
> What kind of numbers are you looking for - list of downloads per item - do you need date ranges?
>
> Best Wishes,
> -Nate
>

> On Tue, May 15, 2012 at 1:57 PM, Paul Hoffman <pa...@flo.org<mailto:pa...@flo.org>> wrote:
> I'm hoping that there's a way to get more granular download statistics
> without having to visit each and every item in a repository. Can anyone
> suggest an SQL query or some other way of getting at these numbers?
>
> Thanks in advance,
>
> Paul.
>
> --

> Paul Hoffman <pa...@flo.org<mailto:pa...@flo.org>>

> Systems Librarian
> Fenway Libraries Online
> c/o Wentworth Institute of Technology
> 550 Huntington Ave.
> Boston, MA 02115

> (617) 445-2914<tel:%28617%29%20445-2914>
> (617) 442-2384<tel:%28617%29%20442-2384> (FLO main number)

>
> --
> You received this message because you are subscribed to the Google Groups "irplus" group.

> To post to this group, send email to irp...@googlegroups.com<mailto:irp...@googlegroups.com>.
> To unsubscribe from this group, send email to irplus+un...@googlegroups.com<mailto:irplus%2Bunsu...@googlegroups.com>.

> For more options, visit this group at http://groups.google.com/group/irplus?hl=en.
>
>
>
> --
> You received this message because you are subscribed to the Google Groups "irplus" group.
> To post to this group, send email to irp...@googlegroups.com.
> To unsubscribe from this group, send email to irplus+un...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/irplus?hl=en.

[Nate Sarr]

Hi Paul,

Sorry for the delay - below is a select that should do it - with date ranges.  However it will not include items that have no downloads.  Give it a try and let me know if the numbers don't look correct.  You can use the inst_id to find the item.  For example your urls should look like the following with a different domain when browsing to an item from the item browse:

https://urresearch3.lib.rochester.edu/institutionalPublicationPublicView.action?institutionalItemId=4927 

just replace the 4927 with the inst_id value in the column it will bring you to the item to double check.  One thing we do here is we write the select data out to a file and load it into excel so people can play with the numbers.  Let me know if you need to do this and need a hand. 

select item.name as item_title,
sum(file_download_info.count) as downloads,
date_of_deposit as deposit_date,
institutional_collection.name as collection,
institutional_item.institutional_item_id as inst_id
from ir_item.item,
     ir_item.item_file,
     ir_file.ir_file,
     ir_repository.institutional_item_version,
     ir_repository.versioned_institutional_item,
     ir_repository.institutional_item,
     ir_repository.institutional_collection,
     ir_statistics.file_download_info
where  item.item_id = institutional_item_version.item_id
and item.item_id = item_file.item_id
and item_file.ir_file_id = ir_file.ir_file_id
and ir_file.ir_file_id = file_download_info.ir_file_id
and institutional_item_version.versioned_institutional_item_id = versioned_institutional_item.versioned_institutional_item_id
and institutional_item.versioned_institutional_item_id = versioned_institutional_item.versioned_institutional_item_id
and institutional_item.institutional_collection_id = institutional_collection.institutional_collection_id
and download_date between '2011-05-01' and '2011-11-30'
group by item_title, inst_id, deposit_date, collection
order by downloads desc, item.name asc


-Nate