sessions
3 years ago
create_city.txt
3 years ago
create_country.txt
3 years ago
create_referrer.txt
3 years ago
create_resource.txt
3 years ago
create_view.txt
3 years ago
get_campaign.txt
3 years ago
get_campaigns.txt
3 years ago
get_cities.txt
3 years ago
get_city.txt
3 years ago
get_countries.txt
3 years ago
get_country.txt
3 years ago
get_current_traffic.txt
3 years ago
get_referrer.txt
3 years ago
get_referrers.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_campaigns.txt
56 lines
| 1 | SELECT |
| 2 | * |
| 3 | FROM ( |
| 4 | SELECT |
| 5 | GROUP_CONCAT(DISTINCT campaigns.campaign_id) AS campaign_ids, |
| 6 | initial_view_resource.id AS resource_id, |
| 7 | cached_title AS title, |
| 8 | utm_source, |
| 9 | utm_medium, |
| 10 | utm_campaign, |
| 11 | utm_term, |
| 12 | utm_content, |
| 13 | SUM( IF(sessions.created_at BETWEEN :start AND :end, views.views, NULL)) AS views, |
| 14 | COUNT(DISTINCT IF(sessions.created_at BETWEEN :start AND :end, sessions.visitor_id, NULL)) AS visitors, |
| 15 | COUNT(DISTINCT IF(sessions.created_at BETWEEN :start AND :end, sessions.session_id, NULL)) AS sessions, |
| 16 | COUNT(DISTINCT IF(sessions.created_at BETWEEN :start AND :end AND sessions.final_view_id IS NULL, sessions.session_id, NULL)) AS bounces, |
| 17 | AVG( IF(sessions.created_at BETWEEN :start AND :end, TIMESTAMPDIFF(SECOND, sessions.created_at, sessions.ended_at), NULL)) AS average_session_duration, |
| 18 | SUM( IF(sessions.created_at BETWEEN :prev_start AND :prev_end, views.views, NULL)) AS prev_period_views, |
| 19 | COUNT(DISTINCT IF(sessions.created_at BETWEEN :prev_start AND :prev_end, sessions.visitor_id, NULL)) AS prev_period_visitors, |
| 20 | COUNT(DISTINCT IF(sessions.created_at BETWEEN :prev_start AND :prev_end, sessions.session_id, NULL)) AS previous_period_sessions, |
| 21 | AVG( IF(sessions.created_at BETWEEN :prev_start AND :prev_end, TIMESTAMPDIFF(SECOND, sessions.created_at, sessions.ended_at), NULL)) AS prev_period_average_session_duration, |
| 22 | SUM( IF(sessions.created_at BETWEEN :start AND :end, views.orders, NULL)) AS wc_orders, |
| 23 | SUM( IF(sessions.created_at BETWEEN :start AND :end, views.gross_sales, NULL)) AS wc_gross_sales, |
| 24 | SUM( IF(sessions.created_at BETWEEN :start AND :end, views.total_refunds, NULL)) AS wc_refunds, |
| 25 | SUM( IF(sessions.created_at BETWEEN :start AND :end, views.total_refunded, NULL)) AS wc_refunded_amount |
| 26 | FROM |
| 27 | wp_independent_analytics_sessions AS sessions |
| 28 | JOIN wp_independent_analytics_campaigns AS campaigns ON sessions.campaign_id = campaigns.campaign_id |
| 29 | JOIN wp_independent_analytics_views AS initial_view ON sessions.initial_view_id = initial_view.id |
| 30 | JOIN wp_independent_analytics_resources AS initial_view_resource ON initial_view.resource_id = initial_view_resource.id |
| 31 | JOIN ( |
| 32 | SELECT |
| 33 | sessions.session_id, |
| 34 | COUNT(DISTINCT views.id) AS views, |
| 35 | COUNT(DISTINCT wc_orders.order_id) AS orders, |
| 36 | IFNULL(SUM(wc_orders.total), 0) AS gross_sales, |
| 37 | IFNULL(SUM(wc_orders.total_refunded), 0) AS total_refunded, |
| 38 | IFNULL(SUM(wc_orders.total_refunds), 0) AS total_refunds |
| 39 | FROM |
| 40 | wp_independent_analytics_sessions AS sessions |
| 41 | JOIN wp_independent_analytics_views AS views ON sessions.session_id = views.session_id |
| 42 | LEFT JOIN wp_independent_analytics_wc_orders AS wc_orders ON views.id = wc_orders.view_id AND wc_orders.status IN('wc-completed', 'completed', 'wc-processing', 'processing', 'wc-refunded', 'refunded') |
| 43 | WHERE |
| 44 | sessions.created_at BETWEEN :prev_start AND :end AND views.viewed_at BETWEEN :prev_start AND :end |
| 45 | GROUP BY |
| 46 | sessions.session_id) AS views ON sessions.session_id = views.session_id |
| 47 | WHERE |
| 48 | sessions.created_at BETWEEN :prev_start AND :end |
| 49 | GROUP BY |
| 50 | campaigns.campaign_id, |
| 51 | initial_view_resource.id |
| 52 | ORDER BY |
| 53 | visitors DESC) subquery |
| 54 | WHERE |
| 55 | views > 0 OR prev_period_views > 0 |
| 56 |