Referrer_Types.php — Independent Analytics – WordPress Analytics Plugin 2.14.2

independent-analytics / IAWP / Rows / Referrer_Types.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

Referrer_Types.php

76 lines

1	<?php
2
3	namespace IAWP\Rows;
4
5	use IAWP\Form_Submissions\Form;
6	use IAWP\Illuminate_Builder;
7	use IAWP\Models\Referrer_Type;
8	use IAWP\Query;
9	use IAWP\Query_Taps;
10	use IAWP\Tables;
11	use IAWPSCOPED\Illuminate\Database\Query\Builder;
12	use IAWPSCOPED\Illuminate\Database\Query\JoinClause;
13	/** @internal */
14	class Referrer_Types extends \IAWP\Rows\Rows
15	{
16	public function attach_filters(Builder $query) : void
17	{
18	$query->joinSub($this->get_filter_query(), 'referrer_type_rows', function (JoinClause $join) {
19	$join->on('referrer_type_rows.referrer_type_id', '=', 'referrers.referrer_type_id');
20	});
21	}
22	protected function fetch_rows() : array
23	{
24	$rows = $this->query()->get()->all();
25	return \array_map(function ($row) {
26	return new Referrer_Type($row);
27	}, $rows);
28	}
29	protected function sort_tie_breaker_column() : string
30	{
31	return 'referrer_type';
32	}
33	protected function query(?bool $skip_pagination = \false) : Builder
34	{
35	if ($skip_pagination) {
36	$this->number_of_rows = null;
37	}
38	$sessions_table = Query::get_table_name(Query::SESSIONS);
39	$referrers_table = Query::get_table_name(Query::REFERRERS);
40	$session_statistics = $this->session_statistics_query()->whereNotNull('sessions.referrer_id');
41	$referrer_types_query = Illuminate_Builder::new();
42	$referrer_types_query->select('referrer_types.id AS referrer_type_id', 'referrer_types.referrer_type')->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')->leftJoin($referrer_types_query->raw($referrers_table . ' AS referrers'), function (JoinClause $join) {
47	$join->on('sessions.referrer_id', '=', 'referrers.id');
48	})->leftJoin($referrer_types_query->raw(Tables::referrer_types() . ' AS referrer_types'), function (JoinClause $join) {
49	$join->on('referrers.referrer_type_id', '=', 'referrer_types.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('referrer_types.id', '=', $this->solo_record_id);
54	})->groupBy(['referrers.referrer_type_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(['referrers.referrer_type_id AS referrer_type_id'])->selectRaw('SUM(sessions.total_views) AS previous_period_views')->selectRaw('COUNT(DISTINCT sessions.visitor_id) AS previous_period_visitors')->from($sessions_table, 'sessions')->leftJoin($referrer_types_query->raw($referrers_table . ' AS referrers'), function (JoinClause $join) {
59	$join->on('sessions.referrer_id', '=', 'referrers.id');
60	})->tap(Query_Taps::tap_related_to_examined_record_for_previous_period($this->examiner_config, ['sessions', 'referrers']))->whereBetween('sessions.created_at', $this->get_previous_period_iso_range())->groupBy(['referrers.referrer_type_id']);
61	$outer_query = Illuminate_Builder::new();
62	$outer_query->selectRaw('referrer_types.')->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) {
63	foreach (Form::get_forms() as $form) {
64	$query->selectRaw("IF(visitors = 0, 0, ({$form->submissions_column()} / visitors) * 100) AS {$form->conversion_rate_column()}");
65	}
66	})->fromSub($referrer_types_query, 'referrer_types')->leftJoinSub($previous_period_query, 'previous_period_stats', 'referrer_types.referrer_type_id', '=', 'previous_period_stats.referrer_type_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) {
67	$query->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $this->sort_configuration->column()));
68	});
69	if ($this->using_logical_or_operator() && $this->filtering_by_mixed_columns()) {
70	$og_outer_query = $outer_query;
71	$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()));
72	}
73	return $outer_query;
74	}
75	}
76