Skip to content

📈 Open source, privacy-first web analytics — a cookie-less Google Analytics alternative with built-in error tracking and performance monitoring. Self-host it or use our cloud. Get started for free!

31.6 KB
Raw Blame
import { randomBytes } from 'crypto'
import { parse as parseDomain } from 'tldts'
import countries from 'i18n-iso-countries'
import _isEmpty from 'lodash/isEmpty'
import dayjs from 'dayjs'
import utc from 'dayjs/plugin/utc'
import {
  Injectable,
  BadRequestException,
  InternalServerErrorException,
} from '@nestjs/common'
import { ConfigService } from '@nestjs/config'
import { OAuth2Client } from 'google-auth-library'
import { searchconsole } from '@googleapis/searchconsole'

import CryptoJS from 'crypto-js'

import { isDevelopment, PRODUCTION_ORIGIN, redis } from '../common/constants'
import { ProjectService } from './project.service'
import { deriveKey } from '../common/utils'

dayjs.extend(utc)

type StoredTokens = {
  access_token?: string
  refresh_token?: string
  scope?: string
  expiry_date?: number
  property_uri?: string | null
}

const REDIS_STATE_PREFIX = 'gsc:state:'

const GSC_REDIRECT_URL = isDevelopment
  ? 'http://localhost:3000/gsc-connected'
  : `${PRODUCTION_ORIGIN}/gsc-connected`

const ENCRYPTION_KEY = deriveKey('gsc-token')

const MAX_ROW_LIMIT = 25000
const MAX_BRANDED_PAGES = 10
export const MAX_FILTER_EXPRESSION_LENGTH = 500
export const MAX_FILTERS = 20
export const MAX_BRANDED_TRAFFIC_RANGE_DAYS = 31
export const MAX_POSITION_ANALYTICS_RANGE_DAYS = 31
export const OPTIONAL_ANALYTICS_TIMEOUT_MS = 12000

/**
 * Search Console keeps roughly 16 months of history, so an 'all' period is
 * resolved against a fixed window rather than the project's first event.
 */
export const GSC_ALL_TIME_DAYS = 480

const IMPRESSION_POSITION_BUCKETS = [
  { key: 'pos1To3', label: '1-3' },
  { key: 'pos4To10', label: '4-10' },
  { key: 'pos11To20', label: '11-20' },
  { key: 'pos21Plus', label: '21+' },
] as const

const ORGANIC_POSITION_BUCKET_KEYS = [
  'pos1To3',
  'pos4To10',
  'pos11To20',
  'pos21To50',
  'pos51Plus',
] as const

type ImpressionPositionBucketKey =
  (typeof IMPRESSION_POSITION_BUCKETS)[number]['key']
type OrganicPositionBucketKey = (typeof ORGANIC_POSITION_BUCKET_KEYS)[number]
type OrganicPositionSeriesEntry = { date: string } & Record<
  OrganicPositionBucketKey,
  number
>
export type BrandedTrafficAnalytics = { branded: number; nonBranded: number }
export type PositionAnalytics = {
  impressionsByPosition: {
    key: ImpressionPositionBucketKey
    label: string
    impressions: number
    percentage: number
  }[]
  organicPositions: OrganicPositionSeriesEntry[]
}

const initialImpressionPositionBuckets = (): Record<
  ImpressionPositionBucketKey,
  number
> => ({
  pos1To3: 0,
  pos4To10: 0,
  pos11To20: 0,
  pos21Plus: 0,
})

const initialOrganicPositionEntry = (
  date: string,
): OrganicPositionSeriesEntry => ({
  date,
  pos1To3: 0,
  pos4To10: 0,
  pos11To20: 0,
  pos21To50: 0,
  pos51Plus: 0,
})

const getImpressionPositionBucketKey = (
  position: number,
): ImpressionPositionBucketKey => {
  if (position <= 3) return 'pos1To3'
  if (position <= 10) return 'pos4To10'
  if (position <= 20) return 'pos11To20'
  return 'pos21Plus'
}

const getOrganicPositionBucketKey = (
  position: number,
): OrganicPositionBucketKey => {
  if (position <= 3) return 'pos1To3'
  if (position <= 10) return 'pos4To10'
  if (position <= 20) return 'pos11To20'
  if (position <= 50) return 'pos21To50'
  return 'pos51Plus'
}

