弱点補強 — BigQuery クエリコスト最適化 × dbt Materialization

📋 コピー{{
    config(
        materialized='incremental',
        partition_by={
            'field': 'ordered_date',
            'data_type': 'date',
            'granularity': 'day'
        },
        cluster_by=['campaign_id'],
        incremental_strategy='insert_overwrite',
        on_schema_change='fail'
    )
}}

-- 増分実行時は直近2日分のパーティションを上書き（遅延到着データ対応）
WITH orders_filtered AS (
    SELECT
        o.order_id,
        o.campaign_id,
        o.user_id,
        o.amount_jpy,
        DATE(o.ordered_at) AS ordered_date
    FROM `{{ source('raw', 'orders') }}` AS o
    WHERE
        DATE(o.ordered_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
        {% if is_incremental() %}
        -- 増分実行: 直近2日のパーティションのみ再計算（遅延到着データを考慮）
        AND DATE(o.ordered_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)
        {% endif %}
),

campaign_daily AS (
    SELECT
        c.campaign_id,
        c.campaign_name,
        c.start_date,
        c.end_date,
        c.budget_jpy,
        of.ordered_date,
        COUNT(DISTINCT of.user_id)   AS reached_users,
        COUNT(of.order_id)           AS orders,
        SUM(of.amount_jpy)           AS revenue_jpy
    FROM `{{ source('raw', 'campaigns') }}` AS c
    INNER JOIN orders_filtered AS of
        ON of.campaign_id = c.campaign_id
        AND of.ordered_date BETWEEN c.start_date AND c.end_date
    GROUP BY
        c.campaign_id, c.campaign_name, c.start_date,
        c.end_date, c.budget_jpy, of.ordered_date
)

SELECT
    campaign_id,
    campaign_name,
    start_date,
    end_date,
    budget_jpy,
    ordered_date,
    reached_users,
    orders,
    revenue_jpy,
    SAFE_DIVIDE(revenue_jpy - budget_jpy, budget_jpy) * 100 AS roi_pct_daily
FROM campaign_daily