1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609 |
- package statistics
- import (
- "blog/core/function"
- "blog/models/dblogic"
- "blog/models/tables"
- "blog/models/tool"
- "encoding/json"
- "fmt"
- "strings"
- "time"
- )
- // const user_token = "nDPDkox9PxGoisxoMm9Q"
- const user_token = "Tha-k75HDFTdG9WE_s58"
- const apiUrlNew = "https://dash.adjust.com/control-center/reports-service/report?"
- func TrackerGroup() map[string][]string {
- //token所属渠道
- var trackerGroup map[string][]string /*创建集合 */
- trackerGroup = make(map[string][]string)
- trackerGroup["Organic"] = []string{"g1b8nag"}
- trackerGroup["游戏内部跟踪链接"] = []string{"yhm47x1", "sij469x", "ynlt7ad", "l67x2mt", "6jq5m5g", "a7krf04", "v4x1byi", "327ogs3", "vt67uin", "cl7yqth", "fqupiw4", "b8q4iua", "x2ao8dk", "tri542t", "c9h1f36", "nua60j4", "o5rfavc"}
- trackerGroup["社媒KOL"] = []string{"ys85rvc", "n3l2809", "d2p6i5s", "oscpr9r"}
- trackerGroup["各平台主页渠道"] = []string{"a9nv7dj", "53ece3e", "1z942q1", "d7wopl7", "j7u6h9x", "to6eamo"}
- trackerGroup["邮件营销"] = []string{"cj6x62s", "pgdmu1z", "4x6dr4w"}
- trackerGroup["游戏外邀请"] = []string{"1jn49bp", "y8phovy"}
- trackerGroup["Facebook"] = []string{"2xgabxp", "k1qjyjr", "cwvjh9o", "oss8d1v"}
- trackerGroup["Google_ads"] = []string{"ussdj6t", "n6mofty"}
- trackerGroup["苹果商店页搜索广告"] = []string{"rs64xuy"}
- trackerGroup["Unity"] = []string{"u69nimr"}
- trackerGroup["Applovin"] = []string{"kkph0xk"}
- trackerGroup["IronSource"] = []string{"vjjyyf2"}
- trackerGroup["Chartboost"] = []string{"14dbbj0"}
- trackerGroup["Mintegral"] = []string{"r2phf78"}
- trackerGroup["Vungle"] = []string{"6j6t1sg"}
- trackerGroup["Inplayable"] = []string{"n4tb7gp"}
- trackerGroup["Linkme"] = []string{"yw8hp34"}
- trackerGroup["Tecdo"] = []string{"vbc3665"}
- trackerGroup["Virgrilmedia"] = []string{"6366s1a"}
- trackerGroup["TikTok"] = []string{"6xxj7ry"}
- trackerGroup["Prodege"] = []string{"ji2y5ap"}
- return trackerGroup
- }
- // 可交付数据 细分到广告
- func View(date string, grouping string, kpis string) string {
- if date == "" {
- date = "2021-04-01"
- }
- if grouping == "" {
- grouping = "networks,campaigns,adgroups,creatives,countries,os_names"
- }
- if kpis == "" {
- kpis = "daus,installs,revenue" //daus每日活跃用户数 installs安装 revenue收入
- }
- url := "https://api.adjust.com/kpis/v1/rv3fkx1a8em8?start_date=" + date + "&end_date=" + date + "&grouping=" + grouping + "&kpis=" + kpis + "&user_token=" + user_token + "&attribution_type=click"
- fmt.Println(url)
- body := tool.Get(url)
- var dat map[string]interface{}
- if err := json.Unmarshal([]byte(body), &dat); err != nil {
- for err != nil {
- time.Sleep(3 * time.Second)
- body := tool.Get(url)
- err = json.Unmarshal([]byte(body), &dat)
- }
- }
- list := dat["result_set"].(map[string]interface{})["networks"]
- if list == nil {
- return url
- }
- tableName := "t_adjust_period_view"
- //获取日期为 date的全部数据
- adjustPeriodView := []*tables.TAdjustPeriodView{}
- dblogic.M("GAMES1").Table(tableName).Where("date = ?", date).Find(&adjustPeriodView)
- //遍历数据库中数据,组装成 key =》 id ( key 为 Date,Trackers_channel_token,Campaigns_token,Adgroups_token,Creatives_token,Countries,OsNames 组成)
- adjustPeriodViewMap := map[string]int{}
- for _, row := range adjustPeriodView {
- keyTemp := ""
- keyTemp += row.Date
- keyTemp += row.Trackers_channel_token
- keyTemp += row.Campaigns_token
- keyTemp += row.Adgroups_token
- keyTemp += row.Creatives_token
- keyTemp += row.Countries
- keyTemp += row.OsNames
- adjustPeriodViewMap[keyTemp] = row.Id
- }
- updateCnt := 0
- updateSql := ""
- periodViewTokens := PeriodViewTokens()
- for _, v := range list.([]interface{}) {
- trackers_channel_token := function.Strval(v.(map[string]interface{})["token"])
- trackers_channel_name := function.Strval(v.(map[string]interface{})["name"])
- if trackers_channel_token != "r2phf78" &&
- trackers_channel_token != "ussdj6t" &&
- trackers_channel_token != "6xxj7ry" &&
- trackers_channel_token != "rs64xuy" &&
- !function.IsContain(periodViewTokens, trackers_channel_token) {
- //fmt.Println(trackers_channel_token + " null 不是 FB & Google_ads & Mintegral")
- fmt.Println(trackers_channel_name + " 不是 FB & Google_ads & Mintegral & TikTok & asa")
- continue
- }
- campaigns := v.(map[string]interface{})["campaigns"]
- if campaigns == nil {
- //fmt.Println("campaigns null")
- continue
- }
- for _, campaignsRow := range campaigns.([]interface{}) {
- campaigns_token := function.Strval(campaignsRow.(map[string]interface{})["token"])
- //campaigns_name := function.Strval(campaignsRow.(map[string]interface{})["name"])
- //campaigns_id := function.RegularGetBrackets(campaigns_name) //正则匹配出扩展里面的数值
- //fmt.Println(campaigns_id)
- adgroups := campaignsRow.(map[string]interface{})["adgroups"]
- if adgroups == nil {
- //fmt.Println("adgroups null")
- continue
- }
- for _, adgroupsRow := range adgroups.([]interface{}) {
- adgroups_token := function.Strval(adgroupsRow.(map[string]interface{})["token"])
- //adgroups_name := function.Strval(adgroupsRow.(map[string]interface{})["name"])
- //adgroups_id := function.RegularGetBrackets(adgroups_name) //正则匹配出扩展里面的数值
- //fmt.Println(adgroups_id)
- creatives := adgroupsRow.(map[string]interface{})["creatives"]
- if creatives == nil {
- //fmt.Println("creatives null")
- continue
- }
- for _, creativesRow := range creatives.([]interface{}) {
- creatives_token := function.Strval(creativesRow.(map[string]interface{})["token"])
- countries := creativesRow.(map[string]interface{})["countries"]
- if countries == nil {
- //fmt.Println("countries null")
- continue
- }
- for _, countryRow := range countries.([]interface{}) {
- //第一层国家
- country := function.Strval(countryRow.(map[string]interface{})["country"])
- //fmt.Println("国家:" + country)
- os_names := countryRow.(map[string]interface{})["os_names"]
- if os_names == nil {
- //fmt.Println("os_names null")
- continue
- }
- for _, os_nameRow := range os_names.([]interface{}) {
- //第二层设备
- os_name := function.Strval(os_nameRow.(map[string]interface{})["os_name"])
- //fmt.Println("设备:" + os_name)
- kpi_values := function.Strval(os_nameRow.(map[string]interface{})["kpi_values"])
- keyTemp := date + trackers_channel_token + campaigns_token + adgroups_token + creatives_token + country + os_name
- updateCnt++
- updateSql = "update " + tableName + " set "
- updateSql += "`kpi_values` = '" + kpi_values + "'"
- updateSql += " where id=" + function.Strval(adjustPeriodViewMap[keyTemp])
- db := dblogic.M("GAMES1").Exec(updateSql)
- if db.Error != nil {
- fmt.Print(updateSql)
- fmt.Println(db.Error)
- continue
- }
- if updateCnt%300 == 0 {
- fmt.Print("update cnt:300 time:")
- fmt.Println(time.Now().Unix())
- }
- }
- }
- }
- }
- }
- }
- fmt.Print("updateCnt:")
- fmt.Println(updateCnt)
- return url
- }
- // 同期群 细分到广告
- func PeriodView(date string, grouping string, kpis string) string {
- if date == "" {
- date = "date-04-01"
- }
- if grouping == "" {
- grouping = "networks,campaigns,adgroups,creatives,countries,os_names"
- }
- if kpis == "" {
- kpis = "retained_users,paying_users,revenue_total" //retained_users规模 paying_users支付人数 revenue_total累计总收入
- }
- cohort_period_filter := "0,1,2,6,14,29,59,89,119"
- url := "https://api.adjust.com/kpis/v1/rv3fkx1a8em8/cohorts?start_date=" + date + "&end_date=" + date + "&grouping=" + grouping + "&kpis=" + kpis + "&user_token=" + user_token + "&attribution_type=click" + "&cohort_period_filter=" + cohort_period_filter + "&reattributed=all"
- body := tool.Get(url)
- var dat map[string]interface{}
- if err := json.Unmarshal([]byte(body), &dat); err != nil {
- for err != nil {
- time.Sleep(3 * time.Second)
- body := tool.Get(url)
- err = json.Unmarshal([]byte(body), &dat)
- }
- }
- trackerGroup := TrackerGroup()
- list := dat["result_set"].(map[string]interface{})["networks"]
- if list == nil {
- return url
- }
- tableName := "t_adjust_period_view"
- //获取日期为 date的全部数据
- adjustPeriodView := []*tables.TAdjustPeriodView{}
- dblogic.M("GAMES1").Table(tableName).Where("date = ?", date).Find(&adjustPeriodView)
- //遍历数据库中数据,组装成 key =》 id ( key 为 Date,Trackers_channel_token,Campaigns_token,Adgroups_token,Creatives_token,Countries,OsNames 组成)
- adjustPeriodViewMap := map[string]int{}
- for _, row := range adjustPeriodView {
- keyTemp := ""
- keyTemp += row.Date
- keyTemp += row.Trackers_channel_token
- keyTemp += row.Campaigns_token
- keyTemp += row.Adgroups_token
- keyTemp += row.Creatives_token
- keyTemp += row.Countries
- keyTemp += row.OsNames
- adjustPeriodViewMap[keyTemp] = row.Id
- }
- insertCnt, insertTempCnt := 0, 0
- insertSql := "insert into " + tableName + " (" +
- "`date`," +
- "`trackers_channel_token`," +
- "`trackers_channel_name`," +
- "`trackers_group_type`," +
- "`campaigns_token`," +
- "`campaigns_name`," +
- "`campaigns_id`," +
- "`adgroups_token`," +
- "`adgroups_name`," +
- "`adgroups_id`," +
- "`creatives_token`," +
- "`creatives_name`," +
- "`creatives_id`," +
- "`countries`," +
- "`os_names`," +
- "`period_0_kpis`," +
- "`period_1_kpis`," +
- "`period_2_kpis`," +
- "`period_6_kpis`," +
- "`period_14_kpis`," +
- "`period_29_kpis`," +
- "`period_59_kpis`," +
- "`period_89_kpis`," +
- "`period_119_kpis`) values "
- insertOrigin := insertSql
- updateCnt := 0
- updateSql := ""
- periodViewTokens := PeriodViewTokens()
- for _, v := range list.([]interface{}) {
- trackers_channel_token := function.Strval(v.(map[string]interface{})["token"])
- trackers_channel_name := function.Strval(v.(map[string]interface{})["name"])
- if trackers_channel_token != "r2phf78" &&
- trackers_channel_token != "ussdj6t" &&
- trackers_channel_token != "6xxj7ry" &&
- trackers_channel_token != "rs64xuy" &&
- !function.IsContain(periodViewTokens, trackers_channel_token) {
- fmt.Println(trackers_channel_token + " null 不是 FB & Google_ads & Mintegral & TikTok & asa")
- continue
- }
- campaigns := v.(map[string]interface{})["campaigns"]
- if campaigns == nil {
- continue
- }
- for _, campaignsRow := range campaigns.([]interface{}) {
- campaigns_token := function.Strval(campaignsRow.(map[string]interface{})["token"])
- campaigns_name := function.Strval(campaignsRow.(map[string]interface{})["name"])
- campaigns_id := function.RegularGetBrackets(campaigns_name) //正则匹配出扩展里面的数值
- adgroups := campaignsRow.(map[string]interface{})["adgroups"]
- if adgroups == nil {
- continue
- }
- for _, adgroupsRow := range adgroups.([]interface{}) {
- adgroups_token := function.Strval(adgroupsRow.(map[string]interface{})["token"])
- adgroups_name := function.Strval(adgroupsRow.(map[string]interface{})["name"])
- adgroups_id := function.RegularGetBrackets(adgroups_name) //正则匹配出扩展里面的数值
- creatives := adgroupsRow.(map[string]interface{})["creatives"]
- if creatives == nil {
- continue
- }
- for _, creativesRow := range creatives.([]interface{}) {
- creatives_token := function.Strval(creativesRow.(map[string]interface{})["token"])
- creatives_name := function.Strval(creativesRow.(map[string]interface{})["name"])
- creatives_id := function.RegularGetBrackets(creatives_name) //正则匹配出扩展里面的数值
- if creatives_id == "" {
- //fmt.Println("creatives_id null")
- //continue
- }
- countries := creativesRow.(map[string]interface{})["countries"]
- if countries == nil {
- continue
- }
- for _, countryRow := range countries.([]interface{}) {
- //第一层国家
- country := function.Strval(countryRow.(map[string]interface{})["country"])
- os_names := countryRow.(map[string]interface{})["os_names"]
- if os_names == nil {
- continue
- }
- for _, os_nameRow := range os_names.([]interface{}) {
- //第二层设备
- os_name := function.Strval(os_nameRow.(map[string]interface{})["os_name"])
- var kpisList map[string]string /*创建集合 */
- kpisList = make(map[string]string)
- periods := os_nameRow.(map[string]interface{})["periods"]
- for _, pv := range periods.([]interface{}) {
- period := function.Strval(pv.(map[string]interface{})["period"])
- kpi_values := function.Strval(pv.(map[string]interface{})["kpi_values"])
- kpisList[period] = kpi_values
- }
- trackers_group_type := ""
- for key, _ := range trackerGroup {
- if function.IsContain(trackerGroup[key], trackers_channel_token) {
- trackers_group_type = key
- break
- }
- }
- //判断数据库是否存在,不存在则保存
- keyTemp := ""
- keyTemp += date
- keyTemp += trackers_channel_token
- keyTemp += campaigns_token
- keyTemp += adgroups_token
- keyTemp += creatives_token
- keyTemp += country
- keyTemp += os_name
- if adjustPeriodViewMap[keyTemp] == 0 {
- insertCnt++
- insertTempCnt++
- insertSql += "('"
- insertSql += date + "','"
- insertSql += trackers_channel_token + "','"
- insertSql += trackers_channel_name + "','"
- insertSql += trackers_group_type + "','"
- insertSql += campaigns_token + "','"
- insertSql += strings.Replace(campaigns_name, "'", "\\'", -1) + "','"
- insertSql += campaigns_id + "','"
- insertSql += adgroups_token + "','"
- insertSql += strings.Replace(adgroups_name, "'", "\\'", -1) + "','"
- insertSql += adgroups_id + "','"
- insertSql += creatives_token + "','"
- insertSql += strings.Replace(creatives_name, "'", "\\'", -1) + "','"
- insertSql += creatives_id + "','"
- insertSql += country + "','"
- insertSql += os_name + "','"
- insertSql += kpisList["0"] + "','"
- insertSql += kpisList["1"] + "','"
- insertSql += kpisList["2"] + "','"
- insertSql += kpisList["6"] + "','"
- insertSql += kpisList["14"] + "','"
- insertSql += kpisList["29"] + "','"
- insertSql += kpisList["59"] + "','"
- insertSql += kpisList["89"] + "','"
- insertSql += kpisList["119"] + "'),"
- if insertTempCnt == 300 { //每300条 批量执行
- insertTempCnt = 0
- insertSql = strings.TrimSuffix(insertSql, ",")
- fmt.Print("insert begin:")
- fmt.Println(time.Now())
- dblogic.M("GAMES1").Exec(insertSql)
- fmt.Print("insert end:")
- fmt.Println(time.Now())
- insertSql = insertOrigin
- }
- } else {
- updateCnt++
- updateSql = "update " + tableName + " set "
- updateSql += "`trackers_channel_name` = '" + trackers_channel_name + "',"
- updateSql += "`trackers_group_type` = '" + trackers_group_type + "',"
- updateSql += "`campaigns_name` = '" + strings.Replace(campaigns_name, "'", "\\'", -1) + "',"
- updateSql += "`campaigns_id` = '" + campaigns_id + "',"
- updateSql += "`adgroups_name` = '" + strings.Replace(adgroups_name, "'", "\\'", -1) + "',"
- updateSql += "`adgroups_id` = '" + adgroups_id + "',"
- updateSql += "`creatives_name` = '" + strings.Replace(creatives_name, "'", "\\'", -1) + "',"
- updateSql += "`creatives_id` = '" + creatives_id + "',"
- updateSql += "`period_0_kpis` = '" + kpisList["0"] + "',"
- updateSql += "`period_1_kpis` = '" + kpisList["1"] + "',"
- updateSql += "`period_2_kpis` = '" + kpisList["2"] + "',"
- updateSql += "`period_6_kpis` = '" + kpisList["6"] + "',"
- updateSql += "`period_14_kpis` = '" + kpisList["14"] + "',"
- updateSql += "`period_29_kpis` = '" + kpisList["29"] + "',"
- updateSql += "`period_59_kpis` = '" + kpisList["59"] + "',"
- updateSql += "`period_89_kpis` = '" + kpisList["89"] + "',"
- updateSql += "`period_119_kpis` = '" + kpisList["119"] + "'"
- updateSql += " where id=" + function.Strval(adjustPeriodViewMap[keyTemp])
- db := dblogic.M("GAMES1").Exec(updateSql)
- if db.Error != nil {
- fmt.Print(updateSql)
- fmt.Println(db.Error)
- continue
- }
- if updateCnt%300 == 0 {
- fmt.Print("update cnt:300 time:")
- fmt.Println(time.Now().Unix())
- }
- }
- }
- }
- }
- }
- }
- }
- if insertTempCnt > 0 { //每300条 批量执行
- insertTempCnt = 0
- insertSql = strings.TrimSuffix(insertSql, ",")
- fmt.Print("insert begin:")
- fmt.Println(time.Now())
- dblogic.M("GAMES1").Exec(insertSql)
- fmt.Print("insert end:")
- fmt.Println(time.Now())
- insertSql = insertOrigin
- }
- fmt.Print("insertCnt:")
- fmt.Println(insertCnt)
- fmt.Print("updateCnt:")
- fmt.Println(updateCnt)
- return url
- }
- func PeriodViewCampaigns(date string, grouping string, kpis string) string {
- if date == "" {
- date = "2021-04-01"
- }
- if grouping == "" {
- grouping = "networks,campaigns,countries,os_names"
- }
- if kpis == "" {
- kpis = "retained_users,paying_users,revenue_total" //retained_users规模 paying_users支付人数 revenue_total累计总收入
- }
- cohort_period_filter := "0,1,2,6,14,29,59,89,119"
- url := "https://api.adjust.com/kpis/v1/rv3fkx1a8em8/cohorts?start_date=" + date + "&end_date=" + date + "&grouping=" + grouping + "&kpis=" + kpis + "&user_token=" + user_token + "&attribution_type=click" + "&cohort_period_filter=" + cohort_period_filter + "&reattributed=all"
- fmt.Println(url)
- body := tool.Get(url)
- var dat map[string]interface{}
- if err := json.Unmarshal([]byte(body), &dat); err != nil {
- for err != nil {
- time.Sleep(3 * time.Second)
- body := tool.Get(url)
- err = json.Unmarshal([]byte(body), &dat)
- }
- }
- tableName := "t_adjust_period_campaigns"
- //获取日期为 date的全部数据
- adjustPeriodViewCampaign := []*tables.TAdjustPeriodViewCampaigns{}
- dblogic.M("GAMES1").Table(tableName).Where("date = ?", date).Find(&adjustPeriodViewCampaign)
- //遍历数据库中数据,组装成 key =》 id ( key 为 Date,Trackers_channel_token,Campaigns_token,Countries,OsNames 组成)
- adjustPeriodViewCampaignMap := map[string]int{}
- for _, row := range adjustPeriodViewCampaign {
- keyTemp := ""
- keyTemp += row.Date
- keyTemp += row.Trackers_channel_token
- keyTemp += row.Campaigns_token
- keyTemp += row.Countries
- keyTemp += row.OsNames
- adjustPeriodViewCampaignMap[keyTemp] = row.Id
- }
- insertCnt := 0
- insertTempCnt := 0
- insertSql := "insert into " + tableName + " (" +
- "`date`," +
- "`countries`," +
- "`os_names`," +
- "`trackers_channel_token`," +
- "`trackers_channel_name`," +
- "`trackers_group_type`," +
- "`campaigns_token`," +
- "`campaigns_name`," +
- "`campaigns_id`," +
- "`period_0_kpis`," +
- "`period_1_kpis`," +
- "`period_2_kpis`," +
- "`period_6_kpis`," +
- "`period_14_kpis`," +
- "`period_29_kpis`," +
- "`period_59_kpis`," +
- "`period_89_kpis`," +
- "`period_119_kpis`) values "
- insertOrigin := insertSql
- updateCnt := 0
- updateSql := ""
- trackerGroup := TrackerGroup()
- list := dat["result_set"].(map[string]interface{})["networks"]
- if list == nil {
- return url
- }
- for _, v := range list.([]interface{}) {
- trackers_channel_token := function.Strval(v.(map[string]interface{})["token"])
- trackers_channel_name := function.Strval(v.(map[string]interface{})["name"])
- campaigns := v.(map[string]interface{})["campaigns"]
- if campaigns == nil {
- //fmt.Println("campaigns null")
- continue
- }
- for _, campaignsRow := range campaigns.([]interface{}) {
- campaigns_token := function.Strval(campaignsRow.(map[string]interface{})["token"])
- campaigns_name := function.Strval(campaignsRow.(map[string]interface{})["name"])
- campaigns_id := function.RegularGetBrackets(campaigns_name) //正则匹配出扩展里面的数值
- countries := campaignsRow.(map[string]interface{})["countries"]
- if countries == nil {
- //fmt.Println("countries null")
- continue
- }
- for _, countryRow := range countries.([]interface{}) {
- //第一层国家
- country := function.Strval(countryRow.(map[string]interface{})["country"])
- //fmt.Println("国家:" + country)
- os_names := countryRow.(map[string]interface{})["os_names"]
- if os_names == nil {
- //fmt.Println("os_names null")
- continue
- }
- for _, os_nameRow := range os_names.([]interface{}) {
- //第二层设备
- os_name := function.Strval(os_nameRow.(map[string]interface{})["os_name"])
- //fmt.Println("设备:" + os_name)
- var kpisList map[string]string /*创建集合 */
- kpisList = make(map[string]string)
- periods := os_nameRow.(map[string]interface{})["periods"]
- for _, pv := range periods.([]interface{}) {
- period := function.Strval(pv.(map[string]interface{})["period"])
- kpi_values := function.Strval(pv.(map[string]interface{})["kpi_values"])
- kpisList[period] = kpi_values
- }
- trackers_group_type := ""
- for key, _ := range trackerGroup {
- if function.IsContain(trackerGroup[key], trackers_channel_token) {
- trackers_group_type = key
- break
- }
- }
- if trackers_group_type == "Facebook" {
- //fmt.Println("Facebook continue")
- continue
- }
- keyTemp := date + trackers_channel_token + campaigns_token + country + os_name
- if adjustPeriodViewCampaignMap[keyTemp] == 0 {
- insertCnt++
- insertTempCnt++
- insertSql += "('"
- insertSql += date + "','"
- insertSql += country + "','"
- insertSql += os_name + "','"
- insertSql += trackers_channel_token + "','"
- insertSql += trackers_channel_name + "','"
- insertSql += trackers_group_type + "','"
- insertSql += campaigns_token + "','"
- insertSql += campaigns_name + "','"
- insertSql += campaigns_id + "','"
- insertSql += kpisList["0"] + "','"
- insertSql += kpisList["1"] + "','"
- insertSql += kpisList["2"] + "','"
- insertSql += kpisList["6"] + "','"
- insertSql += kpisList["14"] + "','"
- insertSql += kpisList["29"] + "','"
- insertSql += kpisList["59"] + "','"
- insertSql += kpisList["89"] + "','"
- insertSql += kpisList["119"] + "'),"
- if insertTempCnt == 300 { //每300条 批量执行
- insertTempCnt = 0
- insertSql = strings.TrimSuffix(insertSql, ",")
- fmt.Print("insert begin:")
- fmt.Println(time.Now())
- dblogic.M("GAMES1").Exec(insertSql)
- fmt.Print("insert end:")
- fmt.Println(time.Now())
- insertSql = insertOrigin
- }
- } else {
- updateCnt++
- updateSql = "update " + tableName + " set "
- updateSql += "`trackers_channel_name`='" + trackers_channel_name + "',"
- updateSql += "`trackers_group_type`='" + trackers_group_type + "',"
- updateSql += "`campaigns_name`='" + campaigns_name + "',"
- updateSql += "`campaigns_id`='" + campaigns_id + "',"
- updateSql += "`period_0_kpis`='" + kpisList["0"] + "',"
- updateSql += "`period_1_kpis`='" + kpisList["1"] + "',"
- updateSql += "`period_2_kpis`='" + kpisList["2"] + "',"
- updateSql += "`period_6_kpis`='" + kpisList["6"] + "',"
- updateSql += "`period_14_kpis`='" + kpisList["14"] + "',"
- updateSql += "`period_29_kpis`='" + kpisList["29"] + "',"
- updateSql += "`period_59_kpis`='" + kpisList["59"] + "',"
- updateSql += "`period_89_kpis`='" + kpisList["89"] + "',"
- updateSql += "`period_119_kpis`='" + kpisList["119"] + "' where id=" + function.Strval(adjustPeriodViewCampaignMap[keyTemp])
- db := dblogic.M("GAMES1").Exec(updateSql)
- if db.Error != nil {
- fmt.Print(updateSql)
- fmt.Println(db.Error)
- continue
- }
- if updateCnt%300 == 0 {
- fmt.Print("update cnt:300 time:\t")
- fmt.Println(time.Now().Unix())
- }
- }
- }
- }
- }
- }
- if insertTempCnt > 0 { //每300条 批量执行
- insertTempCnt = 0
- insertSql = strings.TrimSuffix(insertSql, ",")
- fmt.Print("insert begin:")
- fmt.Println(time.Now())
- dblogic.M("GAMES1").Exec(insertSql)
- fmt.Print("insert end:")
- fmt.Println(time.Now())
- insertSql = insertOrigin
- }
- fmt.Print("insertCnt:")
- fmt.Println(insertCnt)
- fmt.Print("updateCnt:")
- fmt.Println(updateCnt)
- return url
- }
- // 可交付数据 优化一次性请求多个设备 国家
- func Tracker2(stime string, etime string, user_token string, trackerGroup map[string][]string, dateType string) map[string]interface{} {
- kpis := "daus,installs,revenue" //daus、设备安装数、收入
- grouping := "trackers,countries,os_names"
- url := "https://api.adjust.com/kpis/v1/rv3fkx1a8em8?start_date=" + stime + "&end_date=" + etime + "&grouping=" + grouping + "&kpis=" + kpis + "&user_token=" + user_token + "&attribution_type=click"
- var saveDate string = ""
- if dateType == "day" {
- saveDate = stime
- } else if dateType == "week" {
- //周
- sdate, _ := function.Strtotime("2006-01-02", stime)
- edate, _ := function.Strtotime("2006-01-02", etime)
- saveDate = function.Date("20060102", sdate) + "~" + function.Date("20060102", edate)
- } else if dateType == "month" {
- //月
- sdate, _ := function.Strtotime("2006-01-02", stime)
- edate, _ := function.Strtotime("2006-01-02", etime)
- saveDate = function.Date("20060102", sdate) + "~" + function.Date("20060102", edate)
- }
- fmt.Print("request begin:")
- fmt.Println(time.Now())
- body := tool.Get(url)
- fmt.Print("request end:")
- fmt.Println(time.Now())
- //fmt.Println("body----------------------------------")
- //fmt.Println(body)
- var dat map[string]interface{}
- if err := json.Unmarshal([]byte(body), &dat); err != nil {
- for err != nil {
- time.Sleep(3 * time.Second)
- body := tool.Get(url)
- err = json.Unmarshal([]byte(body), &dat)
- }
- }
- var listMap map[string]string /*创建集合 */
- listMap = make(map[string]string)
- parameters, _ := dat["result_parameters"].(map[string]interface{})["trackers"]
- if parameters == nil {
- return make(map[string]interface{})
- }
- for _, v := range parameters.([]interface{}) {
- name := function.Strval(v.(map[string]interface{})["name"])
- token := function.Strval(v.(map[string]interface{})["token"])
- listMap[token] = name
- }
- //fmt.Println(listMap)
- //遍历方法二
- list := dat["result_set"].(map[string]interface{})["trackers"]
- if list == nil {
- return make(map[string]interface{})
- }
- //fmt.Println(list)
- //判断数据库是否存在,不存在则保存
- var tableName string = ""
- if dateType == "day" {
- tableName = "t_adjust"
- } else if dateType == "week" {
- //周
- tableName = "t_adjust_week"
- } else if dateType == "month" {
- //月
- tableName = "t_adjust_month"
- }
- insertCnt := 0
- insertTempCnt := 0
- insertSql := "insert into " + tableName + " (`date`,`countries`,`os_names`,`trackers_channel_token`,`trackers_channel_name`,`trackers_group_type`,`kpi_values`) values "
- updateCnt := 0
- updateSql := ""
- adjusts := []*tables.TAdjust{}
- dblogic.M("GAMES1").Table(tableName).Where("date = ?", saveDate).Find(&adjusts)
- adjustsMap := map[string]int{}
- for _, row := range adjusts {
- adjustsMap[row.Date+row.Countries+row.OsNames+row.TrackersChannelToken] = row.Id
- }
- for _, v := range list.([]interface{}) {
- token := function.Strval(v.(map[string]interface{})["token"])
- //fmt.Println(token)
- countries := v.(map[string]interface{})["countries"]
- if countries == nil {
- continue
- }
- for _, countryRow := range countries.([]interface{}) {
- //第一层国家
- country := function.Strval(countryRow.(map[string]interface{})["country"])
- //fmt.Println("国家:"+country)
- os_names := countryRow.(map[string]interface{})["os_names"]
- if os_names == nil {
- continue
- }
- for _, os_nameRow := range os_names.([]interface{}) {
- //第二层设备
- os_name := function.Strval(os_nameRow.(map[string]interface{})["os_name"])
- kpi_values := function.Strval(os_nameRow.(map[string]interface{})["kpi_values"])
- //if kpi_values == nil{
- // continue
- //}
- trackers_channel_name := listMap[token]
- trackers_group_type := "Organic_all"
- for key, _ := range trackerGroup { //通过 token 获取对应的 渠道 trackers_group_type
- //fmt.Println(value)
- if function.IsContain(trackerGroup[key], token) {
- trackers_group_type = key
- break
- }
- }
- //var count int
- var keyTemp string
- //通过 日期,国家,设备,token 拼接唯一 key
- keyTemp = saveDate + country + os_name + token
- if adjustsMap[keyTemp] == 0 { //key不存在,则添加
- //插入
- insertSql += "( '" + saveDate +
- "','" + country +
- "','" + os_name +
- "','" + token +
- "','" + trackers_channel_name +
- "','" + trackers_group_type +
- "','" + kpi_values +
- "'),"
- insertCnt++
- insertTempCnt++
- if insertTempCnt == 300 {
- insertTempCnt = 0
- insertSql = strings.TrimSuffix(insertSql, ",")
- fmt.Print("insert begin:")
- fmt.Println(time.Now())
- dblogic.M("GAMES1").Exec(insertSql)
- fmt.Print("insert end:")
- fmt.Println(time.Now())
- insertSql = "insert into " + tableName + " (`date`,`countries`,`os_names`,`trackers_channel_token`,`trackers_channel_name`,`trackers_group_type`,`kpi_values`) values "
- }
- } else { //存在,则更新
- updateSql = ""
- updateSql += "update " + tableName + " set "
- updateSql += "trackers_channel_name='" + trackers_channel_name + "',"
- updateSql += "trackers_group_type='" + trackers_group_type + "',"
- updateSql += "kpi_values='" + kpi_values + "',"
- updateSql = strings.TrimSuffix(updateSql, ",")
- //updateSql += " where `date`='"+saveDate+"'" + " and `countries`='"+country+"'" + " and `os_names`='"+os_name+"'" + " and `trackers_channel_token`='"+token+"';\n"
- updateSql += " where `id`='" + function.Strval(adjustsMap[keyTemp]) + "';"
- updateCnt++
- db := dblogic.M("GAMES1").Exec(updateSql)
- if db.Error != nil {
- fmt.Print(updateSql)
- fmt.Println(db.Error)
- continue
- }
- if updateCnt%300 == 0 {
- fmt.Print("update cnt:300 time:")
- fmt.Println(time.Now().Unix())
- }
- }
- }
- }
- }
- if insertTempCnt > 0 {
- insertSql = strings.TrimSuffix(insertSql, ",")
- fmt.Print("insert begin:")
- fmt.Println(time.Now())
- dblogic.M("GAMES1").Exec(insertSql)
- fmt.Print("insert end:")
- fmt.Println(time.Now())
- insertSql = ""
- }
- retMap := make(map[string]interface{})
- fmt.Print("insertCnt:")
- fmt.Println(insertCnt)
- fmt.Print("updateCnt:")
- fmt.Println(updateCnt)
- return retMap
- }
- // 可交付数据 只取到系列
- func TrackerCampaigns(date string) string {
- grouping := "networks,campaigns,countries,os_names"
- kpis := "daus,installs,revenue" //daus每日活跃用户数 installs安装 revenue收入
- url := "https://api.adjust.com/kpis/v1/rv3fkx1a8em8?start_date=" + date + "&end_date=" + date + "&grouping=" + grouping + "&kpis=" + kpis + "&user_token=" + user_token + "&attribution_type=click"
- fmt.Println(url)
- body := tool.Get(url)
- var dat map[string]interface{}
- if err := json.Unmarshal([]byte(body), &dat); err != nil {
- for err != nil {
- time.Sleep(3 * time.Second)
- body := tool.Get(url)
- err = json.Unmarshal([]byte(body), &dat)
- }
- }
- trackerGroup := TrackerGroup()
- list := dat["result_set"].(map[string]interface{})["networks"]
- if list == nil {
- return url
- }
- tableName := "t_adjust_period_campaigns"
- //获取日期为 date的全部数据
- adjustPeriodViewCampaign := []*tables.TAdjustPeriodViewCampaigns{}
- dblogic.M("GAMES1").Table(tableName).Where("date = ?", date).Find(&adjustPeriodViewCampaign)
- //遍历数据库中数据,组装成 key =》 id ( key 为 Date,Trackers_channel_token,Campaigns_token,Countries,OsNames 组成)
- adjustPeriodViewCampaignMap := map[string]int{}
- for _, row := range adjustPeriodViewCampaign {
- keyTemp := ""
- keyTemp += row.Date
- keyTemp += row.Trackers_channel_token
- keyTemp += row.Campaigns_token
- keyTemp += row.Countries
- keyTemp += row.OsNames
- adjustPeriodViewCampaignMap[keyTemp] = row.Id
- }
- updateCnt := 0
- updateSql := ""
- for _, v := range list.([]interface{}) {
- trackers_channel_token := function.Strval(v.(map[string]interface{})["token"])
- campaigns := v.(map[string]interface{})["campaigns"]
- if campaigns == nil {
- //fmt.Println("campaigns null")
- continue
- }
- for _, campaignsRow := range campaigns.([]interface{}) {
- campaigns_token := function.Strval(campaignsRow.(map[string]interface{})["token"])
- countries := campaignsRow.(map[string]interface{})["countries"]
- if countries == nil {
- //fmt.Println("countries null")
- continue
- }
- for _, countryRow := range countries.([]interface{}) {
- //第一层国家
- country := function.Strval(countryRow.(map[string]interface{})["country"])
- os_names := countryRow.(map[string]interface{})["os_names"]
- if os_names == nil {
- //fmt.Println("os_names null")
- continue
- }
- for _, os_nameRow := range os_names.([]interface{}) {
- //第二层设备
- os_name := function.Strval(os_nameRow.(map[string]interface{})["os_name"])
- //fmt.Println("设备:" + os_name)
- kpi_values := function.Strval(os_nameRow.(map[string]interface{})["kpi_values"])
- trackers_group_type := ""
- for key, _ := range trackerGroup {
- if function.IsContain(trackerGroup[key], trackers_channel_token) {
- trackers_group_type = key
- break
- }
- }
- if trackers_group_type == "Facebook" {
- //fmt.Println("Facebook continue")
- continue
- }
- updateCnt++
- keyTemp := date + trackers_channel_token + campaigns_token + country + os_name
- updateSql = "update " + tableName + " set `kpi_values`='" + kpi_values + "' where id=" + function.Strval(adjustPeriodViewCampaignMap[keyTemp])
- db := dblogic.M("GAMES1").Exec(updateSql)
- if db.Error != nil {
- fmt.Print(updateSql)
- fmt.Println(db.Error)
- continue
- }
- if updateCnt%300 == 0 {
- fmt.Print("update cnt:300 time:")
- fmt.Println(time.Now().Unix())
- }
- }
- }
- }
- }
- fmt.Print("updateCnt:")
- fmt.Println(updateCnt)
- return url
- }
- func PeriodViewTokens() []string {
- periodViewTokens := []string{}
- periodViewChannels := []string{"Facebook", "Google_ads", "Mintegral", "苹果商店页搜索广告", "TikTok"}
- trackerGroup := TrackerGroup()
- for _, channel := range periodViewChannels {
- for _, token := range trackerGroup[channel] {
- periodViewTokens = append(periodViewTokens, token)
- }
- }
- return periodViewTokens
- }
- // 同期群数据2 一次性请求多个设备 国家
- func Dates2(stime string, etime string, user_token string, trackerGroup map[string][]string, dateType string) {
- kpis := "retained_users,paying_users,revenue_total,paying_user_size"
- grouping := "trackers,countries,os_names"
- cohort_period_filter := "0,1,2,6,14,29,59,89,119"
- //https://api.adjust.com/kpis/v1/rv3fkx1a8em8/cohorts?start_date=2021-01-01&end_date=2021-01-31&grouping=trackers,countries,os_names&kpis=retained_users,paying_users,revenue_total&user_token=nDPDkox9PxGoisxoMm9Q&attribution_type=click&cohort_period_filter=0,1,2,6,14,29,59,89,119&reattributed=all
- url := "https://api.adjust.com/kpis/v1/rv3fkx1a8em8/cohorts?start_date=" + stime + "&end_date=" + etime + "&grouping=" + grouping + "&kpis=" + kpis + "&user_token=" + user_token + "&attribution_type=click" + "&cohort_period_filter=" + cohort_period_filter + "&reattributed=all"
- fmt.Print("request begin:")
- fmt.Println(time.Now())
- body := tool.Get(url)
- fmt.Print("request end:")
- fmt.Println(time.Now())
- var saveDate string = ""
- if dateType == "day" {
- saveDate = stime
- } else if dateType == "week" {
- //周
- sdate, _ := function.Strtotime("2006-01-02", stime)
- edate, _ := function.Strtotime("2006-01-02", etime)
- saveDate = function.Date("20060102", sdate) + "~" + function.Date("20060102", edate)
- } else if dateType == "month" {
- //月
- sdate, _ := function.Strtotime("2006-01-02", stime)
- edate, _ := function.Strtotime("2006-01-02", etime)
- saveDate = function.Date("20060102", sdate) + "~" + function.Date("20060102", edate)
- }
- var dat map[string]interface{}
- if err := json.Unmarshal([]byte(body), &dat); err != nil {
- for err != nil {
- time.Sleep(3 * time.Second)
- body := tool.Get(url)
- err = json.Unmarshal([]byte(body), &dat)
- }
- }
- var listMap map[string]string /*创建集合 */
- listMap = make(map[string]string)
- parameters, _ := dat["result_parameters"].(map[string]interface{})["trackers"]
- if parameters == nil {
- return
- }
- for _, v := range parameters.([]interface{}) {
- name := function.Strval(v.(map[string]interface{})["name"])
- token := function.Strval(v.(map[string]interface{})["token"])
- //fmt.Println("name:"+name)
- //fmt.Println("token:"+token)
- listMap[token] = name
- }
- list := dat["result_set"].(map[string]interface{})["trackers"]
- if list == nil {
- return
- }
- //判断数据库是否存在,不存在则保存
- var tableName string = ""
- if dateType == "day" {
- tableName = "t_adjust_period"
- } else if dateType == "week" {
- //周
- tableName = "t_adjust_period_week"
- } else if dateType == "month" {
- //月
- tableName = "t_adjust_period_month"
- }
- insertTemp, insertCnt := 0, 0
- insertSql := "insert into " + tableName + " (`date`,`countries`,`os_names`,`trackers_channel_token`,`trackers_channel_name`,`trackers_group_type`,`period_0_kpis`,`period_1_kpis`,`period_2_kpis`,`period_6_kpis`,`period_14_kpis`,`period_29_kpis`,`period_59_kpis`,`period_89_kpis`,`period_119_kpis`) values "
- updateCnt := 0
- updateSql := ""
- adjustsPeriod := []*tables.TAdjustPeriod{}
- dblogic.M("GAMES1").Table(tableName).Where("date = ?", saveDate).Find(&adjustsPeriod)
- adjustsPeriodMap := map[string]int{}
- for _, row := range adjustsPeriod {
- adjustsPeriodMap[row.Date+row.Countries+row.OsNames+row.TrackersChannelToken] = row.Id
- }
- for _, v := range list.([]interface{}) {
- token := function.Strval(v.(map[string]interface{})["token"])
- //fmt.Println(token)
- countries := v.(map[string]interface{})["countries"]
- if countries == nil {
- continue
- }
- for _, countryRow := range countries.([]interface{}) {
- //第一层国家
- country := function.Strval(countryRow.(map[string]interface{})["country"])
- //fmt.Println("国家:" + country)
- os_names := countryRow.(map[string]interface{})["os_names"]
- if os_names == nil {
- continue
- }
- for _, os_nameRow := range os_names.([]interface{}) {
- //第二层设备
- os_name := function.Strval(os_nameRow.(map[string]interface{})["os_name"])
- //fmt.Println("设备:" + os_name)
- var kpisList map[string]string /*创建集合 */
- kpisList = make(map[string]string)
- periods := os_nameRow.(map[string]interface{})["periods"]
- for _, pv := range periods.([]interface{}) {
- period := function.Strval(pv.(map[string]interface{})["period"])
- kpi_values := function.Strval(pv.(map[string]interface{})["kpi_values"])
- kpisList[period] = kpi_values
- }
- trackers_channel_name := listMap[token]
- trackers_group_type := "Organic_all"
- for key, _ := range trackerGroup {
- if function.IsContain(trackerGroup[key], token) {
- trackers_group_type = key
- break
- }
- }
- keyTemp := saveDate + country + os_name + token
- if adjustsPeriodMap[keyTemp] == 0 {
- insertCnt++
- insertTemp++
- insertSql += "('"
- insertSql += saveDate + "','"
- insertSql += country + "','"
- insertSql += os_name + "','"
- insertSql += token + "','"
- insertSql += trackers_channel_name + "','"
- insertSql += trackers_group_type + "','"
- insertSql += kpisList["0"] + "','"
- insertSql += kpisList["1"] + "','"
- insertSql += kpisList["2"] + "','"
- insertSql += kpisList["6"] + "','"
- insertSql += kpisList["14"] + "','"
- insertSql += kpisList["29"] + "','"
- insertSql += kpisList["59"] + "','"
- insertSql += kpisList["89"] + "','"
- insertSql += kpisList["119"] + "'),"
- if insertTemp == 300 { //每300条 批量执行
- insertTemp = 0
- insertSql = strings.TrimSuffix(insertSql, ",")
- fmt.Print("insert begin:")
- fmt.Println(time.Now())
- dblogic.M("GAMES1").Exec(insertSql)
- fmt.Print("insert end:")
- fmt.Println(time.Now())
- insertSql = "insert into " + tableName + " (`date`,`countries`,`os_names`,`trackers_channel_token`,`trackers_channel_name`,`trackers_group_type`,`period_0_kpis`,`period_1_kpis`,`period_2_kpis`,`period_6_kpis`,`period_14_kpis`,`period_29_kpis`,`period_59_kpis`,`period_89_kpis`,`period_119_kpis`) values "
- }
- } else {
- updateCnt++
- updateSql = "update " + tableName + " set "
- updateSql += "`trackers_channel_name` = '" + trackers_channel_name + "',"
- updateSql += "`trackers_group_type` = '" + trackers_group_type + "',"
- updateSql += "`period_0_kpis` = '" + kpisList["0"] + "',"
- updateSql += "`period_1_kpis` = '" + kpisList["1"] + "',"
- updateSql += "`period_2_kpis` = '" + kpisList["2"] + "',"
- updateSql += "`period_6_kpis` = '" + kpisList["6"] + "',"
- updateSql += "`period_14_kpis` = '" + kpisList["14"] + "',"
- updateSql += "`period_29_kpis` = '" + kpisList["29"] + "',"
- updateSql += "`period_59_kpis` = '" + kpisList["59"] + "',"
- updateSql += "`period_89_kpis` = '" + kpisList["89"] + "',"
- updateSql += "`period_119_kpis` = '" + kpisList["119"] + "'"
- updateSql += " where id=" + function.Strval(adjustsPeriodMap[keyTemp])
- db := dblogic.M("GAMES1").Exec(updateSql)
- if db.Error != nil {
- fmt.Print(updateSql)
- fmt.Println(db.Error)
- continue
- }
- if updateCnt%300 == 0 {
- fmt.Print("update cnt:300 time:")
- fmt.Println(time.Now().Unix())
- }
- }
- }
- }
- }
- if insertTemp > 0 { //每300条 批量执行
- insertTemp = 0
- insertSql = strings.TrimSuffix(insertSql, ",")
- fmt.Print("insert begin:")
- fmt.Println(time.Now())
- dblogic.M("GAMES1").Exec(insertSql)
- fmt.Print("insert end:")
- fmt.Println(time.Now())
- }
- fmt.Print("insertCnt:")
- fmt.Println(insertCnt)
- fmt.Print("updateCnt:")
- fmt.Println(updateCnt)
- }
- func getCountry() {
- list := []tables.TCountryCode{}
- dblogic.M("GAMES").Table("t_country_code").Find(&list)
- }
- type Periods struct {
- Kpi_values []int64
- Period int64
- }
- type OsNames struct {
- Os_name string
- Periods []Periods
- }
- type Countries struct {
- Country string
- Os_names []OsNames
- }
- type Creatives struct {
- Name string
- Token string
- Countries []Countries
- }
- type Adgroups struct {
- Name string
- Token string
- Creatives []Creatives
- }
- type Campaigns struct {
- Name string
- Token string
- Adgroups []Adgroups
- }
- func PeriodViewNew(date string, grouping string, kpis string) map[string]interface{} {
- // 根据 渠道, token
- var params = make(map[string]string)
- params["dimensions"] = "network,tracker_token,adgroup_id_network,creative_id_network,channel,os_name,country_code,campaign_id_network,campaign_network,adgroup_network"
- params["metrics"] = "daus,installs,revenue,retained_users_d0,retained_users_d1,retained_users_d2,retained_users_d6,retained_users_d14,retained_users_d29,retained_users_d59,retained_users_d89,retained_users_d119,paying_users_d0,paying_users_d1,paying_users_d2,paying_users_d6,paying_users_d14,paying_users_d29,paying_users_d59,paying_users_d89,paying_users_d119,revenue_total_d0,revenue_total_d1,revenue_total_d2,revenue_total_d6,revenue_total_d14,revenue_total_d29,revenue_total_d59,revenue_total_d89,revenue_total_d119,paying_user_size_d0,paying_user_size_d1,paying_user_size_d2,paying_user_size_d6,paying_user_size_d14,paying_user_size_d29,paying_user_size_d59,paying_user_size_d89,paying_user_size_d119"
- params["attribution_type"] = "click"
- params["reattributed"] = "false"
- params["app_token__in"] = "rv3fkx1a8em8"
- params["country_code__in"] = "de"
- params["date_period"] = fmt.Sprintf("%v:%v", date, date)
- url := apiUrlNew
- for pk, pv := range params {
- url += fmt.Sprintf("%v=%v&", pk, pv)
- }
- headers := map[string]string{
- "Authorization": "Bearer Tha-k75HDFTdG9WE_s58",
- }
- fmt.Println(url)
- body := tool.GetWithHeaders(url, headers)
- var dat map[string]interface{}
- if err := json.Unmarshal([]byte(body), &dat); err != nil {
- for err != nil {
- time.Sleep(3 * time.Second)
- body := tool.Get(url)
- err = json.Unmarshal([]byte(body), &dat)
- }
- }
- trackerGroup := TrackerGroup()
- list := dat["result_set"].(map[string]interface{})["networks"]
- if list == nil {
- return map[string]interface{}{}
- }
- tableName := "t_adjust_period_view"
- //获取日期为 date的全部数据
- adjustPeriodView := []*tables.TAdjustPeriodView{}
- dblogic.M("GAMES1").Table(tableName).Where("date = ?", date).Find(&adjustPeriodView)
- //遍历数据库中数据,组装成 key =》 id ( key 为 Date,Trackers_channel_token,Campaigns_token,Adgroups_token,Creatives_token,Countries,OsNames 组成)
- adjustPeriodViewMap := map[string]int{}
- for _, row := range adjustPeriodView {
- keyTemp := ""
- keyTemp += row.Date
- keyTemp += row.Trackers_channel_token
- keyTemp += row.Campaigns_token
- keyTemp += row.Adgroups_token
- keyTemp += row.Creatives_token
- keyTemp += row.Countries
- keyTemp += row.OsNames
- adjustPeriodViewMap[keyTemp] = row.Id
- }
- insertCnt, insertTempCnt := 0, 0
- insertSql := "insert into " + tableName + " (" +
- "`date`," +
- "`trackers_channel_token`," +
- "`trackers_channel_name`," +
- "`trackers_group_type`," +
- "`campaigns_token`," +
- "`campaigns_name`," +
- "`campaigns_id`," +
- "`adgroups_token`," +
- "`adgroups_name`," +
- "`adgroups_id`," +
- "`creatives_token`," +
- "`creatives_name`," +
- "`creatives_id`," +
- "`countries`," +
- "`os_names`," +
- "`period_0_kpis`," +
- "`period_1_kpis`," +
- "`period_2_kpis`," +
- "`period_6_kpis`," +
- "`period_14_kpis`," +
- "`period_29_kpis`," +
- "`period_59_kpis`," +
- "`period_89_kpis`," +
- "`period_119_kpis`) values "
- insertOrigin := insertSql
- updateCnt := 0
- updateSql := ""
- periodViewTokens := PeriodViewTokens()
- for _, v := range list.([]interface{}) {
- trackers_channel_token := function.Strval(v.(map[string]interface{})["token"])
- trackers_channel_name := function.Strval(v.(map[string]interface{})["name"])
- if trackers_channel_token != "r2phf78" &&
- trackers_channel_token != "ussdj6t" &&
- trackers_channel_token != "6xxj7ry" &&
- trackers_channel_token != "rs64xuy" &&
- !function.IsContain(periodViewTokens, trackers_channel_token) {
- fmt.Println(trackers_channel_token + " null 不是 FB & Google_ads & Mintegral & TikTok & asa")
- continue
- }
- campaigns := v.(map[string]interface{})["campaigns"]
- if campaigns == nil {
- continue
- }
- for _, campaignsRow := range campaigns.([]interface{}) {
- campaigns_token := function.Strval(campaignsRow.(map[string]interface{})["token"])
- campaigns_name := function.Strval(campaignsRow.(map[string]interface{})["name"])
- campaigns_id := function.RegularGetBrackets(campaigns_name) //正则匹配出扩展里面的数值
- adgroups := campaignsRow.(map[string]interface{})["adgroups"]
- if adgroups == nil {
- continue
- }
- for _, adgroupsRow := range adgroups.([]interface{}) {
- adgroups_token := function.Strval(adgroupsRow.(map[string]interface{})["token"])
- adgroups_name := function.Strval(adgroupsRow.(map[string]interface{})["name"])
- adgroups_id := function.RegularGetBrackets(adgroups_name) //正则匹配出扩展里面的数值
- creatives := adgroupsRow.(map[string]interface{})["creatives"]
- if creatives == nil {
- continue
- }
- for _, creativesRow := range creatives.([]interface{}) {
- creatives_token := function.Strval(creativesRow.(map[string]interface{})["token"])
- creatives_name := function.Strval(creativesRow.(map[string]interface{})["name"])
- creatives_id := function.RegularGetBrackets(creatives_name) //正则匹配出扩展里面的数值
- if creatives_id == "" {
- //fmt.Println("creatives_id null")
- //continue
- }
- countries := creativesRow.(map[string]interface{})["countries"]
- if countries == nil {
- continue
- }
- for _, countryRow := range countries.([]interface{}) {
- //第一层国家
- country := function.Strval(countryRow.(map[string]interface{})["country"])
- os_names := countryRow.(map[string]interface{})["os_names"]
- if os_names == nil {
- continue
- }
- for _, os_nameRow := range os_names.([]interface{}) {
- //第二层设备
- os_name := function.Strval(os_nameRow.(map[string]interface{})["os_name"])
- var kpisList map[string]string /*创建集合 */
- kpisList = make(map[string]string)
- periods := os_nameRow.(map[string]interface{})["periods"]
- for _, pv := range periods.([]interface{}) {
- period := function.Strval(pv.(map[string]interface{})["period"])
- kpi_values := function.Strval(pv.(map[string]interface{})["kpi_values"])
- kpisList[period] = kpi_values
- }
- trackers_group_type := ""
- for key, _ := range trackerGroup {
- if function.IsContain(trackerGroup[key], trackers_channel_token) {
- trackers_group_type = key
- break
- }
- }
- //判断数据库是否存在,不存在则保存
- keyTemp := ""
- keyTemp += date
- keyTemp += trackers_channel_token
- keyTemp += campaigns_token
- keyTemp += adgroups_token
- keyTemp += creatives_token
- keyTemp += country
- keyTemp += os_name
- if adjustPeriodViewMap[keyTemp] == 0 {
- insertCnt++
- insertTempCnt++
- insertSql += "('"
- insertSql += date + "','"
- insertSql += trackers_channel_token + "','"
- insertSql += trackers_channel_name + "','"
- insertSql += trackers_group_type + "','"
- insertSql += campaigns_token + "','"
- insertSql += strings.Replace(campaigns_name, "'", "\\'", -1) + "','"
- insertSql += campaigns_id + "','"
- insertSql += adgroups_token + "','"
- insertSql += strings.Replace(adgroups_name, "'", "\\'", -1) + "','"
- insertSql += adgroups_id + "','"
- insertSql += creatives_token + "','"
- insertSql += strings.Replace(creatives_name, "'", "\\'", -1) + "','"
- insertSql += creatives_id + "','"
- insertSql += country + "','"
- insertSql += os_name + "','"
- insertSql += kpisList["0"] + "','"
- insertSql += kpisList["1"] + "','"
- insertSql += kpisList["2"] + "','"
- insertSql += kpisList["6"] + "','"
- insertSql += kpisList["14"] + "','"
- insertSql += kpisList["29"] + "','"
- insertSql += kpisList["59"] + "','"
- insertSql += kpisList["89"] + "','"
- insertSql += kpisList["119"] + "'),"
- if insertTempCnt == 300 { //每300条 批量执行
- insertTempCnt = 0
- insertSql = strings.TrimSuffix(insertSql, ",")
- fmt.Print("insert begin:")
- fmt.Println(time.Now())
- dblogic.M("GAMES1").Exec(insertSql)
- fmt.Print("insert end:")
- fmt.Println(time.Now())
- insertSql = insertOrigin
- }
- } else {
- updateCnt++
- updateSql = "update " + tableName + " set "
- updateSql += "`trackers_channel_name` = '" + trackers_channel_name + "',"
- updateSql += "`trackers_group_type` = '" + trackers_group_type + "',"
- updateSql += "`campaigns_name` = '" + strings.Replace(campaigns_name, "'", "\\'", -1) + "',"
- updateSql += "`campaigns_id` = '" + campaigns_id + "',"
- updateSql += "`adgroups_name` = '" + strings.Replace(adgroups_name, "'", "\\'", -1) + "',"
- updateSql += "`adgroups_id` = '" + adgroups_id + "',"
- updateSql += "`creatives_name` = '" + strings.Replace(creatives_name, "'", "\\'", -1) + "',"
- updateSql += "`creatives_id` = '" + creatives_id + "',"
- updateSql += "`period_0_kpis` = '" + kpisList["0"] + "',"
- updateSql += "`period_1_kpis` = '" + kpisList["1"] + "',"
- updateSql += "`period_2_kpis` = '" + kpisList["2"] + "',"
- updateSql += "`period_6_kpis` = '" + kpisList["6"] + "',"
- updateSql += "`period_14_kpis` = '" + kpisList["14"] + "',"
- updateSql += "`period_29_kpis` = '" + kpisList["29"] + "',"
- updateSql += "`period_59_kpis` = '" + kpisList["59"] + "',"
- updateSql += "`period_89_kpis` = '" + kpisList["89"] + "',"
- updateSql += "`period_119_kpis` = '" + kpisList["119"] + "'"
- updateSql += " where id=" + function.Strval(adjustPeriodViewMap[keyTemp])
- db := dblogic.M("GAMES1").Exec(updateSql)
- if db.Error != nil {
- fmt.Print(updateSql)
- fmt.Println(db.Error)
- continue
- }
- if updateCnt%300 == 0 {
- fmt.Print("update cnt:300 time:")
- fmt.Println(time.Now().Unix())
- }
- }
- }
- }
- }
- }
- }
- }
- if insertTempCnt > 0 { //每300条 批量执行
- insertTempCnt = 0
- insertSql = strings.TrimSuffix(insertSql, ",")
- fmt.Print("insert begin:")
- fmt.Println(time.Now())
- dblogic.M("GAMES1").Exec(insertSql)
- fmt.Print("insert end:")
- fmt.Println(time.Now())
- insertSql = insertOrigin
- }
- fmt.Print("insertCnt:")
- fmt.Println(insertCnt)
- fmt.Print("updateCnt:")
- fmt.Println(updateCnt)
- return map[string]interface{}{}
- }
- func ViewNew(date string, grouping string, kpis string) interface{} {
- if date == "" {
- date = "2021-04-01"
- }
- if grouping == "" {
- grouping = "networks,campaigns,adgroups,creatives,countries,os_names"
- }
- if kpis == "" {
- kpis = "daus,installs,revenue" //daus每日活跃用户数 installs安装 revenue收入
- }
- url := "https://api.adjust.com/kpis/v1/rv3fkx1a8em8?countries=de&start_date=" + date + "&end_date=" + date + "&grouping=" + grouping + "&kpis=" + kpis + "&user_token=" + user_token + "&attribution_type=click"
- fmt.Println(url)
- body := tool.Get(url)
- var dat map[string]interface{}
- if err := json.Unmarshal([]byte(body), &dat); err != nil {
- for err != nil {
- time.Sleep(3 * time.Second)
- body := tool.Get(url)
- err = json.Unmarshal([]byte(body), &dat)
- }
- }
- return dat
- list := dat["result_set"].(map[string]interface{})["networks"]
- if list == nil {
- return url
- }
- tableName := "t_adjust_period_view"
- //获取日期为 date的全部数据
- adjustPeriodView := []*tables.TAdjustPeriodView{}
- dblogic.M("GAMES1").Table(tableName).Where("date = ?", date).Find(&adjustPeriodView)
- //遍历数据库中数据,组装成 key =》 id ( key 为 Date,Trackers_channel_token,Campaigns_token,Adgroups_token,Creatives_token,Countries,OsNames 组成)
- adjustPeriodViewMap := map[string]int{}
- for _, row := range adjustPeriodView {
- keyTemp := ""
- keyTemp += row.Date
- keyTemp += row.Trackers_channel_token
- keyTemp += row.Campaigns_token
- keyTemp += row.Adgroups_token
- keyTemp += row.Creatives_token
- keyTemp += row.Countries
- keyTemp += row.OsNames
- adjustPeriodViewMap[keyTemp] = row.Id
- }
- updateCnt := 0
- updateSql := ""
- periodViewTokens := PeriodViewTokens()
- for _, v := range list.([]interface{}) {
- trackers_channel_token := function.Strval(v.(map[string]interface{})["token"])
- trackers_channel_name := function.Strval(v.(map[string]interface{})["name"])
- if trackers_channel_token != "r2phf78" &&
- trackers_channel_token != "ussdj6t" &&
- trackers_channel_token != "6xxj7ry" &&
- trackers_channel_token != "rs64xuy" &&
- !function.IsContain(periodViewTokens, trackers_channel_token) {
- //fmt.Println(trackers_channel_token + " null 不是 FB & Google_ads & Mintegral")
- fmt.Println(trackers_channel_name + " 不是 FB & Google_ads & Mintegral & TikTok & asa")
- continue
- }
- campaigns := v.(map[string]interface{})["campaigns"]
- if campaigns == nil {
- //fmt.Println("campaigns null")
- continue
- }
- for _, campaignsRow := range campaigns.([]interface{}) {
- campaigns_token := function.Strval(campaignsRow.(map[string]interface{})["token"])
- //campaigns_name := function.Strval(campaignsRow.(map[string]interface{})["name"])
- //campaigns_id := function.RegularGetBrackets(campaigns_name) //正则匹配出扩展里面的数值
- //fmt.Println(campaigns_id)
- adgroups := campaignsRow.(map[string]interface{})["adgroups"]
- if adgroups == nil {
- //fmt.Println("adgroups null")
- continue
- }
- for _, adgroupsRow := range adgroups.([]interface{}) {
- adgroups_token := function.Strval(adgroupsRow.(map[string]interface{})["token"])
- //adgroups_name := function.Strval(adgroupsRow.(map[string]interface{})["name"])
- //adgroups_id := function.RegularGetBrackets(adgroups_name) //正则匹配出扩展里面的数值
- //fmt.Println(adgroups_id)
- creatives := adgroupsRow.(map[string]interface{})["creatives"]
- if creatives == nil {
- //fmt.Println("creatives null")
- continue
- }
- for _, creativesRow := range creatives.([]interface{}) {
- creatives_token := function.Strval(creativesRow.(map[string]interface{})["token"])
- countries := creativesRow.(map[string]interface{})["countries"]
- if countries == nil {
- //fmt.Println("countries null")
- continue
- }
- for _, countryRow := range countries.([]interface{}) {
- //第一层国家
- country := function.Strval(countryRow.(map[string]interface{})["country"])
- //fmt.Println("国家:" + country)
- os_names := countryRow.(map[string]interface{})["os_names"]
- if os_names == nil {
- //fmt.Println("os_names null")
- continue
- }
- for _, os_nameRow := range os_names.([]interface{}) {
- //第二层设备
- os_name := function.Strval(os_nameRow.(map[string]interface{})["os_name"])
- //fmt.Println("设备:" + os_name)
- kpi_values := function.Strval(os_nameRow.(map[string]interface{})["kpi_values"])
- keyTemp := date + trackers_channel_token + campaigns_token + adgroups_token + creatives_token + country + os_name
- updateCnt++
- updateSql = "update " + tableName + " set "
- updateSql += "`kpi_values` = '" + kpi_values + "'"
- updateSql += " where id=" + function.Strval(adjustPeriodViewMap[keyTemp])
- db := dblogic.M("GAMES1").Exec(updateSql)
- if db.Error != nil {
- fmt.Print(updateSql)
- fmt.Println(db.Error)
- continue
- }
- if updateCnt%300 == 0 {
- fmt.Print("update cnt:300 time:")
- fmt.Println(time.Now().Unix())
- }
- }
- }
- }
- }
- }
- }
- fmt.Print("updateCnt:")
- fmt.Println(updateCnt)
- return url
- }
|