type GSCRow = {
  keys: string[]
  clicks?: number
  impressions?: number
  ctr?: number
  position?: number
}

export type GSCContext = {
  sc: ReturnType<typeof searchconsole>
  siteUrl: string
}

interface QueryGSCOptions {
  dimensions?: string[]
  rowLimit?: number
  startRow?: number
  dataState?: string
  dimensionFilterGroups?: any[]
  signal?: AbortSignal
}

export function parseBrandKeywords(
  raw: string | null | undefined,
): string[] | null {
  if (!raw) return null
  try {
    const parsed = JSON.parse(raw)
    if (
      Array.isArray(parsed) &&
      parsed.every((item) => typeof item === 'string')
    ) {
      return parsed
    }
    return null
  } catch {
    return null
  }
}

@Injectable()
export class GSCService {
  constructor(
    private readonly configService: ConfigService,
    private readonly projectService: ProjectService,
  ) {}

  private getOAuthClient() {
    const clientId = this.configService.get<string>('GOOGLE_GSC_CLIENT_ID')
    const clientSecret = this.configService.get<string>(
      'GOOGLE_GSC_CLIENT_SECRET',
    )

    if (!clientId || !clientSecret) {
      throw new InternalServerErrorException(
        'Google GSC Client is not configured',
      )
    }

    return new OAuth2Client(clientId, clientSecret, GSC_REDIRECT_URL)
  }

  async generateConnectURL(uid: string, pid: string): Promise<{ url: string }> {
    const project = await this.projectService.getRedisProject(pid)
    this.projectService.allowedToManage(project, uid)

    const oauth2Client = this.getOAuthClient()

    const state = randomBytes(32).toString('hex')
    await redis.set(
      REDIS_STATE_PREFIX + state,
      JSON.stringify({ uid, pid }),
      'EX',
      600,
    )

    const url = oauth2Client.generateAuthUrl({
      access_type: 'offline',
      scope: [
        'https://www.googleapis.com/auth/webmasters.readonly',
        'https://www.googleapis.com/auth/userinfo.email',
      ],
      prompt: 'consent',
      state,
    })

    return { url }
  }

  async handleOAuthCallback(uid: string, code: string, state: string) {
    if (!state) {
      throw new BadRequestException('Invalid or expired OAuth state')
    }

    const cached = await redis.get(REDIS_STATE_PREFIX + state)
    if (!cached) {
      throw new BadRequestException('Invalid or expired OAuth state')
    }

    let payload: { uid?: string; pid?: string }
    try {
      payload = JSON.parse(cached)
    } catch {
      throw new BadRequestException('Invalid or expired OAuth state')
    }

    if (!payload?.pid || !payload?.uid || payload.uid !== uid) {
      throw new BadRequestException('Invalid or expired OAuth state')
    }

    const { pid } = payload

    const project = await this.projectService.getRedisProject(pid)

    this.projectService.allowedToManage(project, uid)

    const oauth2Client = this.getOAuthClient()
    const { tokens } = await oauth2Client.getToken(code)
    oauth2Client.setCredentials(tokens)

    let accountEmail: string | null = null
    try {
      const res = await fetch('https://www.googleapis.com/oauth2/v2/userinfo', {
        headers: {
          Authorization: `Bearer ${tokens.access_token}`,
        },
      })
      const data = await res.json()
      accountEmail = data?.email || null
    } catch {
      //
    }

    const previous = await this.getStoredTokens(pid)

    const toStore: StoredTokens = {
      access_token: tokens.access_token,
      refresh_token: tokens.refresh_token || previous.refresh_token,
      expiry_date: tokens.expiry_date,
      scope: tokens.scope,
      property_uri: previous.property_uri ?? null,
    }

    await this.setStoredTokens(pid, toStore)

    await this.projectService.update({ id: pid }, {
      gscAccountEmail: accountEmail,
    } as any)

    await redis.del(REDIS_STATE_PREFIX + state)

    return { pid }
  }

