893 lines
35 KiB
Python
893 lines
35 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Pobiera dane z Google Ads API + GA4 za wskazany miesiąc i zapisuje jako JSON.
|
|
|
|
Użycie:
|
|
python scripts/reports/fetch_monthly_report_data.py --customer studio-zoe.pl --month 2026-02
|
|
python scripts/reports/fetch_monthly_report_data.py --customer 3871661050 --month 2026-02 --output output/report.json
|
|
"""
|
|
|
|
import argparse
|
|
import calendar
|
|
import csv
|
|
import json
|
|
import os
|
|
import re
|
|
import sys
|
|
import io
|
|
import tomllib
|
|
from datetime import datetime, timedelta
|
|
from pathlib import Path
|
|
from urllib.parse import parse_qs, urlparse
|
|
|
|
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding="utf-8", errors="replace")
|
|
sys.path.insert(0, str(Path(__file__).parent.parent))
|
|
from lib.gads_client import get_client, get_customer_id, run_query
|
|
import requests
|
|
|
|
ROOT = Path(__file__).parent.parent.parent
|
|
sys.path.insert(0, str(ROOT))
|
|
from src.gads_v2.config import load_env
|
|
|
|
load_env(ROOT / ".env")
|
|
|
|
|
|
def load_client_report_config(domain):
|
|
"""Load scalar report settings for a client from config/clients.toml."""
|
|
config_path = ROOT / "config" / "clients.toml"
|
|
if not config_path.exists():
|
|
return {}
|
|
data = tomllib.loads(config_path.read_text(encoding="utf-8"))
|
|
return data.get("clients", {}).get(domain, {})
|
|
|
|
|
|
def parse_month(month_str):
|
|
"""Parse YYYY-MM to (year, month) and calculate date range."""
|
|
year, month = map(int, month_str.split("-"))
|
|
last_day = calendar.monthrange(year, month)[1]
|
|
start = f"{year}-{month:02d}-01"
|
|
end = f"{year}-{month:02d}-{last_day:02d}"
|
|
return year, month, start, end
|
|
|
|
|
|
def prev_month(year, month):
|
|
"""Calculate previous month's date range."""
|
|
if month == 1:
|
|
py, pm = year - 1, 12
|
|
else:
|
|
py, pm = year, month - 1
|
|
last_day = calendar.monthrange(py, pm)[1]
|
|
start = f"{py}-{pm:02d}-01"
|
|
end = f"{py}-{pm:02d}-{last_day:02d}"
|
|
return py, pm, start, end
|
|
|
|
|
|
def pct_change(current, previous):
|
|
"""Calculate percentage change, handling zero division."""
|
|
if previous == 0:
|
|
return 100.0 if current > 0 else 0.0
|
|
return round(((current - previous) / previous) * 100, 1)
|
|
|
|
|
|
def normalize_header(value):
|
|
value = (value or "").strip().lower()
|
|
replacements = {
|
|
"ą": "a",
|
|
"ć": "c",
|
|
"ę": "e",
|
|
"ł": "l",
|
|
"ń": "n",
|
|
"ó": "o",
|
|
"ś": "s",
|
|
"ź": "z",
|
|
"ż": "z",
|
|
}
|
|
for src, dst in replacements.items():
|
|
value = value.replace(src, dst)
|
|
return re.sub(r"[^a-z0-9]+", "", value)
|
|
|
|
|
|
def parse_money(value):
|
|
text = str(value or "").strip()
|
|
if not text:
|
|
return 0.0
|
|
text = text.replace("PLN", "").replace("zl", "").replace("zł", "")
|
|
text = text.replace("\u00a0", " ").replace(" ", "")
|
|
if "," in text and "." in text:
|
|
text = text.replace(".", "").replace(",", ".")
|
|
elif "," in text:
|
|
text = text.replace(",", ".")
|
|
text = re.sub(r"[^0-9.\-]", "", text)
|
|
return round(float(text), 2) if text else 0.0
|
|
|
|
|
|
def parse_int_value(value):
|
|
return int(round(parse_money(value)))
|
|
|
|
|
|
def parse_history_month(value):
|
|
text = str(value or "").strip()
|
|
if not text:
|
|
return ""
|
|
if re.fullmatch(r"\d{4}-\d{2}", text):
|
|
return text
|
|
if re.fullmatch(r"\d{2}[.-]\d{4}", text):
|
|
month, year = re.split(r"[.-]", text)
|
|
return f"{int(year):04d}-{int(month):02d}"
|
|
if re.fullmatch(r"\d{4}[./-]\d{1,2}[./-]\d{1,2}", text):
|
|
year, month, _day = re.split(r"[./-]", text)
|
|
return f"{int(year):04d}-{int(month):02d}"
|
|
if re.fullmatch(r"\d{1,2}[./-]\d{1,2}[./-]\d{4}", text):
|
|
_day, month, year = re.split(r"[./-]", text)
|
|
return f"{int(year):04d}-{int(month):02d}"
|
|
return text
|
|
|
|
|
|
def month_locative(month):
|
|
names = {
|
|
"01": "styczniu",
|
|
"02": "lutym",
|
|
"03": "marcu",
|
|
"04": "kwietniu",
|
|
"05": "maju",
|
|
"06": "czerwcu",
|
|
"07": "lipcu",
|
|
"08": "sierpniu",
|
|
"09": "wrześniu",
|
|
"10": "październiku",
|
|
"11": "listopadzie",
|
|
"12": "grudniu",
|
|
}
|
|
return names.get(str(month)[5:7], str(month))
|
|
|
|
|
|
def normalize_month_reference(text, month):
|
|
"""Keep SEO worksheet text aligned with the report month when row date matches."""
|
|
target = month_locative(month)
|
|
return re.sub(
|
|
r"^W miesiącu [A-Za-ząćęłńóśźżĄĆĘŁŃÓŚŹŻ]+",
|
|
f"W {target}",
|
|
text,
|
|
count=1,
|
|
)
|
|
|
|
|
|
def parse_sheet_config(sheet_config):
|
|
value = str(sheet_config or "").strip()
|
|
if not value:
|
|
return "", "0"
|
|
if value.startswith("http"):
|
|
parsed = urlparse(value)
|
|
match = re.search(r"/spreadsheets/d/([^/]+)", parsed.path)
|
|
spreadsheet_id = match.group(1) if match else value
|
|
query_gid = parse_qs(parsed.query).get("gid", [None])[0]
|
|
fragment_gid = parse_qs(parsed.fragment).get("gid", [None])[0]
|
|
return spreadsheet_id, query_gid or fragment_gid or ""
|
|
if ":" in value:
|
|
return value.split(":", 1)
|
|
return value, ""
|
|
|
|
|
|
def fetch_sales_history_from_sheet(domain, sheet_config):
|
|
"""Fetch monthly sales history from a public Google Sheet CSV export."""
|
|
spreadsheet_id, gid = parse_sheet_config(sheet_config)
|
|
export_url = f"https://docs.google.com/spreadsheets/d/{spreadsheet_id}/gviz/tq?tqx=out:csv"
|
|
if gid:
|
|
export_url += f"&gid={gid}"
|
|
response = requests.get(export_url, timeout=30)
|
|
response.raise_for_status()
|
|
response.encoding = "utf-8"
|
|
|
|
reader = csv.DictReader(io.StringIO(response.text))
|
|
history = []
|
|
for row in reader:
|
|
normalized = {normalize_header(key): value for key, value in row.items()}
|
|
month = parse_history_month(
|
|
normalized.get("month")
|
|
or normalized.get("miesiac")
|
|
or normalized.get("data")
|
|
or normalized.get("date")
|
|
)
|
|
revenue = parse_money(
|
|
normalized.get("revenue")
|
|
or normalized.get("przychod")
|
|
or normalized.get("przychody")
|
|
or normalized.get("sprzedaz")
|
|
or normalized.get("wartosc")
|
|
)
|
|
transactions = parse_int_value(
|
|
normalized.get("transactions")
|
|
or normalized.get("transakcje")
|
|
or normalized.get("zamowienia")
|
|
or normalized.get("orders")
|
|
)
|
|
if not month or not revenue:
|
|
continue
|
|
aov = parse_money(
|
|
normalized.get("aov")
|
|
or normalized.get("sredniakoszyka")
|
|
or normalized.get("sredniawartosckoszyka")
|
|
or normalized.get("sredniawartosczamowienia")
|
|
)
|
|
if not aov and transactions:
|
|
aov = round(revenue / transactions, 2)
|
|
history.append({
|
|
"month": month,
|
|
"transactions": transactions,
|
|
"revenue": revenue,
|
|
"aov": aov,
|
|
"source": "google_sheet",
|
|
})
|
|
return sorted(history, key=lambda item: item["month"])
|
|
|
|
|
|
def fetch_seo_text_rows_from_sheet(sheet_config, month):
|
|
"""Fetch monthly SEO text rows from a public Google Sheet CSV export."""
|
|
spreadsheet_id, gid = parse_sheet_config(sheet_config)
|
|
export_url = f"https://docs.google.com/spreadsheets/d/{spreadsheet_id}/gviz/tq?tqx=out:csv"
|
|
if gid:
|
|
export_url += f"&gid={gid}"
|
|
response = requests.get(export_url, timeout=30)
|
|
response.raise_for_status()
|
|
response.encoding = "utf-8"
|
|
|
|
reader = csv.DictReader(io.StringIO(response.text))
|
|
rows = []
|
|
for row in reader:
|
|
normalized = {normalize_header(key): value for key, value in row.items()}
|
|
row_month = parse_history_month(
|
|
normalized.get("month")
|
|
or normalized.get("miesiac")
|
|
or normalized.get("data")
|
|
or normalized.get("date")
|
|
)
|
|
text = (
|
|
normalized.get("url")
|
|
or normalized.get("link")
|
|
or normalized.get("tekst")
|
|
or normalized.get("opis")
|
|
or normalized.get("dzialanie")
|
|
or normalized.get("dzialania")
|
|
or ""
|
|
).strip()
|
|
if row_month == month and text:
|
|
rows.append({
|
|
"date": row.get("Data") or row.get("data") or row_month,
|
|
"url": normalize_month_reference(text, month),
|
|
})
|
|
return rows
|
|
|
|
|
|
def apply_sheet_ecommerce(report, sales_history, month, previous_month):
|
|
"""Use Google Sheet sales data for e-commerce KPI cards."""
|
|
by_month = {row["month"]: row for row in sales_history}
|
|
current = by_month.get(month)
|
|
if not current:
|
|
return False
|
|
previous = by_month.get(previous_month, {"transactions": 0, "revenue": 0.0, "aov": 0.0})
|
|
has_previous = previous_month in by_month
|
|
ecommerce = (report.get("ga4") or {}).get("ecommerce") or {}
|
|
ecommerce["current"] = {
|
|
"transactions": current.get("transactions", 0),
|
|
"revenue": current.get("revenue", 0.0),
|
|
"aov": current.get("aov", 0.0),
|
|
}
|
|
ecommerce["previous"] = {
|
|
"transactions": previous.get("transactions", 0),
|
|
"revenue": previous.get("revenue", 0.0),
|
|
"aov": previous.get("aov", 0.0),
|
|
}
|
|
ecommerce["mom_change"] = {
|
|
"transactions_pct": pct_change(ecommerce["current"]["transactions"], ecommerce["previous"]["transactions"]) if has_previous else None,
|
|
"revenue_pct": pct_change(ecommerce["current"]["revenue"], ecommerce["previous"]["revenue"]) if has_previous else None,
|
|
"aov_pct": pct_change(ecommerce["current"]["aov"], ecommerce["previous"]["aov"]) if has_previous else None,
|
|
}
|
|
ecommerce["source"] = "google_sheet"
|
|
if report.get("ga4") is None:
|
|
report["ga4"] = {}
|
|
report["ga4"]["ecommerce"] = ecommerce
|
|
return True
|
|
|
|
|
|
def fetch_google_ads_data(client, customer_id, start_date, end_date):
|
|
"""Fetch campaign metrics for a date range."""
|
|
query = f"""
|
|
SELECT campaign.id, campaign.name, campaign.status,
|
|
campaign.advertising_channel_type,
|
|
metrics.impressions, metrics.clicks,
|
|
metrics.cost_micros, metrics.conversions,
|
|
metrics.conversions_value,
|
|
metrics.ctr, metrics.average_cpc
|
|
FROM campaign
|
|
WHERE segments.date BETWEEN '{start_date}' AND '{end_date}'
|
|
AND campaign.status != 'REMOVED'
|
|
"""
|
|
rows = run_query(client, customer_id, query)
|
|
|
|
campaigns = {}
|
|
for r in rows:
|
|
cid = str(r.campaign.id)
|
|
if cid not in campaigns:
|
|
campaigns[cid] = {
|
|
"id": cid,
|
|
"name": r.campaign.name,
|
|
"status": r.campaign.status.name,
|
|
"type": r.campaign.advertising_channel_type.name,
|
|
"impressions": 0, "clicks": 0, "cost": 0.0,
|
|
"conversions": 0.0, "conversion_value": 0.0,
|
|
"ctr": 0.0, "cpc": 0.0,
|
|
}
|
|
c = campaigns[cid]
|
|
c["impressions"] += r.metrics.impressions
|
|
c["clicks"] += r.metrics.clicks
|
|
c["cost"] += r.metrics.cost_micros / 1_000_000
|
|
c["conversions"] += r.metrics.conversions
|
|
c["conversion_value"] += r.metrics.conversions_value
|
|
|
|
# Calculate derived metrics
|
|
for c in campaigns.values():
|
|
c["cost"] = round(c["cost"], 2)
|
|
c["conversions"] = round(c["conversions"], 1)
|
|
c["conversion_value"] = round(c["conversion_value"], 2)
|
|
c["ctr"] = round((c["clicks"] / c["impressions"] * 100) if c["impressions"] else 0, 2)
|
|
c["cpc"] = round((c["cost"] / c["clicks"]) if c["clicks"] else 0, 2)
|
|
c["cpa"] = round((c["cost"] / c["conversions"]) if c["conversions"] else 0, 2)
|
|
c["roas"] = round((c["conversion_value"] / c["cost"]) if c["cost"] else 0, 2)
|
|
|
|
return list(campaigns.values())
|
|
|
|
|
|
def calc_totals(campaigns):
|
|
"""Sum up totals across campaigns."""
|
|
t = {"impressions": 0, "clicks": 0, "cost": 0.0, "conversions": 0.0, "conversion_value": 0.0}
|
|
for c in campaigns:
|
|
t["impressions"] += c["impressions"]
|
|
t["clicks"] += c["clicks"]
|
|
t["cost"] += c["cost"]
|
|
t["conversions"] += c["conversions"]
|
|
t["conversion_value"] += c.get("conversion_value", 0.0)
|
|
|
|
t["cost"] = round(t["cost"], 2)
|
|
t["conversions"] = round(t["conversions"], 1)
|
|
t["conversion_value"] = round(t["conversion_value"], 2)
|
|
t["ctr"] = round((t["clicks"] / t["impressions"] * 100) if t["impressions"] else 0, 2)
|
|
t["cpc"] = round((t["cost"] / t["clicks"]) if t["clicks"] else 0, 2)
|
|
t["cpa"] = round((t["cost"] / t["conversions"]) if t["conversions"] else 0, 2)
|
|
t["roas"] = round((t["conversion_value"] / t["cost"]) if t["cost"] else 0, 2)
|
|
return t
|
|
|
|
|
|
def fetch_daily_data(client, customer_id, start_date, end_date):
|
|
"""Fetch daily breakdown for charts."""
|
|
query = f"""
|
|
SELECT segments.date,
|
|
metrics.impressions, metrics.clicks, metrics.cost_micros
|
|
FROM campaign
|
|
WHERE segments.date BETWEEN '{start_date}' AND '{end_date}'
|
|
AND campaign.status != 'REMOVED'
|
|
"""
|
|
rows = run_query(client, customer_id, query)
|
|
|
|
daily = {}
|
|
for r in rows:
|
|
d = r.segments.date
|
|
if d not in daily:
|
|
daily[d] = {"date": d, "impressions": 0, "clicks": 0, "cost": 0.0}
|
|
daily[d]["impressions"] += r.metrics.impressions
|
|
daily[d]["clicks"] += r.metrics.clicks
|
|
daily[d]["cost"] += r.metrics.cost_micros / 1_000_000
|
|
|
|
result = sorted(daily.values(), key=lambda x: x["date"])
|
|
for d in result:
|
|
d["cost"] = round(d["cost"], 2)
|
|
return result
|
|
|
|
|
|
def fetch_search_terms(client, customer_id, start_date, end_date, limit=15):
|
|
"""Fetch top search terms by clicks."""
|
|
query = f"""
|
|
SELECT search_term_view.search_term,
|
|
metrics.impressions, metrics.clicks,
|
|
metrics.cost_micros, metrics.conversions
|
|
FROM search_term_view
|
|
WHERE segments.date BETWEEN '{start_date}' AND '{end_date}'
|
|
ORDER BY metrics.clicks DESC
|
|
LIMIT {limit}
|
|
"""
|
|
rows = run_query(client, customer_id, query)
|
|
|
|
terms = []
|
|
for r in rows:
|
|
clicks = r.metrics.clicks
|
|
impressions = r.metrics.impressions
|
|
terms.append({
|
|
"term": r.search_term_view.search_term,
|
|
"impressions": impressions,
|
|
"clicks": clicks,
|
|
"cost": round(r.metrics.cost_micros / 1_000_000, 2),
|
|
"conversions": round(r.metrics.conversions, 1),
|
|
"ctr": round((clicks / impressions * 100) if impressions else 0, 2),
|
|
})
|
|
return terms
|
|
|
|
|
|
def fetch_ga4_data(property_id, start_date, end_date, prev_start, prev_end):
|
|
"""Fetch GA4 data: sessions, users, traffic sources, devices."""
|
|
from google.oauth2.credentials import Credentials
|
|
from google.analytics.data_v1beta import BetaAnalyticsDataClient
|
|
from google.analytics.data_v1beta.types import (
|
|
RunReportRequest, DateRange, Metric, Dimension, OrderBy,
|
|
)
|
|
|
|
credentials = Credentials(
|
|
token=None,
|
|
refresh_token=os.environ["GA4_REFRESH_TOKEN"],
|
|
client_id=os.environ["GOOGLE_ADS_OAUTH2_CLIENT_ID"],
|
|
client_secret=os.environ["GOOGLE_ADS_OAUTH2_CLIENT_SECRET"],
|
|
token_uri="https://oauth2.googleapis.com/token",
|
|
)
|
|
client = BetaAnalyticsDataClient(credentials=credentials)
|
|
prop = f"properties/{property_id}"
|
|
|
|
# 1. Sessions & Users (current + previous month)
|
|
def get_totals(sd, ed):
|
|
resp = client.run_report(RunReportRequest(
|
|
property=prop,
|
|
date_ranges=[DateRange(start_date=sd, end_date=ed)],
|
|
metrics=[
|
|
Metric(name="sessions"),
|
|
Metric(name="totalUsers"),
|
|
Metric(name="newUsers"),
|
|
Metric(name="screenPageViews"),
|
|
Metric(name="averageSessionDuration"),
|
|
Metric(name="bounceRate"),
|
|
],
|
|
))
|
|
row = resp.rows[0] if resp.rows else None
|
|
if not row:
|
|
return {"sessions": 0, "users": 0, "new_users": 0, "pageviews": 0, "avg_duration": 0, "bounce_rate": 0}
|
|
return {
|
|
"sessions": int(row.metric_values[0].value),
|
|
"users": int(row.metric_values[1].value),
|
|
"new_users": int(row.metric_values[2].value),
|
|
"pageviews": int(row.metric_values[3].value),
|
|
"avg_duration": round(float(row.metric_values[4].value), 1),
|
|
"bounce_rate": round(float(row.metric_values[5].value) * 100, 1),
|
|
}
|
|
|
|
current = get_totals(start_date, end_date)
|
|
previous = get_totals(prev_start, prev_end)
|
|
|
|
# 2. Traffic sources
|
|
resp = client.run_report(RunReportRequest(
|
|
property=prop,
|
|
date_ranges=[DateRange(start_date=start_date, end_date=end_date)],
|
|
dimensions=[Dimension(name="sessionSourceMedium")],
|
|
metrics=[Metric(name="sessions")],
|
|
order_bys=[OrderBy(metric=OrderBy.MetricOrderBy(metric_name="sessions"), desc=True)],
|
|
limit=10,
|
|
))
|
|
sources = []
|
|
for row in resp.rows:
|
|
sources.append({
|
|
"source_medium": row.dimension_values[0].value,
|
|
"sessions": int(row.metric_values[0].value),
|
|
})
|
|
|
|
# 3. Devices
|
|
resp = client.run_report(RunReportRequest(
|
|
property=prop,
|
|
date_ranges=[DateRange(start_date=start_date, end_date=end_date)],
|
|
dimensions=[Dimension(name="deviceCategory")],
|
|
metrics=[Metric(name="sessions")],
|
|
order_bys=[OrderBy(metric=OrderBy.MetricOrderBy(metric_name="sessions"), desc=True)],
|
|
))
|
|
devices = []
|
|
for row in resp.rows:
|
|
devices.append({
|
|
"device": row.dimension_values[0].value,
|
|
"sessions": int(row.metric_values[0].value),
|
|
})
|
|
|
|
# 4. Daily sessions (for chart)
|
|
resp = client.run_report(RunReportRequest(
|
|
property=prop,
|
|
date_ranges=[DateRange(start_date=start_date, end_date=end_date)],
|
|
dimensions=[Dimension(name="date")],
|
|
metrics=[Metric(name="sessions"), Metric(name="totalUsers")],
|
|
order_bys=[OrderBy(dimension=OrderBy.DimensionOrderBy(dimension_name="date"))],
|
|
))
|
|
daily_sessions = []
|
|
for row in resp.rows:
|
|
raw = row.dimension_values[0].value
|
|
formatted = f"{raw[:4]}-{raw[4:6]}-{raw[6:]}"
|
|
daily_sessions.append({
|
|
"date": formatted,
|
|
"sessions": int(row.metric_values[0].value),
|
|
"users": int(row.metric_values[1].value),
|
|
})
|
|
|
|
return {
|
|
"current": current,
|
|
"previous": previous,
|
|
"mom_change": {
|
|
"sessions_pct": pct_change(current["sessions"], previous["sessions"]),
|
|
"users_pct": pct_change(current["users"], previous["users"]),
|
|
"new_users_pct": pct_change(current["new_users"], previous["new_users"]),
|
|
"pageviews_pct": pct_change(current["pageviews"], previous["pageviews"]),
|
|
"avg_duration_pct": pct_change(current["avg_duration"], previous["avg_duration"]),
|
|
"bounce_rate_pct": pct_change(current["bounce_rate"], previous["bounce_rate"]),
|
|
},
|
|
"sources": sources,
|
|
"devices": devices,
|
|
"daily": daily_sessions,
|
|
}
|
|
|
|
|
|
def fetch_ga4_ecommerce(property_id, start_date, end_date, prev_start, prev_end):
|
|
"""Fetch GA4 e-commerce data: transactions, revenue, AOV."""
|
|
from google.oauth2.credentials import Credentials
|
|
from google.analytics.data_v1beta import BetaAnalyticsDataClient
|
|
from google.analytics.data_v1beta.types import (
|
|
RunReportRequest, DateRange, Metric, Dimension, OrderBy,
|
|
)
|
|
|
|
credentials = Credentials(
|
|
token=None,
|
|
refresh_token=os.environ["GA4_REFRESH_TOKEN"],
|
|
client_id=os.environ["GOOGLE_ADS_OAUTH2_CLIENT_ID"],
|
|
client_secret=os.environ["GOOGLE_ADS_OAUTH2_CLIENT_SECRET"],
|
|
token_uri="https://oauth2.googleapis.com/token",
|
|
)
|
|
client = BetaAnalyticsDataClient(credentials=credentials)
|
|
prop = f"properties/{property_id}"
|
|
|
|
def get_ecom(sd, ed):
|
|
resp = client.run_report(RunReportRequest(
|
|
property=prop,
|
|
date_ranges=[DateRange(start_date=sd, end_date=ed)],
|
|
metrics=[
|
|
Metric(name="transactions"),
|
|
Metric(name="purchaseRevenue"),
|
|
Metric(name="averagePurchaseRevenue"),
|
|
],
|
|
))
|
|
row = resp.rows[0] if resp.rows else None
|
|
if not row:
|
|
return {"transactions": 0, "revenue": 0.0, "aov": 0.0}
|
|
return {
|
|
"transactions": int(row.metric_values[0].value),
|
|
"revenue": round(float(row.metric_values[1].value), 2),
|
|
"aov": round(float(row.metric_values[2].value), 2),
|
|
}
|
|
|
|
current = get_ecom(start_date, end_date)
|
|
previous = get_ecom(prev_start, prev_end)
|
|
|
|
# Daily revenue chart
|
|
resp = client.run_report(RunReportRequest(
|
|
property=prop,
|
|
date_ranges=[DateRange(start_date=start_date, end_date=end_date)],
|
|
dimensions=[Dimension(name="date")],
|
|
metrics=[Metric(name="purchaseRevenue"), Metric(name="transactions")],
|
|
order_bys=[OrderBy(dimension=OrderBy.DimensionOrderBy(dimension_name="date"))],
|
|
))
|
|
daily_revenue = []
|
|
for row in resp.rows:
|
|
raw = row.dimension_values[0].value
|
|
formatted = f"{raw[:4]}-{raw[4:6]}-{raw[6:]}"
|
|
daily_revenue.append({
|
|
"date": formatted,
|
|
"revenue": round(float(row.metric_values[0].value), 2),
|
|
"transactions": int(row.metric_values[1].value),
|
|
})
|
|
|
|
# Revenue by source
|
|
resp = client.run_report(RunReportRequest(
|
|
property=prop,
|
|
date_ranges=[DateRange(start_date=start_date, end_date=end_date)],
|
|
dimensions=[Dimension(name="sessionSourceMedium")],
|
|
metrics=[Metric(name="purchaseRevenue"), Metric(name="transactions")],
|
|
order_bys=[OrderBy(metric=OrderBy.MetricOrderBy(metric_name="purchaseRevenue"), desc=True)],
|
|
limit=10,
|
|
))
|
|
revenue_by_source = []
|
|
for row in resp.rows:
|
|
revenue_by_source.append({
|
|
"source_medium": row.dimension_values[0].value,
|
|
"revenue": round(float(row.metric_values[0].value), 2),
|
|
"transactions": int(row.metric_values[1].value),
|
|
})
|
|
|
|
# Top products by revenue
|
|
resp = client.run_report(RunReportRequest(
|
|
property=prop,
|
|
date_ranges=[DateRange(start_date=start_date, end_date=end_date)],
|
|
dimensions=[Dimension(name="itemName")],
|
|
metrics=[
|
|
Metric(name="itemRevenue"),
|
|
Metric(name="itemsPurchased"),
|
|
],
|
|
order_bys=[OrderBy(metric=OrderBy.MetricOrderBy(metric_name="itemRevenue"), desc=True)],
|
|
limit=10,
|
|
))
|
|
top_products = []
|
|
for row in resp.rows:
|
|
top_products.append({
|
|
"name": row.dimension_values[0].value,
|
|
"revenue": round(float(row.metric_values[0].value), 2),
|
|
"quantity": int(row.metric_values[1].value),
|
|
})
|
|
|
|
return {
|
|
"current": current,
|
|
"previous": previous,
|
|
"mom_change": {
|
|
"transactions_pct": pct_change(current["transactions"], previous["transactions"]),
|
|
"revenue_pct": pct_change(current["revenue"], previous["revenue"]),
|
|
"aov_pct": pct_change(current["aov"], previous["aov"]),
|
|
},
|
|
"daily": daily_revenue,
|
|
"revenue_by_source": revenue_by_source,
|
|
"top_products": top_products,
|
|
}
|
|
|
|
|
|
def main():
|
|
parser = argparse.ArgumentParser(description="Pobierz dane do raportu miesięcznego")
|
|
parser.add_argument("--customer", required=True, help="Domena lub Google Ads customer ID")
|
|
parser.add_argument("--month", required=True, help="Miesiąc raportu (YYYY-MM)")
|
|
parser.add_argument("--output", help="Ścieżka do pliku JSON")
|
|
parser.add_argument("--ga4-property", help="GA4 Property ID (domyślnie z .env)")
|
|
parser.add_argument("--skip-ga4", action="store_true", help="Pomiń dane GA4")
|
|
args = parser.parse_args()
|
|
|
|
customer_id = get_customer_id(args.customer)
|
|
client = get_client(use_proto_plus=True)
|
|
|
|
year, month, start_date, end_date = parse_month(args.month)
|
|
py, pm, prev_start, prev_end = prev_month(year, month)
|
|
month_names_pl = {
|
|
1: "Styczeń", 2: "Luty", 3: "Marzec", 4: "Kwiecień",
|
|
5: "Maj", 6: "Czerwiec", 7: "Lipiec", 8: "Sierpień",
|
|
9: "Wrzesień", 10: "Październik", 11: "Listopad", 12: "Grudzień",
|
|
}
|
|
|
|
# Resolve domain name for output
|
|
domain = args.customer if not args.customer.replace("-", "").isdigit() else args.customer
|
|
|
|
print(f"Pobieram dane Google Ads: {domain} za {args.month}...")
|
|
|
|
# Google Ads data
|
|
campaigns = fetch_google_ads_data(client, customer_id, start_date, end_date)
|
|
prev_campaigns = fetch_google_ads_data(client, customer_id, prev_start, prev_end)
|
|
totals = calc_totals(campaigns)
|
|
prev_totals = calc_totals(prev_campaigns)
|
|
daily = fetch_daily_data(client, customer_id, start_date, end_date)
|
|
search_terms = fetch_search_terms(client, customer_id, start_date, end_date)
|
|
|
|
mom_change = {
|
|
"impressions_pct": pct_change(totals["impressions"], prev_totals["impressions"]),
|
|
"clicks_pct": pct_change(totals["clicks"], prev_totals["clicks"]),
|
|
"cost_pct": pct_change(totals["cost"], prev_totals["cost"]),
|
|
"conversions_pct": pct_change(totals["conversions"], prev_totals["conversions"]),
|
|
"conversion_value_pct": pct_change(totals["conversion_value"], prev_totals["conversion_value"]),
|
|
"ctr_pct": pct_change(totals["ctr"], prev_totals["ctr"]),
|
|
"cpc_pct": pct_change(totals["cpc"], prev_totals["cpc"]),
|
|
"cpa_pct": pct_change(totals["cpa"], prev_totals["cpa"]),
|
|
"roas_pct": pct_change(totals["roas"], prev_totals["roas"]),
|
|
}
|
|
|
|
report = {
|
|
"client": domain,
|
|
"month": args.month,
|
|
"month_name": month_names_pl[month],
|
|
"year": year,
|
|
"prev_month": f"{py}-{pm:02d}",
|
|
"prev_month_name": month_names_pl[pm],
|
|
"generated_at": datetime.now().isoformat(),
|
|
"google_ads": {
|
|
"campaigns": campaigns,
|
|
"totals": totals,
|
|
"prev_totals": prev_totals,
|
|
"mom_change": mom_change,
|
|
"daily": daily,
|
|
"search_terms": search_terms,
|
|
},
|
|
}
|
|
|
|
# GA4 data
|
|
if not args.skip_ga4:
|
|
ga4_property = args.ga4_property
|
|
if not ga4_property:
|
|
# Try to find GA4 property in .env
|
|
env_key = f"GA4_PROPERTY_ID_{domain}"
|
|
ga4_property = os.environ.get(env_key)
|
|
|
|
if ga4_property:
|
|
print(f"Pobieram dane GA4 (property: {ga4_property})...")
|
|
try:
|
|
ga4 = fetch_ga4_data(ga4_property, start_date, end_date, prev_start, prev_end)
|
|
report["ga4"] = ga4
|
|
print(f" GA4: {ga4['current']['sessions']} sesji, {ga4['current']['users']} uzytkownikow")
|
|
except Exception as e:
|
|
print(f" UWAGA: Blad GA4: {e}")
|
|
report["ga4"] = None
|
|
else:
|
|
print(f" Brak GA4 Property ID w .env ({env_key}) - pomijam GA4")
|
|
report["ga4"] = None
|
|
else:
|
|
report["ga4"] = None
|
|
|
|
# Semstorm SEO data
|
|
semstorm_login = os.environ.get("SEMSTORM_LOGIN", "")
|
|
if semstorm_login:
|
|
print(f"Pobieram dane Semstorm...")
|
|
try:
|
|
sys.path.insert(0, str(Path(__file__).parent))
|
|
from fetch_semstorm_data import fetch_domain_stats
|
|
semstorm = fetch_domain_stats(domain, args.month)
|
|
report["semstorm"] = semstorm
|
|
if semstorm and semstorm.get("current"):
|
|
cur = semstorm["current"]
|
|
print(f" Semstorm: TOP3={cur['top3']}, TOP10={cur['top10']}, TOP50={cur['top50']}, traffic={cur['traffic']}")
|
|
except Exception as e:
|
|
print(f" UWAGA: Blad Semstorm: {e}")
|
|
report["semstorm"] = None
|
|
else:
|
|
report["semstorm"] = None
|
|
|
|
# E-commerce data: Shoper (primary) or GA4 (fallback)
|
|
shoper_key = f"SHOPER_API_URL_{domain}"
|
|
if os.environ.get(shoper_key):
|
|
print(f"Pobieram dane e-commerce ze Shoper...")
|
|
try:
|
|
from fetch_shoper_data import fetch_shoper_ecommerce
|
|
shoper_ecom = fetch_shoper_ecommerce(domain, args.month, f"{py}-{pm:02d}")
|
|
if shoper_ecom and shoper_ecom["current"]["transactions"] > 0:
|
|
# Get revenue_by_source and top_products from GA4
|
|
if report.get("ga4") and ga4_property:
|
|
try:
|
|
ga4_ecom = fetch_ga4_ecommerce(ga4_property, start_date, end_date, prev_start, prev_end)
|
|
if ga4_ecom:
|
|
shoper_ecom["revenue_by_source"] = ga4_ecom.get("revenue_by_source", [])
|
|
shoper_ecom["top_products"] = ga4_ecom.get("top_products", [])
|
|
except Exception as e:
|
|
print(f" UWAGA: GA4 revenue_by_source/top_products: {e}")
|
|
shoper_ecom["revenue_by_source"] = []
|
|
shoper_ecom["top_products"] = []
|
|
if report.get("ga4") is None:
|
|
report["ga4"] = {}
|
|
report["ga4"]["ecommerce"] = shoper_ecom
|
|
cur = shoper_ecom["current"]
|
|
print(f" Shoper: {cur['transactions']} zamówień, {cur['revenue']:.2f} PLN, AOV {cur['aov']:.2f} PLN")
|
|
else:
|
|
if report.get("ga4"):
|
|
report["ga4"]["ecommerce"] = None
|
|
except Exception as e:
|
|
print(f" UWAGA: Blad Shoper: {e}")
|
|
if report.get("ga4"):
|
|
report["ga4"]["ecommerce"] = None
|
|
elif report.get("ga4") and ga4_property:
|
|
print(f"Pobieram dane GA4 e-commerce...")
|
|
try:
|
|
ecom = fetch_ga4_ecommerce(ga4_property, start_date, end_date, prev_start, prev_end)
|
|
if ecom and ecom["current"]["transactions"] > 0:
|
|
report["ga4"]["ecommerce"] = ecom
|
|
cur = ecom["current"]
|
|
print(f" E-commerce (GA4): {cur['transactions']} transakcji, {cur['revenue']:.2f} PLN przychodu")
|
|
else:
|
|
report["ga4"]["ecommerce"] = None
|
|
except Exception as e:
|
|
print(f" UWAGA: Blad GA4 e-commerce: {e}")
|
|
if report.get("ga4"):
|
|
report["ga4"]["ecommerce"] = None
|
|
|
|
# Monthly sales history for chart. Prefer client Google Sheet when configured.
|
|
client_report_config = load_client_report_config(domain)
|
|
sales_history_sheet = client_report_config.get("sales_history_sheet") or os.environ.get(f"GSHEET_SALES_HISTORY_{domain}")
|
|
report_start = os.environ.get(f"REPORT_START_DATE_{domain}")
|
|
if sales_history_sheet:
|
|
try:
|
|
sales_history = fetch_sales_history_from_sheet(domain, sales_history_sheet)
|
|
if apply_sheet_ecommerce(report, sales_history, args.month, f"{py}-{pm:02d}"):
|
|
current_sheet = report["ga4"]["ecommerce"]["current"]
|
|
print(
|
|
f" E-commerce (Google Sheet): {current_sheet['transactions']} transakcji, "
|
|
f"{current_sheet['revenue']:.2f} PLN przychodu"
|
|
)
|
|
filtered = [e for e in sales_history if not report_start or e["month"] >= report_start]
|
|
report["sales_history"] = filtered
|
|
print(f" Historia sprzedaży z Google Sheet: {len(filtered)} miesięcy")
|
|
except Exception as e:
|
|
report["sales_history"] = []
|
|
print(f" UWAGA: Nie udalo sie pobrac historii sprzedazy z Google Sheet: {e}")
|
|
else:
|
|
ecom_data = report.get("ga4", {}).get("ecommerce") if report.get("ga4") else None
|
|
if ecom_data and ecom_data.get("current", {}).get("transactions", 0) > 0:
|
|
history_path = ROOT / "clients" / domain / "sales_history.json"
|
|
history_path.parent.mkdir(parents=True, exist_ok=True)
|
|
sales_history = []
|
|
if history_path.exists():
|
|
with open(history_path, "r", encoding="utf-8") as f:
|
|
sales_history = json.load(f)
|
|
cur_entry = {
|
|
"month": args.month,
|
|
"transactions": ecom_data["current"]["transactions"],
|
|
"revenue": ecom_data["current"]["revenue"],
|
|
"aov": ecom_data["current"]["aov"],
|
|
"source": ecom_data.get("source", "ga4"),
|
|
}
|
|
by_month = {e["month"]: e for e in sales_history}
|
|
by_month[args.month] = cur_entry
|
|
sales_history = sorted(by_month.values(), key=lambda x: x["month"])
|
|
with open(history_path, "w", encoding="utf-8") as f:
|
|
json.dump(sales_history, f, indent=2, ensure_ascii=False)
|
|
filtered = [e for e in sales_history if not report_start or e["month"] >= report_start]
|
|
report["sales_history"] = filtered
|
|
print(f" Historia sprzedaży: {len(filtered)} miesięcy zapisanych")
|
|
else:
|
|
report["sales_history"] = []
|
|
|
|
# SEO links from Google Sheets
|
|
seo_links_key = f"GSHEET_SEO_LINKS_{domain}"
|
|
if os.environ.get(seo_links_key):
|
|
print(f"Pobieram linki SEO...")
|
|
try:
|
|
from fetch_seo_links import fetch_seo_links, fetch_seo_activities
|
|
seo_links = fetch_seo_links(domain, args.month)
|
|
report["seo_links"] = seo_links or []
|
|
print(f" Linki SEO: {len(report['seo_links'])} w {args.month}")
|
|
# SEO activities (text box)
|
|
seo_act_key = f"GSHEET_SEO_ACTIVITIES_{domain}"
|
|
if os.environ.get(seo_act_key):
|
|
seo_activities = fetch_seo_activities(domain, args.month)
|
|
report["seo_activities"] = seo_activities
|
|
if seo_activities:
|
|
print(f" Działania SEO: {len(seo_activities)} znaków")
|
|
except Exception as e:
|
|
print(f" UWAGA: Blad SEO links: {e}")
|
|
report["seo_links"] = []
|
|
else:
|
|
report["seo_links"] = []
|
|
|
|
# SEO works and links from config/clients.toml override the legacy .env-only fetch above.
|
|
seo_works_sheet = client_report_config.get("seo_works_history_sheet")
|
|
if seo_works_sheet:
|
|
print(f"Pobieram dzialania SEO z config/clients.toml...")
|
|
try:
|
|
seo_work_rows = fetch_seo_text_rows_from_sheet(seo_works_sheet, args.month)
|
|
report["seo_activities"] = "\n\n".join(row["url"] for row in seo_work_rows)
|
|
print(f" Dzialania SEO: {len(seo_work_rows)} wpisow w {args.month}")
|
|
except Exception as e:
|
|
print(f" UWAGA: Blad dzialan SEO: {e}")
|
|
report["seo_activities"] = ""
|
|
|
|
seo_links_sheet = client_report_config.get("seo_links_history_sheet")
|
|
if seo_links_sheet:
|
|
print(f"Pobieram linki SEO z config/clients.toml...")
|
|
try:
|
|
seo_links = fetch_seo_text_rows_from_sheet(seo_links_sheet, args.month)
|
|
report["seo_links"] = seo_links or []
|
|
print(f" Linki SEO: {len(report['seo_links'])} w {args.month}")
|
|
except Exception as e:
|
|
print(f" UWAGA: Blad SEO links: {e}")
|
|
report["seo_links"] = []
|
|
|
|
# Output
|
|
if args.output:
|
|
output_path = Path(args.output)
|
|
else:
|
|
output_path = ROOT / "scripts" / "reports" / "output" / f"{domain}_{args.month}.json"
|
|
|
|
output_path.parent.mkdir(parents=True, exist_ok=True)
|
|
with open(output_path, "w", encoding="utf-8") as f:
|
|
json.dump(report, f, ensure_ascii=False, indent=2)
|
|
|
|
print(f"\nZapisano: {output_path}")
|
|
print(f"Google Ads: {totals['clicks']} klikniec, {totals['conversions']} konwersji, {totals['cost']:.2f} PLN")
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|