prepare( 'dim_type = %s', $dim ); if ( $days > 0 ) { $where .= $wpdb->prepare( ' AND `date` >= %s', gmdate( 'Y-m-d', time() - $days * DAY_IN_SECONDS ) ); } if ( '' !== $q ) { $where .= $wpdb->prepare( ' AND dim_value LIKE %s', '%' . $wpdb->esc_like( $q ) . '%' ); } return $where; } } if ( ! function_exists( 'aieo_gsc_terms_sql' ) ) { function aieo_gsc_terms_sql( $params ) { $where = aieo_gsc_window_where( $params ); return "SELECT MIN(dim_hash) AS row_hash, MIN(dim_value) AS term, SUM(clicks) AS clicks, SUM(impressions) AS impressions, SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr, SUM(position * impressions) / NULLIF(SUM(impressions), 0) AS position, MAX(object_type) AS object_type, MAX(object_id) AS object_id, MAX(`date`) AS last_seen FROM {prefix}aieo_gsc_daily WHERE {$where} GROUP BY dim_hash"; } } if ( ! function_exists( 'aieo_gsc_by_type_sql' ) ) { function aieo_gsc_by_type_sql( $params ) { $params['dim'] = 'page'; $params['q'] = ''; $where = aieo_gsc_window_where( $params ); return "SELECT IF(object_id > 0, object_type, 'not_a_single_page') AS ctype, COUNT(DISTINCT dim_hash) AS pages, SUM(clicks) AS clicks, SUM(impressions) AS impressions, SUM(impressions) / NULLIF((SELECT SUM(impressions) FROM {prefix}aieo_gsc_daily WHERE {$where}), 0) AS share FROM {prefix}aieo_gsc_daily WHERE {$where} GROUP BY ctype"; } } $aieo_gsc_params = array( 'days' => array( 'type' => 'int', 'default' => 90, 'options' => array( 0, 7, 30, 90, 180, 480 ), 'label' => 'Window', ), 'dim' => array( 'type' => 'text', 'default' => 'query', 'options' => array( 'query', 'page' ), 'label' => 'Dimension', ), 'q' => array( 'type' => 'text', 'default' => '', 'label' => 'Filter', ), ); $aieo_gsc_measures = array( array( 'key' => 'clicks', 'label' => 'Clicks', 'type' => 'integer', 'width' => '90px', 'sortable' => true, 'filter' => 'number_range', 'aggregate' => 'SUM', ), array( 'key' => 'impressions', 'label' => 'Impressions', 'type' => 'integer', 'width' => '110px', 'sortable' => true, 'filter' => 'number_range', 'aggregate' => 'SUM', ), array( 'key' => 'ctr', 'label' => 'CTR', 'type' => 'percent', 'width' => '80px', 'sortable' => true, 'format' => array( 'of' => 'ratio', 'precision' => 1 ), ), array( 'key' => 'position', 'label' => 'Position', 'type' => 'decimal', 'width' => '90px', 'sortable' => true, 'filter' => 'number_range', 'format' => array( 'precision' => 1 ), 'bands' => array( array( 'max' => 10, 'tone' => 'success' ), array( 'min' => 10.01, 'max' => 20, 'tone' => 'warning' ), ), ), ); return array( array( 'id' => 'seo_gsc_terms', 'label' => 'Top Google Search queries', 'description' => '', 'source' => 'sql', 'sql' => 'aieo_gsc_terms_sql', 'registry_id' => 'seo_search_queries.history', 'scope' => 'local', 'version' => '1', 'per_page' => 50, 'params' => $aieo_gsc_params, 'row_key' => 'row_hash', 'default_sort' => array( 'impressions', 'desc' ), 'empty_state' => 'No stored Search Console rows for this window yet. Connect, or press Refresh data: the history table fills from Google and keeps growing.', 'columns' => array_merge( array( array( 'key' => 'row_hash', 'label' => 'Hash', 'type' => 'code', 'hidden' => true, ), array( 'key' => 'term', 'label' => 'Query or page', 'type' => 'text', 'sortable' => true, 'searchable' => true, 'filter' => 'text', 'truncate' => 80, 'tooltip' => true, ), ), $aieo_gsc_measures, array( array( 'key' => 'object_type', 'label' => 'Content type', 'type' => 'badge', 'width' => '120px', 'sortable' => true, 'filter' => 'select', 'empty' => '', ), array( 'key' => 'object_id', 'label' => 'Content', 'type' => 'object', 'width' => '200px', 'sortable' => true, 'empty' => '', 'empty_zero' => true, ), array( 'key' => 'last_seen', 'label' => 'Last seen', 'type' => 'date', 'width' => '110px', 'nowrap' => true, 'tone' => 'secondary', 'sortable' => true, 'hidden' => true, ), ) ), ), array( 'id' => 'seo_gsc_by_type', 'label' => 'Google traffic by content type', 'description' => '', 'source' => 'sql', 'sql' => 'aieo_gsc_by_type_sql', 'registry_id' => 'seo_search_queries.by_type', 'scope' => 'local', 'version' => '1', 'per_page' => 50, 'params' => $aieo_gsc_params, 'row_key' => 'ctype', 'default_sort' => array( 'impressions', 'desc' ), 'empty_state' => 'No stored page rows for this window yet.', 'columns' => array( array( 'key' => 'ctype', 'label' => 'Content type', 'type' => 'badge', 'sortable' => true, ), array( 'key' => 'pages', 'label' => 'Pages', 'type' => 'integer', 'width' => '100px', 'sortable' => true, 'aggregate' => 'SUM', ), array( 'key' => 'clicks', 'label' => 'Clicks', 'type' => 'integer', 'width' => '100px', 'sortable' => true, 'aggregate' => 'SUM', ), array( 'key' => 'impressions', 'label' => 'Impressions', 'type' => 'integer', 'width' => '120px', 'sortable' => true, 'aggregate' => 'SUM', ), array( 'key' => 'share', 'label' => 'Share of impressions', 'type' => 'percent', 'width' => '100px', 'sortable' => true, 'empty' => '', 'format' => array( 'of' => 'ratio', 'precision' => 1 ), ), ), ), );