---
name: cereal-bi
description: Standard Operating Procedure for querying, assembling, and publishing real-time interactive BI dashboards, SQL playground investigations, and studio metric pages on Cereal BI.
---

# Cereal BI Agent Operating Standard (Zero-Distance Insights)

This skill empowers AI agents to explore, query, and publish live interactive BI dashboards, SQL playground investigations, and studio metric pages on Cereal BI in **under 30 seconds**.

---

## 🧭 Navigation & Sub-Document Index

For detailed specifications, inspect the dedicated sections below:
1. **Schema & Data Dictionary** (18 Tables: 9 Mart aggregates + 9 STG raw event tables)
2. **Golden SQL Recipes** (P&L, A/B Testing, Meta ROAS, Cohort LTV, OpEx)
3. **Component Catalog** (`<Grid>`, `<BigValue>`, `<CustomTable>`, `<ExploreQueryLink>`, `<LineChart>`, `<BarChart>`, `<Alert>`)
4. **API & CLI Contract** (`/api/save-report`, `/api/sidebar-config`, `/api/page-content`, `npm run query`)

---

## ⚡ 1. Rapid Data Verification via CLI (<1 Second)

Always verify your DuckDB query logic via the CLI before authoring or saving report pages:

```bash
cd /Users/cheonmyeongseung/cereal-bi
npm run query "SELECT * FROM mart.mart_daily ORDER BY date_kst DESC LIMIT 5;"
```

---

## 🛠️ 2. Standardized Page Authoring Protocol

Every Cereal BI metric page adheres to a standardized structure:

### Required Page Template (`pages/[category]/[slug].md`)
```markdown
---
title: Desire Onboarding V2 전환율 분석
sidebar_position: 10
full_width: true
---

# Desire Onboarding V2 전환율 분석

> 신규 가입 유저의 온보딩 V2 플로우와 결제 전환율(CVR) 및 ARPU를 비교 분석하는 리포트입니다.

```sql exp_data
SELECT 
  variant,
  assigned_devices,
  msg_devices,
  payer_devices,
  revenue_krw,
  pay_cvr_pct,
  arpu_krw,
  srm_flag
FROM mart.mart_exp_total 
WHERE experiment_id = 'desire-onboarding-v2'
ORDER BY variant;
```

<Grid cols=4>
  <BigValue data={exp_data} value=assigned_devices title="총 배정 기기수" />
  <BigValue data={exp_data} value=pay_cvr_pct title="결제 전환율" />
  <BigValue data={exp_data} value=arpu_krw title="ARPU" />
  <BigValue data={exp_data} value=srm_flag title="SRM 검정" />
</Grid>

<CustomTable data={exp_data} />

<ExploreQueryLink 
  title="실험 원본 STG 검증 쿼리" 
  sql="SELECT * FROM mart.stg_experiment WHERE experiment_id = 'desire-onboarding-v2' LIMIT 50;"
  hint="DuckDB에서 직접 원본 데이터와 전환율을 검증할 수 있습니다."
/>
```

---

## 🚀 3. Publishing Modes

### Option A: Programmatic API Call (Fastest for Autonomous Agents)
```bash
curl -X POST http://localhost:3000/api/save-report \
  -H "Content-Type: application/json" \
  -d '{
    "title": "Onboarding V2 전환율 분석",
    "category": "experiment",
    "sql": "SELECT * FROM mart.mart_exp_total WHERE experiment_id = \"desire-onboarding-v2\";",
    "description": "온보딩 V2 실험 결과 리포트입니다."
  }'
```

### Option B: Interactive Studio GUI
1. Navigate to `http://localhost:3000/studio/`
2. Write SQL and markdown in the syntax-highlighted editor.
3. Click **🚀 Publish Report (`⌘S`)** to automatically generate the file and register it to the sidebar without a full page reload.
4. Any existing page in the sidebar can be reopened in Studio mode via `/studio?page=[path]`.

---

# 📚 SECTION 1: 18-Table Schema SOT (Data Dictionary)

DuckDB in Cereal BI connects to the Parquet data lake generated by the `mart_reader` pipeline.
All tables are partitioned or indexed under the `mart` schema.

## 📊 1. MART Aggregate Tables (`mart.*`)

