Automation of data collection for advertising companies: make your life easier

I've been in internet marketing for 5 years and spend the lion's share of my time collecting ad placements reports. According to my observations, marketers do not often ask themselves the question of how to facilitate the collection of data on advertising campaigns for an analyst, or (now I will say a scary thing), how you can collect data yourself and analyze your own traffic yourself.

I want to share the best-practices and give several ready-made developments that will be useful to both marketers and established marketing analysts.

This longread is entirely devoted to the issues that web marketing deals with and is based on my personal experience of working with the guys from Betting League marketing. (I can talk endlessly about mobile advertising, mobile-marketing, how we built Buying Dashboard and customer analytics back in mobio.ru, and maybe I'll write separately.)

1) The importance of markup

The cornerstone of all analytics, which can increase the time to collect a report from 15 minutes to 15 hours or more, is the markup of advertising campaigns.

Internet marketing works on different models (CPM, CPC for media, commission, RevShare and CPA for performance marketing). There are two main points: traffic source and placement tracker. The devil, as you know, is in the details - there are a lot of small and important points that I would like to discuss. But first things first:

1. "Cost" of advertising placement.

   
   
   
   
   

   (MyTarget, Google Ads, Google DV360, Facebook, DSP), , ( ). Adfox — side . , , , MyTarget Facebook, . / , .

   
   
   
   
   

2. «» .

   
   
   
   
   

   2021 , , (CPA, RevShare) ( hard- soft- KPI) . Google Analytics. , - (, , ), ( , , ). , .

   
   
   
   
   

3. — .

   
   
   
   
   

   , — — . , . .

   
   
   
   
   

   Google Analytics , ? .
   
   

   
   
   
   
   

   :

   
   
   
   
   

   • (utm_source)

     
     
     
     
     

   • (utm_campaign)

     
     
     
     
     

   • (utm_medium)

     
     
     
     
     

   • (utm_content)

     
     
     
     
     

   • (utm_term)

     
     
     
     
     

   - , .

   
   
   
   
   

   , :

   
   
   
   
   

   • / , URL- , .

     
     
     
     
     

   • - (, ) , : (cashback, kashback, cashback10, cash10)

     
     
     
     
     

   • -, , / (mobile_640x480, 640x480_mobile).

     
     
     
     
     

   • ( ), utm_content, utm_campaign.

     
     
     
     
     

   , :

   
   
   
   
   

   Utm_source — //,

   
   
   
   
   

   Utm_campaign — ,

   
   
   
   
   

   Utm_medium — , cpm/cpc

   
   
   
   
   

   Utm_content — ,

   
   
   
   
   

   { }_{ }|{ }. . 

   
   
   
   
   

   — , . Adfox : . , - , . , {utm_campaign} - { utm_content} - { } | { }.

   
   
   
   
   

   ( 15 ), .

   
   
   
   
   

   . , ( ). / , . , . 

   
   
   
   
   

2)   

Adfox . . Adfox , . «» . «» API», Adfox. , API .

            . .

1.     : 

