city_statistics.txt
3 years ago
city_statistics_with_woocommerce.txt
3 years ago
country_statistics.txt
3 years ago
create_referrer.txt
3 years ago
create_resource.txt
3 years ago
create_session.txt
3 years ago
create_view.txt
3 years ago
create_visitor.txt
3 years ago
get_campaign.txt
3 years ago
get_campaigns.txt
3 years ago
get_campaigns_with_woocommerce.txt
3 years ago
get_current_session.txt
3 years ago
get_current_traffic.txt
3 years ago
get_referrer.txt
3 years ago
get_referrers.txt
3 years ago
get_referrers_with_woocommerce.txt
3 years ago
get_resource.txt
3 years ago
get_resources.txt
3 years ago
get_visitors_by_minute_interval.txt
3 years ago
get_visitors_by_ten_second_interval.txt
3 years ago
get_referrers.txt
36 lines
| 1 | SELECT * |
| 2 | FROM (SELECT GROUP_CONCAT(DISTINCT sessions.referrer_id) as referrer_ids, |
| 3 | IFNULL(referrer_groups.name, referrers.domain) as group_name, |
| 4 | GROUP_CONCAT(DISTINCT IFNULL(referrer_groups.domain, referrers.domain)) as domain, |
| 5 | GROUP_CONCAT(DISTINCT referrer_groups.type) as type, |
| 6 | COUNT(CASE WHEN views.viewed_at BETWEEN :start AND :end THEN 1 END) AS views, |
| 7 | COUNT(DISTINCT (CASE |
| 8 | WHEN views.viewed_at BETWEEN :start AND :end |
| 9 | THEN sessions.visitor_id END)) AS visitors, |
| 10 | COUNT(DISTINCT (CASE |
| 11 | WHEN views.viewed_at BETWEEN :start AND :end |
| 12 | THEN sessions.session_id END)) AS sessions, |
| 13 | COUNT(CASE |
| 14 | WHEN views.viewed_at BETWEEN :prev_start AND :prev_end |
| 15 | THEN 1 END) AS prev_period_views, |
| 16 | COUNT(DISTINCT (CASE |
| 17 | WHEN views.viewed_at BETWEEN :prev_start AND :prev_end |
| 18 | THEN sessions.visitor_id END)) AS prev_period_visitors, |
| 19 | COUNT(DISTINCT (CASE |
| 20 | WHEN views.viewed_at BETWEEN :prev_start AND :prev_end |
| 21 | THEN sessions.session_id END)) AS prev_period_sessions, |
| 22 | 0 AS wc_orders, |
| 23 | 0 AS wc_gross_sales, |
| 24 | 0 AS wc_refunds, |
| 25 | 0 AS wc_refunded_amount |
| 26 | FROM wp_independent_analytics_views AS views |
| 27 | LEFT JOIN wp_independent_analytics_sessions AS sessions |
| 28 | ON views.session_id = sessions.session_id |
| 29 | LEFT JOIN wp_independent_analytics_referrers AS referrers |
| 30 | ON sessions.referrer_id = referrers.id |
| 31 | LEFT JOIN wp_independent_analytics_referrer_groups AS referrer_groups |
| 32 | ON referrers.domain = referrer_groups.domain_to_match |
| 33 | WHERE views.viewed_at BETWEEN :prev_start AND :end |
| 34 | GROUP BY group_name |
| 35 | ORDER BY visitors DESC, views DESC) subquery |
| 36 | WHERE views > 0 |