app/lib/google/search-console.server.ts

Functions

Imports

It imports app/db/schema.ts, app/lib/google/service-account.server.ts, app/lib/projects.server.ts, app/lib/sites.server.ts. Imported by app/lib/google/refresh.server.ts, app/routes/editor.tsx.

Source

1// Search Console per page (design section 5, "Insight"), read by the service account, which Dustin
2// adds as a user on each property. Fetched at most once a day, since Google's data moves daily and
3// lags two to three days; the editor shows the post's own row.
4
5import { and, eq } from "drizzle-orm";
6import { drizzle } from "drizzle-orm/d1";
7
8import { searchConsolePages } from "~/db/schema";
9import type { SiteProject } from "~/lib/projects.server";
10import { siteConnection, siteEntry, type SiteId } from "~/lib/sites.server";
11
12import { saClient } from "./service-account.server";
13
14type Fetch = typeof fetch;
15
16const DAY = 24 * 60 * 60 * 1000;
17/** Google's data settles a few days after the fact; the window ends before the unsettled days. */
18const LAG_DAYS = 3;
19const WINDOW_DAYS = 28;
20
21/** The Search Console property for a site: a domain property unless a var says otherwise. */
22function propertyFor(env: Env, site: SiteId): string {
23  return siteEntry(site).searchConsoleProperty(env);
24}
25
26function day(d: Date): string {
27  return d.toISOString().slice(0, 10);
28}
29
30export type ScRefresh = { pages: number; skipped: boolean; start: string; end: string };
31
32export async function refreshSearchConsole(env: Env, project: { id: number; site: SiteId }, fetcher?: Fetch, now = new Date()): Promise<ScRefresh> {
33  const d = drizzle(env.DB);
34  const last = await d.select({ fetchedAt: searchConsolePages.fetchedAt }).from(searchConsolePages).where(eq(searchConsolePages.projectId, project.id)).get();
35  const end = day(new Date(now.getTime() - LAG_DAYS * DAY));
36  const start = day(new Date(now.getTime() - (LAG_DAYS + WINDOW_DAYS - 1) * DAY));
37  if (last && now.getTime() - Date.parse(last.fetchedAt) < DAY) return { pages: 0, skipped: true, start, end };
38
39  const property = propertyFor(env, project.site);
40  const sa = saClient(env, fetcher);
41  const res = await sa.request("POST", `https://searchconsole.googleapis.com/webmasters/v3/sites/${encodeURIComponent(property)}/searchAnalytics/query`, {
42    startDate: start,
43    endDate: end,
44    dimensions: ["page"],
45    rowLimit: 25000,
46    dataState: "final",
47  });
48  if (!res.ok) {
49    const hint = res.status === 403 ? " Add the service account as a user on the property in Search Console." : "";
50    throw new Error(`Search Console refused the query for ${property} (${res.status}).${hint}`);
51  }
52  const body = (await res.json()) as { rows?: { keys: string[]; clicks: number; impressions: number; ctr: number; position: number }[] };
53  const at = now.toISOString();
54  await d.delete(searchConsolePages).where(eq(searchConsolePages.projectId, project.id));
55  for (const r of body.rows ?? []) {
56    await d.insert(searchConsolePages).values({
57      projectId: project.id,
58      page: r.keys[0] ?? "",
59      clicks: Math.round(r.clicks),
60      impressions: Math.round(r.impressions),
61      ctr: r.ctr,
62      position: r.position,
63      startDate: start,
64      endDate: end,
65      fetchedAt: at,
66    });
67  }
68  return { pages: body.rows?.length ?? 0, skipped: false, start, end };
69}
70
71/** The post's row, found by its full URL on the site. */
72export async function pageStats(env: Env, project: SiteProject, path: string | null) {
73  const connection = siteConnection(env, project.site);
74  if (!path || connection.state !== "connected") return null;
75  const url = new URL(path, connection.origin).href;
76  return (
77    (await drizzle(env.DB)
78      .select()
79      .from(searchConsolePages)
80      .where(and(eq(searchConsolePages.projectId, project.id), eq(searchConsolePages.page, url)))
81      .get()) ?? null
82  );
83}