All files / src/app/api/admin/analytics/funnels route.ts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
export const dynamic = "force-dynamic";

/**
 * Funnel Analysis API Route
 *
 * Provides funnel analysis data for the admin dashboard.
 * GET - Get funnel metrics
 */

import { NextRequest, NextResponse } from 'next/server';
import { } from "next-auth";
import { prisma } from "@/lib/prisma";
import { } from "@/lib/core";
import { FUNNELS, type FunnelId } from "@/lib/analytics/funnels";
import {
  withAdmin,
  withErrorHandling,
  successResponse,
  ApiSuccessResponse,
  ApiErrorResponse } from "@/lib/api";
import { } from "@/lib/api/middleware";

/**
 * Get start date based on range string
 */
function getStartDate(range: string): Date {
  const now = new Date();
  switch (range) {
    case "1d":
      return new Date(now.getTime() - 24 * 60 * 60 * 1000);
    case "7d":
      return new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000);
    case "30d":
      return new Date(now.getTime() - 30 * 24 * 60 * 60 * 1000);
    case "90d":
      return new Date(now.getTime() - 90 * 24 * 60 * 60 * 1000);
    default:
      return new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000);
  }
}

/**
 * GET /api/admin/analytics/funnels
 * Get funnel analysis data
 * Query params:
 *   - range (1d|7d|30d|90d)
 *   - funnel (checkout|signup|productDiscovery|engagement) - optional, defaults to all
 */
async function handleGet(request: NextRequest): Promise<NextResponse<ApiSuccessResponse<unknown> | ApiErrorResponse>> {
  const { searchParams } = new URL(request.url);
  const range = searchParams.get("range") || "7d";
  const funnelId = searchParams.get("funnel") as FunnelId | null;
  const startDate = getStartDate(range);

  // Determine which funnels to analyze
  const funnelIds = funnelId ? [funnelId] : Object.keys(FUNNELS) as FunnelId[];

  // Get funnel data for each funnel
  const funnelResults = await Promise.all(
    funnelIds.map(async (id) => {
      const funnel = FUNNELS[id];
      const stepEventNames = funnel.steps.map((s) => s.id);

      // Get counts for each step
      // Funnel events are stored with eventName="funnel_step" and step_id in properties
      const stepCounts = await Promise.all(
        stepEventNames.map(async (stepId, index) => {
          // Use raw SQL for JSON path query since Prisma's JSON filtering
          // syntax varies by database type
          // Note: Table is "analytics_events" and columns use snake_case (event_type, event_name, created_at)
          const result = await prisma.$queryRaw<[{ count: bigint }]>`
            SELECT COUNT(*) as count
            FROM analytics_events
            WHERE created_at >= ${startDate}
              AND event_type = 'funnel'
              AND event_name = 'funnel_step'
              AND JSON_EXTRACT(properties, '$.step_id') = ${stepId}
          `;
          const count = Number(result[0]?.count || 0);

          return {
            id: stepId,
            name: funnel.steps[index].name,
            description: funnel.steps[index].description,
            count,
            index };
        })
      );

      // Calculate conversion rates between steps
      // Conversion rate = (current step count / previous step count) * 100
      // Dropoff rate = max(0, 100 - conversion rate) - never negative
      const stepsWithConversion = stepCounts.map((step, index) => {
        const previousStep = index > 0 ? stepCounts[index - 1] : null;

        // Calculate raw conversion rate
        let conversionRate: number;
        if (index === 0) {
          conversionRate = 100;
        } else if (previousStep && previousStep.count > 0) {
          conversionRate = Number(((step.count / previousStep.count) * 100).toFixed(1));
        } else {
          conversionRate = step.count > 0 ? 100 : 0;
        }

        // Dropoff rate: if conversion > 100%, dropoff is 0 (or could show negative as "growth")
        // Using Math.max to avoid negative dropoff when there are more events in current step
        const dropoffRate = index === 0 ? 0 : Number(Math.max(0, 100 - conversionRate).toFixed(1));

        return {
          ...step,
          conversionRate,
          dropoffRate };
      });

      // Calculate overall funnel conversion
      const firstStepCount = stepCounts[0]?.count || 0;
      const lastStepCount = stepCounts[stepCounts.length - 1]?.count || 0;
      const overallConversionRate = firstStepCount > 0
        ? Math.round((lastStepCount / firstStepCount) * 100 * 10) / 10
        : 0;

      return {
        id,
        name: funnel.name,
        description: funnel.description,
        steps: stepsWithConversion,
        overallConversionRate,
        totalEntries: firstStepCount,
        totalCompletions: lastStepCount };
    })
  );

  return successResponse({
    funnels: funnelResults,
    dateRange: {
      start: startDate.toISOString(),
      end: new Date().toISOString() } });
}

export const GET = withErrorHandling(withAdmin(handleGet));