🎄 科学と神々株式会社 アドベントカレンダー 2025
Hybrid License System Day 18: 統計・分析機能
Admin Service編 (3/5)
📖 はじめに
Day 18では、統計・分析機能を学びます。リアルタイム統計、アクティブユーザー数、ライセンス使用状況、グラフデータ生成を実装しましょう。
📊 分析機能の設計
提供する統計情報
1. 概要統計
- 総ユーザー数
- 総ライセンス数
- アクティブライセンス数
- プラン別ユーザー分布
2. 時系列データ
- 日別ユーザー登録数
- 月別ライセンス発行数
- プラン変更履歴
3. 収益分析
- プラン別収益
- MRR (Monthly Recurring Revenue)
- 解約率 (Churn Rate)
4. ユーザー行動分析
- アクティブユーザー数 (DAU/MAU)
- ライセンス使用率
- エンゲージメント指標
🎯 概要統計API
Overview Statistics
// admin-service/src/controllers/analyticsController.js
class AnalyticsController {
constructor(db) {
this.db = db;
}
/**
* ダッシュボード概要統計
*/
async getOverview(req, res) {
try {
// 単一クエリで複数集計（効率的）
const overview = this.db.prepare(`
SELECT
COUNT(DISTINCT u.user_id) as total_users,
COUNT(DISTINCT l.license_id) as total_licenses,
SUM(CASE WHEN l.status = 'active' THEN 1 ELSE 0 END) as active_licenses,
SUM(CASE WHEN l.status = 'expired' THEN 1 ELSE 0 END) as expired_licenses,
SUM(CASE WHEN l.status = 'revoked' THEN 1 ELSE 0 END) as revoked_licenses,
SUM(CASE WHEN u.plan = 'free' THEN 1 ELSE 0 END) as free_users,
SUM(CASE WHEN u.plan = 'professional' THEN 1 ELSE 0 END) as professional_users,
SUM(CASE WHEN u.plan = 'enterprise' THEN 1 ELSE 0 END) as enterprise_users
FROM users u
LEFT JOIN licenses l ON u.user_id = l.user_id
`).get();
// 今日の新規ユーザー数
const today = new Date().toISOString().split('T')[0];
const newUsersToday = this.db.prepare(`
SELECT COUNT(*) as count
FROM users
WHERE DATE(created_at) = ?
`).get(today);
// 今週のライセンス発行数
const weekAgo = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000).toISOString();
const newLicensesThisWeek = this.db.prepare(`
SELECT COUNT(*) as count
FROM licenses
WHERE created_at >= ?
`).get(weekAgo);
// レスポンス
res.json({
success: true,
data: {
overview: {
totalUsers: overview.total_users || 0,
totalLicenses: overview.total_licenses || 0,
activeLicenses: overview.active_licenses || 0,
expiredLicenses: overview.expired_licenses || 0,
revokedLicenses: overview.revoked_licenses || 0
},
planDistribution: {
free: overview.free_users || 0,
professional: overview.professional_users || 0,
enterprise: overview.enterprise_users || 0
},
recentActivity: {
newUsersToday: newUsersToday.count || 0,
newLicensesThisWeek: newLicensesThisWeek.count || 0
},
timestamp: new Date().toISOString()
}
});
} catch (error) {
console.error('Get overview error:', error);
res.status(500).json({
success: false,
error: 'Internal Server Error'
});
}
}
}
module.exports = AnalyticsController;
📈 時系列データAPI
ユーザー登録トレンド
class AnalyticsController {
/**
* ユーザー登録トレンド（日別/月別）
*/
async getUserTrend(req, res) {
try {
const { period = 'daily', range = 30 } = req.query;
let groupBy, dateFormat;
if (period === 'daily') {
groupBy = "DATE(created_at)";
dateFormat = '%Y-%m-%d';
} else if (period === 'monthly') {
groupBy = "strftime('%Y-%m', created_at)";
dateFormat = '%Y-%m';
} else {
return res.status(400).json({
success: false,
error: 'Invalid period. Use daily or monthly'
});
}
// 期間計算
const endDate = new Date();
const startDate = new Date(endDate);
startDate.setDate(startDate.getDate() - parseInt(range));
// トレンドデータ取得
const trend = this.db.prepare(`
SELECT
${groupBy} as date,
COUNT(*) as count,
SUM(CASE WHEN plan = 'free' THEN 1 ELSE 0 END) as free_count,
SUM(CASE WHEN plan = 'professional' THEN 1 ELSE 0 END) as pro_count,
SUM(CASE WHEN plan = 'enterprise' THEN 1 ELSE 0 END) as enterprise_count
FROM users
WHERE created_at >= ? AND created_at <= ?
GROUP BY ${groupBy}
ORDER BY date ASC
`).all(startDate.toISOString(), endDate.toISOString());
res.json({
success: true,
data: {
period,
range: parseInt(range),
trend
}
});
} catch (error) {
console.error('Get user trend error:', error);
res.status(500).json({
success: false,
error: 'Internal Server Error'
});
}
}
/**
* ライセンス発行トレンド
*/
async getLicenseTrend(req, res) {
try {
const { period = 'daily', range = 30 } = req.query;
let groupBy;
if (period === 'daily') {
groupBy = "DATE(created_at)";
} else if (period === 'monthly') {
groupBy = "strftime('%Y-%m', created_at)";
} else {
return res.status(400).json({
success: false,
error: 'Invalid period'
});
}
const endDate = new Date();
const startDate = new Date(endDate);
startDate.setDate(startDate.getDate() - parseInt(range));
const trend = this.db.prepare(`
SELECT
${groupBy} as date,
COUNT(*) as total_issued,
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active,
SUM(CASE WHEN status = 'expired' THEN 1 ELSE 0 END) as expired,
SUM(CASE WHEN status = 'revoked' THEN 1 ELSE 0 END) as revoked
FROM licenses
WHERE created_at >= ? AND created_at <= ?
GROUP BY ${groupBy}
ORDER BY date ASC
`).all(startDate.toISOString(), endDate.toISOString());
res.json({
success: true,
data: {
period,
range: parseInt(range),
trend
}
});
} catch (error) {
console.error('Get license trend error:', error);
res.status(500).json({
success: false,
error: 'Internal Server Error'
});
}
}
}
💰 収益分析API
Revenue Analytics
class AnalyticsController {
/**
* プラン別収益分析
*/
async getRevenueAnalytics(req, res) {
try {
// プラン別価格設定
const planPricing = {
free: 0,
professional: 29.99,
enterprise: 99.99
};
// アクティブユーザー別収益計算
const revenueByPlan = this.db.prepare(`
SELECT
u.plan,
COUNT(DISTINCT u.user_id) as user_count,
COUNT(DISTINCT l.license_id) as license_count
FROM users u
LEFT JOIN licenses l ON u.user_id = l.user_id AND l.status = 'active'
GROUP BY u.plan
`).all();
// 収益計算
const revenue = revenueByPlan.map(item => ({
plan: item.plan,
users: item.user_count,
licenses: item.license_count,
pricePerUser: planPricing[item.plan] || 0,
totalRevenue: (planPricing[item.plan] || 0) * item.user_count
}));
// MRR (Monthly Recurring Revenue) 計算
const mrr = revenue.reduce((sum, item) => sum + item.totalRevenue, 0);
// ARR (Annual Recurring Revenue)
const arr = mrr * 12;
// 月別収益トレンド（過去6ヶ月）
const sixMonthsAgo = new Date();
sixMonthsAgo.setMonth(sixMonthsAgo.getMonth() - 6);
const monthlyRevenue = this.db.prepare(`
SELECT
strftime('%Y-%m', created_at) as month,
plan,
COUNT(*) as new_users
FROM users
WHERE created_at >= ?
GROUP BY month, plan
ORDER BY month ASC
`).all(sixMonthsAgo.toISOString());
res.json({
success: true,
data: {
currentRevenue: revenue,
metrics: {
mrr: parseFloat(mrr.toFixed(2)),
arr: parseFloat(arr.toFixed(2)),
averageRevenuePerUser: revenue.length > 0
? parseFloat((mrr / revenue.reduce((sum, r) => sum + r.users, 0)).toFixed(2))
: 0
},
monthlyTrend: monthlyRevenue
}
});
} catch (error) {
console.error('Get revenue analytics error:', error);
res.status(500).json({
success: false,
error: 'Internal Server Error'
});
}
}
/**
* 解約率分析 (Churn Rate)
*/
async getChurnRate(req, res) {
try {
// 今月の開始時点のユーザー数
const startOfMonth = new Date();
startOfMonth.setDate(1);
startOfMonth.setHours(0, 0, 0, 0);
const usersAtStart = this.db.prepare(`
SELECT COUNT(*) as count
FROM users
WHERE created_at < ?
`).get(startOfMonth.toISOString());
// 今月の解約ライセンス数（revokedステータス）
const churned = this.db.prepare(`
SELECT COUNT(*) as count
FROM licenses
WHERE status = 'revoked'
AND updated_at >= ?
`).get(startOfMonth.toISOString());
// チャーンレート計算
const churnRate = usersAtStart.count > 0
? ((churned.count / usersAtStart.count) * 100).toFixed(2)
: 0;
res.json({
success: true,
data: {
month: startOfMonth.toISOString().substring(0, 7),
usersAtStartOfMonth: usersAtStart.count,
churnedUsers: churned.count,
churnRate: parseFloat(churnRate),
retentionRate: parseFloat((100 - churnRate).toFixed(2))
}
});
} catch (error) {
console.error('Get churn rate error:', error);
res.status(500).json({
success: false,
error: 'Internal Server Error'
});
}
}
}
📊 グラフデータ生成
Chart Data Format
class AnalyticsController {
/**
* グラフ用データ生成（Chart.js形式）
*/
async getChartData(req, res) {
try {
const { type, range = 30 } = req.query;
if (type === 'user-growth') {
const data = await this.getUserGrowthChartData(range);
return res.json({ success: true, data });
}
if (type === 'license-status') {
const data = await this.getLicenseStatusChartData();
return res.json({ success: true, data });
}
if (type === 'plan-distribution') {
const data = await this.getPlanDistributionChartData();
return res.json({ success: true, data });
}
res.status(400).json({
success: false,
error: 'Invalid chart type'
});
} catch (error) {
console.error('Get chart data error:', error);
res.status(500).json({
success: false,
error: 'Internal Server Error'
});
}
}
/**
* ユーザー成長グラフデータ
*/
async getUserGrowthChartData(range) {
const endDate = new Date();
const startDate = new Date(endDate);
startDate.setDate(startDate.getDate() - parseInt(range));
const data = this.db.prepare(`
SELECT
DATE(created_at) as date,
COUNT(*) as count
FROM users
WHERE created_at >= ? AND created_at <= ?
GROUP BY DATE(created_at)
ORDER BY date ASC
`).all(startDate.toISOString(), endDate.toISOString());
// Chart.js形式に変換
return {
labels: data.map(d => d.date),
datasets: [{
label: 'New Users',
data: data.map(d => d.count),
borderColor: 'rgb(75, 192, 192)',
tension: 0.1
}]
};
}
/**
* ライセンスステータス円グラフデータ
*/
async getLicenseStatusChartData() {
const data = this.db.prepare(`
SELECT
status,
COUNT(*) as count
FROM licenses
GROUP BY status
`).all();
return {
labels: data.map(d => d.status),
datasets: [{
data: data.map(d => d.count),
backgroundColor: [
'rgb(75, 192, 192)', // active
'rgb(255, 205, 86)', // expired
'rgb(255, 99, 132)' // revoked
]
}]
};
}
/**
* プラン分布グラフデータ
*/
async getPlanDistributionChartData() {
const data = this.db.prepare(`
SELECT
plan,
COUNT(*) as count
FROM users
GROUP BY plan
`).all();
return {
labels: data.map(d => d.plan),
datasets: [{
label: 'Users by Plan',
data: data.map(d => d.count),
backgroundColor: [
'rgb(54, 162, 235)', // free
'rgb(255, 159, 64)', // professional
'rgb(153, 102, 255)' // enterprise
]
}]
};
}
}
🎯 次のステップ
Day 19では、監査ログシステムを学びます。監査ログの設計、重要操作の記録、ログ検索機能、セキュリティ監視について詳しく解説します。
🔗 関連リンク
次回予告: Day 19では、イベント記録パターンとログ検索・フィルタリングを詳しく解説します！
Copyright © 2025 Gods & Golem, Inc. All rights reserved.