Counting visitors on AAQ

gistfile1.sql

-- Count first step of AAQ. Asking them to register or sign-in (only if not logged in yet)
select ds, count(1) as visitors
FROM research_logs
WHERE
ds >= '2012-10-10'
AND `domain`='support.mozilla.com'
AND ip_address != 'NULL' AND http_version = 200 AND request_type = 'POST'
AND parse_url(concat('http://a.com',request_url),'PATH') RLIKE '\\/questions\\/'
AND parse_url(concat('http://a.com',request_url),'QUERY','step') = 'aaq-register'
AND user_agent NOT LIKE '%bot%'
GROUP BY ds;

-- Count second step of AAQ. Asking for question (everyone gets this)
select ds, count(1) as visitors
FROM research_logs
WHERE
ds >= '2012-10-10'
AND `domain`='support.mozilla.com'
AND ip_address != 'NULL' AND http_version = 200 AND request_type = 'GET'
AND parse_url(concat('http://a.com',request_url),'PATH') RLIKE '\\/questions\\/'
AND parse_url(concat('http://a.com',request_url),'QUERY','step') = 'aaq-question'
AND user_agent NOT LIKE '%bot%'
GROUP BY ds;

-- Count last page of AAQ (only those who haven't approved email address yet)
select ds, count(1) as visitors
FROM research_logs
WHERE
ds >= '2012-10-10'
AND `domain`='support.mozilla.com'
AND ip_address != 'NULL' AND http_version = 200 AND request_type = 'GET'
AND parse_url(concat('http://a.com',request_url),'PATH') RLIKE '\\/questions/new/confirm'
AND user_agent NOT LIKE '%bot%'
GROUP BY ds;