367 lines
25 KiB
Python
367 lines
25 KiB
Python
"""Read-only PostgreSQL analytics for the HTML development UI."""
|
|
from collections import Counter, defaultdict
|
|
from datetime import datetime, timedelta, timezone
|
|
import math
|
|
import statistics
|
|
from database import connect
|
|
from metrics_api import engagement, monetization, gamepasses
|
|
from player_records import record_page, player_inventory
|
|
from prompt_metrics import prompt_analytics
|
|
from health_reports import reports_for_game
|
|
from data_quality import quality_log
|
|
|
|
|
|
class ApiError(Exception):
|
|
def __init__(self, status, message):
|
|
self.status, self.message = status, message
|
|
|
|
|
|
def integer(params, key, default, maximum=1000000000):
|
|
try:
|
|
value = int(params.get(key, [str(default)])[0])
|
|
except (ValueError, TypeError):
|
|
raise ApiError(400, f"Invalid {key}.")
|
|
if not 1 <= value <= maximum:
|
|
raise ApiError(400, f"Invalid {key}.")
|
|
return value
|
|
|
|
|
|
def rows(conn, query, args=()):
|
|
return conn.execute(query, args).fetchall()
|
|
|
|
|
|
def one(conn, query, args=()):
|
|
return conn.execute(query, args).fetchone()
|
|
|
|
|
|
def label(day):
|
|
return f"{day:%b} {day.day}"
|
|
|
|
|
|
def game(conn, game_id):
|
|
row = one(conn, "SELECT id,name,roblox_experience_id,timezone,properties FROM games WHERE id=%s", (game_id,))
|
|
if not row:
|
|
raise ApiError(404, "Game not found.")
|
|
return {"id":row["id"],"name":row["name"],"robloxExperienceId":row["roblox_experience_id"],"timezone":row["timezone"],"properties":row["properties"]}
|
|
|
|
|
|
def fields_templates(conn, game_id):
|
|
fields = rows(conn,"SELECT key,label,format,source FROM player_fields WHERE game_id=%s ORDER BY key",(game_id,))
|
|
templates = {r["key"]:{"label":r["label"],"columns":r["columns"]} for r in rows(conn,"SELECT key,label,columns FROM column_templates WHERE game_id=%s ORDER BY key",(game_id,))}
|
|
templates["all"]={"label":"All values","columns":[f["key"] for f in fields]}
|
|
return fields,templates
|
|
|
|
|
|
def player_values(r):
|
|
now=datetime.now(timezone.utc)
|
|
p=dict(r["latest_data"] or {})
|
|
p.update({"playerId":r["id"],"gameId":r["game_id"],"robloxUserId":r["roblox_user_id"],"name":r["display_name"],
|
|
"id":f"User ID {r['roblox_user_id']}","firstSeen":r["first_seen"],"lastSeenAt":r["last_seen"],"snapshotAt":r["snapshot_at"],
|
|
"firstSeenDays":(now-r["first_seen"]).total_seconds()/86400,
|
|
"lastSeen":round((now-r["last_seen"]).total_seconds()/60,1) if r["last_seen"] else None,
|
|
"sessions":r["session_count"],"playtime":r["playtime"],"avgSession":r["average_session"],
|
|
"medianSession":r["median_session"],"activeDays":r["active_days"],
|
|
"robux":r["robux_recorded"],"purchases":r["products_bought"]})
|
|
p["robuxPerHour"]=round(p["robux"]/(p["playtime"]/3600),2) if p.get("robux") is not None and p["playtime"] else None
|
|
return p
|
|
|
|
|
|
def all_players(conn, gid):
|
|
return [player_values(r) for r in rows(conn,"SELECT * FROM player_statistics WHERE game_id=%s",(gid,))]
|
|
|
|
|
|
def segment_players(players, segment):
|
|
if segment=="all": return players
|
|
if segment=="first": return [p for p in players if p["firstSeenDays"]<=1]
|
|
key={"whales":"robux","playtime":"playtime"}.get(segment)
|
|
if not key: raise ApiError(400,"Unknown player group.")
|
|
known=sorted([p[key] for p in players if p.get(key) is not None and p[key]>0],reverse=True)
|
|
cutoff=known[max(0,math.ceil(len(known)*.2)-1)] if known else None
|
|
return [p for p in players if cutoff is not None and p.get(key) is not None and p[key]>=cutoff]
|
|
|
|
|
|
def player_list(conn,gid,params):
|
|
fields,templates=fields_templates(conn,gid)
|
|
players=all_players(conn,gid)
|
|
ranking=params.get('ranking',['level'])[0]
|
|
if ranking not in {f['key'] for f in fields}: raise ApiError(400,'Unknown ranking.')
|
|
boards=[]
|
|
for key in dict.fromkeys(['robux','playtime','cash',ranking]):
|
|
field=next((f for f in fields if f['key']==key),None)
|
|
if not field: continue
|
|
eligible=[p for p in players if type(p.get(key)) in (int,float)]
|
|
leaders=sorted(eligible,key=lambda p:(-p[key],p['playerId']))[:5]
|
|
boards.append({'key':key,'label':field['label'],'format':field['format'],
|
|
'players':[{'playerId':p['playerId'],'name':p['name'],'robloxUserId':p['robloxUserId'],'value':p[key]} for p in leaders]})
|
|
known_levels=[p["level"] for p in players if p.get("level") is not None]
|
|
known_times=[p["playtime"] for p in players if p["playtime"] is not None]
|
|
metrics={"playerCount":len(players),"activePlayers":sum(p["lastSeen"] is not None and p["lastSeen"]<=7*1440 for p in players),
|
|
"averageLevel":round(statistics.mean(known_levels),1) if known_levels else None,"totalPlaytime":sum(known_times) if known_times else None}
|
|
players=segment_players(players,params.get("segment",["all"])[0])
|
|
needle=params.get("q",[""])[0].strip().lower()
|
|
players=[p for p in players if needle in f"{p['name'] or ''} {p['robloxUserId']}".lower()]
|
|
key=params.get("sort",["level"])[0]
|
|
if key not in {f["key"] for f in fields}: raise ApiError(400,"Unknown player sort field.")
|
|
direction=params.get("direction",["desc"])[0]
|
|
if direction not in ("asc","desc"): raise ApiError(400,"Unknown direction.")
|
|
known=sorted([p for p in players if p.get(key) is not None],key=lambda p:(p[key],p["playerId"]),reverse=direction=="desc")
|
|
players=known+sorted([p for p in players if p.get(key) is None],key=lambda p:p["playerId"])
|
|
size=integer(params,"page_size",20,20)
|
|
page=min(integer(params,"page",1),max(1,math.ceil(len(players)/size)))
|
|
return {"metrics":metrics,"fields":fields,"templates":templates,"leaderboards":boards,"playerRows":players[(page-1)*size:page*size],
|
|
"pagination":{"page":page,"pageSize":size,"total":len(players),"pages":max(1,math.ceil(len(players)/size))},
|
|
"groupDefinitions":{"whales":"Top 20% of known positive Robux spenders","playtime":"Top 20% of known positive playtime","first":"First seen within 24 hours"}}
|
|
|
|
|
|
def sessions(conn,gid,pid=None,limit=50):
|
|
clause,args=("AND s.player_id=%s",(gid,pid,limit)) if pid else ("",(gid,limit))
|
|
return rows(conn,f"""SELECT s.id,s.player_id,p.display_name,p.roblox_user_id,s.started_at,s.ended_at,s.place_name,s.stats,
|
|
extract(epoch FROM s.ended_at-s.started_at)::double precision duration,
|
|
(s.ended_at IS NULL AND s.started_at>now()-interval '6 hours' AND s.started_at<=now()) AS "joinAvailable" FROM sessions s
|
|
JOIN players p ON p.game_id=s.game_id AND p.id=s.player_id WHERE s.game_id=%s {clause}
|
|
ORDER BY s.started_at DESC,s.id DESC LIMIT %s""",args)
|
|
|
|
|
|
def events(conn,gid,pid=None,limit=50):
|
|
clause,args=("AND e.player_id=%s",(gid,pid,limit)) if pid else ("",(gid,limit))
|
|
return rows(conn,f"""SELECT e.id,e.category,e.name,e.player_id,p.roblox_user_id,e.occurred_at,e.properties
|
|
FROM events e LEFT JOIN players p ON p.game_id=e.game_id AND p.id=e.player_id
|
|
WHERE e.game_id=%s {clause} ORDER BY e.occurred_at DESC,e.id DESC LIMIT %s""",args)
|
|
|
|
|
|
def retention(conn,gid,days):
|
|
visits=rows(conn,"SELECT player_id,array_agg(DISTINCT (started_at AT TIME ZONE 'UTC')::date) dates FROM sessions WHERE game_id=%s GROUP BY player_id",(gid,))
|
|
cohorts=defaultdict(list)
|
|
today=datetime.now(timezone.utc).date()
|
|
for row in visits:
|
|
dates=set(row["dates"])
|
|
cohorts[min(dates)].append(dates)
|
|
def rate(offset):
|
|
eligible=[(d,members) for d,members in cohorts.items() if today-timedelta(days=days-1)<=d and d+timedelta(days=offset)<today]
|
|
denominator=sum(len(members) for _,members in eligible)
|
|
count=sum(sum(d+timedelta(days=offset) in dates for dates in members) for d,members in eligible)
|
|
return round(100*count/denominator,1) if denominator else None
|
|
starts=[today-timedelta(days=i) for i in reversed(range(days))]
|
|
grid=[]
|
|
for start in starts:
|
|
members=cohorts.get(start,[])
|
|
values=[round(100*sum(start+timedelta(days=d) in dates for dates in members)/len(members),1)
|
|
if members and start+timedelta(days=d)<today else None for d in range(8)]
|
|
grid.append({"name":label(start),"players":len(members),"values":values})
|
|
mature=[(d,m) for d,m in cohorts.items() if starts[0]<=d and d+timedelta(days=7)<today]
|
|
denominator=sum(len(m) for d,m in mature)
|
|
week=[round(100*sum(sum(d+timedelta(days=offset) in dates for dates in members) for d,members in mature)/denominator,2) if denominator else None for offset in range(1,8)]
|
|
return {"retention":rate(1),"day7":rate(7),"weekRetention":{"labels":[f'Day {d}' for d in range(1,8)],
|
|
"series":[{"name":"Exact-day retention","format":"percent","values":week}],"players":denominator},"retentionGrid":{"columns":[f"Day {d}" for d in range(8)],"rows":grid},
|
|
"labels":[r["name"] for r in grid],"series":[{"name":"Day 1","values":[r["values"][1] for r in grid]},{"name":"Day 7","values":[r["values"][7] for r in grid]}]}
|
|
|
|
|
|
def root_metrics(conn,gid,days):
|
|
latest=rows(conn,"SELECT DISTINCT ON(category,name) category,name,recorded_at,value,properties FROM game_metrics WHERE game_id=%s ORDER BY category,name,recorded_at DESC",(gid,))
|
|
trend=rows(conn,"""SELECT (recorded_at AT TIME ZONE 'UTC')::date AS day,avg(value) value FROM game_metrics
|
|
WHERE game_id=%s AND category='ccu' AND recorded_at>=current_date-(%s-1) GROUP BY day ORDER BY day""",(gid,days))
|
|
return {"rows":latest,"labels":[label(r["day"]) for r in trend],"series":[{"name":"Average CCU","values":[round(r["value"],1) if r["value"] is not None else None for r in trend]}]}
|
|
|
|
|
|
def overview(conn,gid,days,player_ids=None):
|
|
metrics=one(conn,"""SELECT count(*) sessions,count(DISTINCT player_id) players,
|
|
avg(extract(epoch FROM ended_at-started_at))::double precision "avgSession",
|
|
percentile_cont(.5) WITHIN GROUP(ORDER BY extract(epoch FROM ended_at-started_at))::double precision "medianSession",
|
|
count(*) FILTER(WHERE ended_at IS NULL AND started_at>now()-interval '6 hours') active,
|
|
count(DISTINCT player_id) FILTER(WHERE player_id IN (SELECT id FROM players WHERE game_id=%s AND first_seen>=current_date-(%s-1))) "newPlayers"
|
|
FROM sessions WHERE game_id=%s AND started_at>=current_date-(%s-1)
|
|
AND (%s::bigint[] IS NULL OR player_id=ANY(%s))""",(gid,days,gid,days,player_ids,player_ids))
|
|
metrics["returningPlayers"]=metrics["players"]-metrics["newPlayers"]
|
|
metrics.update(one(conn,'SELECT count(*) events,count(DISTINCT name) "eventTypes",max(occurred_at) "latestEvent" FROM events WHERE game_id=%s AND occurred_at>=current_date-(%s-1)',(gid,days)))
|
|
metrics["retention"]=retention(conn,gid,days)["retention"]
|
|
metrics["visits"]=one(conn,"SELECT sum(value) value FROM game_metrics WHERE game_id=%s AND category='visits' AND recorded_at>=current_date-(%s-1)",(gid,days))["value"]
|
|
ccu=one(conn,"SELECT value FROM game_metrics WHERE game_id=%s AND category='ccu' AND name='concurrent_players' ORDER BY recorded_at DESC LIMIT 1",(gid,))
|
|
metrics["ccu"]=ccu["value"] if ccu else None
|
|
daily=rows(conn,"SELECT (started_at AT TIME ZONE 'UTC')::date AS day,count(*) sessions,count(DISTINCT player_id) players FROM sessions WHERE game_id=%s AND started_at>=current_date-(%s-1) AND (%s::bigint[] IS NULL OR player_id=ANY(%s)) GROUP BY day ORDER BY day",(gid,days,player_ids,player_ids))
|
|
lookup={r["day"]:r for r in daily}
|
|
dates=[datetime.now(timezone.utc).date()-timedelta(days=i) for i in reversed(range(days))]
|
|
top=rows(conn,"SELECT name,count(*) count FROM events WHERE game_id=%s AND occurred_at>=current_date-(%s-1) GROUP BY name ORDER BY count DESC LIMIT 7",(gid,days))
|
|
return {"metrics":metrics,"labels":[label(d) for d in dates],"series":[{"name":key.capitalize(),"values":[lookup.get(d,{}).get(key,0) for d in dates]} for key in ("players","sessions")],
|
|
"eventLabels":[r["name"] for r in top],"eventValues":[r["count"] for r in top]}
|
|
|
|
|
|
def event_analytics(conn,gid,days):
|
|
catalog=rows(conn,'SELECT name,array_agg(DISTINCT category ORDER BY category) categories FROM events WHERE game_id=%s GROUP BY name ORDER BY name',(gid,))
|
|
daily=rows(conn,"SELECT name,(occurred_at AT TIME ZONE 'UTC')::date AS day,count(*) total FROM events WHERE game_id=%s AND occurred_at>=current_date-(%s-1) GROUP BY name,day",(gid,days))
|
|
totals={r['name']:r for r in rows(conn,"SELECT name,count(*) total,count(DISTINCT player_id) players,max(occurred_at) latest FROM events WHERE game_id=%s AND occurred_at>=current_date-(%s-1) GROUP BY name",(gid,days))}
|
|
lookup={(r['name'],r['day']):r['total'] for r in daily}
|
|
dates=[datetime.now(timezone.utc).date()-timedelta(days=i) for i in reversed(range(days))]
|
|
summary=[{'name':r['name'],'categories':r['categories'],'total':totals.get(r['name'],{}).get('total',0),
|
|
'players':totals.get(r['name'],{}).get('players',0),'average':totals.get(r['name'],{}).get('total',0)/days,
|
|
'latest':totals.get(r['name'],{}).get('latest')} for r in catalog]
|
|
unique=one(conn,'SELECT count(DISTINCT player_id) players,max(occurred_at) latest FROM events WHERE game_id=%s AND occurred_at>=current_date-(%s-1)',(gid,days))
|
|
return {'metrics':{'events':sum(r['total'] for r in summary),'eventTypes':len(catalog),'players':unique['players'],'latestEvent':unique['latest']},
|
|
'labels':[label(d) for d in dates],
|
|
'series':[{'name':r['name'],'values':[lookup.get((r['name'],d),0) for d in dates]} for r in catalog],
|
|
'eventSummary':summary}
|
|
|
|
|
|
def stage_funnel(runs,definition):
|
|
"""Sequential stage reach; unknown observations never become drop-offs."""
|
|
stages=[('Start',definition['start_event'])]+[('Step',s) for s in definition['configuration'].get('steps',[])]
|
|
if definition.get('end_event'): stages.append(('End',definition['end_event']))
|
|
unknown=sum(r.get('stage_progress') is None for r in runs)
|
|
survivors=[(r['stage_progress'],0) for r in runs if isinstance(r.get('stage_progress'),list)]
|
|
count=len(survivors); previous=count; result=[]
|
|
for index,(kind,event) in enumerate(stages):
|
|
reached=[]
|
|
for progress,cursor in survivors:
|
|
try: reached.append((progress,progress.index(event,cursor)+1))
|
|
except ValueError: pass
|
|
survivors=reached
|
|
current=count if index==0 else len(reached)
|
|
known=True
|
|
result.append({'label':kind,'event':event,'runs':current,
|
|
'reachPercent':100*current/count if count and known else None,
|
|
'dropoffPercent':100*(previous-current)/previous if index and previous and known else None,
|
|
'conversionPercent':100*current/previous if index and previous and known else None,
|
|
'droppedRuns':previous-current if index and known else None,'unknownRuns':unknown if index else 0})
|
|
previous=current
|
|
return result
|
|
|
|
|
|
def activities(conn,gid,params,days):
|
|
definitions=rows(conn,"SELECT id,key,name,start_event,end_event,configuration FROM activity_definitions WHERE game_id=%s ORDER BY id",(gid,))
|
|
if not definitions: return {"definitions":[],"metrics":{},"outcomes":[],"captured":[],"journey":[],"labels":[],"series":[]}
|
|
aid=integer(params,"activity_id",definitions[0]["id"])
|
|
definition=next((d for d in definitions if d["id"]==aid),None)
|
|
if not definition: raise ApiError(404,"Activity does not belong to this game.")
|
|
clause,args="",[gid,aid,days]
|
|
segment=params.get("segment",["all"])[0]
|
|
if segment!="all":
|
|
ids=[p["playerId"] for p in segment_players(all_players(conn,gid),segment)]
|
|
clause="AND player_id=ANY(%s)"
|
|
args.append(ids)
|
|
runs=rows(conn,f"SELECT *,extract(epoch FROM ended_at-started_at)::double precision duration FROM activity_runs WHERE game_id=%s AND definition_id=%s AND ended_at IS NOT NULL AND outcome IS NOT NULL AND started_at>=current_date-(%s-1) {clause} ORDER BY started_at",args)
|
|
completed=[r for r in runs if r["duration"] is not None]
|
|
times=[r["duration"] for r in completed]
|
|
prompts=[r for r in runs if (r["prompt_count"] or 0)>0]
|
|
count=len(runs)
|
|
metrics={"runs":count,"average":statistics.mean(times) if times else None,"median":statistics.median(times) if times else None,
|
|
"completion":round(100*len(completed)/count,1) if count else None,"promptRate":round(100*len(prompts)/count,1) if count else None,
|
|
"purchaseRate":round(100*sum(r["outcome"]=="purchased" for r in runs)/count,1) if count else None,
|
|
"promptPurchaseRate":round(100*sum(r["outcome"]=="purchased" for r in prompts)/len(prompts),1) if prompts else None}
|
|
if not any('prompt' in step or 'purchase' in step for step in definition['configuration'].get('steps',[])):
|
|
metrics.update(promptRate=None,purchaseRate=None,promptPurchaseRate=None)
|
|
outcomes=[]
|
|
for outcome in sorted({r["outcome"] for r in runs},key=lambda v:v or ""):
|
|
members=[r for r in runs if r["outcome"]==outcome]
|
|
durations=[r["duration"] for r in members if r["duration"] is not None]
|
|
outcomes.append({"outcome":outcome,"runs":len(members),"share":round(100*len(members)/count,1),"average":statistics.mean(durations) if durations else None,"median":statistics.median(durations) if durations else None})
|
|
captured=[]
|
|
for key in sorted({k for r in runs for k in r["captured_values"]}):
|
|
values=[r["captured_values"][key] for r in runs if r["captured_values"].get(key) is not None]
|
|
numeric=values and all(isinstance(v,(int,float)) for v in values)
|
|
value=round(statistics.mean(values),2) if numeric else Counter(str(v) for v in values).most_common(1)[0][0] if values else None
|
|
captured.append({"key":key,"value":value,"summary":"Average reported value" if numeric else "Most frequent reported value"})
|
|
daily=defaultdict(list)
|
|
for r in completed: daily[r["started_at"].date()].append(r["duration"])
|
|
dates=sorted(daily)
|
|
journey=stage_funnel(runs,definition)
|
|
return {"definitions":definitions,"definition":definition,"metrics":metrics,"outcomes":outcomes,"captured":captured,"journey":journey,
|
|
"labels":[label(d) for d in dates],"series":[{"name":"Average run duration (seconds)","values":[statistics.mean(daily[d]) for d in dates]}]}
|
|
|
|
|
|
def player_history(conn,gid,pid,fields,params):
|
|
snapshots=rows(conn,"SELECT captured_at,data FROM player_snapshots WHERE game_id=%s AND player_id=%s ORDER BY captured_at,id",(gid,pid))
|
|
history=rows(conn,"""SELECT ps.captured_at,ps.data,st.*,sp.robux,sp.purchases FROM player_snapshots ps
|
|
CROSS JOIN LATERAL(SELECT count(*)::int sessions,
|
|
sum(extract(epoch FROM ended_at-started_at)) FILTER(WHERE ended_at<=ps.captured_at)::double precision playtime,
|
|
avg(extract(epoch FROM ended_at-started_at)) FILTER(WHERE ended_at<=ps.captured_at)::double precision "avgSession",
|
|
percentile_cont(.5) WITHIN GROUP(ORDER BY extract(epoch FROM ended_at-started_at)) FILTER(WHERE ended_at<=ps.captured_at)::double precision "medianSession",
|
|
count(DISTINCT (started_at AT TIME ZONE 'UTC')::date) FILTER(WHERE started_at>=ps.captured_at-interval '30 days')::int "activeDays"
|
|
FROM sessions WHERE game_id=ps.game_id AND player_id=ps.player_id AND started_at<=ps.captured_at) st
|
|
CROSS JOIN LATERAL(SELECT coalesce(sum(unit_price_robux*quantity),0)::double precision robux,
|
|
coalesce(sum(quantity),0)::int purchases FROM product_sales
|
|
WHERE game_id=ps.game_id AND player_id=ps.player_id AND purchased_at<=ps.captured_at AND unit_price_robux>0) sp
|
|
WHERE ps.game_id=%s AND ps.player_id=%s ORDER BY ps.captured_at,ps.id""",(gid,pid))
|
|
supported={'sessions','playtime','avgSession','medianSession','activeDays','robuxPerHour','robux','purchases'}
|
|
graph_fields=[dict(f) for f in fields if f['source']=='snapshot' or f['key'] in supported]
|
|
for key,title,fmt in [('sessions','Sessions','number'),('playtime','Lifetime playtime','duration'),
|
|
('avgSession','Average session','duration'),('medianSession','Median session','duration'),
|
|
('activeDays','Active days (30d)','number'),('robux','Robux spent','robux'),
|
|
('purchases','Products bought','number'),('robuxPerHour','Robux per playtime hour','decimal')]:
|
|
if not any(f['key']==key for f in graph_fields):
|
|
graph_fields.append({'key':key,'label':title,'format':fmt,'source':'derived'})
|
|
known={f['key'] for f in graph_fields}
|
|
for key in sorted({key for s in snapshots for key,value in s['data'].items() if type(value) in (int,float)}-known):
|
|
graph_fields.append({'key':key,'label':key,'format':'decimal','source':'snapshot'})
|
|
for point in history:
|
|
point['values']=dict(point['data'])
|
|
point['values'].update({key:point.get(key) for key in supported if key!='robuxPerHour'})
|
|
point['values']['robuxPerHour']=point['robux']*3600/point['playtime'] if point['playtime'] else None
|
|
selected=[params.get('value',['cash'])[0],params.get('value2',[''])[0]]
|
|
keys=list(dict.fromkeys(key for key in selected if key))
|
|
by_key={f['key']:f for f in graph_fields}
|
|
if not keys or any(key not in by_key for key in keys): raise ApiError(400,'Unknown player history value.')
|
|
return {'snapshots':snapshots,'historyFields':graph_fields,
|
|
'labels':[s['captured_at'].strftime('%d %b %Y %H:%M UTC') for s in history],
|
|
'series':[{'name':by_key[key]['label'],'key':key,'format':by_key[key]['format'],
|
|
'values':[s['values'].get(key) for s in history]} for key in keys]}
|
|
|
|
|
|
def player_detail(conn,gid,pid,params):
|
|
record=one(conn,"SELECT * FROM player_statistics WHERE game_id=%s AND id=%s",(gid,pid))
|
|
if not record: raise ApiError(404,"Player not found in this game.")
|
|
fields,_=fields_templates(conn,gid)
|
|
history=player_history(conn,gid,pid,fields,params)
|
|
history.pop('snapshots',None)
|
|
pages={kind:record_page(conn,gid,pid,kind,{}) for kind in ('snapshots','sessions','activities','events','purchases')}
|
|
peers=all_players(conn,gid)
|
|
groups=[title for key,title in [('whales','Whale'),('playtime','Top playtime'),('first','First-time')]
|
|
if any(p['playerId']==pid for p in segment_players(peers,key))]
|
|
return {"player":player_values(record),"fields":fields,"groups":groups,"recordPages":pages,
|
|
"snapshots":pages['snapshots']['records'],"sessions":pages['sessions']['records'],"events":pages['events']['records'],
|
|
"activities":pages['activities']['records'],"sales":pages['purchases']['records'],
|
|
**player_inventory(conn,gid,pid),**history}
|
|
|
|
|
|
|
|
def dispatch(path,params):
|
|
parts=path.strip('/').split('/')
|
|
with connect() as conn:
|
|
if parts==['api','games']:
|
|
return {"games":[game(conn,r["id"]) for r in rows(conn,"SELECT id FROM games ORDER BY id")]}
|
|
if len(parts)<3 or parts[:2]!=['api','games'] or not parts[2].isdigit(): raise ApiError(404,"Use /api/games/{internal_game_id}/{dataset}.")
|
|
gid=int(parts[2]); root=game(conn,gid)
|
|
base={"gameId":gid,"game":root}
|
|
if len(parts)==3:
|
|
return base|{"datasets":{k:f"/api/games/{gid}/{k}" for k in ('players','sessions','events','metrics','activities','overview','retention','engagement','monetization','gamepasses','prompts','reports','data-quality')}}
|
|
days=integer(params,'days',14,90); route=parts[3]
|
|
if route=='players':
|
|
if len(parts)==6 and parts[4].isdigit(): return base|record_page(conn,gid,int(parts[4]),parts[5],params)
|
|
if len(parts)==5 and parts[4].isdigit(): return base|player_detail(conn,gid,int(parts[4]),params)
|
|
if len(parts)!=4: raise ApiError(404,'Unknown player route.')
|
|
return base|player_list(conn,gid,params)
|
|
if len(parts)!=4: raise ApiError(404,'Unknown dataset route.')
|
|
if route=='activities': return base|activities(conn,gid,params,days)
|
|
if route=='reports': return base|reports_for_game(conn,gid)
|
|
if route=='data-quality': return base|quality_log(conn,gid,params)
|
|
if route=='metrics': return base|root_metrics(conn,gid,days)
|
|
if route=='engagement': return base|engagement(conn,gid,days)
|
|
if route=='prompts': return base|prompt_analytics(conn,gid,days,params.get('source',['all'])[0])
|
|
if route=='monetization': return base|monetization(conn,gid,days)
|
|
if route=='gamepasses': return base|gamepasses(conn,gid)
|
|
if route=='events': return base|event_analytics(conn,gid,days)
|
|
if route not in ('overview','sessions','events','retention'): raise ApiError(404,'Dataset not found.')
|
|
selected_ids=None
|
|
if route=='sessions' and params.get('segment',['all'])[0]!='all':
|
|
selected_ids=[p['playerId'] for p in segment_players(all_players(conn,gid),params['segment'][0])]
|
|
data=overview(conn,gid,days,selected_ids)
|
|
if route in ('overview','sessions'):
|
|
data['engagement']=engagement(conn,gid,days,selected_ids)
|
|
data['metrics'].update(data['engagement']['metrics'])
|
|
data['segment']=params.get('segment',['all'])[0]
|
|
if route=='overview':
|
|
data['rootMetrics']=root_metrics(conn,gid,days)
|
|
data['healthReports']=reports_for_game(conn,gid)['reports']
|
|
else:
|
|
ret=retention(conn,gid,days); data['metrics']['day7']=ret['day7']
|
|
data.update({k:v for k,v in ret.items() if k not in ('retention','day7')})
|
|
return base|data
|