  private async getStoredTokens(pid: string): Promise<StoredTokens> {
    const project = await this.projectService.findOne({
      where: { id: pid },
      select: [
        'gscAccessTokenEnc',
        'gscRefreshTokenEnc',
        'gscTokenExpiry',
        'gscScope',
        'gscPropertyUri',
      ],
    })

    if (!project) return {}

    const decrypt = (val?: string | null) => {
      if (!val) return undefined
      try {
        const bytes = CryptoJS.Rabbit.decrypt(val, ENCRYPTION_KEY)
        return bytes.toString(CryptoJS.enc.Utf8) || undefined
      } catch {
        return undefined
      }
    }

    const expiry = project.gscTokenExpiry
      ? Number(project.gscTokenExpiry)
      : undefined

    return {
      access_token: decrypt(project.gscAccessTokenEnc),
      refresh_token: decrypt(project.gscRefreshTokenEnc),
      expiry_date: expiry,
      scope: project.gscScope || undefined,
      property_uri: project.gscPropertyUri || null,
    }
  }

  private async setStoredTokens(pid: string, tokens: StoredTokens) {
    const encrypt = (val?: string) =>
      val ? CryptoJS.Rabbit.encrypt(val, ENCRYPTION_KEY).toString() : null

    await this.projectService.update({ id: pid }, {
      gscAccessTokenEnc: encrypt(tokens.access_token),
      gscRefreshTokenEnc: encrypt(tokens.refresh_token),
      gscTokenExpiry: tokens.expiry_date as any,
      gscScope: tokens.scope || null,
      gscPropertyUri: tokens.property_uri ?? null,
    } as any)
  }

  async disconnect(pid: string) {
    await this.projectService.update({ id: pid }, {
      gscAccessTokenEnc: null,
      gscRefreshTokenEnc: null,
      gscTokenExpiry: null,
      gscScope: null,
      gscPropertyUri: null,
      gscAccountEmail: null,
    } as any)
  }

  async isConnected(pid: string) {
    const tokens = await this.getStoredTokens(pid)
    return !_isEmpty(tokens?.refresh_token || tokens?.access_token)
  }

  async getStatus(
    pid: string,
  ): Promise<{ connected: boolean; email: string | null }> {
    const connected = await this.isConnected(pid)
    if (!connected) return { connected, email: null }
    const project = await this.projectService.findOne({
      where: { id: pid },
      select: ['gscAccountEmail'],
    })
    return { connected, email: project?.gscAccountEmail || null }
  }

  /**
   * Whether the project can currently be queried: an account must be connected
   * and one of its properties linked. Unlike getStatus this needs no account
   * email, so it is safe to expose to anyone with project read access.
   */
  async getConnectionState(
    pid: string,
  ): Promise<{ connected: boolean; property: string | null }> {
    const tokens = await this.getStoredTokens(pid)
    const connected = !_isEmpty(tokens?.refresh_token || tokens?.access_token)

    return {
      connected,
      property: connected ? tokens.property_uri || null : null,
    }
  }

  private async getAuthedClientForPid(pid: string) {
    const tokens = await this.getStoredTokens(pid)
    if (_isEmpty(tokens)) {
      throw new BadRequestException(
        'Search Console is not connected for this project',
      )
    }
    const oauth2Client = this.getOAuthClient()
    oauth2Client.setCredentials(tokens)

    if (tokens.expiry_date && tokens.expiry_date <= Date.now()) {
      const refreshed = await oauth2Client.getAccessToken()
      const newAccess =
        typeof refreshed === 'string' ? refreshed : refreshed?.token
      if (newAccess) {
        const updated: StoredTokens = {
          ...tokens,
          access_token: newAccess,
          expiry_date: Date.now() + 55 * 60 * 1000,
        }
        await this.setStoredTokens(pid, updated)
        oauth2Client.setCredentials({ ...updated })
      }
    }

    return oauth2Client
  }

  async getGSCContext(pid: string): Promise<GSCContext> {
    const tokens = await this.getStoredTokens(pid)
    if (_isEmpty(tokens) || _isEmpty(tokens.property_uri)) {
      throw new BadRequestException(
        'Search Console property is not linked for this project',
      )
    }

    const oauth2Client = this.getOAuthClient()
    oauth2Client.setCredentials(tokens)

    if (tokens.expiry_date && tokens.expiry_date <= Date.now()) {
      const refreshed = await oauth2Client.getAccessToken()
      const newAccess =
        typeof refreshed === 'string' ? refreshed : refreshed?.token
      if (newAccess) {
        const updated: StoredTokens = {
          ...tokens,
          access_token: newAccess,
          expiry_date: Date.now() + 55 * 60 * 1000,
        }
        await this.setStoredTokens(pid, updated)
        oauth2Client.setCredentials({ ...updated })
      }
    }

    return {
      sc: searchconsole({ version: 'v1', auth: oauth2Client }),
      siteUrl: tokens.property_uri as string,
    }
  }

  async listSites(
    pid: string,
  ): Promise<{ siteUrl: string; permissionLevel?: string }[]> {
    const auth = await this.getAuthedClientForPid(pid)
    const sc = searchconsole({ version: 'v1', auth })
    try {
      const { data } = await sc.sites.list({})
      // @ts-ignore
      const sites = (data?.siteEntry || data?.siteEntries || []) as any[]
      return sites.map((s: any) => ({
        siteUrl: s.siteUrl,
        permissionLevel: s.permissionLevel,
      }))
    } catch {
      throw new InternalServerErrorException(
        'Failed to fetch Search Console sites',
      )
    }
  }

  async setProperty(pid: string, propertyUri: string) {
    if (typeof propertyUri !== 'string' || propertyUri.trim().length === 0) {
      throw new BadRequestException('propertyUri is required')
    }

    const tokens = await this.getStoredTokens(pid)
    if (_isEmpty(tokens)) {
      throw new BadRequestException(
        'Search Console is not connected for this project',
      )
    }

    const sites = await this.listSites(pid)
    const isOwned = sites.some((site) => site.siteUrl === propertyUri)

    if (!isOwned) {
      throw new BadRequestException(
        'The provided propertyUri is not available for the connected account',
      )
    }

    await this.projectService.update({ id: pid }, {
      gscPropertyUri: propertyUri,
    } as any)
  }

  private parseFilters(filtersStr?: string) {
    if (!filtersStr) return undefined

    let filters: unknown
    try {
      filters = JSON.parse(filtersStr)
    } catch {
      throw new BadRequestException('Invalid filters parameter')
    }

    if (!Array.isArray(filters) || filters.length > MAX_FILTERS) {
      throw new BadRequestException('Invalid filters parameter')
    }

    const mappedFilters: Array<{
      dimension: string
      expression: string
      operator?: string
    }> = []

    for (const filter of filters) {
      if (typeof filter !== 'object' || filter === null) {
        throw new BadRequestException('Invalid filters parameter')
      }

      const raw = filter as {
        dimension?: unknown
        operator?: unknown
        value?: unknown
        column?: unknown
        filter?: unknown
        isExclusive?: unknown
        isContains?: unknown
      }

      const isV2 = typeof raw.dimension === 'string'
      const filterDimension = isV2 ? raw.dimension : raw.column
      const value = isV2 ? raw.value : raw.filter

      if (typeof filterDimension !== 'string' || typeof value !== 'string') {
        continue
      }
      if (value.length > MAX_FILTER_EXPRESSION_LENGTH) continue

      let dimension: string | undefined
      let expression = value

      if (filterDimension === 'page' || filterDimension === 'pg') {
        dimension = 'page'
      } else if (filterDimension === 'keywords') {
        dimension = 'query'
      } else if (filterDimension === 'country' || filterDimension === 'cc') {
        dimension = 'country'
        if (expression.length === 2) {
          const alpha3 = countries.alpha2ToAlpha3(expression.toUpperCase())
          if (!alpha3) continue
          expression = alpha3.toLowerCase()
        } else {
          expression = expression.toLowerCase()
        }
      } else if (filterDimension === 'device' || filterDimension === 'dv') {
        dimension = 'device'
        expression = expression.toUpperCase()
      } else {
        continue
      }

      let operator = 'equals'
      if (isV2) {
        if (raw.operator === 'is_not') {
          operator = 'notEquals'
        } else if (raw.operator === 'contains') {
          operator = 'contains'
        } else if (raw.operator === 'contains_not') {
          operator = 'notContains'
        }
      } else if (raw.isExclusive) {
        operator = raw.isContains ? 'notContains' : 'notEquals'
      } else if (raw.isContains) {
        operator = 'contains'
      }

      const filterObj: {
        dimension: string
        expression: string
        operator?: string
      } = {
        dimension,
        expression,
      }
      if (operator !== 'equals') {
        filterObj.operator = operator
      }

      mappedFilters.push(filterObj)
    }

    if (mappedFilters.length > 0) {
      return [{ groupType: 'and', filters: mappedFilters }]
    }

    return undefined
  }

  private getDimensionFilterGroups(
    filtersStr?: string,
    extraFilters: Array<{ dimension: string; expression: string }> = [],
  ) {
    const baseGroups = this.parseFilters(filtersStr) || []

    if (_isEmpty(extraFilters)) {
      return baseGroups
    }

    if (_isEmpty(baseGroups)) {
      return [{ groupType: 'and', filters: extraFilters }]
    }

    return baseGroups.map((group, index) =>
      index === 0
        ? {
            ...group,
            filters: [...(group.filters || []), ...extraFilters],
          }
        : group,
    )
  }

  private async getProjectBrandKeywords(pid: string): Promise<string[]> {
    const project = await this.projectService.findOne({
      where: { id: pid },
      select: ['name', 'websiteUrl', 'brandKeywords'],
    })

    if (!project) return []

    const parsed = parseBrandKeywords(project.brandKeywords)
    if (parsed && parsed.length > 0) {
      return Array.from(
        new Set(parsed.map((k) => k.toLowerCase().trim()).filter(Boolean)),
      )
    }

    const keywords = new Set<string>()

    if (project.websiteUrl) {
      try {
        const { domainWithoutSuffix } = parseDomain(project.websiteUrl)
        if (domainWithoutSuffix) {
          keywords.add(domainWithoutSuffix.toLowerCase())
        }
      } catch {
        //
      }
    }

    if (project.name) {
      const name = project.name.toLowerCase().trim()
      if (name.length >= 3) {
        keywords.add(name)
      }
    }

    return Array.from(keywords)
  }

  private async queryGSC(
    ctx: GSCContext,
    from: string,
    to: string,
    options: QueryGSCOptions = {},
  ): Promise<GSCRow[]> {
    const startDate = dayjs(from).format('YYYY-MM-DD')
    const endDate = dayjs(to).format('YYYY-MM-DD')

    const { data } = await ctx.sc.searchanalytics.query(
      {
        siteUrl: ctx.siteUrl,
        requestBody: {
          startDate,
          endDate,
          ...(options.dimensions ? { dimensions: options.dimensions } : {}),
          ...(options.rowLimit != null ? { rowLimit: options.rowLimit } : {}),
          ...(options.startRow != null ? { startRow: options.startRow } : {}),
          dataState: options.dataState || 'all',
          ...(options.dimensionFilterGroups &&
          options.dimensionFilterGroups.length > 0
            ? { dimensionFilterGroups: options.dimensionFilterGroups }
            : {}),
        },
      },
      options.signal ? { signal: options.signal } : undefined,
    )

    return (data.rows || []) as GSCRow[]
  }

  private static clampLimit(limit: number, fallback = 50): number {
    return Math.max(1, Math.min(limit || fallback, MAX_ROW_LIMIT))
  }

  private static clampOffset(offset: number): number {
    return Math.max(0, offset || 0)
  }

  async getKeywords(
    pid: string,
    from: string,
    to: string,
    limit = 250,
    offset = 0,
    filtersStr?: string,
    page?: string,
    ctx?: GSCContext,
  ): Promise<
    {
      name: string
      count: number
      impressions: number
      position: number
      ctr: number
    }[]
  > {
    const gsc = ctx || (await this.getGSCContext(pid))
    const dimensionFilterGroups = this.getDimensionFilterGroups(
      filtersStr,
      page ? [{ dimension: 'page', expression: page }] : [],
    )

    try {
      const rows = await this.queryGSC(gsc, from, to, {
        dimensions: ['query'],
        rowLimit: GSCService.clampLimit(limit, 250),
        startRow: GSCService.clampOffset(offset),
        dimensionFilterGroups,
      })

      return rows
        .map((row) => ({
          name: row.keys?.[0] || '(not set)',
          count: Math.round(row.clicks || 0),
          impressions: Math.round(row.impressions || 0),
          position: Number(Number(row.position || 0).toFixed(2)),
          ctr: Number(Number((row.ctr || 0) * 100).toFixed(2)),
        }))
        .filter(Boolean)
    } catch {
      throw new InternalServerErrorException(
        'Failed to fetch keywords from Search Console',
      )
    }
  }

  async getSummary(
    pid: string,
    from: string,
    to: string,
    filtersStr?: string,
    ctx?: GSCContext,
  ): Promise<{
    clicks: number
    impressions: number
    ctr: number
    position: number
  }> {
    const gsc = ctx || (await this.getGSCContext(pid))
    const dimensionFilterGroups = this.getDimensionFilterGroups(filtersStr)

    try {
      const rows = await this.queryGSC(gsc, from, to, {
        dimensionFilterGroups,
      })

      const row = rows[0]
      return {
        clicks: Math.round(row?.clicks || 0),
        impressions: Math.round(row?.impressions || 0),
        ctr: Number(Number((row?.ctr || 0) * 100).toFixed(2)),
        position: Number(Number(row?.position || 0).toFixed(1)),
      }
    } catch {
      throw new InternalServerErrorException(
        'Failed to fetch summary from Search Console',
      )
    }
  }

  async getDateSeries(
    pid: string,
    from: string,
    to: string,
    timeBucket?: string,
    filtersStr?: string,
    ctx?: GSCContext,
  ): Promise<
    {
      date: string
      clicks: number
      impressions: number
      ctr: number
      position: number
    }[]
  > {
    const gsc = ctx || (await this.getGSCContext(pid))
    const dimensionFilterGroups = this.getDimensionFilterGroups(filtersStr)
    const isHourly = timeBucket === 'hour'
    const isMonthly = timeBucket === 'month'

    try {
      const rows = await this.queryGSC(gsc, from, to, {
        dimensions: isHourly ? ['hour'] : ['date'],
        rowLimit: 5000,
        dataState: isHourly ? 'hourly_all' : 'all',
        dimensionFilterGroups,
      })

      if (isMonthly) {
        const monthlySeries = new Map<
          string,
          { clicks: number; impressions: number; weightedPosition: number }
        >()

        for (const row of rows) {
          const rawDate = row.keys?.[0]
          if (!rawDate) continue

          const date = dayjs.utc(rawDate).format('YYYY-MM-01')
          const clicks = row.clicks || 0
          const impressions = row.impressions || 0
          const position = row.position || 0
          const existing = monthlySeries.get(date) || {
            clicks: 0,
            impressions: 0,
            weightedPosition: 0,
          }

          existing.clicks += clicks
          existing.impressions += impressions
          existing.weightedPosition += position * impressions
          monthlySeries.set(date, existing)
        }

        return Array.from(monthlySeries.entries())
          .sort(([dateA], [dateB]) => dateA.localeCompare(dateB))
          .map(([date, entry]) => ({
            date,
            clicks: Math.round(entry.clicks),
            impressions: Math.round(entry.impressions),
            ctr:
              entry.impressions > 0
                ? Number(((entry.clicks / entry.impressions) * 100).toFixed(2))
                : 0,
            position:
              entry.impressions > 0
                ? Number(
                    (entry.weightedPosition / entry.impressions).toFixed(1),
                  )
                : 0,
          }))
      }

      return rows.map((row) => ({
        date: row.keys?.[0]
          ? isHourly
            ? dayjs.utc(row.keys[0]).format('YYYY-MM-DD HH:mm:ss')
            : row.keys[0]
          : '',
        clicks: Math.round(row.clicks || 0),
        impressions: Math.round(row.impressions || 0),
        ctr: Number(Number((row.ctr || 0) * 100).toFixed(2)),
        position: Number(Number(row.position || 0).toFixed(1)),
      }))
    } catch {
      throw new InternalServerErrorException(
        'Failed to fetch date series from Search Console',
      )
    }
  }

  async getTopPages(
    pid: string,
    from: string,
    to: string,
    limit = 50,
    offset = 0,
    filtersStr?: string,
    query?: string,
    ctx?: GSCContext,
  ): Promise<
    {
      page: string
      clicks: number
      impressions: number
      ctr: number
      position: number
    }[]
  > {
    const gsc = ctx || (await this.getGSCContext(pid))
    const dimensionFilterGroups = this.getDimensionFilterGroups(
      filtersStr,
      query ? [{ dimension: 'query', expression: query }] : [],
    )

    try {
      const rows = await this.queryGSC(gsc, from, to, {
        dimensions: ['page'],
        rowLimit: GSCService.clampLimit(limit),
        startRow: GSCService.clampOffset(offset),
        dimensionFilterGroups,
      })

      return rows.map((row) => ({
        page: row.keys?.[0] || '(not set)',
        clicks: Math.round(row.clicks || 0),
        impressions: Math.round(row.impressions || 0),
        ctr: Number(Number((row.ctr || 0) * 100).toFixed(2)),
        position: Number(Number(row.position || 0).toFixed(1)),
      }))
    } catch {
      throw new InternalServerErrorException(
        'Failed to fetch top pages from Search Console',
      )
    }
  }

  async getTopCountries(
    pid: string,
    from: string,
    to: string,
    limit = 50,
    offset = 0,
    filtersStr?: string,
    ctx?: GSCContext,
  ): Promise<
    {
      country: string
      clicks: number
      impressions: number
      ctr: number
      position: number
    }[]
  > {
    const gsc = ctx || (await this.getGSCContext(pid))
    const dimensionFilterGroups = this.getDimensionFilterGroups(filtersStr)

    try {
      const rows = await this.queryGSC(gsc, from, to, {
        dimensions: ['country'],
        rowLimit: GSCService.clampLimit(limit),
        startRow: GSCService.clampOffset(offset),
        dimensionFilterGroups,
      })

      return rows.map((row) => ({
        country: (row.keys?.[0] || '').toLowerCase(),
        clicks: Math.round(row.clicks || 0),
        impressions: Math.round(row.impressions || 0),
        ctr: Number(Number((row.ctr || 0) * 100).toFixed(2)),
        position: Number(Number(row.position || 0).toFixed(1)),
      }))
    } catch {
      throw new InternalServerErrorException(
        'Failed to fetch top countries from Search Console',
      )
    }
  }

  async getTopDevices(
    pid: string,
    from: string,
    to: string,
    limit = 50,
    offset = 0,
    filtersStr?: string,
    ctx?: GSCContext,
  ): Promise<
    {
      device: string
      clicks: number
      impressions: number
      ctr: number
      position: number
    }[]
  > {
    const gsc = ctx || (await this.getGSCContext(pid))
    const dimensionFilterGroups = this.getDimensionFilterGroups(filtersStr)

    try {
      const rows = await this.queryGSC(gsc, from, to, {
        dimensions: ['device'],
        rowLimit: GSCService.clampLimit(limit),
        startRow: GSCService.clampOffset(offset),
        dimensionFilterGroups,
      })

      return rows.map((row) => ({
        device: (row.keys?.[0] || '').toLowerCase(),
        clicks: Math.round(row.clicks || 0),
        impressions: Math.round(row.impressions || 0),
        ctr: Number(Number((row.ctr || 0) * 100).toFixed(2)),
        position: Number(Number(row.position || 0).toFixed(1)),
      }))
    } catch {
      throw new InternalServerErrorException(
        'Failed to fetch top devices from Search Console',
      )
    }
  }

  async getBrandedTraffic(
    pid: string,
    from: string,
    to: string,
    filtersStr?: string,
    ctx?: GSCContext,
    signal?: AbortSignal,
  ): Promise<BrandedTrafficAnalytics> {
    const brandKeywords = await this.getProjectBrandKeywords(pid)
    if (_isEmpty(brandKeywords)) {
      return { branded: 0, nonBranded: 0 }
    }

    const gsc = ctx || (await this.getGSCContext(pid))
    const dimensionFilterGroups = this.getDimensionFilterGroups(filtersStr)
    const rowLimit = MAX_ROW_LIMIT
    let startRow = 0
    let branded = 0
    let nonBranded = 0
    let pages = 0

    try {
      while (pages < MAX_BRANDED_PAGES && !signal?.aborted) {
        const rows = await this.queryGSC(gsc, from, to, {
          dimensions: ['query'],
          rowLimit,
          startRow,
          dimensionFilterGroups,
          signal,
        })

        for (const row of rows) {
          if (signal?.aborted) break

          const query = (row.keys?.[0] || '').toLowerCase()
          const clicks = row.clicks || 0

          if (brandKeywords.some((keyword) => query.includes(keyword))) {
            branded += clicks
          } else {
            nonBranded += clicks
          }
        }

        if (signal?.aborted || rows.length < rowLimit) break

        startRow += rowLimit
        pages++
      }

      return {
        branded: Math.round(branded),
        nonBranded: Math.round(nonBranded),
      }
    } catch {
      throw new InternalServerErrorException(
        'Failed to fetch branded traffic from Search Console',
      )
    }
  }

  async getPositionAnalytics(
    pid: string,
    from: string,
    to: string,
    filtersStr?: string,
    ctx?: GSCContext,
    signal?: AbortSignal,
  ): Promise<PositionAnalytics> {
    const gsc = ctx || (await this.getGSCContext(pid))
    const dimensionFilterGroups = this.getDimensionFilterGroups(filtersStr)

    try {
      const impressionBuckets = initialImpressionPositionBuckets()
      const organicPositionsByDate = new Map<
        string,
        OrganicPositionSeriesEntry
      >()
      let totalImpressions = 0
      let startRow = 0

      while (!signal?.aborted) {
        const page = await this.queryGSC(gsc, from, to, {
          dimensions: ['date', 'query'],
          rowLimit: MAX_ROW_LIMIT,
          startRow,
          dimensionFilterGroups,
          signal,
        })

        for (const row of page) {
          if (signal?.aborted) break

          const position = Number(row.position || 0)
          const impressions = Math.round(row.impressions || 0)
          const date = row.keys?.[0]

          if (
            !date ||
            !Number.isFinite(position) ||
            position <= 0 ||
            impressions <= 0
          ) {
            continue
          }

          const impressionBucket = getImpressionPositionBucketKey(position)
          impressionBuckets[impressionBucket] += impressions
          totalImpressions += impressions

          const organicBucket = getOrganicPositionBucketKey(position)
          const organicEntry =
            organicPositionsByDate.get(date) ||
            initialOrganicPositionEntry(date)
          organicEntry[organicBucket] += 1
          organicPositionsByDate.set(date, organicEntry)
        }

        if (signal?.aborted || page.length < MAX_ROW_LIMIT) break

        startRow += MAX_ROW_LIMIT
      }

      const organicPositions: OrganicPositionSeriesEntry[] = []
      const datesWithPositionData = Array.from(
        organicPositionsByDate.keys(),
      ).sort()

      if (datesWithPositionData.length > 0) {
        let cursor = dayjs(datesWithPositionData[0]).startOf('day')
        const requestedEnd = dayjs(to).startOf('day')
        const lastDataDate = dayjs(
          datesWithPositionData[datesWithPositionData.length - 1],
        ).startOf('day')
        const end = lastDataDate.isBefore(requestedEnd)
          ? lastDataDate
          : requestedEnd

        while (cursor.isBefore(end) || cursor.isSame(end, 'day')) {
          const date = cursor.format('YYYY-MM-DD')
          organicPositions.push(
            organicPositionsByDate.get(date) ||
              initialOrganicPositionEntry(date),
          )
          cursor = cursor.add(1, 'day')
        }
      }

      return {
        impressionsByPosition: IMPRESSION_POSITION_BUCKETS.map(
          ({ key, label }) => ({
            key,
            label,
            impressions: impressionBuckets[key],
            percentage:
              totalImpressions > 0
                ? Number(
                    ((impressionBuckets[key] / totalImpressions) * 100).toFixed(
                      1,
                    ),
                  )
                : 0,
          }),
        ),
        organicPositions,
      }
    } catch {
      throw new InternalServerErrorException(
        'Failed to fetch position analytics from Search Console',
      )
    }
  }
}