123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772 |
- <?php
- const ChannelTrackMaps = [
- "Organic" => [ "g1b8nag" ],
- "游戏内部跟踪链接" => [ "yhm47x1", "sij469x", "ynlt7ad", "l67x2mt", "6jq5m5g", "a7krf04", "v4x1byi", "327ogs3", "vt67uin", "cl7yqth", "fqupiw4", "b8q4iua", "x2ao8dk", "tri542t", "c9h1f36", "nua60j4", "o5rfavc" ],
- "社媒KOL" => [ "ys85rvc", "n3l2809", "d2p6i5s", "oscpr9r" ],
- "各平台主页渠道" => [ "a9nv7dj", "53ece3e", "1z942q1", "d7wopl7", "j7u6h9x", "to6eamo" ],
- "邮件营销" => [ "cj6x62s", "pgdmu1z", "4x6dr4w" ],
- "游戏外邀请" => [ "1jn49bp", "y8phovy" ],
- "Facebook" => [ "2xgabxp", "k1qjyjr", "cwvjh9o", "oss8d1v" ],
- "Google_ads" => [ "ussdj6t", "n6mofty" ],
- "苹果商店页搜索广告" => [ "rs64xuy" ],
- "Unity" => [ "u69nimr" ],
- "Applovin" => [ "kkph0xk" ],
- "IronSource" => [ "vjjyyf2" ],
- "Chartboost" => [ "14dbbj0" ],
- "Mintegral" => [ "r2phf78" ],
- "Vungle" => [ "6j6t1sg" ],
- "Inplayable" => [ "n4tb7gp" ],
- "Linkme" => [ "yw8hp34" ],
- "Tecdo" => [ "vbc3665" ],
- "Virgrilmedia" => [ "6366s1a" ],
- "TikTok" => [ "6xxj7ry" ],
- "Prodege" => [ "ji2y5ap" ],
- ];
- const ViewChannels = [
- "Facebook", "Google_ads", "Mintegral", "苹果商店页搜索广告", "TikTok"
- ];
- if (substr(PHP_SAPI_NAME(), 0, 3) !== 'cli') {
- die("The program runs only in CLI mode!");
- }
- const API_TOKEN = 'Tha-k75HDFTdG9WE_s58';
- const APP_TOKEN = 'rv3fkx1a8em8';
- const DB_CONN = 'backend';
- const API_URL = 'https://dash.adjust.com/control-center/reports-service/report?';
- !defined('TIMESTAMP') && define('TIMESTAMP', time());
- $sid = $lid = 1;
- include_once dirname(__FILE__) . "/crontab.inc.php";
- include_once dirname(__FILE__) . "/../com.php";
- set_time_limit(0);
- $hi = date('Hi', TIMESTAMP);
- if (!empty($argv[1]))$hi = $argv[1];
- oo::logs()->debug3( "hi:{$hi}, time:". TIMESTAMP, 'adjustDataCrontab');
- switch ($hi){
- case '0000':// 8点00分 拉取周报
- case '0800':// 8点00分 拉取周报
- Adjust::adjustReport('week') ;
- break;
- case '0015':// 8点15分 拉取周报
- case '0815':// 8点15分 拉取周报
- Adjust::adjustReport('day') ;
- break;
- case '0030':// 8点30分 拉取月报
- case '0830':// 8点30分 拉取月报
- Adjust::adjustReport('month') ;
- break;
- case '0045':// 8点45分 拉取日报明细
- Adjust::adjustReportCampaign() ;
- break;
- case '0845':// 8点45分 拉取日报明细
- Adjust::adjustReportCampaign(7) ;
- break;
- case '0010':// 9点 拉取日报明细
- Adjust::adjustReportViews() ;
- break;
- case '0810':// 9点 拉取日报明细
- Adjust::adjustReportViews(7) ;
- break;
- case '0001':// prodege 七日过岛120 人数
- Adjust::prodegeSpend() ;
- break;
- case '0005':// asaction 七日过岛100 人数
- Adjust::adActionSpend() ;
- break;
- case '0740':// Mistplay 花费
- Adjust::mistplaySpend() ;
- case '0745':// adjoe 花费
- Adjust::adjoeSpend() ;
- break;
- case '0035':// digitalturbine 花费
- Adjust::digitalturbineSpend() ;
- break;
- }
- Class Adjust{
- static $headers = [
- 'Authorization:Bearer '.API_TOKEN
- ];
- static $channelDateEvents = [
- 'adaction' => [ //adaction 7 天100 岛 20刀
- '2022-12-01' => [
- 'passisland100_d7_events_cohort' => 20
- ],
- ],
- 'prodege' => [ // Prodege多了一个链接 然后这个月开始事件改了哈 改成了20天内过120岛 给35刀,过200岛 给50刀,也就是说两个20天内 两个事件都过得话 就是85刀
- '2022-12-01' => [
- 'passisland120_d20_events_cohort' => 35,
- 'passisland200_d20_events_cohort' => 50,
- ],
- '2022-09-01' => [
- 'passisland120_d7_events_cohort' => 45,
- ],
- '2022-08-01' => [
- 'passisland20_d7_events_cohort' => 45 * 0.537542662,
- ],
- ],
- ];
- private static function getDateList($type, $maxNum = 120){
- $dayTime = strtotime(date('Y-m-d', TIMESTAMP));
- switch ($type){
- case 'day':
- $beginTime = $dayTime - 86400;
- break;
- case 'week':
- $maxNum = 52;
- $weekIdx = date('w', $dayTime);
- $weekIdx = empty($weekIdx) ? 7 : $weekIdx;
- $beginTime = $dayTime - ($weekIdx-1)*86400;
- break;
- case 'month':
- $maxNum = 13;
- $monthIdx = date('d', $dayTime);
- $beginTime = $dayTime - ($monthIdx-1)*86400;
- break;
- default:
- return[];
- }
- $dateList = [];
- for($i = 0; $i<= $maxNum; $i++){
- $stime = strtotime("-{$i} {$type}", $beginTime);
- $etime = strtotime("+1 {$type}", $stime) - 86400;
- $dateList[] = [date('Y-m-d', $stime), date('Y-m-d', $etime)];
- }
- return $dateList;
- }
- private static function getOldData($table, $date){
- $sql = "select * from {$table} where date = '{$date}'";
- $oldDatas = oo::commonOprDb(DB_CONN)->getAll($sql,MYSQLI_ASSOC);
- return $oldDatas;
- }
- # 周报,月报
- static function adjustReport($type = 'month'){
- $typePrefix = substr($type, 0, 1);
- $dateList = self::getDateList($type);
- $periodItemsPrefix = ['retained_users','paying_users','all_revenue_total','paying_user_size'];
- $periodDays = self::getPeriodIdxs($type);
- $periodItems = self::getPeriodItems($type);
- $adjustReportTable = otable::adjustReport($type);
- $dayCnt = 0;
- oo::commonOprDb(DB_CONN)->query("set names utf8mb4");
- foreach ($dateList as $dateArray){
- $insertAdjustData = [];
- $updateAdjustData = [];
- $dayCnt++;
- list($start, $end) = $dateArray;
- oo::logs()->debug3( "index:$dayCnt:{$start} ~ {$end}:" , 'adjustDataCrontab');
- $dateKey = $type == 'day' ? $start : date('Ymd', strtotime($start)) . '~'.date('Ymd', strtotime($end)) ;
- $params['date_period'] = "{$start}:{$end}";
- $params['dimensions'] = "country_code,network,tracker_token,os_name";
- $params['metrics'] = implode(',',$periodItems);
- $params['attribution_type'] = "click";
- // $params['reattributed'] = "false";
- $params['app_token__in'] = APP_TOKEN;
- $url = urldecode(API_URL . http_build_query($params));
- oo::logs()->debug3( $url , 'adjustDataCrontab');
- $response = funs::curlPost($url, self::$headers, [], 'GET');
- $response = @json_decode($response, true)['rows'] ?? [];
- oo::logs()->debug3( '记录数:' . count($response) , 'adjustDataCrontab');
- # 查询老数据
- $oldDatas = self::getOldData($adjustReportTable, $dateKey);
- $oldAdjustDataIdMaps = [];
- foreach ($oldDatas as $data){
- $key = "{$data['date']}{$data['countries']}{$data['os_names']}{$data['trackers_channel_token']}";
- $oldAdjustDataIdMaps[$key] = $data['id'];
- }
- $newkips = [0,0,0];
- foreach ($response as $data){
- $key = "{$dateKey}{$data['country_code']}{$data['os_name']}{$data['tracker_token']}";
- $adjustData = [
- 'date' => $dateKey,
- 'countries' => $data['country_code'],
- 'os_names' => $data['os_name'],
- 'trackers_channel_token' => $data['tracker_token'],
- 'trackers_channel_name' => $data['network'],
- 'trackers_group_type' => $data['network'],
- 'kpi_values' => json_encode([$data['daus'], $data['installs'], $data['all_revenue']],32),
- ];
- $periodData = [];
- foreach ($periodDays as $day){
- $kips = [];
- foreach ($periodItemsPrefix as $prefix){
- $kips[] = $data["{$prefix}_{$typePrefix}{$day}"];
- }
- $periodData[$day] = $kips;
- }
- $adjustData['period_values'] = json_encode($periodData);
- if(empty($oldAdjustDataIdMaps[$key])) # 新纪录
- $insertAdjustData[] = $adjustData;
- else
- $updateAdjustData[$oldAdjustDataIdMaps[$key]] = $adjustData;
- }
- oo::logs()->debug3( 'insertAdjustData:'.count($insertAdjustData), 'adjustDataCrontab');
- oo::logs()->debug3( 'updateAdjustData:'.count($updateAdjustData), 'adjustDataCrontab');
- if(count($insertAdjustData)){
- $insertAdjustDataList = array_chunk($insertAdjustData, 100);
- foreach ($insertAdjustDataList as $insertAdjustData){
- oo::commonOprDb(DB_CONN)->query(funs::db_intsertArrSQL($adjustReportTable, array_keys($insertAdjustData[0]), $insertAdjustData));
- }
- }
- foreach ($updateAdjustData as $id => $updateItem){
- oo::commonOprDb(DB_CONN)->query(funs::db_updateSQL($adjustReportTable, $updateItem, ' id='.$id));
- }
- oo::logs()->debug3( '--------------------------------------------------------------' , 'adjustDataCrontab');
- }
- oo::logs()->debug3( $dayCnt , 'adjustDataCrontab');
- }
- static function adjustReportViews($days = 120){
- ini_set('memory_limit', -1);
- $type = 'day';
- $typePrefix = substr($type, 0, 1);
- $dateList = self::getDateList($type, $days);
- $periodItemsPrefix = ['retained_users','paying_users','all_revenue_total','revenue_total'];
- $periodDays = [
- 0,
- 1,
- 2,
- 6,
- 14,
- 29,
- 59,
- 89,
- 119,
- ];
- $periodItems = self::getPeriodItems($type, $periodDays);
- $adjustViewsTable = otable::adjustPeriodViews();
- $dayCnt = 0;
- oo::commonOprDb(DB_CONN)->query("set names utf8mb4");
- $viewTracks = [];
- foreach (ViewChannels as $channel){
- $viewTracks = array_merge($viewTracks, (array)ChannelTrackMaps[$channel]);
- }
- foreach ($dateList as $dateArray){
- $insertAdjustData = [];
- $updateAdjustData = [];
- $dayCnt++;
- list($start, $end) = $dateArray;
- oo::logs()->debug3( "index:$dayCnt:{$start} ~ {$end}:" , 'adjustDataCrontab');
- // $dateKey = $type == 'day' ? $start : date('Ymd', strtotime($start)) . '~'.date('Ymd', strtotime($end)) ;
- $dateKey = $start;
- $params['date_period'] = "{$start}:{$end}";
- $params['dimensions'] = "channel,os_name,country_code,partner_name,adgroup_id_network,adgroup_network,creative_id_network,creative_network,campaign_id_network,campaign_network,network_token,campaign_token,adgroup_token,creative_token";
- $params['metrics'] = implode(',',$periodItems);
- $params['attribution_type'] = "click";
- $params['app_token__in'] = APP_TOKEN;
- $params['ad_revenue_sources'] = "Applovin+MAX+FB+only%2CAppLovin+Max";//&ad_revenue_sources=Applovin+MAX+FB+only%2CAppLovin+Max
- // $params['reattributed'] = "false";
- $url = urldecode(API_URL . http_build_query($params));
- oo::logs()->debug3( $url , 'adjustDataCrontab');
- $response = funs::curlPost($url, self::$headers, [], 'GET');
- $response = json_decode($response, true)['rows'] ?? [];
- oo::logs()->debug3( '记录数:' . count($response) , 'adjustDataCrontab');
- # 查询老数据
- $oldViewsDatas = self::getOldData($adjustViewsTable, $dateKey);
- $oldViewsDataIdMaps = [];
- foreach ($oldViewsDatas as $data){
- $key = "{$data['date']}{$data['countries']}{$data['os_names']}{$data['trackers_channel_token']}{$data['campaigns_token']}{$data['adgroups_token']}{$data['creatives_token']}";
- $oldViewsDataIdMaps[$key] = $data['id'];
- }
- foreach ($response as $data){
- if (!in_array($data['network_token'], $viewTracks))continue;
- if(empty($data['campaign_token']) || empty($data['adgroup_token']) || empty($data['creative_token']) )continue;
- $key = "{$dateKey}{$data['country_code']}{$data['os_name']}{$data['network_token']}{$data['campaign_token']}{$data['adgroup_token']}{$data['creative_token']}";
- $adjustData = [
- 'date' => $dateKey,
- 'trackers_channel_token' => $data['network_token'],
- 'trackers_channel_name' => $data['partner_name'],
- 'trackers_group_type' => $data['channel'],
- 'campaigns_token' => $data['campaign_token'],
- 'campaigns_name' => $data['campaign_network'],
- 'campaigns_id' => $data['campaign_id_network'],
- 'adgroups_token' => $data['adgroup_token'],
- 'adgroups_name' => $data['adgroup_network'],
- 'adgroups_id' => $data['adgroup_id_network'],
- 'creatives_token' => $data['creative_token'],
- 'creatives_name' => $data['creative_network'],
- 'creatives_id' => $data['creative_id_network'],
- 'countries' => $data['country_code'] ?? 'none',
- 'os_names' => $data['os_name'],
- 'kpi_values' => json_encode([$data['daus'], $data['installs'], $data['all_revenue'], $data['revenue']],32),
- ];
- foreach ($periodDays as $day){
- $kips = [];
- foreach ($periodItemsPrefix as $prefix){
- $kips[] = $data["{$prefix}_{$typePrefix}{$day}"];
- }
- $adjustData["period_{$day}_kpis"] = json_encode($kips);
- }
- if(empty($oldViewsDataIdMaps[$key])) # 新纪录
- $insertAdjustData[] = $adjustData;
- else
- $updateAdjustData[$oldViewsDataIdMaps[$key]] = $adjustData;
- }
- oo::logs()->debug3( 'insertAdjustData:'.count($insertAdjustData), 'adjustDataCrontab');
- oo::logs()->debug3( 'updateAdjustData:'.count($updateAdjustData), 'adjustDataCrontab');
- if(count($insertAdjustData)){
- $insertAdjustDataList = array_chunk($insertAdjustData, 500);
- foreach ($insertAdjustDataList as $insertAdjustData){
- oo::commonOprDb(DB_CONN)->query(funs::db_intsertArrSQL($adjustViewsTable, array_keys($insertAdjustData[0]), $insertAdjustData));
- }
- }
- foreach ($updateAdjustData as $id => $updateItem){
- oo::commonOprDb(DB_CONN)->query(funs::db_updateSQL($adjustViewsTable, $updateItem, ' id='.$id));
- }
- oo::logs()->debug3( '--------------------------------------------------------------' , 'adjustDataCrontab');
- }
- oo::logs()->debug3( $dayCnt , 'adjustDataCrontab');
- }
- static function adjustReportCampaign($days = 120){
- $type = 'day';
- $typePrefix = substr($type, 0, 1);
- $dateList = self::getDateList($type, $days);
- $periodItemsPrefix = ['retained_users','paying_users','all_revenue_total','revenue_total'];
- $periodDays = [
- 0,
- 1,
- 2,
- 6,
- 14,
- 29,
- 59,
- 89,
- 119,
- ];
- $periodItems = self::getPeriodItems($type, $periodDays);
- $adjustDataTable = otable::adjustPeriodCampaigns();
- $dayCnt = 0;
- oo::commonOprDb(DB_CONN)->query("set names utf8mb4");
- $viewTracks = [];
- foreach (ViewChannels as $channel){
- $viewTracks = array_merge($viewTracks, (array)ChannelTrackMaps[$channel]);
- }
- foreach ($dateList as $dateArray){
- $insertAdjustData = [];
- $updateAdjustData = [];
- $dayCnt++;
- list($start, $end) = $dateArray;
- oo::logs()->debug3( "index:$dayCnt:{$start} ~ {$end}:" , 'adjustDataCrontab');
- // $dateKey = $type == 'day' ? $start : date('Ymd', strtotime($start)) . '~'.date('Ymd', strtotime($end)) ;
- $dateKey = $start;
- $params['date_period'] = "{$start}:{$end}";
- $params['dimensions'] = "channel,os_name,country_code,partner_name,campaign_id_network,campaign_network,network_token,campaign_token";
- $params['metrics'] = implode(',',$periodItems);
- $params['attribution_type'] = "click";
- $params['app_token__in'] = APP_TOKEN;
- $params['ad_revenue_sources'] = "Applovin+MAX+FB+only%2CAppLovin+Max";//&ad_revenue_sources=Applovin+MAX+FB+only%2CAppLovin+Max
- // $params['reattributed'] = "false";
- $url = urldecode(API_URL . http_build_query($params));
- oo::logs()->debug3( $url , 'adjustDataCrontab');
- $response = funs::curlPost($url, self::$headers, [], 'GET');
- $response = @json_decode($response, true)['rows'] ?? [];
- oo::logs()->debug3( '记录数:' . count($response) , 'adjustDataCrontab');
- # 查询老数据
- $oldAdjustData = self::getOldData($adjustDataTable, $dateKey);
- $oldAdjustDataIdMaps = [];
- foreach ($oldAdjustData as $data){
- $key = "{$data['date']}{$data['countries']}{$data['os_names']}{$data['trackers_channel_token']}{$data['campaigns_token']}";
- $oldAdjustDataIdMaps[$key] = $data['id'];
- }
- foreach ($response as $data){
- if (in_array($data['network_token'], $viewTracks))continue;
- $key = "{$dateKey}{$data['country_code']}{$data['os_name']}{$data['network_token']}{$data['campaign_token']}";
- $adjustData = [
- 'date' => $dateKey,
- 'countries' => $data['country_code'] ?? 'none',
- 'os_names' => $data['os_name'],
- 'trackers_channel_token' => $data['network_token'],
- 'trackers_channel_name' => $data['partner_name'],
- 'trackers_group_type' => $data['channel'],
- 'campaigns_token' => $data['campaign_token'],
- 'campaigns_name' => $data['campaign_network'],
- 'campaigns_id' => $data['campaign_id_network'],
- 'kpi_values' => json_encode([$data['daus'], $data['installs'], $data['all_revenue'], $data['revenue']],32),// dau install 全部收入 内购收入
- ];
- foreach ($periodDays as $day){
- $kips = [];
- foreach ($periodItemsPrefix as $prefix){
- $kips[] = $data["{$prefix}_{$typePrefix}{$day}"];
- }
- $adjustData["period_{$day}_kpis"] = json_encode($kips);
- }
- if(empty($oldAdjustDataIdMaps[$key])) # 新纪录
- $insertAdjustData[] = $adjustData;
- else
- $updateAdjustData[$oldAdjustDataIdMaps[$key]] = $adjustData;
- }
- oo::logs()->debug3( 'insertAdjustData:'.count($insertAdjustData), 'adjustDataCrontab');
- oo::logs()->debug3( 'updateAdjustData:'.count($updateAdjustData), 'adjustDataCrontab');
- if(count($insertAdjustData)){
- $insertAdjustDataList = array_chunk($insertAdjustData, 500);
- foreach ($insertAdjustDataList as $insertAdjustData){
- oo::commonOprDb(DB_CONN)->query(funs::db_intsertArrSQL($adjustDataTable, array_keys($insertAdjustData[0]), $insertAdjustData));
- }
- }
- foreach ($updateAdjustData as $id => $updateItem){
- oo::commonOprDb(DB_CONN)->query(funs::db_updateSQL($adjustDataTable, $updateItem, ' id='.$id));
- }
- oo::logs()->debug3( '--------------------------------------------------------------' , 'adjustDataCrontab');
- }
- oo::logs()->debug3( $dayCnt , 'adjustDataCrontab');
- }
- private static function getPeriodIdxs($type){
- $periodDays = [];
- $max = 0;
- switch ($type){
- case 'day':
- $max = 120;
- break;
- case 'week':
- $max = 52;
- break;
- case 'month':
- $max = 13;
- break;
- default:
- return [];
- }
- for ($i=0;$i<=$max;$i++){
- if($type == 'week'){
- if($i > 1 && $i%4)continue;
- }
- $periodDays[] = $i;
- }
- return $periodDays;
- }
- private static function getPeriodItems($type, $periodDays = null){
- $periodItemsPrefix = ['retained_users','paying_users','all_revenue_total','revenue_total','paying_user_size'];
- $typePrefix = substr($type, 0, 1);
- $periodDays = $periodDays ?? self::getPeriodIdxs($type);
- $periodItems = ['daus','installs','cohort_all_revenue','all_revenue','revenue'];
- foreach ($periodItemsPrefix as $item){
- foreach ($periodDays as $day){
- $periodItems[] = "{$item}_{$typePrefix}{$day}";
- }
- }
- return $periodItems;
- }
- static function prodegeSpend(){
- $channel = 'prodege';
- $end = date('Y-m-d', TIMESTAMP - 20 * 86400);
- $start = $end;
- $table = otable::prodegeSpends();
- $events = array_keys(self::getEventsByChannelDate($channel, $start));
- $event = implode(',', $events);
- $query = "full_data=true&attribution_source=first&cost_mode=mixed&reattributed=all&attribution_type=all&readable_names=false&format_dates=false&metrics=installs,{$event}&dimensions=day&sort=day&utc_offset=%2B00:00&cohort_maturity=mature&sandbox=false&chart_1_dimensions=day&chart_1_metrics=installs&chart_2_dimensions=day&chart_2_metrics=installs&date_period={$start}:{$end}&channel_id__in=partner_1582&app_token__in=".APP_TOKEN;
- $url = API_URL . $query;
- oo::logs()->debug3( $url , 'adjustDataCrontab');
- $response = funs::curlPost($url, self::$headers, [], 'GET');
- $rows = @json_decode($response, true)['rows'] ?? [];
- oo::logs()->debug3( '记录数:' . count($rows) , 'adjustDataCrontab');
- $insertData = [];
- $deleteDates = [];
- foreach ($rows as $row){
- $deleteDates[] = $row['day'];
- $insertData[] = [
- 'date' => $row['day'],
- 'installs' => $row['installs'],
- 'spends' => self::getSpends($channel, $row),
- ];
- }
- $deleteDatesStr = implode("','", $deleteDates);
- oo::commonOprDb(DB_CONN)->query("delete from {$table} where date in ('{$deleteDatesStr}')");
- if (count($insertData)) oo::commonOprDb(DB_CONN)->query(funs::db_intsertArrSQL($table, array_keys($insertData[0]), $insertData));
- }
-
- static function adActionSpend($time = TIMESTAMP){
- $channel = 'adaction';
- $end = date('Y-m-d', $time - 7 * 86400);
- $start = $end;//date('Y-m-d', $time - 14 * 86400);
- $table = otable::adActionSpends();
- $events = array_keys(self::getEventsByChannelDate($channel, $start));
- $event = implode(',', $events);
- $query = "full_data=true&attribution_source=first&cost_mode=mixed&reattributed=all&attribution_type=all&readable_names=false&format_dates=false&metrics=installs,{$event}&dimensions=day&sort=day&utc_offset=%2B00:00&cohort_maturity=mature&sandbox=false&chart_1_dimensions=day&chart_1_metrics=installs&chart_2_dimensions=day&chart_2_metrics=installs&date_period={$start}:{$end}&channel_id__in=partner_234&app_token__in=".APP_TOKEN;
- $url = API_URL . $query;
- oo::logs()->debug3( $url , 'adjustDataCrontab');
- $response = funs::curlPost($url, self::$headers, [], 'GET');
- $rows = @json_decode($response, true)['rows'] ?? [];
- oo::logs()->debug3( '记录数:' . count($rows) , 'adjustDataCrontab');
- $insertData = [];
- $deleteDates = [];
- foreach ($rows as $row){
- $deleteDates[] = $row['day'];
- $insertData[] = [
- 'date' => $row['day'],
- 'installs' => $row['installs'],
- 'spends' => self::getSpends($channel, $row) ,
- ];
- }
- $deleteDatesStr = implode("','", $deleteDates);
- oo::commonOprDb(DB_CONN)->query("delete from {$table} where date in ('{$deleteDatesStr}')");
- if (count($insertData)) oo::commonOprDb(DB_CONN)->query(funs::db_intsertArrSQL($table, array_keys($insertData[0]), $insertData));
- }
- private static function getSpends($channel, array $row){
- $events = self::getEventsByChannelDate($channel, $row['day']);
- $spends = 0;
- foreach ($events as $event => $cpi){
- $spends += $row[$event] * floatval($cpi);
- }
- return $spends;
- }
- private static function getEventsByChannelDate($channel, $day){
- $channel = strtolower($channel);
- $channelEvents = self::$channelDateEvents[$channel] ?? [];
- if(empty($channelEvents))return [];
- foreach ($channelEvents as $date => $events){
- if($date <= $day){
- return $events;
- }
- }
- return $events;
- }
- static function mistplaySpend(){
- $table = otable::mistplaySpends();
- $end = date('Y-m-d', TIMESTAMP - 86400);
- $start = $end;
- $params = [
- 'full_data' => 'true',
- 'attribution_source' => 'first',
- 'cost_mode' => 'mixed',
- 'reattributed' => 'all',
- 'attribution_type' => 'all',
- 'readable_names' => 'false',
- 'format_dates' => 'false',
- 'metrics' => 'cost,installs',
- 'dimensions' => 'day',
- 'sort' => 'day',
- 'utc_offset' => urldecode('%2B00:00'),
- 'cohort_maturity' => 'mature',
- 'sandbox' => 'false',
- 'chart_1_dimensions' => 'day',
- 'chart_1_metrics' => 'installs',
- 'date_period' => "{$start}:{$end}",
- 'channel_id__in' => 'partner_1111',
- 'app_token__in' => APP_TOKEN,
- ];
- $query = http_build_query($params);
- $url = API_URL . $query;
- oo::logs()->debug3( 'request url:'.$url , 'adjustDataCrontab');
- $response = funs::curlPost($url, self::$headers, [], 'GET');
- $rows = @json_decode($response, true)['rows'] ?? [];
- oo::logs()->debug3( '记录数:' . count($rows) , 'adjustDataCrontab');
- $insertData = [];
- $deleteDates = [];
- foreach ($rows as $row){
- $deleteDates[] = $row['day'];
- $insertData[] = [
- 'date' => $row['day'],
- 'installs' => $row['installs'],
- 'spends' => $row['cost'],
- ];
- }
- $deleteDatesStr = implode("','", $deleteDates);
- oo::commonOprDb(DB_CONN)->query("delete from {$table} where date in ('{$deleteDatesStr}')");
- if (count($insertData)) oo::commonOprDb(DB_CONN)->query(funs::db_intsertArrSQL($table, array_keys($insertData[0]), $insertData));
- }
- static function adjoeSpend(){
- $table = otable::adjoeSpends();
- $end = date('Y-m-d', TIMESTAMP - 86400);
- $start = $end;
- $params = [
- 'full_data' => 'true',
- 'attribution_source' => 'first',
- 'cost_mode' => 'mixed',
- 'reattributed' => 'all',
- 'attribution_type' => 'all',
- 'readable_names' => 'false',
- 'format_dates' => 'false',
- 'metrics' => 'cost,installs',
- 'dimensions' => 'day',
- 'sort' => 'day',
- 'utc_offset' => urldecode('%2B00:00'),
- 'cohort_maturity' => 'mature',
- 'sandbox' => 'false',
- 'chart_1_dimensions' => 'day',
- 'chart_1_metrics' => 'installs',
- 'date_period' => "{$start}:{$end}",
- 'channel_id__in' => 'partner_1523',
- 'app_token__in' => APP_TOKEN,
- ];
- $query = http_build_query($params);
- $url = API_URL . $query;
- oo::logs()->debug3( 'request url:'.$url , 'adjustDataCrontab');
- $response = funs::curlPost($url, self::$headers, [], 'GET');
- $rows = @json_decode($response, true)['rows'] ?? [];
- oo::logs()->debug3( '记录数:' . count($rows) , 'adjustDataCrontab');
- $insertData = [];
- $deleteDates = [];
- foreach ($rows as $row){
- $deleteDates[] = $row['day'];
- $insertData[] = [
- 'date' => $row['day'],
- 'installs' => $row['installs'],
- 'spends' => $row['cost'],
- ];
- }
- $deleteDatesStr = implode("','", $deleteDates);
- oo::commonOprDb(DB_CONN)->query("delete from {$table} where date in ('{$deleteDatesStr}')");
- if (count($insertData)) oo::commonOprDb(DB_CONN)->query(funs::db_intsertArrSQL($table, array_keys($insertData[0]), $insertData));
- }
- static function digitalturbineSpend(){
- $tb = otable::digitalturbineSpends();
- $row = oo::commonOprDb(DB_CONN)->getOne("select * from {$tb} where id>0 limit 1");
- $end = date('Y-m-d', TIMESTAMP - 86400);
- if (empty($row)){
- $start = "2023-08-23";
- }else{
- $start = $end;
- }
- // 获取token
- $token = self::getDigitalturbineAccessToken();
- // 获取报告url
- $csvStr = self::getDigitalturbineReportData($token, $start, $end);
- if(empty($csvStr)){
- oo::logs()->debug3( "获取 digitalturbine 报告为空" , 'adjustDataCrontab');
- return;
- }
- $keysMapping = [
- "date" =>"date",
- "Country" =>"country",
- "Advertiser ARPU" =>"arpu",
- "Advertiser Spend" =>"spends",
- "Offer CTR" =>"ctr",
- "Offer Clicks" =>"clicks",
- "Offer Impressions" =>"impressions",
- "Offer Installs" =>'installs',
- "Campaign ID" =>'campaign_id',
- "Campaign Name" =>'campaign_name',
- "Device OS" =>'os',
- ];
- $csvStr = strtr($csvStr, ["\r\n" => "\n"]);
- $lines = explode("\n", $csvStr);
- $reportData = [];
- $keys = [];
- foreach ($lines as $line){
- $values = str_getcsv( $line);
- if(empty($keys)){
- foreach ($values as $k){
- $keys[] = $keysMapping[$k];
- }
- }else{
- if (count($keys) != count($values))continue;
- $reportData[] = array_combine($keys, $values);
- }
- }
- $reportData = array_filter($reportData);
- if (count($reportData))oo::commonOprDb(DB_CONN)->query(funs::db_intsertArrSQL($tb, $keys, $reportData));
- }
- private static function getDigitalturbineAccessToken(){
- $tokenUrl = 'https://reporting.fyber.com/auth/v1/token';
- $clientId = '0524e6d1d62825487a36ad567e88ff88';
- $clientSecret = '2D_oIp9qNVFyaGFuKxhY5l6wmT1aOBJcd91MXU5BXh26GdkpUYw5VOpkToBjWCQRau6c9ZKNPzk_C6dWDnAn7L0pbIdVg_4vLhFws2IxfS94JNdWgpDfStso1wqq9x_BpDRiT7UAw3SxeFWduH6OVsumkefQUGM-336EuJ242554QuJ0dJa0uo0SFTp7fif6fKL5FvXu2jDyP44pM4AcaMJBnUxfUUlDEdsHCxOWUb3EC8loqczgp_L6wK0viH3KwkqIzxsZ2sYAYgXmILv-B18hwCqL3gqnOhcqeYN0vwGuOCyoM5LF99TGw1yqRGyfRjNp5dD1k0Uptidtv6p5gg';
- $requestBody = [
- "grant_type" => "client_credentials",
- "client_id" => $clientId,
- "client_secret" => $clientSecret,
- ];
- $header = [
- 'Content-Type:application/json'
- ];
- $response = funs::curlPost($tokenUrl, $header, json_encode($requestBody), 'POST');
- oo::logs()->debug3( '获取 digitalturbine token 请求:'.json_encode($requestBody).' 响应:'.$response , 'adjustDataCrontab');
- if(is_string($response))$response = json_decode($response, true);
- if (empty($response['accessToken'])){
- return '';
- }
- return $response['accessToken'];
- }
- private static function getDigitalturbineReportData($accessToken, $start, $end){
- if(empty($accessToken)){
- return [];
- }
- $requestBody = [
- "source" =>"event",
- "dateRange" =>[
- "start" =>$start,
- "end" =>$end
- ],
- "metrics" =>[
- "Advertiser Spend",
- "Advertiser ARPU",
- "Offer Impressions",
- "Offer Clicks",
- "Offer CTR",
- "Offer Installs",
- ],
- "splits" =>[
- "Country",
- "Date",
- "Campaign ID",
- "Campaign Name",
- "Device OS",
- ],
- "filters" =>[
- // [
- // "dimension" =>"Country",
- // "values" =>[
- // "GB",
- // "US",
- // "DE"
- // ]
- // ]
- ]
- ];
- $header = [
- 'Content-Type:application/json',
- "Authorization: Bearer {$accessToken}"
- ];
- $url = 'https://reporting.fyber.com/api/v1/report/offerwall?format=csv';
- $response = funs::curlPost($url, $header, json_encode($requestBody), 'POST');
- oo::logs()->debug3( '获取 digitalturbine 报告url 请求:'.json_encode($requestBody).' 响应:'.$response , 'adjustDataCrontab');
- if(is_string($response))$response = json_decode($response, true);
- if (empty($response['url'])){
- return[];
- }
- sleep(6 * 60); // 等待对方文件生成
- return file_get_contents($response['url']);
- }
- }
- oo::logs()->debug3( "end:".time() , 'adjustDataCrontab');
|