Search Console Dashboard

Instructions

Implementation Reference

• src/tools/get-dashboard.ts:13-338 (handler)

  The main handler function 'getDashboardData' that executes the get_dashboard tool logic. It queries a SQLite database to retrieve comprehensive dashboard data including summary metrics (clicks, impressions, CTR, position), daily trends, top queries, top pages, country breakdown, ranking buckets, new/lost queries, and branded split. The function handles date range calculations, comparison modes, and returns structured data for the dashboard UI.

  export function getDashboardData(params: DashboardParams): any {
    const { siteUrl, dateRange = '3m', comparisonMode = 'previous_period', matchWeekdays = false, brandTerms } = params;
    const dbPath = getDbPath(siteUrl);
  
    if (!existsSync(dbPath)) {
      throw new Error(`No database found for "${siteUrl}". Run sync_gsc_data first.`);
    }
  
    const { current, prior } = getPeriodDates(dateRange, undefined, comparisonMode, matchWeekdays);
    const comparisonDisabled = comparisonMode === 'disabled';
    const db = new Database(dbPath);
  
    try {
      // 1. Summary metrics
      const currentSummary = db.queryOne(`
        SELECT
          COALESCE(SUM(clicks), 0) as clicks,
          COALESCE(SUM(impressions), 0) as impressions,
          ROUND(CAST(SUM(clicks) AS REAL) / NULLIF(SUM(impressions), 0), 4) as ctr,
          ROUND(AVG(position), 1) as avg_position
        FROM search_analytics
        WHERE date BETWEEN ? AND ?
      `, [current.startDate, current.endDate]);
  
      const priorSummary = db.queryOne(`
        SELECT
          COALESCE(SUM(clicks), 0) as clicks,
          COALESCE(SUM(impressions), 0) as impressions,
          ROUND(CAST(SUM(clicks) AS REAL) / NULLIF(SUM(impressions), 0), 4) as ctr,
          ROUND(AVG(position), 1) as avg_position
        FROM search_analytics
        WHERE date BETWEEN ? AND ?
      `, [prior.startDate, prior.endDate]);
  
      // 2. Daily trend (with CTR and position for metric toggles)
      const dailyTrend = db.query(`
        SELECT date,
          SUM(clicks) as clicks,
          SUM(impressions) as impressions,
          ROUND(CAST(SUM(clicks) AS REAL) / NULLIF(SUM(impressions), 0), 4) as ctr,
          ROUND(AVG(position), 1) as avg_position
        FROM search_analytics
        WHERE date BETWEEN ? AND ?
        GROUP BY date
        ORDER BY date ASC
      `, [current.startDate, current.endDate]);
  
      // 2b. Prior period daily trend (for dashed overlay)
      const priorDailyTrend = comparisonDisabled ? [] : db.query(`
        SELECT date,
          SUM(clicks) as clicks,
          SUM(impressions) as impressions,
          ROUND(CAST(SUM(clicks) AS REAL) / NULLIF(SUM(impressions), 0), 4) as ctr,
          ROUND(AVG(position), 1) as avg_position
        FROM search_analytics
        WHERE date BETWEEN ? AND ?
        GROUP BY date
        ORDER BY date ASC
      `, [prior.startDate, prior.endDate]);
  
      // 3. Top queries with change + CTR + position
      const topQueries = db.query(`
        SELECT query,
          SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END) as clicks,
          SUM(CASE WHEN date BETWEEN ? AND ? THEN impressions ELSE 0 END) as impressions,
          ROUND(CAST(SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END) AS REAL) /
            NULLIF(SUM(CASE WHEN date BETWEEN ? AND ? THEN impressions ELSE 0 END), 0), 4) as ctr,
          ROUND(AVG(CASE WHEN date BETWEEN ? AND ? THEN position ELSE NULL END), 1) as avg_position,
          SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END) as prior_clicks,
          SUM(CASE WHEN date BETWEEN ? AND ? THEN impressions ELSE 0 END) as prior_impressions,
          ROUND(CAST(SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END) AS REAL) /
            NULLIF(SUM(CASE WHEN date BETWEEN ? AND ? THEN impressions ELSE 0 END), 0), 4) as prior_ctr,
          ROUND(AVG(CASE WHEN date BETWEEN ? AND ? THEN position ELSE NULL END), 1) as prior_avg_position,
          ROUND(
            (SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END)
             - SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END))
            * 100.0
            / NULLIF(SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END), 0),
            1
          ) as clicks_change_pct
        FROM search_analytics
        WHERE query IS NOT NULL
          AND (date BETWEEN ? AND ? OR date BETWEEN ? AND ?)
        GROUP BY query
        HAVING clicks > 0
        ORDER BY clicks DESC
        LIMIT 100
      `, [
        current.startDate, current.endDate,
        current.startDate, current.endDate,
        current.startDate, current.endDate,
        current.startDate, current.endDate,
        current.startDate, current.endDate,
        prior.startDate, prior.endDate,
        prior.startDate, prior.endDate,
        prior.startDate, prior.endDate,
        prior.startDate, prior.endDate,
        prior.startDate, prior.endDate,
        current.startDate, current.endDate,
        prior.startDate, prior.endDate,
        prior.startDate, prior.endDate,
        current.startDate, current.endDate,
        prior.startDate, prior.endDate,
      ]);
  
      // 4. Top pages with change + CTR + position
      const topPages = db.query(`
        SELECT page,
          SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END) as clicks,
          SUM(CASE WHEN date BETWEEN ? AND ? THEN impressions ELSE 0 END) as impressions,
          ROUND(CAST(SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END) AS REAL) /
            NULLIF(SUM(CASE WHEN date BETWEEN ? AND ? THEN impressions ELSE 0 END), 0), 4) as ctr,
          ROUND(AVG(CASE WHEN date BETWEEN ? AND ? THEN position ELSE NULL END), 1) as avg_position,
          SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END) as prior_clicks,
          SUM(CASE WHEN date BETWEEN ? AND ? THEN impressions ELSE 0 END) as prior_impressions,
          ROUND(CAST(SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END) AS REAL) /
            NULLIF(SUM(CASE WHEN date BETWEEN ? AND ? THEN impressions ELSE 0 END), 0), 4) as prior_ctr,
          ROUND(AVG(CASE WHEN date BETWEEN ? AND ? THEN position ELSE NULL END), 1) as prior_avg_position,
          ROUND(
            (SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END)
             - SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END))
            * 100.0
            / NULLIF(SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END), 0),
            1
          ) as clicks_change_pct
        FROM search_analytics
        WHERE page IS NOT NULL
          AND (date BETWEEN ? AND ? OR date BETWEEN ? AND ?)
        GROUP BY page
        HAVING clicks > 0
        ORDER BY clicks DESC
        LIMIT 100
      `, [
        current.startDate, current.endDate,
        current.startDate, current.endDate,
        current.startDate, current.endDate,
        current.startDate, current.endDate,
        current.startDate, current.endDate,
        prior.startDate, prior.endDate,
        prior.startDate, prior.endDate,
        prior.startDate, prior.endDate,
        prior.startDate, prior.endDate,
        prior.startDate, prior.endDate,
        current.startDate, current.endDate,
        prior.startDate, prior.endDate,
        prior.startDate, prior.endDate,
        current.startDate, current.endDate,
        prior.startDate, prior.endDate,
      ]);
  
      // 5. Country breakdown
      const countries = db.query(`
        SELECT country,
          SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END) as clicks,
          SUM(CASE WHEN date BETWEEN ? AND ? THEN impressions ELSE 0 END) as impressions,
          SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END) as prior_clicks,
          SUM(CASE WHEN date BETWEEN ? AND ? THEN impressions ELSE 0 END) as prior_impressions,
          ROUND(
            (SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END)
             - SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END))
            * 100.0
            / NULLIF(SUM(CASE WHEN date BETWEEN ? AND ? THEN clicks ELSE 0 END), 0),
            1
          ) as clicks_change_pct
        FROM search_analytics
        WHERE country IS NOT NULL
          AND (date BETWEEN ? AND ? OR date BETWEEN ? AND ?)
        GROUP BY country
        HAVING clicks > 0
        ORDER BY clicks DESC
        LIMIT 50
      `, [
        current.startDate, current.endDate,
        current.startDate, current.endDate,
        prior.startDate, prior.endDate,
        prior.startDate, prior.endDate,
        current.startDate, current.endDate,
        prior.startDate, prior.endDate,
        prior.startDate, prior.endDate,
        current.startDate, current.endDate,
        prior.startDate, prior.endDate,
      ]);
  
      // 6. Ranking buckets — computed in SQL to avoid pulling millions of rows into JS
      const rankingBuckets = db.query(`
        SELECT
          CASE
            WHEN avg_pos <= 3 THEN '1-3'
            WHEN avg_pos <= 10 THEN '4-10'
            WHEN avg_pos <= 20 THEN '11-20'
            WHEN avg_pos <= 50 THEN '21-50'
            WHEN avg_pos <= 100 THEN '51-100'
            ELSE '100+'
          END as bucket,
          COUNT(*) as count
        FROM (
          SELECT AVG(position) as avg_pos
          FROM search_analytics
          WHERE date BETWEEN ? AND ? AND query IS NOT NULL
          GROUP BY query
        )
        GROUP BY bucket
        ORDER BY MIN(avg_pos)
      `, [current.startDate, current.endDate]);
  
      // 7. New queries (in current but not in prior) — using EXCEPT for efficiency
      const newQueries = comparisonDisabled ? [] : db.query(`
        SELECT query, SUM(clicks) as clicks, SUM(impressions) as impressions, ROUND(AVG(position), 1) as avg_position
        FROM search_analytics
        WHERE date BETWEEN ? AND ?
          AND query IS NOT NULL
          AND query IN (
            SELECT query FROM search_analytics WHERE date BETWEEN ? AND ? AND query IS NOT NULL
            EXCEPT
            SELECT query FROM search_analytics WHERE date BETWEEN ? AND ? AND query IS NOT NULL
          )
        GROUP BY query
        HAVING clicks > 0
        ORDER BY clicks DESC
        LIMIT 50
      `, [current.startDate, current.endDate, current.startDate, current.endDate, prior.startDate, prior.endDate]);
  
      // 8. Lost queries (in prior but not in current) — using EXCEPT for efficiency
      const lostQueries = comparisonDisabled ? [] : db.query(`
        SELECT query, SUM(clicks) as clicks, SUM(impressions) as impressions, ROUND(AVG(position), 1) as avg_position
        FROM search_analytics
        WHERE date BETWEEN ? AND ?
          AND query IS NOT NULL
          AND query IN (
            SELECT query FROM search_analytics WHERE date BETWEEN ? AND ? AND query IS NOT NULL
            EXCEPT
            SELECT query FROM search_analytics WHERE date BETWEEN ? AND ? AND query IS NOT NULL
          )
        GROUP BY query
        HAVING clicks > 0
        ORDER BY clicks DESC
        LIMIT 50
      `, [prior.startDate, prior.endDate, prior.startDate, prior.endDate, current.startDate, current.endDate]);
  
      // 9. Branded split (if brandTerms provided)
      let brandedSplit = null;
      if (brandTerms && brandTerms.length > 0) {
        const brandConditions = brandTerms.map(() => 'LOWER(query) LIKE ?').join(' OR ');
        const brandValues = brandTerms.map(t => `%${t.toLowerCase()}%`);
  
        const splitSummary = db.query(`
          SELECT
            CASE WHEN ${brandConditions} THEN 'branded' ELSE 'non-branded' END as segment,
            SUM(clicks) as clicks,
            SUM(impressions) as impressions
          FROM search_analytics
          WHERE date BETWEEN ? AND ? AND query IS NOT NULL
          GROUP BY segment
        `, [...brandValues, current.startDate, current.endDate]);
  
        const priorSplitSummary = comparisonDisabled ? [] : db.query(`
          SELECT
            CASE WHEN ${brandConditions} THEN 'branded' ELSE 'non-branded' END as segment,
            SUM(clicks) as clicks,
            SUM(impressions) as impressions
          FROM search_analytics
          WHERE date BETWEEN ? AND ? AND query IS NOT NULL
          GROUP BY segment
        `, [...brandValues, prior.startDate, prior.endDate]);
  
        const splitTrend = db.query(`
          SELECT date,
            CASE WHEN ${brandConditions} THEN 'branded' ELSE 'non-branded' END as segment,
            SUM(clicks) as clicks
          FROM search_analytics
          WHERE date BETWEEN ? AND ? AND query IS NOT NULL
          GROUP BY date, segment
          ORDER BY date ASC
        `, [...brandValues, current.startDate, current.endDate]);
  
        brandedSplit = { summary: splitSummary, priorSummary: priorSplitSummary, trend: splitTrend };
      }
  
      // 10. Last synced timestamp
      const meta = db.getPropertyMeta(siteUrl);
      const lastSyncedAt = meta?.lastSyncedAt ?? null;
  
      const pctChange = (curr: number, prev: number): number | null =>
        prev === 0 ? null : Math.round(((curr - prev) / prev) * 1000) / 10;
  
      return {
        siteUrl,
        dateRange,
        comparisonMode,
        matchWeekdays,
        lastSyncedAt,
        period: { current, prior },
        summary: {
          current: {
            clicks: currentSummary.clicks,
            impressions: currentSummary.impressions,
            ctr: currentSummary.ctr,
            avgPosition: currentSummary.avg_position,
          },
          prior: {
            clicks: priorSummary.clicks,
            impressions: priorSummary.impressions,
            ctr: priorSummary.ctr,
            avgPosition: priorSummary.avg_position,
          },
          changes: {
            clicksPct: pctChange(currentSummary.clicks, priorSummary.clicks),
            impressionsPct: pctChange(currentSummary.impressions, priorSummary.impressions),
            ctrPct: pctChange(currentSummary.ctr ?? 0, priorSummary.ctr ?? 0),
            avgPositionPct: pctChange(currentSummary.avg_position ?? 0, priorSummary.avg_position ?? 0),
          },
        },
        dailyTrend,
        priorDailyTrend,
        topQueries,
        topPages,
        countries,
        rankingBuckets,
        newQueries,
        lostQueries,
        brandedSplit,
      };
    } finally {
      db.close();
    }
  }

