안녕하세요.
셀렉트어드민의 여러 기능을 활용하여 아래와 같은 대시보드를 만드실 수 있습니다.
관련 도움이 필요하시거나 원하시는 템플릿이 있다면 문의해주세요. 감사합니다.
menus:
- path: pages/ecommerce-dashboard
name: 이커머스 대시보드
pages:
- path: pages/ecommerce-dashboard
layout: dashboard
title: 이커머스 판매 성과 대시보드
subtitle: 매출, 상품별 판매량, 광고 ROI, 재고 현황을 한눈에 확인하세요
params:
# 1. 분석 기간 (범위 날짜)
- key: date_range
label: 분석 기간
format: date
range: true
defaultValueFn: |
return [
moment().subtract(3, 'months').format('YYYY-MM-DD'),
moment().format('YYYY-MM-DD')
]
# 2. 상품 카테고리 (드롭다운)
- key: category
label: 상품 카테고리
defaultValue: all
dropdown:
- value: 'all'
label: 전체 카테고리
- value: '전자제품'
label: 전자제품
- value: '의류'
label: 의류
- value: '신발'
label: 신발
- value: '화장품'
label: 화장품
query:
'all': '1=1'
'전자제품': "p.category = '전자제품'"
'의류': "p.category = '의류'"
'신발': "p.category = '신발'"
'화장품': "p.category = '화장품'"
# 3. 주문 상태 (드롭다운)
- key: order_status
label: 주문 상태
defaultValue: all_status
dropdown:
- value: 'delivered_shipped'
label: 배송완료+배송중
- value: 'delivered'
label: 배송완료만
- value: 'all_status'
label: 모든 상태
query:
'delivered_shipped': "o.order_status IN ('delivered', 'shipped')"
'delivered': "o.order_status = 'delivered'"
'all_status': '1=1'
# 4. 브랜드 (드롭다운)
- key: brand
label: 브랜드
defaultValue: all
dropdown:
- value: 'all'
label: 전체 브랜드
- value: '애플'
label: 애플
- value: '삼성'
label: 삼성
- value: '나이키'
label: 나이키
- value: '아디다스'
label: 아디다스
query:
'all': '1=1'
'애플': "p.brand = '애플'"
'삼성': "p.brand = '삼성'"
'나이키': "p.brand = '나이키'"
'아디다스': "p.brand = '아디다스'"
# 5. 최소 주문금액 (입력 필드)
- key: min_amount
label: 최소 주문금액
format: number
defaultValue: 0
blocks:
# 1. 월별 매출 및 주문 건수 - (주요 KPI용) 가로 전체
- id: monthly-revenue
style:
minWidth: 100%
minHeight: 380px
type: query
resource: mysql.qa
sqlType: select
sql: >
SELECT
DATE_FORMAT(o.order_date, '%Y-%m') as month,
COUNT(*) as order_count,
SUM(o.total_amount) as total_revenue,
ROUND(AVG(o.total_amount), 0) as avg_order_value
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= :date_range1
AND o.order_date <= :date_range2
AND {{ query }}
AND o.total_amount >= :min_amount
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY month
display: chartjs
displayFn: |
const labels = rows.map(row => row.month);
const revenueData = rows.map(row => row.total_revenue);
const orderData = rows.map(row => row.order_count);
const data = {
labels: labels,
datasets: [{
type: 'bar',
label: '매출 (원)',
data: revenueData,
backgroundColor: 'rgba(54, 162, 235, 0.6)',
borderColor: 'rgba(54, 162, 235, 1)',
yAxisID: 'y'
}, {
type: 'line',
label: '주문 건수',
data: orderData,
backgroundColor: 'rgba(255, 99, 132, 0.6)',
borderColor: 'rgba(255, 99, 132, 1)',
yAxisID: 'y1'
}]
};
const config = {
data: data,
options: {
responsive: true,
maintainAspectRatio: false,
scales: {
y: {
type: 'linear',
display: true,
position: 'left',
title: { display: true, text: '매출 (원)' }
},
y1: {
type: 'linear',
display: true,
position: 'right',
title: { display: true, text: '주문 건수' },
grid: { drawOnChartArea: false }
}
},
plugins: {
title: { display: true, text: '월별 매출 및 주문 건수 추이' }
}
}
};
const myChart = new Chart(ctx, config);
# 2~4. 3열 카드 스타일 (Top 상품, 광고 ROI, 카테고리 매출 비중)
- id: top-products
style:
minWidth: calc(33.33% - 1rem)
minHeight: 300px
type: query
resource: mysql.qa
sqlType: select
sql: >
SELECT
p.product_name,
p.category,
p.brand,
SUM(oi.quantity) as total_quantity,
SUM(oi.total_price) as total_revenue,
COUNT(DISTINCT oi.order_id) as order_count
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= :date_range1
AND o.order_date <= :date_range2
AND {{ query }}
AND o.total_amount >= :min_amount
GROUP BY p.product_id, p.product_name, p.category, p.brand
ORDER BY total_quantity DESC
LIMIT 8
display: chartjs
displayFn: |
const labels = rows.map(row => row.product_name.length > 12 ? row.product_name.substring(0, 12) + '...' : row.product_name);
const quantityData = rows.map(row => row.total_quantity);
const categoryColors = {
'전자제품': 'rgba(54, 162, 235, 0.8)',
'신발': 'rgba(255, 99, 132, 0.8)',
'의류': 'rgba(75, 192, 192, 0.8)'
};
const backgroundColors = rows.map(row => categoryColors[row.category] || 'rgba(153, 102, 255, 0.8)');
const data = {
labels: labels,
datasets: [{
label: '판매량 (개)',
data: quantityData,
backgroundColor: backgroundColors,
borderWidth: 1
}]
};
const config = {
type: 'bar',
data: data,
options: {
responsive: true,
maintainAspectRatio: false,
plugins: {
title: { display: true, text: '상품별 판매량 Top 8' },
legend: { display: false }
},
scales: {
y: { title: { display: true, text: '판매량 (개)' } },
x: {
ticks: {
maxRotation: 45,
minRotation: 45,
font: { size: 10 }
}
}
}
}
};
const myChart = new Chart(ctx, config);
- id: ad-roi
style:
minWidth: calc(33.33% - 1rem)
minHeight: 300px
type: query
resource: mysql.qa
sqlType: select
sql: >
SELECT
ac.campaign_name,
ac.campaign_type,
SUM(ap.ad_spend) as total_spend,
SUM(ap.revenue) as total_revenue,
SUM(ap.conversions) as total_conversions,
ROUND((SUM(ap.revenue) - SUM(ap.ad_spend)) / SUM(ap.ad_spend) * 100, 2) as roi_percentage
FROM ad_campaigns ac
JOIN ad_performance ap ON ac.campaign_id = ap.campaign_id
WHERE ap.performance_date >= :date_range1
AND ap.performance_date <= :date_range2
GROUP BY ac.campaign_id, ac.campaign_name, ac.campaign_type
ORDER BY roi_percentage DESC
LIMIT 6
display: chartjs
displayFn: |
const labels = rows.map(row => row.campaign_name.length > 10 ? row.campaign_name.substring(0, 10) + '...' : row.campaign_name);
const roiData = rows.map(row => row.roi_percentage);
// 타입별 색상 정의
const campaignTypeColors = {
'search': 'rgba(255, 99, 132, 0.8)',
'display': 'rgba(54, 162, 235, 0.8)',
'social': 'rgba(75, 192, 192, 0.8)',
'video': 'rgba(153, 102, 255, 0.8)'
};
const backgroundColors = rows.map(row =>
campaignTypeColors[row.campaign_type] || 'rgba(201, 203, 207, 0.8)'
);
const data = {
labels: labels,
datasets: [{
label: 'ROI (%)',
data: roiData,
backgroundColor: backgroundColors,
borderColor: backgroundColors.map(color => color.replace('0.8', '1')),
borderWidth: 1
}]
};
const config = {
type: 'bar',
data: data,
options: {
responsive: true,
maintainAspectRatio: false,
plugins: {
title: {
display: true,
text: '광고 캠페인 ROI Top 6'
},
tooltip: {
callbacks: {
afterLabel: function(context) {
const index = context.dataIndex;
const spend = rows[index].total_spend;
const revenue = rows[index].total_revenue;
const conversions = rows[index].total_conversions;
const type = rows[index].campaign_type;
return [
`타입: ${type}`,
`광고비: ${spend.toLocaleString()}원`,
`매출: ${revenue.toLocaleString()}원`,
`전환수: ${conversions.toLocaleString()}개`
];
}
}
},
legend: { display: false }
},
scales: {
y: {
title: { display: true, text: 'ROI (%)' },
beginAtZero: true
},
x: {
ticks: {
maxRotation: 45,
minRotation: 45,
font: { size: 10 }
}
}
}
}
};
const myChart = new Chart(ctx, config);
- id: category-revenue
style:
minWidth: calc(33.33% - 1rem)
minHeight: 300px
type: query
resource: mysql.qa
sqlType: select
sql: >
SELECT
p.category,
SUM(oi.total_price) as total_revenue,
ROUND(
SUM(oi.total_price) * 100.0 /
SUM(SUM(oi.total_price)) OVER (),
1
) as revenue_percentage
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= :date_range1
AND o.order_date <= :date_range2
AND {{ query }}
AND o.total_amount >= :min_amount
GROUP BY p.category
ORDER BY total_revenue DESC
display: chartjs
displayFn: |
const labels = rows.map(row => row.category);
const revenueData = rows.map(row => row.total_revenue);
const percentages = rows.map(row => row.revenue_percentage);
const data = {
labels: labels,
datasets: [{
data: revenueData,
backgroundColor: [
'rgba(255, 99, 132, 0.8)',
'rgba(54, 162, 235, 0.8)',
'rgba(255, 206, 86, 0.8)',
'rgba(75, 192, 192, 0.8)',
'rgba(153, 102, 255, 0.8)'
],
borderWidth: 2
}]
};
const config = {
type: 'doughnut',
data: data,
options: {
responsive: true,
maintainAspectRatio: false,
plugins: {
title: { display: true, text: '카테고리별 매출 구성' },
tooltip: {
callbacks: {
label: function(context) {
const label = context.label;
const value = context.parsed;
const percentage = percentages[context.dataIndex];
return `${label}: ${percentage}%`;
}
}
}
}
}
};
const myChart = new Chart(ctx, config);
# 5~6. 비교 중심 하단 2열 (일별 추이, 재고 현황)
- id: daily-trend
style:
minWidth: calc(50% - 1rem)
minHeight: 340px
type: query
resource: mysql.qa
sqlType: select
sql: >
SELECT
o.order_date,
COUNT(*) as daily_orders,
SUM(o.total_amount) as daily_revenue,
ROUND(AVG(o.total_amount), 0) as avg_order_value
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= :date_range1
AND o.order_date <= :date_range2
AND {{ query }}
AND o.total_amount >= :min_amount
GROUP BY o.order_date
ORDER BY o.order_date
display: chartjs
displayFn: |
const labels = rows.map(row => {
const date = new Date(row.order_date);
return (date.getMonth() + 1) + '/' + date.getDate();
});
const revenueData = rows.map(row => row.daily_revenue);
const orderData = rows.map(row => row.daily_orders);
const data = {
labels: labels,
datasets: [{
label: '일별 매출',
data: revenueData,
borderColor: 'rgba(54, 162, 235, 1)',
backgroundColor: 'rgba(54, 162, 235, 0.1)',
tension: 0.4,
yAxisID: 'y'
}, {
label: '일별 주문수',
data: orderData,
borderColor: 'rgba(255, 99, 132, 1)',
backgroundColor: 'rgba(255, 99, 132, 0.1)',
tension: 0.4,
yAxisID: 'y1'
}]
};
const config = {
type: 'line',
data: data,
options: {
responsive: true,
maintainAspectRatio: false,
scales: {
y: {
type: 'linear',
display: true,
position: 'left',
title: { display: true, text: '매출 (원)' }
},
y1: {
type: 'linear',
display: true,
position: 'right',
title: { display: true, text: '주문 수' },
grid: { drawOnChartArea: false }
}
},
plugins: {
title: { display: true, text: '최근 30일 일별 매출 및 주문 트렌드' }
}
}
};
const myChart = new Chart(ctx, config);
- id: inventory-status
style:
minWidth: calc(50% - 1rem)
minHeight: 340px
type: query
resource: mysql.qa
sqlType: select
sql: >
SELECT
p.category,
SUM(CASE WHEN p.stock_quantity <= 10 THEN 1 ELSE 0 END) as critical_stock,
SUM(CASE WHEN p.stock_quantity BETWEEN 11 AND 50 THEN 1 ELSE 0 END) as low_stock,
SUM(CASE WHEN p.stock_quantity BETWEEN 51 AND 100 THEN 1 ELSE 0 END) as normal_stock,
SUM(CASE WHEN p.stock_quantity > 100 THEN 1 ELSE 0 END) as high_stock
FROM products p
GROUP BY p.category
ORDER BY (critical_stock + low_stock + normal_stock + high_stock) DESC
display: chartjs
displayFn: |
const labels = rows.map(row => row.category);
const criticalData = rows.map(row => row.critical_stock);
const lowData = rows.map(row => row.low_stock);
const normalData = rows.map(row => row.normal_stock);
const highData = rows.map(row => row.high_stock);
const data = {
labels: labels,
datasets: [{
label: '위험 (≤10개)',
data: criticalData,
backgroundColor: 'rgba(255, 99, 132, 0.8)'
}, {
label: '부족 (11-50개)',
data: lowData,
backgroundColor: 'rgba(255, 206, 86, 0.8)'
}, {
label: '정상 (51-100개)',
data: normalData,
backgroundColor: 'rgba(75, 192, 192, 0.8)'
}, {
label: '충분 (>100개)',
data: highData,
backgroundColor: 'rgba(54, 162, 235, 0.8)'
}]
};
const config = {
type: 'bar',
data: data,
options: {
responsive: true,
maintainAspectRatio: false,
scales: {
x: { stacked: true },
y: {
stacked: true,
title: { display: true, text: '상품 수량' }
}
},
plugins: {
title: { display: true, text: '카테고리별 재고 현황' }
}
}
};
const myChart = new Chart(ctx, config);