xuelin-0
commented
6 years ago 袁沅 8-23 17:24:08 现在说w的值可以暂时不改~
袁沅 8-23 17:24:19 但是广告组维度的 cpa 错了/
袁沅 8-23 18:21:29 那个暂时不改啦。
薛霖 8-23 18:21:46 全都不改？
袁沅 8-23 18:21:49 以后这个还需要改的~
薛霖 8-23 18:22:06 是不是现在改起来很麻烦
袁沅 8-23 18:22:16 整个单价的值都有点问题~
袁沅 8-23 18:22:45 web_price_cny 这个值不准
袁沅 8-23 18:23:09 而且 6.6
袁沅 8-23 18:23:24 在数据逻辑那边也是一个变量
袁沅 8-23 18:23:31 汇率
描述： 广告分析模块，广告组/建站URL页签（W系数、目标W系数、止损W系数、盈利CPA、止损CPA）合计的数据与列表数据不一致（合计数据是计算得来，列表数据直接取值/平均值）
步骤： 1.进入广告分析-广告组/建站URL页签 2.查询任意一条数据记录 3.数据库SQL查询： select count(1) over() total_count, t.facebook_adset_id, sum(t.effective_count) as effective_count, sum(t.spend_usd) as spend_usd, sum(t.spend_cny) as spend_cny, sum(t.impression_count) as impression_count, sum(t.link_click_count) as link_click_count, round(decode(nvl(sum(t.effective_count), 0), 0, null, sum(t.spend_usd) / sum(t.effective_count)), 4) as cpa, round(decode(nvl(sum(t.link_click_count), 0), 0, null, sum(t.spend_usd) / sum(t.link_click_count)), 4) as cpc, round(decode(nvl(sum(t.impression_count), 0), 0, null, sum(t.spend_usd) / sum(t.impression_count) 1000), 4) as cpm, round(decode(nvl(sum(t.impression_count), 0), 0, null, sum(t.link_click_count)) / sum(t.impression_count), 4) as ctr, round(decode(nvl(sum(t.link_click_count), 0), 0, null, sum(t.effective_count) / sum(t.link_click_count)), 4) as cvr, avg(t.web_price_cny) web_price_cny, round(round(decode(nvl(sum(t.impression_count), 0), 0, null, sum(t.link_click_count) / sum(t.impression_count)), 4) round(decode(nvl(sum(t.link_click_count), 0), 0, null, sum(t.effective_count) / sum(t.link_click_count)), 4) 1000, 4) as w, --ctr cvr 1000
round(round(decode(nvl(sum(t.impression_count), 0), 0, null, sum(t.spend_usd) / sum(t.impression_count) 1000), 4) / round((avg(t.web_price_cny) 0.75 - 40 - 40 - 50) / 6.6 0.85, 4), 4) as target_w, --目标W系数 =cpm / 盈利cpa
round(round(decode(nvl(sum(t.impression_count), 0), 0, null, sum(t.spend_usd) / sum(t.impression_count) 1000), 4) / round((avg(t.web_price_cny) 0.75 - 40 - 40 - 0) / 6.6 0.85, 4), 4) as limit_w, --止损w系数 = cpm/止损cpa round((avg(t.web_price_cny) 0.75 - 40 - 40 - 50) / 6.6 0.85,4) as target_cpa, round((avg(t.web_price_cny) 0.75 - 40 - 40) / 6.6 * 0.85,4) as limit_cpa
from DWS.V_DWS_AMS_ADSET_STAT_D t GROUP BY t.facebook_adset_id ;
实际结果： 前端展示查询结果：
SQL查询结果：
期望结果： 广告组/建站URL页签（W系数、目标W系数、止损W系数、盈利CPA、止损CPA）合计的数据与列表数据应一致