| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182 | import uuidimport datetimeimport tornadoimport jsonimport pandas as pdimport osimport yamlimport requestsfrom commonFunc import get_db_engine, is_contains_gender, is_contains_agefrom config.url import create_campaign_url, create_group_and_creative_url, headerswith open('config/config.yaml', mode='r', encoding='utf-8') as f:    config = yaml.load(f.read(), Loader=yaml.FullLoader)class AiTargetCombine(tornado.web.RequestHandler):    def post(self):        res = {'code': 0,               'message': "SUCCESS"}        data = self.request.body        data = str(data, 'utf8')        data = json.loads(data, encoding='utf8')        try:            pass        except Exception:            pass        # 返回接口结果        self.write(json.dumps(res))        self.flush()class GetTargetAndAssemblyParameters(object):    def __init__(self, account_id, project_id):        self.account_id = account_id        self.project_id = project_id        self.engine = None        self.get_database_engine()        self.signature_target_combine = None        self.advertiser_strategy_id = None        self.advertiser_strategy = {}        self.ai_strategy_uuid = None    def get_database_engine(self):        # 数据库连接引擎,依据开发环境/生产环境 进行切换        if os.getenv('environment', 'unknown') == 'dev':            self.engine = get_db_engine(config['devDB'])        else:            self.engine = get_db_engine(config['productDB'])    def get_advertiser_strategy_info(self):        """        获取指定账户下客户的策略信息        为后续的写入库表,校验一致性做准备        """        sql = """                select *                 from ctop_ai_kuaishou_advertiser_strategy                 where account_id = %d                limit 1                """ % self.account_id        advertiser_strategy_df = pd.read_sql(sql, self.engine)        self.advertiser_strategy_id = int(advertiser_strategy_df['id'].values[0])        self.advertiser_strategy = advertiser_strategy_df.T.to_dict()[0]    def add_campaign(self):        """        新增广告计划,如果存在则跳过。        response_data = {            "code": 0,            "data": {                "account_id": 23212,                "campaign_id": 30956133,                "campaign_create_time": "2021-01-13 13:50:16"            },            "message": "SUCCESS"}        """        # 1、请求创建广告计划的接口        create_campaign_request_data = {'account_id': self.account_id,                                        'campaign_name': self.advertiser_strategy['campaign_name'] + '_优质定向',                                        'type': self.advertiser_strategy['campaign_type']}        request = requests.post(url=create_campaign_url,                                headers=headers,                                data=json.JSONEncoder().encode(create_campaign_request_data))        response_data = json.loads(request.text)        # 2、对接口返回的结果处理        campaign_info_to_db = {            'campaign_uuid': str(uuid.uuid4()),            'account_id': self.account_id,            'ai_strategy_uuid': self.ai_strategy_uuid,            'campaign_name': self.advertiser_strategy['campaign_name'],            'campaign_type': self.advertiser_strategy['campaign_type'],            'create_time': datetime.datetime.now()        }        res = 0        if response_data['code'] == 0:            campaign_info_to_db['campaign_id'] = response_data['data'].get('campaign_id', None)            campaign_info_to_db['campaign_create_time'] = response_data['data'].get('campaign_create_time', None)            campaign_info_to_db['status'] = response_data.get('code', None)            campaign_info_to_db['message'] = response_data.get('message', None)        else:            campaign_info_to_db['message'] = response_data['message']            campaign_info_to_db['status'] = response_data['code']            res = -1        # 3、写入计划层级的操作表        df = pd.DataFrame.from_dict(campaign_info_to_db, orient='index').T        df.to_sql(name="ctop_ai_kuaishou_campaign_level_operation_record",                  con=self.engine,                  if_exists='append',                  index=False)        return res    def get_signature_and_target(self):        """        从 ctop_ai_kuaishou_signature_recommended_target_combine 表中读取素材和对应的定向        """        sql = """        select * from ctop_ai_kuaishou_signature_recommended_target_combine where project_id = %s and stat_date = curdate()        """ % self.project_id        df = pd.read_sql(sql, self.engine)        # 计算组合的概率,相对于实际投放概率高出了百分之多少        df['improve_ratio'] = (df['combine_estimate_prob'] - df['actual_prob']) / df['actual_prob']        # 两种类型的过滤标准不一样,分开进行判断,然后对结果进行合并        df_1 = df[(df['target_type'] == 'action_ratio') &                  (df['improve_ratio'] >= config['filterTargetCombine']['actionRatio']['improveRatio']) &                  (df['sample_size'] >= config['filterTargetCombine']['actionRatio']['sampleSize'])]        df_2 = df[(df['target_type'] == 'convertRatio') &                  (df['improve_ratio'] >= config['filterTargetCombine']['convertRatio']['improveRatio']) &                  (df['sample_size'] >= config['filterTargetCombine']['convertRatio']['sampleSize'])]        merge_df = pd.concat([df_1, df_2], axis=0)        self.signature_target_combine = merge_df[['signature', 'age', 'gender', 'city', 'business', 'province', 'client']]. \            to_dict(orient='records')    def write_intelligence_strategy_table(self):        """        拼接好的组创意的参数写入到 ctop_ai_kuaishou_intelligence_strategy 中的 ai_strategy_request_content        """        pass    def get_target_intersection(self):        """        两个表中读取的定向取交集,还是子集?        age	struct	可选	自定义年龄段	不传值表示不限,传值具体见下方表格;与ages_range不能同时传        age	字段	类型	是否必填	说明	         备注            min	int	必填	    年龄最小限制	年龄区间最小为18岁            max	int	必填	    年龄最大限制	年龄区间最大为55岁,且年龄最大限制须大于等于年龄最小限制        ages_range	int[]	可选	固定年龄段	与age不能同时传;【18:表示18-23岁】;【24:表示24-30岁】;【31:表示31-40岁】;【41:表示41-49岁】;【50:表示50-100岁】        ctop_ai_kuaishou_signature_recommended_target_combine 表中的 city 和 province 都对应 self.advertiser_strategy 的 region []  varchar        age 对应 ages_range []  varchar        client 对应 platform_os int        business_interest 对应 business_interest varchar        gender 对应 gender int        """        for item in self.signature_target_combine:            gender_bool, gender_dict = is_contains_age(item['gender'], self.advertiser_strategy['gender'])            age_bool, age_dict = is_contains_age(item['age'], self.advertiser_strategy['age'])            if gender_bool & age_bool:                target_combine = {'signature': item['signature']}                target_combine.update(gender_dict)                target_combine.update(age_dict)            else:                pass        pass    def assembly_group_and_creative_params(self):        """        拼接参数,请求创建接口        """        pass
 |