Cities.php — Independent Analytics – WordPress Analytics Plugin 2.14.2

independent-analytics / IAWP / Rows / Cities.php

independent-analytics / IAWP / Rows Last commit date

Campaign_Landing_Pages.php 5 months ago Campaign_UTM_Campaigns.php 5 months ago Campaign_UTM_Mediums.php 5 months ago Campaign_UTM_Sources.php 5 months ago Campaigns.php 5 months ago Cities.php 5 months ago Countries.php 5 months ago Device_Browsers.php 5 months ago Device_OSS.php 5 months ago Device_Types.php 5 months ago Filter.php 5 months ago Forms.php 5 months ago Journeys.php 5 months ago Link_Patterns.php 5 months ago Links.php 5 months ago Pages.php 5 months ago Referrer_Types.php 5 months ago Referrers.php 5 months ago Rows.php 5 months ago

Cities.php

74 lines

1	<?php
2
3	namespace IAWP\Rows;
4
5	use IAWP\Form_Submissions\Form;
6	use IAWP\Illuminate_Builder;
7	use IAWP\Models\Geo;
8	use IAWP\Query;
9	use IAWP\Query_Taps;
10	use IAWPSCOPED\Illuminate\Database\Query\Builder;
11	use IAWPSCOPED\Illuminate\Database\Query\JoinClause;
12	/** @internal */
13	class Cities extends \IAWP\Rows\Rows
14	{
15	public function attach_filters(Builder $query) : void
16	{
17	$query->joinSub($this->get_filter_query(), 'city_rows', function (JoinClause $join) {
18	$join->on('city_rows.city_id', '=', 'sessions.city_id');
19	});
20	}
21	protected function fetch_rows() : array
22	{
23	$rows = $this->query()->get()->all();
24	return \array_map(function ($row) {
25	return new Geo($row);
26	}, $rows);
27	}
28	protected function sort_tie_breaker_column() : string
29	{
30	return 'city';
31	}
32	protected function query(?bool $skip_pagination = \false) : Builder
33	{
34	if ($skip_pagination) {
35	$this->number_of_rows = null;
36	}
37	$sessions_table = Query::get_table_name(Query::SESSIONS);
38	$countries_table = Query::get_table_name(Query::COUNTRIES);
39	$cities_table = Query::get_table_name(Query::CITIES);
40	$session_statistics = $this->session_statistics_query()->whereNotNull('sessions.city_id');
41	$cities_query = Illuminate_Builder::new();
42	$cities_query->select('countries.country_id', 'countries.country_code', 'countries.country', 'countries.continent', 'cities.subdivision', 'cities.city', 'cities.city_id')->selectRaw('IFNULL(CAST(SUM(sessions.views) AS SIGNED), 0) AS views')->selectRaw('COUNT(DISTINCT sessions.visitor_id) AS visitors')->selectRaw('COUNT(DISTINCT sessions.session_id) AS sessions')->selectRaw('ROUND(AVG( TIMESTAMPDIFF(SECOND, sessions.created_at, sessions.ended_at))) AS average_session_duration')->selectRaw('COUNT(DISTINCT IF(sessions.final_view_id IS NULL, sessions.session_id, NULL)) AS bounces')->selectRaw('SUM(sessions.clicks) AS clicks')->selectRaw('SUM(sessions.wc_orders) AS wc_orders')->selectRaw('SUM(sessions.wc_gross_sales) AS wc_gross_sales')->selectRaw('SUM(sessions.wc_refunded_amount) AS wc_refunded_amount')->selectRaw('SUM(sessions.wc_refunds) AS wc_refunds')->selectRaw('SUM(sessions.form_submissions) AS form_submissions')->tap(function (Builder $query) {
43	foreach (Form::get_forms() as $form) {
44	$query->selectRaw("SUM(sessions.{$form->submissions_column()}) AS {$form->submissions_column()}");
45	}
46	})->fromSub($session_statistics, 'sessions')->join($cities_query->raw($cities_table . ' AS cities'), function (JoinClause $join) {
47	$join->on('sessions.city_id', '=', 'cities.city_id');
48	})->join($cities_query->raw($countries_table . ' AS countries'), function (JoinClause $join) {
49	$join->on('cities.country_id', '=', 'countries.country_id');
50	})->when(!$this->appears_to_be_for_real_time_analytics(), function (Builder $query) {
51	$query->whereBetween('sessions.created_at', $this->get_current_period_iso_range());
52	})->when(\is_int($this->solo_record_id), function (Builder $query) {
53	$query->where('cities.city_id', '=', $this->solo_record_id);
54	})->groupBy('cities.city_id')->having('views', '>', 0)->tap(fn(Builder $query) => $this->apply_record_filters($query))->when($this->can_order_and_limit_at_record_level(), function (Builder $query) {
55	$query->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $this->sort_configuration->column()));
56	});
57	$previous_period_query = Illuminate_Builder::new();
58	$previous_period_query->select(['sessions.city_id'])->selectRaw('SUM(sessions.total_views) AS previous_period_views')->selectRaw('COUNT(DISTINCT sessions.visitor_id) AS previous_period_visitors')->from($sessions_table, 'sessions')->tap(Query_Taps::tap_related_to_examined_record_for_previous_period($this->examiner_config, ['sessions']))->whereBetween('sessions.created_at', $this->get_previous_period_iso_range())->groupBy('sessions.city_id');
59	$outer_query = Illuminate_Builder::new();
60	$outer_query->selectRaw('cities.')->selectRaw('IF(sessions = 0, 0, views / sessions) AS views_per_session')->selectRaw('IFNULL((views - previous_period_views) / previous_period_views 100, 0) AS views_growth')->selectRaw('IFNULL((visitors - previous_period_visitors) / previous_period_visitors * 100, 0) AS visitors_growth')->selectRaw('IFNULL(bounces / sessions * 100, 0) AS bounce_rate')->selectRaw('ROUND(CAST(wc_gross_sales - wc_refunded_amount AS SIGNED)) AS wc_net_sales')->selectRaw('IF(visitors = 0, 0, (wc_orders / visitors) * 100) AS wc_conversion_rate')->selectRaw('IF(visitors = 0, 0, (wc_gross_sales - wc_refunded_amount) / visitors) AS wc_earnings_per_visitor')->selectRaw('IF(wc_orders = 0, 0, ROUND(CAST(wc_gross_sales / wc_orders AS SIGNED))) AS wc_average_order_volume')->selectRaw('IF(visitors = 0, 0, (form_submissions / visitors) * 100) AS form_conversion_rate')->tap(function (Builder $query) {
61	foreach (Form::get_forms() as $form) {
62	$query->selectRaw("IF(visitors = 0, 0, ({$form->submissions_column()} / visitors) * 100) AS {$form->conversion_rate_column()}");
63	}
64	})->fromSub($cities_query, 'cities')->leftJoinSub($previous_period_query, 'previous_period_stats', 'cities.city_id', '=', 'previous_period_stats.city_id')->tap(fn(Builder $query) => $this->apply_aggregate_filters($query))->when(!$this->can_order_and_limit_at_record_level() && !($this->using_logical_or_operator() && $this->filtering_by_mixed_columns()), function (Builder $query) {
65	$query->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $this->sort_configuration->column()));
66	});
67	if ($this->using_logical_or_operator() && $this->filtering_by_mixed_columns()) {
68	$og_outer_query = $outer_query;
69	$outer_query = Illuminate_Builder::new()->select('*')->fromSub($og_outer_query, 'records')->tap(fn(Builder $query) => $this->apply_or_filters($query))->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $this->sort_configuration->column()));
70	}
71	return $outer_query;
72	}
73	}
74