ai_target_combine_handler.py 7.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182
  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
  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.advertiser_strategy_id = None
  35. self.advertiser_strategy = {}
  36. self.ai_strategy_uuid = None
  37. def get_database_engine(self):
  38. # 数据库连接引擎,依据开发环境/生产环境 进行切换
  39. if os.getenv('environment', 'unknown') == 'dev':
  40. self.engine = get_db_engine(config['devDB'])
  41. else:
  42. self.engine = get_db_engine(config['productDB'])
  43. def get_advertiser_strategy_info(self):
  44. """
  45. 获取指定账户下客户的策略信息
  46. 为后续的写入库表,校验一致性做准备
  47. """
  48. sql = """
  49. select *
  50. from ctop_ai_kuaishou_advertiser_strategy
  51. where account_id = %d
  52. limit 1
  53. """ % self.account_id
  54. advertiser_strategy_df = pd.read_sql(sql, self.engine)
  55. self.advertiser_strategy_id = int(advertiser_strategy_df['id'].values[0])
  56. self.advertiser_strategy = advertiser_strategy_df.T.to_dict()[0]
  57. def add_campaign(self):
  58. """
  59. 新增广告计划,如果存在则跳过。
  60. response_data = {
  61. "code": 0,
  62. "data": {
  63. "account_id": 23212,
  64. "campaign_id": 30956133,
  65. "campaign_create_time": "2021-01-13 13:50:16"
  66. },
  67. "message": "SUCCESS"}
  68. """
  69. # 1、请求创建广告计划的接口
  70. create_campaign_request_data = {'account_id': self.account_id,
  71. 'campaign_name': self.advertiser_strategy['campaign_name'] + '_优质定向',
  72. 'type': self.advertiser_strategy['campaign_type']}
  73. request = requests.post(url=create_campaign_url,
  74. headers=headers,
  75. data=json.JSONEncoder().encode(create_campaign_request_data))
  76. response_data = json.loads(request.text)
  77. # 2、对接口返回的结果处理
  78. campaign_info_to_db = {
  79. 'campaign_uuid': str(uuid.uuid4()),
  80. 'account_id': self.account_id,
  81. 'ai_strategy_uuid': self.ai_strategy_uuid,
  82. 'campaign_name': self.advertiser_strategy['campaign_name'],
  83. 'campaign_type': self.advertiser_strategy['campaign_type'],
  84. 'create_time': datetime.datetime.now()
  85. }
  86. res = 0
  87. if response_data['code'] == 0:
  88. campaign_info_to_db['campaign_id'] = response_data['data'].get('campaign_id', None)
  89. campaign_info_to_db['campaign_create_time'] = response_data['data'].get('campaign_create_time', None)
  90. campaign_info_to_db['status'] = response_data.get('code', None)
  91. campaign_info_to_db['message'] = response_data.get('message', None)
  92. else:
  93. campaign_info_to_db['message'] = response_data['message']
  94. campaign_info_to_db['status'] = response_data['code']
  95. res = -1
  96. # 3、写入计划层级的操作表
  97. df = pd.DataFrame.from_dict(campaign_info_to_db, orient='index').T
  98. df.to_sql(name="ctop_ai_kuaishou_campaign_level_operation_record",
  99. con=self.engine,
  100. if_exists='append',
  101. index=False)
  102. return res
  103. def get_signature_and_target(self):
  104. """
  105. 从 ctop_ai_kuaishou_signature_recommended_target_combine 表中读取素材和对应的定向
  106. """
  107. sql = """
  108. select * from ctop_ai_kuaishou_signature_recommended_target_combine where project_id = %s and stat_date = curdate()
  109. """ % self.project_id
  110. df = pd.read_sql(sql, self.engine)
  111. # 计算组合的概率,相对于实际投放概率高出了百分之多少
  112. df['improve_ratio'] = (df['combine_estimate_prob'] - df['actual_prob']) / df['actual_prob']
  113. # 两种类型的过滤标准不一样,分开进行判断,然后对结果进行合并
  114. df_1 = df[(df['target_type'] == 'action_ratio') &
  115. (df['improve_ratio'] >= config['filterTargetCombine']['actionRatio']['improveRatio']) &
  116. (df['sample_size'] >= config['filterTargetCombine']['actionRatio']['sampleSize'])]
  117. df_2 = df[(df['target_type'] == 'convertRatio') &
  118. (df['improve_ratio'] >= config['filterTargetCombine']['convertRatio']['improveRatio']) &
  119. (df['sample_size'] >= config['filterTargetCombine']['convertRatio']['sampleSize'])]
  120. merge_df = pd.concat([df_1, df_2], axis=0)
  121. self.signature_target_combine = merge_df[['signature', 'age', 'gender', 'city', 'business', 'province', 'client']]. \
  122. to_dict(orient='records')
  123. def write_intelligence_strategy_table(self):
  124. """
  125. 拼接好的组创意的参数写入到 ctop_ai_kuaishou_intelligence_strategy 中的 ai_strategy_request_content
  126. """
  127. pass
  128. def get_target_intersection(self):
  129. """
  130. 两个表中读取的定向取交集,还是子集?
  131. age struct 可选 自定义年龄段 不传值表示不限,传值具体见下方表格;与ages_range不能同时传
  132. age 字段 类型 是否必填 说明 备注
  133. min int 必填 年龄最小限制 年龄区间最小为18岁
  134. max int 必填 年龄最大限制 年龄区间最大为55岁,且年龄最大限制须大于等于年龄最小限制
  135. ages_range int[] 可选 固定年龄段 与age不能同时传;【18:表示18-23岁】;【24:表示24-30岁】;【31:表示31-40岁】;【41:表示41-49岁】;【50:表示50-100岁】
  136. ctop_ai_kuaishou_signature_recommended_target_combine 表中的 city 和 province 都对应 self.advertiser_strategy 的 region [] varchar
  137. age 对应 ages_range [] varchar
  138. client 对应 platform_os int
  139. business_interest 对应 business_interest varchar
  140. gender 对应 gender int
  141. """
  142. for item in self.signature_target_combine:
  143. gender_bool, gender_dict = is_contains_age(item['gender'], self.advertiser_strategy['gender'])
  144. age_bool, age_dict = is_contains_age(item['age'], self.advertiser_strategy['age'])
  145. if gender_bool & age_bool:
  146. target_combine = {'signature': item['signature']}
  147. target_combine.update(gender_dict)
  148. target_combine.update(age_dict)
  149. else:
  150. pass
  151. pass
  152. def assembly_group_and_creative_params(self):
  153. """
  154. 拼接参数,请求创建接口
  155. """
  156. pass