import uuid import datetime import tornado import json import pandas as pd import os import yaml import requests from commonFunc import get_db_engine, is_contains_gender, is_contains_age from config.url import create_campaign_url, create_group_and_creative_url, headers with 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