• src/tools/get-dashboard.ts:5-11 (schema)

  The DashboardParams interface defines the input schema for the get_dashboard tool, including siteUrl (required), dateRange (optional, default '3m'), comparisonMode (optional, default 'previous_period'), matchWeekdays (optional, default false), and brandTerms (optional array of strings).

  export interface DashboardParams {
    siteUrl: string;
    dateRange?: string;
    comparisonMode?: ComparisonMode;
    matchWeekdays?: boolean;
    brandTerms?: string[];
  }

• src/server.ts:213-242 (registration)

  Registration of the 'get_dashboard' tool using registerAppTool. Defines the tool name, title, description, and inputSchema using Zod validation for siteUrl (required), dateRange, comparisonMode, matchWeekdays, and brandTerms. The handler calls getDashboardData and returns the structured content.

  registerAppTool(
    server,
    'get_dashboard',
    {
      title: 'Search Console Dashboard',
      description: 'Drill into a single property. Shows an interactive dashboard with hero metrics, trend chart, top queries, top pages, country breakdown, ranking distribution, new/lost queries, and branded split. Use the siteUrl from get_overview results. Requires synced data — run setup first if needed.',
      inputSchema: {
        siteUrl: z.string().describe('GSC property URL, e.g. "sc-domain:example.com". Get this from the overview or list_properties.'),
        dateRange: z.string().optional().describe('Date range: "7d", "28d", "3m", "6m", "12m", "16m", or named presets: "lw", "tm", "lm", "tq", "lq", "ytd". Default: "3m".'),
        comparisonMode: z.enum(['previous_period', 'year_over_year', 'previous_month', 'disabled']).optional().describe('Comparison mode. Default: "previous_period".'),
        matchWeekdays: z.boolean().optional().describe('Align comparison period to match weekday patterns. Default: false.'),
        brandTerms: z.array(z.string()).optional().describe('Brand terms for branded/non-branded split (e.g. ["mysite", "my site"]).'),
      },
      _meta: { ui: { resourceUri: dashboardResourceUri } },
    },
    async (args) => {
      try {
        const data = getDashboardData(args);
        return {
          content: [{ type: 'text', text: JSON.stringify(data, null, 2) }],
          structuredContent: data,
        };
      } catch (error) {
        return {
          content: [{ type: 'text', text: JSON.stringify({ error: (error as Error).message }) }],
          isError: true,
        };
      }
    }
  );

