87 lines
4.3 KiB
Python
87 lines
4.3 KiB
Python
# -*- coding: utf-8 -*-
|
|
import datetime
|
|
from ops.plog import define_logger
|
|
import logging
|
|
import os
|
|
from ops.mtga import FromTga, GetTgaConfig
|
|
|
|
if os.path.isdir("/data/logs"):
|
|
define_logger("/data/logs/collect_data.log")
|
|
else:
|
|
define_logger("/tmp/collect_data.log")
|
|
|
|
log = logging.getLogger(__name__)
|
|
|
|
|
|
class CollectGameDataAD:
|
|
def __init__(self, **kwargs):
|
|
self.gameid = kwargs.get('gameid', 0)
|
|
self.channelid = kwargs.get('channelid', 6001)
|
|
self.times = kwargs.get('times', datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
|
|
self.date = datetime.datetime.strptime(self.times, "%Y-%m-%d %H:%M:%S").strftime("%Y-%m-%d")
|
|
self.ad_channel = kwargs.get('ad_channel', '')
|
|
|
|
gc = GetTgaConfig()
|
|
self.url = gc.url
|
|
item = gc.get_api_key(self.gameid, self.channelid)
|
|
self.suffix = item.get('suffix', None)
|
|
self.tga = FromTga(url=self.url, token=item.get('api_key', None))
|
|
|
|
def _run_tga_sql(self, sql):
|
|
data = self.tga.get_data(sql)
|
|
try:
|
|
if data:
|
|
return int(data[0][0])
|
|
else:
|
|
return 0
|
|
except Exception:
|
|
log.error(f"get data ={data} error!", exc_info=True)
|
|
return 0
|
|
|
|
def get_DAU(self):
|
|
sql = f"""SELECT count(distinct \"#account_id\") FROM v_event_{self.suffix} where "$part_event"='event_11_1' AND gameid='{self.gameid}' AND ad_channel='{self.ad_channel}' AND "#server_time" BETWEEN TIMESTAMP '{self.date} 00:00:00' AND TIMESTAMP '{self.times}'"""""
|
|
return self._run_tga_sql(sql)
|
|
|
|
|
|
def get_registerUser(self):
|
|
sql = f"""SELECT count(distinct "#user_id") FROM v_user_{self.suffix} where gameid='{self.gameid}' AND ad_channel='{self.ad_channel}' AND account_register_time <= TIMESTAMP '{self.times}'"""
|
|
return self._run_tga_sql(sql)
|
|
|
|
|
|
def get_newuser(self):
|
|
sql = f"""SELECT count(distinct "#account_id") FROM v_event_{self.suffix} where gameid='{self.gameid}' and "$part_event"='event_11_1' and ad_channel='{self.ad_channel}' and account_register_date between TIMESTAMP'{self.date} 00:00:00' and TIMESTAMP'{self.times}'"""
|
|
return self._run_tga_sql(sql)
|
|
|
|
def get_loginuser(self):
|
|
sql = f"""SELECT count(1) FROM v_event_{self.suffix} where gameid='{self.gameid}' and
|
|
"$part_event"='event_11_1' and ad_channel='{self.ad_channel}' and "#server_time" BETWEEN TIMESTAMP '{self.date} 00:00:00' AND TIMESTAMP '{self.times}'"""
|
|
return self._run_tga_sql(sql)
|
|
|
|
def get_sharenum(self):
|
|
sql = f"""SELECT count(1) FROM v_event_{self.suffix} where "$part_event"='event_11_10' and gameid='{self.gameid}' and ad_channel='{self.ad_channel}' and "#server_time" BETWEEN TIMESTAMP '{self.date} 00:00:00' AND TIMESTAMP '{self.times}'"""
|
|
print(f"1 {sql}")
|
|
return self._run_tga_sql(sql)
|
|
|
|
|
|
def get_sharebypeople(self):
|
|
sql = f"""SELECT count(distinct \"#account_id\") FROM v_event_{self.suffix} where "$part_event"='event_11_10' and gameid='{self.gameid}' and ad_channel='{self.ad_channel}' and "#server_time" BETWEEN TIMESTAMP '{self.date} 00:00:00' AND TIMESTAMP '{self.times}'"""
|
|
return self._run_tga_sql(sql)
|
|
|
|
def get_retain(self, next_step=1):
|
|
now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
|
|
btimes = (datetime.datetime.strptime(now, '%Y-%m-%d %H:%M:%S') - datetime.timedelta(days=next_step)).strftime('%Y-%m-%d')
|
|
login_sql = f"""select count(distinct "#account_id") from v_event_{self.suffix} where
|
|
"$part_event"='event_11_1' AND gameid='{self.gameid}' AND ad_channel='{self.ad_channel}' AND account_register_date
|
|
BETWEEN TIMESTAMP'{btimes} 00:00:00' AND TIMESTAMP'{btimes} 23:59:59' AND "$part_date"='{now[:10]}' """
|
|
newuser_sql = f"""select count(distinct "#account_id") from v_event_{self.suffix} where gameid='{self.gameid}' AND ad_channel='{self.ad_channel}' AND account_register_date BETWEEN TIMESTAMP'{btimes} 00:00:00' AND TIMESTAMP'{btimes} 23:59:59'"""
|
|
loguser = self._run_tga_sql(login_sql)
|
|
newuser = self._run_tga_sql(newuser_sql)
|
|
try:
|
|
retain_rate = round((100 * loguser / newuser), 2)
|
|
except Exception:
|
|
retain_rate = 0
|
|
log.error("collect retain failed!", exc_info=True)
|
|
print(f"2 {retain_rate} {loguser} {newuser}")
|
|
return retain_rate
|
|
|