OJO-ERD
Tables (35)
SMS
- sms_id — BIGINT — Primary key — NOT NULL
- sms_type — VARCHAR(20)
- content — TEXT
- sent_at — DATETIME
access_logs
- access_logs_id — BIGINT — Primary key — NOT NULL
- user_id — BIGINT
- email — VARCHAR(50)
- role — VARCHAR(30)
- method — VARCHAR(10) — NOT NULL — GET/POST/…
- path — VARCHAR(500) — NOT NULL
- status_code — INT — NOT NULL — 200/401/403/500 등
- ip — VARCHAR(255)
- user_agent — VARCHAR(255)
- created_at — DATETIME — NOT NULL
- admin_id — BIGINT — NOT NULL
admin
- admin_id — BIGINT — Primary key — NOT NULL
- name — VARCHAR(30) — NOT NULL
- email — VARCHAR(255) — NOT NULL
- phone — VARCHAR(15) — NOT NULL
- google — BOOLEAN — NOT NULL
- role — VARCHAR(20) — CS/마케팅/관리자
- status — VARCHAR(20) — NOT NULL — 재직/퇴사
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- password — VARCHAR(255)
advice
- advice_id — BIGINT — Primary key — NOT NULL
- member_id — BIGINT — NOT NULL — 상담을 받은 고객
- admin_id — BIGINT — NOT NULL — 상담을 진행한 상담사
- category_id — INT — NOT NULL
- promotion_id — VARCHAR(255) — 특정 프로모션 관련 상담인경우
- direction — VARCHAR(255) — NOT NULL — IN,OUT bound 구분
- channel — VARCHAR(255) — NOT NULL — CALL,APP,SMS 등 구분
- advice_content — TEXT
- start_at — DATETIME
- end_at — DATETIME
- created_at — DATETIME — NOT NULL
- satisfaction_score — BIGINT — 1~10 만족도
analysis
- analysis_id — BIGINT — Primary key — NOT NULL
- rfm_score — INT
- type — VARCHAR(255) — vip, 잠재 vip, 고객, 이탈 우려 고객, 이탈 고객
- ltv — BIGINT
- lifecycle_stage — VARCHAR(255)
- created_at — DATETIME
- member_id — BIGINT — NOT NULL
auth_users
- auth_users_id — BIGINT — Primary key — NOT NULL
- name — VARCHAR(20)
- email — VARCHAR(255) — NOT NULL
- provider — VARCHAR(30) — NOT NULL — GOOGLE
- role — VARCHAR(30) — NOT NULL — ROLE_ADMIN / ROLE_AGENT
- status — VARCHAR(30) — NOT NULL — ACTIVE / BLOCKED
- created_at — DATETIME — NOT NULL
- last_login_at — DATETIME
auth_whitelist_emails
- auth_whitelist_emails_id — BIGINT — Primary key — NOT NULL
- email — VARCHAR(255) — NOT NULL
- created_at — DATETIME — NOT NULL
categories
- category_id — INT — Primary key — NOT NULL
- parent_id — INT
- category_name — VARCHAR(255) — NOT NULL
channel
- channel_id — BIGINT — Primary key — NOT NULL
- admin_id — BIGINT — NOT NULL
- name — VARCHAR(50) — NOT NULL
- description — TEXT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- status — VARCHAR(20) — NOT NULL
channel_member
- ch_member_id — BIGINT — Primary key — NOT NULL
- channel_id — BIGINT — NOT NULL
- member_id — BIGINT — NOT NULL
- created_at — DATETIME — NOT NULL
churn_prediction
- prediction_id — BIGINT — Primary key — NOT NULL
- member_id — BIGINT — NOT NULL
- churn_score — FLOAT
- risk_grade — VARCHAR(10) — ex. DANGER, WARNING, SAFE
- predicted_at — DATETIME
cohort
- cohort_id — BIGINT — Primary key — NOT NULL
- dimension_id — INT — NOT NULL
- target — INT — (주), 월, 년
- member_count — BIGINT
- conversion_rate — FLOAT
- retention_rate — FLOAT
- avg_revenue — DECIMAL(10, 2)
- dimension_value — VARCHAR(255)
- base_date — DATETIME — NOT NULL
data_usage
- data_usage_id — BIGINT — Primary key — NOT NULL
- member_id — BIGINT — NOT NULL
- usage_date — DATE — NOT NULL
- usage_time — INT — NOT NULL — 0~23 (가장 많이 사용한 시간 1회)
- usage_amount — BIGINT
- region — VARCHAR(255)
- created_at — DATETIME — NOT NULL
dimension
- dimension_id — INT — Primary key — NOT NULL
- dimension_type — VARCHAR(255) — 나이대, 성별, 특성(가족, 혼자), 거주 지역, 사용 서비스, 세그먼트
feature_consultation
- consultation_id — BIGINT — Primary key — NOT NULL
- member_id — BIGINT — NOT NULL
- feature_base_date — DATE
- total_consult_count — INT — NOT NULL
- last_7d_consult_count — INT — NOT NULL
- last_30d_consult_count — INT — NOT NULL
- avg_monthly_consult_count — FLOAT — NOT NULL
- last_consult_date — DATE
- night_consult_count — INT
- weekend_consult_count — INT
- top_consult_category — VARCHAR(30)
- total_complaint_count — INT — NOT NULL
- last_consult_days_ago — INT
feature_lifecycle
- lifecycle_id — BIGINT — Primary key — NOT NULL
- member_id — BIGINT — NOT NULL
- feature_base_date — DATE
- member_lifetime_days — INT — NOT NULL
- days_since_last_activity — INT
- contract_end_days_left — INT
- is_dormant_flag — char — NOT NULL — 휴면 여부(Y/N)
- is_new_customer_flag — char(1) — NOT NULL — 신규 가입 여부(최근 30일 기준)
- is_terminated_flag — char(1) — NOT NULL
- signup_date — DATE — NOT NULL
feature_monetary
- monetary_id — BIGINT — Primary key — NOT NULL
- member_id — BIGINT — NOT NULL
- feature_base_date — DATE
- total_revenue — BIGINT — NOT NULL
- last_payment_amount — BIGINT — NOT NULL
- avg_monthly_bill — FLOAT — NOT NULL
- last_payment_date — DATE
- payment_count_6m — INT — NOT NULL
- monthly_revenue — BIGINT — NOT NULL
- payment_delay_count — INT — NOT NULL
- prev_monthly_revenue — BIGINT — NOT NULL
- is_vip_prev_month — CHAR — NOT NULL — Y/N
- avg_order_val — FLOAT — NOT NULL
- purchase_cycle — INT — NOT NULL
feature_usage
- usage_id — BIGINT — Primary key — NOT NULL
- member_id — BIGINT — NOT NULL
- feature_base_date — DATE
- total_usage_amount — BIGINT — NOT NULL
- avg_daily_usage — FLOAT — NOT NULL
- max_usage_amount — BIGINT — NOT NULL
- usage_peak_hour — INT
- premium_service_count — INT — NOT NULL
- last_activity_date — DATE
- usage_active_days_30d — INT — NOT NULL
invoice
- invoice_id — BIGINT — Primary key — NOT NULL
- member_id — BIGINT — NOT NULL
- base_month — VARCHAR(6) — NOT NULL
- due_date — DATE — NOT NULL
- billed_amount — BIGINT — NOT NULL
- overdue_amount — BIGINT — NOT NULL
- created_at — DATETIME — NOT NULL
invoice_detail
- invoice_detail_id — BIGINT — Primary key — NOT NULL
- invoice_id — BIGINT — NOT NULL
- product_id — BIGINT — NOT NULL
- product_name_snapshot — VARCHAR(50) — NOT NULL — 상품 이름 변경되도 과거로 고정
- product_type — VARCHAR(20) — NOT NULL
- quantity — BIGINT — 타입이 요금제인 경우
- total_price — BIGINT — NOT NULL
- started_at — DATETIME — NOT NULL
- end_at — DATETIME
layout_config
- layout_config_id — BIGINT — Primary key — NOT NULL
- admin_id — BIGINT — NOT NULL
- page — VARCHAR(50) — NOT NULL — 분석, 고객
- layout_json — TEXT — NOT NULL — JSON 형태로 저장
- updated_at — DATETIME — NOT NULL
member
- member_id — BIGINT — Primary key — NOT NULL
- name — VARCHAR(10) — NOT NULL
- phone — VARCHAR(30) — NOT NULL
- email — VARCHAR(50) — NOT NULL
- gender — VARCHAR(10) — NOT NULL
- birth_date — DATE — NOT NULL
- region — VARCHAR(255) — NOT NULL — 지역(도,시)
- address — VARCHAR(255) — NOT NULL — 우편번호,도로명등 세부위치
- household_type — INT — NOT NULL — 1, 2, 3, 4이상
- created_at — DATETIME — NOT NULL
- status — VARCHAR(20) — NOT NULL — ACTIVE등 휴면, 탈퇴
member_consent
- member_consent_id — BIGINT — Primary key — NOT NULL
- member_id — BIGINT — NOT NULL
- personal_accepted — CHAR(1) — NOT NULL — Y/N
- marketing_accepted — CHAR(1) — NOT NULL — Y/N
- is_converted — CHAR(1) — Y/N
- accepted_at — DATETIME — NOT NULL
- expires_at — DATETIME
memo
- memo_id — BIGINT — Primary key — NOT NULL
- member_id — BIGINT — NOT NULL
- admin_id — BIGINT — NOT NULL
- content — TEXT — NOT NULL
- summary — VARCHAR(20) — 상담외 기록, 특이사항, 블랙리스트 사유 등
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
payment
- payment_id — BIGINT — Primary key — NOT NULL
- invoice_id — BIGINT — NOT NULL
- paid_amount — BIGINT
- paid_method — VARCHAR(20) — card,bank등
- paid_at — DATETIME
- created_at — DATETIME
product
- product_id — BIGINT — Primary key — NOT NULL
- price — BIGINT — NOT NULL
- product_category — VARCHAR(20) — NOT NULL
- product_name — VARCHAR(50) — NOT NULL
- product_type — VARCHAR(20) — NOT NULL — 구독/단건
promotion
- promotion_id — VARCHAR(255) — Primary key — NOT NULL
- promotion_name — VARCHAR(255) — NOT NULL
- promotion_detail — TEXT
recommendations
- recommendations_id — BIGINT — Primary key — NOT NULL
- member_id — BIGINT — NOT NULL
- item — VARCHAR(255)
- reason — TEXT
- score — FLOAT
- rank — VARCHAR(255)
- created_at — VARCHAR(255)
refresh_tokens
- refresh_tokens_id — BIGINT — Primary key — NOT NULL
- admin_id — BIGINT — NOT NULL
- token — VARCHAR(1000) — NOT NULL
rfm
- member_id — BIGINT — Foreign key → member.member_id — NOT NULL — 고객 1명당 1개의 RFM 생성
- recency — DATETIME — 가장 최근에 구매한 날짜
- frequency — INT — 6달 동안 구매한 총 회수(feature_monetary맞춰 수정)
- monetary — BIGINT — 금액 지출 양
- updated_at — DATETIME
rfm_kpi
- kpi_id — BIGINT — Primary key — NOT NULL
- crr — Decimal(5, 4) — VIP가 유지되는 비율
- churn_rate — Decimal(5, 4) — 이탈률(RFM에서 R)이 갑작스럽게 낮아진 비율
- nrr — Decimal(5, 4) — VIP가 아닌 일반 고객이 지난 달 대비 얼마나 많은 매출에 대한 비율
- base_month — VARCHAR(6) — NOT NULL
snapshot_billing
- snapshot_id — BIGINT — Primary key — NOT NULL
- member_id — BIGINT — NOT NULL
- base_month — VARCHAR(6) — NOT NULL — 202602 형태
- billed_amount — BIGINT — NOT NULL
- overdue_amount — BIGINT
- overdue_months — BIGINT
- grade — VARCHAR(20)
- month_revenue — BIGINT — NOT NULL
- total_revenue — BIGINT
subscription_period
- subscription_period_id — BIGINT — Primary key — NOT NULL
- product_id — BIGINT — NOT NULL
- member_id — BIGINT — NOT NULL
- quantity — BIGINT — NOT NULL
- total_price — BIGINT — NOT NULL
- started_at — DATETIME — NOT NULL
- status — VARCHAR(20) — NOT NULL — ACTIVE, CANCLED
- end_at — DATETIME
- reason_code — TEXT — text, code
system_config
- system_config_id — INT — Primary key — NOT NULL
- value — INT
- updated_at — DATETIME
verification_codes
- verification_codes_id — BIGINT — Primary key — NOT NULL
- target — VARCHAR(255) — NOT NULL — email 또는 phone 값
- type — VARCHAR(30) — NOT NULL — EMAIL / PHONE
- code — VARCHAR(10) — NOT NULL
- status — VARCHAR(30) — NOT NULL — SENT / VERIFIED / EXPIRED
- expires_at — DATETIME — NOT NULL
- created_at — DATETIME — NOT NULL — @CreationTimestamp