129 lines
12 KiB
Python
129 lines
12 KiB
Python
"""Interpret trends across systems without repeating the period report scorecards."""
|
|
from datetime import timedelta
|
|
|
|
|
|
def direction(current,previous):
|
|
if current is None or previous is None: return None
|
|
if previous==0: return 1 if current>0 else 0
|
|
change=(current-previous)/abs(previous)
|
|
return 1 if change>.03 else -1 if change<-.03 else 0
|
|
|
|
|
|
def economy_trends(conn,gid,start,end):
|
|
return conn.execute('''WITH paired AS (
|
|
SELECT l.data latest,b.data baseline FROM players p
|
|
CROSS JOIN LATERAL(SELECT data FROM player_snapshots WHERE game_id=p.game_id AND player_id=p.id
|
|
AND captured_at>=%(start)s::date AND captured_at<%(end)s::date ORDER BY captured_at DESC,id DESC LIMIT 1) l
|
|
CROSS JOIN LATERAL(SELECT data FROM player_snapshots WHERE game_id=p.game_id AND player_id=p.id
|
|
AND captured_at<%(start)s::date ORDER BY captured_at DESC,id DESC LIMIT 1) b
|
|
WHERE p.game_id=%(gid)s), deltas AS (
|
|
SELECT CASE WHEN jsonb_typeof(latest->'level')='number' AND jsonb_typeof(baseline->'level')='number'
|
|
THEN (latest->>'level')::double precision-(baseline->>'level')::double precision END levels,
|
|
CASE WHEN jsonb_typeof(latest->'cashEarned')='number' AND jsonb_typeof(baseline->'cashEarned')='number'
|
|
THEN (latest->>'cashEarned')::double precision-(baseline->>'cashEarned')::double precision END earned,
|
|
CASE WHEN jsonb_typeof(latest->'cashSpent')='number' AND jsonb_typeof(baseline->'cashSpent')='number'
|
|
THEN (latest->>'cashSpent')::double precision-(baseline->>'cashSpent')::double precision END spent FROM paired)
|
|
SELECT count(*)::int matched_players,percentile_cont(.5) WITHIN GROUP(ORDER BY levels) median_level_gain,
|
|
count(*) FILTER(WHERE earned>=0 AND spent>=0)::int economy_players,
|
|
sum(earned) FILTER(WHERE earned>=0 AND spent>=0)::double precision earned,
|
|
sum(spent) FILTER(WHERE earned>=0 AND spent>=0)::double precision spent FROM deltas''',
|
|
{'gid':gid,'start':start,'end':end}).fetchone()
|
|
|
|
|
|
def deep_analysis(conn,report,start,end,evidence):
|
|
from health_reports import number,window_metrics
|
|
m=report['metrics'];p=report['previousMetrics'];name=report['gameName']
|
|
reach=direction(m['active_players'],p['active_players'])
|
|
ccu=direction(m['average_ccu'],p['average_ccu'])
|
|
spending=direction(m['robux'],p['robux'])
|
|
duration=direction(m['average_session'],p['average_session'])
|
|
visits=lambda r:r['sessions']/r['active_players'] if r['active_players'] else None
|
|
repeat=direction(visits(m),visits(p))
|
|
recent=m if report['periodDays']==1 else window_metrics(conn,report['gameId'],end-timedelta(days=1),end)
|
|
weekday=window_metrics(conn,report['gameId'],end-timedelta(days=8),end-timedelta(days=7))
|
|
momentum=direction(recent['active_players'],weekday['active_players'])
|
|
if reach==-1 and ccu==1:
|
|
overall=f"{name} is drawing more concurrent presence from a smaller active audience. That suggests more intense use among remaining players; check longer visits and repeat use before treating the CCU rise as acquisition growth."
|
|
elif reach==-1 and spending==1:
|
|
overall=f"{name} is monetizing a narrower audience more heavily. Revenue resilience is useful, but it can mask weakening player renewal; the next improvement should broaden engagement rather than rely only on extracting more from existing buyers."
|
|
elif reach==1 and spending==-1:
|
|
overall=f"{name} is expanding reach without a matching improvement in spending. That separates the acquisition opportunity from the commercial weakness: first check whether the added audience reaches useful progression and purchase moments."
|
|
elif reach==1 and ccu in (0,1):
|
|
overall=f"{name} has a broader audience and a stable or growing concurrency base. The question is whether those arrivals establish a repeat-play habit; traffic growth by itself does not show that the first-session experience is improving."
|
|
elif reach==-1:
|
|
overall=f"{name} is losing audience breadth. Better monetization or longer sessions among the players who remain should be read separately from that loss; the main health question is how reliably new visitors become repeat players."
|
|
else:
|
|
overall=f"{name}'s recent movement is mixed rather than a clear change in overall health. Judge reach, attention, and spending together, and prioritize the weakest transition between arriving, engaging, returning, and buying."
|
|
if momentum==-1:
|
|
overall+=' The latest complete day was also below the same weekday last week for active reach, so the softness is visible beyond a simple weekday/weekend comparison.'
|
|
elif momentum==1 and reach==-1:
|
|
overall+=' The latest complete day beat its same-weekday audience comparison, offering an early recovery signal that still needs several more days of follow-through.'
|
|
elif momentum==1:
|
|
overall+=' The latest same-weekday comparison supports the broader reach improvement.'
|
|
|
|
strengths=[]
|
|
if duration==1:
|
|
strengths.append('The players who engage are sustaining longer sessions, so there is useful attention to build on once someone enters the core loop.')
|
|
if repeat==1:
|
|
strengths.append('Session starts per active player also increased, consistent with a more frequently returning or more intensely engaged remaining audience.')
|
|
new_time=evidence.get('newSession');returning_time=evidence.get('returningSession')
|
|
if returning_time and new_time and returning_time>new_time*1.25:
|
|
strengths.append('Returning players stay meaningfully longer than first-day players, suggesting the established loop has value for those who get past the initial experience.')
|
|
sources=evidence.get('promptSources',{});player=sources.get('player',{});game=sources.get('game',{})
|
|
if player.get('conversion') is not None and player.get('bought',0)>0:
|
|
strengths.append('Players who explicitly seek a purchase do convert, providing a concrete signal of demand to build around.')
|
|
if m['retention'] is not None and p['retention'] is not None and m['retention']>p['retention']+1:
|
|
strengths.append('Mature next-day return improved as well, so the positive signals are not confined to the current session.')
|
|
if not strengths:
|
|
strengths.append('No broad improvement is established across these measures in this window. Use the completed activity and purchase observations to identify one repeatable success before scaling changes.')
|
|
|
|
angle={
|
|
'willow-v1':'For an exploration-and-collection loop, make the first discovery or collection reward obvious, then give players a visible next goal and a reason to revisit it.',
|
|
'orbit-v1':'For a racing loop, inspect time to the first controllable race, the clarity of early checkpoints, and how much friction sits between finishing and starting another race.',
|
|
'dungeon-v1':'For a crafting-and-quest loop, inspect how soon players receive a useful reward and whether the next upgrade or quest gives them a clear short-term objective.'}
|
|
key=conn.execute('SELECT sample_key FROM games WHERE id=%s',(report['gameId'],)).fetchone()['sample_key']
|
|
context=angle.get(key,'Inspect time to the first meaningful action and how clearly the game presents the next achievable goal.')
|
|
if m['first_five_rate'] is not None and m['first_five_rate']<50:
|
|
weakness='The clearest audience weakness is early establishment: most first sessions with a known end finish before five minutes. Longer sessions among established players do not solve that initial loss. '
|
|
elif m['retention'] is not None and m['first_five_rate'] is not None and m['retention']<m['first_five_rate']/3:
|
|
weakness='Keeping players occupied in the first visit is not translating into the same strength of next-day return. Inspect the ending of the first visit and the reason to come back, alongside onboarding. '
|
|
else:
|
|
weakness='Focus the audience review on the handoff from the first enjoyable action to the next visit, using first-session and mature return cohorts together. '
|
|
weakness+=context
|
|
|
|
drop=evidence.get('largestDrop')
|
|
if drop and drop['previousRuns']>=20:
|
|
friction=f"The most visible activity friction is in {drop['activity']}, between {drop['from']} and {drop['to']}. The observed loss is {number(drop['dropoff'])}% of runs reaching the preceding stage. Check whether the next action is understood, accessible, and worth its reward; the stage counts locate the loss but do not identify its cause."
|
|
else:
|
|
friction='There is not a sufficiently populated observed transition here to justify naming a single activity bottleneck. Review completed journeys before choosing a stage to redesign.'
|
|
economy=dict(economy_trends(conn,report['gameId'],start,end))
|
|
if economy['economy_players']>=10 and economy['spent'] and economy['earned']>economy['spent']*1.25:
|
|
friction+=' Matched returning-player snapshots show recorded currency earnings outpacing spending. Check whether useful upgrades and currency sinks keep pace with rewards; this pattern alone does not establish that prices are wrong.'
|
|
elif economy['matched_players']>=10 and economy['median_level_gain'] and economy['median_level_gain']>0:
|
|
friction+=' Matched returning players are still gaining levels. Preserve that progression payoff while checking whether the next unlock remains clear and worthwhile.'
|
|
|
|
largest=evidence.get('largestPayerShare')
|
|
if largest is not None and largest>=35:
|
|
commercial=f"Revenue is concentrated: the largest payer supplied {number(largest)}% of the period's recorded spending. Recheck the trend with that account removed before concluding that monetization improved broadly, and track the spread of paying players rather than just the total."
|
|
elif largest is not None:
|
|
commercial='No single payer supplied more than roughly a third of recorded spending in this window. Still compare payer conversion and spend per payer separately so that changes in audience size do not hide a weakening purchase experience.'
|
|
else:
|
|
commercial='A broad monetization conclusion is not established from attributed purchases in this window. Start with the observed purchase journey and the distribution of successful buyers.'
|
|
if player.get('conversion') is not None and game.get('conversion') is not None:
|
|
gap=player['conversion']-game['conversion']
|
|
if gap>10:
|
|
commercial+=' Explicit purchase actions outperform automatic offers. Test game-triggered offers nearer a relevant need or reward, using matched products and player groups to separate placement from existing purchase intent.'
|
|
elif gap<-10:
|
|
commercial+=' Automatic offers currently convert better than explicit purchase actions. Check whether the product mix and player groups differ before increasing offer frequency.'
|
|
else:
|
|
commercial+=' Prompt-source conversion is relatively close; product relevance and decision friction are better next comparisons than assuming one source is inherently stronger.'
|
|
priorities='Start with one measurable first-session improvement, then test the identified activity transition or progression handoff. Review the effect on five-minute engagement and mature next-day return before expanding the change. Separately test offer placement, judging success by payer breadth and revenue concentration as well as total Robux.'
|
|
return {'title':'Everything at a glance','paragraphs':[
|
|
{'title':'Direction of travel','text':overall},
|
|
{'title':"What's working",'text':' '.join(strengths[:3])},
|
|
{'title':'Main player-experience weakness','text':weakness},
|
|
{'title':'Activity & progression friction','text':friction},
|
|
{'title':'Quality of monetization','text':commercial},
|
|
{'title':'What to prioritize','text':priorities}],
|
|
'economyEvidence':economy}
|