Portal Stats

Here are some of the CartoDB queries used, extracted from https://github.com/VertNet/webapp/blob/feature/jot-stats/vertnet/service/tasks/daily_portal_stats.py

Counts of queries and records downloaded by month

select concat(year,'-',month) as date, queries, records from ( select extract(month from date(created_at)) as month, extract(year from date(created_at)) as year, count(*) as queries, sum(count) as records from query_log_master where type='download' group by extract(month from date(created_at)), extract(year from date(created_at)) order by extract(year from date(created_at)), extract(month from date(created_at))) as foo

Explicit stuff for pie charts

select query, sum(count) from query_log_master group by query

Records viewed by resource

select results_by_resource from query_log_master where client='portal-prod' and results_by_resource is not null

Searches and records by type

select type, count(*) as searches, sum(count) as records from query_log_master group by type