# Cohort queries and joins

## Pull a bounded event window

PostHog's HogQL endpoint is `POST https://us.posthog.com/api/projects/{projectId}/query/`. Supply the project ID at runtime; don't put it in a public skill. Query the signup cohort plus enough days after the last signup to observe the later retention window. Use paginated or partitioned queries if one response or execution window cannot hold the cohort. Confirm current event and property names in the team's definitions document.

Optional session telemetry can use distinct session IDs grouped by verified user identity. Check the row limit and cursor or partitioning support; a truncated result is not a complete population. Keep telemetry-only users separate from identified signup cohorts until identity is reconciled.

## Classify a user without hardcoded company thresholds

The following TypeScript operates on sanitized event objects. Real adapters must deduplicate repeated events and verify each event's source and timestamp.

```typescript
type UsageEvent = {
  kind: 'message' | 'connection' | 'integration-use';
  at: string;
  userInitiated?: boolean;
  success?: boolean;
};
type Policy = { windowDays: number; messageMin: number; chatOnlyMin: number };
const day = 24 * 60 * 60 * 1000;

function classifyUser(signupAt: string, observedAt: string, events: UsageEvent[], policy: Policy) {
  const signup = Date.parse(signupAt);
  const observed = Date.parse(observedAt);
  if (!Number.isFinite(signup) || !Number.isFinite(observed)) {
    throw new Error('Dates must be valid timestamps');
  }
  const firstEnd = signup + policy.windowDays * day;
  const secondEnd = firstEnd + policy.windowDays * day;
  const bounded = events
    .map(event => ({ ...event, millis: Date.parse(event.at) }))
    .filter(event => Number.isFinite(event.millis) && event.millis >= signup);

  const activated = (deadline: number) => {
    const seen = bounded.filter(event => event.millis < deadline);
    const messages = seen.filter(event => event.kind === 'message' && event.userInitiated).length;
    const connected = seen.some(event => event.kind === 'connection' && event.success);
    const used = seen.some(event => event.kind === 'integration-use');
    return (messages >= policy.messageMin && (connected || used)) ||
      messages >= policy.chatOnlyMin;
  };
  const w1Complete = observed >= firstEnd;
  const w2Complete = observed >= secondEnd;
  return {
    w1: w1Complete ? activated(firstEnd) : null,
    ever: activated(observed + 1),
    w2Active: w2Complete
      ? bounded.some(event => event.kind === 'message' && event.userInitiated &&
          event.millis >= firstEnd && event.millis < secondEnd)
      : null,
  };
}
```

`null` means not yet observable, not false. Aggregate only eligible cohorts. The code treats the initial window as elapsed clock time after signup; if your canonical definition uses local calendar days instead, implement that explicitly and version the definition.

## Compare paying coverage

Normalize verified customer identities consistently across analytics and billing, then compute:

```typescript
const observedIdentities = new Set(analyticsUsers.map(user => normalizeIdentity(user.identity)));
const payingIdentities = new Set(billingCustomers.map(user => normalizeIdentity(user.identity)));
const unmatchedPaying = [...payingIdentities].filter(id => !observedIdentities.has(id));
```

Here `normalizeIdentity`, `analyticsUsers`, and `billingCustomers` are inputs from authorized systems, not global variables to copy blindly. Keep any unmatched payers out of a rate whose denominator contains only identified analytics users. Report their count separately and investigate why the match failed. Don't log identity lists in dashboard output.