To help marketers: write to automatically upload data from Adfox with visualization

last article was considered an example of setting for automatic downloading of data from Yandex Metrics. This facilitates the regular discharge, but the way it turns out semi-automatic: it is necessary to run the script, copy the results of the discharge to themselves and to continue their registration. See how to make the process fully automatic. For example, we use the rollout of their advertising system Adfox.

Adfox has a lot of ready-made reports, but even a simple complication of the reporting requirements cause problems. There is an option for setting up regular reports, but in most cases it is necessary to set combinations push myself.

This article shows you how to make a script that uploads the CTR of all banners that are available to the account (do it manually is unrealistic at the time). And how to display it on the chart as a simple HTML page. Ie we will review all the campaigns and flights, take the impressions and clicks for all banners and draw a graph of CTR. As in the last article we will work on instances of Amazon Web Services to be versatile and most importantly free. Upload data in PHP, graphics draw in Highcharts. In the code implemented the easiest way of loading and processing data without any improvements. In the end we get the daily updated for yesterday page view:



Start with creating your account on AWS (if not). This procedure is described in detail in the documentation: docs.aws.amazon.com/AWSEC2/latest/UserGuide/ec2-ug.pdf (to the point of Getting started). Next, we go through the stage described in this documentation in Chapter "Tutorial: Installing a LAMP Web Server on Amazon Linux" install we need to unload and data storage PHP and MySQL (in the future, to use MySQL better than text files). You can certainly do without it using the built-in Python. Launch an Amazon Linux (see previous article where we have chosen Ubuntu Server instance). Here we need exactly Linux, but not Ubuntu. To start the LAMP web server on Ubuntu see the documentation: help.ubuntu.com/community/ApacheMySQLPHP. So, choose Amazon Linux instance, check the Security Group and make sure we have the private key. After a couple of minutes we get our Public DNS of the instance and connect to it using Putty (Windows) or Terminal for MAC:



Put a LAMP web server. Run the command check that you have the latest updates:
$ sudo yum update -y

We put the appropriate PHP version:
$ sudo yum install -y httpd24 php56 mysql55-server php56-mysqlnd

Start the Apache web server:
$ sudo service httpd start

Want Apache web server to start at every system startup:
$ sudo chkconfig httpd on

Test that all went well: kopipastom in the browser on our Public DNS of the instance and get the following page:



Now the root directory /var/www/ "owns" the root user. Make access to the user ec2-user:

Create a group "www":
$ sudo groupadd www

Add user ec2-user to the group www
$ sudo usermod -a-G www ec2-user

For the change in force close Putty or the Terminal (command exit) to connect to the instance again. Check that the user ec2-user are registered in the group www
$ groups
ec2-user wheel www

Change the owner of the folder /var/www
$ sudo chown-R root:www /var/www

Added the possibility for the user ec2-user and other users in the group www permissions to modify the folder /var/www
$ sudo chmod 2775 /var/www
$ find /var/www -type d-exec sudo chmod 2775 {} +
$ find /var/www -type f -exec sudo chmod 0664 {} +

Test that we can create. PHP files in /var/www. To do this, create a file phpinfo.php and write in it <?php phpinfo(); ?>:
$ echo "<?php phpinfo(); ?>" > /var/www/html/phpinfo.php

Again in the browser address bar kopipastom our Public DNS of the instance and add "/phpinfo.php". We need to get the next page with the PHP configuration:


For security purposes, delete the file we created phpinfo.php:
$ rm /var/www/html/phpinfo.php

Well, we are ready to upload data from Adfox!

Unload statistics of Adfox
The documentation for the API Adfox can be found in help.adfox.ru (download link at the bottom of the page). In General, the documentation is not very clear, so in the General case you can use the following rule: if you need to get the link for the report in the API, then copy the link from the browser and fills in her username and password + '&isXML=1' to receive data in XML format. If you need a search function or campaign settings, you have to look in the API documentation.

In the General case, the API requests are accompanied by Adfox login (how you log in) and HaShem SHA-256 from your password. Generate hash to the password in any website for the search term "sha-256 online". For example, here: www.xorbin.com/tools/sha256-hash-calculator

If your password is "12345", then calls to the API as the password necessary to use the value "5994471abb01112afcc18159f6cc74b4f511b99806da59b3caf5a9c173cacfc5":



