Gale Analytics - Patron Profile and Collection Intelligence

\timing on
\set search_path to gale_analytics, public, evergreen;
\set county '''sample_county_name'''
\set orgunit '''sample_org_unit'''
\set parentorg '''sample_parent_org_unit'''

drop table if exists gale;
drop table if exists apts;

-- patron profile data
create table gale as
select aua.street1, aua.street2, aua.city, aua.state, aua.post_code, circ.id as circ_id, circ.xact_start,
acl.name as "copy_location", ac.circ_modifier
from action.circulation circ
join actor.usr au on au.id = circ.usr
join actor.usr_address aua on aua.id = au.mailing_address
join actor.org_unit child_org on child_org.id = au.home_ou
join actor.org_unit parent_org on parent_org.id = child_org.parent_ou
join asset.copy ac on ac.id = circ.target_copy
join asset.copy_location acl on acl.id = circ.copy_location
where parent_org.shortname = :orgunit and circ.xact_start > now() - interval '3 years'
-- where child_org.shortname = :orgunit  and circ.xact_start > now() - interval '3 years'
-- several of these optional restrictions are to help them create more geographically shaped data
-- and (aua.post_code ilike '%' or aua.post_code ilike '%')
-- and aua.county ilike :county
and aua.post_code != '' and street1 != ''
;

-- data for the collection development module
insert into gale (street1, street2, city, state, post_code, circ_id, xact_start, copy_location, circ_modifier)  (
    select aua.street1, aua.street2, aua.city, aua.state, aua.post_code, circ.id, circ.circ_time, nct.name, nct.name
    from action.non_cataloged_circulation circ
    join actor.usr au on au.id = circ.patron
    join actor.usr_address aua on aua.id = au.mailing_address
    join actor.org_unit child_org on child_org.id = au.home_ou
    join actor.org_unit parent_org on parent_org.id = child_org.parent_ou
    join config.non_cataloged_type nct on nct.id = circ.item_type
    where parent_org.shortname = :parent_org and circ.circ_time > now() - interval '3 years'
    and nct.name not ilike 'Kinderbox%'
    -- where child_org.shortname = 'YCL-BK'  and circ.circ_time > now() - interval '3 years'
    -- and (aua.post_code ilike '%' or aua.post_code ilike '%')
    -- and aua.county ilike :county
    and aua.post_code != '' and street1 != ''
);

update gale set copy_location = 'STATS ONLY' where circ_modifier = copy_location;

alter table gale add column id serial;

-- swap apartments lines so gale will read them
create table apts as
select id, substring(street1 from '^(.+?),') as street, substring(street1 from ',.*$') as apt,
street1, street2 from gale where street1 ilike '%,%'
and street1 ilike '%apt%';
update gale rg set street1 = ra.apt from (select id, apt from apts) ra where ra.id = rg.id;
update gale rg set street2 = ra.street from (select id, street from apts) ra where ra.id = rg.id;
update gale set street1 = trim(leading ',' from street1), street2 = trim(leading ',' from street2) where circ_id is not null;
update gale set street1 = trim(leading ' ' from street1), street2 = trim(leading ' ' from street2) where circ_id is not null;
update gale set street1 = trim(trailing ',' from street1), street2 = trim(trailing ',' from street2) where circ_id is not null;
update gale set street1 = trim(trailing ' ' from street1), street2 = trim(trailing ' ' from street2) where circ_id is not null;

-- cleans up SIP activity not tied to any discernable account
-- should be redundant now with the criteria set above but still here just in case
delete from gale where post_code = '99999';

-- cleanup as these will get kicked otherwise
update gale set street2 = null where street2 = '';


-- select * from gale where id in (select id from apts);
-- test

-- adding in the electronic activity for collection development
insert into gale (street1, street2, city, state, post_code, circ_id, xact_start, copy_location, circ_modifier)  (
        select aua.street1, aua.street2, aua.city, aua.state, aua.post_code, usr_act.id, usr_act.event_time, act_type.ewho, act_type.ewho
        from actor.usr_activity usr_act
        join actor.usr au on au.id = usr_act.usr
        join actor.usr_address aua on aua.id = au.mailing_address
        join actor.org_unit child_org on child_org.id = au.home_ou
        join actor.org_unit parent_org on parent_org.id = child_org.parent_ou
        join config.usr_activity_type act_type on act_type.id = usr_act.etype
        where parent_org.shortname = :parentorg and usr_act.event_time > now() - interval '3 months'
        and aua.county ilike :orgunit and aua.post_code != '' and street1 != ''
        and act_type.ehow = 'sip2' and act_type.ewhat = 'verify'
        -- and act_type.ewho = 'overdrive'
);


-- now let's clean up shelving to be a cleaner audience expector
-- a major pain because of other library's locations

update gale set copy_location = 'Adult' where copy_location ilike 'Adult%';
update gale set copy_location = 'Juvenile' where copy_location ilike 'Juv%';
update gale set copy_location = 'Teen' where copy_location ilike 'Teen%';
update gale set copy_location = 'Teen' where copy_location ilike 'YA%';
update gale set copy_location = 'Juvenile' where copy_location ilike 'Child%';
update gale set copy_location = 'Juvenile' where copy_location ilike 'Easy%';
update gale set copy_location = 'Teen' where copy_location ilike 'Young%';
update gale set copy_location = 'Juvenile' where copy_location ilike 'Board%';
update gale set copy_location = 'Adult' where copy_location not in ('Adult','Juvenile','Teen','SIP2');

-- just to see if we got everything
select count(circ_modifier), copy_location from gale where copy_location not in ('Adult','Juvenile','Teen','SIP2') group by 2;

-- ----------------------------------------------------------------------------------------------------------
-- Patron profiles gale report ------------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------


select street1 as "ADDRESS", street2 as "SUITE (OPTIONAL)", city as "CITY", state as "STATE", post_code as "ZIP CODE",
count(circ_id) as "CHECKOUTS (OPTIONAL)", max(date(xact_start)) as "DATE"
from gale
-- where state ilike 'sc' or state ilike 'nc'
-- where post_code ilike '297%'
group by 1, 2, 3, 4, 5;
-- remember after pasting into excel to remove the 'null's with actual blanks, stupid copying and pasting

-- ----------------------------------------------------------------------------------------------------------
-- collection intelligence gale report ----------------------------------------------------------------------
-- only generating the first file, second file not needed ---------------------------------------------------
-- ----------------------------------------------------------------------------------------------------------

-- \o collection_intelligence.txt

select rg.circ_modifier as "FORMAT", rg.copy_location as "SUBJECT/GENRE", street1 as "STREET ADDRESS",
street2 as "STREET ADDRESS LINE 2", city as "CITY", state as "STATE", post_code as "ZIP"
from gale rg
where rg.circ_modifier is not null
;