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