<?php
/**
*
* High level class that fetches all data, mainly used for reports but can be accessed directly
* 
* @package Alpha Insights
* @version 2.0.0
* @author WPDavies
* @link https://wpdavies.dev/
*
*/
defined( 'ABSPATH' ) || exit;
class WPD_Data_Warehouse {
/**
*
*  The filter used for filtering data
* 
*  ['date_format_display'] = Holds the date format for grouping
*  ['start_date] = Holds the start date to filter by
*  ['end_date] = Holds the end date to filter by
*   
*/
private array $filter = array();
/**
*
*  Data by date containers
*
*/
private array $data_by_date_containers = array();
/**
* 
*  Stores any errors in request, useful for debugging
* 
**/
private array $errors = array();
/**
*
*  All data that has been fetched will be stored in this array
* 
*  Associative array with keys for each data segment and a breakdown of totals, data & data_by_date
*  Only store what we need so that we don't overdo the memory
* 
*/
private array $data = array(
'analytics' => array(
'totals' => array(),
'raw_data' => array(),
'organised_data' => array(),
'data_by_date' => array(),
'total_db_records' => 0,
),
'product_impressions' => array(
'totals' => array(),
'raw_data' => array(),
'organised_data' => array(),
'data_by_date' => array(),
'total_db_records' => 0
),
'anonymous_queries' => array(), // If we make arbitrary SQL queries, they can go in here
'total_db_records' => 0 // Stores all records fetched
);
/** 
*   Initialize the constructor for the data warehouse. Will load the filters & date containers on initialization
* 
*   @param array $filter The filter to be used on the data, expected to be passed as an array at this point
*   @param array $data_by_date_containers An array of dates with empty values to use for reports
*
**/
public function __construct( $filter = array() ) {
// Configure Filter
$this->set_filter( $filter );
// Setup data by date
$this->set_data_by_date_containers();
}
/**
*
*  Setup props, loads filter and date containers in if they are set
* 
* @param array $filter
* @param array $data_by_date_containers
* @return null
*
*/
private function set_filter( $filter ) {
$this->filter = $filter;
}
/**
*
*  Setup the date shells
*
*/
private function set_data_by_date_containers() {
// Fetch details
$max_date       = $this->get_selected_date_range('end_date');    // date in the past
$min_date       = $this->get_selected_date_range('start_date');  // current date
$n_days_period  = $this->get_n_days_range();
// Set Default Display Format
if ( ! isset($this->filter['date_format_display']) ) $this->filter['date_format_display'] = 'day';
/**
*
*  Construct date data
*
*/
if ( $this->filter['date_format_display'] == 'day' ) {
$date_range = $this->get_date_range_array($min_date, $max_date, '+1 day', 'Y-m-d' );
$date_format = 'Y-m-d';
} elseif ( $this->filter['date_format_display'] == 'month' ) {
$date_format = 'M Y';
$date_range = $this->get_date_range_array($min_date, $max_date, '+1 day', $date_format );
} elseif ( $this->filter['date_format_display'] == 'quarter' ) {
$date_format = 'M Y';
$date_range = $this->get_date_range_array($min_date, $max_date, '+3 months', $date_format ); // Changed step
} elseif ( $this->filter['date_format_display'] == 'year' ) {
$date_format = 'Y';
$date_range = $this->get_date_range_array($min_date, $max_date, '+1 year', $date_format );
} else {
$date_format = 'Y-m-d';
$date_range = $this->get_date_range_array($min_date, $max_date, '+1 day', $date_format );
}
$calculations_by_day = array(
'Mon' => 0,
'Tue' => 0,
'Wed' => 0,
'Thu' => 0,
'Fri' => 0,
'Sat' => 0,
'Sun' => 0,
);
$calculations_by_time = array(
'12am' => 0,
'1am' => 0,
'2am' => 0,
'3am' => 0,
'4am' => 0,
'5am' => 0,
'6am' => 0,
'7am' => 0,
'8am' => 0,
'9am' => 0,
'10am' => 0,
'11am' => 0,
'12pm' => 0,
'1pm' => 0,
'2pm' => 0,
'3pm' => 0,
'4pm' => 0,
'5pm' => 0,
'6pm' => 0,
'7pm' => 0,
'8pm' => 0,
'9pm' => 0,
'10pm' => 0,
'11pm' => 0,
);
$date_formatting_container = array();
foreach ( $date_range as $date_array_val ) {
$date_formatting_container[$date_array_val] = array(
'x' =>  $date_array_val,
'y' =>  0           
);
}
// Return data
$data_by_date_containers = array(
'n_days_period' => $n_days_period,
'date_format' => $date_format,
'date_from' => $min_date,
'date_to' => $max_date,
'date_range_container' => $date_formatting_container,
'calculations_by_day' => $calculations_by_day,
'calculations_by_time' => $calculations_by_time
);
// Set data by date containers
$this->data_by_date_containers = $data_by_date_containers;
// Return data by date containers
return $data_by_date_containers;
}
/**
* 
*  Stores data in the data prop
*  
*  @param string $data_type The relevant data type, i.e. expense, orders, etc etc (see init $data prop for available types)
*  @param array $data The data to pass in, must be one of: totals, raw_data, organised_data, data_by_date
* 
**/
private function set_data( $data_type, $data ) {
// Data type passed is incorrect
if ( ! is_string($data_type) ) {
$this->set_error( 'Trying to set data, ' . $data_type . ' is not a string.' );
return false;
}
// Data payload is incorrect
if ( ! is_array($data) ) {
$this->set_error( 'Trying to set data, ' . $data . ' is not an array.' );
return false;
}
// Allowed keys to be set into array
$allowed_data_keys = array( 'totals', 'raw_data', 'organised_data', 'data_by_date', 'total_db_records' );
// Store the data
foreach( $data as $key => $data ) {
// Only store allowed keys
if ( ! in_array( $key, $allowed_data_keys ) ) {
$this->set_error( 'Trying to set data, ' . $key . ' is not an allowed key.' );
continue;
}
// Store the key
$this->data[$data_type][$key] = $data;
}
// Update the total record count
$this->set_total_db_records();
}
/**
* 
*  Checks all stored db records and sets the data property accordingly
* 
**/
public function set_total_db_records() {
// Get the array keys
$array_keys = array_keys( $this->get_data() );
// Set the default count
$count = 0;
// Loop through the keys
foreach( $array_keys as $data_type ) {
if ( isset($this->data[$data_type]['total_db_records']) ) {
$count += $this->data[$data_type]['total_db_records'];
}
}
// Update our data
$this->data['total_db_records'] = $count;
// Return result
return $count;
}
/**
* 
*  Sets an error in the class
* 
**/
private function set_error( $error ) {
// This method does most of the error handling
$this->log( $error, true );
}
/**
* 
*  Returns an associative array of all date meta being used
* 
**/
public function get_date_meta_data() {
$date_meta = array(
'start_date' => $this->get_start_date(),
'end_date' => $this->get_end_date(),
'n_days_period' => $this->get_n_days_range(),
'date_format_display' => 'day',
'time_parser' => 'yyyy-MM-dd'
);
// Overrides
if ( isset($this->filter['date_format_display']) ) {
$date_meta['date_format_display'] = $this->filter['date_format_display'];
}
// Overrides
if ( isset($this->filter['time_parser']) ) {
$date_meta['time_parser'] = $this->filter['time_parser'];
}
// Return
return $date_meta;
}
/**
* 
*  Returns data by date containers
* 
**/
public function get_data_by_date_containers() {
// Set the containers if not set yet
if ( empty($this->data_by_date_containers) ) {
$this->set_data_by_date_containers();
}
return $this->data_by_date_containers;
}
/**
* 
*  Gets the start date we are using for this request
* 
**/
public function get_start_date( $format = 'Y-m-d' ) {
return $this->get_selected_date_range('start_date', $format);
}
/**
* 
*  Gets the start date we are using for this request
* 
**/
public function get_end_date( $format = 'Y-m-d' ) {
return $this->get_selected_date_range('end_date', $format);
}
/**
*
*  Returns selected date range
*  Accepts start_date or end_date
* 
*  @todo Get rid of this and move it into get_start_date() and get_end_date()
*
*/
public function get_selected_date_range( $result = 'start_date', $format = 'Y-m-d' ) {
$days_in_past   = (string) '-' . $this->get_n_days_range() . ' days';
$wp_timestamp   = current_time( 'timestamp' );
if ( $result == 'start_date' ) {
$start = date($format, strtotime( $days_in_past, $wp_timestamp ) ); // this needs to be based on wp time as below
if ( isset( $this->filter['start_date'] ) && ! empty($this->filter['start_date']) ) {
$start = date( $format, strtotime($this->filter['start_date']) );
}
return $start;
} elseif ( $result == 'end_date' ) {
$end = current_time( $format ); 
if ( isset($this->filter['end_date']) && ! empty($this->filter['end_date']) ) {
$end = date( $format, strtotime($this->filter['end_date']));
}
return $end;
}
}
/**
*
*  @return array $dates Returns array of dates within defined range
*
*/
public function get_date_range_array( $first, $last, $step = '+1 day', $output_format = 'Y-m-d' ) {
$dates              = array();
$current_date       = strtotime($first);
$end_date           = strtotime($last);
while( $current_date <= $end_date ) {
$dates[] = date($output_format, $current_date);
$current_date = strtotime($step, $current_date);
}
return array_values( array_unique( $dates ) );
}
/**
*
*  Check if we are limiting range by X days
*
*/
public function get_n_days_range() {
// Defaults
$days = 30;
if ( isset( $this->filter['start_date'] ) && isset( $this->filter['end_date'] ) ) {
$start  = new DateTime( $this->filter['start_date'] );
$end    = new DateTime( $this->filter['end_date'] );
// Only execute this if we're sure these are now objects
if ( is_a($start, 'DateTime') && is_a($end, 'DateTime') ) {
$days = $end->diff( $start )->format('%a') + 1;
return $days;
}
}
return $days;
}
/**
* 
*  Returns the currently set filter
* 
*  @return array Returns all active filters on this instance of the class
* 
**/
public function get_filter( $key = null ) {
// Check if they've passed a key in
if ( ! is_null($key) ) {
// Return their key if found
if ( isset($this->filter[$key]) ) {
return $this->filter[$key];
}
// Otherwise false, we couldn't find it
return false;
}
// Return all filters
return $this->filter;
}
/**
* 
*  Data Return Function
* 
*  If no params are set, it will return all data
*  If only the data_type is set, it will return all found data for that data_type
*  If the data_type and data_key are set and found, will return that payload
*  
*  @param string $data_type The data type, i.e. analytics, orders etc..
*  @param string $data_key The data_key to fetch for, will return that specific key's data if found with the data_type. Accepts totals, raw_data, organised_data, data_by_date & total_db_records
* 
*  @return array|bool Returns the requested data or false if we couldn't resolve the request
* 
**/
public function get_data( $data_type = false, $data_key = false ) {
// Insufficient parameters passed, return all data
if ( $data_type === false && $data_key === false ) {
return $this->data;
}
// Return all data for the data type if found and no data_key is set
if ( isset( $this->data[$data_type] ) && $data_key === false ) {
return $this->data[$data_type];
}
// Try and return their target key if found
if ( isset( $this->data[$data_type] ) && isset($this->data[$data_type][$data_key]) ) {
return $this->data[$data_type][$data_key];
}
// Incorrect query
return false;
}
/**
* 
*  Gets all set errors
* 
**/
public function get_errors() {
return $this->errors;
}
/**
* 
*  Gets the total record count
* 
**/
public function get_total_db_records() {
// Found it
if ( isset($this->data['total_db_records']) ) {
return $this->data['total_db_records'];
}
// Doesnt exist?
return false;
}
/**
*
*  Fetches Analytics data from DB and stores it, raw data only no processing.
*  @see get_gmt_from_date() for converting input date
*  @link https://developer.wordpress.org/reference/functions/get_gmt_from_date/
*
*/
public function fetch_analytics_data() {
global $wpdb;
// Collect Vars
$wpd_db = new WPD_Database_Interactor();
$woo_events_table = $wpd_db->events_table;
$session_data_table = $wpd_db->session_data_table;
// Apply filters if required
$where_clause = '';
$filters = $this->get_filter();
if ( isset($filters['start_date']) && isset($filters['end_date']) ) {
$start_date = get_gmt_from_date( $filters['start_date'] );
$end_date = get_gmt_from_date( $filters['end_date'] );
// Add 1 day to end date, so that it's inclusive of actual date
$end_date = date("Y-m-d H:i:s", strtotime('+24 hours', strtotime($end_date)));
$where_clause .= ' AND events.date_created_gmt >= \'' . $start_date . '\'';
$where_clause .= ' AND events.date_created_gmt <= \'' . $end_date . '\'';
}
// Single session ID defined
if ( isset($filters['session_id']) && ! empty($filters['session_id']) && is_string($filters['session_id']) ) {
$where_clause .= ' AND session_data.session_id = \'' . $filters["session_id"] . '\'';
}
// Prepare query
$sql_query = 
"SELECT 
events.session_id,
events.date_created_gmt AS event_date_created_gmt,
events.event_type, 
events.event_quantity,
events.event_value,
events.product_id,
events.variation_id,
events.page_href, 
events.object_type, 
events.object_id, 
session_data.user_id, 
session_data.landing_page,
session_data.referral_url,
session_data.date_created_gmt AS session_date_created_gmt,
session_data.date_updated_gmt AS session_date_updated_gmt,
session_data.device,
session_data.browser,
session_data.ip_address
FROM $woo_events_table AS events
LEFT JOIN $session_data_table AS session_data ON events.session_id = session_data.session_id
WHERE 1=1
$where_clause
ORDER BY events.date_created_gmt ASC";
// Fetch Results
$results = $wpdb->get_results( $sql_query, 'ARRAY_A' );
// DB Error
if ( $wpdb->last_error  ) {
wpd_write_log( 'Error capturing analytics data from DB, dumping the error and query.', 'db_error' );
wpd_write_log( $wpdb->last_error, 'db_error' );
wpd_write_log( $wpdb->last_query, 'db_error' );
$this->set_error( $wpdb->last_error );
return false;
}
// Get count of records from query
$record_count = (is_array($results)) ? count($results) : 0;
// Store results
$this->set_data( 'analytics', array('raw_data' => $results) );
$this->set_data( 'analytics', array('total_db_records' => $record_count) );
// Organise data (This will also update the stored props)
$this->organise_analytics_data();
// Return results (will return all relevant props also stored by organised_analytics_data)
return $this->get_data( 'analytics' );
}
/**
*
*  Prepare Analytics Data, reforms it into a more usable array
*  @todo object not being passed into product page views
*  @todo method of tracking products purchased is okay, but doesnt give me an order value. Need to create an event for order_created or whatever
*  
*/
private function organise_analytics_data() {
// Start prep
$raw_analytics_data = $this->get_data( 'analytics', 'raw_data' );
$organised_data = array();
$session_summary_container = array(
'page_views'                    => 0,
'non_page_view_events'          => 0, // All non-page view events
'category_page_views'           => 0,
'product_clicks'                => 0,
'product_page_views'            => 0,
'add_to_carts'                  => 0,
'add_to_cart_value'             => 0,
'initiate_checkouts'            => 0,
'transactions'                  => 0,
'transaction_value'             => 0,
'product_transaction_value'     => 0,
'unique_products_purchased'     => 0, // Unique line items
'total_products_purchased'      => 0, // Quantity of products purchased
);
$session_container = array(
'summary'       => $session_summary_container,
'session'       => array(),
'events'        => array()
);
$page_view_container = array(
'session_count' => array(),
'user_count'    => array(),
'views'         => 0,
'transactions'  => 0,
'revenue'       => 0
);
$totals = array(
'total_records'                         => 0,
'sessions'                              => 0,
'users'                                 => 0, // (count of unique IP's)
'page_views'                            => 0,
'non_page_view_events'                  => 0, // All non-page view events
'session_duration'                      => 0,
'average_session_duration'              => 0,
'category_page_views'                   => 0,
'sessions_with_category_page_views'     => 0,
'product_clicks'                        => 0,
'product_page_views'                    => 0,
'sessions_with_product_page_views'      => 0,
'add_to_carts'                          => 0,
'add_to_cart_value'                     => 0,
'sessions_with_add_to_cart'             => 0,
'initiate_checkouts'                    => 0,
'sessions_with_initiate_checkout'       => 0,
'transactions'                          => 0,
'sessions_with_transaction'             => 0,
'transaction_value'                     => 0,
'product_transaction_value'             => 0,
'unique_products_purchased'             => 0, // Unique line items
'total_products_purchased'              => 0, // Quantity of products purchased
'event_summary'                         => array(),
'campaign_summary'                      => array(),
'acquisition_summary'                   => array(),
'product_summary'                       => array(),
'page_view_summary'                     => array(),
'landing_page_summary'                  => array(),
'referral_url_summary'                  => array(),
'calculations'                          => array(
'sessions_per_day'                          => 0,
'users_per_day'                             => 0,
'page_views_per_session'                    => 0,
'events_per_session'                        => 0,
'percent_sessions_with_category_view'       => 0.00,
'percent_sessions_with_product_page_view'   => 0.00,
'percent_sessions_with_add_to_cart'         => 0.00,
'percent_sessions_with_initiate_checkout'   => 0.00,
'conversion_rate'                           => 0.00
)
);
$temp_counter = array(
'session_id' => array(),
'ip_address' => array(),
'sessions_by_date' => array(),
'ip_address_by_date' => array()
);
$data_by_date = array(
'sessions_by_date' => $this->data_by_date_containers['date_range_container'], // Unique Per Day
'users_by_date' => $this->data_by_date_containers['date_range_container'], // Unique Per Day
'page_views_by_date' => $this->data_by_date_containers['date_range_container'],
'events_by_date' => $this->data_by_date_containers['date_range_container'],
'category_page_views_by_date' => $this->data_by_date_containers['date_range_container'],
'product_clicks_by_date' => $this->data_by_date_containers['date_range_container'],
'product_page_views_by_date' => $this->data_by_date_containers['date_range_container'],
'add_to_carts_by_date' => $this->data_by_date_containers['date_range_container'],
'transactions_by_date' => $this->data_by_date_containers['date_range_container'],
);
// Check for unique events per session -> store sessions ID and do unique count at the end
$session_unique_array = array(
'sessions_with_category_page_view' => array(),
'sessions_with_product_page_view' => array(),
'sessions_with_add_to_cart' => array(),
'sessions_with_initiate_checkout' => array(),
'sessions_with_transaction' => array()
);
// If we're empty, return defaults.
if ( empty( $raw_analytics_data ) || ! is_array( $raw_analytics_data ) ) {
$analytics_data = array(
'totals' => $totals,
'data_by_date' => $data_by_date,
'organised_data' => $organised_data
);
// Store data
$this->set_data( 'analytics', $analytics_data );
// Basically returns an empty / default array
return $analytics_data;
}
/**
*
*  Perform all calculations
*
*/
foreach( $raw_analytics_data as $event ) {
// Event Data
$session_id                 = $event['session_id'];
$event_date_created_gmt     = $event['event_date_created_gmt'];
$event_type                 = $event['event_type'];
$event_quantity             = $event['event_quantity'];
$event_value                = $event['event_value'];
$product_id                 = $event['product_id'];
$variation_id               = $event['variation_id'];
$page_href                  = $event['page_href'];
$object_type                = $event['object_type'];
$object_id                  = $event['object_id'];
// @todo this should likely be done on submission of data
if ($event_type == 'add_to_cart') $event_value = $event_value * $event_quantity;
// Session Data
$user_id                    = $event['user_id'];
$ip_address                 = $event['ip_address'];
$landing_page               = $event['landing_page'];
$referral_url               = $event['referral_url'];
$session_date_created_gmt   = $event['session_date_created_gmt'];
$session_date_updated_gmt   = $event['session_date_updated_gmt'];
$device                     = $event['device'];
$browser                    = $event['browser'];
// Variable cleaning
$session_duration               = $this->calculate_difference_in_seconds( $session_date_updated_gmt, $session_date_created_gmt );
$event_timestamp_in_local       = get_date_from_gmt( $event_date_created_gmt );
$session_date_created_local     = get_date_from_gmt( $session_date_created_gmt );
$session_date_updated_local     = get_date_from_gmt( $session_date_updated_gmt );
$landing_page_url_components    = $this->get_url_components( $landing_page );
$landing_page_path              = $landing_page_url_components['path'];
$landing_page_query_parameters  = $landing_page_url_components['query_parameters'];
$session_traffic_source         = $this->determine_traffic_source( $referral_url, $landing_page_query_parameters );
$event_page_url_components      = $this->get_url_components( $page_href );
$event_page_path                = $event_page_url_components['path'];
$event_formatted_date           = $this->reformat_date_to_date_format($event_timestamp_in_local); // Formatted for date date
(isset($landing_page_query_parameters['utm_campaign'])) ? $utm_campaign = $landing_page_query_parameters['utm_campaign'] : $utm_campaign = null;
// Setup session container
if ( ! isset($organised_data[$session_id]) ) $organised_data[$session_id] = $session_container;
// Store Events
$organised_data[$session_id]['events'][] = array(
'event_type' => $event_type,
'event_timestamp_in_local' => $event_timestamp_in_local, // need to create
'event_quantity' => $event_quantity,
'event_value' => $event_value,
'event_product_id' => $product_id,
'event_variation_id' => $variation_id,
'event_page_href' => $page_href,
'event_page_path' => $event_page_path,
'object_type' => $object_type,
'object_id' => $object_id
);
// Store Session Meta
$organised_data[$session_id]['session'] = array(
'session_id' => $session_id,
'ip_address' => $ip_address,
'user_id' => $user_id,
'session_start_in_local' => $session_date_created_local,
'session_end_in_local' => $session_date_updated_local,
'session_duration' => $session_duration,
'landing_page' => $landing_page,
'landing_page_path' => $landing_page_path,
'landing_page_query_parameters' => $landing_page_query_parameters,
'landing_page_campaign' => $utm_campaign,
'referral_url' => $referral_url,
'referral_source' => $session_traffic_source,
'device' => $device,
'browser' => $browser,
);
/**
*
*  Calculate session totals
*
*/
// Data Totals
$totals['total_records']++;
/**
*
*  Calculate Totals
* 
*/
// Total Sessions
if ( ! isset($temp_counter['session_id'][$session_id]) ) {
$temp_counter['session_id'][$session_id] = true;
$totals['sessions']++;
$totals['session_duration'] += $session_duration;
}
// Total Users
if ( ! isset($temp_counter['ip_address'][$ip_address]) ) {
$temp_counter['ip_address'][$ip_address] = 1;
$totals['users']++;
}
// Sessions by date
if ( ! isset($temp_counter['sessions_by_date'][$event_formatted_date][$session_id]) ) {
// Temp Counter for unique sessions by date
$temp_counter['sessions_by_date'][$event_formatted_date][$session_id] = 1;
// If this array key has not been setup, set it up
if ( ! isset($data_by_date['sessions_by_date'][$event_formatted_date]['y']) ) $data_by_date['sessions_by_date'][$event_formatted_date]['y'] = 0;
// Increment the event count
$data_by_date['sessions_by_date'][$event_formatted_date]['y']++;
}
// Users by date
if ( ! isset($temp_counter['ip_address_by_date'][$event_formatted_date][$ip_address]) ) {
// Temp counter for unique users per date
$temp_counter['ip_address_by_date'][$event_formatted_date][$ip_address] = 1;
// Setup user count by date if it hasn't been setup
if ( ! isset($data_by_date['users_by_date'][$event_formatted_date]['y']) ) $data_by_date['users_by_date'][$event_formatted_date]['y'] = 0;
// Increment the users by date count
$data_by_date['users_by_date'][$event_formatted_date]['y']++;
}
// Landing Page Data
if ( ! empty($landing_page_path) ) {
if ( ! isset($totals['landing_page_summary'][$landing_page_path]) ) {
$totals['landing_page_summary'][$landing_page_path] = $page_view_container;
}
$totals['landing_page_summary'][$landing_page_path]['session_count'][$session_id] = 1;
$totals['landing_page_summary'][$landing_page_path]['user_count'][$ip_address] = 1;
$totals['landing_page_summary'][$landing_page_path]['views']++;
}
// Referral Page Data
if ( ! empty($referral_url) ) {
if ( ! isset($totals['referral_url_summary'][$referral_url]) ) {
$totals['referral_url_summary'][$referral_url] = $page_view_container;
}
$totals['referral_url_summary'][$referral_url]['session_count'][$session_id] = 1;
$totals['referral_url_summary'][$referral_url]['user_count'][$ip_address] = 1;
$totals['referral_url_summary'][$referral_url]['views']++;
}
// Page Views
if ($event_type == 'page_view') {
$organised_data[$session_id]['summary']['page_views']++;
$totals['page_views']++;
if ( ! isset($data_by_date['page_views_by_date'][$event_formatted_date]['y']) ) $data_by_date['page_views_by_date'][$event_formatted_date]['y'] = 0;
$data_by_date['page_views_by_date'][$event_formatted_date]['y']++;
// Handle total page view data
if ( ! isset($totals['page_view_summary'][$event_page_path]) ) {
$totals['page_view_summary'][$event_page_path] = $page_view_container;
}
$totals['page_view_summary'][$event_page_path]['session_count'][$session_id] = 1;
$totals['page_view_summary'][$event_page_path]['user_count'][$ip_address] = 1;
$totals['page_view_summary'][$event_page_path]['views']++;
}
// Events
if ($event_type != 'page_view') {
$organised_data[$session_id]['summary']['non_page_view_events']++;
$totals['non_page_view_events']++;
if ( ! isset($data_by_date['events_by_date'][$event_formatted_date]['y']) ) $data_by_date['events_by_date'][$event_formatted_date]['y'] = 0;
$data_by_date['events_by_date'][$event_formatted_date]['y']++;
}
// Product Click
if ($event_type == 'product_click') {
$organised_data[$session_id]['summary']['product_clicks']++;
$totals['product_clicks']++;
$data_by_date['product_clicks_by_date'][$event_formatted_date]['y']++;
}
// Product Category Page Views
if ($event_type == 'page_view' && $object_type == 'product_cat') {
$organised_data[$session_id]['summary']['category_page_views']++;
$totals['category_page_views']++;
$data_by_date['category_page_views_by_date'][$event_formatted_date]['y']++;
if ( ! in_array($session_id, $session_unique_array['sessions_with_category_page_view']) ) {
$session_unique_array['sessions_with_category_page_view'][] = $session_id;
$totals['sessions_with_category_page_views']++;
}
}
// Product Page Views
if ($event_type == 'page_view' && $object_type == 'product') {
$organised_data[$session_id]['summary']['product_page_views']++;
$totals['product_page_views']++;
if ( ! isset($data_by_date['product_page_views_by_date'][$event_formatted_date]['y']) ) $data_by_date['product_page_views_by_date'][$event_formatted_date]['y'] = 0;
$data_by_date['product_page_views_by_date'][$event_formatted_date]['y']++;
if ( ! in_array($session_id, $session_unique_array['sessions_with_product_page_view']) ) {
$session_unique_array['sessions_with_product_page_view'][] = $session_id;
$totals['sessions_with_product_page_views']++;
}
}
// Add to cart
if ($event_type == 'add_to_cart') {
$organised_data[$session_id]['summary']['add_to_carts']++;
$organised_data[$session_id]['summary']['add_to_cart_value'] += $event_value;
$totals['add_to_carts']++;
$totals['add_to_cart_value'] += $event_value;
if ( ! isset($data_by_date['add_to_carts_by_date'][$event_formatted_date]['y']) ) $data_by_date['add_to_carts_by_date'][$event_formatted_date]['y'] = 0;
$data_by_date['add_to_carts_by_date'][$event_formatted_date]['y']++;
if ( ! in_array($session_id, $session_unique_array['sessions_with_add_to_cart']) ) {
$session_unique_array['sessions_with_add_to_cart'][] = $session_id;
$totals['sessions_with_add_to_cart']++;
}
}
// Initiate Checkout
if ($event_type == 'init_checkout') {
$organised_data[$session_id]['summary']['initiate_checkouts']++;
$totals['initiate_checkouts']++;
if ( ! in_array($session_id, $session_unique_array['sessions_with_initiate_checkout']) ) {
$session_unique_array['sessions_with_initiate_checkout'][] = $session_id;
$totals['sessions_with_initiate_checkout']++;
}
}
// Purchase - Product Line Items
if ($event_type == 'product_purchase') {
$organised_data[$session_id]['summary']['unique_products_purchased']++;
$organised_data[$session_id]['summary']['total_products_purchased'] += $event_quantity;
$totals['unique_products_purchased']++;
$totals['total_products_purchased'] += $event_quantity;
}
// Transaction
if ($event_type == 'transaction') {
$organised_data[$session_id]['summary']['transactions']++;
$organised_data[$session_id]['summary']['transaction_value'] += $event_value;
$totals['transactions']++;
$totals['transaction_value'] += $event_value;
if ( ! isset($data_by_date['transactions_by_date'][$event_formatted_date]['y']) ) $data_by_date['transactions_by_date'][$event_formatted_date]['y'] = 0;
$data_by_date['transactions_by_date'][$event_formatted_date]['y']++;
if ( ! in_array($session_id, $session_unique_array['sessions_with_transaction']) ) {
$session_unique_array['sessions_with_transaction'][] = $session_id;
$totals['sessions_with_transaction']++;
}
if (! empty($landing_page_path)) {
$totals['landing_page_summary'][$landing_page_path]['revenue'] += $event_value;
$totals['landing_page_summary'][$landing_page_path]['transactions']++;
}
if (! empty($referral_url)) {
$totals['referral_url_summary'][$referral_url]['revenue'] += $event_value;
$totals['referral_url_summary'][$referral_url]['transactions']++;
} 
}
/**
*
*  Log all events here
*
*/
// Setup container if not exists
if ( ! isset($totals['event_summary'][$event_type]) ) {
$totals['event_summary'][$event_type] = array('total_count' => 0, 'user_count' => array(), 'session_count' => array(), 'total_value' => 0);
}
$totals['event_summary'][$event_type]['total_count']++;
$totals['event_summary'][$event_type]['user_count'][$ip_address] = 1;
$totals['event_summary'][$event_type]['session_count'][$session_id] = 1;
$totals['event_summary'][$event_type]['total_value'] += $event_value;
/**
* 
*  Campaign Performance
* 
*/
if ( ! is_null($utm_campaign) ) {
if ( ! isset($totals['campaign_summary'][$utm_campaign]) ) {
$totals['campaign_summary'][$utm_campaign] = array('session_count' => array(), 'user_count' => array(), 'transactions' => 0, 'total_value' => 0);
}
$totals['campaign_summary'][$utm_campaign]['user_count'][$ip_address] = 1;
$totals['campaign_summary'][$utm_campaign]['session_count'][$session_id] = 1;
if ( $event_type == 'transaction' ) {
$totals['campaign_summary'][$utm_campaign]['transactions']++;
$totals['campaign_summary'][$utm_campaign]['total_value'] += $event_value;
}
}
/**
* 
*  Acquisition Performance
* 
*/
if ( ! isset($totals['acquisition_summary'][$session_traffic_source]) ) {
$totals['acquisition_summary'][$session_traffic_source] = array('session_count' => array(), 'channel_percent' => 0.00, 'user_count' => array(), 'transactions' => 0, 'total_value' => 0);
}
$totals['acquisition_summary'][$session_traffic_source]['user_count'][$ip_address] = 1;
$totals['acquisition_summary'][$session_traffic_source]['session_count'][$session_id] = 1;
if ( $event_type == 'transaction' ) {
$totals['acquisition_summary'][$session_traffic_source]['transactions']++;
$totals['acquisition_summary'][$session_traffic_source]['total_value'] += $event_value;
}
/**
* 
*  Product Performance
* 
*/
if ( $product_id > 0 ) {
if ( ! isset($totals['product_summary'][$product_id]) ) {
$totals['product_summary'][$product_id] = array(
'user_count'            => array(), 
'session_count'         => array(), 
'category_impressions'  => 0, 
'product_clicks'        => 0, 
'ctr_percent'           => 0, 
'product_page_views'    => 0, 
'add_to_cart'           => 0, 
'add_to_cart_per_session'   => 0, 
'transactions'          => 0, 
'qty_purchased'         => 0, 
'total_value'           => 0
);
}
$totals['product_summary'][$product_id]['user_count'][$ip_address] = 1;
$totals['product_summary'][$product_id]['session_count'][$session_id] = 1;
if ( $event_type == 'product_click' ) $totals['product_summary'][$product_id]['product_clicks']++;
if ( $event_type == 'add_to_cart' ) $totals['product_summary'][$product_id]['add_to_cart']++;
if ( $event_type == 'page_view' && $object_type == 'product' ) $totals['product_summary'][$product_id]['product_page_views']++;
if ( $event_type == 'product_purchase' ) {
$totals['product_summary'][$product_id]['transactions']++;
$totals['product_summary'][$product_id]['total_value'] += $event_value;
$totals['product_summary'][$product_id]['qty_purchased'] += $event_quantity;                
}
}
}
// Some cleaning - All Events
if ( is_array($totals['event_summary']) && ! empty($totals['event_summary']) ) {
foreach( $totals['event_summary'] as $event_key => $event_data ) {
$totals['event_summary'][$event_key]['user_count'] = count($event_data['user_count']);
$totals['event_summary'][$event_key]['session_count'] = count($event_data['session_count']);
}
}
// Some cleaning - Campaigns
if ( is_array($totals['campaign_summary']) && ! empty($totals['campaign_summary']) ) {
foreach( $totals['campaign_summary'] as $campaign_name => $campaign_data ) {
$totals['campaign_summary'][$campaign_name]['user_count'] = count($campaign_data['user_count']);
$totals['campaign_summary'][$campaign_name]['session_count'] = count($campaign_data['session_count']);
$totals['campaign_summary'][$campaign_name]['conversion_rate'] = wpd_calculate_percentage( $totals['campaign_summary'][$campaign_name]['transactions'], $totals['campaign_summary'][$campaign_name]['session_count'], 2 );
}
}
// Some cleaning - Campaigns
if ( is_array($totals['acquisition_summary']) && ! empty($totals['acquisition_summary']) ) {
foreach( $totals['acquisition_summary'] as $acquisition_channel => $acquisition_data ) {
$totals['acquisition_summary'][$acquisition_channel]['user_count'] = count($acquisition_data['user_count']);
$totals['acquisition_summary'][$acquisition_channel]['session_count'] = count($acquisition_data['session_count']);
$totals['acquisition_summary'][$acquisition_channel]['channel_percent'] = wpd_calculate_percentage( $totals['acquisition_summary'][$acquisition_channel]['session_count'], $totals['sessions'], 2 );
$totals['acquisition_summary'][$acquisition_channel]['conversion_rate'] = wpd_calculate_percentage( $totals['acquisition_summary'][$acquisition_channel]['transactions'], $totals['sessions'], 2 );
}
}
// Some cleaning - Products
if ( is_array($totals['product_summary']) && ! empty($totals['product_summary']) ) {
$product_impression_data = $this->fetch_product_impressions();
$product_impression_data = $product_impression_data['organised_data'];
foreach( $totals['product_summary'] as $product_id => $product_data ) {
if ( isset($product_impression_data[$product_id]) ) $totals['product_summary'][$product_id]['category_impressions'] = $product_impression_data[$product_id];
$totals['product_summary'][$product_id]['user_count'] = count($product_data['user_count']);
$totals['product_summary'][$product_id]['session_count'] = count($product_data['session_count']);
$totals['product_summary'][$product_id]['ctr_percent'] = wpd_calculate_percentage( $totals['product_summary'][$product_id]['product_clicks'], $totals['product_summary'][$product_id]['category_impressions'], 2 );
$totals['product_summary'][$product_id]['conversion_rate'] = wpd_calculate_percentage( $totals['product_summary'][$product_id]['transactions'], $totals['sessions'], 2 );
$totals['product_summary'][$product_id]['add_to_cart_per_session'] = wpd_calculate_percentage( $totals['product_summary'][$product_id]['add_to_cart'], $totals['sessions'], 2 );
}
}
// Some cleaning - Page View Data
if ( is_array($totals['page_view_summary']) && ! empty($totals['page_view_summary']) ) {
foreach($totals['page_view_summary'] as $page_view_href => $page_data) {
$totals['page_view_summary'][$page_view_href]['session_count'] = count( $page_data['session_count'] );
$totals['page_view_summary'][$page_view_href]['user_count'] = count( $page_data['user_count'] );
}
$totals['page_view_summary'] = wpd_sort_multi_level_array($totals['page_view_summary'], 'session_count');
}
// Some cleaning - Landing Page Data
if ( is_array($totals['landing_page_summary']) && ! empty($totals['landing_page_summary']) ) {
foreach($totals['landing_page_summary'] as $page_view_href => $page_data) {
$totals['landing_page_summary'][$page_view_href]['session_count'] = count( $page_data['session_count'] );
$totals['landing_page_summary'][$page_view_href]['user_count'] = count( $page_data['user_count'] );
}
$totals['landing_page_summary'] = wpd_sort_multi_level_array($totals['landing_page_summary'], 'session_count');
}
// Some cleaning - Referral URL Data
if ( is_array($totals['referral_url_summary']) && ! empty($totals['referral_url_summary']) ) {
foreach($totals['referral_url_summary'] as $page_view_href => $page_data) {
$totals['referral_url_summary'][$page_view_href]['session_count'] = count( $page_data['session_count'] );
$totals['referral_url_summary'][$page_view_href]['user_count'] = count( $page_data['user_count'] );
}
$totals['referral_url_summary'] = wpd_sort_multi_level_array($totals['referral_url_summary'], 'session_count');
}
// Do total calculations
$number_of_days = $this->data_by_date_containers['n_days_period'];
$totals['average_session_duration'] = wpd_divide( $totals['session_duration'], $totals['sessions'], 2 );
$totals['calculations']['sessions_per_day'] = wpd_divide( $totals['sessions'], $number_of_days, 2 );
$totals['calculations']['users_per_day'] = wpd_divide( $totals['users'], $number_of_days, 2 );
$totals['calculations']['page_views_per_session'] = wpd_divide( $totals['page_views'], $totals['sessions'], 2 );
$totals['calculations']['events_per_session'] = wpd_divide( $totals['non_page_view_events'], $totals['sessions'], 2 );
$totals['calculations']['percent_sessions_with_category_view'] = wpd_calculate_percentage( $totals['sessions_with_category_page_views'], $totals['sessions'], 2 );
$totals['calculations']['percent_sessions_with_product_page_view'] = wpd_calculate_percentage( $totals['sessions_with_product_page_views'], $totals['sessions'], 2 );
$totals['calculations']['percent_sessions_with_add_to_cart'] = wpd_calculate_percentage( $totals['sessions_with_add_to_cart'], $totals['sessions'], 2 );
$totals['calculations']['percent_sessions_with_initiate_checkout'] = wpd_calculate_percentage( $totals['sessions_with_initiate_checkout'], $totals['sessions'], 2 );
$totals['calculations']['conversion_rate'] = wpd_calculate_percentage( $totals['transactions'], $totals['sessions'], 2 );
// Configure return object
$analytics_data = array(
'totals' => $totals,
'data_by_date' => $data_by_date,
'organised_data' => $organised_data
);
// Store the data into the prop
$this->set_data( 'analytics', $analytics_data );
return $analytics_data;
}
/**
*
*  Fetches product impressions from category page, used to supplement the analytics data.
*  @see get_gmt_from_date() for converting input date
*  @link https://developer.wordpress.org/reference/functions/get_gmt_from_date/
*
*/
public function fetch_product_impressions() {
global $wpdb;
// Collect Vars
$wpd_db = new WPD_Database_Interactor();
$product_impressions_table = $wpd_db->product_impressions_table;
$return_array = array();
// Apply filters if required
$where_clause = '';
$filters = $this->filter;
if ( isset($filters['start_date']) && isset($filters['end_date']) ) {
$start_date = get_gmt_from_date( $filters['start_date'] );
$end_date = get_gmt_from_date( $filters['end_date'] );
// Add 1 day to end date, so that it's inclusive of actual date
$end_date = date("Y-m-d H:i:s", strtotime('+24 hours', strtotime($end_date)));
$where_clause .= ' AND product_impressions.date_created_gmt >= \'' . $start_date . '\'';
$where_clause .= ' AND product_impressions.date_created_gmt <= \'' . $end_date . '\'';
}
// Prepare query
$sql_query =
"SELECT 
product_impressions.product_id as product_id,
count(product_id) as impressions
FROM $product_impressions_table AS product_impressions
WHERE 1=1
$where_clause
GROUP BY product_id";
// Fetch Results
$results = $wpdb->get_results( $sql_query, 'ARRAY_A' );
// Log an error
if ( $wpdb->last_error  ) {
wpd_write_log( 'Error capturing product impressions data from DB, dumping the error and query.', 'db_error' );
wpd_write_log( $wpdb->last_error, 'db_error' );
wpd_write_log( $wpdb->last_query, 'db_error' );
$this->set_error( $wpdb->last_error );
return false;
}
// Get count of records from query
$record_count = (is_array($results)) ? count($results) : 0;
// Store the found data
$this->set_data( 'product_impressions', array('raw_data' => $results) );
$this->set_data( 'product_impressions', array('total_db_records' => $record_count) );
// Re Organise the array
if ( is_array($results) && ! empty($results) ) {
// Cleanup
foreach( $results as $row ) {
$return_array[$row['product_id']] = $row['impressions'];
}
// Store the organised data
$this->set_data( 'product_impressions', array('organised_data' => $return_array) );
}
// Return results
return $this->get_data( 'product_impressions' );
}
/**
* 
*  Collects count of sessions
* 
*  Will take on board filters for session_id, ip_address, user_id and dates 
* 
**/
public function fetch_session_count() {
global $wpdb;
// Collect Vars
$wpd_db = new WPD_Database_Interactor();
$session_data_table = $wpd_db->session_data_table;
// Apply filters if required
$where_clause = '';
$filters = $this->filter;
if ( isset($filters['start_date']) && isset($filters['end_date']) ) {
$start_date = get_gmt_from_date( $filters['start_date'] );
$end_date = get_gmt_from_date( $filters['end_date'] );
// Add 1 day to end date, so that it's inclusive of actual date
$end_date = date("Y-m-d H:i:s", strtotime('+24 hours', strtotime($end_date)));
$where_clause .= ' AND date_created_gmt >= \'' . $start_date . '\'';
$where_clause .= ' AND date_created_gmt <= \'' . $end_date . '\'';
}
// Single session ID defined
if ( isset($filters['session_id']) && ! empty($filters['session_id']) && is_string($filters['session_id']) ) {
$where_clause .= ' AND session_id = \'' . $filters["session_id"] . '\'';
}
// Single user_id
if ( isset($filters['user_id']) && ! empty($filters['user_id']) && is_numeric($filters['user_id']) ) {
$where_clause .= ' AND user_id = ' . $filters["user_id"] . '';
}
// Single ip_address
if ( isset($filters['ip_address']) && ! empty($filters['ip_address']) && is_string($filters['ip_address']) ) {
$where_clause .= ' AND ip_address = \'' . $filters["ip_address"] . '\'';
}
// Prepare query
$sql_query = 
"SELECT COUNT(*)
FROM $session_data_table
WHERE 1=1
$where_clause";
// Fetch Results
$results = $wpdb->get_var( $sql_query );
// Error
if ( $wpdb->last_error  ) {
wpd_write_log( 'Error capturing analytics data from DB, dumping the error and query.', 'db_error' );
wpd_write_log( $wpdb->last_error, 'db_error' );
wpd_write_log( $wpdb->last_query, 'db_error' );
return 0;
}
// Return results
if ( is_numeric($results) ) {
return $results;
} else {
return 0;
}
}
/**
* 
* Collects all product analytics for a given product id(s)
* 
* @param int $product_id = Product ID to search for in query
* @return int $product_analytics
* 
*/
public function fetch_product_statistic( $product_statistic = null, $product_id = 0 ) {
// First, search for filter's product id
if ( $product_id === 0 ) {
// Product ID not set, lets check the filter
if ( isset($this->filter['product_id']) && is_int($this->filter['product_id']) && $this->filter['product_id'] > 0 ) {
$product_id = $this->filter['product_id'];
} else {
return 0; // Return the default
}
}
// First, search for filter's product id
if ( $product_statistic === null ) {
// Product ID not set, lets check the filter
if ( isset($this->filter['fetch_product_stat'])  && ! empty( $this->filter['fetch_product_stat'] ) ) {
$product_statistic = $this->filter['fetch_product_stat'];
} else {
return 0; // Return the default
}
}
global $wpdb;
// Collect Vars
$wpd_db = new WPD_Database_Interactor();
$product_impressions_table = $wpd_db->product_impressions_table;
$events_table = $wpd_db->events_table;
$return_array = array();
// Apply filters if required
$where_clause = '';
$filters = $this->filter;
if ( isset($filters['start_date']) && isset($filters['end_date']) ) {
$start_date = get_gmt_from_date( $filters['start_date'] );
$end_date = get_gmt_from_date( $filters['end_date'] );
// Add 1 day to end date, so that it's inclusive of actual date
$end_date = date("Y-m-d H:i:s", strtotime('+24 hours', strtotime($end_date)));
$where_clause .= ' AND date_created_gmt >= \'' . $start_date . '\'';
$where_clause .= ' AND date_created_gmt <= \'' . $end_date . '\'';
}
// Already checked to make sure theres a product id > 0
$where_clause .= ' AND product_id = \'' . $product_id . '\'';
if ( $product_statistic == 'product_impression' ) {
// Prepare query
$sql_query = 
"SELECT count(*)
FROM $product_impressions_table
WHERE 1=1
$where_clause";
// Fetch Results
$results = (int) $wpdb->get_var( $sql_query );
if ( $wpdb->last_error  ) {
wpd_write_log( 'Error collecting product statistic from DB, dumping the error and query.', 'db_error' );
wpd_write_log( $wpdb->last_error, 'db_error' );
wpd_write_log( $wpdb->last_query, 'db_error' );
}
return $results;
} else { // Assuming all other events are in the events table
$where_clause .= ' AND event_type = \'' . $product_statistic . '\'';
// Prepare query
$sql_query = 
"SELECT count(*)
FROM $events_table
WHERE 1=1
$where_clause";
// Fetch Results
$results = (int) $wpdb->get_var( $sql_query );
if ( $wpdb->last_error  ) {
wpd_write_log( 'Error collecting product statistic from DB, dumping the error and query.', 'db_error' );
wpd_write_log( $wpdb->last_error, 'db_error' );
wpd_write_log( $wpdb->last_query, 'db_error' );
}
return $results;
}
}
/**
*
*  Returns date in format for the date container
*
*/
private function reformat_date_to_date_format( $date ) {
$date_container_date_format = $this->data_by_date_containers['date_format'];
$formatted_date = date( $date_container_date_format, strtotime($date) );
return $formatted_date;
}
/**
*
*  Calculates difference in seconds between two dates - used for session duration
*
*/
private function calculate_difference_in_seconds( $recent_date, $old_date ) {
if ( is_null($recent_date) || is_null($old_date) ) {
return 0;
}
$recent_date_string = strtotime($recent_date);
$old_date_string    = strtotime($old_date);
(int) $difference_in_seconds = $recent_date_string - $old_date_string;
return $difference_in_seconds;
}
/**
*
*  Checks for Query Parameters and returns as associated array if found
*
*/
private function get_url_components( $url ) {
$result = array(
'url' => $url,
'path' => null,
'query_parameters' => array()
);
if ( is_null($url) ) return $result;
// Prevents issues with 038;
$url = htmlspecialchars_decode( $url );
$result['url'] = $url;
$parsed_url = parse_url( $url );
if ( isset($parsed_url['path']) && ! empty($parsed_url['path']) ) {
$result['path'] = $parsed_url['path'];
}
// Only collect query params
$query_parameters = parse_url( $url, PHP_URL_QUERY );
if ( ! empty($query_parameters) ) {
$result['decoded_qp'] = $query_parameters;
parse_str( $query_parameters, $result['query_parameters'] );
}
return $result;
}
/**
*
*  Calculate traffic source type
*
*/
private function determine_traffic_source( $referral_url, $query_parameters = null ) {
$traffic_type = new WPD_Traffic_Type( $referral_url, $query_parameters );
return $traffic_type->determine_traffic_source();
}
/**
* 
*  Logs general messages and optionally errors to:
*  wpd_google_ads_api_log.txt and wpd_google_ads_api_error_log.txt
* 
*  @param string|array|WP_Error $message the content to print to the log
*  @param bool $error Set to true if you want to log this to the error log in addition to the general api log
*  @return void
* 
**/
private function log( $message, $error = false ) {
// Setup backtrace
$backtrace = debug_backtrace();
$last_call = $backtrace[1]['function'];
if ( $last_call == 'set_error' ) $last_call = $backtrace[2]['function'];
if ( $error ) {
// WP Error
if ( is_a( $message, 'WP_Error' ) ) {
$error_message = (string) $message->get_error_message();
}
// Array
if ( is_array($message) || is_object($message) ) {
$error_message = (string) json_encode( $message );
}
// Confirm string
if ( is_string($message) ) {
$error_message = (string) $message;
}
// Save the errors to instance
if ( $error_message ) {
$this->errors[] = $error_message;
}
// Log errors
wpd_write_log( 'Backtrace function: ' . $last_call, 'data_warehouse_error' );
wpd_write_log( $message, 'data_warehouse_error' );
}
// Log the message
wpd_write_log( $message, 'data_warehouse' );
return $message;
}
}