• src/tools/helpers.ts:120-211 (helper)

  Helper function 'getPeriodDates' that calculates current and prior period date ranges based on the comparison mode (previous_period, year_over_year, previous_month, disabled). Handles weekday alignment for accurate comparisons and edge cases like leap years.

  export function getPeriodDates(
    range: string,
    referenceDate?: Date,
    comparisonMode: ComparisonMode = 'previous_period',
    matchWeekdays: boolean = false
  ): {
    current: { startDate: string; endDate: string };
    prior: { startDate: string; endDate: string };
  } {
    const current = parseDateRange(range, referenceDate);
  
    if (comparisonMode === 'disabled') {
      // Return empty prior period — callers should check for this
      return {
        current,
        prior: { startDate: current.startDate, endDate: current.startDate },
      };
    }
  
    const startMs = new Date(current.startDate).getTime();
    const endMs = new Date(current.endDate).getTime();
    const DAY = 24 * 60 * 60 * 1000;
    const durationMs = endMs - startMs;
  
    let priorStart: Date;
    let priorEnd: Date;
  
    switch (comparisonMode) {
      case 'year_over_year': {
        const s = new Date(current.startDate);
        const e = new Date(current.endDate);
        const sDay = s.getDate();
        const eDay = e.getDate();
        s.setFullYear(s.getFullYear() - 1);
        // Handle leap year: Feb 29 → Feb 28 (setFullYear may roll to Mar 1)
        if (s.getDate() !== sDay) s.setDate(0); // last day of previous month
        e.setFullYear(e.getFullYear() - 1);
        if (e.getDate() !== eDay) e.setDate(0);
        priorStart = s;
        priorEnd = e;
        break;
      }
      case 'previous_month': {
        const s = new Date(current.startDate);
        const e = new Date(current.endDate);
        const sMonth = (s.getMonth() - 1 + 12) % 12;
        const eMonth = (e.getMonth() - 1 + 12) % 12;
        s.setMonth(s.getMonth() - 1);
        // Handle day overflow: e.g. Mar 31 → setMonth(-1) → "Feb 31" → Mar 3
        if (s.getMonth() !== sMonth) s.setDate(0); // last day of intended month
        e.setMonth(e.getMonth() - 1);
        if (e.getMonth() !== eMonth) e.setDate(0);
        priorStart = s;
        priorEnd = e;
        break;
      }
      case 'previous_period':
      default: {
        priorEnd = new Date(startMs - DAY);
        priorStart = new Date(priorEnd.getTime() - durationMs);
        break;
      }
    }
  
    // Match weekdays: shift prior period so that the start day-of-week matches
    if (matchWeekdays) {
      const currentStartDay = new Date(current.startDate).getDay();
      const priorStartDay = priorStart.getDay();
      let diff = currentStartDay - priorStartDay;
      // Find the closest shift that aligns weekdays (within ±3 days)
      if (diff > 3) diff -= 7;
      if (diff < -3) diff += 7;
      priorStart = new Date(priorStart.getTime() + diff * DAY);
      priorEnd = new Date(priorEnd.getTime() + diff * DAY);
  
      // Safety: ensure prior period never overlaps with current period
      const currentStartMs = new Date(current.startDate).getTime();
      if (priorEnd.getTime() >= currentStartMs) {
        const overshoot = priorEnd.getTime() - currentStartMs + DAY;
        priorStart = new Date(priorStart.getTime() - overshoot);
        priorEnd = new Date(priorEnd.getTime() - overshoot);
      }
    }
  
    return {
      current,
      prior: {
        startDate: formatDate(priorStart),
        endDate: formatDate(priorEnd),
      },
    };
  }

• src/tools/helpers.ts:44-46 (helper)

  Helper function 'getDbPath' that constructs the SQLite database file path for a given GSC site URL by sanitizing the URL and appending .db extension in the data directory.

  export function getDbPath(siteUrl: string): string {
    return join(getDataDir(), sanitizeSiteUrl(siteUrl) + '.db');
  }