magento-visitor-analytics.sql

-- Totals per month
select
    s.name `Store View`,
    date_format(u.visit_time, "%Y-%m") Period,
    count(distinct vi.remote_addr) `Unique Visits`,
    sum(vi.http_user_agent regexp 'bot|spider|w3c|validat|crawl|monitor|php') `Total Visits from Bots`,
    count(*) `Total Visits` -- Warning: slow
from log_url u
inner join log_visitor_info vi
    on vi.visitor_id = u.visitor_id
inner join log_url_info i
    on i.url_id = u.url_id
inner join log_visitor v
    on v.visitor_id = vi.visitor_id
inner join core_store s
    on s.store_id = v.store_id
where u.visit_time > now() - interval 6 month
group by Period, v.store_id
order by `Store View`, Period;

-- Top pages per day last month
select
    s.name `Store View`,
    date_format(u.visit_time, "%Y-%m-%d") Period,
    count(distinct vi.remote_addr) `Unique Visits`,
    count(*) Visits,
    replace(url, concat(substring_index(url, "/", 3), "/"), "") Page
from log_url u
inner join log_visitor_info vi
    on vi.visitor_id = u.visitor_id
inner join log_url_info i
    on i.url_id = u.url_id
inner join log_visitor v
    on v.visitor_id = vi.visitor_id
inner join core_store s
    on s.store_id = v.store_id
where u.visit_time > now() - interval 1 month
group by Period, v.store_id, Page
order by Period DESC, `Unique Visits` DESC, `Store View`;