Campaigns.php — Independent Analytics – WordPress Analytics Plugin 2.14.2

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

Campaigns.php

80 lines

1	<?php
2
3	namespace IAWP\Rows;
4
5	use IAWP\Form_Submissions\Form;
6	use IAWP\Illuminate_Builder;
7	use IAWP\Models\Campaign;
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 Campaigns extends \IAWP\Rows\Rows
15	{
16	public function attach_filters(Builder $query) : void
17	{
18	$query->joinSub($this->get_filter_query(), 'campaign_rows', function (JoinClause $join) {
19	$join->on('campaign_rows.campaign_id', '=', 'sessions.campaign_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 Campaign($row);
27	}, $rows);
28	}
29	protected function sort_tie_breaker_column() : string
30	{
31	return 'title';
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	$campaigns_table = Query::get_table_name(Query::CAMPAIGNS);
40	$session_statistics = $this->session_statistics_query()->whereNotNull('sessions.campaign_id');
41	$campaigns_query = Illuminate_Builder::new();
42	$campaigns_query->select('campaigns.campaign_id', 'landing_pages.title', 'utm_sources.utm_source', 'utm_mediums.utm_medium', 'utm_campaigns.utm_campaign', 'utm_term', 'utm_content')->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($campaigns_query->raw($campaigns_table . ' AS campaigns'), function (JoinClause $join) {
47	$join->on('sessions.campaign_id', '=', 'campaigns.campaign_id');
48	})->join($campaigns_query->raw(Tables::landing_pages() . ' AS landing_pages'), function (JoinClause $join) {
49	$join->on('campaigns.landing_page_id', '=', 'landing_pages.id');
50	})->join($campaigns_query->raw(Tables::utm_sources() . ' AS utm_sources'), function (JoinClause $join) {
51	$join->on('campaigns.utm_source_id', '=', 'utm_sources.id');
52	})->join($campaigns_query->raw(Tables::utm_mediums() . ' AS utm_mediums'), function (JoinClause $join) {
53	$join->on('campaigns.utm_medium_id', '=', 'utm_mediums.id');
54	})->join($campaigns_query->raw(Tables::utm_campaigns() . ' AS utm_campaigns'), function (JoinClause $join) {
55	$join->on('campaigns.utm_campaign_id', '=', 'utm_campaigns.id');
56	})->when(!$this->appears_to_be_for_real_time_analytics(), function (Builder $query) {
57	$query->whereBetween('sessions.created_at', $this->get_current_period_iso_range());
58	})->when(\is_int($this->solo_record_id), function (Builder $query) {
59	$query->where('campaigns.campaign_id', '=', $this->solo_record_id);
60	})->groupBy('campaigns.campaign_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) {
61	$query->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $this->sort_configuration->column()));
62	});
63	$previous_period_query = Illuminate_Builder::new();
64	$previous_period_query->select(['sessions.campaign_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.campaign_id');
65	$outer_query = Illuminate_Builder::new();
66	$outer_query->selectRaw('campaigns.')->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) {
67	foreach (Form::get_forms() as $form) {
68	$query->selectRaw("IF(visitors = 0, 0, ({$form->submissions_column()} / visitors) * 100) AS {$form->conversion_rate_column()}");
69	}
70	})->fromSub($campaigns_query, 'campaigns')->leftJoinSub($previous_period_query, 'previous_period_stats', 'campaigns.campaign_id', '=', 'previous_period_stats.campaign_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) {
71	$query->tap(fn(Builder $query) => $this->apply_order_and_limit($query, $this->sort_configuration->column()));
72	});
73	if ($this->using_logical_or_operator() && $this->filtering_by_mixed_columns()) {
74	$og_outer_query = $outer_query;
75	$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()));
76	}
77	return $outer_query;
78	}
79	}
80