### 1.1 `mart.mart_daily`
**Description**: Daily accounting SOT (revenue, refunds, costs, contribution profit, ARPU, active devices).
- `date_kst` (VARCHAR / DATE): KST date (`YYYY-MM-DD`) - Primary Partition Key
- `gross_revenue_krw` (BIGINT): Total payment amount (KRW)
- `refund_krw` (BIGINT): Total refund amount (KRW)
- `net_revenue_krw` (BIGINT): `gross_revenue_krw - refund_krw`
- `cost_krw` (BIGINT): Total daily cost (Meta Ads + OpenRouter + Replicate)
- `meta_ads_krw` (BIGINT): Meta ad spend SOT
- `openrouter_krw` (BIGINT): OpenRouter LLM API spend
- `replicate_krw` (BIGINT): Replicate image gen spend
- `profit_krw` (BIGINT): `net_revenue_krw - cost_krw`
- `new_devices` (INTEGER): Newly onboarded devices on this day
- `msg_devices` (INTEGER): Devices sending at least 1 message
- `payers` (INTEGER): Unique paying devices on this day
- `pay_cvr_pct` (FLOAT): `payers / new_devices * 100`

### 1.2 `mart.mart_cohort_daily`
**Description**: Cohort-based LTV & retention tracking by install date.
- `cohort_date_kst` (VARCHAR / DATE): User cohort install date
- `new_devices` (INTEGER): Size of cohort
- `d0_rev_krw` (BIGINT): Day 0 cumulative revenue
- `d1_rev_krw` (BIGINT): Day 1 cumulative revenue
- `d3_rev_krw` (BIGINT): Day 3 cumulative revenue
- `d7_rev_krw` (BIGINT): Day 7 cumulative revenue
- `d14_rev_krw` (BIGINT): Day 14 cumulative revenue
- `d30_rev_krw` (BIGINT): Day 30 cumulative revenue
- `total_revenue_krw` (BIGINT): Total cumulative revenue to date
- `ad_spend_krw` (BIGINT): Estimated cohort acquisition cost
- `profit_per_user_krw` (BIGINT): Net profit per onboarded device

### 1.3 `mart.mart_aarrr_device`
**Description**: Full device-level AARRR funnel attribution ledger.
- `device_id` (VARCHAR): Unique device ID (UUID)
- `created_date_kst` (VARCHAR): First appearance date
- `utm_source` (VARCHAR): Traffic source (e.g. `meta`, `direct`)
- `utm_campaign` (VARCHAR): Campaign name
- `utm_content` (VARCHAR): Creative / Ad ID
- `utm_medium` (VARCHAR): Channel type
- `is_msg_user` (BOOLEAN): Sent >= 1 message
- `is_handoff_user` (BOOLEAN): Claimed Web-to-App handoff token
- `is_payer` (BOOLEAN): Paid at least once
- `revenue_krw` (BIGINT): Lifetime revenue from this device

### 1.4 `mart.mart_exp_total`
**Description**: Accumulated A/B experiment totals with sample ratio mismatch (SRM) checks.
- `experiment_id` (VARCHAR): Unique experiment key
- `variant` (VARCHAR): Variant key (`control`, `treatment_a`, etc.)
- `assigned_devices` (BIGINT): Devices allocated
- `msg_devices` (BIGINT): Devices engaging with messages
- `payer_devices` (BIGINT): Paying devices
- `revenue_krw` (BIGINT): Total revenue generated
- `msg_cvr_pct` (FLOAT): Message conversion rate (`msg_devices / assigned_devices * 100`)
- `pay_cvr_pct` (FLOAT): Pay conversion rate (`payer_devices / assigned_devices * 100`)
- `arpu_krw` (FLOAT): Average revenue per user (`revenue_krw / assigned_devices`)
- `srm_flag` (VARCHAR): SRM status (`PASS` / `CHECK_SRM`)

### 1.5 `mart.mart_exp`
**Description**: Daily time-series breakdown of active experiments.
- `date_kst` (VARCHAR): KST Date
- `experiment_id` (VARCHAR): Experiment key
- `variant` (VARCHAR): Variant key
- `assigned_devices` (INTEGER): Daily new assigned devices
- `payers` (INTEGER): Daily new payers
- `revenue_krw` (BIGINT): Daily revenue

