File "class-posts.php"
Full Path: /home/diablzlo/glucosebalnce.com/wp-content/plugins/seo-by-rank-math-pro/includes/modules/analytics/class-posts.php
File size: 22.67 KB
MIME-type: text/x-php
Charset: utf-8
<?php
/**
* The Analytics Module
*
* @since 2.0.0
* @package RankMath
* @subpackage RankMath\modules
* @author Rank Math <support@rankmath.com>
*/
namespace RankMathPro\Analytics;
use stdClass;
use WP_Error;
use WP_REST_Request;
use RankMath\Helper;
use RankMath\Traits\Cache;
use RankMath\Traits\Hooker;
use RankMath\Google\Console;
use RankMath\Google\Analytics;
use RankMath\Analytics\Stats;
use RankMath\Helpers\DB as DB_Helper;
defined( 'ABSPATH' ) || exit;
/**
* Posts class.
*/
class Posts {
use Hooker;
use Cache;
/**
* Main instance
*
* Ensure only one instance is loaded or can be loaded.
*
* @return Posts
*/
public static function get() {
static $instance;
if ( is_null( $instance ) && ! ( $instance instanceof Posts ) ) {
$instance = new Posts();
$instance->setup();
}
return $instance;
}
/**
* Constructor.
*/
public function setup() {
$this->filter( 'rank_math/analytics/post_data', 'add_and_sort_data', 10, 2 );
$this->filter( 'rank_math/analytics/get_objects_by_score_args', 'get_objects_by_score_args', 10, 2 );
$this->filter( 'rank_math/analytics/get_posts_rows_by_objects', 'get_posts_rows_by_objects', 10, 2 );
}
/**
* Get posts by objects.
*
* @param boolean $result Check.
* @param WP_REST_Request $request Full details about the request.
* @return $args for order and orderby.
*/
public function get_objects_by_score_args( $result, WP_REST_Request $request ) {
$orderby = $request->get_param( 'orderby' );
$is_valid_order_param = in_array( $orderby, [ 'title', 'seo_score' ], true );
$orderby = $is_valid_order_param ? $orderby : 'created';
$order = strtoupper( $request->get_param( 'order' ) );
$args['orderBy'] = $orderby;
$args['order'] = $order;
return $args;
}
/**
* Change user perference.
*
* @param array $data array.
* @param WP_REST_Request $request post object.
* @return array $data sorted array.
*/
public function add_and_sort_data( $data, WP_REST_Request $request ) {
$id = $request->get_param( 'id' );
// In case schemas data isn't set to this post, try to get default schema info.
if ( empty( $data['schemas_in_use'] ) ) {
$data['schemas_in_use'] = Helper::get_default_schema_type( $id, true, true );
}
if ( ! Console::is_console_connected() ) {
return $data;
}
// Get keywords info for this post.
$keywords = DB::analytics()
->selectCount( 'DISTINCT(query)', 'keywords' )
->whereLike( 'page', $data['page'], '%', '' )
->whereBetween( 'created', [ Stats::get()->start_date, Stats::get()->end_date ] )
->getVar();
$old_keywords = DB::analytics()
->selectCount( 'DISTINCT(query)', 'keywords' )
->whereLike( 'page', $data['page'], '%', '' )
->whereBetween( 'created', [ Stats::get()->compare_start_date, Stats::get()->compare_end_date ] )
->getVar();
$data['keywords'] = [
'total' => (int) $keywords,
'previous' => (int) $old_keywords,
'difference' => $keywords - $old_keywords,
];
$data['backlinks'] = [
'total' => 0,
'previous' => 0,
'difference' => 0,
];
$data = $this->add_badges( $data );
$data = $this->get_graph_data_for_post( $data );
$data = $this->add_ranking_keywords( $data );
// Get analytics data for this post.
$metrices = Stats::get()->get_analytics_data(
[
'pages' => [ $data['page'] ],
'pageview' => true,
'sub_where' => " AND page = '{$data['page']}'",
]
);
if ( ! empty( $metrices ) ) {
$metrices = current( $metrices );
}
$data = array_merge(
(array) $data,
(array) $metrices
);
$orderby = $request->get_param( 'orderby' );
$order = empty( $request->get_param( 'order' ) ) ? 'DESC' : strtoupper( $request->get_param( 'order' ) );
if ( 'query' !== $orderby ) {
$data['rankingKeywords'] = $this->ranking_keyword_array_sort( $data['rankingKeywords'], $order, $orderby );
}
if ( 'query' === $orderby ) {
if ( 'DESC' === $order ) {
uasort(
$data['rankingKeywords'],
function ( $a, $b ) use ( $orderby ) {
return strtolower( $a[ $orderby ] ) < strtolower( $b[ $orderby ] );
}
);
}
if ( 'ASC' === $order ) {
uasort(
$data['rankingKeywords'],
function ( $a, $b ) use ( $orderby ) {
return strtolower( $a[ $orderby ] ) > strtolower( $b[ $orderby ] );
}
);
}
}
return $data;
}
/**
* Sort array for ranking keyword by order and orderby
*
* @param array $arr array.
*
* @param Variable $arr_order is order direction.
*
* @param Variable $arr_orderby is key for sort.
*/
public function ranking_keyword_array_sort( $arr, $arr_order, $arr_orderby ) {
if ( empty( $arr ) || empty( $arr_orderby ) ) {
return $arr;
}
if ( 'DESC' === $arr_order ) {
uasort(
$arr,
function ( $a, $b ) use ( $arr_orderby ) {
return $a[ $arr_orderby ]['total'] < $b[ $arr_orderby ]['total'];
}
);
}
if ( 'ASC' === $arr_order ) {
uasort(
$arr,
function ( $a, $b ) use ( $arr_orderby ) {
return $a[ $arr_orderby ]['total'] > $b[ $arr_orderby ]['total'];
}
);
}
return $arr;
}
/**
* Get posts by objects.
*
* @param boolean $result Check.
* @param WP_REST_Request $request Full details about the request.
* @return array Posts rows.
*/
public function get_posts_rows_by_objects( $result, WP_REST_Request $request ) {
$orderby = $request->get_param( 'orderby' );
$order = strtoupper( $request->get_param( 'order' ) );
$objects = Stats::get()->get_objects_by_score( $request );
$objects = Links::get_links_by_objects( $objects );
$pages = isset( $objects['rows'] ) ? \array_keys( $objects['rows'] ) : [];
$pages = array_map( 'esc_sql', $pages );
$pageviews = Pageviews::get_pageviews( [ 'pages' => $pages ] );
$pageviews = Stats::get()->set_page_as_key( $pageviews['rows'] );
$console = Stats::get()->get_analytics_data(
[
'orderBy' => 'diffImpressions',
'pageview' => true,
'offset' => 0, // Here offset should always zero.
'perpage' => ! empty( $objects['rowsFound'] ) ? $objects['rowsFound'] : 0,
'sub_where' => " AND page IN ('" . join( "', '", $pages ) . "')",
]
);
$new_rows = [];
if ( ! empty( $objects['rows'] ) ) {
foreach ( $objects['rows'] as $object ) {
$page = $object['page'];
if ( isset( $pageviews[ $page ] ) ) {
$object['pageviews'] = [
'total' => (int) $pageviews[ $page ]['pageviews'],
'difference' => (int) $pageviews[ $page ]['difference'],
];
}
if ( isset( $console[ $page ] ) ) {
$object = \array_merge( $console[ $page ], $object );
}
if ( ! isset( $object['links'] ) ) {
$object['links'] = new stdClass();
}
$new_rows[ $page ] = $object;
}
}
$history = $this->get_graph_data_for_pages( $pages );
$new_rows = Stats::get()->set_page_position_graph( $new_rows, $history );
if ( in_array( $orderby, [ 'position', 'clicks', 'pageviews', 'impressions' ], true ) ) {
$new_rows = $this->analytics_array_sort( $new_rows, $order, $orderby );
}
if ( empty( $new_rows ) ) {
$new_rows['response'] = 'No Data';
}
return [
'rows' => $new_rows,
'rowsFound' => ! empty( $objects['rowsFound'] ) ? $objects['rowsFound'] : 0,
];
}
/**
* Sort array by order and orderby
*
* @param array $arr array.
*
* @param Variable $arr_order is order direction.
*
* @param Variable $arr_orderby is key for sort.
*
* @return $arr sorted array
*/
public function analytics_array_sort( $arr, $arr_order, $arr_orderby ) {
if ( 'DESC' === $arr_order ) {
uasort(
$arr,
function ( $a, $b ) use ( $arr_orderby ) {
if ( false === array_key_exists( $arr_orderby, $a ) ) {
$a[ $arr_orderby ] = [ 'total' => '0' ];
}
if ( false === array_key_exists( $arr_orderby, $b ) ) {
$b[ $arr_orderby ] = [ 'total' => '0' ];
}
return $a[ $arr_orderby ]['total'] < $b[ $arr_orderby ]['total'];
}
);
}
if ( 'ASC' === $arr_order ) {
uasort(
$arr,
function ( $a, $b ) use ( $arr_orderby ) {
if ( false === array_key_exists( $arr_orderby, $a ) ) {
$a[ $arr_orderby ] = [ 'total' => '0' ];
}
if ( false === array_key_exists( $arr_orderby, $b ) ) {
$b[ $arr_orderby ] = [ 'total' => '0' ];
}
return $a[ $arr_orderby ]['total'] > $b[ $arr_orderby ]['total'];
}
);
}
return $arr;
}
/**
* Get ranking keywords data and append it to existing post data.
*
* @param array $post Post array.
* @return array
*/
public function add_ranking_keywords( $post ) {
$page = $post['page'];
$sub_query = "AND page = '{$page}'";
$data = Stats::get()->get_analytics_data(
[
'dimension' => 'query',
'offset' => 0,
'perpage' => 20,
'orderBy' => 'impressions',
'sub_where' => $sub_query,
]
);
$history = Keywords::get()->get_graph_data_for_keywords( \array_keys( $data ), $sub_query );
$post['rankingKeywords'] = Stats::get()->set_query_position( $data, $history );
return $post;
}
/**
* Append badges data into existing post data.
*
* @param array $post Post array.
* @return array
*/
public function add_badges( $post ) {
$post['badges'] = [
'clicks' => $this->get_position_for_badges( 'clicks', $post['page'] ),
'traffic' => $this->get_position_for_badges( 'traffic', $post['page'] ),
'keywords' => $this->get_position_for_badges( 'query', $post['page'] ),
'impressions' => $this->get_position_for_badges( 'impressions', $post['page'] ),
];
return $post;
}
/**
* Get position for badges.
*
* @param string $column Column name.
* @param string $page Page url.
* @return integer
*/
public function get_position_for_badges( $column, $page ) {
$start = gmdate( 'Y-m-d H:i:s', strtotime( '-30 days ', Stats::get()->end ) );
if ( 'traffic' === $column ) {
$rows = DB::traffic()
->select( 'page' )
->selectSum( 'pageviews', 'pageviews' )
->whereBetween( 'created', [ $start, Stats::get()->end_date ] )
->groupBy( 'page' )
->orderBy( 'pageviews', 'DESC' )
->limit( 5 );
} else {
$rows = DB::analytics()
->select( 'page' )
->whereBetween( 'created', [ $start, Stats::get()->end_date ] )
->groupBy( 'page' )
->orderBy( $column, 'DESC' )
->limit( 5 );
}
if ( 'impressions' === $column || 'click' === $column ) {
$rows->selectSum( $column, $column );
}
if ( 'query' === $column ) {
$rows->selectCount( 'DISTINCT(query)', 'keywords' );
}
$rows = $rows->get( ARRAY_A );
foreach ( $rows as $index => $row ) {
if ( $page === $row['page'] ) {
return $index + 1;
}
}
return 99;
}
/**
* Append analytics graph data into existing post data.
*
* @param array $post Post array.
* @return array
*/
public function get_graph_data_for_post( $post ) {
global $wpdb;
// Step1. Get splitted date intervals for graph within selected date range.
$data = new stdClass();
$page = $post['page'];
$intervals = Stats::get()->get_intervals();
$sql_daterange = Stats::get()->get_sql_date_intervals( $intervals );
// Step2. Get analytics data summary for each splitted date intervals.
$metrics = DB_Helper::get_results( // phpcs:disable -- $sql_daterange, $page are escaped.
$wpdb->prepare(
"SELECT DATE_FORMAT( created, '%%Y-%%m-%%d') as date, SUM( clicks ) as clicks, SUM(impressions) as impressions, ROUND( AVG(ctr), 2 ) as ctr, {$sql_daterange}
FROM {$wpdb->prefix}rank_math_analytics_gsc
WHERE created BETWEEN %s AND %s AND page LIKE '%{$page}'
GROUP BY range_group",
Stats::get()->start_date,
Stats::get()->end_date
)
);
// Step3. Get position data summary for each splitted date intervals.
$query = $wpdb->prepare(
"SELECT page, MAX(CONCAT(t.uid, ':', t.range_group)) as range_group FROM
(SELECT page, MAX(CONCAT(page, ':', DATE(created), ':', LPAD((100 - position), 3, '0'))) as uid, {$sql_daterange}
FROM {$wpdb->prefix}rank_math_analytics_gsc
WHERE created BETWEEN %s AND %s AND page LIKE '%{$page}'
GROUP BY range_group, DATE(created)
ORDER BY DATE(created) DESC) AS t
GROUP BY t.range_group",
Stats::get()->start_date,
Stats::get()->end_date
);
$positions = DB_Helper::get_results( $query );
$positions = Stats::get()->extract_data_from_mixed( $positions, 'range_group', ':', [ 'range_group', 'position', 'date' ] );
// Step4. Get keywords count for each splitted date intervals.
$query = $wpdb->prepare(
"SELECT DATE_FORMAT( created, '%%Y-%%m-%%d') as date, COUNT(DISTINCT(query)) as keywords, {$sql_daterange}
FROM {$wpdb->prefix}rank_math_analytics_gsc
WHERE created BETWEEN %s AND %s AND page LIKE '%{$page}'
GROUP BY range_group",
Stats::get()->start_date,
Stats::get()->end_date
);
$keywords = DB_Helper::get_results( $query );
// Step5. Filter graph data.
$metrics = Stats::get()->filter_graph_rows( $metrics );
$positions = Stats::get()->filter_graph_rows( $positions );
$keywords = Stats::get()->filter_graph_rows( $keywords );
// Step6. Convert types.
$metrics = array_map( [ Stats::get(), 'normalize_graph_rows' ], $metrics );
$positions = array_map( [ Stats::get(), 'normalize_graph_rows' ], $positions );
$keywords = array_map( [ Stats::get(), 'normalize_graph_rows' ], $keywords );
// Step7. Merge all analytics data.
$data = Stats::get()->get_date_array(
$intervals['dates'],
[
'clicks' => [],
'impressions' => [],
'position' => [],
'ctr' => [],
'keywords' => [],
'pageviews' => [],
]
);
$data = Stats::get()->get_merge_data_graph( $metrics, $data, $intervals['map'] );
$data = Stats::get()->get_merge_data_graph( $positions, $data, $intervals['map'] );
$data = Stats::get()->get_merge_data_graph( $keywords, $data, $intervals['map'] );
// Step8. Get traffic data in case analytics is connected for each splitted data intervals.
if ( Analytics::is_analytics_connected() ) {
$query = $wpdb->prepare(
"SELECT DATE_FORMAT( created, '%%Y-%%m-%%d') as date, SUM( pageviews ) as pageviews, {$sql_daterange}
FROM {$wpdb->prefix}rank_math_analytics_ga
WHERE created BETWEEN %s AND %s AND page LIKE '%{$page}'
GROUP BY range_group",
Stats::get()->start_date,
Stats::get()->end_date
);
$traffic = DB_Helper::get_results( $query );
// Filter graph data.
$traffic = Stats::get()->filter_graph_rows( $traffic );
// Convert types.
$traffic = array_map( [ Stats::get(), 'normalize_graph_rows' ], $traffic );
$data = Stats::get()->get_merge_data_graph( $traffic, $data, $intervals['map'] );
}
$data = Stats::get()->get_graph_data_flat( $data );
// Step9. Append graph data into existing post data.
$post['graph'] = array_values( $data );
return $post;
}
/**
* Get posts rows.
*
* @param WP_REST_Request $request Full details about the request.
*
* @return array Posts rows.
*/
public function get_posts_rows( WP_REST_Request $request ) {
$per_page = 25;
$cache_args = $request->get_params();
$cache_args['per_page'] = $per_page;
$cache_group = 'rank_math_rest_posts_rows';
$cache_key = $this->generate_hash( $cache_args );
$data = $this->get_cache( $cache_key, $cache_group );
if ( ! empty( $data ) ) {
return $data;
}
$data = [];
// Pagination.
$offset = ( $request->get_param( 'page' ) - 1 ) * $per_page;
$orderby = $request->get_param( 'orderby' );
$post_type = sanitize_key( $request->get_param( 'postType' ) );
$order = $request->get_param( 'order' );
$order = in_array( $order, [ 'asc', 'desc' ], true ) ? $order : 'desc';
$order = strtoupper( $order );
$post_type_clause = $post_type ? " AND o.object_subtype = '{$post_type}'" : '';
if ( 'pageviews' === $orderby ) {
// Get posts order by pageviews.
$t_data = Pageviews::get_pageviews_with_object(
[
'order' => $order,
'limit' => "LIMIT {$offset}, {$per_page}",
'sub_where' => $post_type_clause,
]
);
$pageviews = Stats::get()->set_page_as_key( $t_data['rows'] );
$pages = \array_keys( $pageviews );
$pages = array_map( 'esc_sql', $pages );
$console = Stats::get()->get_analytics_data(
[
'offset' => 0, // Should set as 0.
'perpage' => $per_page,
'objects' => false,
'sub_where' => " AND page IN ('" . join( "', '", $pages ) . "')",
]
);
$data['rowsFound'] = $this->rows_found();
foreach ( $pageviews as $page => &$pageview ) {
$pageview['pageviews'] = [
'total' => '0' === $pageview['pageviews'] ? 'n/a' : (int) $pageview['pageviews'],
'difference' => '0' === $pageview['difference'] ? 'n/a' : (int) $pageview['difference'],
];
if ( isset( $console[ $page ] ) ) {
unset( $console[ $page ]['pageviews'] );
$pageview = \array_merge( $pageview, $console[ $page ] );
}
}
$history = $this->get_graph_data_for_pages( $pages );
$pageviews = Stats::get()->set_page_position_graph( $pageviews, $history );
$data['rows'] = $pageviews;
} else {
// Get posts order by impressions.
$t_data = DB::objects()
->select( [ 'page', 'title', 'object_id' ] )
->where( 'is_indexable', 1 );
if ( 'title' === $orderby ) {
$t_data->orderBy( $orderby, $order )
->limit( $per_page, $offset );
}
$t_data = $t_data->get( ARRAY_A );
$pages = Stats::get()->set_page_as_key( $t_data );
$params = \array_keys( $pages );
$params = array_map( 'esc_sql', $params );
$args = [
'dimension' => 'page',
'offset' => 0,
'perpage' => 20000,
'sub_where' => " AND page IN ('" . join( "', '", $params ) . "')",
];
if ( 'title' !== $orderby ) {
$args['orderBy'] = $orderby;
$args['order'] = $order;
}
$rows = Stats::get()->get_analytics_data( $args );
if ( 'title' !== $orderby ) {
foreach ( $pages as $page => $row ) {
if ( ! isset( $rows[ $page ] ) ) {
$rows[ $page ] = $row;
} else {
$rows[ $page ] = \array_merge( $rows[ $page ], $row );
}
}
$history = $this->get_graph_data_for_pages( $params );
$data['rows'] = Stats::get()->set_page_position_graph( $rows, $history );
$data['rowsFound'] = count( $pages );
// Filter array by $offset, $perpage value.
$data['rows'] = array_slice( $data['rows'], $offset, $per_page, true );
} else {
foreach ( $pages as $page => &$row ) {
if ( isset( $rows[ $page ] ) ) {
$row = \array_merge( $row, $rows[ $page ] );
}
}
$history = $this->get_graph_data_for_pages( $params );
$data['rows'] = Stats::get()->set_page_position_graph( $pages, $history );
$data['rowsFound'] = $this->rows_found();
}
// Get fetched page info again.
$pages = Stats::get()->set_page_as_key( $data['rows'] );
$params = \array_keys( $pages );
$params = array_map( 'esc_sql', $params );
// Get pageviews info.
$pageviews = Pageviews::get_pageviews_with_object(
[
'limit' => "LIMIT 0, {$per_page}",
'sub_where' => " AND o.page IN ('" . join( "', '", $params ) . "')" . $post_type_clause,
]
);
$pageviews = Stats::get()->set_page_as_key( $pageviews['rows'] );
// Merge pageview info into main data.
foreach ( $data['rows'] as $page => &$row ) {
if ( isset( $pageviews[ $page ] ) ) {
$pageview = [
'pageviews' => [
'total' => '0' === $pageviews[ $page ]['pageviews'] ? 'n/a' : (int) $pageviews[ $page ]['pageviews'],
'difference' => '0' === $pageviews[ $page ]['difference'] ? 'n/a' : (int) $pageviews[ $page ]['difference'],
],
];
$row = \array_merge( $row, $pageview );
}
}
}
if ( empty( $data ) ) {
$data['response'] = 'No Data';
} else {
$this->set_cache( $cache_key, $data, $cache_group, DAY_IN_SECONDS );
}
return $data;
}
/**
* Get top 5 winning posts.
*
* @return array
*/
public function get_winning_posts() {
global $wpdb;
$cache_key = Stats::get()->get_cache_key( 'winning_posts', Stats::get()->days . 'days' );
$cache = get_transient( $cache_key );
if ( false !== $cache ) {
return $cache;
}
$rows = Stats::get()->get_analytics_data(
[
'order' => 'ASC',
'objects' => true,
'pageview' => true,
'offset' => 0,
'perpage' => 5,
'type' => 'win',
]
);
$history = $this->get_graph_data_for_pages( \array_keys( $rows ) );
$rows = Stats::get()->set_page_position_graph( $rows, $history );
if ( empty( $rows ) ) {
$rows['response'] = 'No Data';
}
set_transient( $cache_key, $rows, DAY_IN_SECONDS );
return $rows;
}
/**
* Get top 5 losing posts.
*
* @return object
*/
public function get_losing_posts() {
global $wpdb;
$cache_key = Stats::get()->get_cache_key( 'losing_posts', Stats::get()->days . 'days' );
$cache = get_transient( $cache_key );
if ( false !== $cache ) {
return $cache;
}
$rows = Stats::get()->get_analytics_data(
[
'objects' => true,
'pageview' => true,
'offset' => 0,
'perpage' => 5,
'type' => 'lose',
]
);
$history = $this->get_graph_data_for_pages( \array_keys( $rows ) );
$rows = Stats::get()->set_page_position_graph( $rows, $history );
if ( empty( $rows ) ) {
$rows['response'] = 'No Data';
}
set_transient( $cache_key, $rows, DAY_IN_SECONDS );
return $rows;
}
/**
* Get graph data for pages.
*
* @param array $pages Pages to get data for.
*
* @return array
*/
public function get_graph_data_for_pages( $pages ) {
global $wpdb;
$intervals = Stats::get()->get_intervals();
$sql_daterange = Stats::get()->get_sql_date_intervals( $intervals );
$pages = \array_map( 'esc_sql', $pages );
$pages = '(\'' . join( '\', \'', $pages ) . '\')';
$query = $wpdb->prepare(
"SELECT page, date, MAX(CONCAT(t.uid, ':', t.range_group)) as range_group FROM
( SELECT page, DATE_FORMAT( created,'%%Y-%%m-%%d') as date, MAX( CONCAT( page, ':', DATE( created ), ':', LPAD( ( 100 - position ), 3, '0' ) ) ) as uid, {$sql_daterange}
FROM {$wpdb->prefix}rank_math_analytics_gsc
WHERE page IN {$pages} AND created BETWEEN %s AND %s
GROUP BY page, range_group, DATE(created)
ORDER BY page ASC, DATE(created) DESC) AS t
GROUP BY t.page, t.range_group
ORDER BY date ASC",
Stats::get()->start_date,
Stats::get()->end_date
);
$data = DB_Helper::get_results( $query );
$data = Stats::get()->extract_data_from_mixed( $data, 'range_group', ':', [ 'range_group', 'position' ] );
$data = Stats::get()->filter_graph_rows( $data );
return array_map( [ Stats::get(), 'normalize_graph_rows' ], $data );
}
/**
* Count indexable pages.
*
* @return mixed
*/
private function rows_found() {
return DB::objects()
->selectCount( 'page' )
->where( 'is_indexable', 1 )
->getVar();
}
}