Skip to content

📈 Open source, privacy-first web analytics — a cookie-less Google Analytics alternative with built-in error tracking and performance monitoring. Self-host it or use our cloud. Get started for free!

35.5 KB
Raw Blame
import { ApiKeyAccess } from '../api-key/api-key-access.decorator'
import {
  Controller,
  Get,
  Put,
  Delete,
  Query,
  Param,
  Body,
  NotFoundException,
  Post,
  ForbiddenException,
  BadRequestException,
  HttpException,
  HttpStatus,
  ParseIntPipe,
  Ip,
  Headers,
} from '@nestjs/common'
import { ApiTags, ApiResponse, ApiBearerAuth } from '@nestjs/swagger'
import _isEmpty from 'lodash/isEmpty'
import _map from 'lodash/map'
import _omit from 'lodash/omit'
import _pick from 'lodash/pick'
import _round from 'lodash/round'
import dayjs from 'dayjs'

import { UserService } from '../user/user.service'
import { ProjectService } from '../project/project.service'
import { AppLoggerService } from '../logger/logger.service'
import { PlanCode } from '../user/entities/user.entity'
import {
  AnalyticsService,
  DataType,
  getLowestPossibleTimeBucket,
} from '../analytics/analytics.service'
import { normalizeFiltersToV1Json } from '../analytics/v2/query/filters.translator'
import { Auth } from '../auth/decorators'
import { CurrentUserId } from '../auth/decorators/current-user-id.decorator'
import {
  Goal,
  GoalType,
  GoalMatchType,
  GoalCondition,
} from './entity/goal.entity'
import {
  CreateGoalDto,
  UpdateGoalDto,
  GoalDto,
  GoalStatsDto,
} from './dto/goal.dto'
import { GoalService } from './goal.service'
import { clickhouse } from '../common/integrations/clickhouse'
import { getIPFromHeaders } from '../common/utils'
import { trackCustom } from '../common/analytics'

const GOALS_MAXIMUM = 50 // Maximum goals per project

const timeBucketConversion: Record<string, string> = Object.assign(
  Object.create(null),
  {
    minute: 'toStartOfMinute',
    hour: 'toStartOfHour',
    day: 'toStartOfDay',
    month: 'toStartOfMonth',
    year: 'toStartOfYear',
  },
)

const DEFAULT_TAKE = 100
const MAX_TAKE = 250
const MAX_SKIP = 50_000

const clampPagination = (take?: number, skip?: number) => {
  const safeTake =
    typeof take === 'number' && Number.isFinite(take)
      ? Math.min(Math.max(take, 1), MAX_TAKE)
      : DEFAULT_TAKE
  const safeSkip =
    typeof skip === 'number' && Number.isFinite(skip)
      ? Math.min(Math.max(skip, 0), MAX_SKIP)
      : 0

  return { take: safeTake, skip: safeSkip }
}

const GOAL_CONDITION_COLUMNS: Record<string, string> = {
  page: 'pg',
  event: 'event_name',
  host: 'host',
  country: 'cc',
  device: 'dv',
  browser: 'br',
  os: 'os',
  referrer: 'ref',
  utm_source: 'so',
  utm_medium: 'me',
  utm_campaign: 'ca',
  utm_term: 'te',
  utm_content: 'co',
  pg: 'pg',
  event_name: 'event_name',
  ref: 'ref',
  so: 'so',
  me: 'me',
  ca: 'ca',
  te: 'te',
  co: 'co',
  cc: 'cc',
  dv: 'dv',
  br: 'br',
}

type GoalBreakdownKey =
  | 'countries'
  | 'devices'
  | 'browsers'
  | 'sources'
  | 'campaigns'
  | 'pages'
  | 'profileTypes'

type GoalBreakdowns = Record<GoalBreakdownKey, Record<string, number>>

type TimeToConvertMetric = {
  average: number | null
  median: number | null
  p75: number | null
}

@ApiTags('Goal')
@Controller('goal')
export class GoalController {
  constructor(
    private readonly goalService: GoalService,
    private readonly projectService: ProjectService,
    private readonly logger: AppLoggerService,
    private readonly userService: UserService,
    private readonly analyticsService: AnalyticsService,
  ) {}

  @ApiBearerAuth()
  @Get('/:goalId')
  @Auth(true, true)
  @ApiKeyAccess('goals:read', 'goal', 'goalId')
  @ApiResponse({ status: 200, type: Goal })
  async getGoal(
    @CurrentUserId() userId: string,
    @Param('goalId') goalId: string,
    @Headers() headers: { 'x-password'?: string },
  ) {
    this.logger.log({ userId, goalId }, 'GET /goal/:goalId')

    const goal = await this.goalService.findOne({
      where: { id: goalId },
      relations: ['project'],
    })

    if (_isEmpty(goal)) {
      throw new NotFoundException('Goal not found')
    }

    const project = await this.projectService.getFullProject(goal.project.id)

    this.projectService.allowedToView(project, userId, headers['x-password'])

    return {
      ..._omit(goal, ['project']),
      pid: goal.project.id,
    }
  }

