71 lines
5.3 KiB
Python
71 lines
5.3 KiB
Python
"""Lightweight product-prompt measurements, separate from activity sessions."""
|
|
def prompt_analytics(conn,gid,days,source='all'):
|
|
if source not in ('all','game','player'):
|
|
from sample_api import ApiError
|
|
raise ApiError(400,'Unknown prompt source.')
|
|
products=conn.execute('''SELECT p.id,p.name,p.roblox_product_id,count(i.id)::int prompts,
|
|
count(i.id) FILTER(WHERE i.outcome='bought')::int bought,
|
|
count(i.id) FILTER(WHERE i.outcome='closed')::int closed,
|
|
count(i.id) FILTER(WHERE i.outcome IS NULL)::int unresolved,
|
|
avg(extract(epoch FROM i.ended_at-i.started_at)) FILTER(WHERE i.outcome='bought')::double precision buy_time,
|
|
avg(extract(epoch FROM i.ended_at-i.started_at)) FILTER(WHERE i.outcome='closed')::double precision close_time
|
|
FROM products p LEFT JOIN interaction_events i ON i.game_id=p.game_id AND i.product_id=p.id AND i.kind='product_prompt'
|
|
AND i.outcome IN ('bought','closed') AND i.ended_at IS NOT NULL AND i.trigger_source IN ('game','player')
|
|
AND i.started_at>=current_date-(%s-1) AND (%s='all' OR i.trigger_source=%s)
|
|
WHERE p.game_id=%s GROUP BY p.id ORDER BY p.id''',(days,source,source,gid)).fetchall()
|
|
for row in products: row['conversion']=100*row['bought']/row['prompts'] if row['prompts'] else None
|
|
daily=conn.execute('''SELECT (started_at AT TIME ZONE 'UTC')::date AS day,product_id,count(*)::int prompts,
|
|
count(*) FILTER(WHERE outcome='bought')::int bought,
|
|
avg(extract(epoch FROM ended_at-started_at)) FILTER(WHERE outcome='bought')::double precision buy_time,
|
|
avg(extract(epoch FROM ended_at-started_at)) FILTER(WHERE outcome='closed')::double precision close_time
|
|
FROM interaction_events WHERE game_id=%s AND kind='product_prompt'
|
|
AND outcome IN ('bought','closed') AND ended_at IS NOT NULL AND trigger_source IN ('game','player') AND started_at>=current_date-(%s-1)
|
|
AND (%s='all' OR trigger_source=%s) GROUP BY day,product_id ORDER BY day''',(gid,days,source,source)).fetchall()
|
|
totals=conn.execute('''SELECT count(*)::int prompts,count(*) FILTER(WHERE outcome='bought')::int bought,
|
|
avg(extract(epoch FROM ended_at-started_at)) FILTER(WHERE outcome='bought')::double precision buy_time,
|
|
avg(extract(epoch FROM ended_at-started_at)) FILTER(WHERE outcome='closed')::double precision close_time
|
|
FROM interaction_events WHERE game_id=%s AND kind='product_prompt'
|
|
AND outcome IN ('bought','closed') AND ended_at IS NOT NULL AND trigger_source IN ('game','player') AND started_at>=current_date-(%s-1)
|
|
AND (%s='all' OR trigger_source=%s)''',(gid,days,source,source)).fetchone()
|
|
totals['conversion']=100*totals['bought']/totals['prompts'] if totals['prompts'] else None
|
|
from datetime import datetime,timedelta,timezone
|
|
dates=[datetime.now(timezone.utc).date()-timedelta(days=i) for i in reversed(range(days))]
|
|
lookup={(r['product_id'],r['day']):r for r in daily}
|
|
series=[]
|
|
for p in products:
|
|
values=[]
|
|
for day in dates:
|
|
row=lookup.get((p['id'],day))
|
|
values.append(100*row['bought']/row['prompts'] if row and row['prompts'] else None)
|
|
series.append({'name':p['name'],'format':'percent','values':values})
|
|
grouped=conn.execute('''SELECT trigger_source source,count(*)::int prompts,
|
|
count(*) FILTER(WHERE outcome='bought')::int bought,count(*) FILTER(WHERE outcome='closed')::int closed,
|
|
count(*) FILTER(WHERE outcome IS NULL)::int unresolved,
|
|
avg(extract(epoch FROM ended_at-started_at)) FILTER(WHERE outcome='bought')::double precision buy_time,
|
|
avg(extract(epoch FROM ended_at-started_at)) FILTER(WHERE outcome='closed')::double precision close_time
|
|
FROM interaction_events WHERE game_id=%s AND kind='product_prompt'
|
|
AND outcome IN ('bought','closed') AND ended_at IS NOT NULL AND trigger_source IN ('game','player') AND started_at>=current_date-(%s-1)
|
|
GROUP BY trigger_source''',(gid,days)).fetchall()
|
|
labels={'game':'Game-induced','player':'Player-induced'}
|
|
by_source={r['source']:r for r in grouped}
|
|
comparison=[]
|
|
for key,name in labels.items():
|
|
row=by_source.get(key,{'source':key,'prompts':0,'bought':0,'closed':0,'unresolved':0,'buy_time':None,'close_time':None})
|
|
row['name']=name;row['conversion']=100*row['bought']/row['prompts'] if row['prompts'] else None
|
|
comparison.append(row)
|
|
source_days=conn.execute('''SELECT (started_at AT TIME ZONE 'UTC')::date AS day,trigger_source source,
|
|
count(*)::int prompts,count(*) FILTER(WHERE outcome='bought')::int bought
|
|
FROM interaction_events WHERE game_id=%s AND kind='product_prompt'
|
|
AND outcome IN ('bought','closed') AND ended_at IS NOT NULL AND trigger_source IN ('game','player') AND started_at>=current_date-(%s-1)
|
|
GROUP BY day,trigger_source''',(gid,days)).fetchall()
|
|
source_lookup={(r['source'],r['day']):r for r in source_days}
|
|
source_series=[]
|
|
for key,name in labels.items():
|
|
values=[]
|
|
for day in dates:
|
|
row=source_lookup.get((key,day))
|
|
values.append(100*row['bought']/row['prompts'] if row and row['prompts'] else None)
|
|
source_series.append({'name':name,'format':'percent','values':values})
|
|
return {'metrics':totals,'products':products,'source':source,'sourceComparison':comparison,
|
|
'sourceSeries':source_series,'series':series,'labels':[d.strftime('%d %b') for d in dates]}
|