GitHubActionsでGA4データを取得してLaravelに自動保存してみた1

{
  "site_id": 1,
  "month": "2025-09-01",

  "domain": "example.com",
  "page_path": "/about",
  "page_path_hash": "sha256('/about')",

  "active_users": 1234,
  "sessions": 1600,
  "screen_page_views": 2800,
  "engaged_sessions": 900,
  "engagement_rate": 0.5625,
  "avg_session_duration_sec": 65.3,

  "raw_json": {
    "property_id": "123456789",
    "url": "https://example.com/about",
    "source": "ga4",
    "month_input": "2025-09",
    "note": "この月の集計サマリ。必要なら他指標も追加。"
  }
}

name: GA4 Monthly Report CI to API

on:
  workflow_dispatch:
    inputs:
      month:
        description: "対象月 (YYYY-MM、省略時は実行月)"
        required: false
  #schedule:
  #  - cron: "10 18 1 * *"   # UTC基準 = JSTで毎月2日 03:10

jobs:
  prepare:
    runs-on: ubuntu-latest
    outputs:
      matrix: ${{ steps.read.outputs.matrix }}
    steps:
      - uses: actions/checkout@v4
      - id: read
        run: |
          echo "matrix=$(jq -c . ci/config/ga4_sites.json)" >> "$GITHUB_OUTPUT"

  run-report:
    needs: prepare
    runs-on: ubuntu-latest
    timeout-minutes: 10
    strategy:
      matrix: ${{ fromJSON(needs.prepare.outputs.matrix) }}

    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-python@v5
        with:
          python-version: "3.12"

      - name: 必要なライブラリをインストール
        run: |
          python -m pip install --upgrade pip
          pip install google-analytics-data pandas requests

      - name: GA4のキーを書き込む
        run: |
          echo '${{ secrets[matrix.key_secret_name] }}' > ga4-key.json

      - name: GA4からデータを取得してLaravelへ送信（Python内でPOST）
        env:
          KEY_PATH:   ga4-key.json
          PROPERTY_ID: ${{ matrix.property_id }}
          URL:         ${{ matrix.url }}
          DOMAIN:      ${{ matrix.domain }}
          SITE_ID:     ${{ matrix.site_id }}
          MONTH:       ${{ github.event.inputs.month }}

          # Laravel API
          API_BASE:    ${{ secrets.APP_API_BASE }}
          JOB_TOKEN:   ${{ secrets.JOBS_TOKEN }}

        run: |
          if [ -z "${SITE_ID:-}" ]; then export SITE_ID=1; fi
          python ci/scripts/ga4_report_to_api.py

{
  "include": [
    { "property_id": "123456789", "url": "https://hogehoge1.com", "domain": "hogehoge1", "key_secret_name": "GA4_KEY_JSON_SITE", "SITE_ID": "1"},
    { "property_id": "987654321", "url": "https://hogehoge2.com", "domain": "hogehoge2", "key_secret_name": "GA4_KEY_JSON_SITE", "SITE_ID": "2"}
  ]
}

#!/usr/bin/env python3

# =========================================================
#
# GA4からデータを取得してLaravelへデータを渡す
#
# =========================================================

import os
import sys
from datetime import datetime, timedelta
from calendar import monthrange

import requests
import pandas as pd
from google.oauth2 import service_account
from google.analytics.data_v1beta import BetaAnalyticsDataClient
from google.analytics.data_v1beta.types import (
    DateRange, Metric, Dimension, RunReportRequest, Filter, FilterExpression
)

# ========= 設定 =========
KEY_PATH    = os.getenv("KEY_PATH", "ga4-key.json")
PROPERTY_ID = os.getenv("PROPERTY_ID")
URL         = os.getenv("URL", "")
DOMAIN      = os.getenv("DOMAIN", "report")
MONTH_STR   = os.getenv("MONTH", "").strip()

if not PROPERTY_ID:
    print("ERROR: PROPERTY_ID is required")
    sys.exit(1)

# ========= 認証 =========
credentials = service_account.Credentials.from_service_account_file(KEY_PATH)
client = BetaAnalyticsDataClient(credentials=credentials)

# ========= 日付範囲 =========
def month_range(year: int, month: int):
    start = datetime(year, month, 1)
    last_day = monthrange(year, month)[1]
    end = datetime(year, month, last_day)
    return start.strftime("%Y-%m-%d"), end.strftime("%Y-%m-%d")

today = datetime.today()
if MONTH_STR:
    base = datetime.strptime(MONTH_STR + "-01", "%Y-%m-%d")
else:
    base = today

this_y, this_m = base.year, base.month
last = (datetime(this_y, this_m, 1) - timedelta(days=1))
last_y, last_m = last.year, last.month

this_month_start, this_month_end = month_range(this_y, this_m)
last_month_start, last_month_end = month_range(last_y, last_m)