  @ApiBearerAuth()
  @Get('/project/:projectId')
  @Auth(true, true)
  @ApiKeyAccess('goals:read', 'params', 'projectId')
  @ApiResponse({ status: 200, type: [GoalDto] })
  async getProjectGoals(
    @CurrentUserId() userId: string,
    @Param('projectId') projectId: string,
    @Headers() headers: { 'x-password'?: string },
    @Query('take', new ParseIntPipe({ optional: true })) take?: number,
    @Query('skip', new ParseIntPipe({ optional: true })) skip?: number,
    @Query('search') search?: string,
  ) {
    this.logger.log(
      { userId, projectId, take, skip, search },
      'GET /goal/project/:projectId',
    )

    const project = await this.projectService.getFullProject(projectId)

    if (_isEmpty(project)) {
      throw new NotFoundException('Project not found')
    }

    this.projectService.allowedToView(project, userId, headers['x-password'])

    const { take: safeTake, skip: safeSkip } = clampPagination(take, skip)

    const result = await this.goalService.paginate(
      { take: safeTake, skip: safeSkip },
      { project: { id: projectId } },
      ['project'],
      search,
    )

    // @ts-expect-error
    result.results = _map(result.results, (goal) => ({
      ..._omit(goal, ['project']),
      pid: goal.project.id,
    }))

    return result
  }

  @ApiBearerAuth()
  @Post('/')
  @Auth()
  @ApiResponse({ status: 201, type: GoalDto })
  async createGoal(
    @Body() goalDto: CreateGoalDto,
    @CurrentUserId() uid: string,
    @Headers() headers: Record<string, string>,
    @Ip() requestIp: string,
  ) {
    this.logger.log({ uid, pid: goalDto.pid }, 'POST /goal')

    const ip = getIPFromHeaders(headers) || requestIp || ''

    const user = await this.userService.findOne({
      where: { id: uid },
    })

    if (!user.isActive) {
      throw new ForbiddenException('Please, verify your email address first')
    }

    const project = await this.projectService.getFullProject(goalDto.pid)

    if (_isEmpty(project)) {
      throw new NotFoundException('Project not found')
    }

    this.projectService.allowedToManage(
      project,
      uid,
      'You are not allowed to add goals to this project',
    )

    const goalsCount = await this.goalService.count({
      where: { project: { id: goalDto.pid } },
    })

    if (project.admin?.planCode === PlanCode.none) {
      throw new HttpException(
        'You cannot create new goals due to no active subscription. Please upgrade your account plan to continue.',
        HttpStatus.PAYMENT_REQUIRED,
      )
    }

    if (project.admin?.isAccountBillingSuspended) {
      throw new HttpException(
        'The account that owns this site is currently suspended, this is because of a billing issue. Please resolve the issue to continue.',
        HttpStatus.PAYMENT_REQUIRED,
      )
    }

    if (goalsCount >= GOALS_MAXIMUM) {
      throw new HttpException(
        `You cannot create more than ${GOALS_MAXIMUM} goals per project.`,
        HttpStatus.PAYMENT_REQUIRED,
      )
    }

    try {
      const goal = new Goal()
      goal.name = goalDto.name
      goal.type = goalDto.type
      goal.matchType = goalDto.matchType
      goal.value = goalDto.value || null
      goal.metadataFilters = goalDto.metadataFilters || null
      goal.conditions = goalDto.conditions || null
      goal.project = project

      const newGoal = await this.goalService.create(goal)

      await trackCustom(ip, headers['user-agent'], {
        ev: 'GOAL_CREATED',
        meta: {
          type: goalDto.type,
          matchType: goalDto.matchType,
          metadataFiltersExist: !!goalDto.metadataFilters?.length,
        },
      })

      return {
        ..._omit(newGoal, ['project']),
        pid: goalDto.pid,
      }
    } catch (reason) {
      this.logger.error({ reason }, 'Error while creating goal')
      throw new BadRequestException('Error occurred while creating goal')
    }
  }

