ai_target_combine_handler.py 39 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680
  1. import uuid
  2. import datetime
  3. import tornado
  4. import json
  5. import traceback
  6. import pandas as pd
  7. import numpy as np
  8. import pymysql
  9. import os
  10. import yaml
  11. import random
  12. import requests
  13. from utils.commonFunc import get_db_engine, is_contains_gender, is_contains_age, is_contains_region
  14. from utils.BaseClass import NpEncoder
  15. from config.url import create_campaign_url, create_group_and_creative_url, headers, update_campaign_status_url
  16. import logging
  17. from concurrent_log import ConcurrentTimedRotatingFileHandler
  18. log_formatter = logging.Formatter('%(asctime)s %(levelname)s %(message)s', '%Y/%m/%d %I:%M:%S %p')
  19. log_handler = ConcurrentTimedRotatingFileHandler("/data/pythonProject/ai_target/logs/ai_target_combine.log", when="midnight", backupCount=100)
  20. log_handler.setFormatter(log_formatter)
  21. logger = logging.getLogger('ai_strategy_request_logger')
  22. logger.addHandler(log_handler)
  23. logger.setLevel(logging.DEBUG)
  24. logger.info("ai_target_combine_logger started!")
  25. print('id of ai_target_combine_logger %s' % id(logger))
  26. with open('/data/pythonProject/ai_target/config/config.yaml', mode='r', encoding='utf-8') as f:
  27. config = yaml.load(f.read(), Loader=yaml.FullLoader)
  28. class AiTargetCombine(tornado.web.RequestHandler):
  29. def post(self):
  30. # 解析接收到的参数
  31. data = self.request.body
  32. data = str(data, 'utf8')
  33. data = json.loads(data, encoding='utf8')
  34. account_id = data['account_id']
  35. project_id = data['project_id']
  36. logger.info("账户信息=%s, 当前进程=%s, 当前进程的主进程=%s" % (account_id, os.getpid(), os.getppid()))
  37. try:
  38. # 1、创建类实例
  39. ins = GetTargetAndAssemblyParameters(account_id, project_id)
  40. # 2、判断target_combine_to_create是否为空,为空则不执行后续的操作
  41. if not ins.target_combine_to_create:
  42. logger.info("account_id = %s, message = '没有定向组合: 定向冲突或者素材关联的创意个数达到上限' " % ins.account_id)
  43. self.write(json.dumps({"account_id": account_id, "message": "没有定向组合: 定向冲突或者素材关联的创意个数达到上限", "code": 1}))
  44. self.flush()
  45. else:
  46. # 2、判断当天是否已经存在包含【优质定向】的广告计划,如果有则获取该计划id,否则需要新建计划
  47. sql = """select campaign_id, campaign_name from ctop_kuaishou_campaign
  48. where account_id = %s
  49. and put_create_time >= curdate()
  50. and campaign_name like '%s'""" % (ins.account_id, '%%优质定向%%')
  51. df = pd.read_sql(sql, ins.engine)
  52. if df.empty:
  53. ins.add_campaign()
  54. else:
  55. ins.campaign_id = df['campaign_id'].values[0]
  56. # 3、拼装广告组和创意的参数
  57. ins.assembly_group_and_creative_params()
  58. # 4、信息写如到智能策略表中
  59. ins.write_intelligence_strategy_table()
  60. # 5、
  61. # 5、发送创建组和创意的请求
  62. request = requests.post(create_group_and_creative_url,
  63. headers=headers,
  64. data=json.dumps(ins.request_data, cls=NpEncoder))
  65. response_data = json.loads(request.text)
  66. # 6、接口的返回信息更新到数据库表
  67. ins.update_intelligence_strategy_table(message=response_data)
  68. logger.info("account_id = %s, 策略uuid = %s 的请求返回信息:%s" % (ins.account_id, ins.ai_strategy_uuid, response_data))
  69. self.write(json.dumps({"account_id": account_id, "message": str(response_data), "code": 1}))
  70. self.flush()
  71. except:
  72. logger.error("account_id = %s, traceback is %s" % (account_id, traceback.format_exc()))
  73. self.write(json.dumps({"account_id": account_id, "message": str(traceback.format_exc()), "code": -1}))
  74. self.flush()
  75. class GetTargetAndAssemblyParameters(object):
  76. def __init__(self, account_id, project_id):
  77. self.account_id = account_id
  78. self.project_id = project_id
  79. self.engine = None
  80. self.product_engine = None
  81. self.db_config = None
  82. self.advertiser_strategy_id = None
  83. self.advertiser_strategy = {}
  84. self.ai_strategy_uuid = str(uuid.uuid4())
  85. self.request_data = {}
  86. self.campaign_id = None
  87. self.operation_type = 1 # 新增广告组
  88. self.target_combine_from_table = []
  89. self.target_combine_filter_by_subset = []
  90. self.target_combine_to_create = []
  91. self.get_database_engine() # 获取数据库信息
  92. self.get_advertiser_strategy_info() # 获取账户配置信息
  93. self.get_signature_and_target() # 从素材定向组合表中获取最新的定向组合
  94. self.filter_target_combine_by_whether_is_subset() # 筛选出为账户配置子集的定向组合
  95. self.filter_target_combine_by_creative_cnt() # 依据素材在指定账户下关联的创意个数,进行筛选创建个数,防止超限导致的创建失败
  96. def get_database_engine(self):
  97. # 数据库连接引擎,依据开发环境/生产环境 进行切换
  98. if os.getenv('LYY_DEV', 'unknown') == 'dev':
  99. self.engine = get_db_engine(config['devDB'])
  100. self.db_config = config['devDB']
  101. else:
  102. self.engine = get_db_engine(config['productDB'])
  103. self.db_config = config['productDB']
  104. # 有些表需要连接生产数据库,不管当前是开发环境还是测试环境
  105. self.product_engine = get_db_engine(config['productDB'])
  106. def get_advertiser_strategy_info(self):
  107. """
  108. 获取指定账户下客户的策略信息(不管是开发环境还是生产环境,该表都读取生产数据库)
  109. 为后续的写入库表,校验一致性做准备
  110. """
  111. sql = """
  112. select *
  113. from ctop_ai_kuaishou_advertiser_strategy
  114. where account_id = %d
  115. limit 1
  116. """ % self.account_id
  117. advertiser_strategy_df = pd.read_sql(sql, self.product_engine)
  118. self.advertiser_strategy_id = int(advertiser_strategy_df['id'].values[0])
  119. self.advertiser_strategy = advertiser_strategy_df.T.to_dict()[0]
  120. def add_campaign(self):
  121. """
  122. 新增广告计划,如果存在则跳过。
  123. response_data = {
  124. "code": 0,
  125. "data": {
  126. "account_id": 23212,
  127. "campaign_id": 30956133,
  128. "campaign_create_time": "2021-01-13 13:50:16"
  129. },
  130. "message": "SUCCESS"}
  131. """
  132. # 1、请求创建广告计划的接口
  133. campaign_name = self.advertiser_strategy['campaign_name']
  134. campaign_name = campaign_name.replace('{{自定义}}', '')
  135. campaign_name = campaign_name.replace('{{日期}}', '')
  136. campaign_name += '_优质定向_'
  137. campaign_name += str(datetime.datetime.now().date())
  138. create_campaign_request_data = {'account_id': self.account_id,
  139. 'campaign_name': campaign_name,
  140. 'type': self.advertiser_strategy['campaign_type'],
  141. 'day_budget': self.advertiser_strategy['campaign_day_budget'],
  142. 'day_budget_schedule': eval(self.advertiser_strategy['campaign_day_budget_schedule'])
  143. if self.advertiser_strategy['campaign_day_budget_schedule'] else None}
  144. request = requests.post(url=create_campaign_url,
  145. headers=headers,
  146. data=json.dumps(create_campaign_request_data, cls=NpEncoder))
  147. response_data = json.loads(request.text)
  148. # 2、对接口返回的结果处理
  149. campaign_info_to_db = {
  150. 'campaign_uuid': str(uuid.uuid4()),
  151. 'account_id': self.account_id,
  152. 'ai_strategy_uuid': self.ai_strategy_uuid,
  153. 'campaign_name': campaign_name,
  154. 'campaign_type': self.advertiser_strategy['campaign_type'],
  155. 'day_budget': self.advertiser_strategy['campaign_day_budget'],
  156. 'day_budget_schedule': self.advertiser_strategy['campaign_day_budget_schedule'],
  157. 'create_time': datetime.datetime.now()
  158. }
  159. res = 0
  160. if response_data['code'] == 0:
  161. self.campaign_id = response_data['data'].get('campaign_id', None)
  162. campaign_info_to_db['campaign_id'] = self.campaign_id
  163. campaign_info_to_db['campaign_create_time'] = response_data['data'].get('campaign_create_time', None)
  164. campaign_info_to_db['status'] = response_data.get('code', None)
  165. campaign_info_to_db['message'] = response_data.get('message', None)
  166. # self.advertiser_strategy['campaign_status'] 如果为暂停,需要创建成功后,调用修改广告计划状态的接口
  167. # `campaign_status` int(11) DEFAULT '1' COMMENT '计划状态: 1:投放 2:暂停 '
  168. if self.advertiser_strategy['campaign_status'] == 2:
  169. update_campaign_status_request = requests.post(url=update_campaign_status_url,
  170. headers=headers,
  171. data=json.dumps({'accountId': self.account_id,
  172. 'putStatus': 2,
  173. 'userId': '113dee46c7df464da78c07a985e92cd1',
  174. 'campaignIds': [self.campaign_id]},
  175. cls=NpEncoder))
  176. update_campaign_status_response_data = json.loads(update_campaign_status_request.text)
  177. if update_campaign_status_response_data.get('result') and \
  178. update_campaign_status_response_data.get('result').get('failCount') == 0:
  179. logger.info("账户 %s 下的广告计划 %s 暂停成功!" % (self.account_id, self.campaign_id))
  180. else:
  181. logger.error("账户 %s 下的广告计划 %s 暂停失败,异常信息: %s!" %
  182. (self.account_id,
  183. self.campaign_id,
  184. str(update_campaign_status_response_data.get('result'))))
  185. raise Exception("账户 %s 下的广告计划 %s 暂停失败!" % (self.account_id, self.campaign_id))
  186. else:
  187. campaign_info_to_db['message'] = response_data['message']
  188. campaign_info_to_db['status'] = response_data['code']
  189. res = -1
  190. raise Exception("账户 %s,新建广告计划失败,详细:%s!" % (self.account_id, str(response_data)))
  191. # 3、写入计划层级的操作表
  192. df = pd.DataFrame.from_dict(campaign_info_to_db, orient='index').T
  193. df.to_sql(name="ctop_ai_kuaishou_campaign_level_operation_record",
  194. con=self.engine,
  195. if_exists='append',
  196. index=False)
  197. return res
  198. def get_signature_and_target(self):
  199. """
  200. 从 ctop_ai_kuaishou_signature_recommended_target_combine 表中读取素材和对应的定向
  201. 读取配置文件的条件,筛选出符合条件的定向组合
  202. """
  203. sql = """
  204. select * from ctop_ai_kuaishou_signature_recommended_target_combine where project_id = %s
  205. and stat_date = (select max(stat_date) from ctop_ai_kuaishou_signature_recommended_target_combine)
  206. """ % self.project_id
  207. df = pd.read_sql(sql, self.engine)
  208. # 计算组合的概率,相对于实际投放概率高出了百分之多少,样本量是否达标(读取配置文件),且p值小于等于0.05
  209. df['improve_ratio'] = (df['combine_estimate_prob'] - df['actual_prob']) / df['actual_prob']
  210. # 两种类型的过滤标准不一样,分开进行判断,然后对结果进行合并
  211. df_1 = df[(df['target_type'] == 'action_ratio') &
  212. (df['improve_ratio'] >= config['filterTargetCombine']['actionRatio']['improveRatio']) &
  213. (df['sample_size'] >= config['filterTargetCombine']['actionRatio']['sampleSize']) &
  214. (df['p_value'] <= 0.05)]
  215. df_2 = df[(df['target_type'] == 'convert_ratio') &
  216. (df['improve_ratio'] >= config['filterTargetCombine']['convertRatio']['improveRatio']) &
  217. (df['sample_size'] >= config['filterTargetCombine']['convertRatio']['sampleSize']) &
  218. (df['p_value'] <= 0.05)]
  219. merge_df = pd.concat([df_1, df_2], axis=0)
  220. # 'age', 'gender', 'city', 'business', 'province', 'client'
  221. features = [key for key, value in config['bayesDim'].items() if value['isOn']]
  222. self.target_combine_from_table = merge_df[['id', 'signature'] + features].to_dict(orient='records')
  223. def write_intelligence_strategy_table(self):
  224. """
  225. 拼接好的组创意的参数写入到 ctop_ai_kuaishou_intelligence_strategy 中的 ai_strategy_request_content
  226. """
  227. intelligence_strategy_dict = \
  228. {
  229. 'ai_strategy_uuid': self.ai_strategy_uuid,
  230. 'advertiser_strategy_id': self.advertiser_strategy_id,
  231. 'account_id': self.account_id,
  232. 'ai_strategy_request_content': str(self.request_data),
  233. 'ai_strategy_remark': '优质定向组合',
  234. 'create_time': datetime.datetime.now()
  235. }
  236. df = pd.DataFrame.from_dict(intelligence_strategy_dict, orient='index').T
  237. df.to_sql(name="ctop_ai_kuaishou_intelligence_strategy",
  238. con=self.engine,
  239. if_exists='append',
  240. index=False)
  241. def filter_target_combine_by_whether_is_subset(self):
  242. """
  243. ctop_ai_kuaishou_signature_recommended_target_combine 表中的 city 和 province 都对应 self.advertiser_strategy 的 region [] varchar
  244. age 对应 ages_range [] varchar
  245. client 对应 platform_os int
  246. business_interest 对应 business_interest varchar
  247. gender 对应 gender int
  248. """
  249. gender_bool = True
  250. age_bool = True
  251. region_bool = True
  252. for item in self.target_combine_from_table:
  253. if 'gender' in item.keys():
  254. gender_bool, gender_dict = is_contains_gender(item['gender'], self.advertiser_strategy['gender'])
  255. if 'age' in item.keys():
  256. age_bool, age_dict = is_contains_age(item['age'],
  257. self.advertiser_strategy['age_min'],
  258. self.advertiser_strategy['age_max'],
  259. self.advertiser_strategy['ages_range'])
  260. if 'city' in item.keys():
  261. region_bool, region_dict = is_contains_region(item['city'], self.advertiser_strategy['region'])
  262. if gender_bool & age_bool & region_bool:
  263. target_combine = {'signature': item['signature'], 'target_combine_id': item['id']}
  264. target_combine.update(gender_dict) if 'gender' in item.keys() else None
  265. target_combine.update(age_dict) if 'age' in item.keys() else None
  266. target_combine.update(region_dict) if 'city' in item.keys() else None
  267. self.target_combine_filter_by_subset.append(target_combine)
  268. else:
  269. logger.info("推荐定向:%s 与 账户 %s 配置信息里的定向(gender:%s, age_min: %s,age_max: %s, ages_ranges:%s,region:%s )存在冲突" %
  270. (item,
  271. self.account_id,
  272. self.advertiser_strategy['gender'],
  273. self.advertiser_strategy['age_min'],
  274. self.advertiser_strategy['age_max'],
  275. self.advertiser_strategy['ages_range'],
  276. self.advertiser_strategy['region']))
  277. def filter_target_combine_by_creative_cnt(self):
  278. """
  279. 依据素材已经关联的创意个数,来定素材可以创建的定向组合个数, 并从中随机随着N个
  280. """
  281. if not self.target_combine_filter_by_subset:
  282. return None
  283. # 1-1 获取素材在该账户下关联的创意个数,过滤掉素材关联创意个数超过200的情况
  284. df = pd.DataFrame(self.target_combine_filter_by_subset)
  285. # target_combine_filter_by_subset 转化为 dataframe 时 age_min, age_max 会填充为 np.nan,
  286. # 会导致后续拼装的参数发送给java服务报错(jackson.core.JsonParseException: Non-standard token \'NaN\':)
  287. # 需要把 np.nan 替换为 None
  288. df.replace({np.nan: None}, inplace=True)
  289. # signature in ('03b93728f0d82ea7865c3c7cf632bc1b',),避免这样的sql报错
  290. if df.signature.nunique() == 1:
  291. sig_lst = tuple(list(df.signature.unique()) * 2)
  292. else:
  293. sig_lst = tuple(list(df.signature.unique()))
  294. sql = """
  295. select t1.signature, t2.creative_count
  296. from
  297. (select account_id, signature, photo_id from ctop_kuaishou_video_get
  298. where account_id = %s and signature in %s
  299. group by account_id, signature) t1
  300. left join
  301. ctop_kuaishou_video_relate_creatives t2
  302. on t1.account_id = t2.account_id and t1.photo_id = t2.photo_id
  303. """ % (self.account_id, sig_lst)
  304. creative_cnt_df = pd.read_sql(sql, self.product_engine)
  305. creative_cnt_df = creative_cnt_df[creative_cnt_df.creative_count < 200]
  306. # 1-2 计算每个素材还能创建的广告组(定向)个数: (200 - 已关联创意个数) / 15
  307. creative_cnt_df['target_combine_cnt'] = np.floor((200 - creative_cnt_df['creative_count']) / 15)
  308. creative_cnt_df = creative_cnt_df[creative_cnt_df.target_combine_cnt >= 1]
  309. final_target_combine = pd.DataFrame([])
  310. for sig in creative_cnt_df.signature.unique():
  311. n = creative_cnt_df[creative_cnt_df.signature == sig].target_combine_cnt.values[0]
  312. now = datetime.datetime.now()
  313. # Cannot take a larger sample than population when 'replace=False'
  314. n = n if n <= len(df[df.signature == sig]) else len(df[df.signature == sig])
  315. sig_target_df = df[df.signature == sig].sample(int(n), axis=0, random_state=(now.year + now.month + now.day))
  316. final_target_combine = final_target_combine.append(sig_target_df)
  317. self.target_combine_to_create = final_target_combine.to_dict(orient='records')
  318. # 从集合中随机选取N个组合
  319. combine_cnt = config['filterTargetCombine']['combineCnt']
  320. random.shuffle(self.target_combine_to_create)
  321. self.target_combine_to_create = self.target_combine_to_create[:combine_cnt]
  322. def assembly_group_and_creative_params(self):
  323. """
  324. 拼接参数,请求创建接口
  325. """
  326. group_params, group_params_to_db = self.get_group_params()
  327. creative_params, creative_params_to_db = self.get_creative_params()
  328. self.request_data['group_list'] = []
  329. group_cnt = 1
  330. batch_group_params_to_db = []
  331. batch_creative_params_to_db = []
  332. for item in self.target_combine_to_create:
  333. # 使用优质定向更新组层级的参数
  334. target_combine = item.copy()
  335. del target_combine['target_combine_id']
  336. del target_combine['signature']
  337. group_params.update(target_combine)
  338. # 写入数据库的字段,list类型需要转化为str
  339. group_params_to_db.update(target_combine)
  340. cols = ['ages_range', 'region']
  341. for col in cols:
  342. if col in target_combine.keys():
  343. group_params_to_db[col] = str(group_params_to_db[col])
  344. group_uuid = str(uuid.uuid4())
  345. group_name = group_params['unit_name']
  346. # 获取素材名称, 在广告组命名中进行替换 (同时获取photo_id, 用于拼装创意层级的参数)
  347. sql = """select photo_id, photo_name, material_type
  348. from ctop_kuaishou_video_get where account_id = %s and signature = '%s' limit 1""" % \
  349. (self.account_id, item['signature'])
  350. signature_df = pd.read_sql(sql, self.product_engine)
  351. photo_name = signature_df['photo_name'].values[0] if not signature_df.empty else 'unknown'
  352. photo_id = signature_df['photo_id'].values[0] if not signature_df.empty else None
  353. material_type = signature_df['material_type'].values[0] if not signature_df.empty else None
  354. group_name.replace('{{素材名称}}', photo_name)
  355. group_name = group_name + '_优质定向' + '_' + str(group_cnt)
  356. # 写入数据表中组信息
  357. single_group_params_to_db = group_params_to_db.copy()
  358. single_group_params_to_db['target_combine_id'] = item['target_combine_id']
  359. single_group_params_to_db['group_uuid'] = group_uuid
  360. single_group_params_to_db['group_name'] = group_name
  361. single_group_params_to_db['create_time'] = datetime.datetime.now()
  362. batch_group_params_to_db.append(single_group_params_to_db)
  363. # 拼装返回json的组信息
  364. single_group_params_to_request = group_params.copy()
  365. single_group_params_to_request['group_uuid'] = group_uuid
  366. single_group_params_to_request['unit_name'] = group_name
  367. single_group_params_to_request['creative_list'] = []
  368. group_cnt += 1
  369. # 拼装广告组下面的创意信息
  370. # 一个素材组合为15个一样的创意,因信息流已经下架,不需要提供封面信息
  371. for creative_cnt in range(15):
  372. creative_uuid = str(uuid.uuid4())
  373. creative_name = creative_params_to_db['creative_name'] + '_' + str(creative_cnt + 1)
  374. single_creative_params_to_db = creative_params_to_db.copy()
  375. single_creative_params_to_db['creative_uuid'] = creative_uuid
  376. single_creative_params_to_db['creative_name'] = creative_name
  377. single_creative_params_to_db['photo_id'] = int(photo_id) if photo_id else None
  378. single_creative_params_to_db['creative_material_type'] = material_type
  379. single_creative_params_to_db['create_time'] = datetime.datetime.now()
  380. batch_creative_params_to_db.append(single_creative_params_to_db)
  381. single_creative_params_to_request = creative_params.copy()
  382. single_creative_params_to_request['creative_uuid'] = creative_uuid
  383. single_creative_params_to_request['creative_name'] = creative_name
  384. single_creative_params_to_request['photo_id'] = int(photo_id) if photo_id else None
  385. single_creative_params_to_request['creative_material_type'] = material_type
  386. # 单个创意信息加入到组里面的 creative_list 中
  387. single_group_params_to_request['creative_list'].append(single_creative_params_to_request)
  388. # 组和创意信息添加到最终返回数据 group_list 中
  389. self.request_data['group_list'].append(single_group_params_to_request)
  390. # 创意层级信息批量写入数据库
  391. creative_df = pd.DataFrame(batch_creative_params_to_db)
  392. creative_df.to_sql(name="ctop_ai_kuaishou_creative_level_operation_record",
  393. con=self.engine,
  394. if_exists='append',
  395. index=False)
  396. # 组层级信息批量写入数据库
  397. group_df = pd.DataFrame(batch_group_params_to_db)
  398. group_df.to_sql(name="ctop_ai_kuaishou_unit_level_operation_record",
  399. con=self.engine,
  400. if_exists='append',
  401. index=False)
  402. self.request_data['account_id'] = self.account_id
  403. self.request_data['campaign_id'] = self.campaign_id
  404. def get_group_params(self):
  405. # 1、从账户信息表读取基本的配置信息
  406. group_params = {'unit_name': self.advertiser_strategy.get('group_name'),
  407. 'put_status': self.advertiser_strategy.get('group_status'),
  408. 'template_id': self.advertiser_strategy.get('template_id'),
  409. 'bid_type': self.advertiser_strategy.get('bid_type'),
  410. 'use_app_market': self.advertiser_strategy.get('use_app_market'),
  411. 'app_store': self.advertiser_strategy.get('app_store'),
  412. 'bid': self.advertiser_strategy.get('bid'),
  413. 'cpa_bid': self.advertiser_strategy.get('cpa_bid'),
  414. 'smart_bid': self.advertiser_strategy.get('smart_bid'),
  415. 'ocpx_action_type': self.advertiser_strategy.get('ocpx_action_type'),
  416. 'deep_conversion_type': self.advertiser_strategy.get('deep_conversion_type'),
  417. 'roi_ratio': self.advertiser_strategy.get('roi_ratio'),
  418. 'deep_conversion_bid': self.advertiser_strategy.get('deep_conversion_bid'),
  419. 'scene_id': self.advertiser_strategy.get('scene_id'),
  420. 'unit_type': self.advertiser_strategy.get('unit_type'),
  421. 'begin_time': self.advertiser_strategy.get('begin_time') if self.advertiser_strategy.get('begin_time')
  422. else str(datetime.datetime.now().date()),
  423. 'end_time': self.advertiser_strategy.get('end_time'),
  424. 'schedule_time': self.advertiser_strategy.get('schedule_time'),
  425. 'day_budget': self.advertiser_strategy.get('unit_day_budget'),
  426. 'day_budget_schedule': self.advertiser_strategy.get('unit_day_budget_schedule'),
  427. 'convert_id': self.advertiser_strategy.get('convert_id'),
  428. 'url_type': self.advertiser_strategy.get('url_type'),
  429. 'web_uri_type': self.advertiser_strategy.get('web_uri_type'),
  430. 'url': self.advertiser_strategy.get('url'),
  431. 'schema_uri': self.advertiser_strategy.get('schema_uri'),
  432. 'app_id': self.advertiser_strategy.get('app_id'),
  433. 'show_mode': self.advertiser_strategy.get('show_mode'),
  434. 'speed': self.advertiser_strategy.get('speed'),
  435. 'site_type': self.advertiser_strategy.get('site_type'),
  436. 'gift_data': self.advertiser_strategy.get('gift_data'),
  437. 'video_landing_page': self.advertiser_strategy.get('video_landing_page'),
  438. 'auto_target': self.advertiser_strategy.get('auto_target'),
  439. 'auto_create_photo': self.advertiser_strategy.get('auto_create_photo'),
  440. # 'item_id': self.advertiser_strategy.get('item_id'),
  441. # 'merchant_item_put_type': self.advertiser_strategy.get('merchant_item_put_type'),
  442. # 'fiction_id': self.advertiser_strategy.get('fiction_id'),
  443. 'smart_cover': self.advertiser_strategy.get('smart_cover'),
  444. 'asset_mining': self.advertiser_strategy.get('asset_mining'),
  445. # 'consult_id': self.advertiser_strategy.get('consult_id'),
  446. # 'adv_card_option': self.advertiser_strategy.get('adv_card_option'),
  447. # 'adv_card_list': self.advertiser_strategy.get('adv_card_list'),
  448. # 'card_type': self.advertiser_strategy.get('card_type'),
  449. # 'merchandise_id': self.advertiser_strategy.get('merchandise_id'),
  450. # 'merchandise_type': self.advertiser_strategy.get('merchandise_type'),
  451. # 'intention_target': self.advertiser_strategy.get('intention_target'),
  452. # 'playable_id': self.advertiser_strategy.get('playable_id'),
  453. # 'play_button': self.advertiser_strategy.get('play_button'),
  454. # 'dpa_unit_param': self.advertiser_strategy.get('dpa_unit_param'),
  455. 'region': self.advertiser_strategy.get('region'),
  456. 'district_ids': self.advertiser_strategy.get('district_ids'),
  457. 'user_type': self.advertiser_strategy.get('user_type'),
  458. 'age_min': self.advertiser_strategy.get('age_min'),
  459. 'age_max': self.advertiser_strategy.get('age_min'),
  460. 'ages_range': self.advertiser_strategy.get('ages_range'),
  461. 'gender': self.advertiser_strategy.get('gender'),
  462. 'platform_os': self.advertiser_strategy.get('platform_os'),
  463. 'android_osv': self.advertiser_strategy.get('android_osv'),
  464. 'ios_osv': self.advertiser_strategy.get('ios_osv'),
  465. 'network': self.advertiser_strategy.get('network'),
  466. 'device_brand': self.advertiser_strategy.get('device_brand'),
  467. 'device_price': self.advertiser_strategy.get('device_price'),
  468. 'business_interest_type': self.advertiser_strategy.get('business_interest_type'),
  469. 'business_interest': self.advertiser_strategy.get('business_interest'),
  470. 'fans_star': self.advertiser_strategy.get('fans_star'),
  471. 'interest_video': self.advertiser_strategy.get('interest_video'),
  472. 'app_interest': self.advertiser_strategy.get('app_interest'),
  473. # 'app_interest_ids': self.advertiser_strategy.get('app_interest_ids'),
  474. 'app_ids': self.advertiser_strategy.get('app_ids'),
  475. 'filter_converted_level': self.advertiser_strategy.get('filter_converted_level'),
  476. 'population': self.advertiser_strategy.get('population'),
  477. 'exclude_population': self.advertiser_strategy.get('exclude_population'),
  478. 'paid_audience': self.advertiser_strategy.get('paid_audience'),
  479. 'splash_ad_switch': self.advertiser_strategy.get('splash_ad_switch'),
  480. 'is_open': self.advertiser_strategy.get('is_open'),
  481. 'no_age_break': self.advertiser_strategy.get('no_age_break'),
  482. 'no_gender_break': self.advertiser_strategy.get('no_gender_break'),
  483. 'no_area_break': self.advertiser_strategy.get('no_area_break'),
  484. 'behavior_interest': self.advertiser_strategy.get('behavior_interest')
  485. }
  486. group_params['unit_name'] = group_params['unit_name'].replace('{{自定义}}', '')
  487. # 2、smart_cover 和 asset_mining 需要将0/1 转化为 False/True
  488. group_params['smart_cover'] = True if group_params.get('smart_cover', 0) == 1 else False
  489. group_params['asset_mining'] = True if group_params.get('asset_mining', 0) == 1 else False
  490. # 3、广告组名称,通配符的替换
  491. # 900299 -{{广告位置}}--{{创意制作方式}}--{{日期}}- (日期、素材名称、应用名称、应用标记、创意制作方式、广告位置)
  492. # 3-1 获取应用标记和应用名称
  493. sql = """select app_version, app_name from ctop_kuaishou_app_list where app_id = %s limit 1""" % \
  494. group_params.get('app_id', -1)
  495. app_info_df = pd.read_sql(sql, self.engine)
  496. app_version = app_info_df['app_version'].values[0] if not app_info_df.empty else 'unknown'
  497. app_name = app_info_df['app_name'].values[0] if not app_info_df.empty else 'unknown'
  498. # 3-2 素材名称, 在拼装广告组和创意层级函数中获取
  499. # 3-3 获取创意制作方式
  500. unit_type = '自定义' if group_params.get('unit_type') == 4 \
  501. else ('程序化创意' if group_params.get('unit_type') == 5 else '程序化创意2.0')
  502. # 3-4 获取广告位置
  503. scene_id_dict = {1: '优选广告位',
  504. 3: '视频播放页广告-便利贴广告',
  505. 5: '联盟广告',
  506. 6: '上下滑大屏广告',
  507. 7: '信息流广告',
  508. 10: '联盟场景'}
  509. scene_id = '+'.join([scene_id_dict[item] for item in eval(group_params.get('scene_id'))])
  510. unit_name = group_params['unit_name'].replace('{{应用标记}}', app_version). \
  511. replace('{{应用名称}}', app_name). \
  512. replace('{{创意制作方式}}', unit_type). \
  513. replace('{{广告位置}}', scene_id)
  514. # 3-5 日期, 在拼装广告组和创意层级函数中获取
  515. now_date = datetime.date.today().strftime('%Y-%m-%d')
  516. if '日期' in group_params['unit_name']:
  517. unit_name = unit_name.replace('{{日期}}', now_date)
  518. else:
  519. unit_name = unit_name + '_' + now_date
  520. group_params['unit_name'] = unit_name
  521. # 4、处理 cpa_bid
  522. # 因为存在 【单出价请输入单数字,阶梯出价请用/隔开】 如:1900/2000/2100 cpa_bid varchar(255) DEFAULT NULL COMMENT '出价'
  523. # 处理方式按'/'进行分割,取最后一个值(也就是最大值),并转化为int型
  524. group_params['cpa_bid'] = int(group_params['cpa_bid'].split('/')[-1]) if '/' in group_params['cpa_bid'] \
  525. else (int(group_params['cpa_bid'].split('-')[-1]) if '-' in group_params['cpa_bid'] else int(group_params['cpa_bid']))
  526. # 5、 写入数据库的组参数
  527. group_params_to_db = group_params.copy()
  528. # 5-1 需要删除的字段
  529. drop_cols = ['put_status', 'splash_ad_switch']
  530. for col in drop_cols:
  531. if col in group_params_to_db.keys():
  532. del group_params_to_db[col]
  533. group_params_to_db['group_name'] = group_params_to_db.pop('unit_name')
  534. group_params_to_db['ai_strategy_uuid'] = self.ai_strategy_uuid
  535. group_params_to_db['account_id'] = self.account_id
  536. group_params_to_db['campaign_id'] = self.campaign_id
  537. group_params_to_db['operation_type'] = self.operation_type
  538. group_params_to_db['status'] = 1
  539. group_params_to_db['message'] = None
  540. # 6、用于组装发送请求的部分字段,需要转化为list类型
  541. cols_to_list = ['app_store', 'scene_id', 'region', 'day_budget_schedule', 'ages_range', 'device_brand', 'business_interest',
  542. 'fans_star', 'interest_video', 'app_interest', 'app_interest_ids', 'app_ids', 'population', 'district_ids',
  543. 'exclude_population', 'paid_audience']
  544. for col in cols_to_list:
  545. if col in group_params.keys():
  546. group_params[col] = eval(group_params[col]) if group_params[col] else None
  547. return group_params, group_params_to_db
  548. def get_creative_params(self):
  549. # 1、从账户信息表读取基本的配置信息
  550. creative_params = {'creative_name': self.advertiser_strategy.get('creative_name'),
  551. 'put_status': self.advertiser_strategy.get('group_status'),
  552. 'action_bar_text': self.advertiser_strategy.get('action_bar_text'),
  553. 'description': self.advertiser_strategy.get('description'),
  554. 'short_slogan': self.advertiser_strategy.get('short_slogan'),
  555. 'sticker_title': self.advertiser_strategy.get('sticker_title'),
  556. 'overlay_type': self.advertiser_strategy.get('overlay_type'),
  557. 'expose_tag': self.advertiser_strategy.get('expose_tag'),
  558. 'new_expose_tag': self.advertiser_strategy.get('new_expose_tag'),
  559. 'site_id': self.advertiser_strategy.get('site_id'),
  560. 'click_track_url': self.advertiser_strategy.get('click_track_url'),
  561. 'impression_url': self.advertiser_strategy.get('impression_url'),
  562. 'ad_photo_played_t3s_url': self.advertiser_strategy.get('ad_photo_played_t3s_url'),
  563. 'actionbar_click_url': self.advertiser_strategy.get('actionbar_click_url'),
  564. 'creative_category': self.advertiser_strategy.get('creative_category'),
  565. 'creative_tag': self.advertiser_strategy.get('creative_tag'),
  566. 'live_creative_type': self.advertiser_strategy.get('live_creative_type')
  567. }
  568. # 1、description 可能存在多个值,需要从中随机获取一个
  569. if creative_params['description'] is not None:
  570. description_lst = eval(creative_params['description'])
  571. random.shuffle(description_lst)
  572. creative_params['description'] = description_lst[0]
  573. # 2 写入数据库的创意参数
  574. creative_params_to_db = creative_params.copy()
  575. drop_cols = ['put_status', 'live_creative_type']
  576. for col in drop_cols:
  577. if col in creative_params_to_db:
  578. del creative_params_to_db[col]
  579. creative_params_to_db['ai_strategy_uuid'] = self.ai_strategy_uuid
  580. creative_params_to_db['account_id'] = self.account_id
  581. creative_params_to_db['campaign_id'] = self.campaign_id
  582. creative_params_to_db['operation_type'] = self.operation_type
  583. creative_params_to_db['status'] = 1
  584. creative_params_to_db['message'] = None
  585. # 3、用于组装发送请求的部分字段,需要转化为list类型
  586. cols_to_list = ['creative_tag', 'new_expose_tag']
  587. for col in cols_to_list:
  588. if col in creative_params.keys():
  589. creative_params[col] = eval(creative_params[col]) if creative_params[col] else None
  590. return creative_params, creative_params_to_db
  591. def update_intelligence_strategy_table(self, message=None):
  592. """
  593. 更新 intelligence_strategy_table
  594. :return:
  595. """
  596. db_con = pymysql.connect(host=self.db_config['host'],
  597. port=self.db_config['port'],
  598. user=self.db_config['username'],
  599. password=self.db_config['password'],
  600. database=self.db_config['database'],
  601. charset='utf8')
  602. cursor = db_con.cursor()
  603. if message:
  604. cursor.execute("""UPDATE ctop_ai_kuaishou_intelligence_strategy
  605. SET message = %s
  606. WHERE ai_strategy_uuid = %s""", (str(message), self.ai_strategy_uuid))
  607. db_con.commit()
  608. db_con.close()