#!/usr/bin/env python3 # -*- coding: utf-8 -*- import os import json import datetime import argparse from google.oauth2.credentials import Credentials from googleapiclient.discovery import build def fetch_and_update_raw_data(date_range_days=28): token_path = "/root/.hermes/profiles/reviewer/google_token.json" if not os.path.exists(token_path): raise FileNotFoundError("Không tìm thấy Google OAuth Token.") with open(token_path, 'r') as f: info = json.load(f) creds = Credentials.from_authorized_user_info(info) # Init APIs gsc_service = build('searchconsole', 'v1', credentials=creds) ga4_service = build('analyticsdata', 'v1beta', credentials=creds) today = datetime.date.today() # Kỳ hiện tại (Current Period) start_date = (today - datetime.timedelta(days=date_range_days)).strftime("%Y-%m-%d") end_date = (today - datetime.timedelta(days=3)).strftime("%Y-%m-%d") # GSC trễ 3 ngày # Kỳ trước (Previous Period - đối sánh tương đương số ngày) prev_end_date = (today - datetime.timedelta(days=date_range_days + 1)).strftime("%Y-%m-%d") prev_start_date = (today - datetime.timedelta(days=date_range_days * 2)).strftime("%Y-%m-%d") site_url = "https://www.greenleafvietnam.com/" property_id = "481665400" print(f"Calling APIs for Current: {start_date} to {end_date} | Previous: {prev_start_date} to {prev_end_date}...") # ------------------ GSC (Google Search Console) ------------------ # # 1. Fetch Queries (Kỳ hiện tại) req_queries = {'startDate': start_date, 'endDate': end_date, 'dimensions': ['query'], 'rowLimit': 100} res_queries = gsc_service.searchanalytics().query(siteUrl=site_url, body=req_queries).execute() gsc_queries = [] for r in res_queries.get('rows', []): gsc_queries.append({ "query": r['keys'][0], "clicks": r['clicks'], "impressions": r['impressions'], "ctr": f"{r['ctr'] * 100:.1f}%", "position": round(r['position'], 1) }) # 2. Fetch Daily Stats (Kỳ hiện tại) req_daily = {'startDate': start_date, 'endDate': end_date, 'dimensions': ['date'], 'rowLimit': 1000} res_daily = gsc_service.searchanalytics().query(siteUrl=site_url, body=req_daily).execute() gsc_daily_current = [] for r in sorted(res_daily.get('rows', []), key=lambda x: x['keys'][0]): gsc_daily_current.append({ "date": r['keys'][0], "clicks": r['clicks'], "impressions": r['impressions'], "ctr": r['ctr'], "position": round(r['position'], 1) }) # 3. Fetch Daily Stats (Kỳ trước - previous) req_daily_prev = {'startDate': prev_start_date, 'endDate': prev_end_date, 'dimensions': ['date'], 'rowLimit': 1000} res_daily_prev = gsc_service.searchanalytics().query(siteUrl=site_url, body=req_daily_prev).execute() gsc_daily_previous = [] for r in sorted(res_daily_prev.get('rows', []), key=lambda x: x['keys'][0]): gsc_daily_previous.append({ "date": r['keys'][0], "clicks": r['clicks'], "impressions": r['impressions'], "ctr": r['ctr'], "position": round(r['position'], 1) }) # 4. Fetch GSC Landing Pages req_pages = {'startDate': start_date, 'endDate': end_date, 'dimensions': ['page'], 'rowLimit': 300} res_pages = gsc_service.searchanalytics().query(siteUrl=site_url, body=req_pages).execute() gsc_pages = [] for r in res_pages.get('rows', []): gsc_pages.append({ "page": r['keys'][0], "clicks": r['clicks'], "impressions": r['impressions'], "ctr": r['ctr'], "position": round(r['position'], 1) }) # ------------------ GA4 (Google Analytics 4) ------------------ # print(f"Calling GA4 API (Property: {property_id})...") # 5. Fetch GA4 Daily Sessions (Kỳ hiện tại) req_ga4_daily = { "dateRanges": [{"startDate": start_date, "endDate": end_date}], "dimensions": [{"name": "date"}], "metrics": [{"name": "sessions"}, {"name": "conversions"}], "limit": 1000 } res_ga4_daily = ga4_service.properties().runReport(property=f"properties/{property_id}", body=req_ga4_daily).execute() ga4_daily_current = [] for r in sorted(res_ga4_daily.get('rows', []), key=lambda x: x['dimensionValues'][0]['value']): raw_date = r['dimensionValues'][0]['value'] formatted_date = f"{raw_date[:4]}-{raw_date[4:6]}-{raw_date[6:]}" ga4_daily_current.append({ "date": formatted_date, "sessions": int(r['metricValues'][0]['value']), "conversions": int(r['metricValues'][1]['value']) }) # 6. Fetch GA4 Daily Sessions (Kỳ trước - previous) req_ga4_daily_prev = { "dateRanges": [{"startDate": prev_start_date, "endDate": prev_end_date}], "dimensions": [{"name": "date"}], "metrics": [{"name": "sessions"}, {"name": "conversions"}], "limit": 1000 } res_ga4_daily_prev = ga4_service.properties().runReport(property=f"properties/{property_id}", body=req_ga4_daily_prev).execute() ga4_daily_previous = [] for r in sorted(res_ga4_daily_prev.get('rows', []), key=lambda x: x['dimensionValues'][0]['value']): raw_date = r['dimensionValues'][0]['value'] formatted_date = f"{raw_date[:4]}-{raw_date[4:6]}-{raw_date[6:]}" ga4_daily_previous.append({ "date": formatted_date, "sessions": int(r['metricValues'][0]['value']), "conversions": int(r['metricValues'][1]['value']) }) # 7. Fetch GA4 Channels (Daily Mix for Stacked Chart) req_ga4_channels = { "dateRanges": [{"startDate": start_date, "endDate": end_date}], "dimensions": [{"name": "date"}, {"name": "sessionDefaultChannelGroup"}], "metrics": [{"name": "sessions"}], "limit": 5000 } res_ga4_channels = ga4_service.properties().runReport(property=f"properties/{property_id}", body=req_ga4_channels).execute() ga4_daily_mix = [] for r in res_ga4_channels.get('rows', []): raw_date = r['dimensionValues'][0]['value'] formatted_date = f"{raw_date[:4]}-{raw_date[4:6]}-{raw_date[6:]}" ga4_daily_mix.append({ "date": formatted_date, "channel": r['dimensionValues'][1]['value'], "sessions": int(r['metricValues'][0]['value']) }) # 8. Fetch GA4 Channel Summary Table (Kỳ hiện tại) req_ga4_ch_sum = { "dateRanges": [{"startDate": start_date, "endDate": end_date}], "dimensions": [{"name": "sessionDefaultChannelGroup"}], "metrics": [{"name": "sessions"}, {"name": "engagedSessions"}], "limit": 100 } res_ga4_ch_sum = ga4_service.properties().runReport(property=f"properties/{property_id}", body=req_ga4_ch_sum).execute() ga4_channels_current = [] for r in res_ga4_ch_sum.get('rows', []): sess = int(r['metricValues'][0]['value']) eng = int(r['metricValues'][1]['value']) ga4_channels_current.append({ "channel": r['dimensionValues'][0]['value'], "sessions": sess, "engaged_sessions": eng, "engagement_rate": eng / sess if sess > 0 else 0 }) # 8.5 Fetch GA4 Channel Summary Table (Kỳ trước - previous) req_ga4_ch_sum_prev = { "dateRanges": [{"startDate": prev_start_date, "endDate": prev_end_date}], "dimensions": [{"name": "sessionDefaultChannelGroup"}], "metrics": [{"name": "sessions"}, {"name": "engagedSessions"}], "limit": 100 } res_ga4_ch_sum_prev = ga4_service.properties().runReport(property=f"properties/{property_id}", body=req_ga4_ch_sum_prev).execute() ga4_channels_previous = [] for r in res_ga4_ch_sum_prev.get('rows', []): sess = int(r['metricValues'][0]['value']) eng = int(r['metricValues'][1]['value']) ga4_channels_previous.append({ "channel": r['dimensionValues'][0]['value'], "sessions": sess, "engaged_sessions": eng, "engagement_rate": eng / sess if sess > 0 else 0 }) # 9. Fetch GA4 Landing Pages Table # 9. Fetch GA4 Landing Pages Table req_ga4_landing = { "dateRanges": [{"startDate": start_date, "endDate": end_date}], "dimensions": [{"name": "landingPagePlusQueryString"}, {"name": "sessionDefaultChannelGroup"}], "metrics": [{"name": "sessions"}, {"name": "engagedSessions"}], "limit": 300 } res_ga4_landing = ga4_service.properties().runReport(property=f"properties/{property_id}", body=req_ga4_landing).execute() ga4_landing_pages = [] for r in res_ga4_landing.get('rows', []): sess = int(r['metricValues'][0]['value']) eng = int(r['metricValues'][1]['value']) ga4_landing_pages.append({ "page": r['dimensionValues'][0]['value'], "source_medium": r['dimensionValues'][1]['value'], "sessions": sess, "engaged_sessions": eng, "engagement_rate": eng / sess if sess > 0 else 0 }) # 10. Fetch GA4 AI Referrals (GEO data) req_ga4_geo = { "dateRanges": [{"startDate": start_date, "endDate": end_date}], "dimensions": [{"name": "sessionSource"}], "metrics": [{"name": "sessions"}, {"name": "conversions"}], "dimensionFilter": { "filter": { "fieldName": "sessionSource", "stringFilter": { "matchType": "CONTAINS", "value": "ai" # Lọc các nguồn có chứa chữ 'ai' hoặc 'chatgpt' hoặc 'perplexity' } } }, "limit": 100 } # Thêm fallback lọc thủ công các domain AI phổ biến nếu filter string không đủ rộng req_ga4_geo_broad = { "dateRanges": [{"startDate": start_date, "endDate": end_date}], "dimensions": [{"name": "landingPagePlusQueryString"}, {"name": "sessionSource"}], "metrics": [{"name": "sessions"}, {"name": "conversions"}, {"name": "engagedSessions"}], "limit": 5000 } res_ga4_broad = ga4_service.properties().runReport(property=f"properties/{property_id}", body=req_ga4_geo_broad).execute() ai_whitelist = [ "chatgpt.com", "perplexity.ai", "claude.ai", "openai.com", "gemini.google.com", "copilot.microsoft.com", "android-app://com.perplexity.aria" ] ga4_geo_referrals = [] total_ai_sessions = 0 for r in res_ga4_broad.get('rows', []): page = r['dimensionValues'][0]['value'] src = r['dimensionValues'][1]['value'].lower() is_ai = False matched_source = src for ai_domain in ai_whitelist: if ai_domain in src: is_ai = True matched_source = ai_domain break if is_ai: sess = int(r['metricValues'][0]['value']) conv = int(r['metricValues'][1]['value']) eng = int(r['metricValues'][2]['value']) total_ai_sessions += sess ga4_geo_referrals.append({ "page": page, "source": matched_source, "sessions": sess, "conversions": conv, "engagement_rate": eng / sess if sess > 0 else 0 }) # 9.5 Fetch GSC Page-Query mapping to find top query per page req_page_query = { 'startDate': start_date, 'endDate': end_date, 'dimensions': ['page', 'query'], 'rowLimit': 5000 } res_pq = gsc_service.searchanalytics().query(siteUrl=site_url, body=req_page_query).execute() # Gom nhóm các query theo page, chọn query có clicks nhiều nhất (hoặc impressions nếu clicks = 0) page_query_map = {} for r in res_pq.get('rows', []): p = r['keys'][0] q = r['keys'][1] cl = r['clicks'] im = r['impressions'] if p not in page_query_map: page_query_map[p] = {"query": q, "clicks": cl, "impressions": im} else: # Ưu tiên query có clicks nhiều hơn, hoặc impressions nhiều hơn if cl > page_query_map[p]["clicks"] or (cl == page_query_map[p]["clicks"] and im > page_query_map[p]["impressions"]): page_query_map[p] = {"query": q, "clicks": cl, "impressions": im} # Format mapping sang dạng tương đối để lưu JSON gsc_page_top_query = {} for p, val in page_query_map.items(): # Clean page URL clean_p = p.replace('https://www.greenleafvietnam.com', '') if clean_p == '': clean_p = '/' gsc_page_top_query[clean_p] = val["query"] # 11. Fetch GA4 AI Daily Trend (Current Period) req_ga4_geo_daily = { "dateRanges": [{"startDate": start_date, "endDate": end_date}], "dimensions": [{"name": "date"}, {"name": "sessionSource"}], "metrics": [{"name": "sessions"}], "limit": 10000 } res_ga4_geo_daily = ga4_service.properties().runReport(property=f"properties/{property_id}", body=req_ga4_geo_daily).execute() # 12. Fetch GA4 AI Daily Trend (Previous Period) req_ga4_geo_daily_prev = { "dateRanges": [{"startDate": prev_start_date, "endDate": prev_end_date}], "dimensions": [{"name": "date"}, {"name": "sessionSource"}], "metrics": [{"name": "sessions"}], "limit": 10000 } res_ga4_geo_daily_prev = ga4_service.properties().runReport(property=f"properties/{property_id}", body=req_ga4_geo_daily_prev).execute() # Gom nhóm theo ngày ai_whitelist = ["chatgpt.com", "perplexity.ai", "claude.ai", "openai.com", "gemini.google.com", "copilot.microsoft.com", "android-app://com.perplexity.aria"] def process_daily_geo(report_res): daily_map = {} for r in report_res.get('rows', []): dt = r['dimensionValues'][0]['value'] src = r['dimensionValues'][1]['value'].lower() sess = int(r['metricValues'][0]['value']) is_ai = any(ai in src for ai in ai_whitelist) if is_ai: daily_map[dt] = daily_map.get(dt, 0) + sess # Sắp xếp theo ngày tăng dần sorted_daily = [{"date": k, "sessions": v} for k, v in sorted(daily_map.items())] return sorted_daily ga4_geo_daily_current = process_daily_geo(res_ga4_geo_daily) ga4_geo_daily_previous = process_daily_geo(res_ga4_geo_daily_prev) # ------------------ GHI ĐÈ FILE ------------------ # raw_data_path = "/opt/ai-os/products/ceo/projects/GLV/seo-dashboard-app/public/seo_raw_data.json" with open(raw_data_path, "r", encoding="utf-8") as f: data = json.load(f) data["current_period"]["from"] = start_date data["current_period"]["to"] = end_date # GSC data["gsc_queries"] = gsc_queries data["gsc_daily_current"] = gsc_daily_current data["gsc_daily_previous"] = gsc_daily_previous data["gsc_pages"] = gsc_pages # GA4 data["ga4_daily_current"] = ga4_daily_current data["ga4_daily_previous"] = ga4_daily_previous data["ga4_daily_mix"] = ga4_daily_mix data["ga4_channels_current"] = ga4_channels_current data["ga4_channels_previous"] = ga4_channels_previous data["ga4_landing_pages"] = ga4_landing_pages data["ga4_geo_referrals"] = ga4_geo_referrals data["total_ai_sessions"] = total_ai_sessions data["ga4_geo_daily_current"] = ga4_geo_daily_current data["ga4_geo_daily_previous"] = ga4_geo_daily_previous data["gsc_page_top_query"] = gsc_page_top_query with open(raw_data_path, "w", encoding="utf-8") as f: json.dump(data, f, ensure_ascii=False, indent=2) print(f"-> Đã trích xuất thành công {len(gsc_daily_current)} mốc current và {len(gsc_daily_previous)} mốc previous từ API!") return data if __name__ == "__main__": parser = argparse.ArgumentParser() parser.add_argument("--days", type=int, default=28, help="Số ngày trích xuất dữ liệu") args = parser.parse_args() fetch_and_update_raw_data(date_range_days=args.days)