  @ApiBearerAuth()
  @Put('/:id')
  @Auth()
  @ApiResponse({ status: 200, type: GoalDto })
  async updateGoal(
    @Param('id') id: string,
    @Body() goalDto: UpdateGoalDto,
    @CurrentUserId() uid: string,
  ) {
    this.logger.log({ id, uid }, 'PUT /goal/:id')

    const goal = await this.goalService.findOneWithRelations(id)

    if (_isEmpty(goal)) {
      throw new NotFoundException()
    }

    this.projectService.allowedToManage(
      goal.project,
      uid,
      'You are not allowed to manage this goal',
    )

    const updatePayload: Partial<Goal> = {
      ..._pick(goalDto, [
        'name',
        'type',
        'matchType',
        'value',
        'metadataFilters',
        'conditions',
        'active',
      ]),
    }

    await this.goalService.update(id, updatePayload)

    const updatedGoal = await this.goalService.findOne({ where: { id } })
    if (!updatedGoal) {
      throw new NotFoundException('Goal not found after update')
    }

    return {
      ..._omit(updatedGoal, ['project']),
      pid: goal.project.id,
    }
  }

  @ApiBearerAuth()
  @Delete('/:id')
  @Auth()
  @ApiResponse({ status: 204, description: 'Empty body' })
  async deleteGoal(
    @Param('id') id: string,
    @CurrentUserId() uid: string,
    @Headers() headers: Record<string, string>,
    @Ip() requestIp: string,
  ) {
    this.logger.log({ id, uid }, 'DELETE /goal/:id')

    const ip = getIPFromHeaders(headers) || requestIp || ''

    const goal = await this.goalService.findOneWithRelations(id)

    if (_isEmpty(goal)) {
      throw new NotFoundException()
    }

    this.projectService.allowedToManage(
      goal.project,
      uid,
      'You are not allowed to manage this goal',
    )

    await this.goalService.delete(id)

    await trackCustom(ip, headers['user-agent'], {
      ev: 'GOAL_DELETED',
    })
  }

  private buildGoalMatchCondition(goal: Goal): {
    condition: string
    params: Record<string, string>
  } {
    const params: Record<string, string> = {}

    if (goal.type === GoalType.CUSTOM_EVENT) {
      if (goal.matchType === GoalMatchType.EXACT) {
        params.goalValue = goal.value || ''
        return { condition: `event_name = {goalValue:String}`, params }
      }
      if ((goal.value || '').trim() === '') {
        return { condition: '1=0', params: {} }
      }
      params.goalValue = goal.value || ''
      return {
        condition: `event_name ILIKE concat('%', {goalValue:String}, '%')`,
        params,
      }
    }

    if (goal.matchType === GoalMatchType.EXACT) {
      params.goalValue = goal.value || ''
      return { condition: `pg = {goalValue:String}`, params }
    }
    if ((goal.value || '').trim() === '') {
      return { condition: '1=0', params: {} }
    }
    params.goalValue = goal.value || ''
    return {
      condition: `pg ILIKE concat('%', {goalValue:String}, '%')`,
      params,
    }
  }

  private buildMetadataCondition(goal: Goal): {
    condition: string
    params: Record<string, string>
  } {
    if (!goal.metadataFilters || goal.metadataFilters.length === 0) {
      return { condition: '', params: {} }
    }

    const conditions: string[] = []
    const params: Record<string, string> = {}

    goal.metadataFilters.forEach((filter, index) => {
      const keyParam = `metaKey${index}`
      const valueParam = `metaValue${index}`
      params[keyParam] = filter.key
      params[valueParam] = filter.value
      conditions.push(
        `has(meta.key, {${keyParam}:String}) AND meta.value[indexOf(meta.key, {${keyParam}:String})] = {${valueParam}:String}`,
      )
    })

    return {
      condition:
        conditions.length > 0 ? `AND (${conditions.join(' AND ')})` : '',
      params,
    }
  }

