만약에 매일 매일 자료를 수집하게 되면 조회수나 기타 업데이트되는 정보들도 있을것 같은데, 이것을 통해서 갑자기 떠오르는 채널 혹은 콘텐츠가 발견되고 하겠네. 그리고 이런 정보를

네, BigQuery만으로도 매일 수집된 조회수·좋아요·댓글·시청 지속 시간 변화에서 “갑자기 뜨는” 채널·콘텐츠를 손쉽게 탐지할 수 있습니다. 주요 기법과 예시 SQL을 정리해 드릴게요.

---

1. 일별 집계 테이블 준비

먼저, 원시 이벤트를 일별로 집계한 테이블(예: daily_metrics)이 있어야 합니다.

sql
복사편집
-- 예시: daily_metrics 테이블 스키마
-- date            DATE
-- video_id        STRING
-- channel_id      STRING
-- views           INT64
-- likes           INT64
-- comments        INT64
-- watch_duration  FLOAT64  -- 총 시청 초(by sum) 혹은 평균 시청 시간

이 테이블은 앞서 말씀드린 Cloud Scheduler → BigQuery Load 파이프라인으로 매일 갱신됩니다.

---

2. 전일 대비 성장률 계산

LAG() 윈도우 함수를 써서 전일 수치와 비교한 성장률(Percent Change)을 계산하고, 급등한 항목만 필터링합니다.

sql
복사편집
WITH growth AS (
  SELECT
    date,
    video_id,
    channel_id,
    views,
    LAG(views) OVER (PARTITION BY video_id ORDER BY date) AS prev_views,
    SAFE_DIVIDE(views - LAG(views) OVER (PARTITION BY video_id ORDER BY date),
                LAG(views) OVER (PARTITION BY video_id ORDER BY date)
               ) AS pct_change
  FROM `project.dataset.daily_metrics`
)
SELECT
  date,
  video_id,
  channel_id,
  views,
  prev_views,
  pct_change
FROM growth
WHERE prev_views IS NOT NULL
  AND pct_change > 0.5   -- 50% 이상 성장한 콘텐츠만
ORDER BY pct_change DESC
LIMIT 20;

• pct_change가 높을수록 전일 대비 급격히 떠오른 콘텐츠입니다.

• 원하는 임계값(0.5 등)을 조정해 알림용 조건을 세팅할 수 있습니다.

---

3. 채널 단위 성장 탐지

콘텐츠뿐 아니라 채널 단위로도 같은 방식으로 성장률을 볼 수 있습니다.

sql
복사편집
WITH channel_growth AS (
  SELECT
    date,
    channel_id,
    SUM(views) AS total_views,
    LAG(SUM(views)) OVER (PARTITION BY channel_id ORDER BY date) AS prev_views
  FROM `project.dataset.daily_metrics`
  GROUP BY date, channel_id
)
SELECT
  date,
  channel_id,
  total_views,
  prev_views,
  SAFE_DIVIDE(total_views - prev_views, prev_views) AS pct_change
FROM channel_growth
WHERE prev_views IS NOT NULL
  AND SAFE_DIVIDE(total_views - prev_views, prev_views) > 0.3  -- 30% 이상 성장
ORDER BY pct_change DESC
LIMIT 10;

---

4. BigQuery ML로 이상치·스파이크 감지

SQL 외에도 BigQuery ML의 이상치 탐지 기능을 쓰면, 수치가 일정 패턴을 벗어나는 시점을 자동으로 표시해 줍니다.

sql
복사편집
-- 1) 모델 생성: 채널별 일일 조회수 이상치 탐지용
CREATE OR REPLACE MODEL `project.dataset.channel_views_anomaly_model`
OPTIONS(
  model_type = 'timeseries_anomaly',
  time_series_timestamp_col = 'date',
  time_series_data_col = 'total_views',
  time_series_timestamp_format = 'YYYY-MM-DD',
  horizon = 1
) AS
SELECT
  date,
  channel_id,
  SUM(views) AS total_views
FROM `project.dataset.daily_metrics`
GROUP BY date, channel_id;

-- 2) 이상치 감지
SELECT *
FROM ML.DETECT_ANOMALIES(
  MODEL `project.dataset.channel_views_anomaly_model`,
  (
    SELECT date, SUM(views) AS total_views
    FROM `project.dataset.daily_metrics`
    WHERE channel_id = 'YOUR_CHANNEL_ID'
    GROUP BY date
    ORDER BY date
  )
)
WHERE is_anomaly = TRUE;

• is_anomaly = TRUE인 날짜가 트래픽 급등·급감 시점입니다.

• 채널별·콘텐츠별로 모델을 만들고 주기적으로 ML.DETECT_ANOMALIES를 호출해 알림을 설정할 수 있습니다.

---

5. 시각화·알림 자동화

1. Looker Studio나 Data Studio에 위 쿼리를 연결해 “오늘의 급등 콘텐츠 탑10” 차트를 만든 뒤

2. Scheduled Report(주간·일간 이메일) 또는 Alerts 기능을 활용해 팀에 자동 발송

또는, Cloud Functions + Pub/Sub를 이용해 특정 쿼리 결과를 슬랙·메일로 푸시하는 방법도 있습니다.

---

결론

• 배치 수집된 일별 테이블에서

  • 윈도우 함수로 성장률 계산 → 급등 콘텐츠/채널 필터

  • BigQuery ML로 이상치 감지 → 자동 스파이크 알림

• 전혀 복잡한 실시간 파이프라인 없이도, BigQuery만으로 충분히 “뜨는” 콘텐츠를 발견하고 대응할 수 있습니다.

추가로, 경고 임계값 설정이나 대시보드 예시가 필요하시면 알려주세요!