本文目录导读：
我来为你提供一份关于PHP项目中用户行为追踪与漏斗分析的完整解决方案。
数据采集方案
数据库表结构设计
-- 用户行为日志表
CREATE TABLE `user_behavior_logs` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL COMMENT '用户ID',
`session_id` varchar(64) NOT NULL COMMENT '会话ID',
`event_type` varchar(50) NOT NULL COMMENT '事件类型(visit/click/purchase等)',
`event_name` varchar(100) NOT NULL COMMENT '事件名称',
`event_value` text COMMENT '事件参数(JSON格式)',
`page_url` varchar(500) NOT NULL COMMENT '页面URL',
`referrer_url` varchar(500) DEFAULT NULL COMMENT '来源URL',
`ip` varchar(45) DEFAULT NULL COMMENT 'IP地址',
`user_agent` text COMMENT '浏览器UA',
`device_type` varchar(20) DEFAULT NULL COMMENT '设备类型',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_event_type` (`event_type`),
KEY `idx_created_at` (`created_at`),
KEY `idx_session_id` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 用户会话表（用于追踪完整流程）
CREATE TABLE `user_sessions` (
`id` varchar(64) NOT NULL,
`user_id` int(11) NOT NULL,
`start_time` timestamp NOT NULL,
`end_time` timestamp NULL,
`first_page` varchar(500) DEFAULT NULL,
`last_page` varchar(500) DEFAULT NULL,
`page_views` int(11) DEFAULT 1,
`device_info` text,
`ip` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_start_time` (`start_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
PHP数据采集实现
<?php
class UserBehaviorTracker {
private $db;
private $userId;
private $sessionId;
public function __construct($db) {
$this->db = $db;
$this->userId = $this->getCurrentUserId();
$this->sessionId = $this->getOrCreateSessionId();
}
/**
* 记录用户行为事件
*/
public function track($eventType, $eventName, $eventValue = []) {
$data = [
'user_id' => $this->userId,
'session_id' => $this->sessionId,
'event_type' => $eventType,
'event_name' => $eventName,
'event_value' => json_encode($eventValue, JSON_UNESCAPED_UNICODE),
'page_url' => $_SERVER['REQUEST_URI'] ?? '',
'referrer_url' => $_SERVER['HTTP_REFERER'] ?? '',
'ip' => $this->getClientIp(),
'user_agent' => $_SERVER['HTTP_USER_AGENT'] ?? '',
'device_type' => $this->detectDeviceType($_SERVER['HTTP_USER_AGENT'] ?? ''),
'created_at' => date('Y-m-d H:i:s')
];
$this->saveToDatabase($data);
$this->updateSession();
}
/**
* 记录页面访问
*/
public function trackPageView($pageName = '') {
$this->track('visit', $pageName ?: 'page_view', ['page_name' => $pageName]);
}
/**
* 记录按钮点击
*/
public function trackClick($buttonName, $extraData = []) {
$this->track('click', $buttonName, $extraData);
}
/**
* 记录用户转化
*/
public function trackConversion($conversionType, $value = null) {
$this->track('conversion', $conversionType, ['value' => $value]);
}
private function getCurrentUserId() {
return $_SESSION['user_id'] ?? 0;
}
private function getOrCreateSessionId() {
if (!isset($_SESSION['tracking_session_id'])) {
$_SESSION['tracking_session_id'] = uniqid('sess_', true);
}
return $_SESSION['tracking_session_id'];
}
private function saveToDatabase($data) {
$sql = "INSERT INTO user_behavior_logs
(user_id, session_id, event_type, event_name, event_value,
page_url, referrer_url, ip, user_agent, device_type, created_at)
VALUES
(:user_id, :session_id, :event_type, :event_name, :event_value,
:page_url, :referrer_url, :ip, :user_agent, :device_type, :created_at)";
$stmt = $this->db->prepare($sql);
$stmt->execute($data);
}
private function updateSession() {
// 更新会话信息
$sql = "INSERT INTO user_sessions (id, user_id, start_time, first_page, ip)
VALUES (:id, :user_id, NOW(), :page_url, :ip)
ON DUPLICATE KEY UPDATE
end_time = NOW(),
page_views = page_views + 1,
last_page = :page_url";
$stmt = $this->db->prepare($sql);
$stmt->execute([
'id' => $this->sessionId,
'user_id' => $this->userId,
'page_url' => $_SERVER['REQUEST_URI'] ?? '',
'ip' => $this->getClientIp()
]);
}
private function getClientIp() {
if (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
$ips = explode(',', $_SERVER['HTTP_X_FORWARDED_FOR']);
return trim($ips[0]);
}
return $_SERVER['REMOTE_ADDR'] ?? '0.0.0.0';
}
private function detectDeviceType($userAgent) {
if (preg_match('/Mobile|Android|iPhone|iPad/i', $userAgent)) {
return 'mobile';
} elseif (preg_match('/Tablet|iPad/i', $userAgent)) {
return 'tablet';
}
return 'desktop';
}
}
漏斗分析实现
漏斗模型类
<?php
class FunnelAnalyzer {
private $db;
public function __construct($db) {
$this->db = $db;
}
/**
* 创建漏斗分析
* @param array $steps 步骤定义 [['event_type'=>'visit','event_name'=>'homepage'], ...]
* @param string $startDate 开始日期
* @param string $endDate 结束日期
* @param array $filters 过滤条件
*/
public function analyzeFunnel($steps, $startDate, $endDate, $filters = []) {
$results = [];
$totalUsers = 0;
foreach ($steps as $index => $step) {
$stepData = $this->getStepData($step, $startDate, $endDate, $filters, $index);
$results[] = $stepData;
if ($index == 0) {
$totalUsers = $stepData['unique_users'];
}
}
// 计算漏斗转化率
return $this->calculateConversionRates($results, $totalUsers);
}
private function getStepData($step, $startDate, $endDate, $filters, $stepIndex) {
$sql = "SELECT COUNT(DISTINCT user_id) as unique_users,
COUNT(*) as total_events
FROM user_behavior_logs
WHERE event_type = :event_type
AND event_name = :event_name
AND created_at BETWEEN :start_date AND :end_date";
// 如果是后续步骤，需要限定用户之前已完成前面步骤
if ($stepIndex > 0) {
$sql .= " AND user_id IN (
SELECT DISTINCT user_id FROM user_behavior_logs
WHERE event_type = :prev_event_type
AND event_name = :prev_event_name
AND created_at BETWEEN :start_date AND :end_date
)";
}
// 添加额外过滤条件
$params = [
'event_type' => $step['event_type'],
'event_name' => $step['event_name'],
'start_date' => $startDate,
'end_date' => $endDate
];
if (!empty($filters)) {
$sql .= $this->buildFilterConditions($filters, $params);
}
$stmt = $this->db->prepare($sql);
$stmt->execute($params);
$result = $stmt->fetch(PDO::FETCH_ASSOC);
return [
'step_name' => $step['name'] ?? $step['event_name'],
'event_type' => $step['event_type'],
'event_name' => $step['event_name'],
'unique_users' => (int)$result['unique_users'],
'total_events' => (int)$result['total_events']
];
}
private function calculateConversionRates($steps, $totalUsers) {
$funnelData = [];
foreach ($steps as $index => $step) {
$currentUsers = $step['unique_users'];
// 计算转化率
$overallRate = $totalUsers > 0 ? round(($currentUsers / $totalUsers) * 100, 2) : 0;
$stepRate = $index > 0 ?
($steps[$index - 1]['unique_users'] > 0 ?
round(($currentUsers / $steps[$index - 1]['unique_users']) * 100, 2) : 0) : 100;
$funnelData[] = [
'step' => $step['step_name'],
'users' => $currentUsers,
'events' => $step['total_events'],
'step_conversion_rate' => $stepRate,
'overall_conversion_rate' => $overallRate,
'drop_off' => $index > 0 ? $steps[$index - 1]['unique_users'] - $currentUsers : 0
];
}
return $funnelData;
}
private function buildFilterConditions($filters, &$params) {
$conditions = '';
foreach ($filters as $field => $value) {
if (is_array($value)) {
$placeholders = [];
foreach ($value as $i => $v) {
$key = "filter_{$field}_{$i}";
$placeholders[] = ":$key";
$params[$key] = $v;
}
$conditions .= " AND $field IN (" . implode(',', $placeholders) . ")";
} else {
$key = "filter_{$field}";
$conditions .= " AND $field = :$key";
$params[$key] = $value;
}
}
return $conditions;
}
}
时间序列分析
<?php
class TimeSeriesFunnelAnalyzer extends FunnelAnalyzer {
/**
* 按天分析漏斗趋势
*/
public function analyzeDailyFunnel($steps, $startDate, $endDate, $filters = []) {
$dailyData = [];
$current = strtotime($startDate);
$end = strtotime($endDate);
while ($current <= $end) {
$date = date('Y-m-d', $current);
$dayStart = $date . ' 00:00:00';
$dayEnd = $date . ' 23:59:59';
$funnel = $this->analyzeFunnel($steps, $dayStart, $dayEnd, $filters);
$dailyData[$date] = $funnel;
$current = strtotime('+1 day', $current);
}
return $dailyData;
}
/**
* 获取趋势数据用于图表
*/
public function getTrendData($steps, $startDate, $endDate, $filters = []) {
$dailyFunnel = $this->analyzeDailyFunnel($steps, $startDate, $endDate, $filters);
$trendData = [
'dates' => [],
'steps' => []
];
foreach ($steps as $step) {
$trendData['steps'][$step['name'] ?? $step['event_name']] = [];
}
foreach ($dailyFunnel as $date => $funnel) {
$trendData['dates'][] = $date;
foreach ($funnel as $stepData) {
$stepName = $stepData['step'];
$trendData['steps'][$stepName][] = $stepData['users'];
}
}
return $trendData;
}
}
高级漏斗分析功能
<?php
class AdvancedFunnelAnalyzer extends FunnelAnalyzer {
/**
* 分段分析（按设备、渠道等）
*/
public function segmentFunnel($steps, $startDate, $endDate, $segmentBy = 'device_type') {
$segments = $this->getDistinctValues($segmentBy, $startDate, $endDate);
$segmentedResults = [];
foreach ($segments as $segment) {
$filters = [$segmentBy => $segment];
$funnel = $this->analyzeFunnel($steps, $startDate, $endDate, $filters);
$segmentedResults[$segment] = $funnel;
}
return $segmentedResults;
}
/**
* 用户路径分析
*/
public function getUserPathAnalysis($userId, $limit = 20) {
$sql = "SELECT event_type, event_name, page_url, created_at
FROM user_behavior_logs
WHERE user_id = :user_id
ORDER BY created_at
LIMIT :limit";
$stmt = $this->db->prepare($sql);
$stmt->execute(['user_id' => $userId, 'limit' => $limit]);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
/**
* 流失用户分析
*/
public function analyzeDropOffUsers($steps, $startDate, $endDate) {
$droppedAtStep = [];
foreach ($steps as $index => $step) {
if ($index > 0) {
$sql = "SELECT DISTINCT l1.user_id
FROM user_behavior_logs l1
WHERE l1.event_type = :prev_type
AND l1.event_name = :prev_name
AND l1.created_at BETWEEN :start AND :end
AND NOT EXISTS (
SELECT 1 FROM user_behavior_logs l2
WHERE l2.user_id = l1.user_id
AND l2.event_type = :curr_type
AND l2.event_name = :curr_name
AND l2.created_at BETWEEN :start AND :end
)";
$stmt = $this->db->prepare($sql);
$stmt->execute([
'prev_type' => $steps[$index - 1]['event_type'],
'prev_name' => $steps[$index - 1]['event_name'],
'curr_type' => $step['event_type'],
'curr_name' => $step['event_name'],
'start' => $startDate,
'end' => $endDate
]);
$droppedAtStep[$step['name'] ?? $step['event_name']] = [
'count' => $stmt->rowCount(),
'user_ids' => $stmt->fetchAll(PDO::FETCH_COLUMN)
];
}
}
return $droppedAtStep;
}
private function getDistinctValues($field, $startDate, $endDate) {
$sql = "SELECT DISTINCT $field FROM user_behavior_logs
WHERE $field IS NOT NULL
AND created_at BETWEEN :start AND :end";
$stmt = $this->db->prepare($sql);
$stmt->execute(['start' => $startDate, 'end' => $endDate]);
return $stmt->fetchAll(PDO::FETCH_COLUMN);
}
}
前端集成与展示
JavaScript跟踪代码
// tracking.js
class UserBehaviorTracker {
constructor(config) {
this.config = {
trackPageViews: true,
trackClicks: true,
trackFormSubmissions: true,
scrollTracking: false,
serverUrl: '/api/track',
...config
};
this.init();
}
init() {
if (this.config.trackPageViews) {
this.trackPageView();
}
if (this.config.trackClicks) {
this.bindClickTracking();
}
if (this.config.trackFormSubmissions) {
this.bindFormTracking();
}
if (this.config.scrollTracking) {
this.initScrollTracking();
}
}
trackPageView() {
this.sendEvent('visit', 'page_view', {
page: window.location.pathname,
title: document.title,
referrer: document.referrer
});
}
trackEvent(eventType, eventName, data = {}) {
this.sendEvent(eventType, eventName, data);
}
bindClickTracking() {
document.addEventListener('click', (e) => {
const target = e.target;
const dataTrack = target.getAttribute('data-track');
const dataTrackValue = target.getAttribute('data-track-value');
if (dataTrack) {
this.sendEvent('click', dataTrack, {
value: dataTrackValue || target.innerText || target.href,
element: target.tagName,
id: target.id,
className: target.className
});
}
}, true);
}
bindFormTracking() {
document.addEventListener('submit', (e) => {
const form = e.target;
const formName = form.getAttribute('name') || form.id || 'unknown_form';
this.sendEvent('form_submit', formName, {
fields: this.getFormFields(form)
});
});
}
initScrollTracking() {
let scrollTimeout;
window.addEventListener('scroll', () => {
clearTimeout(scrollTimeout);
scrollTimeout = setTimeout(() => {
const scrollPercent = Math.round(
(window.scrollY + window.innerHeight) / document.documentElement.scrollHeight * 100
);
if (scrollPercent >= 25 && scrollPercent < 50) {
this.sendEvent('scroll', '25_percent');
} else if (scrollPercent >= 50 && scrollPercent < 75) {
this.sendEvent('scroll', '50_percent');
} else if (scrollPercent >= 75 && scrollPercent < 100) {
this.sendEvent('scroll', '75_percent');
} else if (scrollPercent >= 100) {
this.sendEvent('scroll', '100_percent');
}
}, 500);
});
}
sendEvent(eventType, eventName, data = {}) {
const payload = {
event_type: eventType,
event_name: eventName,
event_value: data,
timestamp: new Date().toISOString(),
page_url: window.location.href,
user_agent: navigator.userAgent
};
// 使用Beacon API或fetch发送数据
if (navigator.sendBeacon) {
navigator.sendBeacon(this.config.serverUrl, JSON.stringify(payload));
} else {
fetch(this.config.serverUrl, {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify(payload)
});
}
}
getFormFields(form) {
const fields = {};
const formData = new FormData(form);
for (let [key, value] of formData.entries()) {
fields[key] = value;
}
return fields;
}
}
// 使用示例
const tracker = new UserBehaviorTracker({
trackPageViews: true,
trackClicks: true,
trackFormSubmissions: true,
scrollTracking: true,
serverUrl: '/api/track-event'
});
PHP后端接收接口
<?php
// api/track-event.php
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../includes/UserBehaviorTracker.php';
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: POST, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type');
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
http_response_code(200);
exit;
}
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
http_response_code(405);
echo json_encode(['error' => 'Method not allowed']);
exit;
}
try {
$input = json_decode(file_get_contents('php://input'), true);
if (!$input || !isset($input['event_type']) || !isset($input['event_name'])) {
http_response_code(400);
echo json_encode(['error' => 'Invalid input']);
exit;
}
$db = Database::getConnection();
$tracker = new UserBehaviorTracker($db);
$tracker->track(
$input['event_type'],
$input['event_name'],
$input['event_value'] ?? []
);
http_response_code(200);
echo json_encode(['success' => true]);
} catch (Exception $e) {
http_response_code(500);
echo json_encode(['error' => $e->getMessage()]);
}
数据报表与API
漏斗分析API
<?php
// api/funnel-analysis.php
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../analytics/FunnelAnalyzer.php';
require_once __DIR__ . '/../analytics/AdvancedFunnelAnalyzer.php';
class FunnelAPI {
private $db;
private $analyzer;
public function __construct() {
$this->db = Database::getConnection();
}
public function handleRequest() {
$action = $_GET['action'] ?? 'funnel';
switch ($action) {
case 'funnel':
$this->getFunnelData();
break;
case 'trend':
$this->getTrendData();
break;
case 'segment':
$this->getSegmentedData();
break;
case 'dropoff':
$this->getDropOffUsers();
break;
default:
$this->response(['error' => 'Invalid action'], 400);
}
}
private function getFunnelData() {
$steps = $this->getRequestSteps();
$startDate = $_GET['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
$endDate = $_GET['end_date'] ?? date('Y-m-d');
$analyzer = new FunnelAnalyzer($this->db);
$data = $analyzer->analyzeFunnel($steps, $startDate, $endDate);
$this->response([
'success' => true,
'data' => $data,
'metadata' => [
'start_date' => $startDate,
'end_date' => $endDate,
'total_steps' => count($steps)
]
]);
}
private function getTrendData() {
$steps = $this->getRequestSteps();
$startDate = $_GET['start_date'] ?? date('Y-m-d', strtotime('-7 days'));
$endDate = $_GET['end_date'] ?? date('Y-m-d');
$analyzer = new TimeSeriesFunnelAnalyzer($this->db);
$data = $analyzer->getTrendData($steps, $startDate, $endDate);
$this->response([
'success' => true,
'data' => $data
]);
}
private function getSegmentedData() {
$steps = $this->getRequestSteps();
$startDate = $_GET['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
$endDate = $_GET['end_date'] ?? date('Y-m-d');
$segmentBy = $_GET['segment_by'] ?? 'device_type';
$analyzer = new AdvancedFunnelAnalyzer($this->db);
$data = $analyzer->segmentFunnel($steps, $startDate, $endDate, $segmentBy);
$this->response([
'success' => true,
'data' => $data,
'segment_by' => $segmentBy
]);
}
private function getDropOffUsers() {
$steps = $this->getRequestSteps();
$startDate = $_GET['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
$endDate = $_GET['end_date'] ?? date('Y-m-d');
$analyzer = new AdvancedFunnelAnalyzer($this->db);
$data = $analyzer->analyzeDropOffUsers($steps, $startDate, $endDate);
$this->response([
'success' => true,
'data' => $data
]);
}
private function getRequestSteps() {
$stepsJson = $_GET['steps'] ?? '[]';
$steps = json_decode($stepsJson, true);
if (empty($steps)) {
// 默认漏斗步骤
$steps = [
['event_type' => 'visit', 'event_name' => 'page_view', 'name' => '访问首页'],
['event_type' => 'visit', 'event_name' => 'product_page', 'name' => '浏览商品'],
['event_type' => 'click', 'event_name' => 'add_to_cart', 'name' => '加入购物车'],
['event_type' => 'click', 'event_name' => 'checkout', 'name' => '开始结算'],
['event_type' => 'conversion', 'event_name' => 'purchase', 'name' => '完成购买']
];
}
return $steps;
}
private function response($data, $statusCode = 200) {
http_response_code($statusCode);
echo json_encode($data, JSON_UNESCAPED_UNICODE);
exit;
}
}
// 处理请求
$api = new FunnelAPI();
$api->handleRequest();
使用示例
在前端页面中使用
<!-- 在页面中添加跟踪代码 -->
<script src="/js/tracking.js"></script>
<script>
// 初始化跟踪器
const tracker = new UserBehaviorTracker({
trackPageViews: true,
trackClicks: true,
trackFormSubmissions: true,
serverUrl: '/api/track-event'
});
// 手动跟踪特定事件
function trackAddToCart(productId, productName) {
tracker.trackEvent('click', 'add_to_cart', {
product_id: productId,
product_name: productName
});
}
function trackPurchase(orderId, total) {
tracker.trackEvent('conversion', 'purchase', {
order_id: orderId,
total: total
});
}
</script>
<!-- 使用data-track属性自动跟踪点击 -->
<button data-track="cta_button" data-track-value="立即购买">
立即购买
</button>
<a href="/product/123" data-track="product_click" data-track-value="商品123">
查看商品
</a>
PHP后端调用
<?php
// 在控制器中使用
class OrderController {
public function placeOrder() {
// 处理订单逻辑
// 跟踪购买事件
$tracker = new UserBehaviorTracker($db);
$tracker->trackConversion('purchase', [
'order_id' => $orderId,
'total' => $totalAmount,
'product_count' => $productCount
]);
// 重定向到成功页面
header('Location: /order/success/' . $orderId);
}
}
前端调用API获取漏斗数据
// 获取漏斗数据并渲染图表
async function renderFunnelChart() {
const steps = [
{ event_type: 'visit', event_name: 'page_view', name: '访问首页' },
{ event_type: 'visit', event_name: 'product_page', name: '浏览商品' },
{ event_type: 'click', event_name: 'add_to_cart', name: '加入购物车' },
{ event_type: 'click', event_name: 'checkout', name: '开始结算' },
{ event_type: 'conversion', event_name: 'purchase', name: '完成购买' }
];
const response = await fetch(`/api/funnel-analysis.php?action=funnel&steps=${JSON.stringify(steps)}&start_date=2024-01-01&end_date=2024-01-31`);
const result = await response.json();
if (result.success) {
createFunnelChart(result.data);
}
}
function createFunnelChart(funnelData) {
// 使用Chart.js或其他图表库渲染漏斗图
const ctx = document.getElementById('funnelChart').getContext('2d');
new Chart(ctx, {
type: 'bar',
data: {
labels: funnelData.map(d => d.step),
datasets: [{
label: '用户数',
data: funnelData.map(d => d.users),
backgroundColor: [
'rgba(54, 162, 235, 0.8)',
'rgba(75, 192, 192, 0.8)',
'rgba(255, 206, 86, 0.8)',
'rgba(255, 159, 64, 0.8)',
'rgba(255, 99, 132, 0.8)'
],
borderWidth: 1
}]
},
options: {
indexAxis: 'y',
plugins: {
tooltip: {
callbacks: {
afterLabel: function(context) {
const data = funnelData[context.dataIndex];
return [
`转化率: ${data.step_conversion_rate}%`,
`整体转化率: ${data.overall_conversion_rate}%`,
`流失用户: ${data.drop_off}`
];
}
}
}
},
scales: {
x: {
beginAtZero: true
}
}
}
});
}
这个完整的PHP用户行为与漏斗分析系统包含了数据采集、存储、分析、API和前端展示的完整解决方案,你可以根据实际需求进行调整和扩展。