  private buildConditionExpression(
    condition: GoalCondition,
    index: number,
  ): { condition: string; params: Record<string, string> } {
    const params: Record<string, string> = {}
    const eventType = condition.eventType || 'any'
    const parts: string[] = []

    if (
      eventType === GoalType.PAGEVIEW ||
      eventType === GoalType.CUSTOM_EVENT
    ) {
      params[`conditionType${index}`] = eventType
      parts.push(`type = {conditionType${index}:String}`)
    } else if (condition.field === 'pg' || condition.field === 'page') {
      parts.push(`type = 'pageview'`)
    } else if (
      condition.field === 'event' ||
      condition.field === 'event_name'
    ) {
      parts.push(`type = 'custom_event'`)
    } else {
      parts.push(`type IN ('pageview', 'custom_event')`)
    }

    if (condition.field === 'metadata') {
      const keyParam = `conditionMetaKey${index}`
      params[keyParam] = condition.metadataKey || ''

      if (!params[keyParam]) {
        return { condition: '1=0', params: {} }
      }

      if (condition.operator === 'exists') {
        parts.push(`indexOf(meta.key, {${keyParam}:String}) > 0`)
      } else if (condition.operator === 'not_exists') {
        parts.push(`indexOf(meta.key, {${keyParam}:String}) = 0`)
      } else {
        const valueParam = `conditionValue${index}`
        params[valueParam] = condition.value || ''
        const valueExpression = `meta.value[indexOf(meta.key, {${keyParam}:String})]`
        parts.push(`indexOf(meta.key, {${keyParam}:String}) > 0`)

        if (condition.operator === 'contains') {
          parts.push(
            `${valueExpression} ILIKE concat('%', {${valueParam}:String}, '%')`,
          )
        } else if (condition.operator === 'not_contains') {
          parts.push(
            `NOT (${valueExpression} ILIKE concat('%', {${valueParam}:String}, '%'))`,
          )
        } else {
          parts.push(
            `${valueExpression} ${condition.operator === 'not_equals' ? '!=' : '='} {${valueParam}:String}`,
          )
        }
      }

      return { condition: `(${parts.join(' AND ')})`, params }
    }

    const column = GOAL_CONDITION_COLUMNS[condition.field]
    if (!column) {
      return { condition: '1=0', params: {} }
    }

    if (condition.operator === 'exists') {
      parts.push(`${column} IS NOT NULL AND ${column} != ''`)
    } else if (condition.operator === 'not_exists') {
      parts.push(`(${column} IS NULL OR ${column} = '')`)
    } else {
      const valueParam = `conditionValue${index}`
      params[valueParam] = condition.value || ''

      if (!params[valueParam]) {
        return { condition: '1=0', params: {} }
      }

      if (condition.operator === 'contains') {
        parts.push(`${column} ILIKE concat('%', {${valueParam}:String}, '%')`)
      } else if (condition.operator === 'not_contains') {
        parts.push(
          `NOT (${column} ILIKE concat('%', {${valueParam}:String}, '%'))`,
        )
      } else {
        parts.push(
          `${column} ${condition.operator === 'not_equals' ? '!=' : '='} {${valueParam}:String}`,
        )
      }
    }

    return { condition: `(${parts.join(' AND ')})`, params }
  }

  private buildGoalEventCondition(goal: Goal): {
    condition: string
    params: Record<string, string>
  } {
    if (goal.conditions?.conditions?.length) {
      const relation = goal.conditions.relation === 'OR' ? ' OR ' : ' AND '
      const builtConditions = goal.conditions.conditions.map(
        (condition, index) => this.buildConditionExpression(condition, index),
      )

      return {
        condition: `(${builtConditions
          .map((condition) => condition.condition)
          .join(relation)})`,
        params: Object.assign(
          {},
          ...builtConditions.map((condition) => condition.params),
        ),
      }
    }

    const goalType =
      goal.type === GoalType.CUSTOM_EVENT ? 'custom_event' : 'pageview'
    const { condition: matchCondition, params: matchParams } =
      this.buildGoalMatchCondition(goal)
    const { condition: metaCondition, params: metaParams } =
      this.buildMetadataCondition(goal)

    return {
      condition: `(type = '${goalType}' AND ${matchCondition} ${metaCondition})`,
      params: { ...matchParams, ...metaParams },
    }
  }

  private buildGoalConversionsSubquery(
    goal: Goal,
    filtersQuery: string,
  ): {
    query: string
    params: Record<string, string | number>
  } {
    const conditions = goal.conditions?.conditions || []

    if (conditions.length > 1 && goal.conditions?.relation === 'AND') {
      const builtConditions = conditions.map((condition, index) =>
        this.buildConditionExpression(condition, index),
      )

      return {
        query: `
          SELECT psid, max(matchedAt) AS conversionAt, 1 AS conversions
          FROM (
            SELECT psid, conditionIndex, min(created) AS matchedAt
            FROM (
              ${builtConditions
                .map(
                  (condition, index) => `
                    SELECT psid, created, ${index} AS conditionIndex
                    FROM events
                    WHERE pid = {pid:FixedString(12)}
                      AND (${condition.condition})
                      ${filtersQuery}
                      AND psid != 0
                      AND created BETWEEN {groupFrom:String} AND {groupTo:String}
                  `,
                )
                .join(' UNION ALL ')}
            )
            GROUP BY psid, conditionIndex
          )
          GROUP BY psid
          HAVING countDistinct(conditionIndex) = {conditionsCount:UInt32}
        `,
        params: {
          ...Object.assign(
            {},
            ...builtConditions.map((condition) => condition.params),
          ),
          conditionsCount: conditions.length,
        },
      }
    }

    const { condition: goalCondition, params: goalParams } =
      this.buildGoalEventCondition(goal)

    return {
      query: `
        SELECT psid, min(created) AS conversionAt, count() AS conversions
        FROM events
        WHERE pid = {pid:FixedString(12)}
          AND (${goalCondition})
          ${filtersQuery}
          AND psid != 0
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY psid
      `,
      params: goalParams,
    }
  }

