📊 퍼포먼스 마케팅/GA 연동 대시보드 (W/Genspark)

/**
 * Google Ads → Google Sheets 자동 수집 스크립트
 * ─────────────────────────────────────────────
 * 위치: Google Ads → 도구 → 일괄 작업 → 스크립트 → + 새 스크립트
 * 실행: 승인 → 미리보기(오류 확인) → 실행 → 매일 오전 6시 스케줄
 *
 * 시트 구조 (자동 생성):
 *   ads_daily_performance : 일자별 광고 성과 raw
 *   fx_rates              : 환율 참조용 (USD→KRW)
 *
 * 필수 설정: 아래 SPREADSHEET_URL 만 본인 시트 URL로 교체
 */

// ═══════════════════════════════════════════════
// 🛠 여기만 수정하세요
// ═══════════════════════════════════════════════
const SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/여기에_시트_URL_입력';

// 수집 기간: LAST_7_DAYS / LAST_14_DAYS / LAST_30_DAYS / THIS_MONTH
const DATE_RANGE = 'LAST_7_DAYS';

// 시트 탭 이름 (원하면 변경)
const SHEET_NAME = 'ads_daily_performance';

// ═══════════════════════════════════════════════
// 메인
// ═══════════════════════════════════════════════
function main() {
  const ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  let sheet = ss.getSheetByName(SHEET_NAME);
  if (!sheet) {
    sheet = ss.insertSheet(SHEET_NAME);
    sheet.appendRow([
      'report_date', 'account_id', 'account_name',
      'campaign_id', 'campaign_name', 'campaign_objective',
      'adset_id', 'adset_name',
      'ad_id', 'ad_name', 'creative_type', 'ad_status',
      'spend', 'impressions', 'reach', 'frequency',
      'clicks', 'link_clicks', 'ctr', 'cpc', 'cpm',
      'purchases', 'purchase_value',
      'add_to_cart', 'initiate_checkout',
      'roas', 'cpa', 'currency', 'source'
    ]);
  }

  const account = AdsApp.currentAccount();
  const accountId = account.getCustomerId();
  const accountName = account.getName();
  const currency = account.getCurrencyCode();

  const report = AdsApp.report(
    `SELECT
       segments.date,
       campaign.id, campaign.name, campaign.advertising_channel_type,
       ad_group.id, ad_group.name,
       ad_group_ad.ad.id, ad_group_ad.ad.name, ad_group_ad.ad.type, ad_group_ad.status,
       metrics.cost_micros, metrics.impressions, metrics.clicks, metrics.ctr,
       metrics.average_cpc, metrics.average_cpm,
       metrics.conversions, metrics.conversions_value
     FROM ad_group_ad
     WHERE segments.date DURING ${DATE_RANGE}
       AND ad_group_ad.status != 'REMOVED'`
  );

  const rows = report.rows();
  const buffer = [];
  while (rows.hasNext()) {
    const r = rows.next();
    const spend = Number(r['metrics.cost_micros']) / 1e6;
    const conversions = Number(r['metrics.conversions']) || 0;
    const revenue = Number(r['metrics.conversions_value']) || 0;
    const clicks = Number(r['metrics.clicks']) || 0;
    const impressions = Number(r['metrics.impressions']) || 0;

    buffer.push([
      r['segments.date'],
      accountId, accountName,
      r['campaign.id'], r['campaign.name'], r['campaign.advertising_channel_type'],
      r['ad_group.id'], r['ad_group.name'],
      r['ad_group_ad.ad.id'], r['ad_group_ad.ad.name'],
      r['ad_group_ad.ad.type'], r['ad_group_ad.status'],
      spend, impressions, 0, 0,
      clicks, clicks, Number(r['metrics.ctr']) || 0,
      Number(r['metrics.average_cpc']) / 1e6, Number(r['metrics.average_cpm']) / 1e6,
      conversions, revenue,
      0, 0,
      spend > 0 ? revenue / spend : 0,
      conversions > 0 ? spend / conversions : 0,
      currency, 'google_ads_script'
    ]);
  }

  if (buffer.length === 0) {
    Logger.log('수집된 데이터가 없습니다.');
    return;
  }

  // 기존 데이터 중 같은 기간(report_date) 삭제 후 append (upsert 대체)
  const existingDates = new Set(buffer.map(r => r[0]));
  const lastRow = sheet.getLastRow();
  if (lastRow > 1) {
    const dateCol = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
    for (let i = dateCol.length - 1; i >= 0; i--) {
      const d = dateCol[i][0];
      const iso = d instanceof Date
        ? Utilities.formatDate(d, 'GMT+9', 'yyyy-MM-dd')
        : String(d);
      if (existingDates.has(iso)) sheet.deleteRow(i + 2);
    }
  }

  sheet.getRange(sheet.getLastRow() + 1, 1, buffer.length, buffer[0].length).setValues(buffer);
  Logger.log(`✅ ${buffer.length}행 업로드 완료 · 계정 ${accountName}`);
}