# ========= 共通指標（Laravel要件に合わせて sessions を追加） =========
metrics = [
    Metric(name="screenPageViews"),  # 0
    Metric(name="totalUsers"),       # 1
    Metric(name="sessions"),         # 2 ★追加
    Metric(name="engagementRate"),   # 3
    Metric(name="bounceRate"),       # 4
]

def fetch_summary(start_date, end_date):
    request = RunReportRequest(
        property=f"properties/{PROPERTY_ID}",
        dimensions=[],
        metrics=metrics,
        date_ranges=[DateRange(start_date=start_date, end_date=end_date)],
    )
    response = client.run_report(request)
    if not response.rows:
        return {
            "pageviews": 0,
            "users": 0,
            "sessions": 0,            # ★追加
            "engagement_rate": 0.0,
            "bounce_rate": 0,
            "bounce_rate_raw": 0.0,
        }
    mv = response.rows[0].metric_values
    return {
        "pageviews": int(float(mv[0].value or 0)),
        "users": int(float(mv[1].value or 0)),
        "sessions": int(float(mv[2].value or 0)),                  # ★追加
        "engagement_rate": float(mv[3].value or 0.0),
        "bounce_rate": int(round(float(mv[4].value or 0.0) * 100)),
        "bounce_rate_raw": float(mv[4].value or 0.0),
    }

def calc_delta(curr, prev):
    if prev == 0:
        return "N/A"
    return f"{int((curr - prev) / prev * 100)}%"

# ========= レポート生成 =========
this_data = fetch_summary(this_month_start, this_month_end)
last_data = fetch_summary(last_month_start, last_month_end)

summary_df = pd.DataFrame({
    "指標": ["アクセス数", "ユーザー数", "セッション数", "エンゲージメント率（％）", "直帰率"],
    "今月": [
        this_data["pageviews"],
        this_data["users"],
        this_data["sessions"],  # ★追加
        f'{int(this_data["engagement_rate"] * 100)}%',
        f'{this_data["bounce_rate"]}%'
    ],
    "先月": [
        last_data["pageviews"],
        last_data["users"],
        last_data["sessions"],  # ★追加
        f'{int(last_data["engagement_rate"] * 100)}%',
        f'{last_data["bounce_rate"]}%'
    ],
    "前月比": [
        calc_delta(this_data["pageviews"], last_data["pageviews"]),
        calc_delta(this_data["users"], last_data["users"]),
        calc_delta(this_data["sessions"], last_data["sessions"]),  # ★追加
        calc_delta(this_data["engagement_rate"], last_data["engagement_rate"]),
        calc_delta(this_data["bounce_rate_raw"], last_data["bounce_rate_raw"]),
    ]
})

# ========= デバイス別 =========
request = RunReportRequest(
    property=f"properties/{PROPERTY_ID}",
    dimensions=[Dimension(name="deviceCategory")],
    metrics=[Metric(name="activeUsers")],
    date_ranges=[DateRange(start_date=this_month_start, end_date=this_month_end)]
)
response = client.run_report(request)
device_data = {"パソコン": 0, "スマホ": 0}
total_users = 0
for row in response.rows:
    category = row.dimension_values[0].value
    users = int(float(row.metric_values[0].value or 0))
    if category == "desktop":
        device_data["パソコン"] += users
    elif category == "mobile":
        device_data["スマホ"] += users
    total_users += users

device_result = [f"{k}" for k in device_data.keys()]
device_lines = [
    f"{device_data['パソコン']}人（{round(device_data['パソコン'] / total_users * 100) if total_users else 0}%）",
    f"{device_data['スマホ']}人（{round(device_data['スマホ'] / total_users * 100) if total_users else 0}%）"
]

# ========= 流入チャネル =========
request = RunReportRequest(
    property=f"properties/{PROPERTY_ID}",
    dimensions=[Dimension(name="sessionDefaultChannelGroup")],
    metrics=[Metric(name="activeUsers")],
    date_ranges=[DateRange(start_date=this_month_start, end_date=this_month_end)]
)
response = client.run_report(request)
channel_data = {}
channel_total = 0
for row in response.rows:
    key = row.dimension_values[0].value
    val = int(float(row.metric_values[0].value or 0))
    channel_data[key] = val
    channel_total += val

channel_result = [f"{k}" for k in channel_data.keys()]
channel_result_count = [
    f"{int((v / channel_total) * 100)}%" if channel_total else "0%"
    for v in channel_data.values()
]