Our algorithm will consist of two steps:

Step 1. Get a list of all available campaigns.
Take the list of available campaigns. If a lot of campaigns, we can restrict to those that were instituted, for example, August 1:
login.adfox.ru/API.php?object=account&action=list&actionObject=superCampaign&dateAddedFrom=2015-08-01&loginAccount=login&loginPassword=parol-sha-256&isXML=1

The browser will receive the following XML file:



Step 2. For each campaign have its name (a tag ) and ID (). Now for each ID of the received report banners (for example, for campaign with ID=55555):
login.adfox.ru/commonReportsOutputForm.php?period=&startDate=nachalnaya-data&endDate=Konecna-data&criteria=superCampaignBanner&isNewSearch=on&objectName=superCampaign&objectID=55555&ignoreStartDate=off&loginAccount=login&loginPassword=parol-sha-256&isXML=1



Make a script that will do this procedure and write data to a file. The easiest way is to go to the folder /var/www/html, create a folder there adfox and create the file daily.php with the following code:

Code unloading data Adfox

<?php

// Set the date range for which the paged data: startDate and endDate
// Date format for queries to the Adfox DD.mm.gg
date_default_timezone_set("Europe/Moscow");
//$startDate = '04.09.15';
//$endDate = '04.09.15';

// For daily uploads specified as the date of discharge yesterday
$startDate = date('d.m.y', strtotime('-1 day'));
$endDate = $startDate;

// Function getCampaigns shows the login campaign
// Returns an array $campIDs with ID and name campaigns
// In the request body in a variable dateAddedFrom you can specify the creation date of the campaign not to upload everything
function getCampaigns() {
$ch = curl_init();
$options = array(
CURLOPT_URL => 'https://login.adfox.ru/API.php?object=account&action=list&actionObject=superCampaign&dateAddedFrom=2015-08-01&loginAccount=login&loginPassword=parol-sha-256&isXML=1',
CURLOPT_HEADER => "Content-Type:application/xml",
CURLOPT_HTTPAUTH = > CURLAUTH_BASIC,
THIS => FALSE,
CURLOPT_RETURNTRANSFER => TRUE
);

curl_setopt_array($ch, $options);
$data = curl_exec($ch);

curl_close($ch);

// You can write the answer Adfox in the file to verify its correctness
// file_put_contents('request.txt', $data, FILE_APPEND);

// Convert response Adfox from XML into the array vals
$parser=xml_parser_create('UTF-8');
xml_parse_into_struct($parser, $data, $vals, $index);

$campIDs = array();
$j = 0;

// Pass the array vals, choosing from all the options ID the name of the campaigns and recording them in an array campIDs
for ($i = 0; $i < count($vals); ++$i) {

if ($vals[$i]["tag"]=="ID") {

}

if ($vals[$i]["tag"]=="NAME") {
$name = $vals[$i]["value"];

$campIDs[$j]['id'] = $ID;
$campIDs[$j]['name'] = $name;

$j += 1;
}
}

return $campIDs;
}

// Function getBanners for a given campaign ID number requests a report on banners
// The report is written to the array campReport in the form of the ID + name of the flight name of the banner - impressions - clicks
function getBanners($id, $startDate, $endDate) {
$ch = curl_init();
$options = array(
CURLOPT_URL => 'https://login.adfox.ru/commonReportsOutputForm.php?period=&startDate='.$startDate.'&endDate='.$endDate.'&criteria=superCampaignBanner&isNewSearch=on&objectName=superCampaign&objectID='.$id.'&ignoreStartDate=off&loginAccount=login&loginPassword=parol-sha-256&isXML=1',
CURLOPT_HEADER => "Content-Type:application/xml",
CURLOPT_HTTPAUTH = > CURLAUTH_BASIC,
THIS => FALSE,
CURLOPT_RETURNTRANSFER => TRUE
);

curl_setopt_array($ch, $options);
$data = curl_exec($ch);

curl_close($ch);

//You can write the answer Adfox in the file to verify its correctness
//file_put_contents('request.txt', $data, FILE_APPEND);

$parser=xml_parser_create('UTF-8');
xml_parse_into_struct($parser, $data, $vals, $index);

$campReport = array();
$j = 0;

for ($i = 0; $i < count($vals); ++$i) {
if ($vals[$i]["tag"]=="CRITERIA") {
$bannername = $vals[$i]["value"];
}

if ($vals[$i]["tag"]=="FLIGHTNAME") {
$flightname = $vals[$i]["value"];
}

if ($vals[$i]["tag"]=="IMPRESSIONS") {
$impressions = $vals[$i]["value"];
}

if ($vals[$i]["tag"]=="CLICKS") {
$clicks = $vals[$i]["value"];

$campReport[$j]['campaign'] = $id;
$campReport[$j]['flightname'] = $flightname;
$campReport[$j]['bannername'] = $bannername;
$campReport[$j]['impressions'] = $impressions;
$campReport[$j]['clicks'] = $clicks;

$j += 1;
}
}

return $campReport;
}

