prepare (deliberately NOT the esc_sql style of the legacy * REST/Analytics class). Dashboards read aggregated tables only — never the raw * events table. * * GET /analytics/summary?days=N KPI strip * GET /analytics/articles?days=N&... paginated top articles * GET /analytics/articles/{id}?days=N per-article daily series (drill-down) * * Search / engagement / feedback / link endpoints ship with their module units. */ class AnalyticsReports extends BaseAPI { public function register() { $this->get( '/analytics', [ $this, 'index' ], [] ); $this->get( '/analytics/summary', [ $this, 'summary' ], [ 'days' => $this->days_arg(), 'kb' => $this->kb_arg() ] ); $this->get( '/analytics/articles', [ $this, 'articles' ], [ 'days' => $this->days_arg(), 'kb' => $this->kb_arg(), 'per_page' => [ 'type' => 'integer', 'default' => 20, 'sanitize_callback' => function ( $p ) { $p = (int) $p; return ( $p >= 1 && $p <= 100 ) ? $p : 20; } ], 'page' => [ 'type' => 'integer', 'default' => 1, 'sanitize_callback' => function ( $p ) { $p = (int) $p; return $p >= 1 ? $p : 1; } ] ] ); $this->get( '/analytics/articles/(?P\d+)', [ $this, 'article_detail' ], [ 'days' => $this->days_arg(), 'id' => [ 'type' => 'integer', 'required' => true, 'validate_callback' => function ( $p ) { return ! empty( $p ) && is_numeric( $p ); } ] ] ); // Article Performance sub-tabs: docs by category / by knowledge base. // Same paginated, date-windowed shape as /analytics/articles. $leading_args = array_merge( [ 'days' => $this->days_arg(), 'kb' => $this->kb_arg() ], $this->pagination_args() ); $this->get( '/analytics/categories', [ $this, 'categories' ], $leading_args ); $this->get( '/analytics/knowledge-bases', [ $this, 'knowledge_bases' ], $leading_args ); // Reactions: happy/neutral/unhappy KPIs + Most/Least Helpful docs. $this->get( '/analytics/reactions', [ $this, 'reactions' ], array_merge( [ 'days' => $this->days_arg(), 'kb' => $this->kb_arg(), 'order' => [ 'type' => 'string', 'default' => 'most', 'sanitize_callback' => function ( $p ) { return $p === 'least' ? 'least' : 'most'; } ] ], $this->pagination_args() ) ); $this->get( '/analytics/search', [ $this, 'search' ], [ 'days' => $this->days_arg() ] ); $this->get( '/analytics/engagement', [ $this, 'engagement' ], [ 'days' => $this->days_arg(), 'kb' => $this->kb_arg() ] ); $this->get( '/analytics/feedback', [ $this, 'feedback' ], [ 'days' => $this->days_arg(), 'kb' => $this->kb_arg(), 'status' => [ 'type' => 'string', 'default' => '' ], 'feeling' => [ 'type' => 'string', 'default' => '' ], 'per_page' => [ 'type' => 'integer', 'default' => 20, 'sanitize_callback' => function ( $p ) { $p = (int) $p; return ( $p >= 1 && $p <= 100 ) ? $p : 20; } ], 'page' => [ 'type' => 'integer', 'default' => 1, 'sanitize_callback' => function ( $p ) { $p = (int) $p; return $p >= 1 ? $p : 1; } ] ] ); $this->post( '/analytics/feedback/bulk', [ $this, 'feedback_bulk' ], [ 'ids' => [ 'type' => 'array', 'required' => true, 'items' => [ 'type' => 'integer' ] ], 'status' => [ 'type' => 'string', 'required' => true, 'validate_callback' => function ( $p ) { return in_array( $p, [ 'new', 'reviewed', 'resolved' ], true ); } ] ] ); $this->get( '/analytics/links', [ $this, 'links' ], [ 'kb' => $this->kb_arg(), 'per_page' => [ 'type' => 'integer', 'default' => 20, 'sanitize_callback' => function ( $p ) { $p = (int) $p; return ( $p >= 1 && $p <= 100 ) ? $p : 20; } ], 'page' => [ 'type' => 'integer', 'default' => 1, 'sanitize_callback' => function ( $p ) { $p = (int) $p; return $p >= 1 ? $p : 1; } ] ] ); $this->post( '/analytics/links/scan', [ $this, 'links_scan' ], [] ); // GeoIP status + on-demand database download (settings-tab "Download now"). $this->get( '/analytics/geoip/status', [ $this, 'geoip_status' ], [] ); $this->post( '/analytics/geoip/refresh', [ $this, 'geoip_refresh' ], [] ); // GA4 "send test event" — definitive connection check for the MP method. $this->post( '/analytics/ga4/test', [ $this, 'ga4_test' ], [] ); $this->get( '/analytics/authors', [ $this, 'authors' ], [ 'days' => $this->days_arg(), 'kb' => $this->kb_arg() ] ); $this->get( '/analytics/export/(?P[a-z_]+)', [ $this, 'export' ], [ 'days' => $this->days_arg(), 'kb' => $this->kb_arg(), 'module' => [ 'type' => 'string', 'required' => true, 'validate_callback' => function ( $p ) { return in_array( $p, [ 'overview', 'articles', 'categories', 'knowledge_bases', 'reactions', 'search', 'feedback', 'links', 'authors', 'engagement' ], true ); } ] ] ); } public function permission_check( $request = null ) { // Mutations (e.g. feedback bulk status, link scan) need an editor capability; // reads honor the analytics read capability + the analytics_roles setting. if ( $request instanceof WP_REST_Request && $request->get_method() !== 'GET' ) { return current_user_can( 'edit_docs_settings' ); } return $this->can_read(); } /** * Read access (RBAC): the read_docs_analytics cap, an admin, or a role * listed in the `analytics_roles` setting. */ private function can_read() { if ( current_user_can( 'read_docs_analytics' ) || current_user_can( 'manage_options' ) ) { return true; } $roles = (array) betterdocs()->settings->get( 'analytics_roles', [] ); if ( empty( $roles ) || ! is_user_logged_in() ) { return false; } $user = wp_get_current_user(); return ! empty( array_intersect( $roles, (array) $user->roles ) ); } /** * Read-only API discovery: lists the available report endpoints. */ public function index() { return new WP_REST_Response( [ 'version' => '1.0', 'readonly' => true, 'endpoints' => [ 'summary', 'articles', 'articles/{id}', 'search', 'engagement', 'feedback', 'links', 'authors', 'export/{module}' ] ], 200 ); } private function days_arg() { return [ // No strict `integer` type — the "All time" preset sends days=all, // which is mapped to the full data span in resolve_window(). 'default' => 30, 'sanitize_callback' => function ( $p ) { if ( 'all' === $p ) { return 'all'; } $p = (int) $p; // Pro retains 12 months. return ( $p >= 1 && $p <= 365 ) ? $p : 30; } ]; } private function cutoff_date( $days ) { return gmdate( 'Y-m-d', strtotime( '-' . (int) $days . ' days' ) ); } /** * Earliest recorded date across the analytics + search-log tables — the lower * bound for the "All time" window. Falls back to 30 days ago when empty. */ private function earliest_date() { global $wpdb; $a = $wpdb->get_var( "SELECT MIN( created_at ) FROM {$wpdb->prefix}betterdocs_analytics" ); $s = $wpdb->get_var( "SELECT MIN( created_at ) FROM {$wpdb->prefix}betterdocs_search_log" ); $dates = array_filter( [ $a, $s ], function ( $d ) { return $d && '0000-00-00' !== $d; } ); return empty( $dates ) ? gmdate( 'Y-m-d', strtotime( '-30 days' ) ) : min( $dates ); } /** * YYYY-MM-DD or '' — guards the custom-range params before they touch SQL. */ private function sanitize_date( $value ) { $value = (string) $value; return preg_match( '/^\d{4}-\d{2}-\d{2}$/', $value ) ? $value : ''; } /** * Resolve the active date window for a request: returns [ $start, $end, $days ] * where $start/$end are inclusive Y-m-d bounds and $days is the inclusive day * span (used for trend loops and the previous-window delta). * * A valid start_date + end_date pair (the "Custom range" calendar) wins; * otherwise the `days` preset (last N days, ending today) is used — so every * dashboard endpoint becomes range-aware through a single call. start_date / * end_date are read raw and sanitized here, so routes need not register them. */ private function resolve_window( WP_REST_Request $request ) { $start = $this->sanitize_date( $request->get_param( 'start_date' ) ); $end = $this->sanitize_date( $request->get_param( 'end_date' ) ); if ( $start && $end ) { if ( $start > $end ) { $tmp = $start; $start = $end; $end = $tmp; } $days = (int) round( ( strtotime( $end ) - strtotime( $start ) ) / DAY_IN_SECONDS ); return [ $start, $end, max( 1, $days ) ]; } // "All time" — span from the earliest recorded data through today, so the // shared `created_at >= start AND <= end` clauses impose no real lower bound. if ( 'all' === $request->get_param( 'days' ) ) { $start = $this->earliest_date(); $end = gmdate( 'Y-m-d' ); $days = max( 1, (int) round( ( strtotime( $end ) - strtotime( $start ) ) / DAY_IN_SECONDS ) + 1 ); return [ $start, $end, $days ]; } $days = (int) $request->get_param( 'days' ); if ( $days < 1 || $days > 365 ) { $days = 30; } return [ $this->cutoff_date( $days ), gmdate( 'Y-m-d' ), $days ]; } /** * Shared per_page (1–100, default 20) + page (>=1) arg schema for the * paginated list endpoints. */ private function pagination_args() { return [ 'per_page' => [ 'type' => 'integer', 'default' => 20, 'sanitize_callback' => function ( $p ) { $p = (int) $p; return ( $p >= 1 && $p <= 100 ) ? $p : 20; } ], 'page' => [ 'type' => 'integer', 'default' => 1, 'sanitize_callback' => function ( $p ) { $p = (int) $p; return $p >= 1 ? $p : 1; } ] ]; } private function kb_arg() { // Wrap sanitize_title in a closure: WP invokes sanitize_callback as // ( $value, $request, $param ), and sanitize_title's 2nd arg is a fallback // title — passing $request there returns the request object for empty input. return [ 'type' => 'string', 'default' => '', 'sanitize_callback' => function ( $p ) { return sanitize_title( (string) $p ); } ]; } /** * Leading SQL fragment ( " AND IN ( … )" ) constraining a post-id column * to docs in the given knowledge_base slug — used by the Knowledge Base header * filter (only present when Multiple Knowledge Base is enabled). Returns '' when * no KB is selected or the slug is unknown, so callers concatenate it * unconditionally. The fragment resolves to a literal integer term_taxonomy_id * (no `%` placeholders left), so it is safe to embed inside another prepare(). * `$col` is always a hardcoded column name, never user input. */ private function kb_filter( $kb, $col = 'post_id' ) { if ( empty( $kb ) ) { return ''; } $term = get_term_by( 'slug', $kb, 'knowledge_base' ); if ( ! $term || is_wp_error( $term ) ) { return ''; } global $wpdb; return $wpdb->prepare( " AND {$col} IN ( SELECT object_id FROM {$wpdb->term_relationships} WHERE term_taxonomy_id = %d )", (int) $term->term_taxonomy_id ); } public function summary( WP_REST_Request $request ) { global $wpdb; list( $cutoff, $end, $days ) = $this->resolve_window( $request ); // Knowledge Base filter (post-scoped). Search totals stay global — the // search log is keyword-scoped and has no post/KB association. $kb = $this->kb_filter( (string) $request->get_param( 'kb' ) ); $views = $wpdb->get_row( $wpdb->prepare( "SELECT COALESCE( SUM( views ), 0 ) AS total_views, COALESCE( SUM( unique_views ), 0 ) AS total_unique_views FROM {$wpdb->prefix}betterdocs_analytics_daily WHERE stat_date >= %s AND stat_date <= %s{$kb}", $cutoff, $end ), ARRAY_A ); // Reactions still live in the legacy aggregate table. $reactions = $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE( SUM( happy + sad + normal ), 0 ) FROM {$wpdb->prefix}betterdocs_analytics WHERE created_at >= %s AND created_at <= %s{$kb}", $cutoff, $end ) ); $search = $wpdb->get_row( $wpdb->prepare( "SELECT COALESCE( SUM( count ), 0 ) AS total_searches, COALESCE( SUM( not_found_count ), 0 ) AS total_not_found FROM {$wpdb->prefix}betterdocs_search_log WHERE created_at >= %s AND created_at <= %s", $cutoff, $end ), ARRAY_A ); // Per-day series powering the KPI card sparklines (zero-filled over the window). $views_daily = $wpdb->get_results( $wpdb->prepare( "SELECT stat_date AS d, COALESCE( SUM( views ), 0 ) AS views, COALESCE( SUM( unique_views ), 0 ) AS uniques FROM {$wpdb->prefix}betterdocs_analytics_daily WHERE stat_date >= %s AND stat_date <= %s{$kb} GROUP BY stat_date", $cutoff, $end ), ARRAY_A ); $search_daily = $wpdb->get_results( $wpdb->prepare( "SELECT DATE( created_at ) AS d, COALESCE( SUM( count ), 0 ) AS c FROM {$wpdb->prefix}betterdocs_search_log WHERE created_at >= %s AND created_at <= %s GROUP BY DATE( created_at )", $cutoff, $end ), ARRAY_A ); $reactions_daily = $wpdb->get_results( $wpdb->prepare( "SELECT DATE( created_at ) AS d, COALESCE( SUM( happy + sad + normal ), 0 ) AS c FROM {$wpdb->prefix}betterdocs_analytics WHERE created_at >= %s AND created_at <= %s{$kb} GROUP BY DATE( created_at )", $cutoff, $end ), ARRAY_A ); $views_map = $uniques_map = $search_map = $reactions_map = []; foreach ( (array) $views_daily as $r ) { $views_map[ $r['d'] ] = (int) $r['views']; $uniques_map[ $r['d'] ] = (int) $r['uniques']; } foreach ( (array) $search_daily as $r ) { $search_map[ $r['d'] ] = (int) $r['c']; } foreach ( (array) $reactions_daily as $r ) { $reactions_map[ $r['d'] ] = (int) $r['c']; } $spark_views = $spark_uniques = $spark_searches = $spark_reactions = []; $trend = []; $cursor = strtotime( $cutoff ); $end_ts = strtotime( $end ); while ( $cursor <= $end_ts ) { $d = gmdate( 'Y-m-d', $cursor ); $spark_views[] = $views_map[ $d ] ?? 0; $spark_uniques[] = $uniques_map[ $d ] ?? 0; $spark_searches[] = $search_map[ $d ] ?? 0; $spark_reactions[] = $reactions_map[ $d ] ?? 0; // Date-labeled series for the Trends charts (Article Performance, Overview). $trend[] = [ 'date' => $d, 'views' => $views_map[ $d ] ?? 0, 'uniques' => $uniques_map[ $d ] ?? 0, 'searches' => $search_map[ $d ] ?? 0, 'reactions' => $reactions_map[ $d ] ?? 0 ]; $cursor += DAY_IN_SECONDS; } // Previous equal-length window (immediately before $cutoff), for the // "vs prev" delta on each KPI card. $prev_cutoff = gmdate( 'Y-m-d', strtotime( "-{$days} days", strtotime( $cutoff ) ) ); $prev_views = $wpdb->get_row( $wpdb->prepare( "SELECT COALESCE( SUM( views ), 0 ) AS total_views, COALESCE( SUM( unique_views ), 0 ) AS total_unique_views FROM {$wpdb->prefix}betterdocs_analytics_daily WHERE stat_date >= %s AND stat_date < %s{$kb}", $prev_cutoff, $cutoff ), ARRAY_A ); $prev_reactions = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE( SUM( happy + sad + normal ), 0 ) FROM {$wpdb->prefix}betterdocs_analytics WHERE created_at >= %s AND created_at < %s{$kb}", $prev_cutoff, $cutoff ) ); $prev_searches = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE( SUM( count ), 0 ) FROM {$wpdb->prefix}betterdocs_search_log WHERE created_at >= %s AND created_at < %s", $prev_cutoff, $cutoff ) ); // Percentage change vs the previous window; null when there is no baseline. $delta = function ( $current, $previous ) { $previous = (int) $previous; if ( $previous <= 0 ) { return null; } return round( ( ( (int) $current - $previous ) / $previous ) * 100, 1 ); }; return new WP_REST_Response( [ 'days' => $days, 'kpis' => [ 'total_views' => (int) ( $views['total_views'] ?? 0 ), 'total_unique_views' => (int) ( $views['total_unique_views'] ?? 0 ), 'total_reactions' => (int) $reactions, 'total_searches' => (int) ( $search['total_searches'] ?? 0 ), 'total_not_found' => (int) ( $search['total_not_found'] ?? 0 ) ], 'spark' => [ 'total_views' => $spark_views, 'total_unique_views' => $spark_uniques, 'total_searches' => $spark_searches, 'total_reactions' => $spark_reactions ], 'delta' => [ 'total_views' => $delta( $views['total_views'] ?? 0, $prev_views['total_views'] ?? 0 ), 'total_unique_views' => $delta( $views['total_unique_views'] ?? 0, $prev_views['total_unique_views'] ?? 0 ), 'total_searches' => $delta( $search['total_searches'] ?? 0, $prev_searches ), 'total_reactions' => $delta( $reactions, $prev_reactions ) ], 'trend' => $trend ], 200 ); } public function articles( WP_REST_Request $request ) { global $wpdb; list( $start, $end, $days ) = $this->resolve_window( $request ); $per_page = (int) $request->get_param( 'per_page' ); $page = (int) $request->get_param( 'page' ); $offset = ( $page - 1 ) * $per_page; $kb = $this->kb_filter( (string) $request->get_param( 'kb' ) ); $total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT( DISTINCT post_id ) FROM {$wpdb->prefix}betterdocs_analytics_daily WHERE stat_date >= %s AND stat_date <= %s{$kb}", $start, $end ) ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT post_id, SUM( views ) AS views, SUM( unique_views ) AS unique_views FROM {$wpdb->prefix}betterdocs_analytics_daily WHERE stat_date >= %s AND stat_date <= %s{$kb} GROUP BY post_id ORDER BY views DESC LIMIT %d OFFSET %d", $start, $end, $per_page, $offset ) ); // Reactions live in the legacy aggregate (same source as summary/authors). // Fetch them only for this page's posts in one query, then merge. $reactions_map = []; $post_ids = array_map( 'intval', wp_list_pluck( (array) $rows, 'post_id' ) ); if ( ! empty( $post_ids ) ) { $ids_in = implode( ',', $post_ids ); $reaction_rows = $wpdb->get_results( $wpdb->prepare( "SELECT post_id, SUM( happy + sad + normal ) AS reactions FROM {$wpdb->prefix}betterdocs_analytics WHERE created_at >= %s AND created_at <= %s AND post_id IN ({$ids_in}) GROUP BY post_id", $start, $end ) ); foreach ( (array) $reaction_rows as $rr ) { $reactions_map[ (int) $rr->post_id ] = (int) $rr->reactions; } } $items = []; foreach ( (array) $rows as $row ) { $post_id = (int) $row->post_id; $items[] = [ 'post_id' => $post_id, 'title' => get_the_title( $post_id ), 'permalink' => get_permalink( $post_id ), 'status' => get_post_status( $post_id ), 'views' => (int) $row->views, 'unique_views' => (int) $row->unique_views, 'reactions' => $reactions_map[ $post_id ] ?? 0 ]; } return new WP_REST_Response( [ 'days' => $days, 'page' => $page, 'per_page' => $per_page, 'total' => $total, 'items' => $items ], 200 ); } /** * Shared engine for the Article Performance "Categories" and "Knowledge Base" * sub-tabs: aggregate analytics_daily by the doc's terms in $taxonomy, paged * and date-windowed exactly like articles(). Joining through term_relationships * (not analytics_daily.kb_id, which is 0 for rows backfilled from older * versions) keeps historical/migrated data visible. * * @param string $taxonomy 'doc_category' or 'knowledge_base'. * @param bool $apply_kb Honor the KB header filter (categories) vs. ignore * it (the KB tab is itself a per-KB breakdown). */ private function leading_terms( WP_REST_Request $request, $taxonomy, $apply_kb = true ) { global $wpdb; list( $start, $end ) = $this->resolve_window( $request ); $per_page = (int) $request->get_param( 'per_page' ); $page = (int) $request->get_param( 'page' ); $offset = ( $page - 1 ) * $per_page; $kb = $apply_kb ? $this->kb_filter( (string) $request->get_param( 'kb' ), 'd.post_id' ) : ''; $daily = $wpdb->prefix . 'betterdocs_analytics_daily'; // Published docs only — matches the old dashboard's definition so term // totals don't include drafts/trashed/deleted docs (QA #15). $from = "FROM {$daily} d JOIN {$wpdb->posts} p ON p.ID = d.post_id AND p.post_status = 'publish' JOIN {$wpdb->term_relationships} tr ON tr.object_id = d.post_id JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = %s WHERE d.stat_date >= %s AND d.stat_date <= %s{$kb}"; $total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT( DISTINCT tt.term_id ) {$from}", $taxonomy, $start, $end ) ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT tt.term_id AS term_id, SUM( d.views ) AS views, SUM( d.unique_views ) AS unique_views {$from} GROUP BY tt.term_id ORDER BY views DESC LIMIT %d OFFSET %d", $taxonomy, $start, $end, $per_page, $offset ) ); // Reactions come from the LIVE legacy aggregate — analytics_daily's // happy/sad/normal are only ever written by the one-time backfill (the // aggregator never updates them), so sourcing them there would freeze // term reactions at the update snapshot (QA #15; same pattern articles() // already uses). $kb_legacy = $apply_kb ? $this->kb_filter( (string) $request->get_param( 'kb' ), 'a.post_id' ) : ''; $reaction_rows = $wpdb->get_results( $wpdb->prepare( "SELECT tt.term_id AS term_id, SUM( a.happy + a.sad + a.normal ) AS reactions FROM {$wpdb->prefix}betterdocs_analytics a JOIN {$wpdb->posts} p ON p.ID = a.post_id AND p.post_status = 'publish' JOIN {$wpdb->term_relationships} tr ON tr.object_id = a.post_id JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = %s WHERE a.created_at >= %s AND a.created_at <= %s{$kb_legacy} GROUP BY tt.term_id", $taxonomy, $start, $end ) ); $reactions_map = []; foreach ( (array) $reaction_rows as $r ) { $reactions_map[ (int) $r->term_id ] = (int) $r->reactions; } $items = []; foreach ( (array) $rows as $row ) { $term = get_term( (int) $row->term_id ); if ( ! $term || is_wp_error( $term ) ) { continue; } $link = get_term_link( $term ); $items[] = [ 'term_id' => (int) $row->term_id, 'name' => $term->name, 'slug' => $term->slug, 'link' => is_wp_error( $link ) ? '' : $link, 'views' => (int) $row->views, 'unique_views' => (int) $row->unique_views, 'reactions' => isset( $reactions_map[ (int) $row->term_id ] ) ? $reactions_map[ (int) $row->term_id ] : 0 ]; } return new WP_REST_Response( [ 'page' => $page, 'per_page' => $per_page, 'total' => $total, 'items' => $items ], 200 ); } /** Article Performance → Categories sub-tab (doc_category breakdown). */ public function categories( WP_REST_Request $request ) { return $this->leading_terms( $request, 'doc_category', true ); } /** Article Performance → Knowledge Base sub-tab (knowledge_base breakdown). */ public function knowledge_bases( WP_REST_Request $request ) { return $this->leading_terms( $request, 'knowledge_base', false ); } /** * Reactions analytics: happy / neutral / unhappy KPI totals + a paginated docs * list ranked by helpfulness. `order=most` (default) ranks by happy reactions, * `order=least` by unhappy (sad) — mirroring the legacy Most/Least Helpful tabs. * Reactions are read from the legacy aggregate (same source as summary/authors). */ public function reactions( WP_REST_Request $request ) { global $wpdb; list( $start, $end, $days ) = $this->resolve_window( $request ); $per_page = (int) $request->get_param( 'per_page' ); $page = (int) $request->get_param( 'page' ); $offset = ( $page - 1 ) * $per_page; $least = ( $request->get_param( 'order' ) === 'least' ); $kb = $this->kb_filter( (string) $request->get_param( 'kb' ) ); $table = $wpdb->prefix . 'betterdocs_analytics'; // KPI totals over the window (order-independent). $kpis = $wpdb->get_row( $wpdb->prepare( "SELECT COALESCE( SUM( happy ), 0 ) AS happy, COALESCE( SUM( normal ), 0 ) AS normal, COALESCE( SUM( sad ), 0 ) AS sad FROM {$table} WHERE created_at >= %s AND created_at <= %s{$kb}", $start, $end ), ARRAY_A ); // Per-day series powering each KPI card's sparkline (zero-filled over the window). $daily = $wpdb->get_results( $wpdb->prepare( "SELECT created_at AS d, COALESCE( SUM( happy ), 0 ) AS h, COALESCE( SUM( normal ), 0 ) AS n, COALESCE( SUM( sad ), 0 ) AS s FROM {$table} WHERE created_at >= %s AND created_at <= %s{$kb} GROUP BY created_at", $start, $end ), ARRAY_A ); $hm = $nm = $sm = []; foreach ( (array) $daily as $r ) { $hm[ $r['d'] ] = (int) $r['h']; $nm[ $r['d'] ] = (int) $r['n']; $sm[ $r['d'] ] = (int) $r['s']; } $spark_happy = $spark_normal = $spark_sad = []; $trend = []; $cursor = strtotime( $start ); $end_ts = strtotime( $end ); while ( $cursor <= $end_ts ) { $d = gmdate( 'Y-m-d', $cursor ); $spark_happy[] = $hm[ $d ] ?? 0; $spark_normal[] = $nm[ $d ] ?? 0; $spark_sad[] = $sm[ $d ] ?? 0; // Date-labeled series for the Reactions Trends chart. $trend[] = [ 'date' => $d, 'happy' => $hm[ $d ] ?? 0, 'normal' => $nm[ $d ] ?? 0, 'sad' => $sm[ $d ] ?? 0 ]; $cursor += DAY_IN_SECONDS; } // Previous equal-length window (immediately before $start) for the "vs prev" delta. $prev_cutoff = gmdate( 'Y-m-d', strtotime( "-{$days} days", strtotime( $start ) ) ); $prev = $wpdb->get_row( $wpdb->prepare( "SELECT COALESCE( SUM( happy ), 0 ) AS happy, COALESCE( SUM( normal ), 0 ) AS normal, COALESCE( SUM( sad ), 0 ) AS sad FROM {$table} WHERE created_at >= %s AND created_at < %s{$kb}", $prev_cutoff, $start ), ARRAY_A ); $pct = function ( $current, $previous ) { $previous = (int) $previous; if ( $previous <= 0 ) { return null; } return round( ( ( (int) $current - $previous ) / $previous ) * 100, 1 ); }; // Rank + filter column are hardcoded (never user input): happy for Most // Helpful, sad for Least Helpful; only docs with that reaction are listed. $rank_col = $least ? 'sad' : 'happy'; $total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM ( SELECT post_id FROM {$table} WHERE created_at >= %s AND created_at <= %s{$kb} GROUP BY post_id HAVING SUM( {$rank_col} ) > 0 ) t", $start, $end ) ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT post_id, SUM( happy ) AS happy, SUM( normal ) AS normal, SUM( sad ) AS sad, SUM( happy + normal + sad ) AS total FROM {$table} WHERE created_at >= %s AND created_at <= %s{$kb} GROUP BY post_id HAVING SUM( {$rank_col} ) > 0 ORDER BY {$rank_col} DESC LIMIT %d OFFSET %d", $start, $end, $per_page, $offset ) ); $items = []; foreach ( (array) $rows as $row ) { $post_id = (int) $row->post_id; $items[] = [ 'post_id' => $post_id, 'title' => get_the_title( $post_id ), 'permalink' => get_permalink( $post_id ), 'status' => get_post_status( $post_id ), 'happy' => (int) $row->happy, 'normal' => (int) $row->normal, 'sad' => (int) $row->sad, 'total' => (int) $row->total ]; } return new WP_REST_Response( [ 'kpis' => [ 'happy' => (int) ( $kpis['happy'] ?? 0 ), 'normal' => (int) ( $kpis['normal'] ?? 0 ), 'sad' => (int) ( $kpis['sad'] ?? 0 ) ], 'spark' => [ 'happy' => $spark_happy, 'normal' => $spark_normal, 'sad' => $spark_sad ], 'delta' => [ 'happy' => $pct( $kpis['happy'] ?? 0, $prev['happy'] ?? 0 ), 'normal' => $pct( $kpis['normal'] ?? 0, $prev['normal'] ?? 0 ), 'sad' => $pct( $kpis['sad'] ?? 0, $prev['sad'] ?? 0 ) ], 'trend' => $trend, 'order' => $least ? 'least' : 'most', 'page' => $page, 'per_page' => $per_page, 'total' => $total, 'items' => $items ], 200 ); } public function article_detail( WP_REST_Request $request ) { global $wpdb; list( $cutoff, $end, $days ) = $this->resolve_window( $request ); $post_id = (int) $request->get_param( 'id' ); $end_dt = $end . ' 23:59:59'; // Only expose analytics for actual docs — never let an arbitrary post/CPT // id surface another object's title, status, or feedback comments. if ( 'docs' !== get_post_type( $post_id ) ) { return new WP_REST_Response( [ 'error' => 'not_found' ], 404 ); } $series = $wpdb->get_results( $wpdb->prepare( "SELECT stat_date, SUM( views ) AS views, SUM( unique_views ) AS unique_views, AVG( avg_scroll_depth ) AS avg_scroll_depth, SUM( reading_completions ) AS reading_completions FROM {$wpdb->prefix}betterdocs_analytics_daily WHERE post_id = %d AND stat_date >= %s AND stat_date <= %s GROUP BY stat_date ORDER BY stat_date ASC", $post_id, $cutoff, $end ) ); // Reactions (happy/sad/normal) live in the legacy aggregate table — the // daily rollup never populates those columns — so read them from // betterdocs_analytics keyed by day, matching every other endpoint // (summary / authors / reactions). Without this the drawer showed 0. $reactions_by_date = []; $reaction_rows = $wpdb->get_results( $wpdb->prepare( "SELECT DATE( created_at ) AS stat_date, SUM( happy ) AS likes, SUM( sad ) AS dislikes, SUM( normal ) AS neutral FROM {$wpdb->prefix}betterdocs_analytics WHERE post_id = %d AND created_at >= %s AND created_at <= %s GROUP BY DATE( created_at )", $post_id, $cutoff, $end_dt ) ); foreach ( (array) $reaction_rows as $rrow ) { $reactions_by_date[ $rrow->stat_date ] = [ (int) $rrow->likes, (int) $rrow->dislikes, (int) $rrow->neutral ]; } $trend = []; $reactions_trend = []; $total_views = 0; $total_unique = 0; $total_completions = 0; $total_likes = 0; $total_dislikes = 0; $total_neutral = 0; $scroll_sum = 0.0; $scroll_days = 0; foreach ( (array) $series as $row ) { $v = (int) $row->views; $u = (int) $row->unique_views; list( $l, $d, $n ) = $reactions_by_date[ $row->stat_date ] ?? [ 0, 0, 0 ]; $total_views += $v; $total_unique += $u; $total_likes += $l; $total_dislikes += $d; $total_neutral += $n; $total_completions += (int) $row->reading_completions; if ( (float) $row->avg_scroll_depth > 0 ) { $scroll_sum += (float) $row->avg_scroll_depth; $scroll_days++; } $trend[] = [ 'date' => $row->stat_date, 'views' => $v, 'unique_views' => $u ]; $reactions_trend[] = [ 'date' => $row->stat_date, 'likes' => $l, 'dislikes' => $d, 'neutral' => $n ]; } // Country / device breakdown + raw referrers from the events payload. // payload is JSON: {"referrer": "...", "device": "...", "country": "XX"}. $events_table = $wpdb->prefix . 'betterdocs_analytics_events'; $breakdown_rows = $wpdb->get_results( $wpdb->prepare( "SELECT JSON_UNQUOTE( JSON_EXTRACT( payload, '$.country' ) ) AS country, JSON_UNQUOTE( JSON_EXTRACT( payload, '$.device' ) ) AS device, JSON_UNQUOTE( JSON_EXTRACT( payload, '$.referrer' ) ) AS referrer FROM {$events_table} WHERE object_id = %d AND created_at >= %s AND created_at <= %s", $post_id, $cutoff, $end_dt ), ARRAY_A ); $countries = []; $devices = []; $referrers = []; foreach ( (array) $breakdown_rows as $row ) { $c = isset( $row['country'] ) && $row['country'] !== 'null' ? strtoupper( trim( $row['country'] ) ) : ''; $d = isset( $row['device'] ) && $row['device'] !== 'null' ? strtolower( trim( $row['device'] ) ) : ''; if ( $c !== '' ) { $countries[ $c ] = ( $countries[ $c ] ?? 0 ) + 1; } if ( $d !== '' ) { $devices[ $d ] = ( $devices[ $d ] ?? 0 ) + 1; } if ( ! empty( $row['referrer'] ) && $row['referrer'] !== 'null' ) { $referrers[] = $row['referrer']; } } arsort( $countries ); arsort( $devices ); // Search sources — the standard "analytics SaaS" approach: parse each // pageview's referrer for a search query parameter and aggregate. Catches // both the site's own search (?s=, ?search=) and external search engines // that still pass the query (?q=, ?query=). $query_keys = [ 's', 'q', 'search', 'query' ]; $sources = []; foreach ( $referrers as $ref ) { $parts = wp_parse_url( $ref ); if ( empty( $parts['query'] ) ) { continue; } parse_str( $parts['query'], $params ); foreach ( $query_keys as $k ) { if ( ! empty( $params[ $k ] ) && is_scalar( $params[ $k ] ) ) { $kw = trim( (string) $params[ $k ] ); if ( strlen( $kw ) >= 2 ) { $kw = mb_strtolower( $kw ); $sources[ $kw ] = ( $sources[ $kw ] ?? 0 ) + 1; } break; } } } arsort( $sources ); $sources = array_slice( $sources, 0, 10, true ); $top_queries = []; foreach ( $sources as $kw => $count ) { $top_queries[] = [ 'query' => $kw, 'count' => (int) $count ]; } $top_countries = []; foreach ( array_slice( $countries, 0, 6, true ) as $code => $count ) { $top_countries[] = [ 'country' => $code, 'count' => (int) $count ]; } $top_devices = []; foreach ( array_slice( $devices, 0, 4, true ) as $name => $count ) { $top_devices[] = [ 'device' => $name, 'count' => (int) $count ]; } // Recent feedback for this article (matches the Feedback Inbox shape). $feedback = $wpdb->get_results( $wpdb->prepare( "SELECT id, feeling, comment, created_at FROM {$wpdb->prefix}betterdocs_analytics_feedback WHERE post_id = %d AND created_at >= %s AND created_at <= %s ORDER BY created_at DESC LIMIT 10", $post_id, $cutoff, $end_dt ), ARRAY_A ); // Post status + edit URL for the drawer header (badge + "Open in editor"). $post = get_post( $post_id ); $status_obj = get_post_status_object( $post ? $post->post_status : 'publish' ); return new WP_REST_Response( [ 'days' => $days, 'post_id' => $post_id, 'title' => get_the_title( $post_id ), 'status' => [ 'slug' => $post ? (string) $post->post_status : '', 'label' => $status_obj ? (string) $status_obj->label : '' ], 'edit_url' => $post && current_user_can( 'edit_post', $post_id ) ? get_edit_post_link( $post_id, 'raw' ) : '', 'totals' => [ 'views' => $total_views, 'unique_views' => $total_unique, 'likes' => $total_likes, 'dislikes' => $total_dislikes, 'neutral' => $total_neutral, 'avg_scroll_depth' => $scroll_days ? round( $scroll_sum / $scroll_days ) : 0, 'reading_completions' => $total_completions ], 'trend' => $trend, 'reactions_trend' => $reactions_trend, 'breakdown' => [ 'countries' => $top_countries, 'devices' => $top_devices ], 'sources' => $top_queries, 'feedback' => array_map( function ( $f ) { return [ 'id' => (int) $f['id'], 'feeling' => (string) $f['feeling'], 'comment' => (string) $f['comment'], 'created_at' => (string) $f['created_at'] ]; }, (array) $feedback ) ], 200 ); } /** * Search analytics: popular queries, zero-result queries, daily trend + KPIs. * Reads the legacy betterdocs_search_* tables (already populated by Free). */ public function search( WP_REST_Request $request ) { global $wpdb; list( $cutoff, $end, $days ) = $this->resolve_window( $request ); $popular = $wpdb->get_results( $wpdb->prepare( "SELECT k.keyword AS keyword, SUM( l.count ) AS searches, SUM( l.not_found_count ) AS not_found FROM {$wpdb->prefix}betterdocs_search_log l INNER JOIN {$wpdb->prefix}betterdocs_search_keyword k ON k.id = l.keyword_id WHERE l.created_at >= %s AND l.created_at <= %s GROUP BY l.keyword_id ORDER BY searches DESC LIMIT 20", $cutoff, $end ) ); $zero_result = $wpdb->get_results( $wpdb->prepare( "SELECT k.keyword AS keyword, SUM( l.count ) AS searches, SUM( l.not_found_count ) AS not_found FROM {$wpdb->prefix}betterdocs_search_log l INNER JOIN {$wpdb->prefix}betterdocs_search_keyword k ON k.id = l.keyword_id WHERE l.created_at >= %s AND l.created_at <= %s GROUP BY l.keyword_id HAVING not_found > 0 ORDER BY not_found DESC LIMIT 20", $cutoff, $end ) ); $trend_rows = $wpdb->get_results( $wpdb->prepare( "SELECT created_at AS date, SUM( count ) AS searches, SUM( not_found_count ) AS not_found FROM {$wpdb->prefix}betterdocs_search_log WHERE created_at >= %s AND created_at <= %s GROUP BY created_at ORDER BY created_at ASC", $cutoff, $end ) ); $totals = $wpdb->get_row( $wpdb->prepare( "SELECT COALESCE( SUM( count ), 0 ) AS total_searches, COALESCE( SUM( not_found_count ), 0 ) AS total_not_found FROM {$wpdb->prefix}betterdocs_search_log WHERE created_at >= %s AND created_at <= %s", $cutoff, $end ), ARRAY_A ); $map_keywords = function ( $rows ) { $out = []; foreach ( (array) $rows as $r ) { $out[] = [ 'keyword' => $r->keyword, 'searches' => (int) $r->searches, 'not_found' => (int) $r->not_found ]; } return $out; }; $trend = []; foreach ( (array) $trend_rows as $r ) { $trend[] = [ 'date' => $r->date, 'searches' => (int) $r->searches, 'not_found' => (int) $r->not_found ]; } return new WP_REST_Response( [ 'days' => $days, 'kpis' => [ 'total_searches' => (int) ( $totals['total_searches'] ?? 0 ), 'total_not_found' => (int) ( $totals['total_not_found'] ?? 0 ) ], 'popular' => $map_keywords( $popular ), 'zero_result' => $map_keywords( $zero_result ), 'trend' => $trend ], 200 ); } /** * Reader engagement: device / country / referrer / language breakdowns from * the raw events table. device/country/referrer live in the JSON payload * (aggregated with JSON_EXTRACT — degrades to empty on MySQL without JSON); * language is the real `lang` column. */ public function engagement( WP_REST_Request $request ) { global $wpdb; // Events store created_at as a DATETIME, so bound to the end of the last day. list( $cutoff_d, $end_d, $days ) = $this->resolve_window( $request ); $cutoff = $cutoff_d . ' 00:00:00'; $end = $end_d . ' 23:59:59'; $events = $wpdb->prefix . 'betterdocs_analytics_events'; // Events store the doc id in `object_id`, so the KB filter scopes by that. $kb = $this->kb_filter( (string) $request->get_param( 'kb' ), 'object_id' ); $by_json = function ( $path ) use ( $wpdb, $events, $cutoff, $end, $kb ) { $rows = $wpdb->get_results( $wpdb->prepare( "SELECT JSON_UNQUOTE( JSON_EXTRACT( payload, %s ) ) AS k, COUNT(*) AS c FROM {$events} WHERE event_type = 'view' AND created_at >= %s AND created_at <= %s{$kb} GROUP BY k ORDER BY c DESC", $path, $cutoff, $end ) ); return is_array( $rows ) ? $rows : []; }; // Device. $device = []; foreach ( $by_json( '$.device' ) as $r ) { $label = ( $r->k === null || $r->k === '' ) ? 'unknown' : $r->k; $device[] = [ 'label' => $label, 'count' => (int) $r->c ]; } // Country — omit unresolved (no-GeoIP) rows so the Geography panel shows its // clean "needs MaxMind" empty-state instead of a misleading lone "Unknown" row. $geo = []; foreach ( $by_json( '$.country' ) as $r ) { if ( $r->k === null || $r->k === '' ) { continue; } $geo[] = [ 'label' => $r->k, 'count' => (int) $r->c ]; } // Referrer — collapse full URLs to host, "Direct" for empty. $ref_hosts = []; foreach ( $by_json( '$.referrer' ) as $r ) { $url = (string) $r->k; $host = $url === '' ? __( 'Direct', 'betterdocs-pro' ) : ( wp_parse_url( $url, PHP_URL_HOST ) ?: __( 'Direct', 'betterdocs-pro' ) ); $ref_hosts[ $host ] = ( $ref_hosts[ $host ] ?? 0 ) + (int) $r->c; } arsort( $ref_hosts ); $referrer = []; foreach ( array_slice( $ref_hosts, 0, 15, true ) as $host => $count ) { $referrer[] = [ 'label' => $host, 'count' => $count ]; } // Language — real column. $language = []; $lang_rows = $wpdb->get_results( $wpdb->prepare( "SELECT lang AS k, COUNT(*) AS c FROM {$events} WHERE event_type = 'view' AND created_at >= %s AND created_at <= %s{$kb} GROUP BY lang ORDER BY c DESC", $cutoff, $end ) ); foreach ( (array) $lang_rows as $r ) { $label = ( $r->k === null || $r->k === '' ) ? 'unknown' : $r->k; $language[] = [ 'label' => $label, 'count' => (int) $r->c ]; } return new WP_REST_Response( [ 'days' => $days, 'device' => $device, 'geo' => $geo, 'referrer' => $referrer, 'language' => $language, // Lets the Geography card pick a precise empty-state message: // no key / key saved but DB not downloaded yet / DB present but no data. 'geoip_available' => \WPDeveloper\BetterDocsPro\Core\AnalyticsGeoIP::is_available(), 'geoip_key_set' => '' !== trim( (string) betterdocs()->settings->get( 'maxmind_license_key', '' ) ) ], 200 ); } /** * Feedback Inbox: paginated per-item stream + status/feeling counts. * Filters: status (new|reviewed|resolved), feeling (happy|sad|normal). */ public function feedback( WP_REST_Request $request ) { global $wpdb; // Feedback created_at is a DATETIME — bound to the end of the last day. list( $cutoff_d, $end_d, $days ) = $this->resolve_window( $request ); $cutoff = $cutoff_d . ' 00:00:00'; $end = $end_d . ' 23:59:59'; $status = (string) $request->get_param( 'status' ); $feeling = (string) $request->get_param( 'feeling' ); $per = (int) $request->get_param( 'per_page' ); $page = (int) $request->get_param( 'page' ); $offset = ( $page - 1 ) * $per; $table = $wpdb->prefix . 'betterdocs_analytics_feedback'; $kb = $this->kb_filter( (string) $request->get_param( 'kb' ) ); // Build the filtered WHERE with prepared args. $where = 'created_at >= %s AND created_at <= %s' . $kb; $args = [ $cutoff, $end ]; if ( in_array( $status, [ 'new', 'reviewed', 'resolved' ], true ) ) { $where .= ' AND status = %s'; $args[] = $status; } if ( in_array( $feeling, [ 'happy', 'sad', 'normal' ], true ) ) { $where .= ' AND feeling = %s'; $args[] = $feeling; } $total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE {$where}", $args ) ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT id, post_id, feeling, comment, status, created_at FROM {$table} WHERE {$where} ORDER BY created_at DESC LIMIT %d OFFSET %d", array_merge( $args, [ $per, $offset ] ) ) ); $items = []; foreach ( (array) $rows as $r ) { $items[] = [ 'id' => (int) $r->id, 'post_id' => (int) $r->post_id, 'title' => get_the_title( (int) $r->post_id ), 'feeling' => $r->feeling, 'comment' => $r->comment, 'status' => $r->status, 'created_at' => $r->created_at ]; } // Status + feeling counts over the window (unfiltered by status/feeling). $status_counts = [ 'new' => 0, 'reviewed' => 0, 'resolved' => 0 ]; foreach ( (array) $wpdb->get_results( $wpdb->prepare( "SELECT status, COUNT(*) c FROM {$table} WHERE created_at >= %s AND created_at <= %s{$kb} GROUP BY status", $cutoff, $end ) ) as $r ) { if ( isset( $status_counts[ $r->status ] ) ) { $status_counts[ $r->status ] = (int) $r->c; } } $feeling_counts = [ 'happy' => 0, 'normal' => 0, 'sad' => 0 ]; foreach ( (array) $wpdb->get_results( $wpdb->prepare( "SELECT feeling, COUNT(*) c FROM {$table} WHERE created_at >= %s AND created_at <= %s{$kb} GROUP BY feeling", $cutoff, $end ) ) as $r ) { if ( isset( $feeling_counts[ $r->feeling ] ) ) { $feeling_counts[ $r->feeling ] = (int) $r->c; } } return new WP_REST_Response( [ 'days' => $days, 'page' => $page, 'per_page' => $per, 'total' => $total, 'items' => $items, 'status_counts' => $status_counts, 'feeling_counts' => $feeling_counts ], 200 ); } /** * Bulk-update feedback status. Requires edit capability (see permission_check). */ public function feedback_bulk( WP_REST_Request $request ) { global $wpdb; $ids = array_filter( array_map( 'intval', (array) $request->get_param( 'ids' ) ) ); $status = (string) $request->get_param( 'status' ); if ( empty( $ids ) ) { return new WP_REST_Response( [ 'updated' => 0 ], 200 ); } $table = $wpdb->prefix . 'betterdocs_analytics_feedback'; $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) ); $updated = $wpdb->query( $wpdb->prepare( "UPDATE {$table} SET status = %s WHERE id IN ( {$placeholders} )", array_merge( [ $status ], $ids ) ) ); return new WP_REST_Response( [ 'updated' => (int) $updated, 'status' => $status ], 200 ); } /** * Link Health: working/broken counts, last-scan time, and a paginated list * of broken links with the doc they live in. Anchors are excluded from counts. */ public function links( WP_REST_Request $request ) { global $wpdb; $per = (int) $request->get_param( 'per_page' ); $page = (int) $request->get_param( 'page' ); $offset = ( $page - 1 ) * $per; $table = $wpdb->prefix . 'betterdocs_analytics_links'; $kb = $this->kb_filter( (string) $request->get_param( 'kb' ) ); $counts = $wpdb->get_row( "SELECT COUNT(*) AS total, COALESCE( SUM( is_broken ), 0 ) AS broken FROM {$table} WHERE link_type IN ( 'internal', 'external' ){$kb}", ARRAY_A ); $total = (int) ( $counts['total'] ?? 0 ); $broken = (int) ( $counts['broken'] ?? 0 ); $total_broken = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE is_broken = 1{$kb}" ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT post_id, url, http_status, link_type, last_checked FROM {$table} WHERE is_broken = 1{$kb} ORDER BY last_checked DESC LIMIT %d OFFSET %d", $per, $offset ) ); $items = []; foreach ( (array) $rows as $r ) { $items[] = [ 'post_id' => (int) $r->post_id, 'title' => get_the_title( (int) $r->post_id ), 'url' => esc_url( $r->url ), 'http_status' => (int) $r->http_status, 'link_type' => $r->link_type, 'last_checked' => $r->last_checked ]; } $last = (int) get_option( 'betterdocs_analytics_link_scan_last', 0 ); return new WP_REST_Response( [ 'checked' => $total, 'broken' => $broken, 'working' => max( 0, $total - $broken ), 'total' => $total_broken, 'page' => $page, 'per_page' => $per, 'items' => $items, 'last_scanned' => $last ? gmdate( 'c', $last ) : null ], 200 ); } /** * Trigger a fresh Link Health scan (write — needs edit capability). */ public function links_scan() { $this->container->get( \WPDeveloper\BetterDocsPro\Core\AnalyticsLinkScanner::class )->start_scan(); return new WP_REST_Response( [ 'started' => true ], 200 ); } /** * Author Performance: views/unique/docs per doc author (from daily), plus * reactions from the legacy aggregate. */ public function authors( WP_REST_Request $request ) { global $wpdb; list( $cutoff, $end, $days ) = $this->resolve_window( $request ); $kb_raw = (string) $request->get_param( 'kb' ); $kb_d = $this->kb_filter( $kb_raw, 'd.post_id' ); $kb_a = $this->kb_filter( $kb_raw, 'a.post_id' ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT p.post_author AS author_id, SUM( d.views ) AS views, SUM( d.unique_views ) AS unique_views, COUNT( DISTINCT d.post_id ) AS docs FROM {$wpdb->prefix}betterdocs_analytics_daily d INNER JOIN {$wpdb->posts} p ON p.ID = d.post_id WHERE d.stat_date >= %s AND d.stat_date <= %s AND p.post_type = 'docs'{$kb_d} GROUP BY p.post_author ORDER BY views DESC LIMIT 50", $cutoff, $end ) ); $reaction_rows = $wpdb->get_results( $wpdb->prepare( "SELECT p.post_author AS author_id, SUM( a.happy + a.sad + a.normal ) AS reactions FROM {$wpdb->prefix}betterdocs_analytics a INNER JOIN {$wpdb->posts} p ON p.ID = a.post_id WHERE a.created_at >= %s AND a.created_at <= %s AND p.post_type = 'docs'{$kb_a} GROUP BY p.post_author", $cutoff, $end ) ); $reactions = []; foreach ( (array) $reaction_rows as $r ) { $reactions[ (int) $r->author_id ] = (int) $r->reactions; } $items = []; foreach ( (array) $rows as $r ) { $author_id = (int) $r->author_id; $items[] = [ 'author_id' => $author_id, 'name' => get_the_author_meta( 'display_name', $author_id ) ?: __( 'Unknown', 'betterdocs-pro' ), 'docs' => (int) $r->docs, 'views' => (int) $r->views, 'unique_views' => (int) $r->unique_views, 'reactions' => $reactions[ $author_id ] ?? 0 ]; } return new WP_REST_Response( [ 'days' => $days, 'items' => $items ], 200 ); } /** * GeoIP status for the settings UI: key set, DB present, last attempt outcome. */ public function geoip_status() { return new WP_REST_Response( \WPDeveloper\BetterDocsPro\Core\AnalyticsGeoIP::status(), 200 ); } /** * On-demand GeoLite2 download ("Download now" button). Runs synchronously — * the admin is waiting on the button — and returns the fresh status, whose * last_error carries the invalid-key / extraction message when it fails. */ public function geoip_refresh() { $geoip = $this->container->get( \WPDeveloper\BetterDocsPro\Core\AnalyticsGeoIP::class ); $geoip->update_database(); return new WP_REST_Response( \WPDeveloper\BetterDocsPro\Core\AnalyticsGeoIP::status(), 200 ); } /** * GA4 connection test. For the Measurement Protocol method this is definitive: * the payload is validated against GA4's debug endpoint (returns per-field * validation messages), and on success a real `betterdocs_test_event` is sent * so it shows up in GA4 Realtime. The client-side methods can't be verified * from the server, so they get guidance instead. */ public function ga4_test() { $enabled = (bool) betterdocs()->settings->get( 'analytics_ga4', false ); $method = (string) betterdocs()->settings->get( 'ga4_method', 'datalayer' ); if ( ! $enabled ) { return new WP_REST_Response( [ 'status' => 'error', 'message' => __( 'GA4 forwarding is disabled — enable the toggle first.', 'betterdocs-pro' ) ], 200 ); } if ( 'gtag' === $method ) { return new WP_REST_Response( [ 'status' => 'info', 'message' => __( 'The Google Tag method runs in the reader\'s browser. Open a doc page, then check GA4 → Reports → Realtime for a betterdocs_doc_view event.', 'betterdocs-pro' ) ], 200 ); } if ( 'mp' !== $method ) { return new WP_REST_Response( [ 'status' => 'info', 'message' => __( 'The dataLayer method relies on your Google Tag Manager container. Verify your GTM tags fire on betterdocs_doc_view, then check GA4 Realtime.', 'betterdocs-pro' ) ], 200 ); } $measurement_id = sanitize_text_field( (string) betterdocs()->settings->get( 'ga4_measurement_id', '' ) ); $api_secret = trim( (string) betterdocs()->settings->get( 'ga4_api_secret', '' ) ); if ( '' === $measurement_id || '' === $api_secret ) { return new WP_REST_Response( [ 'status' => 'error', 'message' => __( 'Enter both a Measurement ID and an API secret, then save before testing.', 'betterdocs-pro' ) ], 200 ); } $body = wp_json_encode( [ 'client_id' => wp_rand( 1, 2147483647 ) . '.' . time(), 'events' => [ [ 'name' => 'betterdocs_test_event', 'params' => [ 'page_location' => home_url( '/' ), 'engagement_time_msec' => 1, ], ], ], ] ); $args = [ 'timeout' => 8, 'headers' => [ 'Content-Type' => 'application/json' ], 'body' => $body, ]; $query = [ 'measurement_id' => $measurement_id, 'api_secret' => $api_secret, ]; // 1) Validate against the debug endpoint — it echoes validation messages. $debug = wp_remote_post( add_query_arg( $query, 'https://www.google-analytics.com/debug/mp/collect' ), $args ); if ( is_wp_error( $debug ) ) { return new WP_REST_Response( [ 'status' => 'error', 'message' => $debug->get_error_message() ], 200 ); } $messages = json_decode( wp_remote_retrieve_body( $debug ), true ); $messages = isset( $messages['validationMessages'] ) ? (array) $messages['validationMessages'] : []; if ( ! empty( $messages ) ) { $first = $messages[0]; return new WP_REST_Response( [ 'status' => 'error', 'message' => sprintf( /* translators: %s: validation message from GA4 */ __( 'GA4 rejected the test event: %s', 'betterdocs-pro' ), isset( $first['description'] ) ? $first['description'] : wp_json_encode( $first ) ), ], 200 ); } // 2) Accepted — fire it for real so it appears in GA4 Realtime. wp_remote_post( add_query_arg( $query, 'https://www.google-analytics.com/mp/collect' ), $args ); return new WP_REST_Response( [ 'status' => 'success', 'message' => __( 'Test event accepted — look for betterdocs_test_event in GA4 → Reports → Realtime (may take a minute).', 'betterdocs-pro' ), ], 200 ); } /** * Stream a module's data as CSV (articles|search|feedback|links|authors). */ public function export( WP_REST_Request $request ) { $module = (string) $request->get_param( 'module' ); $kb_raw = (string) $request->get_param( 'kb' ); list( $start, $end ) = $this->resolve_window( $request ); list( $headers, $rows ) = $this->export_rows( $module, $start, $end, $kb_raw, $request ); nocache_headers(); header( 'Content-Type: text/csv; charset=utf-8' ); // User-facing filename maps the internal module slug to a friendly name // ('articles' → 'docs', 'knowledge_bases' → 'knowledge-bases'). $slug = [ 'overview' => 'overview', 'articles' => 'docs', 'categories' => 'categories', 'knowledge_bases' => 'knowledge-bases', 'reactions' => 'reactions', 'search' => 'search', 'feedback' => 'feedback', 'links' => 'links', 'authors' => 'authors', 'engagement' => 'engagement', ]; $name = isset( $slug[ $module ] ) ? $slug[ $module ] : $module; header( 'Content-Disposition: attachment; filename="betterdocs-' . $name . '-' . gmdate( 'Ymd' ) . '.csv"' ); $out = fopen( 'php://output', 'w' ); fputcsv( $out, array_map( [ $this, 'neutralize_csv_cell' ], (array) $headers ) ); foreach ( $rows as $row ) { fputcsv( $out, array_map( [ $this, 'neutralize_csv_cell' ], (array) $row ) ); } // export_rows() caps every module at 5,000 rows; when the cap is hit the // file is (likely) partial — say so instead of silently truncating (QA #16). if ( count( $rows ) >= 5000 ) { fputcsv( $out, [ '# Export truncated at 5,000 rows — narrow the date range or filters for complete data.' ] ); } fclose( $out ); exit; } /** * Neutralize CSV / spreadsheet formula injection. A cell whose first character * is one of = + - @ (or a leading tab / carriage return that Excel treats as a * formula lead-in) is prefixed with a single quote so the spreadsheet opens it * as literal text instead of executing it. Visitor-controlled columns (search * keywords, link URLs) are the attack vector; sanitize_text_field() does NOT * strip these leads, so neutralize every exported cell. * * @param mixed $value * @return string */ private function neutralize_csv_cell( $value ) { $value = (string) $value; if ( $value !== '' && in_array( $value[0], [ '=', '+', '-', '@', "\t", "\r" ], true ) ) { return "'" . $value; } return $value; } /** * Build [ headerRow, dataRows[] ] for a module's CSV export (unpaginated, capped). * * @return array{0:array,1:array} */ public function export_rows( $module, $start, $end, $kb_raw = '', $request = null ) { global $wpdb; $cutoff = $start; $end_dt = $end . ' 23:59:59'; $cap = 5000; $kb = $this->kb_filter( $kb_raw ); // Active section/filter forwarded by the header Export button. Sanitized to // fixed enums so they are safe to use in the queries below. $order = ( $request && $request->get_param( 'order' ) === 'least' ) ? 'least' : 'most'; $scope = ( $request && $request->get_param( 'scope' ) === 'all' ) ? 'all' : 'zero'; $status = $request ? (string) $request->get_param( 'status' ) : ''; $status = in_array( $status, [ 'new', 'reviewed', 'resolved' ], true ) ? $status : ''; switch ( $module ) { case 'articles': $rows = $wpdb->get_results( $wpdb->prepare( "SELECT post_id, SUM( views ) AS views, SUM( unique_views ) AS unique_views FROM {$wpdb->prefix}betterdocs_analytics_daily WHERE stat_date >= %s AND stat_date <= %s{$kb} GROUP BY post_id ORDER BY views DESC LIMIT %d", $cutoff, $end, $cap ) ); $out = []; foreach ( (array) $rows as $r ) { $out[] = [ get_the_title( (int) $r->post_id ), get_permalink( (int) $r->post_id ), (int) $r->views, (int) $r->unique_views ]; } return [ [ 'Doc', 'URL', 'Views', 'Unique Views' ], $out ]; case 'search': // scope=zero → only queries that returned no results (the Zero-Result sub-tab). $having = ( 'zero' === $scope ) ? ' HAVING not_found > 0' : ''; $rows = $wpdb->get_results( $wpdb->prepare( "SELECT k.keyword AS keyword, SUM( l.count ) AS searches, SUM( l.not_found_count ) AS not_found FROM {$wpdb->prefix}betterdocs_search_log l INNER JOIN {$wpdb->prefix}betterdocs_search_keyword k ON k.id = l.keyword_id WHERE l.created_at >= %s AND l.created_at <= %s GROUP BY l.keyword_id{$having} ORDER BY searches DESC LIMIT %d", $cutoff, $end, $cap ) ); $out = []; foreach ( (array) $rows as $r ) { $out[] = [ $r->keyword, (int) $r->searches, (int) $r->not_found ]; } return [ [ 'Query', 'Searches', 'No Results' ], $out ]; case 'feedback': // Honor the active Status filter (new|reviewed|resolved) when set. $status_sql = $status ? $wpdb->prepare( ' AND status = %s', $status ) : ''; $rows = $wpdb->get_results( $wpdb->prepare( "SELECT post_id, feeling, status, created_at FROM {$wpdb->prefix}betterdocs_analytics_feedback WHERE created_at >= %s AND created_at <= %s{$kb}{$status_sql} ORDER BY created_at DESC LIMIT %d", $cutoff, $end_dt, $cap ) ); $out = []; foreach ( (array) $rows as $r ) { $out[] = [ get_the_title( (int) $r->post_id ), $r->feeling, $r->status, $r->created_at ]; } return [ [ 'Doc', 'Reaction', 'Status', 'Date' ], $out ]; case 'links': $rows = $wpdb->get_results( $wpdb->prepare( "SELECT post_id, url, http_status, link_type FROM {$wpdb->prefix}betterdocs_analytics_links WHERE is_broken = 1{$kb} ORDER BY last_checked DESC LIMIT %d", $cap ) ); $out = []; foreach ( (array) $rows as $r ) { $out[] = [ get_the_title( (int) $r->post_id ), $r->url, (int) $r->http_status, $r->link_type ]; } return [ [ 'Doc', 'Link', 'HTTP Status', 'Type' ], $out ]; case 'overview': // A summary snapshot so the Overview export differs from Article Performance. $daily_t = $wpdb->prefix . 'betterdocs_analytics_daily'; $v = $wpdb->get_row( $wpdb->prepare( "SELECT COALESCE( SUM( views ), 0 ) AS views, COALESCE( SUM( unique_views ), 0 ) AS uniques FROM {$daily_t} WHERE stat_date >= %s AND stat_date <= %s{$kb}", $cutoff, $end ) ); $sr = $wpdb->get_row( $wpdb->prepare( "SELECT COALESCE( SUM( count ), 0 ) AS searches, COALESCE( SUM( not_found_count ), 0 ) AS not_found FROM {$wpdb->prefix}betterdocs_search_log WHERE created_at >= %s AND created_at <= %s", $cutoff, $end ) ); $rx = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE( SUM( happy + sad + normal ), 0 ) FROM {$wpdb->prefix}betterdocs_analytics WHERE created_at >= %s AND created_at <= %s{$kb}", $cutoff, $end ) ); return [ [ 'Metric', 'Value' ], [ [ 'Total Views', (int) ( $v->views ?? 0 ) ], [ 'Unique Readers', (int) ( $v->uniques ?? 0 ) ], [ 'Searches', (int) ( $sr->searches ?? 0 ) ], [ 'No-Result Searches', (int) ( $sr->not_found ?? 0 ) ], [ 'Reactions', $rx ], ], ]; case 'reactions': // Ranked helpfulness list from the legacy reaction store, honoring order=most|least. $legacy = $wpdb->prefix . 'betterdocs_analytics'; $rank_col = ( 'least' === $order ) ? 'sad' : 'happy'; $rows = $wpdb->get_results( $wpdb->prepare( "SELECT post_id, SUM( happy ) AS happy, SUM( normal ) AS normal, SUM( sad ) AS sad, SUM( happy + normal + sad ) AS total FROM {$legacy} WHERE created_at >= %s AND created_at <= %s{$kb} GROUP BY post_id HAVING SUM( {$rank_col} ) > 0 ORDER BY {$rank_col} DESC LIMIT %d", $cutoff, $end, $cap ) ); $out = []; foreach ( (array) $rows as $r ) { $out[] = [ get_the_title( (int) $r->post_id ), (int) $r->happy, (int) $r->normal, (int) $r->sad, (int) $r->total ]; } return [ [ 'Doc', 'Happy', 'Neutral', 'Unhappy', 'Total' ], $out ]; case 'categories': case 'knowledge_bases': $taxonomy = ( 'knowledge_bases' === $module ) ? 'knowledge_base' : 'doc_category'; $daily_t = $wpdb->prefix . 'betterdocs_analytics_daily'; // KB filter applies only to the category breakdown (post-scoped); // the knowledge-base breakdown is itself global. $kb_join = ( 'knowledge_bases' === $module ) ? '' : $this->kb_filter( $kb_raw, 'd.post_id' ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT tt.term_id AS term_id, SUM( d.views ) AS views, SUM( d.unique_views ) AS unique_views FROM {$daily_t} d JOIN {$wpdb->posts} p ON p.ID = d.post_id AND p.post_status = 'publish' JOIN {$wpdb->term_relationships} tr ON tr.object_id = d.post_id JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = %s WHERE d.stat_date >= %s AND d.stat_date <= %s{$kb_join} GROUP BY tt.term_id ORDER BY views DESC LIMIT %d", $taxonomy, $cutoff, $end, $cap ) ); // Reactions from the LIVE legacy aggregate — analytics_daily's // reaction columns are a frozen backfill snapshot (see leading_terms()). $kb_legacy_x = ( 'knowledge_bases' === $module ) ? '' : $this->kb_filter( $kb_raw, 'a.post_id' ); $reaction_rows = $wpdb->get_results( $wpdb->prepare( "SELECT tt.term_id AS term_id, SUM( a.happy + a.sad + a.normal ) AS reactions FROM {$wpdb->prefix}betterdocs_analytics a JOIN {$wpdb->posts} p ON p.ID = a.post_id AND p.post_status = 'publish' JOIN {$wpdb->term_relationships} tr ON tr.object_id = a.post_id JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = %s WHERE a.created_at >= %s AND a.created_at <= %s{$kb_legacy_x} GROUP BY tt.term_id", $taxonomy, $cutoff, $end ) ); $rx_map = []; foreach ( (array) $reaction_rows as $r ) { $rx_map[ (int) $r->term_id ] = (int) $r->reactions; } $out = []; foreach ( (array) $rows as $r ) { $term = get_term( (int) $r->term_id ); if ( ! $term || is_wp_error( $term ) ) { continue; } $out[] = [ $term->name, (int) $r->views, (int) $r->unique_views, isset( $rx_map[ (int) $r->term_id ] ) ? $rx_map[ (int) $r->term_id ] : 0 ]; } $term_label = ( 'knowledge_bases' === $module ) ? 'Knowledge Base' : 'Category'; return [ [ $term_label, 'Views', 'Unique Views', 'Reactions' ], $out ]; case 'engagement': // Combined device / country / referrer / language breakdown from the events stream. $events = $wpdb->prefix . 'betterdocs_analytics_events'; $cutoff_ev = $cutoff . ' 00:00:00'; $kb_ev = $this->kb_filter( $kb_raw, 'object_id' ); $out = []; $json_dims = [ 'Device' => '$.device', 'Country' => '$.country', 'Referrer' => '$.referrer' ]; foreach ( $json_dims as $type => $path ) { $rows = $wpdb->get_results( $wpdb->prepare( "SELECT JSON_UNQUOTE( JSON_EXTRACT( payload, %s ) ) AS k, COUNT(*) AS c FROM {$events} WHERE event_type = 'view' AND created_at >= %s AND created_at <= %s{$kb_ev} GROUP BY k ORDER BY c DESC LIMIT %d", $path, $cutoff_ev, $end_dt, $cap ) ); foreach ( (array) $rows as $r ) { $empty = ( null === $r->k || '' === $r->k ); $val = $empty ? ( 'Referrer' === $type ? __( 'Direct', 'betterdocs-pro' ) : 'Unknown' ) : $r->k; $out[] = [ $type, $val, (int) $r->c ]; } } $lang_rows = $wpdb->get_results( $wpdb->prepare( "SELECT lang AS k, COUNT(*) AS c FROM {$events} WHERE event_type = 'view' AND created_at >= %s AND created_at <= %s{$kb_ev} GROUP BY lang ORDER BY c DESC LIMIT %d", $cutoff_ev, $end_dt, $cap ) ); foreach ( (array) $lang_rows as $r ) { $val = ( null === $r->k || '' === $r->k ) ? 'unknown' : $r->k; $out[] = [ 'Language', $val, (int) $r->c ]; } return [ [ 'Type', 'Value', 'Sessions' ], $out ]; case 'authors': $kb_d = $this->kb_filter( $kb_raw, 'd.post_id' ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT p.post_author AS author_id, SUM( d.views ) AS views, SUM( d.unique_views ) AS unique_views, COUNT( DISTINCT d.post_id ) AS docs FROM {$wpdb->prefix}betterdocs_analytics_daily d INNER JOIN {$wpdb->posts} p ON p.ID = d.post_id WHERE d.stat_date >= %s AND d.stat_date <= %s AND p.post_type = 'docs'{$kb_d} GROUP BY p.post_author ORDER BY views DESC LIMIT %d", $cutoff, $end, $cap ) ); $out = []; foreach ( (array) $rows as $r ) { $out[] = [ get_the_author_meta( 'display_name', (int) $r->author_id ), (int) $r->docs, (int) $r->views, (int) $r->unique_views ]; } return [ [ 'Author', 'Docs', 'Views', 'Unique Views' ], $out ]; } return [ [], [] ]; } }