# ========= 人気ページ TOP3（PVも取得して送信用に整形） =========
request = RunReportRequest(
    property=f"properties/{PROPERTY_ID}",
    dimensions=[Dimension(name="pagePath")],
    metrics=[Metric(name="screenPageViews")],
    date_ranges=[DateRange(start_date=this_month_start, end_date=this_month_end)],
    dimension_filter=FilterExpression(
        not_expression=FilterExpression(
            filter=Filter(
                field_name="pagePath",
                string_filter=Filter.StringFilter(
                    value="/",
                    match_type=Filter.StringFilter.MatchType.EXACT
                )
            )
        )
    ),
    order_bys=[{"metric": {"metric_name": "screenPageViews"}, "desc": True}],
    limit=3
)
response = client.run_report(request)
top_pages_payload = []  # ← API送信用
top_pages_urls = []     # ← テキスト出力用
for row in response.rows:
    path = row.dimension_values[0].value
    pv = int(float(row.metric_values[0].value or 0))
    full = f"{URL}{path}"
    top_pages_urls.append(full)
    top_pages_payload.append({"url": full, "pageviews": pv})

# ========= 出力 =========
print("■ サマリー")
print(summary_df)
print("\n■ デバイス別アクセス")
print("\n".join(device_result))
print("\n■ 流入チャネル別アクセス")
print("\n".join(channel_result))
print("\n■ 人気ページTOP3（トップページ除外）")
print("\n".join(top_pages_urls))

# ========= 保存（従来どおり） =========
os.makedirs("report", exist_ok=True)
out_path = os.path.join("report", f"{DOMAIN}.txt")
with open(out_path, "w", encoding="utf-8") as f:
    copypaste_lines = [
        str(this_data["pageviews"]),
        str(this_data["users"]),
        f"{round(this_data['engagement_rate'] * 100, 1)}",
        f"{round(this_data['bounce_rate_raw'] * 100, 1)}"
    ]
    copypaste_lines2 = [
        f"{round((this_data['pageviews'] - last_data['pageviews']) / last_data['pageviews'] * 100, 1) if last_data['pageviews'] else 'N/A'}",
        f"{round((this_data['users'] - last_data['users']) / last_data['users'] * 100, 1) if last_data['users'] else 'N/A'}",
        f"{round((this_data['engagement_rate'] - last_data['engagement_rate']) / last_data['engagement_rate'] * 100, 1) if last_data['engagement_rate'] else 'N/A'}",
        f"{round((this_data['bounce_rate_raw'] - last_data['bounce_rate_raw']) / last_data['bounce_rate_raw'] * 100, 1) if last_data['bounce_rate_raw'] else 'N/A'}"
    ]
    f.write("\n".join(copypaste_lines))
    f.write("\n-----------------------\n")
    f.write("\n".join(copypaste_lines2))
    f.write("\n-----------------------\n")
    f.write("\n".join(device_result) + "\n\n")
    f.write("\n".join(device_lines))
    f.write("\n-----------------------\n")
    f.write("\n".join(channel_result) + "\n\n")
    f.write("\n".join(channel_result_count))
    f.write("\n-----------------------\n")
    f.write("\n".join(top_pages_urls) + "\n\n")

print(f"\nSaved: {out_path}")

# ========= Laravelへ送信 =========
API_BASE  = os.getenv("API_BASE")
JOB_TOKEN = os.getenv("JOB_TOKEN")
site_id_env = os.getenv("SITE_ID", "").strip()
try:
    SITE_ID = int(site_id_env) if site_id_env else 1
except ValueError:
    print(f"WARNING: invalid SITE_ID='{site_id_env}', fallback to 1")
    SITE_ID = 1

if not API_BASE:
    print("ERROR: API_BASE is required (e.g., https://your-app.example.com/api)")
    sys.exit(1)
if not JOB_TOKEN:
    print("ERROR: JOB_TOKEN is required")
    sys.exit(1)

# Laravelの期待形に整形
devices_payload = {
    "desktop": {"device": "desktop", "users": device_data.get("パソコン", 0)},
    "mobile":  {"device": "mobile",  "users": device_data.get("スマホ", 0)},
}
channels_payload = {
    k: {"channel": k, "users": int(v)}
    for k, v in channel_data.items()
}

payload = {
    "site_id": SITE_ID,
    "month": f"{this_y}-{this_m:02d}-01",
    "summary": this_data,              # sessions を含む
    "devices": devices_payload,        # {desktop:{device,users}, mobile:{...}}
    "channels": channels_payload,      # {name:{channel,users}}
    "top_pages": top_pages_payload,    # [{url, pageviews}]
    "domain": DOMAIN,
    "property_id": PROPERTY_ID,
    "url": URL,
}

# API受けURL
url = f"{API_BASE.rstrip('/')}/ga4"  # routes/api.php: POST /ga4

# 認証ヘッダ
headers = {
    "X-Job-Token": JOB_TOKEN,
    "Content-Type": "application/json",
    "Accept": "application/json",
}

try:
    resp = requests.post(url, json=payload, headers=headers, timeout=30)
    print("POST", url, "->", resp.status_code)
    print(resp.text)
    resp.raise_for_status()
except requests.RequestException as e:
    print("POST failed:", e)
    sys.exit(1)