/**
 * GA4 → Google Sheets 자동 수집 스크립트
 * ─────────────────────────────────────────
 * 위치: 해당 시트에서 확장 프로그램 → Apps Script
 * ⚠️ 필수: 왼쪽 [서비스] + 버튼 → "Google Analytics Data API" 추가
 * 실행 순서:
 *   1) backfill90Days   (과거 90일 채우기)
 *   2) installDailyTrigger (매일 07시 자동 실행 설정)
 *
 * 시트 탭 (자동 생성): ga4_daily
 */

// ═══════════════════════════════════════════════
// 🛠 여기만 수정하세요
// ═══════════════════════════════════════════════
const GA4_PROPERTY_ID = '000000000';  // 예: 366789012 (숫자만)
const SHEET_TAB = 'ga4_daily';

// ═══════════════════════════════════════════════
// 매일 07시 트리거 설치 (딱 한 번만 실행)
// ═══════════════════════════════════════════════
function installDailyTrigger() {
  // 기존 dailySync 트리거 있으면 제거 (중복 방지)
  ScriptApp.getProjectTriggers().forEach(t => {
    if (t.getHandlerFunction() === 'dailySync') ScriptApp.deleteTrigger(t);
  });
  ScriptApp.newTrigger('dailySync')
    .timeBased().atHour(7).everyDays(1)
    .create();
  Logger.log('✅ 매일 오전 7시 dailySync 트리거 설치 완료');
}

// ═══════════════════════════════════════════════
// 매일 실행: 어제 데이터만 upsert
// ═══════════════════════════════════════════════
function dailySync() {
  const y = new Date(); y.setDate(y.getDate() - 1);
  const iso = Utilities.formatDate(y, 'GMT+9', 'yyyy-MM-dd');
  fetchRange(iso, iso);
}

// ═══════════════════════════════════════════════
// 초기 백필: 최근 90일 (딱 한 번만 실행)
// ═══════════════════════════════════════════════
function backfill90Days() {
  const end = new Date(); end.setDate(end.getDate() - 1);
  const start = new Date(); start.setDate(start.getDate() - 90);
  fetchRange(
    Utilities.formatDate(start, 'GMT+9', 'yyyy-MM-dd'),
    Utilities.formatDate(end, 'GMT+9', 'yyyy-MM-dd')
  );
}

// ═══════════════════════════════════════════════
// 코어: GA4 Data API 호출 → 시트 upsert
// ═══════════════════════════════════════════════
function fetchRange(startDate, endDate) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(SHEET_TAB);
  if (!sheet) {
    sheet = ss.insertSheet(SHEET_TAB);
    sheet.appendRow([
      'report_date', 'source_medium', 'campaign',
      'sessions', 'active_users', 'new_users',
      'engaged_sessions', 'avg_engagement_time',
      'conversions', 'purchase_revenue', 'transactions',
      'imported_at'
    ]);
  }

  const request = {
    dateRanges: [{ startDate, endDate }],
    dimensions: [
      { name: 'date' },
      { name: 'sessionSourceMedium' },
      { name: 'sessionCampaignName' }
    ],
    metrics: [
      { name: 'sessions' }, { name: 'activeUsers' }, { name: 'newUsers' },
      { name: 'engagedSessions' }, { name: 'userEngagementDuration' },
      { name: 'conversions' }, { name: 'purchaseRevenue' }, { name: 'transactions' }
    ],
    limit: 100000
  };

  const response = AnalyticsData.Properties.runReport(request, `properties/${GA4_PROPERTY_ID}`);
  const rows = response.rows || [];
  if (rows.length === 0) {
    Logger.log(`⚠️ ${startDate}~${endDate} 조회 결과 0건`);
    return;
  }

  const now = Utilities.formatDate(new Date(), 'GMT+9', 'yyyy-MM-dd HH:mm:ss');
  const buffer = rows.map(r => {
    const d = r.dimensionValues, m = r.metricValues;
    const dateISO = `${d[0].value.slice(0,4)}-${d[0].value.slice(4,6)}-${d[0].value.slice(6,8)}`;
    const sessions = Number(m[0].value) || 0;
    const engTime = Number(m[4].value) || 0;
    return [
      dateISO, d[1].value, d[2].value,
      sessions, Number(m[1].value) || 0, Number(m[2].value) || 0,
      Number(m[3].value) || 0, sessions > 0 ? engTime / sessions : 0,
      Number(m[5].value) || 0, Number(m[6].value) || 0, Number(m[7].value) || 0,
      now
    ];
  });

  // upsert: 같은 (date, source_medium, campaign) 조합 삭제 후 append
  const keys = new Set(buffer.map(r => `${r[0]}|${r[1]}|${r[2]}`));
  const lastRow = sheet.getLastRow();
  if (lastRow > 1) {
    const existing = sheet.getRange(2, 1, lastRow - 1, 3).getValues();
    for (let i = existing.length - 1; i >= 0; i--) {
      const d = existing[i][0];
      const iso = d instanceof Date
        ? Utilities.formatDate(d, 'GMT+9', 'yyyy-MM-dd')
        : String(d);
      const k = `${iso}|${existing[i][1]}|${existing[i][2]}`;
      if (keys.has(k)) sheet.deleteRow(i + 2);
    }
  }

  sheet.getRange(sheet.getLastRow() + 1, 1, buffer.length, buffer[0].length).setValues(buffer);
  Logger.log(`✅ ${buffer.length}행 · ${startDate}~${endDate}`);
}