{name} · Analytics

\n          \n             ═══ \n            {title}\n             {''.padEnd(w - title.length - 8, '═')}═ \n          
\n          {'  '.padEnd(w - 2, ' ') + '  '}\n\n          \n            \n            \n            \n          \n          \n            \n            \n          
\n          \n            \n            \n          
\n        \n      \n    \n  )\n}\n\nfunction Table({ data, title, props, w }) {\n  const renderProp = (item, prop) => {\n    const gap = '   '\n    let text = String(item[prop] || '')\n    if (prop == 'count') return gap + text.padStart(5)\n    if (text.length > w - 18) text = text.slice(0, w - 21) + '...'\n    if (item.link)\n      return (\n        <>\n          \n            {text}\n          \n          {''.padEnd(w - text.length - 18)}\n        \n      )\n    return text.padEnd(w - 18)\n  }\n  const renderRow = (item) => {\n    return <>{props.map((prop) => renderProp(item, prop))}\n  }\n\n  return (\n    \n      \n         ┌── \n        {title}\n         {''.padEnd(w - title.length - 10, '─')}──┐ \n      
\n      \n      {data.map((item, i) => (\n        \n          {' │   '}\n          {renderRow(item)}\n          {'   │ '}\n        
\n      ))}\n      \n      {Array.from({ length: 10 - data.length }, () => (\n        \n      ))}\n      \n         └──{''.padEnd(w - 6, '─')}┘ \n      
\n       
\n    
\n  )\n}\n\nfunction B({ children }) {\n  return {children}\n}\n\nfunction Spacer({ w }) {\n  return (\n    \n      {' │'.padEnd(w - 2, ' ') + '│ '}\n    
\n  )\n}\n\nfunction countUsersByInterval({ intervalFormat = 'YYYY-MM-DD' }) {\n  return analyticsQuery(`\n  select TO_CHAR(created_at, '${intervalFormat}') as interval, COUNT(distinct fid)\n  from analytics_events\n  where created_at >= NOW() - INTERVAL '1 year'\n  group by interval\n  order by interval desc\n  limit 10`)\n}\nfunction topOpenLocations({ period = 'week' }) {\n  return analyticsQuery(`\n  select param, COUNT(*)\n  from analytics_events\n  where created_at >= NOW() - INTERVAL '1 ${period}' and event = 'miniapp_open'\n  group by param\n  order by count desc\n  limit 10`).then(miniappLocationDataTransformer)\n}\nfunction topClients({ period = 'week' }) {\n  return analyticsQuery(`\n  select param, COUNT(*)\n  from analytics_events\n  where created_at >= NOW() - INTERVAL '1 ${period}' and event = 'miniapp_open'\n  group by param\n  order by count desc\n  limit 500`).then(miniappClientDataTransformer)\n}\nfunction topScreens({ period = 'week' }) {\n  return analyticsQuery(`\n  select param, COUNT(*)\n  from analytics_events\n  where created_at >= NOW() - INTERVAL '1 ${period}' and event = 'screen_view'\n  group by param\n  order by count desc\n  limit 10`).then(screenDataTransformer)\n}\nfunction topUsers({ period = 'week' }) {\n  return analyticsQuery(`\n  select fid, COUNT(*)\n  from analytics_events\n  where created_at >= NOW() - INTERVAL '1 ${period}'\n  group by fid\n  order by count desc\n  limit 10`).then(userDataTransformer)\n}\n\nfunction screenDataTransformer(data: any) {\n  return data.map((item: any) => {\n    return {\n      ...item,\n      link: item.param,\n    }\n  })\n}\nfunction userDataTransformer(data: any) {\n  return data.map((item: any) => {\n    return {\n      ...item,\n      link: `https://sonar.val.run/u/fid:${item.fid}`,\n    }\n  })\n}\nfunction miniappLocationDataTransformer(data: any) {\n  return data.map((item: any) => {\n    return {\n      ...item,\n      locationText: getLocationText(item.param),\n      link: getLocationLink(item.param),\n    }\n  })\n}\nfunction miniappClientDataTransformer(data: any) {\n  // map to client then group by client, then convert to array\n  const clients = data\n    .map((item: any) => {\n      return {\n        ...item,\n        // client: getClientFidText(JSON.parse(item.param).clientFid),\n        clientFid: JSON.parse(item.param).clientFid,\n      }\n    })\n    .reduce((acc: any, item: any) => {\n      const clientFid = item.clientFid\n      if (!acc[clientFid]) {\n        acc[clientFid] = 0\n      }\n      acc[clientFid] += Number(item.count)\n      return acc\n    }, {})\n  return Object.entries(clients)\n    .map(([clientFid, count]) => ({\n      clientFid,\n      client: getClientFidText(clientFid),\n      count: count as number,\n      link: `https://sonar.val.run/u/fid:${clientFid}`,\n    }))\n    .sort((a, b) => b.count - a.count)\n    .slice(0, 10)\n}\n\nfunction getLocationText(param: string) {\n  let locationText = ''\n  if (param) {\n    const location = JSON.parse(param)\n    locationText += '[' + getClientFidText(location.clientFid).slice(0, 4) + '] '\n\n    locationText +=\n      location.locationType\n        ?.replace('cast_embed', 'cast embed') // 📝\n        .replace('cast_share', 'cast share') // 📝\n        .replace('miniapp_open', 'miniapp open') // 🧩\n        .replace('notification', 'notification') // 🔔\n        .replace('channel', 'channel') // 📡\n        .replace('launcher', 'launcher') || '' // 📱\n\n    if (location.locationId) locationText += `: ${location.locationId}`\n  }\n  return locationText\n}\nfunction getClientFidText(clientFid: any) {\n  if (!clientFid) return `Farcaster`\n  if (clientFid == 9152) return `Farcaster`\n  if (clientFid == 399519) return `Base`\n  if (clientFid == 309857) return `Base`\n  if (clientFid == 827605) return `Zapper`\n  if (clientFid == 356900) return `Recaster`\n  return `${clientFid}`\n}\nfunction getLocationLink(param: string) {\n  if (param) {\n    const location = JSON.parse(param)\n    if (location.locationType == 'cast_embed') return `https://sonar.val.run/p/${location.locationId}`\n    if (location.locationType == 'cast_share') return `https://sonar.val.run/p/${location.locationId}`\n  }\n  return ''\n}\n\nasync function analyticsQuery(query: string, args: any[] = []) {\n  const result = await dbQuery(query, args)\n  return safeRows(result.rows)\n}\n\nfunction safeRows(rows: any[]) {\n  return rows.map((row) => {\n    const newRow: Record = {}\n    for (const key in row) {\n      if (typeof row[key] === 'bigint') {\n        newRow[key] = String(row[key])\n      } else {\n        newRow[key] = row[key]\n      }\n    }\n    return newRow\n  })\n}\n","deleted","label",[173,155],"backend","permalink","https://www.val.town/x/moe/FarcasterSpaces/code/backend/analytics.tsx","extension","actionData","errors"]