Find It metrics

metrics.sql

--monthly count of unique visitors
SELECT COUNT(DISTINCT visitor_id) FROM visits WHERE started_at > "2016-01-01" AND started_at < "2016-02-01";

--monthly count of unique searches
SELECT COUNT(id) FROM searches WHERE created_at > "2016-01-01" AND created_at < "2016-02-01";

-- Average length (in minutes) for each session
-- Not available at this time (without adding end_time to session)

-- A quarterly list of searches that return 0-5 results


-- A monthly report that includes % of total searches that return under 10 results

-- Relative popularity of certain facets
SELECT COUNT (id) FROM searches WHERE query_params LIKE '%is_electronic_facet%';
SELECT COUNT (id) FROM searches WHERE query_params LIKE '%format%';
SELECT COUNT (id) FROM searches WHERE query_params LIKE '%pub_date_sort%';
SELECT COUNT (id) FROM searches WHERE query_params LIKE '%language_facet%';
SELECT COUNT (id) FROM searches WHERE query_params LIKE '%lc_1letter_facet%%';
SELECT COUNT (id) FROM searches WHERE query_params LIKE '%subject_era_facet%%';
SELECT COUNT (id) FROM searches WHERE query_params LIKE '%subject_topic_facet%%';
SELECT COUNT (id) FROM searches WHERE query_params LIKE '%subject_geo_facet%%';
SELECT COUNT (id) FROM searches WHERE query_params LIKE '%subject_name_facet%%';
SELECT COUNT (id) FROM searches WHERE query_params LIKE '%record_source_facet%%';


-- % of searches that use facets

-- Percentage of users who return within three months
-- Not actually very helpful because ahoy is very conservative about merging users

-- Most popular referring domains
SELECT referring_domain, COUNT(id) AS num FROM visits GROUP BY referring_domain ORDER BY num DESC LIMIT 5;