[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_261":262,"_263":264},"routes/_app.x.$handle.$name",{"_11":12,"_13":14,"_15":-7,"_18":19,"_17":-7,"_198":-5,"_20":199,"_22":200,"_201":202,"_203":204,"_26":205,"_24":206,"_207":-5,"_208":-5,"_209":51,"_210":211,"_212":43,"_213":-5,"_214":-5,"_215":216},"routes/_app.x.$handle.$name.code.$",{"_11":12,"_13":14,"_15":16,"_17":-7,"_18":19,"_20":21,"_22":23,"_24":25,"_26":27,"_28":-5,"_29":-5,"_30":-5,"_31":-5,"_32":-5},"handle","sarahmamy","name","trustpilot-tracker","branchName","main","version","path","dashboard.html","project",{"_33":143,"_13":14,"_44":144,"_145":-5,"_146":51,"_147":148,"_149":148,"_150":151,"_152":-5,"_153":154,"_155":100,"_156":43,"_157":-5,"_158":-5,"_159":46,"_160":161,"_162":-5,"_163":-5,"_164":-5,"_165":-5,"_166":167,"_168":167,"_169":170,"_171":170,"_172":170,"_173":167,"_174":167,"_175":176,"_177":178,"_179":180,"_181":51},"tree",[54,55,56,57,58,59,60,61,62,63],"file",{"_33":34,"_13":19,"_35":36,"_37":-5,"_38":39,"_40":41,"_42":43,"_44":45,"_17":46,"_47":24,"_48":49,"_50":51,"_52":-5,"_18":53},"contents",[],"upstream","pull","ahead","behind","envVars","id","68c20e30-9ec7-41b8-9f54-28192cc33c89","fileId","55d21350-637d-11f1-ba76-1607ee4eb77e","parentId","branchId","76079492-fc89-40e1-83b5-8979f5d78c01","branchVersion",7,"latest",true,"createdAt",["D",1780952538693],0,"fileType","content","\n\n\n \n \n Trustpilot Tracker\n \n\n\n

⭐ Trustpilot Tracker

Daily trust scores & review counts scraped at 09:00 UTC from the Metabase URL list.

\n \n

\n\n \n \n\n\n","deleted",false,"label",[19],{"_33":135,"_13":136,"_35":137,"_37":-5,"_38":39,"_40":138,"_42":43,"_44":139,"_17":46,"_47":24,"_48":140,"_50":51,"_52":-5,"_71":141,"_18":142},{"_33":127,"_13":128,"_35":129,"_37":-5,"_38":39,"_40":130,"_42":43,"_44":131,"_17":46,"_47":103,"_48":132,"_50":51,"_52":-5,"_71":133,"_18":134},{"_33":118,"_13":119,"_35":120,"_37":-5,"_38":39,"_40":121,"_42":43,"_44":122,"_17":46,"_47":123,"_48":124,"_50":51,"_52":-5,"_71":125,"_18":126},{"_33":34,"_13":19,"_35":36,"_37":-5,"_38":39,"_40":41,"_42":43,"_44":115,"_17":46,"_47":24,"_48":49,"_50":51,"_52":-5,"_71":116,"_18":117},{"_33":107,"_13":108,"_35":109,"_37":-5,"_38":39,"_40":110,"_42":43,"_44":111,"_17":46,"_47":69,"_48":112,"_50":51,"_52":-5,"_71":113,"_18":114},{"_33":98,"_13":99,"_35":100,"_37":-5,"_38":39,"_40":101,"_42":43,"_44":102,"_17":46,"_47":103,"_48":104,"_50":51,"_52":-5,"_71":105,"_18":106},{"_33":90,"_13":91,"_35":92,"_37":-5,"_38":39,"_40":93,"_42":43,"_44":94,"_17":46,"_47":69,"_48":95,"_50":51,"_52":-5,"_71":96,"_18":97},{"_33":82,"_13":83,"_35":84,"_37":-5,"_38":39,"_40":85,"_42":43,"_44":86,"_17":46,"_47":69,"_48":87,"_50":51,"_52":-5,"_71":88,"_18":89},{"_33":74,"_13":75,"_35":76,"_37":-5,"_38":39,"_40":77,"_42":43,"_44":78,"_17":46,"_47":69,"_48":79,"_50":51,"_52":-5,"_71":80,"_18":81},{"_33":64,"_13":65,"_35":66,"_37":-5,"_38":39,"_40":67,"_42":43,"_44":68,"_17":46,"_47":69,"_48":70,"_50":51,"_52":-5,"_71":72,"_18":73},"ed663528-a6fc-477b-8206-ac73e29879ea","scrape.ts","2dfaf37e-637d-11f1-acf2-1607ee4eb77e",2,["D",1780952471850],"script","// Scraping helpers: gather URLs from Metabase, normalize trustpilot links,\n// and extract trust score + review count from a Trustpilot business page.\n\nconst METABASE_URL =\n \"https://metabase.internal.bigblue.co/public/question/27171a11-3254-4b23-82c0-fcbb8469a671\";\n\n// Metabase public questions expose a JSON results endpoint by appending `.json`\n// to the public question URL. We fall back to CSV if JSON isn't available.\nconst METABASE_JSON = METABASE_URL.replace(\"/question/\", \"/question/\") + \".json\";\n\nconst BROWSER_HEADERS = {\n \"User-Agent\":\n \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0 Safari/537.36\",\n \"Accept\": \"text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8\",\n \"Accept-Language\": \"en-US,en;q=0.9\",\n};\n\nconst URL_REGEX = /https?:\\/\\/[^\\s\"'<>)\\]]+/g;\n\n/** Pull every distinct URL out of the Metabase public question results. */\nexport async function gatherUrls(): Promise {\n let text = \"\";\n try {\n const res = await fetch(METABASE_JSON, { headers: BROWSER_HEADERS });\n if (res.ok) {\n text = await res.text();\n }\n } catch (_) { /* ignore, fall through */ }\n\n // Fallback: fetch the raw HTML page if JSON failed.\n if (!text) {\n const res = await fetch(METABASE_URL, { headers: BROWSER_HEADERS });\n text = await res.text();\n }\n\n const found = text.match(URL_REGEX) ?? [];\n const distinct = new Set();\n for (const raw of found) {\n const u = normalizeTrustpilotUrl(raw.replace(/\\\\\\//g, \"/\").trim());\n // Only keep links that look like external review sites (skip metabase/internal/static).\n if (/trustpilot\\.com|google\\.com\\/maps|g\\.page|trustedshops|avis-verifies|verified-reviews/i.test(u)) {\n distinct.add(u);\n }\n }\n return [...distinct];\n}\n\n/** Replace `/evaluate/` with `/review/` in trustpilot links, strip query/hash. */\nexport function normalizeTrustpilotUrl(url: string): string {\n let u = url;\n if (/trustpilot\\.com/i.test(u)) {\n u = u.replace(\"/evaluate/\", \"/review/\");\n // strip query + fragment so the same domain dedupes cleanly\n u = u.split(\"?\")[0].split(\"#\")[0];\n }\n return u;\n}\n\nexport function tenantFromUrl(url: string): string {\n const m = url.match(/\\/review\\/([^/?#]+)/i) || url.match(/\\/evaluate\\/([^/?#]+)/i);\n if (m) return m[1];\n try {\n return new URL(url).hostname;\n } catch {\n return url;\n }\n}\n\nexport function detectPlatform(url: string): string {\n if (/trustpilot\\.com/i.test(url)) return \"trustpilot\";\n if (/google\\.com\\/maps|g\\.page/i.test(url)) return \"google\";\n if (/trustedshops/i.test(url)) return \"trustedshops\";\n if (/avis-verifies|verified-reviews/i.test(url)) return \"verified-reviews\";\n return \"unknown\";\n}\n\nexport interface ScrapeResult {\n trustScore: number | null;\n reviews: number | null;\n lastReviewGiven: string | null;\n error: string | null;\n}\n\n/** Fetch a Trustpilot review page and extract the headline numbers. */\nexport async function scrapeTrustpilot(url: string): Promise {\n try {\n const res = await fetch(url, { headers: BROWSER_HEADERS });\n if (!res.ok) {\n return { trustScore: null, reviews: null, lastReviewGiven: null, error: `HTTP ${res.status}` };\n }\n const html = await res.text();\n\n // Trustpilot embeds structured data in a __NEXT_DATA__ / JSON-LD blob.\n let trustScore: number | null = null;\n let reviews: number | null = null;\n let lastReviewGiven: string | null = null;\n\n // 1) JSON-LD AggregateRating is the most reliable source.\n const ldMatches = [...html.matchAll(/]*type=\"application\\/ld\\+json\"[^>]*>([\\s\\S]*?)<\\/script>/g)];\n for (const m of ldMatches) {\n try {\n const data = JSON.parse(m[1]);\n const nodes = Array.isArray(data) ? data : (data[\"@graph\"] ?? [data]);\n for (const node of nodes) {\n const agg = node.aggregateRating;\n if (agg) {\n if (agg.ratingValue != null) trustScore = Number(agg.ratingValue);\n if (agg.reviewCount != null) reviews = Number(agg.reviewCount);\n else if (agg.ratingCount != null) reviews = Number(agg.ratingCount);\n }\n }\n } catch (_) { /* not valid JSON, skip */ }\n }\n\n // 2) Fallback: parse the meta description / visible text.\n if (trustScore == null) {\n const sm = html.match(/\"trustScore\":\\s*([\\d.]+)/) ||\n html.match(/TrustScore[^0-9]{0,20}([\\d.]+)\\s*out of 5/i);\n if (sm) trustScore = Number(sm[1]);\n }\n if (reviews == null) {\n const rm = html.match(/\"numberOfReviews\":\\s*\\{?\\s*\"total\":\\s*(\\d+)/) ||\n html.match(/([\\d,]+)\\s*reviews?/i);\n if (rm) reviews = Number(rm[1].replace(/,/g, \"\"));\n }\n\n // 3) Date of most recent review (best effort).\n const dm = html.match(/\"datePublished\":\"([^\"]+)\"/);\n if (dm) lastReviewGiven = dm[1];\n\n return { trustScore, reviews, lastReviewGiven, error: null };\n } catch (e) {\n return { trustScore: null, reviews: null, lastReviewGiven: null, error: String(e) };\n }\n}\n","children",[],[65],"d8cd96bc-eb5b-42af-ba26-d9e010a337e9","runScrape.ts","34b71bb6-637d-11f1-9747-1607ee4eb77e",3,["D",1780952483118],"import { initDb, upsertRow, type ScrapeRow } from \"./db.ts\";\nimport {\n detectPlatform,\n gatherUrls,\n scrapeTrustpilot,\n tenantFromUrl,\n} from \"./scrape.ts\";\n\nexport interface RunSummary {\n date: string;\n total: number;\n ok: number;\n failed: number;\n skippedNonTrustpilot: string[];\n rows: ScrapeRow[];\n}\n\n/** Core daily routine, reusable from the cron trigger and the manual button. */\nexport async function runScrape(): Promise {\n await initDb();\n const date = new Date().toISOString().slice(0, 10); // YYYY-MM-DD UTC\n\n const urls = await gatherUrls();\n const rows: ScrapeRow[] = [];\n const skippedNonTrustpilot: string[] = [];\n let ok = 0;\n let failed = 0;\n\n for (const link of urls) {\n const platform = detectPlatform(link);\n\n if (platform !== \"trustpilot\") {\n // The user asked to be guided for non-Trustpilot platforms.\n // We still record the link so they're visible in the dashboard.\n skippedNonTrustpilot.push(link);\n const row: ScrapeRow = {\n scrape_date: date,\n tenant: tenantFromUrl(link),\n link,\n platform,\n trust_score: null,\n reviews: null,\n last_review_given: null,\n raw_error: \"unsupported platform - needs guidance\",\n };\n await upsertRow(row);\n rows.push(row);\n continue;\n }\n\n const result = await scrapeTrustpilot(link);\n const row: ScrapeRow = {\n scrape_date: date,\n tenant: tenantFromUrl(link),\n link,\n platform,\n trust_score: result.trustScore,\n reviews: result.reviews,\n last_review_given: result.lastReviewGiven,\n raw_error: result.error,\n };\n await upsertRow(row);\n rows.push(row);\n if (result.error || result.trustScore == null) failed++;\n else ok++;\n\n // Be polite to Trustpilot — small delay between requests.\n await new Promise((r) => setTimeout(r, 400));\n }\n\n return {\n date,\n total: urls.length,\n ok,\n failed,\n skippedNonTrustpilot,\n rows,\n };\n}\n",[],[75],"b95b917a-94c0-42dc-8857-89f82fc524b0","render.ts","58b28960-637d-11f1-8c5b-1607ee4eb77e",8,["D",1780952543522],"import { readFile } from \"https://esm.town/v/std/utils/index.ts\";\nimport type { PivotResult } from \"./pivot.ts\";\n\n/** Inject the pivot JSON into the static HTML shell. */\nexport function renderDashboard(pivot: PivotResult): string {\n const html = readFile(\"/dashboard.html\", import.meta.url);\n // Escape \"\" so embedded JSON can't break out of the