Getting Reports from Mailchimp, storing them in our database and displaying on our web browser
1st we create 2 tables in our database namely order_reports_mailchimp and analytics_mailchimp,
1st we get the data from mailchimp using API and store in ordre_reports_mailchimp. This data doesnt contains the revenue and transactions details now so those fields are blank, we fetch the reports from the google analytics and store this result in table analytics_mailchimp table, this report contains many extra campaign names which do not belong to mailchimp. Now we match the campaign names in table ordre_reports_mailchimp and analytics_mailchimp and update the 'Revenue' and 'Transcations' fields of order_reports_mailchimp with the values obtained from analytics_mailchimp table. Finally we display the data of order_reports_mailchimp in a tabular format.
There can be a better way to do this anyway, I used this one as it was simple to implement!
Step1.
GET DATA FROM MAILCHIMP USING API AND SAVE IT IN OUR DATABASE order_reports_mailchimp:
saveReports.php
<?php
// ### GETS REPORTS FROM MAILCHIMP API AND STORING THEM IN order_reports_mailchimp TABLE ### //
require_once "functions.php";
require_once "mailchimpLib.php";
require_once "config.php";
require_once "db.php";
$db = new DB();
$list_id = LISTID;
$database = DATABASE;
$username = USERNAME;
$api_key = APIKEY;
$mcLib = new Mailchimp();
$Reports = $mcLib->getList($list_id);
$json_array = getJson($Reports);
$reportsArray = $json_array['reports'];
//echo '<pre>'; print_r($reportsArray);
$response = $mcLib->saveData($reportsArray);
echo '<pre>'; print_r($response);
?>
mailchimpLib.php
<?php
class Mailchimp {
function __construct() {
}
public function getList($list_id){
require_once "functions.php";
global $list_id, $MCAPI, $username, $api_key;
$dateSince = date('Y-m-d h:i:s',strtotime("-11 months"));
$dateSinceArr = (str_replace(' ','T',$dateSince));
$dateSince = $dateSinceArr;
$dateSince = $dateSince.'+00:00';
$count = 1000;
$api = array
(
'login' => $username,
'key' => $api_key,
'url' => 'https://us3.api.mailchimp.com/3.0/reports/?count='.$count.'&since_send_time='.$dateSince.'&list_id='.$list_id
);
$result = $this->mc_request( $api,'GET','','');
return $result;
}
public function mc_request( $api, $type, $target, $data = false ) {
$ch = curl_init( $api['url'] . $target );
curl_setopt( $ch, CURLOPT_HTTPHEADER, array
(
'Content-Type: application/json',
'Authorization: ' . $api['login'] . ' ' . $api['key'],
)
);
curl_setopt($ch, CURLOPT_HEADER, 1);
curl_setopt( $ch, CURLOPT_CUSTOMREQUEST, $type );
curl_setopt( $ch, CURLOPT_TIMEOUT, 5 );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, true );
curl_setopt( $ch, CURLOPT_USERAGENT, 'YOUR-USER-AGENT' );
curl_setopt( $ch, CURLOPT_CUSTOMREQUEST, $type );
if( $data )
curl_setopt( $ch, CURLOPT_POSTFIELDS, json_encode( $data ) );
$response = curl_exec( $ch );
//echo '<pre>'; var_dump($response); die("TEST");
//$httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close( $ch );
return $response;
}
public function getReportsList(){
global $db, $database;
$sqlGet = "SELECT * FROM $database.order_reports_mailchimp
WHERE sendTime BETWEEN NOW() - INTERVAL 3 month AND NOW()
ORDER BY sendTime DESC";
$result = $db->getTable($sqlGet);
return $result;
}
public function saveData($reportsArray){
global $database, $db;
$msg[0] = 'Updated on date: '.date("Y-m-d H:i:s");
$count = count($reportsArray);
for($i=0; $i<$count; $i++){
//echo '<pre>'; print_r($reportsArray[$i]);
$id = $reportsArray[$i]['id'];
$campaignTitle = $this->checkquotes($reportsArray[$i]['campaign_title']);
$type = $reportsArray[$i]['type'];
$emailsSent = $reportsArray[$i]['emails_sent'];
$abuseReports = $reportsArray[$i]['abuse_reports'];
$unsubscribed = $reportsArray[$i]['unsubscribed'];
$sendTime = $this->getTime($reportsArray[$i]['send_time']);
$bounces = $reportsArray[$i]['bounces'];
$hardBounces = $bounces['hard_bounces'];
$softBounces = $bounces['soft_bounces'];
$syntaxErrors = $bounces['syntax_errors'];
$forwards = $reportsArray[$i]['forwards'];
$forwardsCount = $forwards['forwards_count'];
$forwardsOpens = $forwards['forwards_opens'];
$opens = $reportsArray[$i]['opens'];
$opensTotal = $opens['opens_total'];
$uniqueOpens = $opens['unique_opens'];
$open_rate = $opens['open_rate'];
$open_rate = ($open_rate*100);
$openRate = round($open_rate, 2);
$lastOpen = $this->getTime($opens['last_open']);
//$lastOpen = formatTime($last_open);
$clicks = $reportsArray[$i]['clicks'];
$clicksTotal = $clicks['clicks_total'];
$uniqueClicks = $clicks['unique_clicks'];
$uniqueSubscriberClicks = $clicks['unique_subscriber_clicks'];
//$click_rate = $clicks['click_rate'];
//$click_rate = round($clicks['click_rate'],4);
//$click_rate = ($click_rate * 100 );// . '%';
$clickRate = $clicks['click_rate'];
$lastClick = $this->getTime($clicks['last_click']);
$industry_stats = $reportsArray[$i]['industry_stats'];
$industryStatsType = $industry_stats['type'];
$industryStatsOpenRate = $industry_stats['open_rate'];
$industryStatsClickRate = $industry_stats['click_rate'];
$industryStatsBounceRate = $industry_stats['bounce_rate'];
$industryStatsUnopenRate = $industry_stats['unopen_rate'];
$industryStatsUnsubRate = $industry_stats['unsub_rate'];
$industryStatsAbuseRate = $industry_stats['abuse_rate'];
$list_stats = $reportsArray[$i]['list_stats'];
$listStatsSubRate = $list_stats['sub_rate'];
$listStatsUnsubRate = $list_stats['unsub_rate'];
$listStatsOpenRate = $list_stats['open_rate'];
$listStatsClickRate = $list_stats['click_rate'];
$ecommerce = $reportsArray[$i]['ecommerce'];
$ecommerceTotalOrders = $ecommerce['total_orders'];
$ecommerceTotalSpent = $ecommerce['total_spent'];
$ecommerceTotalRevenue = $ecommerce['total_revenue'];
$sqlCheckDuplicate = "SELECT * FROM $database.`order_reports_mailchimp`
WHERE campaignTitle = '$campaignTitle' AND sendTime = '$sendTime'";
$res = $db->GetTable($sqlCheckDuplicate);
$num = count($res);
if($num==0){
$sqlInsert = "INSERT INTO $database.`order_reports_mailchimp`
(`campaignTitle`,`type`,`emailsSent`,`abuseReports`,`unsubscribed`,`sendTime`,`hardBounces`,
`softBounces`,`syntaxErrors`,`forwardsCount`,`forwardsOpens`,`opensTotal`,`uniqueOpens`,`openRate`,
`lastOpen`,`clicksTotal`,`uniqueClicks`,`uniqueSubscriberClicks`,`clickRate`,`lastClick`,
`industryStatsType`,`industryStatsOpenRate`,`industryStatsClickRate`,`industryStatsBounceRate`,
`industryStatsUnopenRate`,`industryStatsUnsubRate`,`industryStatsAbuseRate`,`listStatsSubRate`,
`listStatsUnsubRate`,`listStatsOpenRate`,`listStatsClickRate`,`ecommerceTotalOrders`,
`ecommerceTotalSpent`,`ecommerceTotalRevenue`,`campaignId`)
VALUES('$campaignTitle','$type','$emailsSent','$abuseReports','$unsubscribed','$sendTime',
'$hardBounces','$softBounces','$syntaxErrors','$forwardsCount','$forwardsOpens','$opensTotal',
'$uniqueOpens','$openRate','$lastOpen','$clicksTotal','$uniqueClicks','$uniqueSubscriberClicks',
'$clickRate','$lastClick','$industryStatsType','$industryStatsOpenRate',
'$industryStatsClickRate','$industryStatsBounceRate','$industryStatsUnopenRate',
'$industryStatsUnsubRate','$industryStatsAbuseRate','$listStatsSubRate',
'$listStatsUnsubRate','$listStatsOpenRate','$listStatsClickRate','$ecommerceTotalOrders',
'$ecommerceTotalSpent','$ecommerceTotalRevenue','$id')";
$ins = $db->Command($sqlInsert);
if($ins==1)
$msg[] = "Successfully added record with CampaignTitle '$campaignTitle' in database ".$ins;
else{
$msg[] = "Failed to add record with CampaignTitle '$campaignTitle' in database ".$ins;
}
}else{
//UPDATE ALL FIELDS IN DATABASE
$res = $this->updateFields($reportsArray[$i]);
if($res >=0)
$msg[] = "Updated record with CampaignTitle '$campaignTitle' as it already exists in database ".$res;
else
$msg[] = "Failed to update record with CampaignTitle '$campaignTitle' ".$res;
}
}
return $msg;
}
public function updateFields($reportsArray){
global $database, $db;
$id = $reportsArray['id'];
$campaignTitle = $this->checkquotes($reportsArray['campaign_title']);
$type = $reportsArray['type'];
$emailsSent = $reportsArray['emails_sent'];
$abuseReports = $reportsArray['abuse_reports'];
$unsubscribed = $reportsArray['unsubscribed'];
$sendTime = $this->getTime($reportsArray['send_time']);
$bounces = $reportsArray['bounces'];
$hardBounces = $bounces['hard_bounces'];
$softBounces = $bounces['soft_bounces'];
$syntaxErrors = $bounces['syntax_errors'];
$forwards = $reportsArray['forwards'];
$forwardsCount = $forwards['forwards_count'];
$forwardsOpens = $forwards['forwards_opens'];
$opens = $reportsArray['opens'];
$opensTotal = $opens['opens_total'];
$uniqueOpens = $opens['unique_opens'];
$open_rate = $opens['open_rate'];
$open_rate = ($open_rate*100);
$openRate = round($open_rate, 2);
$lastOpen = $this->getTime($opens['last_open']);
//$lastOpen = formatTime($last_open);
$clicks = $reportsArray['clicks'];
$clicksTotal = $clicks['clicks_total'];
$uniqueClicks = $clicks['unique_clicks'];
$uniqueSubscriberClicks = $clicks['unique_subscriber_clicks'];
//$click_rate = $clicks['click_rate'];
//$click_rate = round($clicks['click_rate'],4);
//$click_rate = ($click_rate * 100 );// . '%';
//$clickRate = $click_rate.' %';
$clickRate = $clicks['click_rate'];
$lastClick = $this->getTime($clicks['last_click']);
$industry_stats = $reportsArray['industry_stats'];
$industryStatsType = $industry_stats['type'];
$industryStatsOpenRate = $industry_stats['open_rate'];
$industryStatsClickRate = $industry_stats['click_rate'];
$industryStatsBounceRate = $industry_stats['bounce_rate'];
$industryStatsUnopenRate = $industry_stats['unopen_rate'];
$industryStatsUnsubRate = $industry_stats['unsub_rate'];
$industryStatsAbuseRate = $industry_stats['abuse_rate'];
$list_stats = $reportsArray['list_stats'];
$listStatsSubRate = $list_stats['sub_rate'];
$listStatsUnsubRate = $list_stats['unsub_rate'];
$listStatsOpenRate = $list_stats['open_rate'];
$listStatsClickRate = $list_stats['click_rate'];
$ecommerce = $reportsArray['ecommerce'];
$ecommerceTotalOrders = $ecommerce['total_orders'];
$ecommerceTotalSpent = $ecommerce['total_spent'];
$ecommerceTotalRevenue = $ecommerce['total_revenue'];
$sql = "UPDATE $database.`order_reports_mailchimp` SET
`campaignTitle` = '$campaignTitle',
`type` = '$type',
`emailsSent` = '$emailsSent',
`abuseReports` = '$abuseReports',
`unsubscribed` = '$unsubscribed',
`sendTime` = '$sendTime',
`hardBounces` = '$hardBounces',
`softBounces` = '$softBounces',
`syntaxErrors` = '$syntaxErrors',
`forwardsCount` = '$forwardsCount',
`forwardsOpens` = '$forwardsOpens',
`opensTotal` = '$opensTotal',
`uniqueOpens` = '$uniqueOpens',
`openRate` = '$openRate',
`lastOpen` = '$lastOpen',
`clicksTotal` = '$clicksTotal',
`uniqueClicks` = '$uniqueClicks',
`uniqueSubscriberClicks` = '$uniqueSubscriberClicks',
`clickRate` = '$clickRate',
`lastClick` = '$lastClick',
`industryStatsType` = '$industryStatsType',
`industryStatsOpenRate` = '$industryStatsOpenRate',
`industryStatsClickRate` = '$industryStatsClickRate',
`industryStatsBounceRate` = '$industryStatsBounceRate',
`industryStatsUnopenRate` = '$industryStatsUnopenRate',
`industryStatsUnsubRate` = '$industryStatsUnsubRate',
`industryStatsAbuseRate` = '$industryStatsAbuseRate',
`listStatsSubRate` = '$listStatsSubRate',
`listStatsUnsubRate` = '$listStatsUnsubRate',
`listStatsOpenRate` = '$listStatsOpenRate',
`listStatsClickRate` = '$listStatsClickRate',
`ecommerceTotalOrders` = '$ecommerceTotalOrders',
`ecommerceTotalSpent` = '$ecommerceTotalSpent',
`ecommerceTotalRevenue` = '$ecommerceTotalRevenue'
WHERE `campaignId` = '$id' AND `sendTime` = '$sendTime'";
//echo $sql; echo '<br/>';echo '<br/>';echo '<br/>';
$upd = $db->Command($sql);
return $upd;
}
public function updateRevenue($campaignTitle,$ecommerceConversionRate,$transactions,$revenue, $sendTime){
global $db, $database;
$sqlUpd = "UPDATE $database.order_reports_mailchimp
SET ecommerceTotalRevenue=$revenue,
transactions=$transactions,
ecommerceConversionRate=$ecommerceConversionRate
WHERE campaignTitle='$campaignTitle' AND sendTime='$sendTime'";
//echo $sqlUpd; echo '<br/>';
$upd = $db->Command($sqlUpd);
return $upd;
}
public function updateAnalyticsFields($campaignsArray){
global $db, $database;
$campaignName = $campaignsArray[0];
$sessions = $this->checkComma($campaignsArray[1]);
$newSessionsPercentage = $this->checkComma($campaignsArray[2]);
$newUsers = $this->checkComma($campaignsArray[3]);
$bounceRate = $this->checkComma($campaignsArray[4]);
$pagesPerSession = $this->checkComma($campaignsArray[5]);
$avgSessionDuration = $this->checkComma($campaignsArray[6]);
$ecommerceConversionRate = $this->checkComma($campaignsArray[7]);
$transactions = $this->checkComma($campaignsArray[8]);
$revenue = $this->checkComma($campaignsArray[9]);
$sqlUpd = "UPDATE $database.`analytics_mailchimp`
SET `campaignName` = '$campaignName',
`sessions` = '$sessions',`newSessionsPercentage` = '$newSessionsPercentage',
`newUsers` = '$newUsers',`bounceRate` = '$bounceRate',
`pagesPerSession` = '$pagesPerSession',`avgSessionDuration` = '$avgSessionDuration',
`ecommerceConversionRate` = '$ecommerceConversionRate',`transactions` = '$transactions',
`revenue` = '$revenue'
WHERE `campaignName` = '$campaignName'";
$upd = $db->Command($sqlUpd);
return $upd;
}
public function checkComma($value){
$val = str_replace(',','',$value);
$val1 = str_replace('$','',$val);
$val2 = str_replace('%','',$val1);
return $val2;
//str_replace($find,$replace,$arr));
}
public function getYear($sendTime){
$TimeArr = explode(' ',$sendTime);
$dateArr = explode('-',$TimeArr[0]);
$month = $dateArr[1];
$year = $dateArr[0];
$day = $dateArr[2];
$yearLast = substr($year, -2);
$newDate = $yearLast.$month.$day;
//echo "New Date ";echo $newDate; die("TEST");
return $newDate;
}
public function getDateTime($sendTime){
$TimeArr = explode(' ',$sendTime);
$dateArr = explode('-',$TimeArr[0]);
$month = $dateArr[1];
//echo "sendTime ".$sendTime; echo '<br/>';
switch ($month) {
case "01": $mm = "1";
break;
case "02": $mm = "2";
break;
case "03": $mm = "3";
break;
case "04": $mm = "4";
break;
case "05": $mm = "5";
break;
case "06": $mm = "6";
break;
case "07": $mm = "7";
break;
case "08": $mm = "8";
break;
case "09": $mm = "9";
break;
default: $mm = $month;
}
$dateVal = $mm.'_';
//$dateVal = $mm.'_'.$dateArr[2].'_'.$dateArr[0];
//echo '<pre>'; print_r($dateArr); die("taa");// [campaignName] => //2016-06-17
return $dateVal;
}
public function checkquotes($str){
$len = strlen($str);
$text = substr($str,0,$len);
$text = str_replace("'", "''", $text);
$text = str_replace('"', '""', $text);
return $text;
}
public function getTime($str){
$timeArr = explode("+", $str);
$timeNew = $timeArr[0];
$timeFormatted = str_replace("T", " ", $timeNew);
return $timeFormatted;
}
}
?>
functions.php
<?php
function getJson($subscribed_members){
$resp = substr(strstr($subscribed_members, '{'), strlen('{'));
$js_result = '{'.$resp;
$jres = json_decode($js_result,true);
return $jres;
}
function getPercentage($data){
$data_percent = ($data*100);
$data_percent = round($data_percent, 2);
$data_percent = $data_percent.' %';
return $data_percent;
}
function formatTime($date_time){
$date_time_array = explode('+',$date_time);
$date_time_format = $date_time_array[0];
$date_formatted = (str_replace('T',' ',$date_time_format));
$old_date_timestamp = strtotime($date_formatted);
$new_date = date('D, F d, Y h:i A', $old_date_timestamp);
return $new_date;
}
function formatTimeReport($date_time){
$date_time_array = explode('+',$date_time);
$date_time_format = $date_time_array[0];
$date_formatted = (str_replace('T',' ',$date_time_format));
$old_date_timestamp = strtotime($date_formatted);
//5/17/16 8:38PM
$new_date = date('m/d/y h:i A', $old_date_timestamp);
return $new_date;
}
function getReports($id){
global $database, $db;
$sqlGet = "SELECT * FROM $database.order_reports_mailchimp
WHERE campaignId = '$id'";
$result = $db->getTable($sqlGet);
return $result;
}
?>
config.php
<?php
define ('LISTID', 'XXX');
define ('DATABASE', 'XXX');
define ('USERNAME', 'XXXX');
define ('APIKEY', 'XXXXX');
?>
Step2. GETTING DATA FROM GA AND STORING THEM INTO analytics_mailchimp table
saveAnalytics.php
<?php
//### GETS REPORTS USING GOOGLE ANALYTICS API AND STORES THEM IN analytics_mailchimp table ###//
require_once "db.php";
require_once "mailchimpLib.php";
$mcLib = new Mailchimp();
$db = new DB();
$database = "XXX";
//$csvFile = 'Campaigns.csv';
$csvFile = 'Reports.csv';
function readCSV($csvFile){
$file_handle = fopen($csvFile, 'r');
while (!feof($file_handle) ) {
$line_of_text[] = fgetcsv($file_handle, 1024);
}
fclose($file_handle);
return $line_of_text;
}
$campaignsArray = readCSV($csvFile);
//echo '<pre>'; print_r($campaignsArray); die('aaa');
$num = count($campaignsArray); //die("abcd");
$msg[0]='Updated on '.date("m/d/Y h:i:s");
for($j=0;$j<$num;$j++){
//echo '<pre>'; print_r($campaignsArray[$j]);
if(isset($campaignsArray[$j][3])&&($campaignsArray[$j][3]!='')){
$campaignName = $campaignsArray[$j][0];
$sessions = $mcLib->checkComma($campaignsArray[$j][1]);
$newSessionsPercentage = $mcLib->checkComma($campaignsArray[$j][2]);
$newUsers = $mcLib->checkComma($campaignsArray[$j][3]);
$bounceRate = $mcLib->checkComma($campaignsArray[$j][4]);
$pagesPerSession = $mcLib->checkComma($campaignsArray[$j][5]);
$avgSessionDuration = $mcLib->checkComma($campaignsArray[$j][6]);
$ecommerceConversionRate = $mcLib->checkComma($campaignsArray[$j][7]);
$transactions = $mcLib->checkComma($campaignsArray[$j][8]);
$revenue = $mcLib->checkComma($campaignsArray[$j][9]);
$sqlCheckDuplicate = "SELECT * FROM $database.`analytics_mailchimp`
WHERE campaignName = '$campaignName'";
$res = $db->GetTable($sqlCheckDuplicate);
$numRes = count($res);
if($numRes==0){
$sqlInsert = "INSERT INTO $database.`analytics_mailchimp`
(`campaignName`,`sessions`,`newSessionsPercentage`,
`newUsers`,`bounceRate`,`pagesPerSession`,`avgSessionDuration`,
`ecommerceConversionRate`,`transactions`,`revenue`,`dateCreate`)
VALUES
('$campaignName','$sessions','$newSessionsPercentage',
'$newUsers','$bounceRate','$pagesPerSession','NULL',
'$ecommerceConversionRate','$transactions','$revenue',now())";
$ins = $db->Command($sqlInsert);
if($ins==1)
$msg[] = "Successfully added data with campaignName ".$campaignName." with ins value: ".$ins;
else{
$msg[] = "Failed to add record with campaignName '$campaignName' in database ".$ins;
}
}else{
//UPDATE ALL FIELDS IN DATABASE
$res = $mcLib->updateAnalyticsFields($campaignsArray[$j]);
if($res >=0)
$msg[] = "Updated record with campaignName '$campaignName' as it already exists in database ".$res;
else
$msg[] = "Failed to update record with campaignName '$campaignName' ".$res;
}
}
}
echo '<pre>'; print_r($msg);
?>
Step 3:
UPDATING Revenue AND Transaction FIELDS OF order_reports_mailchimp TABLE with DATA FROM analytics_mailchimp TABLE
updateRevenue.php
<?php
//### MATCHES THE CAMPAIGN TITLE AND UPDATES THE REVENUE AND TRANSACTION FROM analytics_mailchimp TO order_reports_mailchimp TABLE ###//
require_once "db.php";
require_once "mailchimpLib.php";
$mcLib = new Mailchimp();
$db = new DB();
$database = "XXX";
$message[0]='Updated on '.date("d/m/Y h:i:s");
$sql = "SELECT campaignTitle, sendTime FROM $database.order_reports_mailchimp";
$data = $db->getTable($sql);
$count = count($data);
for($i=0; $i<$count; $i++){
$campaignTitle = $data[$i]['campaignTitle'];
$sendTime = $data[$i]['sendTime'];
$senddate = $mcLib->getDateTime($sendTime);
$result = matchAndUpdateCampaignTitle($campaignTitle, $senddate, $sendTime);
$message[] = $result;
}
echo '<pre>'; print_r($message);
function matchAndUpdateCampaignTitle($campaignTitle, $senddate, $sendTime){
global $db, $database, $mcLib;
$newCampaignName1 = $campaignTitle.'_'.$senddate; //PVC_4_23_2015
$yearMonthDate = $mcLib->getYear($sendTime);
//$newCampaignName = $campaignTitle.'-'.$senddate; //PVC_4_23_2015
$newCampaignName = $campaignTitle.'-'.$yearMonthDate; //PVC_4_23_2015
$sqlSelect = "SELECT * FROM $database.analytics_mailchimp WHERE campaignName LIKE '$newCampaignName%'";
//$sqlSelect = "SELECT * FROM $database.analytics_mailchimp WHERE CONTAINS(campaignName, '$newCampaignName* OR $newCampaignName1')";
$res = $db->getTable($sqlSelect);
//echo '<pre>'; print_r($res);
$num = count($res);
if($num ==0){
$newCampaignName = $campaignTitle.'_'.$senddate; //PVC_4_23_2015
$sqlSelect = "SELECT * FROM $database.analytics_mailchimp WHERE campaignName LIKE '$newCampaignName%'";
//$sqlSelect = "SELECT * FROM $database.analytics_mailchimp WHERE CONTAINS(campaignName, '$newCampaignName* OR $newCampaignName1')";
//campaignName LIKE '$newCampaignName%'";
//WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')
$res = $db->getTable($sqlSelect);
///echo '<pre>'; print_r($res);
$num = count($res);
}
//echo $sqlSelect; echo '<br/>';echo '<pre>'; print_r($res);
if($num>0){
$ecommerceConversionRate = $res[0]['ecommerceConversionRate'];
$transactions = $res[0]['transactions'];
$revenue = $res[0]['revenue'];
if($num>1){
for($i=1;$i<$num; $i++){
$ecommerceConversionRate = $ecommerceConversionRate+$res[$i]['ecommerceConversionRate'];
$transactions = $transactions+$res[$i]['transactions'];
$revenue = $revenue+$res[$i]['revenue'];
}
}
$upd = $mcLib->updateRevenue($campaignTitle,$ecommerceConversionRate,$transactions,$revenue, $sendTime);
if($upd>=0)
$msg = "Successfully updated records for campaignTitle='$campaignTitle' with upd value ".$upd;
else
$msg = "Failed to update records for campaignTitle='$campaignTitle' with upd value ".$upd;
return $msg;
}else{
$msg="campaignTitle '$campaignTitle' doesnt exists in mailchimp google analytics records";
return $msg;
}
}
?>
Step4. DISPLAY DATA FROM order_reports_mailchimp IN A TABULAR FORMAT
displayReports.php
<script src="jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$('#defaultView').show();
});
function getDuration(days){
$('#defaultView').hide();
$.ajax({
url: 'getDuration.php',
type: 'POST',
data: {duration : days},
success: function(data){
document.getElementById('tableView').innerHTML=data;
}
});
}
function viewReport(id){
$('#defaultView').hide();
$('#selectDuration').hide();
$.ajax({
url: 'viewReport.php',
type: 'POST',
data: {campaignId : id},
success: function(data){
document.getElementById('tableView').innerHTML=data;
}
});
}
</script>
<?php
require_once "functions.php";
require_once "db.php";
require_once "config.php";
require_once "mailchimpLib.php";
$mcLib = new Mailchimp();
$db = new DB();
$list_id = LISTID;
$database = DATABASE;
$Reports = $mcLib->getReportsList();
$count = count($Reports);
$reportsArray = $Reports;
?>
<div id="selectDuration" align="center">
<select id="duration" name="Select Days" onchange="getDuration(this.value)">
<option value="7 day">last week</option>
<option value="1 month">last month</option>
<option value="3 month" selected>last 3 months</option>
<option value="6 month">last 6 months</option>
<option value="9 month">last 9 months</option>
<option value="12 month">last year</option>
<option value="24 month">last 2 years</option>
</select>
</div>
</br></br></br>
<div id="tableView">
</div>
<div id="defaultView">
<line>
<box id="ReportData" class="ReportData">
<table align="center">
<tbody>
<?php
for($i=0; $i<$count; $i++){
$id = $reportsArray[$i]['campaignId'];
$campaign_title = $reportsArray[$i]['campaignTitle'];
$type = $reportsArray[$i]['type'];
$emails_sent = $reportsArray[$i]['emailsSent'];
$abuse_reports = $reportsArray[$i]['abuseReports'];
$unsubscribed = $reportsArray[$i]['unsubscribed'];
$send_time = $reportsArray[$i]['sendTime'];
$send_time = formatTime($send_time);
$forwards_count = $reportsArray[$i]['forwardsCount'];
$forwards_opens = $reportsArray[$i]['forwardsOpens'];
$opens_total = $reportsArray[$i]['opensTotal'];
$unique_opens = $reportsArray[$i]['uniqueOpens'];
$open_rate = $reportsArray[$i]['openRate'];
//$open_rate = $opens['open_rate'];
//$open_rate = ($open_rate*100);
$open_rate = round($open_rate, 2);
$last_open = $reportsArray[$i]['lastOpen'];
$last_open = formatTime($last_open);
//$clicks = $reportsArray[$i]['clicks'];
$clicks_total = $reportsArray[$i]['clicksTotal'];
$unique_clicks = $reportsArray[$i]['uniqueClicks'];
$unique_subscriber_clicks = $reportsArray[$i]['uniqueSubscriberClicks'];
$click_rate = round($reportsArray[$i]['clickRate'],4);
$click_rate = ($click_rate * 100 );// . '%';
$click_rate = $click_rate.' %';
$last_click = $reportsArray[$i]['lastClick'];
?>
<tr>
<td><?php echo $i+1; ?></td>
<td>
<strong><?php echo $campaign_title; ?></strong> <br/><?php echo $type; ?></br> Sent on <?php echo $send_time; ?>
</td>
<td>
<?php echo $emails_sent; ?></br>Subscribers
</td>
<td>
<?php echo $open_rate.' %'; ?></br>Opens
</td>
<td>
<?php echo $click_rate; ?></br>Clicks
</td>
<td>
<input type="button" id="<?php echo $id; ?>" value="View Report" onClick="viewReport(this.id)">
</td>
</tr>
<?php } ?>
</tbody>
</table>
</box>
</line>
</div>
getDuration.php
<?php
$result = $_REQUEST;
//echo '<pre>'; print_r($result);
//$list_id = '9df4278869';
$duration = $_REQUEST['duration'];
require_once "db.php";
$db = new DB();
$database = "XXX";
$Reports = getList($duration);
$reportsArray = $Reports;
//echo '<pre>'; print_r($reportsArray);?>
<line>
<box id="ReportData" class="ReportData">
<table align="center">
<tbody>
<?php
$count = count($reportsArray);
for($i=0; $i<$count; $i++){
$id = $reportsArray[$i]['campaignId'];
$campaign_title = $reportsArray[$i]['campaignTitle'];
$type = $reportsArray[$i]['type'];
$emails_sent = $reportsArray[$i]['emailsSent'];
$abuse_reports = $reportsArray[$i]['abuseReports'];
$unsubscribed = $reportsArray[$i]['unsubscribed'];
$send_time = $reportsArray[$i]['sendTime'];
$send_time = formatTime($send_time);
$forwards_count = $reportsArray[$i]['forwardsCount'];
$forwards_opens = $reportsArray[$i]['forwardsOpens'];
$opens_total = $reportsArray[$i]['opensTotal'];
$unique_opens = $reportsArray[$i]['uniqueOpens'];
$open_rate = $reportsArray[$i]['openRate'];
//$open_rate = $opens['open_rate'];
//$open_rate = ($open_rate*100);
$open_rate = round($open_rate, 2);
$last_open = $reportsArray[$i]['lastOpen'];
$last_open = formatTime($last_open);
//$clicks = $reportsArray[$i]['clicks'];
$clicks_total = $reportsArray[$i]['clicksTotal'];
$unique_clicks = $reportsArray[$i]['uniqueClicks'];
$unique_subscriber_clicks = $reportsArray[$i]['uniqueSubscriberClicks'];
$click_rate = round($reportsArray[$i]['clickRate'],4);
$click_rate = ($click_rate * 100 );// . '%';
$click_rate = $click_rate.' %';
//$click_rate = round((float)$click_rate * 100 );// . '%';
$last_click = $reportsArray[$i]['lastClick'];
//$formatter = new NumberFormatter('en_US', NumberFormatter::PERCENT);
//$last_click = $formatter->format($last_click_rate);
?>
<tr>
<td><?php echo $i+1; ?></td>
<td>
<strong><?php echo $campaign_title; ?></strong> <br/><?php echo $type; ?></br> Sent on <?php echo $send_time; ?>
</td>
<td>
<?php echo $emails_sent; ?></br>Subscribers
</td>
<td>
<?php echo $open_rate.' %'; ?></br>Opens
</td>
<td>
<?php echo $click_rate; ?></br>Clicks
</td>
<td>
<input type="button" id="<?php echo $id; ?>" value="View Report" onClick="viewReport(this.id)">
</td>
</tr>
<?php } ?>
</tbody>
</table>
</box>
<?php
function formatTime($date_time){
$date_time_array = explode('+',$date_time);
$date_time_format = $date_time_array[0];
$date_formatted = (str_replace('T',' ',$date_time_format));
return $date_formatted;
}
function getList($duration){
/* $sql = "
SELECT id, campaignTitle, DATE_FORMAT(sendTime, '%m/%d/%Y')
FROM $database.order_reports_mailchimp
WHERE sendTime BETWEEN NOW() - INTERVAL 30 DAY AND NOW()";*/
global $db, $database;
$sqlGet = "SELECT * FROM $database.order_reports_mailchimp
WHERE sendTime BETWEEN NOW() - INTERVAL $duration AND NOW()
ORDER BY sendTime DESC";
$result = $db->getTable($sqlGet);
return $result;
}
viewReport.php
<link rel="stylesheet" type="text/css" href="mailchimpStyle.css">
<?php
require_once("functions.php");
require_once("db.php");
require_once("config.php");
$database = DATABASE;
$db = new DB();
$campaignId = $_REQUEST['campaignId'];
$reports = getReports($campaignId);
echo 'campaignId: '.$campaignId; echo '<pre>'; print_r($reports);
$reportsArray = $reports[0];
$bouncedTotal = ($reportsArray['hardBounces']+$reportsArray['softBounces']);
$opens = $reportsArray['uniqueOpens'];
$clicks = $reportsArray['uniqueSubscriberClicks'];
$clicks_total = $reportsArray['clicksTotal'];
$last_click = formatTimeReport($reportsArray['lastClick']);
$unsubscribed = $reportsArray['unsubscribed'];
$campaign_title = $reportsArray['campaignTitle'];
$emails_sent = $reportsArray['emailsSent'];
$abuse_reports = $reportsArray['abuseReports'];
$total_orders = $reportsArray['ecommerceTotalOrders'];
$total_spent = $reportsArray['ecommerceTotalSpent'];
$total_spent = number_format((float)$total_spent, 2, '.', '');
$total_revenue = $reportsArray['ecommerceTotalRevenue'];
$total_revenue = number_format((float)$total_revenue, 2, '.', '');
$total_spent = '$'.$total_spent;
$total_revenue = '$'.$total_revenue;
$forwards_count = $reportsArray['forwardsCount'];
$open_rate = round($reportsArray['openRate'], 2);
$opens_total = $reportsArray['opensTotal'];
$last_open = formatTimeReport($reportsArray['lastOpen']);
//$open_rate = getPercentage($open_rate);
$successful_deliveries = ($emails_sent-$bouncedTotal);
$percentage_successful_deliveries = ($successful_deliveries/$emails_sent);
$successful_deliveries = number_format($successful_deliveries);
$percentage_successful_deliveries = getPercentage($percentage_successful_deliveries);
$click_per_unique_opens = getPercentage($clicks/$opens);
$click_rates = $reportsArray['clickRate'];
$click_rate = round($click_rates*100, 2);
$industry_stats_open_rate = $reportsArray['industryStatsOpenRate'];
$industry_stats_click_rate = $reportsArray['industryStatsClickRate'];
$industry_stats_open_rate = getPercentage($industry_stats_open_rate);
$industry_stats_click_rate = getPercentage($industry_stats_click_rate);
$list_stats_open_rate = $reportsArray['listStatsOpenRate'];
$list_stats_click_rate = $reportsArray['listStatsClickRate'];
$list_stats_open_rate = round($list_stats_open_rate, 2);
$list_stats_click_rate = round($list_stats_click_rate, 2);
$list_stats_open_rate = $list_stats_open_rate.' %';
$list_stats_click_rate = $list_stats_click_rate.' %';
$send_time = $reportsArray['sendTime'];
$send_time = formatTime($send_time);
$transactions = $reportsArray['transactions'];
if($transactions=='') $transactions = 0;
$ecommerceConversionRates = $reportsArray['ecommerceConversionRate'];
$ecommerceConversionRate = round($ecommerceConversionRates,2);
$ecommerceConversionRate.='%';
?>
<h3 align="left"><?php echo $campaign_title; ?><br/><?php echo $emails_sent; ?> Recipients</h3>
<p align="left">Delivered:<?php echo $send_time; ?> </p>
<br/><br/>
<div class="rTable">
<div class="rTableRow">
<div class="rTableCell"><?php echo $transactions; ?><br/> Transactions</div>
<div class="rTableCell"><?php echo $ecommerceConversionRate; ?><br/> Ecommerce Conversion Rate</div>
<div class="rTableCell"><?php echo $total_revenue; ?><br/> Total Revenue</div>
</div>
</div>
<br/>
<div class="rTable">
<div class="rTableRow">
<div class="rTableCell"></div>
<div class="rTableCell">Open Rate (<?php echo $open_rate; ?>%)</div>
<div class="rTableCell">Click Rate (<?php echo $click_rate; ?>%)</div>
</div>
<div class="rTableRow">
<div class="rTableCell">List Average</div>
<div class="rTableCell"><?php echo $list_stats_open_rate; ?></div>
<div class="rTableCell"><?php echo $list_stats_click_rate; ?></div>
</div>
<div class="rTableRow">
<div class="rTableCell">Industry Average</div>
<div class="rTableCell"><?php echo $industry_stats_open_rate; ?></div>
<div class="rTableCell"><?php echo $industry_stats_click_rate; ?></div>
</div>
</div>
</br></br></br>
<div class="rTable">
<div class="rTableRow">
<div class="rTableCell"><?php echo $opens; ?><br/> Opened</div>
<div class="rTableCell"><?php echo $clicks; ?><br/> Clicked</div>
<div class="rTableCell"><?php echo $bouncedTotal; ?><br/> Bounced</div>
<div class="rTableCell"><?php echo $unsubscribed; ?><br/> Unsubscribed</div>
</div>
</div>
</br></br></br>
<div class="vTable">
<div class="vTableRow">
<div class="vTableCell">
<div class="vTableCellLeft">Successful deliveries </div><div class="vTableCellRight"><?php echo $successful_deliveries; echo ' ('; echo $percentage_successful_deliveries; echo ')'; ?> </div>
</div>
<div class="vTableCell">
<div class="vTableCellLeft">Click per unique opens </div><div class="vTableCellRight"><?php echo $click_per_unique_opens; ?> </div>
</div>
</div>
<div class="vTableRow">
<div class="vTableCell">
<div class="vTableCellLeft">Total opens </div><div class="vTableCellRight"><?php echo $opens_total; ?> </div>
</div>
<div class="vTableCell">
<div class="vTableCellLeft">Total clicks </div><div class="vTableCellRight"><?php echo $clicks_total; ?> </div>
</div>
</div>
<div class="vTableRow">
<div class="vTableCell">
<div class="vTableCellLeft">Last opened </div><div class="vTableCellRight"><?php echo $last_open; ?> </div>
</div>
<div class="vTableCell">
<div class="vTableCellLeft">Last clicked </div><div class="vTableCellRight"><?php echo $last_click; ?> </div>
</div>
</div> <div class="vTableRow">
<div class="vTableCell">
<div class="vTableCellLeft">Forwarded </div><div class="vTableCellRight"><?php echo $forwards_count; ?> </div>
</div>
<div class="vTableCell">
<div class="vTableCellLeft">Abuse reports </div><div class="vTableCellRight"><?php echo $abuse_reports; ?> </div>
</div>
</div>
</div>
