Claude Codeに頼んだらOutlookのKPIレポートが全自動になった話

import requests

# ── 設定 ──────────────────────────────────────────
TENANT_ID     = "YOUR_TENANT_ID"
CLIENT_ID     = "YOUR_CLIENT_ID"
CLIENT_SECRET = "YOUR_CLIENT_SECRET"
SITE_ID       = "YOUR_SHAREPOINT_SITE_ID"
DRIVE_ITEM_ID = "YOUR_EXCEL_FILE_DRIVE_ITEM_ID"
TEAM_ID       = "YOUR_TEAMS_TEAM_ID"
CHANNEL_ID    = "YOUR_CHANNEL_ID"
RECIPIENTS    = ["sales-team@company.com", "manager@company.com"]

def get_graph_token() -> str:
    """Azure ADからGraph APIアクセストークンを取得"""
    url = f"https://login.microsoftonline.com/{TENANT_ID}/oauth2/v2.0/token"
    resp = requests.post(url, data={
        "grant_type":    "client_credentials",
        "client_id":     CLIENT_ID,
        "client_secret":  CLIENT_SECRET,
        "scope":          "https://graph.microsoft.com/.default",
    })
    resp.raise_for_status()
    return resp.json()["access_token"]

import datetime

def fetch_sales_data(token: str) -> dict:
    """
    Graph API経由でSharePointのExcelファイルからKPIデータを取得。
    WorksheetのusedRangeエンドポイントを使い全セルの値を取得する。
    """
    headers = {"Authorization": f"Bearer {token}"}
    base = "https://graph.microsoft.com/v1.0"

    url = (
        f"{base}/sites/{SITE_ID}/drive/items/{DRIVE_ITEM_ID}"
        "/workbook/worksheets('週次KPI')/usedRange"
    )
    resp = requests.get(url, headers=headers)
    resp.raise_for_status()
    raw = resp.json()["values"]  # [[header...], [row1...], ...]

    rows = raw[1:]  # ヘッダー行をスキップ

    # 今週と前週を日付で仕分け
    this_week = {"sales": 0, "deals": 0, "closed": 0}
    last_week = {"sales": 0, "deals": 0, "closed": 0}
    today      = datetime.date.today()
    week_start = today - datetime.timedelta(days=today.weekday())

    for row in rows:
        try:
            row_date = datetime.date.fromisoformat(row[0])
        except (ValueError, TypeError):
            continue
        diff = (week_start - row_date).days
        if   0 <= diff < 7:   # 今週
            this_week["sales"]  += row[1] or 0
            this_week["deals"]  += row[2] or 0
            this_week["closed"] += row[3] or 0
        elif 7 <= diff < 14:  # 前週
            last_week["sales"]  += row[1] or 0
            last_week["deals"]  += row[2] or 0
            last_week["closed"] += row[3] or 0

    close_rate = round(
        this_week["closed"] / max(this_week["deals"], 1) * 100, 1
    )
    lw_rate = round(
        last_week["closed"] / max(last_week["deals"], 1) * 100, 1
    )
    sales_diff = round(
        (this_week["sales"] - last_week["sales"])
        / max(last_week["sales"], 1) * 100, 1
    )

    return {
        "this_week":      this_week,
        "last_week":      last_week,
        "close_rate":     close_rate,
        "lw_close_rate":  lw_rate,
        "sales_diff_pct": sales_diff,
        "week_label": f"{week_start.strftime('%Y/%m/%d')}〜{today.strftime('%m/%d')}",
    }

from anthropic import Anthropic

ANTHROPIC_KEY = "YOUR_ANTHROPIC_API_KEY"

def analyze_data_with_claude(data: dict) -> str:
    """
    Claude APIにKPIサマリーを渡し、ナラティブ分析コメントを生成する。
    マネージャー向けビジネス文体・3〜4文で返却。
    """
    client = Anthropic(api_key=ANTHROPIC_KEY)

    prompt = f"""
あなたは営業コンサルタントです。以下の週次KPIデータを分析し、
マネージャー向けの簡潔なコメント（3〜4文）を日本語で生成してください。
ポジティブな点・注意すべき点・来週への示唆を含めること。

データ:
- 今週売上: ¥{data['this_week']['sales']:,}
- 前週売上: ¥{data['last_week']['sales']:,}（増減 {data['sales_diff_pct']:+.1f}%）
- 新規商談数: {data['this_week']['deals']}件
- 成約率: {data['close_rate']}%（前週 {data['lw_close_rate']}%）

コメントのみを出力し、前置きや説明は不要。
"""

    message = client.messages.create(
        model="claude-opus-4-5",
        max_tokens=300,
        messages=[{"role": "user", "content": prompt}],
    )
    return message.content[0].text.strip()

