def index_old(self):
from gviz_data_table import Table
from rockpack.mainsite.services.user.models import User, UserActivity
user_count = readonly_session.query(func.count(User.id)).\
filter(User.refresh_token != '').scalar()
header = ('user count', 'max lifetime', 'avg lifetime', 'stddev lifetime',
'max active days', 'avg active days', 'stddev active days')
lifetime = func.date_part('days', func.max(UserActivity.date_actioned) -
func.min(UserActivity.date_actioned)).label('lifetime')
active_days = func.count(func.distinct(func.date(
UserActivity.date_actioned))).label('active_days')
activity = readonly_session.query(UserActivity.user, lifetime, active_days).\
group_by(UserActivity.user)
ctx = {}
for key, having_expr in ('all', None), ('1day', lifetime > 1), ('7day', lifetime > 7):
data = activity.having(having_expr).from_self(
func.count('*'),
func.max(lifetime),
func.avg(lifetime),
func.stddev_samp(lifetime),
func.max(active_days),
func.avg(active_days),
func.stddev_samp(active_days)
).one()
table = Table([
dict(id='metric', type=str),
dict(id='value', type=float),
dict(id='%', type=str),
])
pdata = ('%d%%' % (data[0] * 100 / user_count),) + ('',) * 6
table.extend(zip(*(header, map(float, data), pdata)))
ctx['ret_%s_data' % key] = table.encode()
return self.render('admin/retention_stats_old.html', **ctx)
def index(self):
from gviz_data_table import Table
from rockpack.mainsite.services.user.models import User, UserActivity, UserAccountEvent
if request.args.get('activity') == 'activity':
activity_model, activity_date = UserActivity, UserActivity.date_actioned
else:
activity_model, activity_date = UserAccountEvent, UserAccountEvent.event_date
try:
interval_count = int(request.args['interval_count'])
except Exception:
interval_count = 10
interval = request.args.get('interval')
if interval not in ('week', 'month'):
interval = 'week'
cohort = func.date_part(interval, User.date_joined)
cohort_label = func.min(func.date(User.date_joined))
active_interval = (func.date_part(interval, activity_date) - cohort).label('active_interval')
q = readonly_session.query(User).filter(
User.date_joined > LAUNCHDATE, User.refresh_token != '')
if request.args.get('gender') in ('m', 'f'):
q = q.filter(User.gender == request.args['gender'])
if request.args.get('locale') in app.config['ENABLED_LOCALES']:
q = q.filter(User.locale == request.args['locale'])
if request.args.get('age') in ('13-18', '18-25', '25-35', '35-45', '45-55'):
age1, age2 = map(int, request.args['age'].split('-'))
q = q.filter(between(
func.age(User.date_of_birth),
text("interval '%d years'" % age1),
text("interval '%d years'" % age2)
))
active_users = dict(
((c, int(w)), u) for c, w, u in
q.join(
activity_model,
(activity_model.user == User.id) &
(activity_date >= User.date_joined)
).group_by(cohort, active_interval).values(
cohort, active_interval, func.count(func.distinct(activity_model.user))
)
)
table = Table(
[dict(id='cohort', type=date)] +
[dict(id='%s%d' % (interval, i), type=str) for i in range(interval_count)]
)
totals = q.group_by(cohort).order_by(cohort)
for c, l, t in totals.values(cohort, cohort_label, func.count()):
data = []
for i in range(interval_count):
a = active_users.get((c, i), '')
data.append(a and '%d%% (%d)' % (ceil(a * 100.0 / t), a))
table.append([l] + data)
return self.render('admin/retention_stats.html', data=table.encode())
def index(self):
from gviz_data_table import Table
from rockpack.mainsite.services.user.models import User
from .models import AppDownloadRecord
ctx = {}
for action in 'download', 'update':
table = Table((dict(id='date', type=date), dict(id='count', type=long)))
table.extend(
readonly_session.query(AppDownloadRecord).filter_by(action=action).
group_by('1').order_by('1').
values(AppDownloadRecord.date, func.sum(AppDownloadRecord.count))
)
ctx['%s_data' % action] = table.encode()
table = Table([dict(id='date', type=date)] +
[dict(id=i, type=long) for i in 'Total', 'US', 'UK', 'Other'])
def data():
folder = os.path.split(__file__)[0]
db = sqlite3.connect(os.path.join(folder, "sample.db"))
c = db.cursor()
c.execute("SELECT name, salary FROM employees")
cols = [
dict(id=col[0], label=col[0].capitalize(), type=col[1])
for col in c.description
]
# sqlite3 unfortunately does not provide type information
cols[0]['type'] = unicode
cols[1]['type'] = float
t = Table(cols)
for r in c.fetchall():
name, value = r
label = "${0}".format(value)
t.append([name, (value, label)])
return encoder.encode(t)
def _bq2geo(self, bqdata):
# geodata output for region maps must be in the format region, value.
# Assume the query output is in this format, get names from schema.
logging.info(bqdata)
table = Table()
NameGeo = bqdata["schema"]["fields"][0]["name"]
NameVal = bqdata["schema"]["fields"][1]["name"]
table.add_column(NameGeo, unicode, NameGeo)
table.add_column(NameVal, float, NameVal)
for row in bqdata["rows"]:
table.append(["US-" + row["f"][0]["v"], float(row["f"][1]["v"])])
logging.info("FINAL GEODATA---")
logging.info(table)
return encode(table)
def bq2table(bqdata):
def getType(type_str):
if type_str == "INTEGER":
return int
elif type_str == "FLOAT":
return float
elif type_str == "STRING":
return str
else:
return eval(type_str)
table = Table()
for x in bqdata["schema"]["fields"]:
table.add_column(x["name"], getType(x["type"]), x["name"])
if "rows" in bqdata:
for row in bqdata["rows"]:
row_data = []
for x, t in zip(row["f"], bqdata["schema"]["fields"]):
val = getType(t["type"])(x["v"] or 0)
if t["type"] == "FLOAT":
val = float("{0:.2f}".format(val))
row_data.append(val)
# add row only if it's not all zeros
if row_data.count(0) < (len(row_data) - 1):
table.append(row_data)
logging.info("FINAL BOOTTIMEDATA---")
return encode(table)
def index(self):
from gviz_data_table import Table
try:
period = dict(self.periods)[request.args.get('period')]
except:
period = self.periods[0][1]
dates = {'from': datetime.combine(date.today() - timedelta(days=period), time.min)}
dates['to'] = dates['from'] + timedelta(days=period)
for param in 'from', 'to':
try:
dates[param] = datetime.strptime(request.args[param], '%Y-%m-%d %H:%M')
except:
pass
try:
sortindex = self.counts.index(request.args.get('sort')) + self.sortindex_base
except:
sortindex = self.sortindex_base
try:
limit = int(request.args['limit'])
except:
limit = 20
table = Table(self.get_table_head())
for row in self.get_query(dates['from'], dates['to']).\
order_by('%d desc' % sortindex).limit(limit):
item, counts = self.split_row(row)
table.append(item + [int(i) if i else 0 for i in counts])
selects = ('sort', self.counts), ('period', zip(*self.periods)[0])
return self.render('admin/chart_table.html',
data=table.encode(),
selects=selects,
dates=sorted(dates.items()))
def _bq2geo(self, bqdata):
# geodata output for region maps must be in the format region, value.
# Assume the query output is in this format, get names from schema.
logging.info(bqdata)
table = Table()
NameGeo = bqdata['schema']['fields'][0]['name']
NameVal = bqdata['schema']['fields'][1]['name']
table.add_column(NameGeo, unicode, NameGeo)
table.add_column(NameVal, float, NameVal)
for row in bqdata['rows']:
table.append(['US-' + row['f'][0]['v'], float(row['f'][1]['v'])])
logging.info('FINAL GEODATA---')
logging.info(table)
return encode(table)
def _bq2geo(self, bqdata):
# geodata output for region maps must be in the format region, value.
# Assume the query output is in this format, get names from schema.
logging.info(bqdata)
table = Table()
NameGeo = bqdata["schema"]["fields"][0]["name"]
NameVal = bqdata["schema"]["fields"][1]["name"]
table.add_column(NameGeo, unicode, NameGeo)
table.add_column(NameVal, float, NameVal)
for row in bqdata["rows"]:
table.append(["US-"+row["f"][0]["v"], float(row["f"][1]["v"])])
logging.info("FINAL GEODATA---")
logging.info(table)
return encode(table)