  private async getGoalBreakdowns(
    projectId: string,
    groupFrom: string,
    groupTo: string,
    conversionsSubquery: string,
    queryParams: Record<string, unknown>,
  ): Promise<GoalBreakdowns> {
    const emptyBreakdowns: GoalBreakdowns = {
      countries: {},
      devices: {},
      browsers: {},
      sources: {},
      campaigns: {},
      pages: {},
      profileTypes: {},
    }

    const query = `
      WITH conversions AS (
        ${conversionsSubquery}
      ),
      session_info AS (
        SELECT
          c.psid AS psid,
          argMin(e.cc, e.created) AS cc,
          argMin(e.dv, e.created) AS dv,
          argMin(e.br, e.created) AS br,
          argMin(if(e.so IS NOT NULL AND e.so != '', e.so, if(domain(e.ref) != '', domain(e.ref), 'Direct / None')), e.created) AS source,
          argMin(e.ca, e.created) AS campaign,
          argMin(e.pg, e.created) AS page,
          argMax(e.profileId, e.created) AS profileId
        FROM conversions c
        INNER JOIN events e ON c.psid = e.psid
        WHERE e.pid = {pid:FixedString(12)}
          AND e.type IN ('pageview', 'custom_event')
          AND e.created BETWEEN {groupFrom:String} AND {groupTo:String}
          AND e.created <= c.conversionAt
        GROUP BY c.psid
      )
      SELECT kind, val, cnt FROM (
        SELECT 'countries' AS kind, cc AS val, count() AS cnt
        FROM session_info
        WHERE cc IS NOT NULL AND cc != ''
        GROUP BY cc

        UNION ALL

        SELECT 'devices' AS kind, dv AS val, count() AS cnt
        FROM session_info
        WHERE dv IS NOT NULL AND dv != ''
        GROUP BY dv

        UNION ALL

        SELECT 'browsers' AS kind, br AS val, count() AS cnt
        FROM session_info
        WHERE br IS NOT NULL AND br != ''
        GROUP BY br

        UNION ALL

        SELECT 'sources' AS kind, source AS val, count() AS cnt
        FROM session_info
        GROUP BY val

        UNION ALL

        SELECT 'campaigns' AS kind, campaign AS val, count() AS cnt
        FROM session_info
        WHERE campaign IS NOT NULL AND campaign != ''
        GROUP BY campaign

        UNION ALL

        SELECT 'pages' AS kind, page AS val, count() AS cnt
        FROM session_info
        WHERE page IS NOT NULL AND page != ''
        GROUP BY page

        UNION ALL

        SELECT 'profileTypes' AS kind, if(startsWith(profileId, {profilePrefixUser:String}), 'identified', 'anonymous') AS val, count() AS cnt
        FROM session_info
        WHERE profileId IS NOT NULL AND profileId != ''
        GROUP BY val
      )
      ORDER BY kind, cnt DESC, val ASC
      LIMIT 5 BY kind
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: {
          ...queryParams,
          pid: projectId,
          groupFrom,
          groupTo,
          profilePrefixUser: AnalyticsService.PROFILE_PREFIX_USER,
        },
      })
      .then((resultSet) =>
        resultSet.json<{
          kind: GoalBreakdownKey
          val: string
          cnt: number
        }>(),
      )

    for (const row of data) {
      if (!emptyBreakdowns[row.kind] || !row.val) {
        continue
      }

      emptyBreakdowns[row.kind][row.val] = row.cnt
    }

    return emptyBreakdowns
  }

  private async getGoalTimeToConvert(
    projectId: string,
    groupFrom: string,
    groupTo: string,
    conversionsSubquery: string,
    queryParams: Record<string, unknown>,
  ): Promise<{
    fromSessionStart: TimeToConvertMetric
    fromFirstPage: TimeToConvertMetric
  }> {
    const emptyMetric: TimeToConvertMetric = {
      average: null,
      median: null,
      p75: null,
    }

    const query = `
      WITH conversions AS (
        ${conversionsSubquery}
      ),
      session_starts AS (
        SELECT psid, min(firstSeen) AS sessionStart
        FROM sessions FINAL
        WHERE pid = {pid:FixedString(12)}
        GROUP BY psid
      ),
      first_pages AS (
        SELECT psid, min(created) AS firstPageAt
        FROM events
        WHERE pid = {pid:FixedString(12)}
          AND type = 'pageview'
          AND psid != 0
          AND created BETWEEN {groupFrom:String} AND {groupTo:String}
        GROUP BY psid
      )
      SELECT
        round(avgOrNull(if(sessionStart > toDateTime(0) AND sessionStart <= conversionAt, dateDiff('second', sessionStart, conversionAt), NULL)), 2) AS averageSession,
        quantileExactOrNull(0.5)(if(sessionStart > toDateTime(0) AND sessionStart <= conversionAt, dateDiff('second', sessionStart, conversionAt), NULL)) AS medianSession,
        quantileExactOrNull(0.75)(if(sessionStart > toDateTime(0) AND sessionStart <= conversionAt, dateDiff('second', sessionStart, conversionAt), NULL)) AS p75Session,
        round(avgOrNull(if(firstPageAt > toDateTime(0) AND firstPageAt <= conversionAt, dateDiff('second', firstPageAt, conversionAt), NULL)), 2) AS averageFirstPage,
        quantileExactOrNull(0.5)(if(firstPageAt > toDateTime(0) AND firstPageAt <= conversionAt, dateDiff('second', firstPageAt, conversionAt), NULL)) AS medianFirstPage,
        quantileExactOrNull(0.75)(if(firstPageAt > toDateTime(0) AND firstPageAt <= conversionAt, dateDiff('second', firstPageAt, conversionAt), NULL)) AS p75FirstPage
      FROM conversions c
      LEFT JOIN session_starts ss ON c.psid = ss.psid
      LEFT JOIN first_pages fp ON c.psid = fp.psid
    `

    const { data } = await clickhouse
      .query({
        query,
        query_params: { ...queryParams, pid: projectId, groupFrom, groupTo },
      })
      .then((resultSet) =>
        resultSet.json<{
          averageSession: number | null
          medianSession: number | null
          p75Session: number | null
          averageFirstPage: number | null
          medianFirstPage: number | null
          p75FirstPage: number | null
        }>(),
      )

    const row = data[0]

    if (!row) {
      return {
        fromSessionStart: emptyMetric,
        fromFirstPage: emptyMetric,
      }
    }

    return {
      fromSessionStart: {
        average: row.averageSession,
        median: row.medianSession,
        p75: row.p75Session,
      },
      fromFirstPage: {
        average: row.averageFirstPage,
        median: row.medianFirstPage,
        p75: row.p75FirstPage,
      },
    }
  }

  @ApiBearerAuth()
  @Get('/:id/stats')
  @Auth(true, true)
  @ApiKeyAccess('goals:read', 'goal', 'id')
  @ApiResponse({ status: 200, type: GoalStatsDto })
  async getGoalStats(
    @CurrentUserId() userId: string,
    @Param('id') id: string,
    @Query('period') period: string,
    @Query('from') from?: string,
    @Query('to') to?: string,
    @Query('timezone') timezone?: string,
    @Query('filters') filters?: string,
  ) {
    this.logger.log({ userId, id, period, from, to }, 'GET /goal/:id/stats')

    const goal = await this.goalService.findOneWithRelations(id)

    if (_isEmpty(goal)) {
      throw new NotFoundException('Goal not found')
    }

    const project = await this.projectService.getFullProject(goal.project.id)
    this.projectService.allowedToView(project, userId)

    const safeTimezone = this.analyticsService.getSafeTimezone(timezone)
    const timeBucket = getLowestPossibleTimeBucket(period, from, to)

    const { groupFromUTC, groupToUTC } = this.analyticsService.getGroupFromTo(
      from,
      to,
      timeBucket,
      period,
      safeTimezone,
    )

    const [filtersQuery, filtersParams] = this.analyticsService.getFiltersQuery(
      normalizeFiltersToV1Json(filters, 'traffic'),
      DataType.ANALYTICS,
    )
    const { query: conversionsSubquery, params: goalParams } =
      this.buildGoalConversionsSubquery(goal, filtersQuery)

    const conversionsQuery = `
      WITH conversions AS (
        ${conversionsSubquery}
      )
      SELECT 
        sum(conversions) as conversions,
        count() as uniqueSessions
      FROM conversions
    `

    const queryParams = {
      pid: goal.project.id,
      groupFrom: groupFromUTC,
      groupTo: groupToUTC,
      ...goalParams,
      ...filtersParams,
    }

    const { data: conversionsData } = await clickhouse
      .query({ query: conversionsQuery, query_params: queryParams })
      .then((resultSet) =>
        resultSet.json<{ conversions: number; uniqueSessions: number }>(),
      )

    const conversions = conversionsData[0]?.conversions || 0
    const uniqueSessions = conversionsData[0]?.uniqueSessions || 0

    const totalSessionsQuery = `
      SELECT uniqExact(psid) as totalSessions
      FROM events
      WHERE 
        pid = {pid:FixedString(12)}
        AND type IN ('pageview', 'custom_event')
        AND psid != 0
        ${filtersQuery}
        AND created BETWEEN {groupFrom:String} AND {groupTo:String}
    `

    const { data: totalData } = await clickhouse
      .query({
        query: totalSessionsQuery,
        query_params: {
          pid: goal.project.id,
          groupFrom: groupFromUTC,
          groupTo: groupToUTC,
          ...filtersParams,
        },
      })
      .then((resultSet) => resultSet.json<{ totalSessions: number }>())

    const totalSessions = totalData[0]?.totalSessions || 1
    const conversionRate = _round((uniqueSessions / totalSessions) * 100, 2)

    const periodDays = dayjs(groupToUTC).diff(dayjs(groupFromUTC), 'day') || 1
    const previousFrom = dayjs(groupFromUTC)
      .subtract(periodDays, 'day')
      .format('YYYY-MM-DD HH:mm:ss')
    const previousTo = groupFromUTC

    const previousQueryParams = {
      pid: goal.project.id,
      groupFrom: previousFrom,
      groupTo: previousTo,
      ...goalParams,
      ...filtersParams,
    }

    const { data: previousData } = await clickhouse
      .query({ query: conversionsQuery, query_params: previousQueryParams })
      .then((resultSet) =>
        resultSet.json<{ conversions: number; uniqueSessions: number }>(),
      )

    const previousConversions = previousData[0]?.conversions || 0
    const trend =
      previousConversions > 0
        ? _round(
            ((conversions - previousConversions) / previousConversions) * 100,
            2,
          )
        : conversions > 0
          ? 100
          : 0

    const [breakdowns, timeToConvert] = await Promise.all([
      this.getGoalBreakdowns(
        goal.project.id,
        groupFromUTC,
        groupToUTC,
        conversionsSubquery,
        { ...goalParams, ...filtersParams },
      ),
      this.getGoalTimeToConvert(
        goal.project.id,
        groupFromUTC,
        groupToUTC,
        conversionsSubquery,
        { ...goalParams, ...filtersParams },
      ),
    ])

    return {
      conversions,
      uniqueSessions,
      conversionRate,
      previousConversions,
      trend,
      breakdowns,
      timeToConvert,
    }
  }

  @ApiBearerAuth()
  @Get('/:id/sessions')
  @Auth(true, true)
  @ApiKeyAccess('goals:read', 'goal', 'id', [
    'analytics:read',
    'events:read',
    'errors:read',
    'replays:read',
    'revenue:read',
  ])
  async getGoalSessions(
    @CurrentUserId() userId: string,
    @Param('id') id: string,
    @Query('period') period = '7d',
    @Query('from') from?: string,
    @Query('to') to?: string,
    @Query('timezone') timezone?: string,
    @Query('take') take?: string,
    @Query('skip') skip?: string,
  ) {
    this.logger.log({ userId, id, period, from, to }, 'GET /goal/:id/sessions')

    const goal = await this.goalService.findOneWithRelations(id)

    if (_isEmpty(goal)) {
      throw new NotFoundException('Goal not found')
    }

    const project = await this.projectService.getFullProject(goal.project.id)
    this.projectService.allowedToView(project, userId)

    const safeTimezone = this.analyticsService.getSafeTimezone(timezone)
    const timeBucket = getLowestPossibleTimeBucket(period, from, to)

    const { groupFromUTC, groupToUTC } = this.analyticsService.getGroupFromTo(
      from,
      to,
      timeBucket,
      period,
      safeTimezone,
    )

    const goalType =
      goal.type === GoalType.CUSTOM_EVENT ? 'custom_event' : 'pageview'
    const { condition: matchCondition, params: matchParams } =
      this.buildGoalMatchCondition(goal)
    const { condition: metaCondition, params: metaParams } =
      this.buildMetadataCondition(goal)
    const { take: safeTake, skip: safeSkip } = clampPagination(
      Number(take) || 30,
      Number(skip) || 0,
    )
    const projectId = goal.project.id
    const sessions = await this.analyticsService.getSessionsList(
      '',
      {
        params: {
          pid: projectId,
          groupFrom: groupFromUTC,
          groupTo: groupToUTC,
          ...matchParams,
          ...metaParams,
        },
      },
      safeTimezone,
      Math.min(safeTake, 150),
      safeSkip,
      false,
      goalType,
      `AND ${matchCondition} ${metaCondition}`,
    )

    return { sessions, take: Math.min(safeTake, 150), skip: safeSkip }
  }

  private getGroupSubquery(
    timeBucket: string,
  ): [selector: string, groupBy: string] {
    if (timeBucket === 'minute') {
      return [
        'toYear(tz_created) as year, toMonth(tz_created) as month, toDayOfMonth(tz_created) as day, toHour(tz_created) as hour, toMinute(tz_created) as minute',
        'year, month, day, hour, minute',
      ]
    }

    if (timeBucket === 'hour') {
      return [
        'toYear(tz_created) as year, toMonth(tz_created) as month, toDayOfMonth(tz_created) as day, toHour(tz_created) as hour',
        'year, month, day, hour',
      ]
    }

    if (timeBucket === 'day') {
      return [
        'toYear(tz_created) as year, toMonth(tz_created) as month, toDayOfMonth(tz_created) as day',
        'year, month, day',
      ]
    }

    if (timeBucket === 'month') {
      return [
        'toYear(tz_created) as year, toMonth(tz_created) as month',
        'year, month',
      ]
    }

    // year
    return ['toYear(tz_created) as year', 'year']
  }

  private generateDateString(row: { [key: string]: number }): string {
    const { year, month, day, hour, minute } = row

    let dateString = `${year}`

    if (typeof month === 'number') {
      if (month < 10) {
        dateString += `-0${month}`
      } else {
        dateString += `-${month}`
      }
    }

    if (typeof day === 'number') {
      if (day < 10) {
        dateString += `-0${day}`
      } else {
        dateString += `-${day}`
      }
    }

    if (typeof hour === 'number') {
      const strMinute =
        typeof minute === 'number'
          ? minute < 10
            ? `0${minute}`
            : minute
          : '00'

      if (hour < 10) {
        dateString += ` 0${hour}:${strMinute}:00`
      } else {
        dateString += ` ${hour}:${strMinute}:00`
      }
    }

    return dateString
  }

  @ApiBearerAuth()
  @Get('/:id/chart')
  @Auth(true, true)
  @ApiKeyAccess('goals:read', 'goal', 'id')
  async getGoalChart(
    @CurrentUserId() userId: string,
    @Param('id') id: string,
    @Query('period') period: string,
    @Query('from') from?: string,
    @Query('to') to?: string,
    @Query('timeBucket') timeBucket?: string,
    @Query('timezone') timezone?: string,
    @Query('filters') filters?: string,
  ) {
    this.logger.log(
      { userId, id, period, from, to, timeBucket },
      'GET /goal/:id/chart',
    )

    const goal = await this.goalService.findOneWithRelations(id)

    if (_isEmpty(goal)) {
      throw new NotFoundException('Goal not found')
    }

    const project = await this.projectService.getFullProject(goal.project.id)
    this.projectService.allowedToView(project, userId)

    const safeTimezone = this.analyticsService.getSafeTimezone(timezone)
    const resolvedTimeBucket =
      timeBucket || getLowestPossibleTimeBucket(period, from, to)

    const { groupFromUTC, groupToUTC } = this.analyticsService.getGroupFromTo(
      from,
      to,
      resolvedTimeBucket as any,
      period,
      safeTimezone,
    )

    const { xShifted } = this.analyticsService.generateXAxis(
      resolvedTimeBucket as any,
      groupFromUTC,
      groupToUTC,
      safeTimezone,
    )

    const timeBucketFunc = Object.prototype.hasOwnProperty.call(
      timeBucketConversion,
      resolvedTimeBucket,
    )
      ? timeBucketConversion[resolvedTimeBucket]
      : 'toStartOfDay'
    const [selector, groupBy] = this.getGroupSubquery(resolvedTimeBucket)

    const [filtersQuery, filtersParams] = this.analyticsService.getFiltersQuery(
      normalizeFiltersToV1Json(filters, 'traffic'),
      DataType.ANALYTICS,
    )
    const { query: conversionsSubquery, params: goalParams } =
      this.buildGoalConversionsSubquery(goal, filtersQuery)

    const chartQuery = `
      SELECT
        ${selector},
        sum(conversions) as conversions,
        count() as uniqueSessions
      FROM (
        SELECT
          psid,
          conversions,
          ${timeBucketFunc}(toTimeZone(conversionAt, {timezone:String})) as tz_created
        FROM (
          ${conversionsSubquery}
        )
      ) as subquery
      GROUP BY ${groupBy}
      ORDER BY ${groupBy}
    `

    const queryParams = {
      pid: goal.project.id,
      groupFrom: groupFromUTC,
      groupTo: groupToUTC,
      timezone: safeTimezone,
      ...goalParams,
      ...filtersParams,
    }

    const { data } = await clickhouse
      .query({ query: chartQuery, query_params: queryParams })
      .then((resultSet) =>
        resultSet.json<{
          year: number
          month?: number
          day?: number
          hour?: number
          minute?: number
          conversions: number
          uniqueSessions: number
        }>(),
      )

    // Initialize arrays with zeros for all time buckets
    const conversions = Array(xShifted.length).fill(0)
    const uniqueSessions = Array(xShifted.length).fill(0)

    // Fill in actual data at the correct indices
    for (const row of data) {
      const dateString = this.generateDateString(row)
      const index = xShifted.indexOf(dateString)

      if (index !== -1) {
        conversions[index] = row.conversions
        uniqueSessions[index] = row.uniqueSessions
      }
    }

    return {
      chart: {
        x: xShifted,
        conversions,
        uniqueSessions,
      },
    }
  }
}