ai_target_combine_handler.py 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376
  1. import uuid
  2. import datetime
  3. import tornado
  4. import json
  5. import pandas as pd
  6. import os
  7. import yaml
  8. import requests
  9. from commonFunc import get_db_engine, is_contains_gender, is_contains_age, is_contains_region
  10. from config.url import create_campaign_url, create_group_and_creative_url, headers
  11. with open('config/config.yaml', mode='r', encoding='utf-8') as f:
  12. config = yaml.load(f.read(), Loader=yaml.FullLoader)
  13. class AiTargetCombine(tornado.web.RequestHandler):
  14. def post(self):
  15. res = {'code': 0,
  16. 'message': "SUCCESS"}
  17. data = self.request.body
  18. data = str(data, 'utf8')
  19. data = json.loads(data, encoding='utf8')
  20. try:
  21. pass
  22. except Exception:
  23. pass
  24. # 返回接口结果
  25. self.write(json.dumps(res))
  26. self.flush()
  27. class GetTargetAndAssemblyParameters(object):
  28. def __init__(self, account_id, project_id):
  29. self.account_id = account_id
  30. self.project_id = project_id
  31. self.engine = None
  32. self.get_database_engine()
  33. self.signature_target_combine = None
  34. self.final_target_combine = []
  35. self.advertiser_strategy_id = None
  36. self.advertiser_strategy = {}
  37. self.ai_strategy_uuid = str(uuid.uuid4())
  38. self.request_data = None
  39. self.campaign_id = None
  40. self.operation_type = 1 # 新增广告组
  41. def get_database_engine(self):
  42. # 数据库连接引擎,依据开发环境/生产环境 进行切换
  43. if os.getenv('environment', 'unknown') == 'dev':
  44. self.engine = get_db_engine(config['devDB'])
  45. else:
  46. self.engine = get_db_engine(config['productDB'])
  47. def get_advertiser_strategy_info(self):
  48. """
  49. 获取指定账户下客户的策略信息
  50. 为后续的写入库表,校验一致性做准备
  51. """
  52. sql = """
  53. select *
  54. from ctop_ai_kuaishou_advertiser_strategy
  55. where account_id = %d
  56. limit 1
  57. """ % self.account_id
  58. advertiser_strategy_df = pd.read_sql(sql, self.engine)
  59. self.advertiser_strategy_id = int(advertiser_strategy_df['id'].values[0])
  60. self.advertiser_strategy = advertiser_strategy_df.T.to_dict()[0]
  61. def add_campaign(self):
  62. """
  63. 新增广告计划,如果存在则跳过。
  64. response_data = {
  65. "code": 0,
  66. "data": {
  67. "account_id": 23212,
  68. "campaign_id": 30956133,
  69. "campaign_create_time": "2021-01-13 13:50:16"
  70. },
  71. "message": "SUCCESS"}
  72. """
  73. # 1、请求创建广告计划的接口
  74. campaign_name = self.advertiser_strategy['campaign_name'] + '_优质定向'
  75. create_campaign_request_data = {'account_id': self.account_id,
  76. 'campaign_name': campaign_name,
  77. 'type': self.advertiser_strategy['campaign_type']}
  78. request = requests.post(url=create_campaign_url,
  79. headers=headers,
  80. data=json.JSONEncoder().encode(create_campaign_request_data))
  81. response_data = json.loads(request.text)
  82. # 2、对接口返回的结果处理
  83. campaign_info_to_db = {
  84. 'campaign_uuid': str(uuid.uuid4()),
  85. 'account_id': self.account_id,
  86. 'ai_strategy_uuid': self.ai_strategy_uuid,
  87. 'campaign_name': campaign_name,
  88. 'campaign_type': self.advertiser_strategy['campaign_type'],
  89. 'create_time': datetime.datetime.now()
  90. }
  91. res = 0
  92. if response_data['code'] == 0:
  93. self.campaign_id = response_data['data'].get('campaign_id', None)
  94. campaign_info_to_db['campaign_id'] = self.campaign_id
  95. campaign_info_to_db['campaign_create_time'] = response_data['data'].get('campaign_create_time', None)
  96. campaign_info_to_db['status'] = response_data.get('code', None)
  97. campaign_info_to_db['message'] = response_data.get('message', None)
  98. else:
  99. campaign_info_to_db['message'] = response_data['message']
  100. campaign_info_to_db['status'] = response_data['code']
  101. res = -1
  102. # 3、写入计划层级的操作表
  103. df = pd.DataFrame.from_dict(campaign_info_to_db, orient='index').T
  104. df.to_sql(name="ctop_ai_kuaishou_campaign_level_operation_record",
  105. con=self.engine,
  106. if_exists='append',
  107. index=False)
  108. return res
  109. def get_signature_and_target(self):
  110. """
  111. 从 ctop_ai_kuaishou_signature_recommended_target_combine 表中读取素材和对应的定向
  112. 读取配置文件的条件,筛选出符合条件的定向组合
  113. """
  114. sql = """
  115. select * from ctop_ai_kuaishou_signature_recommended_target_combine where project_id = %s and stat_date = curdate()
  116. """ % self.project_id
  117. df = pd.read_sql(sql, self.engine)
  118. # 计算组合的概率,相对于实际投放概率高出了百分之多少
  119. df['improve_ratio'] = (df['combine_estimate_prob'] - df['actual_prob']) / df['actual_prob']
  120. # 两种类型的过滤标准不一样,分开进行判断,然后对结果进行合并
  121. df_1 = df[(df['target_type'] == 'action_ratio') &
  122. (df['improve_ratio'] >= config['filterTargetCombine']['actionRatio']['improveRatio']) &
  123. (df['sample_size'] >= config['filterTargetCombine']['actionRatio']['sampleSize'])]
  124. df_2 = df[(df['target_type'] == 'convertRatio') &
  125. (df['improve_ratio'] >= config['filterTargetCombine']['convertRatio']['improveRatio']) &
  126. (df['sample_size'] >= config['filterTargetCombine']['convertRatio']['sampleSize'])]
  127. merge_df = pd.concat([df_1, df_2], axis=0)
  128. self.signature_target_combine = merge_df[['signature', 'age', 'gender', 'city', 'business', 'province', 'client']]. \
  129. to_dict(orient='records')
  130. def write_intelligence_strategy_table(self):
  131. """
  132. 拼接好的组创意的参数写入到 ctop_ai_kuaishou_intelligence_strategy 中的 ai_strategy_request_content
  133. """
  134. intelligence_strategy_dict = \
  135. {
  136. 'ai_strategy_uuid': self.ai_strategy_uuid,
  137. 'advertiser_strategy_id': self.advertiser_strategy_id,
  138. 'account_id': self.account_id,
  139. 'ai_strategy_request_content': str(self.request_data),
  140. 'ai_strategy_remark': '优质定向组合',
  141. 'create_time': datetime.datetime.now()
  142. }
  143. df = pd.DataFrame.from_dict(intelligence_strategy_dict, orient='index').T
  144. df.to_sql(name="ctop_ai_kuaishou_intelligence_strategy",
  145. con=self.engine,
  146. if_exists='append',
  147. index=False)
  148. def target_combine_is_subset(self):
  149. """
  150. ctop_ai_kuaishou_signature_recommended_target_combine 表中的 city 和 province 都对应 self.advertiser_strategy 的 region [] varchar
  151. age 对应 ages_range [] varchar
  152. client 对应 platform_os int
  153. business_interest 对应 business_interest varchar
  154. gender 对应 gender int
  155. """
  156. for item in self.signature_target_combine:
  157. gender_bool, gender_dict = is_contains_gender(item['gender'], self.advertiser_strategy['gender'])
  158. age_bool, age_dict = is_contains_age(item['age'],
  159. self.advertiser_strategy['age_min'],
  160. self.advertiser_strategy['age_max'],
  161. self.advertiser_strategy['ages_range'])
  162. region_bool, region_dict = is_contains_region(item['city'], self.advertiser_strategy['region'])
  163. if gender_bool & age_bool & region_bool:
  164. target_combine = {'signature': item['signature']}
  165. target_combine.update(gender_dict)
  166. target_combine.update(age_dict)
  167. target_combine.update(region_dict)
  168. self.final_target_combine.append(target_combine)
  169. else:
  170. # TODO logger
  171. pass
  172. def assembly_group_and_creative_params(self):
  173. """
  174. 拼接参数,请求创建接口
  175. """
  176. group_params, group_params_to_db = self.get_group_params()
  177. creative_params = self.get_creative_params()
  178. self.request_data['group_list'] = []
  179. group_cnt = 1
  180. for item in self.final_target_combine:
  181. group_uuid = str(uuid.uuid4())
  182. group_name = group_params['unit_name']
  183. # 1-2 获取素材名称
  184. sql = """select * from ctop_kuaishou_video_get where account_id = %s and signature = '%s' limit 1""" % \
  185. (self.account_id, item['signature'])
  186. signature_name_df = pd.read(sql, self.engine)
  187. signature_name = signature_name_df['photo_name'].values[0]
  188. group_name.replace('{{素材名称}}', signature_name)
  189. group_name = group_name + '_优质定向' + '_' + str(group_cnt)
  190. group_cnt += 1
  191. def get_group_params(self):
  192. group_params = {'unit_name': self.advertiser_strategy.get('group_name'),
  193. 'put_status': self.advertiser_strategy.get('group_status'),
  194. 'template_id': self.advertiser_strategy.get('template_id'),
  195. 'bid_type': self.advertiser_strategy.get('bid_type'),
  196. 'use_app_market': self.advertiser_strategy.get('use_app_market'),
  197. 'app_store': eval(self.advertiser_strategy.get('app_store')) if self.advertiser_strategy.get('app_store') else None,
  198. 'bid': self.advertiser_strategy.get('bid'),
  199. 'cpa_bid': self.advertiser_strategy.get('cpa_bid'),
  200. 'smart_bid': self.advertiser_strategy.get('smart_bid'),
  201. 'ocpx_action_type': self.advertiser_strategy.get('ocpx_action_type'),
  202. 'deep_conversion_type': self.advertiser_strategy.get('deep_conversion_type'),
  203. 'scene_id': eval(self.advertiser_strategy.get('scene_id')) if self.advertiser_strategy.get('scene_id') else None,
  204. 'unit_type': self.advertiser_strategy.get('unit_type'),
  205. 'begin_time': self.advertiser_strategy.get('begin_time'),
  206. 'end_time': self.advertiser_strategy.get('end_time'),
  207. 'schedule_time': self.advertiser_strategy.get('schedule_time'),
  208. 'day_budget': self.advertiser_strategy.get('unit_day_budget'),
  209. 'day_budget_schedule': eval(self.advertiser_strategy.get('unit_day_budget_schedule'))
  210. if self.advertiser_strategy.get('unit_day_budget_schedule') else None,
  211. 'convert_id': self.advertiser_strategy.get('convert_id'),
  212. 'url_type': self.advertiser_strategy.get('url_type'),
  213. 'web_uri_type': self.advertiser_strategy.get('web_uri_type'),
  214. 'url': self.advertiser_strategy.get('url'),
  215. 'schema_uri': self.advertiser_strategy.get('schema_uri'),
  216. 'app_id': self.advertiser_strategy.get('app_id'),
  217. 'show_mode': self.advertiser_strategy.get('show_mode'),
  218. 'speed': self.advertiser_strategy.get('speed'),
  219. 'site_type': self.advertiser_strategy.get('site_type'),
  220. 'gift_data': self.advertiser_strategy.get('gift_data'),
  221. 'video_landing_page': self.advertiser_strategy.get('video_landing_page'),
  222. 'auto_target': self.advertiser_strategy.get('auto_target'),
  223. 'auto_create_photo': self.advertiser_strategy.get('auto_create_photo'),
  224. 'item_id': self.advertiser_strategy.get('item_id'),
  225. 'merchant_item_put_type ': self.advertiser_strategy.get('merchant_item_put_type '),
  226. 'fiction_id': self.advertiser_strategy.get('fiction_id'),
  227. 'smart_cover': self.advertiser_strategy.get('smart_cover'),
  228. 'asset_mining': self.advertiser_strategy.get('asset_mining'),
  229. 'consult_id': self.advertiser_strategy.get('consult_id'),
  230. 'adv_card_option': self.advertiser_strategy.get('adv_card_option'),
  231. 'adv_card_list': self.advertiser_strategy.get('adv_card_list'),
  232. 'card_type': self.advertiser_strategy.get('card_type'),
  233. 'merchandise_id': self.advertiser_strategy.get('merchandise_id'),
  234. 'merchandise_type': self.advertiser_strategy.get('merchandise_type'),
  235. 'intention_target': self.advertiser_strategy.get('intention_target'),
  236. 'dpa_unit_param': self.advertiser_strategy.get('dpa_unit_param'),
  237. 'region': self.advertiser_strategy.get('region'),
  238. 'district_ids': self.advertiser_strategy.get('district_ids'),
  239. 'user_type': self.advertiser_strategy.get('user_type'),
  240. 'age_min': self.advertiser_strategy.get('age_min'),
  241. 'age_max': self.advertiser_strategy.get('age_min'),
  242. 'ages_range': eval(self.advertiser_strategy.get('ages_range'))
  243. if self.advertiser_strategy.get('ages_range') else None,
  244. 'gender': self.advertiser_strategy.get('gender'),
  245. 'platform_os': self.advertiser_strategy.get('platform_os'),
  246. 'android_osv': self.advertiser_strategy.get('android_osv'),
  247. 'ios_osv': self.advertiser_strategy.get('ios_osv'),
  248. 'network': self.advertiser_strategy.get('network'),
  249. 'device_brand': eval(self.advertiser_strategy.get('device_brand'))
  250. if self.advertiser_strategy.get('device_brand') else None,
  251. 'device_price': self.advertiser_strategy.get('device_price'),
  252. 'business_interest_type': self.advertiser_strategy.get('business_interest_type'),
  253. 'business_interest': eval(self.advertiser_strategy.get('business_interest'))
  254. if self.advertiser_strategy.get('business_interest') else None,
  255. 'fans_star': eval(self.advertiser_strategy.get('fans_star'))
  256. if self.advertiser_strategy.get('fans_star') else None,
  257. 'interest_video': eval(self.advertiser_strategy.get('interest_video'))
  258. if self.advertiser_strategy.get('interest_video') else None,
  259. 'app_interest': eval(self.advertiser_strategy.get('app_interest'))
  260. if self.advertiser_strategy.get('app_interest') else None,
  261. 'app_interest_ids': eval(self.advertiser_strategy.get('app_interest_ids'))
  262. if self.advertiser_strategy.get('app_interest_ids') else None,
  263. 'app_ids': eval(self.advertiser_strategy.get('app_ids'))
  264. if self.advertiser_strategy.get('app_ids') else None,
  265. 'filter_converted_level ': self.advertiser_strategy.get('filter_converted_level '),
  266. 'population': eval(self.advertiser_strategy.get('population'))
  267. if self.advertiser_strategy.get('population') else None,
  268. 'exclude_population': eval(self.advertiser_strategy.get('exclude_population'))
  269. if self.advertiser_strategy.get('exclude_population') else None,
  270. 'paid_audience': eval(self.advertiser_strategy.get('paid_audience'))
  271. if self.advertiser_strategy.get('paid_audience') else None,
  272. 'is_open': self.advertiser_strategy.get('is_open'),
  273. 'no_age_break': self.advertiser_strategy.get('no_age_break'),
  274. 'no_gender_break': self.advertiser_strategy.get('no_gender_break'),
  275. 'no_area_break': self.advertiser_strategy.get('no_area_break'),
  276. 'behavior_interest': self.advertiser_strategy.get('behavior_interest')
  277. }
  278. # smart_cover 和 asset_mining 需要将0/1 转化为 False/True
  279. group_params['smart_cover'] = True if group_params.get('smart_cover', 0) == 1 else False
  280. group_params['asset_mining'] = True if group_params.get('asset_mining', 0) == 1 else False
  281. # 广告组名称,通配符的替换
  282. # 900299 -{{广告位置}}--{{创意制作方式}}--{{日期}}- (日期、素材名称、应用名称、应用标记、创意制作方式、广告位置)
  283. # 1-1 获取应用标记和应用名称
  284. sql = """select app_version, app_name from ctop_kuaishou_app_list where app_id = % limit 1""" % \
  285. group_params.get('app_id')
  286. app_info_df = pd.read_sql(sql, self.engine)
  287. app_version = app_info_df['app_version'].values[0]
  288. app_name = app_info_df['app_name'].values[0]
  289. # 1-2 素材名称, 在拼装广告组和创意层级函数中获取
  290. # 1-3 获取创意制作方式
  291. unit_type = '自定义' if group_params.get('unit_type') == 4 \
  292. else ('程序化创意' if group_params.get('unit_type') == 5 else '程序化创意2.0')
  293. # 1-4 获取广告位置
  294. scene_id_dict = {1: '优选广告位',
  295. 3: '视频播放页广告-便利贴广告',
  296. 5: '联盟广告',
  297. 6: '上下滑大屏广告',
  298. 7: '信息流广告',
  299. 10: '联盟场景'}
  300. scene_id = '+'.join([scene_id_dict[item] for item in group_params.get('scene_id')])
  301. group_name = group_params['group_name'].replace('{{应用标记}}', app_version). \
  302. replace('{{应用名称}}', app_name). \
  303. replace('{{创意制作方式}}', unit_type). \
  304. replace('{{广告位置}}', scene_id)
  305. # 1-2 日期, 在拼装广告组和创意层级函数中获取
  306. now_date = str(datetime.datetime.now().date())
  307. if '日期' in group_params['group_name']:
  308. group_name = group_name.replace('{{日期}}', '_' + now_date)
  309. else:
  310. group_name = group_name + '_' + now_date
  311. group_params['unit_name'] = group_name
  312. # 2 写入数据库的组参数
  313. group_params_to_db = group_params.copy()
  314. group_params_to_db['group_name'] = group_params_to_db.pop('unit_name')
  315. group_params_to_db['ai_strategy_uuid'] = self.ai_strategy_uuid
  316. group_params_to_db['account_id'] = self.account_id
  317. group_params_to_db['campaign_id'] = self.campaign_id
  318. group_params_to_db['operation_type'] = self.operation_type
  319. group_params_to_db['status'] = 1
  320. group_params_to_db['message'] = None
  321. return group_params, group_params_to_db
  322. def get_creative_params(self):
  323. creative_params = {'creative_name': self.advertiser_strategy.get('creative_name'),
  324. 'put_status': self.advertiser_strategy.get('put_status'),
  325. 'action_bar_text': self.advertiser_strategy.get('action_bar_text'),
  326. 'description': self.advertiser_strategy.get('description'),
  327. 'short_slogan': self.advertiser_strategy.get('short_slogan'),
  328. 'sticker_title': self.advertiser_strategy.get('sticker_title'),
  329. 'overlay_type': self.advertiser_strategy.get('overlay_type'),
  330. 'expose_tag': self.advertiser_strategy.get('expose_tag'),
  331. 'new_expose_tag': eval(self.advertiser_strategy.get('new_expose_tag'))
  332. if self.advertiser_strategy.get('new_expose_tag') else None,
  333. 'site_id': self.advertiser_strategy.get('site_id'),
  334. 'click_track_url': self.advertiser_strategy.get('click_track_url'),
  335. 'impression_url': self.advertiser_strategy.get('impression_url'),
  336. 'ad_photo_played_t3s_url': self.advertiser_strategy.get('ad_photo_played_t3s_url'),
  337. 'actionbar_click_url': self.advertiser_strategy.get('actionbar_click_url'),
  338. 'creative_category': self.advertiser_strategy.get('creative_category'),
  339. 'creative_tag': eval(self.advertiser_strategy.get('creative_tag'))
  340. if self.advertiser_strategy.get('creative_tag') else None,
  341. 'live_creative_type': self.advertiser_strategy.get('live_creative_type')
  342. }
  343. return creative_params