{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\nfunction Table({ data, title, props, w }) {\n  const renderProp = (item, prop) => {\n    const gap = '   '\n    const text = String(item[prop] || '')\n    if (prop == 'count') return gap + text.padStart(5)\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() {\n  return analyticsQuery(`\n  select param, COUNT(*)\n  from analytics_events\n  where created_at >= NOW() - INTERVAL '1 week' and event = 'miniapp_open'\n  group by param\n  order by count desc\n  limit 10`).then(miniappLocationDataTransformer)\n}\nfunction topScreens() {\n  return analyticsQuery(`\n  select param, COUNT(*)\n  from analytics_events\n  where created_at >= NOW() - INTERVAL '1 week' and event = 'screen_view'\n  group by param\n  order by count desc\n  limit 10`).then(screenDataTransformer)\n}\nfunction topUsers() {\n  return analyticsQuery(`\n  select fid, COUNT(*)\n  from analytics_events\n  where created_at >= NOW() - INTERVAL '1 week'\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}\n\nfunction getLocationText(param: string) {\n  let locationText = ''\n  if (param) {\n    const location = JSON.parse(param)\n    locationText += getClientFidText(location.clientFid) + ' '\n\n    locationText +=\n      location.locationType\n        ?.replace('cast_embed', 'cast embed') // 📝\n        .replace('cast_share', 'cast share') // 📝\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 `[FC]  `\n  if (clientFid == 9152) return `[FC]  `\n  if (clientFid == 399519) return `[CBW] `\n  if (clientFid == 309857) return `[CBW*]`\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",[170,153],"backend","permalink","https://www.val.town/x/arseniy/FarcasterSpaces/code/backend/analytics.tsx","extension","actionData","errors"]