def format_teams_report(data: dict, insights: str) -> str:
    """TeamsのHTML本文を生成する"""
    diff_sign  = "▲" if data["sales_diff_pct"] >= 0 else "▼"
    diff_color = "#107C10" if data["sales_diff_pct"] >= 0 else "#D83B01"

    return f"""
<h3>📊 週次営業レポート（{data['week_label']}）</h3>
<table border="1" cellpadding="8">
  <tr><th>指標</th><th>今週</th><th>前週</th><th>増減</th></tr>
  <tr>
    <td>売上</td>
    <td><b>¥{data['this_week']['sales']:,}</b></td>
    <td>¥{data['last_week']['sales']:,}</td>
    <td style="color:{diff_color}">{diff_sign} {abs(data['sales_diff_pct']):.1f}%</td>
  </tr>
  <tr>
    <td>新規商談</td>
    <td><b>{data['this_week']['deals']}件</b></td>
    <td>{data['last_week']['deals']}件</td><td>—</td>
  </tr>
  <tr>
    <td>成約率</td>
    <td><b>{data['close_rate']}%</b></td>
    <td>{data['lw_close_rate']}%</td><td>—</td>
  </tr>
</table>
<p><b>🤖 AI所感：</b> {insights}</p>
"""


def post_to_teams(token: str, content: str) -> None:
    """Graph APIでTeamsチャンネルにHTMLメッセージを投稿する"""
    url = (
        f"https://graph.microsoft.com/v1.0"
        f"/teams/{TEAM_ID}/channels/{CHANNEL_ID}/messages"
    )
    resp = requests.post(url,
        headers={
            "Authorization": f"Bearer {token}",
            "Content-Type": "application/json",
        },
        json={"body": {"contentType": "html", "content": content}},
    )
    resp.raise_for_status()
    print("📨 Teams投稿完了")


def send_email_report(
    token: str, html_content: str, subject: str
) -> None:
    """Graph APIでOutlookメールを一斉送信する"""
    sender_upn = "report-bot@company.com"
    url = f"https://graph.microsoft.com/v1.0/users/{sender_upn}/sendMail"

    to_recipients = [
        {"emailAddress": {"address": addr}}
        for addr in RECIPIENTS
    ]
    resp = requests.post(url,
        headers={
            "Authorization": f"Bearer {token}",
            "Content-Type": "application/json",
        },
        json={
            "message": {
                "subject": subject,
                "body": {"contentType": "HTML", "content": html_content},
                "toRecipients": to_recipients,
            },
            "saveToSentItems": "true",
        },
    )
    resp.raise_for_status()
    print(f"✅ 全{len(RECIPIENTS)}名にメール送信完了")

def run_weekly_report() -> None:
    """
    週次レポートのメイン実行関数。
    Windowsタスクスケジューラまたは Linux cron から呼び出す。

    【Linux cron設定例】
      0 8 * * 1  /usr/bin/python3 /opt/reports/weekly_report.py

    【Windows PowerShellタスク登録例】
        $action  = New-ScheduledTaskAction -Execute 'python' `
                   -Argument 'C:\\Reports\\weekly_report.py'
        $trigger = New-ScheduledTaskTrigger -Weekly -DaysOfWeek Monday -At 8am
        Register-ScheduledTask -Action $action -Trigger $trigger `
                               -TaskName 'WeeklyKPIReport'
    """
    print("=== 週次レポート自動生成開始 ===")

    # ① Graph APIトークン取得
    token = get_graph_token()

    # ② SharePointからExcelデータを取得
    print("📊 Excelデータ取得中...")
    data = fetch_sales_data(token)

    # ③ Claude APIでAI分析コメントを生成
    print("🤖 Claude APIで分析コメント生成中...")
    insights = analyze_data_with_claude(data)

    # ④ TeamsとOutlookに同時送信
    teams_content = format_teams_report(data, insights)
    subject = f"【週次営業レポート】{data['week_label']}"

    post_to_teams(token, teams_content)
    send_email_report(token, teams_content, subject)

    print("=== 完了 ===")

if __name__ == "__main__":
    run_weekly_report()