// Get the list of available campaigns
$campIDs = getCampaigns();

// For each campaign requested a report on banners
for ($i = 0; $i < count($campIDs); ++$i) {
$campReport = getBanners($campIDs[$i]['id'], $startDate, $endDate);

// For each banner is written to the file data.txt the result of the query with impressions and clicks
// Full path of the file data.txt you need to specify for the correct operation of the auto-upload Cron
for ($j = 0; $j < count($campReport); ++$j) {
file_put_contents('/var/www/html/adfox/data.txt', $startDate."\t".$campReport[$j]['campaign']."\t".$campReport[$j]['flightname']."\t".$campReport[$j]['bannername']."\t".$campReport[$j]['impressions']."\t".$campReport[$j]['clicks']."\n", FILE_APPEND);

// Write to console line record was created (optional)
echo 'Record created - '.$startDate.' - '.$campReport[$j]['bannername']."\n";
}
}

Run code in a few days (e.g., 1-4 September) in conjunction with the startDate and endDate dates from 01.09.15 to 04.09.15. As a result, the file data.txt need to accumulate statistics for each banner for each day from 1 to 4 September.

It is necessary to display our data on a chart and put a script to automatically update. For drawing graphs we use www.highcharts.com/demo/line-basic. You can download a library for instance or contact the libraries on the Highcharts website. The example uses the second option. All the code samples can be found on www.highcharts.com/demo/line-basic clicking below the graph, click "EDIT IN JSFIDDLE".

For correct operation of the libraries we will need jQuery. Download it site or directly in the console in the folder /var/www/html/adfox:
$ wget code.jquery.com/jquery-1.11.3.js

Added code graphics in a file report.html:

Code of the HTML page with visualization

<html>
<head>

<script src="jquery-1.11.3.js"></script>
<script src="http://code.highcharts.com/highcharts.js"></script>
<script src="http://code.highcharts.com/modules/exporting.js"></script>

</head>

<body>

<script>

// array list of banners that have been uploaded to the file data.txt
var banners = [];

// arrays of dates, impressions, and clicks load from file data.txt
// the values of CTR (ratio of clicks to impressions) calculate
var dates1 = [];
var shows1 = [];
var clicks1 = [];
var ctr1 = [];

var dates2 = [];
var shows2 = [];
var clicks2 = [];
var ctr2 = [];

var dates3 = [];
var shows3 = [];
var clicks3 = [];
var ctr3 = [];

