Script to sync Dub clicks data to your own database

sync-dub-data.ts

import { dub } from "@/lib/dub";
import "dotenv-flow/config";

async function main() {
  let page = 1;
  const limit = 1000;
  while (true) {
    // list events 1000 rows at a time + paginate through all events within the last 24 hours
    const data = await dub.events.list({
      event: "clicks",
      interval: "24h",
      page,
      limit,
    });
    // aggregate by link_id and count the number of clicks
    // by country, city, region, continent, device, browser, os, referrer, and trigger
    const acc = data.reduce(
      (acc, curr) => {
        acc[curr.link.id] = acc[curr.link.id] || {
          countries: {},
          cities: {},
          regions: {},
          continents: {},
          devices: {},
          browsers: {},
          os: {},
          referrers: {},
          triggers: {},
        };
        acc[curr.link.id].countries[curr.click.country] =
          (acc[curr.link.id].countries[curr.click.country] || 0) + 1;
        acc[curr.link.id].cities[curr.click.city] =
          (acc[curr.link.id].cities[curr.click.city] || 0) + 1;
        acc[curr.link.id].regions[curr.click.region] =
          (acc[curr.link.id].regions[curr.click.region] || 0) + 1;
        acc[curr.link.id].continents[curr.click.continent] =
          (acc[curr.link.id].continents[curr.click.continent] || 0) + 1;
        acc[curr.link.id].devices[curr.click.device] =
          (acc[curr.link.id].devices[curr.click.device] || 0) + 1;
        acc[curr.link.id].browsers[curr.click.browser] =
          (acc[curr.link.id].browsers[curr.click.browser] || 0) + 1;
        acc[curr.link.id].os[curr.click.os] =
          (acc[curr.link.id].os[curr.click.os] || 0) + 1;
        acc[curr.link.id].referrers[curr.click.referer] =
          (acc[curr.link.id].referrers[curr.click.referer] || 0) + 1;
        acc[curr.link.id].triggers[curr.click.trigger || "link"] =
          (acc[curr.link.id].triggers[curr.click.trigger || "link"] || 0) + 1;
        return acc;
      },
      {} as Record<
        string,
        {
          countries: Record<string, number>;
          cities: Record<string, number>;
          regions: Record<string, number>;
          continents: Record<string, number>;
          devices: Record<string, number>;
          browsers: Record<string, number>;
          os: Record<string, number>;
          referrers: Record<string, number>;
          triggers: Record<string, number>;
        }
      >,
    );

    console.log(JSON.stringify(acc, null, 2));
    /*
     Example output:
     {
      "link_id": {
        "countries": {
          "US": 100,
          "UK": 50
        },
        "cities": {
          "London": 50,
          "Manchester": 30
        },
        ...
      }
    }
    */

    // TODO: WRITE/SYNC DATA TO DB

    // if the returned rows is less than 1000, it means this is the last batch, so we can stop the while loop
    if (data.length < limit) {
      break;
    }
    page++;
  }
}

main();