show label

app/api/events/track/route.ts

@@ -81,6 +81,8 @@ export async function POST(req: NextRequest) {
       utm_source: eventData.utm_source || '',
       utm_medium: eventData.utm_medium || '',
       utm_campaign: eventData.utm_campaign || '',
+      utm_term: eventData.utm_term || '',
+      utm_content: eventData.utm_content || '',
       
       // Interaction information
       time_spent_sec: eventData.time_spent_sec || 0,

app/api/events/utm/route.ts

@@ -0,0 +1,157 @@
+import { NextRequest, NextResponse } from 'next/server';
+import clickhouse from '@/lib/clickhouse';
+import type { ApiResponse } from '@/lib/types';
+
+interface UtmData {
+  utm_value: string;
+  clicks: number;
+  visitors: number;
+  avg_time_spent: number;
+  bounces: number;
+  conversions: number;
+}
+
+// 格式化日期时间字符串为ClickHouse支持的格式
+const formatDateTime = (dateStr: string): string => {
+  return dateStr.replace('T', ' ').replace('Z', '');
+};
+
+export async function GET(request: NextRequest) {
+  try {
+    const searchParams = request.nextUrl.searchParams;
+    
+    // 获取过滤参数
+    const startTime = searchParams.get('startTime');
+    const endTime = searchParams.get('endTime');
+    const linkId = searchParams.get('linkId');
+    
+    // 获取团队、项目和标签筛选参数
+    const teamIds = searchParams.getAll('teamId');
+    const projectIds = searchParams.getAll('projectId');
+    const tagIds = searchParams.getAll('tagId');
+    const tagNames = searchParams.getAll('tagName');
+    
+    // 获取UTM类型参数
+    const utmType = searchParams.get('utmType') || 'source';
+    
+    // 构建WHERE子句
+    let whereClause = '';
+    const conditions = [];
+    
+    if (startTime) {
+      conditions.push(`event_time >= toDateTime('${formatDateTime(startTime)}')`);
+    }
+    
+    if (endTime) {
+      conditions.push(`event_time <= toDateTime('${formatDateTime(endTime)}')`);
+    }
+    
+    if (linkId) {
+      conditions.push(`link_id = '${linkId}'`);
+    }
+    
+    // 添加团队筛选
+    if (teamIds && teamIds.length > 0) {
+      // 如果只有一个团队ID
+      if (teamIds.length === 1) {
+        conditions.push(`team_id = '${teamIds[0]}'`);
+      } else {
+        // 多个团队ID
+        conditions.push(`team_id IN ('${teamIds.join("','")}')`);
+      }
+    }
+    
+    // 添加项目筛选
+    if (projectIds && projectIds.length > 0) {
+      // 如果只有一个项目ID
+      if (projectIds.length === 1) {
+        conditions.push(`project_id = '${projectIds[0]}'`);
+      } else {
+        // 多个项目ID
+        conditions.push(`project_id IN ('${projectIds.join("','")}')`);
+      }
+    }
+    
+    // 添加标签筛选
+    if ((tagIds && tagIds.length > 0) || (tagNames && tagNames.length > 0)) {
+      // 优先使用tagNames，如果有的话
+      const tagsToUse = tagNames.length > 0 ? tagNames : tagIds;
+      
+      // 使用与buildFilter函数相同的处理方式
+      const tagConditions = tagsToUse.map(tag => 
+        `link_tags LIKE '%${tag}%'`
+      );
+      conditions.push(`(${tagConditions.join(' OR ')})`);
+    }
+    
+    if (conditions.length > 0) {
+      whereClause = `WHERE ${conditions.join(' AND ')}`;
+    }
+    
+    // 确定要分组的UTM字段
+    let utmField;
+    switch (utmType) {
+      case 'source':
+        utmField = 'utm_source';
+        break;
+      case 'medium':
+        utmField = 'utm_medium';
+        break;
+      case 'campaign':
+        utmField = 'utm_campaign';
+        break;
+      case 'term':
+        utmField = 'utm_term';
+        break;
+      case 'content':
+        utmField = 'utm_content';
+        break;
+      default:
+        utmField = 'utm_source';
+    }
+    
+    // 构建SQL查询
+    const query = `
+      SELECT 
+        ${utmField} AS utm_value,
+        COUNT(*) AS clicks,
+        uniqExact(visitor_id) AS visitors,
+        round(AVG(time_spent_sec), 2) AS avg_time_spent,
+        countIf(is_bounce = 1) AS bounces,
+        countIf(conversion_type IN ('visit', 'stay', 'interact', 'signup', 'subscription', 'purchase')) AS conversions
+      FROM shorturl_analytics.events
+      ${whereClause}
+      ${whereClause ? 'AND' : 'WHERE'} ${utmField} != ''
+      GROUP BY utm_value
+      ORDER BY clicks DESC
+      LIMIT 100
+    `;
+    
+    // 执行查询
+    const result = await clickhouse.query({
+      query,
+      format: 'JSONEachRow',
+    });
+    
+    // 获取查询结果
+    const rows = await result.json();
+    const data = rows as UtmData[];
+    
+    // 返回数据
+    const response: ApiResponse<UtmData[]> = {
+      success: true,
+      data
+    };
+    
+    return NextResponse.json(response);
+  } catch (error) {
+    console.error('Error fetching UTM data:', error);
+    
+    const response: ApiResponse<null> = {
+      success: false,
+      error: error instanceof Error ? error.message : 'Unknown error occurred'
+    };
+    
+    return NextResponse.json(response, { status: 500 });
+  }
+}

app/api/types.ts

@@ -54,6 +54,8 @@ export interface Event {
   utm_source: string;
   utm_medium: string;
   utm_campaign: string;
+  utm_term: string;
+  utm_content: string;
   
   // 交互信息
   time_spent_sec: number;

app/components/analytics/UtmAnalytics.tsx

@@ -0,0 +1,203 @@
+"use client";
+
+import { useState, useEffect } from 'react';
+
+interface UtmData {
+  utm_value: string;
+  clicks: number;
+  visitors: number;
+  avg_time_spent: number;
+  bounces: number;
+  conversions: number;
+}
+
+interface UtmAnalyticsProps {
+  startTime?: string;
+  endTime?: string;
+  linkId?: string;
+  teamIds?: string[];
+  projectIds?: string[];
+  tagIds?: string[];
+}
+
+export default function UtmAnalytics({ startTime, endTime, linkId, teamIds, projectIds, tagIds }: UtmAnalyticsProps) {
+  const [activeTab, setActiveTab] = useState<string>('source');
+  const [utmData, setUtmData] = useState<UtmData[]>([]);
+  const [isLoading, setIsLoading] = useState<boolean>(false);
+  const [error, setError] = useState<string | null>(null);
+  
+  // 加载UTM数据
+  useEffect(() => {
+    const fetchUtmData = async () => {
+      setIsLoading(true);
+      setError(null);
+      
+      try {
+        // 构建URL参数
+        const params = new URLSearchParams();
+        if (startTime) params.append('startTime', startTime);
+        if (endTime) params.append('endTime', endTime);
+        if (linkId) params.append('linkId', linkId);
+        params.append('utmType', activeTab);
+        
+        // 添加团队ID参数
+        if (teamIds && teamIds.length > 0) {
+          teamIds.forEach(id => params.append('teamId', id));
+        }
+        
+        // 添加项目ID参数
+        if (projectIds && projectIds.length > 0) {
+          projectIds.forEach(id => params.append('projectId', id));
+        }
+        
+        // 添加标签名称参数
+        if (tagIds && tagIds.length > 0) {
+          tagIds.forEach(tagName => params.append('tagName', tagName));
+        }
+        
+        // 发送请求
+        const response = await fetch(`/api/events/utm?${params}`);
+        
+        if (!response.ok) {
+          throw new Error('Failed to fetch UTM data');
+        }
+        
+        const result = await response.json();
+        
+        if (result.success) {
+          setUtmData(result.data || []);
+        } else {
+          throw new Error(result.error || 'Failed to fetch UTM data');
+        }
+      } catch (err) {
+        setError(err instanceof Error ? err.message : 'Unknown error occurred');
+        console.error('Error fetching UTM data:', err);
+      } finally {
+        setIsLoading(false);
+      }
+    };
+    
+    fetchUtmData();
+  }, [activeTab, startTime, endTime, linkId, teamIds, projectIds, tagIds]);
+  
+  // 安全地格式化数字
+  const formatNumber = (value: number | undefined | null): string => {
+    if (value === undefined || value === null) return '0';
+    return value.toLocaleString();
+  };
+  
+  return (
+    <div className="bg-white rounded-lg shadow p-6 mb-8">
+      <h2 className="text-lg font-semibold text-gray-900 mb-4">UTM Parameters</h2>
+      
+      <div className="mb-4 border-b">
+        <div className="flex">
+          <button 
+            onClick={() => setActiveTab('source')} 
+            className={`px-4 py-2 ${activeTab === 'source' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
+          >
+            Source
+          </button>
+          <button 
+            onClick={() => setActiveTab('medium')} 
+            className={`px-4 py-2 ${activeTab === 'medium' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
+          >
+            Medium
+          </button>
+          <button 
+            onClick={() => setActiveTab('campaign')} 
+            className={`px-4 py-2 ${activeTab === 'campaign' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
+          >
+            Campaign
+          </button>
+          <button 
+            onClick={() => setActiveTab('term')} 
+            className={`px-4 py-2 ${activeTab === 'term' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
+          >
+            Term
+          </button>
+          <button 
+            onClick={() => setActiveTab('content')} 
+            className={`px-4 py-2 ${activeTab === 'content' ? 'border-b-2 border-blue-500 text-blue-600' : 'text-gray-500'}`}
+          >
+            Content
+          </button>
+        </div>
+      </div>
+      
+      {isLoading ? (
+        <div className="flex justify-center items-center py-8">
+          <div className="animate-spin rounded-full h-6 w-6 border-b-2 border-blue-500"></div>
+          <span className="ml-2 text-gray-500">Loading...</span>
+        </div>
+      ) : error ? (
+        <div className="text-red-500 text-center py-8">
+          Error: {error}
+        </div>
+      ) : utmData.length === 0 ? (
+        <div className="text-gray-500 text-center py-8">
+          No data available
+        </div>
+      ) : (
+        <div className="overflow-x-auto">
+          <table className="min-w-full divide-y divide-gray-200">
+            <thead className="bg-gray-50">
+              <tr>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  {activeTab === 'source' ? 'Source' : 
+                   activeTab === 'medium' ? 'Medium' : 
+                   activeTab === 'campaign' ? 'Campaign' : 
+                   activeTab === 'term' ? 'Term' : 'Content'}
+                </th>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  Clicks
+                </th>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  Visitors
+                </th>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  Avg. Time
+                </th>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  Bounce Rate
+                </th>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  Conversions
+                </th>
+              </tr>
+            </thead>
+            <tbody className="bg-white divide-y divide-gray-200">
+              {utmData.map((item, index) => {
+                const bounceRate = item.clicks > 0 ? (item.bounces / item.clicks) * 100 : 0;
+                const conversionRate = item.clicks > 0 ? (item.conversions / item.clicks) * 100 : 0;
+                
+                return (
+                  <tr key={index} className={index % 2 === 0 ? 'bg-white' : 'bg-gray-50'}>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {item.utm_value || 'Unknown'}
+                    </td>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {formatNumber(item.clicks)}
+                    </td>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {formatNumber(item.visitors)}
+                    </td>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {item.avg_time_spent.toFixed(1)}s
+                    </td>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {bounceRate.toFixed(1)}%
+                    </td>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-gray-900">
+                      {formatNumber(item.conversions)} ({conversionRate.toFixed(1)}%)
+                    </td>
+                  </tr>
+                );
+              })}
+            </tbody>
+          </table>
+        </div>
+      )}
+    </div>
+  );
+} 

app/page.tsx

@@ -6,6 +6,7 @@ import { DateRangePicker } from '@/app/components/ui/DateRangePicker';
 import TimeSeriesChart from '@/app/components/charts/TimeSeriesChart';
 import GeoAnalytics from '@/app/components/analytics/GeoAnalytics';
 import DevicePieCharts from '@/app/components/charts/DevicePieCharts';
+import UtmAnalytics from '@/app/components/analytics/UtmAnalytics';
 import { EventsSummary, TimeSeriesData, GeoData, DeviceAnalytics as DeviceAnalyticsType } from '@/app/api/types';
 import { TeamSelector } from '@/app/components/ui/TeamSelector';
 import { ProjectSelector } from '@/app/components/ui/ProjectSelector';
@@ -96,6 +97,7 @@ const extractEventInfo = (event: Event) => {
     eventTime: event.created_at || event.event_time,
     linkName: event.link_label || linkAttrs?.name || eventAttrs?.link_name || event.link_slug || '-',
     originalUrl: event.link_original_url || eventAttrs?.origin_url || '-',
+    fullUrl: eventAttrs?.full_url || '-',
     eventType: event.event_type || '-',
     visitorId: event.visitor_id?.substring(0, 8) || '-',
     referrer: eventAttrs?.referrer || '-',
@@ -123,9 +125,8 @@ export default function HomePage() {
   const [selectedTeamIds, setSelectedTeamIds] = useState<string[]>([]);
   // 添加项目选择状态 - 使用数组支持多选
   const [selectedProjectIds, setSelectedProjectIds] = useState<string[]>([]);
-  // 添加标签选择状态 - 使用数组支持多选
-  const [selectedTagIds, setSelectedTagIds] = useState<string[]>([]);
-  
+  // 添加标签名称状态 - 用于在UI中显示和API请求
+  const [selectedTagNames, setSelectedTagNames] = useState<string[]>([]);
   // 添加分页状态
   const [currentPage, setCurrentPage] = useState<number>(1);
   const [pageSize, setPageSize] = useState<number>(10);
@@ -171,10 +172,10 @@ export default function HomePage() {
           });
         }
         
-        // 添加标签ID参数 - 支持多个标签
-        if (selectedTagIds.length > 0) {
-          selectedTagIds.forEach(tagId => {
-            params.append('tagId', tagId);
+        // 添加标签名称参数 - 支持多个标签
+        if (selectedTagNames.length > 0) {
+          selectedTagNames.forEach(tagName => {
+            params.append('tagName', tagName);
           });
         }
 
@@ -223,7 +224,7 @@ export default function HomePage() {
     };
 
     fetchData();
-  }, [dateRange, selectedTeamIds, selectedProjectIds, selectedTagIds, currentPage, pageSize]);
+  }, [dateRange, selectedTeamIds, selectedProjectIds, selectedTagNames, currentPage, pageSize]);
 
   if (loading) {
     return (
@@ -271,8 +272,17 @@ export default function HomePage() {
             teamIds={selectedTeamIds.length > 0 ? selectedTeamIds : undefined}
           />
           <TagSelector
-            value={selectedTagIds}
-            onChange={(value) => setSelectedTagIds(Array.isArray(value) ? value : [value])}
+            value={selectedTagNames}
+            onChange={(value) => {
+              // TagSelector返回的是标签名称
+              if (Array.isArray(value)) {
+                setSelectedTagNames(value);
+              } else {
+                setSelectedTagNames(value ? [value] : []);
+              }
+              // 我们需要将标签名称映射回ID，但由于TagSelector内部已经做了处理
+              // 这里不需要额外的映射代码，selectedTagNames存储名称即可
+            }}
             className="w-[250px]"
             multiple={true}
             teamIds={selectedTeamIds.length > 0 ? selectedTeamIds : undefined}
@@ -345,26 +355,29 @@ export default function HomePage() {
       )}
 
       {/* 显示标签选择信息 */}
-      {selectedTagIds.length > 0 && (
+      {selectedTagNames.length > 0 && (
         <div className="bg-blue-50 rounded-lg p-3 mb-6 flex items-center">
           <span className="text-blue-700 font-medium mr-2">
-            {selectedTagIds.length === 1 ? 'Tag filter:' : 'Tags filter:'}
+            {selectedTagNames.length === 1 ? 'Tag filter:' : 'Tags filter:'}
           </span>
           <div className="flex flex-wrap gap-2">
-            {selectedTagIds.map(tagName => (
+            {selectedTagNames.map(tagName => (
               <span key={tagName} className="bg-blue-100 text-blue-800 text-xs px-2 py-1 rounded-full">
                 {tagName}
                 <button
-                  onClick={() => setSelectedTagIds(selectedTagIds.filter(name => name !== tagName))}
+                  onClick={() => {
+                    // 移除对应的标签名称
+                    setSelectedTagNames(selectedTagNames.filter(name => name !== tagName));
+                  }}
                   className="ml-1 text-blue-600 hover:text-blue-800"
                 >
                   ×
                 </button>
               </span>
             ))}
-            {selectedTagIds.length > 0 && (
+            {selectedTagNames.length > 0 && (
               <button
-                onClick={() => setSelectedTagIds([])}
+                onClick={() => setSelectedTagNames([])}
                 className="text-xs text-gray-500 hover:text-gray-700 underline"
               >
                 Clear all
@@ -423,6 +436,9 @@ export default function HomePage() {
                 <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
                   Original URL
                 </th>
+                <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
+                  Full URL
+                </th>
                 <th scope="col" className="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">
                   Event Type
                 </th>
@@ -462,6 +478,11 @@ export default function HomePage() {
                         {info.originalUrl}
                       </a>
                     </td>
+                    <td className="px-6 py-4 whitespace-nowrap text-sm text-blue-600">
+                      <a href={info.fullUrl} className="hover:underline truncate max-w-xs block" target="_blank" rel="noopener noreferrer">
+                        {info.fullUrl}
+                      </a>
+                    </td>
                     <td className="px-6 py-4 whitespace-nowrap text-sm">
                       <span className={`px-2 inline-flex text-xs leading-5 font-semibold rounded-full ${
                         info.eventType === 'click' 
@@ -762,41 +783,17 @@ export default function HomePage() {
 
         <div className="bg-white rounded-lg shadow p-6 mb-8">
           <h2 className="text-lg font-semibold text-gray-900 mb-4">Geographic Distribution</h2>
-          <GeoAnalytics 
-            data={geoData} 
-            onViewModeChange={(mode) => {
-              // 构建查询参数
-              const params = new URLSearchParams({
-                startTime: format(dateRange.from, "yyyy-MM-dd'T'HH:mm:ss'Z'"),
-                endTime: format(dateRange.to, "yyyy-MM-dd'T'HH:mm:ss'Z'"),
-                groupBy: mode
-              });
-              
-              // 添加其他筛选参数
-              if (selectedTeamIds.length > 0) {
-                selectedTeamIds.forEach(id => params.append('teamId', id));
-              }
-              
-              if (selectedProjectIds.length > 0) {
-                selectedProjectIds.forEach(id => params.append('projectId', id));
-              }
-              
-              if (selectedTagIds.length > 0) {
-                selectedTagIds.forEach(id => params.append('tagId', id));
-              }
-              
-              // 刷新地理位置数据
-              fetch(`/api/events/geo?${params}`)
-                .then(res => res.json())
-                .then(data => {
-                  if (data.success) {
-                    setGeoData(data.data);
-                  }
-                })
-                .catch(error => console.error('Failed to fetch geo data:', error));
-            }}
-          />
+          <GeoAnalytics data={geoData} />
         </div>
+
+        {/* 添加UTM分析组件 */}
+        <UtmAnalytics 
+          startTime={format(dateRange.from, "yyyy-MM-dd'T'HH:mm:ss'Z'")}
+          endTime={format(dateRange.to, "yyyy-MM-dd'T'HH:mm:ss'Z'")}
+          teamIds={selectedTeamIds}
+          projectIds={selectedProjectIds}
+          tagIds={selectedTagNames}
+        />
       </>
     </div>
   );

scripts/db/sql/clickhouse/add_req_full_path.sql

@@ -0,0 +1,9 @@
+-- add_req_full_path.sql
+-- Add req_full_path column to the shorturl_analytics.events table
+ALTER TABLE
+  shorturl_analytics.events
+ADD
+  COLUMN IF NOT EXISTS req_full_path String COMMENT 'Full request path including query parameters';
+
+-- Display the updated table structure
+DESCRIBE TABLE shorturl_analytics.events;

scripts/db/sql/clickhouse/add_utm_fields.sql

@@ -0,0 +1,41 @@
+-- 添加缺失的UTM参数字段到shorturl_analytics.events表
+-- 创建日期: 2024-07-02
+-- 用途: 增强UTM参数追踪能力
+-- 添加utm_term字段 (用于跟踪付费搜索关键词)
+ALTER TABLE
+  shorturl_analytics.events
+ADD
+  COLUMN utm_term String DEFAULT '' AFTER utm_campaign;
+
+-- 添加utm_content字段 (用于区分相同广告的不同版本或A/B测试)
+ALTER TABLE
+  shorturl_analytics.events
+ADD
+  COLUMN utm_content String DEFAULT '' AFTER utm_term;
+
+-- 验证字段添加成功
+DESCRIBE TABLE shorturl_analytics.events;
+
+-- 示例查询: 查看UTM参数分析数据
+SELECT
+  utm_source,
+  utm_medium,
+  utm_campaign,
+  utm_term,
+  utm_content,
+  COUNT(*) as clicks
+FROM
+  shorturl_analytics.events
+WHERE
+  event_type = 'click'
+  AND utm_source != ''
+GROUP BY
+  utm_source,
+  utm_medium,
+  utm_campaign,
+  utm_term,
+  utm_content
+ORDER BY
+  clicks DESC
+LIMIT
+  10;