### 1.6 `mart.mart_ads_campaign`
**Description**: Meta advertising performance grouped by campaign.
- `date_kst` (VARCHAR): Date
- `campaign_name` (VARCHAR): Full campaign name
- `spend_krw` (BIGINT): Ad spend
- `devices` (INTEGER): Attributed installs
- `payer_devices` (INTEGER): Attributed payers
- `revenue_krw` (BIGINT): Attributed revenue
- `roas` (FLOAT): `revenue_krw / spend_krw`

### 1.7 `mart.mart_ads_adset`
**Description**: Meta advertising performance grouped by adset.
- `date_kst` (VARCHAR): Date
- `adset_name` (VARCHAR): Adset name
- `spend_krw` (BIGINT): Ad spend
- `devices` (INTEGER): Attributed installs
- `payer_devices` (INTEGER): Attributed payers
- `revenue_krw` (BIGINT): Attributed revenue
- `roas` (FLOAT): `revenue_krw / spend_krw`
- `cac_krw` (FLOAT): `spend_krw / devices`

### 1.8 `mart.mart_ads`
**Description**: Meta creative-level (Ad ID) performance.
- `date_kst` (VARCHAR): Date
- `ad_name` (VARCHAR): Creative name / Ad ID
- `spend_krw` (BIGINT): Ad spend
- `devices` (INTEGER): Attributed installs
- `payer_devices` (INTEGER): Attributed payers
- `revenue_krw` (BIGINT): Attributed revenue
- `roas` (FLOAT): `revenue_krw / spend_krw`
- `cpa_krw` (FLOAT): `spend_krw / payer_devices`

### 1.9 `mart.mart_os`
**Description**: Performance segmented by platform / OS (`ios`, `android`, `web`).
- `date_kst` (VARCHAR): Date
- `os` (VARCHAR): Operating system
- `spend_krw` (BIGINT): Attributed ad spend
- `devices` (INTEGER): Active/new devices
- `payers` (INTEGER): Paying devices
- `revenue_krw` (BIGINT): Revenue

---

## 🛠️ 2. STG Raw Lineage Tables (`mart.stg_*`)

| Table | Description | Join Keys |
| :--- | :--- | :--- |
| `mart.stg_experiment` | Device experiment eligibility & variant logs | `device_id`, `experiment_id`, `variant` |
| `mart.stg_device` | Device first-touch UTM parameters & registration | `device_id`, `utm_campaign`, `utm_content` |
| `mart.stg_payment` | All payment transactions (Toss/Store) & refund flags | `device_id`, `paid_date_kst`, `is_refund` |
| `mart.stg_messages` | Real-time chat & message activity events | `device_id`, `event_date_kst`, `event_type` |
| `mart.stg_handoff` | Web-to-app token claims & verification | `device_id`, `claimed_date_kst`, `is_claimed` |
| `mart.stg_ad_spend_meta` | Meta API raw spend by campaign/adset/ad | `campaign_name`, `adset_name`, `ad_name` |
| `mart.stg_ad_spend` | Aggregated multi-channel ad spend SOT | `date_kst`, `source` |
| `mart.stg_subscription` | RevenueCat subscription lifecycle transactions | `device_id`, `store_vendor`, `event_class` |
| `mart.stg_opex` | Provider API bills (OpenRouter, Replicate) | `date_kst`, `vendor`, `amount_krw` |

---

# 🍳 SECTION 2: Golden SQL Recipes

### Recipe 1: Daily Executive P&L (Last 14 Days)
```sql
SELECT 
  date_kst,
  new_devices,
  msg_devices,
  payers,
  revenue_krw AS gross_revenue_krw,
  refund_krw,
  net_revenue_krw,
  cost_krw,
  profit_krw,
  ROUND(((profit_krw::numeric / NULLIF(cost_krw, 0)) * 100), 1) AS roi_pct,
  ROUND((revenue_krw::numeric / NULLIF(cost_krw, 0)), 2) AS roas
FROM mart.mart_daily
ORDER BY date_kst DESC
LIMIT 14;
```