// read the file data.txt line by line
$.get("data.txt", function(data){
var lines = data.split("\n");
for (var i = 0, len = lines.length; i < len; i++) {
// check in the console that the file is read correctly
//console.log(lines[i]);

// share in every line of the file into an array of 6 elements corresponding to the columns in data.txt
elements = lines[i].split("\t");

// the name of the banner is the third column takes the third element of the array elements
// (just in case - numbering of array elements starts with 0, not 1)
banners.push(elements[2]);
}
// check if banner names was read correctly
//console.log(banners);

// get unique name banners and write to the array bannerNames first 3 titles

{
var tmp = {}, out = [];
for(var i = 0, n = this.length; i < n; ++i)
{
if(!tmp[this[i]]) { tmp[this[i]] = true; out.push(this[i]); }
}
return out;
}

bannersNames = banners.unique().slice(0, 3);

// check if received 3 correct banner names
//console.log(bannersNames);

// pass again for the file data.txt and select date, impressions and clicks for banners from an array bannerNames
for (var i = 0, len = lines.length; i < len; i++) {

elements = lines[i].split("\t");

// if line in the file data.txt refers to the first banner, then add to the array of the dates of the impressions and clicks values this line
if (elements[2] == bannersNames[0]) {
dates1.push(elements[0]);
shows1.push(parseInt(elements[4]));
clicks1.push(parseInt(elements[5]));

// toFixed(3) - round CTR values to the third decimal place
if (parseInt(elements[4]) > 0) {
ctr1.push(Number(parseFloat(elements[5] / elements[4] * 100).toFixed(3)));
} else {
ctr1.push(0);
}
}

// same for the second banner
if (elements[2] == bannersNames[1]) {
dates2.push(elements[0]);
shows2.push(parseInt(elements[4]));
clicks2.push(parseInt(elements[5]));
if (parseInt(elements[4]) > 0) {
ctr2.push(Number(parseFloat(elements[5] / elements[4] * 100).toFixed(3)));
} else {
ctr2.push(0);
}
}

// same for the third banner
if (elements[2] == bannersNames[2]) {
dates3.push(elements[0]);
shows3.push(parseInt(elements[4]));
clicks3.push(parseInt(elements[5]));
if (parseInt(elements[4]) > 0) {
ctr3.push(Number(parseFloat(elements[5] / elements[4] * 100).toFixed(3)));
} else {
ctr3.push(0);
}
}
}

// check if all arrays CTR (which will be on the chart) are formatted as numbers
// for example: [0.22, 0.25, 0.30, 0.24]
//console.log(ctr1);
//console.log(ctr2);
//console.log(ctr3);

// display values on the chart CTR
$(function () {
// the name of the block container must match the called <div id="container"... the bottom of the page
$('#container').highcharts({
// graph title
title: {
text: 'Campaign banners CTR',
x: -20 //center
},

// signature to the header
subtitle: {
text: 'Source: ADFOX',
x: -20
},

// as the axis of  ASCII  take values array of dates dates1
xAxis: {
categories: dates1
},

// parameters for y-axis
yAxis: {
title: {
text: 'CTR %'
},
plotLines: [{
value: 0,
width: 1,
color: '#808080'
}]
},

// optional hints when you hover on the chart
tooltip: {
valueSuffix: '%'
},

// settings for legend
legend: {
layout: 'vertical',
align: 'right',
verticalAlign: 'middle',
borderWidth: 0
},

// set the values that you want to display on the chart: the name of the banner and the CTR values from each array
series: [{
name: bannersNames[0],
data: ctr1
}, {
name: bannersNames[1],
data: ctr2
}, {
name: bannersNames[2],
data: ctr3
}],
credits: {
enabled: false
}
});
});
});
</script>

<div id="container" style="min-width: 310px; height: 400px; margin: 0 auto"></div>

</body>
</html>

Open the page report.html in the browser, not forgetting that the file is in the folder adfox:



It was necessary to put our script at the daily update (e.g., 8:00 system time instance, which you can check the date command in console). To do this in the console execute:
$ crontab-e

The default editor, vim. To edit the file press 'i' (edit mode) and add the following line (insert in the editor using Shift+Ins):
00 08 * * * /usr/bin/php /var/www/html/adfox/daily.php > /var/www/html/adfox/out

Then press esc (exit edit mode) and type ':wq' to save and exit. The console should display the line:
crontab: installing new crontab

To check the schedule:
$ crontab-l
00 08 * * * /usr/bin/php /var/www/html/adfox/daily.php > /var/www/html/adfox/out

In out file, you can track the results of the work of Cron tasks. For example, if errors occur during script execution.

Read more about Cron on AWS: docs.aws.amazon.com/opsworks/latest/userguide/workingcookbook-extend-cron.html

Everything is ready. So we have a script that every morning it updates the data on impressions and clicks for all banners available to us yesterday. As well as the HTML page where at any time you can see the dynamics of the CTR of banners over the past few days. On AWS there are many possibilities for configuring secure access to data. Also, if you are improving this approach to write the data into the database MySQL, which is much easier to work with than text files. I hope this approach will help you to save time with regular work with Adfox.

Article based on information from habrahabr.ru