function getCampaignList(key) {

var key = '' // INSERT YOUR API TOKEN HERE

var url =  'https://adfox.yandex.ru/api/v1?object=account&action=list&actionObject=campaign&show=common&limit=200'  

var table = []

var option = { headers : {'X-Yandex-API-Key' : key}

var request = UrlFetchApp.fetch(url, option);

//decode data    

var text = request.getContentText("windows-1251");

var cdata = text.split('<ID>')

 //parse data

// Adfox   ,       

// <ID>

  for(var i in cdata)if(i!=0){

  var id = cdatat[i].split('</ID>')[0]

  var cname = test[i].split('<name>')[1].split('</name>')[0]

  var row = [id,cname]

  table.push(row)

    }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

2.     , . , , UTM . :

function getBannersList(){

//get campaign info

var key = '' // INSERT YOUR API TOKEN HERE

var campdata = getCampaignList(key)

var table = [];

table.push(['Campaign ID','Campaign Name','Banner ID','Banner Name'])

 //get paginated list of banners.   ,      //   100   URL   

 for(var a in campdata){

 var campbanners = []

  var campid = campdata[a][0];

  var cname = campdata[a][1];

var url =

 'https://adfox.yandex.ru/api/v1?object=campaign&action=list&actionObject=banner&show=common&limit=1000&objectID='+campid

 var option = {

 headers : {  'X-Yandex-API-Key' : key  }  }

     var request = UrlFetchApp.fetch(url, option);

  //decode

     var text = request.getContentText("windows-1251");

    var test = text.split('<ID>')

  //  parse

    for(var i in test)if(i!=0){

      var bid = test[i].split('</ID>')[0];

      var bname = test[i].split('<name>')[1].split('</name>');

      campbanners.push(bid);

      table.push([campid,cname,bid,bname])

 }

//pagination    

   var a = campbanners.length

    for(var x=0; x<a; x = x + 100){

  var start = x;

  var end = x + 100;

  if(a>end)var banners = campbanners.slice(start, end);

  else{banners = campbanners.slice(x,a)};

  var banners = campbanners.slice(start, end)

  banners = '['+banners.toString()+']'

  //get taskid for the data batch

  var taskid = orderData(banners,key) //    

 // Logger.log(taskid); //For testing Purposes

  Utilities.sleep(5000) //its better to have a 5 sec delay

  var data = getData(taskid,cname,bname,key); // SEE the code in utilities.gs

    }

  }

SpreadsheetApp.getActive().getSheetByName('banner list').getRange(1,1,table.length,table[0].length).setValues(table);

 

    return;

  

  addCorectBname()

  return test;

}
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

3.     :

function orderData(banners,key,date){ //gets taskId for the batch

//      ""

//        date   .

  var date = new Date();

  date.setHours(-20);

date = date.toISOString().substr(0,10)

var datefrom = date  //'2020-08-01';

var dateto = date   //'2020-09-27'

var url = 

"https://adfox.yandex.ru/api/report/banner?name=days&bannerId="+banners+"&dateFrom="+datefrom+"&dateTo="+dateto+"&precision=high";

var option = { headers : {  'X-Yandex-API-Key' : key  }  }

var request = UrlFetchApp.fetch(url, option);

var text = request.getContentText();

var json = JSON.parse(text);

var taskid = json['result']['taskId'];

return taskid;

}

 

//     id    

function getData(taskId,campname,banname,key){ //gets Report via task ID

  var url = 'https://adfox.yandex.ru/api/report/result?taskId='+taskId;

var option = { headers : { 'X-Yandex-API-Key' : key  }   }

var request = UrlFetchApp.fetch(url, option);

var text = request.getContentText();

var json = JSON.parse(text);

var table = []

var title = json['result']['fields'];

var tab = json['result']['table'];

for(var i in tab){

            var row = tab[i]

row.push(campname,banname);

            table.push(row)

             }

if(table.length > 0){var t = 

SpreadsheetApp.getActive().getSheetByName('data').getDataRange().getValues();

SpreadsheetApp.getActive().getSheetByName('data').getRange(t.length+1,1,table.length,table[0].length).setValues(table);

                      }

  

  return json;

}
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

Adfox. API .

3)

Google Analytics ( ). API Google Analytics . GA.

:

function nextDay(date,loop){

 // var loop = 0 // 0 - ,   -  +,  - -

 // var date = new Date()

  var day = 1000 * 60 * 60 * 24;

  var b = new Date(date.getTime()+loop*day)

  b = b.toISOString().substr(0,10); // -       

  return b;

}

 

function runDemo() {

  var date = new Date();

  date.setHours(12);

  for(var i=1;i<=1;i++){

  var datefrom = nextDay(date,-1*i)

  var dateto=datefrom;

  try {

      

        var firstProfile = getFirstProfile();

        var results = getReportDataForProfile(firstProfile,datefrom,dateto);

      outputToSpreadsheet(results);

  

    } 

    catch(error) {

        Browser.msgBox(error.message);

    }

  }

}
  

function getFirstProfile() {

    var accounts = Analytics.Management.Accounts.list();

    if (accounts.getItems()) {

        var firstAccountId = accounts.getItems()[0].getId();

      Logger.log(firstAccountId)

        var webProperties = Analytics.Management.Webproperties.list(firstAccountId);

        if (webProperties.getItems()) {

            var firstWebPropertyId = webProperties.getItems()[0].getId();

           Logger.log(firstWebPropertyId)

            var profiles = Analytics.Management.Profiles.list(firstAccountId, firstWebPropertyId);

            if (profiles.getItems()) {

                var firstProfile = profiles.getItems()[0];

              var tolog = firstProfile.getId()

              Logger.log(tolog)

                return firstProfile;

            } 

            else {

                throw new Error('No views (profiles) found.');

            }

        } 

        else {

            throw new Error('No webproperties found.');

        }

    } 

    else {

        throw new Error('No accounts found.');

    }

  return;

}

function getReportDataForProfile(firstProfile,datefrom,dateto) {

    var profileId = firstProfile.getId();

    var tableId = 'ga:' + profileId;

    var startDate = datefrom 

    var endDate = dateto

    var optArgs = {

      'dimensions':'ga:source,ga:campaign,ga:medium,ga:adContent,ga:date', // Comma separated list of dimensions.

      'metrics': 'ga:users,ga:sessions,ga:newUsers,ga:bounces,ga:goal10Completions',

        'segment': 'gaid::-1',  //

        'samplingLevel': 'HIGHER_PRECISION',

        'start-index': '1',

        'max-results': '1000000'

    };

  

    // Make a request to the API.

    var results = Analytics.Data.Ga.get(

        tableId,                    // Table id (format ga:xxxxxx).

        startDate,                  // Start-date (format yyyy-MM-dd).

        endDate,                    // End-date (format yyyy-MM-dd).

        'ga:sessions',//,ga:pageviews', // Comma seperated list of metrics.

        optArgs);

  

    if (results.getRows()) {

        return results;

    } 

    else {

        throw new Error('No views (profiles) found');

    }

}

function outputToSpreadsheet(results) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GA");

  var indexFrom = sheet.getDataRange().getValues().length 

  var headerNames = ['ga:source','ga:campaign','ga:medium','ga:adContent','date','ga:users','ga:sessions','ga:newUsers','ga:bounces','1 ']

    sheet.getRange(1, 1, 1, headerNames.length).setValues([headerNames]);

    sheet.getRange(indexFrom+1, 1, results.getRows().length, headerNames.length).setValues(results.getRows());

}
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

. Google Data Studio. . , , , , .