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_campaigns.txt
47 lines
| 1 | SELECT * |
| 2 | FROM (SELECT GROUP_CONCAT(DISTINCT campaigns.campaign_id) as campaign_ids, |
| 3 | cached_title AS title, |
| 4 | utm_source, |
| 5 | utm_medium, |
| 6 | utm_campaign, |
| 7 | utm_term, |
| 8 | utm_content, |
| 9 | COUNT(DISTINCT session_views.id) AS views, |
| 10 | COUNT(DISTINCT session_views.visitor_id) AS visitors, |
| 11 | COUNT(DISTINCT session_views.session_id) AS sessions, |
| 12 | COUNT(DISTINCT previous_session_views.id) AS prev_views, |
| 13 | COUNT(DISTINCT previous_session_views.visitor_id) AS prev_visitors, |
| 14 | COUNT(DISTINCT previous_session_views.session_id) AS prev_sessions, |
| 15 | 0 AS wc_orders, |
| 16 | 0 AS wc_gross_sales, |
| 17 | 0 AS wc_refunds, |
| 18 | 0 AS wc_refunded_amount |
| 19 | FROM wp_independent_analytics_campaigns AS campaigns |
| 20 | LEFT JOIN wp_independent_analytics_sessions AS sessions |
| 21 | ON campaigns.campaign_id = sessions.campaign_id |
| 22 | LEFT JOIN wp_independent_analytics_views AS initial_views |
| 23 | ON initial_views.id = sessions.initial_view_id |
| 24 | LEFT JOIN wp_independent_analytics_resources AS resources |
| 25 | ON resources.id = initial_views.resource_id |
| 26 | LEFT JOIN wp_independent_analytics_views AS views |
| 27 | ON sessions.session_id = views.session_id |
| 28 | LEFT JOIN (SELECT views.id, |
| 29 | sessions.visitor_id, |
| 30 | sessions.session_id |
| 31 | FROM wp_independent_analytics_sessions AS sessions |
| 32 | LEFT JOIN wp_independent_analytics_views AS views |
| 33 | ON sessions.session_id = views.session_id |
| 34 | WHERE views.viewed_at BETWEEN :start AND :end) AS session_views |
| 35 | ON views.id = session_views.id |
| 36 | LEFT JOIN (SELECT views.id, |
| 37 | sessions.visitor_id, |
| 38 | sessions.session_id |
| 39 | FROM wp_independent_analytics_sessions AS sessions |
| 40 | LEFT JOIN wp_independent_analytics_views AS views |
| 41 | ON sessions.session_id = views.session_id |
| 42 | WHERE views.viewed_at BETWEEN :prev_start AND :prev_end) AS previous_session_views |
| 43 | ON views.id = previous_session_views.id |
| 44 | GROUP BY campaigns.campaign_id, |
| 45 | resources.id |
| 46 | ORDER BY visitors DESC, views DESC) subquery |
| 47 | WHERE views > 0 |