Skip to content

Instantly share code, notes, and snippets.

@atadams

atadams/piwik_referer_domain.sql

Created February 7, 2017 14:29
Show Gist options
  • Save atadams/45504a8b115efbfe74349aa3f79a90cd to your computer and use it in GitHub Desktop.
Save atadams/45504a8b115efbfe74349aa3f79a90cd to your computer and use it in GitHub Desktop.
Download ZIP
Piwik Referer Domain
Raw
piwik_referer_domain.sql
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(referer_url, '://', -1), '/', 1), 'www.', -1) AS domain,
count(*) AS visits
FROM piwik_log_visit
WHERE
visit_first_action_time BETWEEN '2017-01-01' AND '2017-02-01'
AND referer_url NOT LIKE '%effectivehealthcare.ahrq.go%'
GROUP BY domain
ORDER BY visits DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment