Function getGscOverview

packages/db/src/gsc.ts:295–336

(
  projectId: string,
  startDate: string,
  endDate: string,
  interval: 'day' | 'week' | 'month' = 'day'
)

}

export async function getGscOverview(
  projectId: string,
  startDate: string,
  endDate: string,
  interval: 'day' | 'week' | 'month' = 'day'
): Promise<
  Array<{
    date: string;
    clicks: number;
    impressions: number;
    ctr: number;
    position: number;
  }>
> {
  const dateExpr =
    interval === 'month'
      ? 'toStartOfMonth(date)'
      : interval === 'week'
        ? 'toStartOfWeek(date)'
        : 'date';

  const result = await originalCh.query({
    query: `
      SELECT
        ${dateExpr} as date,
        sum(clicks) as clicks,
        sum(impressions) as impressions,
        avg(ctr) as ctr,
        avg(position) as position
      FROM gsc_daily
      FINAL
      WHERE project_id = {projectId: String}
        AND date >= {startDate: String}
        AND date <= {endDate: String}
      GROUP BY date
      ORDER BY date ASC
    `,
    query_params: { projectId, startDate, endDate },
    format: 'JSONEachRow',
  });
  return result.json();
}

export async function getGscPages(
  projectId: string,