### Recipe 2: A/B Experiment SRM & Funnel Significance (STG Lineage)
```sql
WITH exp AS (
  SELECT experiment_id, variant, device_id, assigned_date_kst
  FROM mart.stg_experiment 
  WHERE is_eligible = true 
    AND experiment_id = 'desire-onboarding-v2'
),
p AS (
  SELECT device_id, SUM(amount_krw) AS total_revenue_krw
  FROM mart.stg_payment 
  WHERE NOT is_refund 
  GROUP BY 1
),
m AS (
  SELECT device_id, COUNT(*) AS msg_count
  FROM mart.stg_messages 
  WHERE event_type = 'message_sent' 
  GROUP BY 1
)
SELECT 
  e.variant,
  COUNT(DISTINCT e.device_id) AS assigned_devices,
  COUNT(DISTINCT m.device_id) AS msg_devices,
  COUNT(DISTINCT p.device_id) AS payer_devices,
  COALESCE(SUM(p.total_revenue_krw), 0) AS revenue_krw,
  ROUND(COUNT(DISTINCT m.device_id)::FLOAT / NULLIF(COUNT(DISTINCT e.device_id), 0) * 100, 2) AS msg_cvr_pct,
  ROUND(COUNT(DISTINCT p.device_id)::FLOAT / NULLIF(COUNT(DISTINCT e.device_id), 0) * 100, 2) AS pay_cvr_pct,
  ROUND(COALESCE(SUM(p.total_revenue_krw), 0)::FLOAT / NULLIF(COUNT(DISTINCT e.device_id), 0), 0) AS arpu_krw
FROM exp e
LEFT JOIN m ON e.device_id = m.device_id
LEFT JOIN p ON e.device_id = p.device_id
GROUP BY 1 
ORDER BY 1;
```

### Recipe 3: Meta Ad Creative Efficiency
```sql
SELECT 
  c.campaign_name,
  SUM(s.spend_krw) AS stg_raw_spend_krw,
  SUM(c.spend_krw) AS mart_spend_krw,
  SUM(c.devices) AS installs,
  SUM(c.payer_devices) AS payers,
  ROUND(SUM(c.payer_devices)::FLOAT / NULLIF(SUM(c.devices), 0) * 100, 2) AS pay_cvr_pct,
  SUM(c.revenue_krw) AS total_revenue_krw,
  ROUND(SUM(c.revenue_krw)::FLOAT / NULLIF(SUM(c.spend_krw), 0), 2) AS roas,
  ROUND((SUM(c.revenue_krw) - SUM(c.spend_krw))::FLOAT / NULLIF(SUM(c.spend_krw), 0) * 100, 1) AS roi_pct
FROM mart.mart_ads_campaign c
LEFT JOIN mart.stg_ad_spend_meta s 
  ON c.date_kst = s.date_kst AND c.campaign_name = s.campaign_name
WHERE c.date_kst >= CURRENT_DATE - 6
GROUP BY c.campaign_name
ORDER BY mart_spend_krw DESC;
```

---

# 🧩 SECTION 3: Component Catalog

```markdown
<Grid cols=4>
  <BigValue data={my_data} value=gross_revenue_krw title="총매출" />
  <BigValue data={my_data} value=cost_krw title="총비용" />
  <BigValue data={my_data} value=profit_krw title="순이익" />
  <BigValue data={my_data} value=roi_pct title="ROI %" />
</Grid>

<CustomTable data={my_data} />

<ExploreQueryLink 
  title="실험 원본 STG 검증 쿼리" 
  sql="SELECT * FROM mart.stg_experiment WHERE experiment_id = 'desire-onboarding-v2' LIMIT 50;"
  hint="DuckDB에서 직접 원본 데이터와 전환율을 검증할 수 있습니다."
/>
```

---

# 🔌 SECTION 4: API Contract

### REST API Endpoints
- `POST /api/save-report`: Create or overwrite report markdown file and update sidebar dynamically.
- `GET /api/sidebar-config`: Retrieve `{ pinned, archived, customFolders, folderMap, collapsedFolders }`.
- `POST /api/sidebar-config`: Persist sidebar layout state.
- `GET /api/page-content?path=...`: Load raw markdown for any page in Studio editor.
- `GET /api/skill`: Download complete agent SOP & schema bundle.