はじめに
以前の記事で、dbt・BigQuery・Terraform で「数字が合うEC分析基盤」を作りました。今回はその続きで、できあがったテーブル（マート）を見るためのダッシュボードを作りました。
以前から、BigQuery のデータを Looker Studio でダッシュボードにするときの構築の手間が大きいと感じていました。そこで今回は、ダッシュボードをコードで作ってみました。
ダッシュボードを作るときに気をつけたのは、次の2つです。
- せっかく揃えた数字を、画面側で崩さないこと
- 認証情報やSQLをブラウザに出さず、BigQuery の費用も増やさないこと
Next.js（App Router）・React・TypeScript で作り、データは BigQuery か、リポジトリに同梱した架空のサンプルから読みます。サンプルを使えば、クラウドのアカウントなしで手元で動きます。
現場の悩みと設計判断
| 現場でよくある悩み | このリポジトリでの判断 |
|---|---|
| BIツールでダッシュボードを1つ作るたびに、構築の手間が大きい | ダッシュボードをコードで書き、小さな部品に分ける。変更の依頼が小さな差分になり、AIが下書きして人がレビューできる |
| 部署ごとに売上の数字が合わない | 指標の定義は dbt の1か所。画面はマートの行を足し上げるだけ |
| それらしく見えて実は間違っている比率（日ごとの平均単価の平均など） | 比率は合計から出し、率の増減は pt で見せる |
| 認証情報やSQLがブラウザに出てしまう | データの取得はサーバーコンポーネントだけ、server-only のモジュールで行う |
| 画面を開くたびに BigQuery の費用が増える | 結果を10分キャッシュし、RFMは25行に集計してから読む |
| データが変わっていないのに数字が毎日変わる | 期間はデータの最終日から数える |
作り方：AIとの分担
AIコーディングアシスタント（Claude）と一緒に、作り始めてからCIが通るまで1時間以内で作りました。何を作るか、誰のどんな悩みに応えるか、守る条件（架空データのみ・秘密情報を扱わない）を決め、動いた画面を確かめたのは私です。設計の提案、コード、テスト、CI、README はAIが担当しました。
できること
| 画面 | 内容 |
|---|---|
売上画面（URL：/） |
純売上・注文数・平均単価（AOV）・粗利率・キャンセル/返品率を前の期間と比較。チャネル別の売上推移と、チャネルごとの表 |
顧客画面（URL：/customers） |
RFMの5セグメントごとの人数・売上比率・1人あたり売上と、セグメントに合う施策。R×F のマトリクス |
期間（30 / 90 / 365日）とチャネルで絞り込めます。絞り込みはただのリンクなので、どの表示にもURLがあり、そのまま共有できます。
全体像
| 読むマート | 粒度 | 使い道 |
|---|---|---|
mart_daily_channel_sales |
日 × チャネル | KPI、推移グラフ、チャネル表 |
mart_customer_rfm |
R スコア × F スコアで集計 | セグメントのカード、R×F マトリクス |
1. 指標はダッシュボードで作り直さない
「売上は完了した注文だけを数える」という定義は、dbt の fct_orders で1回だけ決めています。ダッシュボードは、マートの行を足し上げて、比率を出すだけにしました。
const ratio = (a: number, b: number): number | null => (b === 0 ? null : a / b);
export function summarize(rows: readonly DailyChannelSales[]): Summary {
const t = rows.reduce(
(acc, r) => {
acc.orders += r.orders;
acc.completedOrders += r.completedOrders;
acc.cancelledOrders += r.cancelledOrders;
acc.returnedOrders += r.returnedOrders;
acc.netRevenue += r.netRevenue;
acc.grossMargin += r.grossMargin;
return acc;
},
{ orders: 0, completedOrders: 0, cancelledOrders: 0, returnedOrders: 0, netRevenue: 0, grossMargin: 0 },
);
return {
...t,
averageOrderValue: ratio(t.netRevenue, t.completedOrders),
marginRate: ratio(t.grossMargin, t.netRevenue),
lossRate: ratio(t.cancelledOrders + t.returnedOrders, t.orders),
};
}
ポイントは、比率は合計から出し、日ごとの比率を平均しないことです。日ごとの平均単価を平均すると、注文の少ない日も多い日も同じ重さになり、数字がずれます。マートには average_order_value の列もありますが、期間で集計し直すときは使わず、売上と件数から計算し直しています。
0で割る場合は null を返し、画面では「–」と表示します。
2. データの取り口をインターフェースで1つにする
画面は、次の小さなインターフェースだけに依存しています。
export interface DataSource {
readonly name: "sample" | "bigquery";
dailyChannelSales(): Promise<DailyChannelSales[]>;
rfmCells(): Promise<RfmCell[]>;
}
実装は2つで、環境変数 DATA_SOURCE で切り替えます。BigQuery のクライアントは、使うときだけ読み込みます。
import "server-only";
async function create(): Promise<DataSource> {
const kind = process.env.DATA_SOURCE ?? "sample";
if (kind === "sample") return createSampleSource();
if (kind === "bigquery") {
const projectId = process.env.BQ_PROJECT_ID;
if (!projectId) throw new Error("DATA_SOURCE=bigquery needs BQ_PROJECT_ID");
const [{ BigQuery }, { createBigQuerySource }] = await Promise.all([
import("@google-cloud/bigquery"),
import("./bigquery"),
]);
const location = process.env.BQ_LOCATION;
const source = createBigQuerySource(
{ projectId, dataset: process.env.BQ_DATASET ?? "marts", location },
new BigQuery({ projectId, location }),
);
return withCache(source, CACHE_TTL_MS);
}
throw new Error(`Unknown DATA_SOURCE: ${kind} (use "sample" or "bigquery")`);
}
サンプルは、ec-analytics-platform を DuckDB でビルドした結果を、スクリプトでJSONに書き出したものです。テストでは「サンプルの合計が、マートの合計と一致すること」を確かめています。
3. データの取得はサーバーだけで行う
ページは React のサーバーコンポーネントで、サーバー上でデータを読みます。ブラウザに届くのは描画済みのHTMLだけなので、認証情報もSQLもブラウザに出ません。データを読むモジュールには import "server-only" を付け、間違ってクライアント側から読み込むとビルドエラーになるようにしています。
export const dynamic = "force-dynamic";
export default async function SalesPage({ searchParams }: { searchParams: SearchParams }) {
const params = await searchParams;
const period = parsePeriod(params.period);
const source = await getDataSource();
const rows = await source.dailyChannelSales();
// ...
}
期間やチャネルの絞り込みも、クライアントの状態ではなくURLのクエリで持っています。useState を使わないので、JavaScriptが動かない環境でも絞り込めます。想定外の値（?period=7d など）は既定値に戻します。
4. SQLは値をパラメータで渡し、テーブル名はチェックする
BigQuery では、値はクエリパラメータで渡せますが、データセット名やテーブル名はパラメータにできません。そこで、名前は文字の種類をチェックしてから SQL に入れています。
const IDENTIFIER = /^[A-Za-z0-9_-]{1,1024}$/;
export function tableRef(projectId: string, dataset: string, table: string): string {
for (const [name, value] of [["project", projectId], ["dataset", dataset], ["table", table]] as const) {
if (!IDENTIFIER.test(value)) throw new Error(`Invalid BigQuery ${name} name: ${value}`);
}
return `\`${projectId}.${dataset}.${table}\``;
}
export function dailySql(config: BigQueryConfig): string {
const table = tableRef(config.projectId, config.dataset, "mart_daily_channel_sales");
return `
select
format_date('%Y-%m-%d', ordered_on) as ordered_on,
channel, orders, completed_orders, cancelled_orders, returned_orders,
net_revenue, gross_margin
from ${table}
where ordered_on > date_sub((select max(ordered_on) from ${table}), interval @lookback_days day)
order by ordered_on, channel`;
}
テストでは、marts`; drop table x; -- のようなデータセット名が拒否されることを確かめています。BigQuery のクライアントは query だけを持つ型にしているので、テストでは偽物のクライアントを渡せます。
5. BigQuery を毎回読まない
マートはバッチで作り直すものなので、ページを開くたびに BigQuery を読んでも、費用が増えるだけです。結果をメモリに10分間持つようにしました。失敗した結果は持たず、次のアクセスでやり直します。
export function withCache(source: DataSource, ttlMs: number, now: () => number = Date.now): DataSource {
const memo = <T>(load: () => Promise<T>) => {
let value: { at: number; data: Promise<T> } | null = null;
return () => {
if (value === null || now() - value.at >= ttlMs) {
const data = load();
const entry = { at: now(), data };
value = entry;
data.catch(() => {
if (value === entry) value = null; // 失敗は持たない
});
}
return value.data;
};
};
// ...
}
読む量も減らしています。顧客のRFMは1人1行で読むと顧客数ぶんの行になりますが、画面に必要なのはセグメントの集計だけなので、BigQuery 側で R スコア × F スコアの25行に集計してから読みます。
6. 期間は「データの最終日」から数える
「直近90日」を今日から数えると、データの更新が止まっている間に数字が毎日変わってしまいます。ec-analytics-platform の RFM と同じく、データの最終日を基準にしました。比較するのは、その直前の同じ日数の期間です。
export function resolvePeriod(latestDate: string, period: PeriodKey): PeriodRanges {
const days = PERIODS[period].days;
const end = latestDate;
const start = addDays(end, -(days - 1));
const prevEnd = addDays(start, -1);
const prevStart = addDays(prevEnd, -(days - 1));
return { current: { start, end }, previous: { start: prevStart, end: prevEnd }, days };
}
日付の計算はすべて UTC の日付文字列で行い、サーバーのタイムゾーンで日付がずれないようにしています。
前期間との比較は、金額や件数は「+12.3%」のような増減率で、粗利率やキャンセル率のような率は「+1.2 pt」のような差で表示します。率をさらに増減率で表すと（47.4% → 47.5% が「+0.2%」になる）、読み違えやすいためです。キャンセル率は下がるほうが良いので、色の向きも逆にしています。
7. グラフは自作のSVG
グラフは、折れ線とマトリクスの2種類だけなので、ライブラリを使わずSVGとHTMLで書きました。バンドルが小さくなり、ライト/ダークの配色もCSSの変数1か所で揃えられます。軸の最大値は、1・2・5 × 10のn乗に切り上げています。
export function niceMax(value: number): number {
if (value <= 0) return 1;
const exp = 10 ** Math.floor(Math.log10(value));
const f = value / exp;
const nice = f <= 1 ? 1 : f <= 2 ? 2 : f <= 5 ? 5 : 10;
return nice * exp;
}
週ごとの集計は期間の終わりから7日ずつ数え、最後の週が必ず丸1週間になるようにしています。
テストとCI
Vitest で22件のテストを書きました。
- 集計：比率を合計から出すこと、0で割らないこと、週の区切りで行が漏れたり二重に数えられたりしないこと
- 期間：うるう年や月末をまたぐ日付計算、想定外の値を既定値に戻すこと
- サンプル：同梱データの合計が、マートの合計と一致すること
- BigQuery：テーブル名のチェック、パラメータの渡し方、数値でない値をエラーにすること、キャッシュ
GitHub Actions では、lint・型チェック・テスト・本番ビルドのあとに、サーバーを起動して各ページが200を返すかも確かめています。
動かす
Node.js 20.9 以上が必要です。
git clone https://github.com/yahayuta/ec-dashboard.git
cd ec-dashboard
npm install
npm run dev # http://localhost:3000（同梱のサンプルで動きます）
npm run check # lint・型チェック・テスト・ビルド
BigQuery を使う場合は、ec-analytics-platform でマートを作ってから、環境変数を設定します。
gcloud auth application-default login
DATA_SOURCE=bigquery BQ_PROJECT_ID=your-gcp-project BQ_DATASET=marts BQ_LOCATION=asia-northeast1 npm run dev
読むだけなので、権限はマートのデータセットへの roles/bigquery.dataViewer と、クエリを実行する roles/bigquery.jobUser で足ります。Cloud Run にもそのままデプロイできます（README に手順があります）。
まとめ
- 指標の定義は dbt に1か所。画面は足し上げて比率を出すだけにする
- 比率は合計から出し、率の比較は「pt」で見せる
- データの取得はサーバーコンポーネントだけで行い、認証情報もSQLもブラウザに出さない
- SQLは値をパラメータで渡し、テーブル名は文字の種類をチェックする
- BigQuery の結果はキャッシュし、読む前に集計して行数を減らす
- 期間はデータの最終日から数え、同じデータなら同じ数字を出す
ダッシュボードをコードで書いておけば、AIに頼むだけで画面を直したり項目を足したりできます。いずれは、非エンジニアのメンバーが自分で運用できる形に移していけると良いと考えています。