123 lines
9.0 KiB
Python
123 lines
9.0 KiB
Python
"""Daily cached sample narratives computed from each game's actual stored metrics.
|
|
|
|
This prototype uses deterministic text, not an AI provider. Completed UTC windows
|
|
and structured evidence can later be supplied to the production report generator.
|
|
"""
|
|
from datetime import datetime,timedelta,timezone
|
|
import logging
|
|
import threading
|
|
from psycopg.types.json import Jsonb
|
|
from database import connect
|
|
|
|
REPORT_LOCK=threading.Lock()
|
|
|
|
|
|
def window_metrics(conn,gid,start,end):
|
|
scope={'gid':gid,'start':start,'end':end}
|
|
session=conn.execute('''SELECT count(*)::int sessions,count(ended_at)::int completed,
|
|
avg(extract(epoch FROM ended_at-started_at))::double precision average_session
|
|
FROM sessions WHERE game_id=%(gid)s AND started_at>=%(start)s::date AND started_at<%(end)s::date''',scope).fetchone()
|
|
audience=conn.execute('''WITH active AS(SELECT DISTINCT player_id FROM player_active_days
|
|
WHERE game_id=%(gid)s AND active_day>=%(start)s::date AND active_day<%(end)s::date)
|
|
SELECT count(*)::int active_players,count(*) FILTER(WHERE p.first_seen>=%(start)s::date)::int new_players,
|
|
count(*) FILTER(WHERE p.first_seen<%(start)s::date)::int returning_players FROM active a
|
|
JOIN players p ON p.game_id=%(gid)s AND p.id=a.player_id''',scope).fetchone()
|
|
dau=conn.execute('''SELECT count(*)::double precision/(%(end)s::date-%(start)s::date) average_dau
|
|
FROM player_active_days WHERE game_id=%(gid)s AND active_day>=%(start)s::date AND active_day<%(end)s::date''',scope).fetchone()
|
|
playtime=conn.execute('''SELECT sum(playtime)::double precision total_playtime FROM session_day_playtime
|
|
WHERE game_id=%(gid)s AND active_day>=%(start)s::date AND active_day<%(end)s::date''',scope).fetchone()
|
|
ccu=conn.execute('''SELECT avg(value)::double precision average_ccu,max(value)::double precision peak_ccu,
|
|
count(value)::int known_ccu,count(*)::int reported_ccu FROM game_metrics WHERE game_id=%(gid)s
|
|
AND category='ccu' AND name='concurrent_players' AND recorded_at>=%(start)s::date AND recorded_at<%(end)s::date''',scope).fetchone()
|
|
revenue=conn.execute('''SELECT coalesce(sum(unit_price_robux*quantity),0)::double precision robux,
|
|
count(DISTINCT player_id) FILTER(WHERE unit_price_robux>0)::int payers,
|
|
count(*) FILTER(WHERE player_id IS NULL AND unit_price_robux>0)::int unattributed
|
|
FROM product_sales WHERE game_id=%(gid)s AND purchased_at>=%(start)s::date AND purchased_at<%(end)s::date''',scope).fetchone()
|
|
retention=conn.execute('''SELECT count(*)::int retention_cohort,
|
|
count(*) FILTER(WHERE EXISTS(SELECT 1 FROM sessions s WHERE s.game_id=p.game_id AND s.player_id=p.id
|
|
AND s.started_at>=(p.first_seen AT TIME ZONE 'UTC')::date+1
|
|
AND s.started_at<(p.first_seen AT TIME ZONE 'UTC')::date+2))::int retained
|
|
FROM players p WHERE game_id=%(gid)s AND first_seen>=%(start)s::date-1 AND first_seen<%(end)s::date-1''',scope).fetchone()
|
|
first=conn.execute('''WITH firsts AS(SELECT DISTINCT ON(player_id) started_at,ended_at FROM sessions
|
|
WHERE game_id=%(gid)s ORDER BY player_id,started_at,id)
|
|
SELECT count(ended_at)::int first_known,count(*) FILTER(WHERE ended_at-started_at>=interval '5 minutes')::int first_five
|
|
FROM firsts WHERE started_at>=%(start)s::date AND started_at<%(end)s::date''',scope).fetchone()
|
|
result=dict(session)|dict(audience)|dict(dau)|dict(playtime)|dict(ccu)|dict(revenue)|dict(retention)|dict(first)
|
|
result['retention']=100*result['retained']/result['retention_cohort'] if result['retention_cohort'] else None
|
|
result['first_five_rate']=100*result['first_five']/result['first_known'] if result['first_known'] else None
|
|
result['payer_conversion']=100*result['payers']/result['active_players'] if result['active_players'] and not result['unattributed'] else None
|
|
return result
|
|
|
|
|
|
def number(value,decimals=1):
|
|
if value is None: return 'N/A'
|
|
return f'{value:,.{decimals}f}'.rstrip('0').rstrip('.') if decimals else f'{value:,.0f}'
|
|
|
|
|
|
def change(current,previous):
|
|
if current is None or previous is None: return 'comparison N/A'
|
|
if previous==0: return 'unchanged at zero' if current==0 else 'up from zero'
|
|
delta=100*(current-previous)/previous
|
|
return f'{delta:+.1f}%'
|
|
|
|
|
|
def build_report(conn,gid,days,as_of):
|
|
start=as_of-timedelta(days=days)
|
|
previous_start=start-timedelta(days=days)
|
|
current=window_metrics(conn,gid,start,as_of)
|
|
previous=window_metrics(conn,gid,previous_start,start)
|
|
game=conn.execute('SELECT name FROM games WHERE id=%s',(gid,)).fetchone()
|
|
comparison='previous day' if days==1 else 'preceding seven days'
|
|
summary=[
|
|
{'title':'CCU & audience','text':f"{game['name']} averaged {number(current['average_ccu'])} CCU ({change(current['average_ccu'],previous['average_ccu'])} vs {comparison}), with a sampled peak of {number(current['peak_ccu'],0)}. {number(current['active_players'],0)} players were active ({change(current['active_players'],previous['active_players'])}), starting {number(current['sessions'],0)} sessions. Average DAU was {number(current['average_dau'])}."},
|
|
{'title':'Engagement','text':f"Known playtime totaled {number(current['total_playtime']/3600 if current['total_playtime'] is not None else None)} hours. Completed sessions averaged {number(current['average_session']/60 if current['average_session'] is not None else None)} minutes ({change(current['average_session'],previous['average_session'])}). There were {number(current['new_players'],0)} new players and {number(current['returning_players'],0)} returning players; {number(current['first_five_rate']) + '%' if current['first_five_rate'] is not None else 'N/A'} of first sessions with a reported end reached five minutes."},
|
|
{'title':'Retention & spending','text':f"D1 return was {number(current['retention']) + '%' if current['retention'] is not None else 'N/A'}, based on {number(current['retention_cohort'],0)} players whose next-day observation fell in this period. Reported spending was R$ {number(current['robux'],0)} ({change(current['robux'],previous['robux'])}), with {number(current['payers'],0) if not current['unattributed'] else 'N/A'} paying players and {number(current['payer_conversion']) + '%' if current['payer_conversion'] is not None else 'N/A'} payer conversion."}
|
|
]
|
|
weekday=None
|
|
if days==1:
|
|
weekday_metrics=window_metrics(conn,gid,start-timedelta(days=7),as_of-timedelta(days=7))
|
|
weekday=f"Same weekday last week: CCU {change(current['average_ccu'],weekday_metrics['average_ccu'])}; active players {change(current['active_players'],weekday_metrics['active_players'])}."
|
|
notes=[]
|
|
if current['known_ccu']<days*24: notes.append(f"CCU uses {current['known_ccu']} known hourly samples out of {days*24} expected; peaks are sampled, not continuous.")
|
|
if current['completed']<current['sessions']: notes.append('Unreported session ends are excluded from duration averages and known playtime.')
|
|
if current['retention_cohort'] and current['retention_cohort']<30: notes.append('The D1 cohort is small; daily percentages may vary considerably.')
|
|
report={'gameId':gid,'gameName':game['name'],'periodDays':days,'startDate':start.isoformat(),
|
|
'endDate':(as_of-timedelta(days=1)).isoformat(),'comparisonStart':previous_start.isoformat(),
|
|
'comparisonEnd':(start-timedelta(days=1)).isoformat(),'paragraphs':summary,'weekdayComparison':weekday,
|
|
'notes':notes,'metrics':current,'previousMetrics':previous,'mode':'sample'}
|
|
from summary_reports import extend_report
|
|
return report|extend_report(conn,report,start,as_of)
|
|
|
|
|
|
def reports_for_game(conn,gid,as_of=None):
|
|
as_of=as_of or datetime.now(timezone.utc).date()
|
|
with REPORT_LOCK:
|
|
saved=conn.execute('SELECT period_days,payload,generated_at FROM daily_health_reports WHERE game_id=%s AND report_date=%s ORDER BY period_days',(gid,as_of)).fetchall()
|
|
known={r['period_days'] for r in saved if r['payload'].get('summaryVersion')==4}
|
|
for days in (1,7):
|
|
if days in known: continue
|
|
payload=build_report(conn,gid,days,as_of)
|
|
conn.execute('''INSERT INTO daily_health_reports(game_id,report_date,period_days,payload)
|
|
VALUES(%s,%s,%s,%s) ON CONFLICT(game_id,report_date,period_days)
|
|
DO UPDATE SET payload=excluded.payload,generated_at=now()''',(gid,as_of,days,Jsonb(payload)))
|
|
saved=conn.execute('SELECT period_days,payload,generated_at FROM daily_health_reports WHERE game_id=%s AND report_date=%s ORDER BY period_days',(gid,as_of)).fetchall()
|
|
return {'reports':[r['payload']|{'generatedAt':r['generated_at']} for r in saved]}
|
|
|
|
|
|
def refresh_all_reports():
|
|
with connect() as conn:
|
|
gids=[r['id'] for r in conn.execute('SELECT id FROM games')]
|
|
for gid in gids:
|
|
with connect() as conn: reports_for_game(conn,gid)
|
|
|
|
|
|
def start_daily_reports():
|
|
def worker():
|
|
while True:
|
|
try: refresh_all_reports()
|
|
except Exception: logging.exception('Daily health report refresh failed')
|
|
threading.Event().wait(60)
|
|
thread=threading.Thread(target=worker,name='daily-health-reports',daemon=True)
|
|
thread.start()
|
|
return thread
|