Get FedEx Shipping Rates using FedEx Web Services
Steps involved:
1. go to fedex.com/developer, and create a test account, download the web services.
2. Create a production account (by putting all the credit card details) and go to test account, goto my profile-manage account.
Add the details of Production account to this account. An auth email will be sent to the admin of production account, if the admin authorizes, you will get an email, follow it and input the required details.
login to fedex.com/developer nd request for app credentials, The password and some details will be displayed on the browser, save this for future use, the account number and other details will be sent to the email, you need these too for the web service call.
3. Open the 'RateWebServiceClient.php' and check the require and wsdl paths
require_once('../../library/fedex-common.php');
//The WSDL is not included with the sample code. //Please include and reference in $path_to_wsdl variable.
//$path_to_wsdl = "../../wsdl/RateService_v18.wsdl";
$path_to_wsdl = "RateService_v18.wsdl";
4. goto library/fedex-common.php and put the values (All Production Values) inside function getProperty()
if($var == 'key') Return 'XXXX';
if($var == 'password') Return 'XXXX';
if($var == 'shipaccount') Return 'account no';
if($var == 'billaccount') Return 'account no';
5. Now as we are running with production account (to get the correct shipping rates) modify the last line of wsdl file to:
<s1:address location="https://ws.fedex.com:443/web-services/rate"/>
Here are the 2 files:
1. fedex-common.php:
<?php
// Copyright 2009, FedEx Corporation. All rights reserved.
/**
* Print SOAP request and response
*/
define('Newline',"<br />");
function printSuccess($client, $response) {
printReply($client, $response);
}
function printReply($client, $response){
$highestSeverity=$response->HighestSeverity;
if($highestSeverity=="SUCCESS"){echo '<h2>The transaction was successful.</h2>';}
if($highestSeverity=="WARNING"){echo '<h2>The transaction returned a warning.</h2>';}
if($highestSeverity=="ERROR"){echo '<h2>The transaction returned an Error.</h2>';}
if($highestSeverity=="FAILURE"){echo '<h2>The transaction returned a Failure.</h2>';}
echo "\n";
printNotifications($response -> Notifications);
printRequestResponse($client, $response);
}
function printRequestResponse($client){
echo '<h2>Request</h2>' . "\n";
echo '<pre>' . htmlspecialchars($client->__getLastRequest()). '</pre>';
echo "\n";
echo '<h2>Response</h2>'. "\n";
echo '<pre>' . htmlspecialchars($client->__getLastResponse()). '</pre>';
echo "\n";
}
/**
* Print SOAP Fault
*/
function printFault($exception, $client) {
echo '<h2>Fault</h2>' . "<br>\n";
echo "<b>Code:</b>{$exception->faultcode}<br>\n";
echo "<b>String:</b>{$exception->faultstring}<br>\n";
writeToLog($client);
echo '<h2>Request</h2>' . "\n";
echo '<pre>' . htmlspecialchars($client->__getLastRequest()). '</pre>';
echo "\n";
}
/**
* SOAP request/response logging to a file
*/
function writeToLog($client){
/**
* __DIR__ refers to the directory path of the library file.
* This location is not relative based on Include/Require.
*/
if (!$logfile = fopen(__DIR__.'/fedextransactions.log', "a"))
{
error_func("Cannot open " . __DIR__.'/fedextransactions.log' . " file.\n", 0);
exit(1);
}
fwrite($logfile, sprintf("\r%s:- %s",date("D M j G:i:s T Y"), $client->__getLastRequest(). "\r\n" . $client->__getLastResponse()."\r\n\r\n"));
}
/**
* This section provides a convenient place to setup many commonly used variables
* needed for the php sample code to function.
*/
function getProperty($var){
if($var == 'key') Return 'XXXX';
if($var == 'password') Return 'XXX';
if($var == 'shipaccount') Return 'acc no';
if($var == 'billaccount') Return 'acc no';
if($var == 'dutyaccount') Return 'XXX';
if($var == 'freightaccount') Return 'XXX';
if($var == 'trackaccount') Return 'XXX';
if($var == 'dutiesaccount') Return 'XXX';
if($var == 'importeraccount') Return 'XXX';
if($var == 'brokeraccount') Return 'XXX';
if($var == 'distributionaccount') Return 'XXX';
if($var == 'locationid') Return 'PLBA';
if($var == 'printlabels') Return false;
if($var == 'printdocuments') Return true;
if($var == 'packagecount') Return '4';
if($var == 'meter') Return 'XXX';
if($var == 'shiptimestamp') Return mktime(10, 0, 0, date("m"), date("d")+1, date("Y"));
if($var == 'spodshipdate') Return '2014-07-21';
if($var == 'serviceshipdate') Return '2017-07-26';
if($var == 'readydate') Return '2014-07-09T08:44:07';
//if($var == 'closedate') Return date("Y-m-d");
if($var == 'closedate') Return '2014-07-17';
if($var == 'pickupdate') Return date("Y-m-d", mktime(8, 0, 0, date("m") , date("d")+1, date("Y")));
if($var == 'pickuptimestamp') Return mktime(8, 0, 0, date("m") , date("d")+1, date("Y"));
if($var == 'pickuplocationid') Return 'XXX';
if($var == 'pickupconfirmationnumber') Return '1';
if($var == 'dispatchdate') Return date("Y-m-d", mktime(8, 0, 0, date("m") , date("d")+1, date("Y")));
if($var == 'dispatchlocationid') Return 'XXX';
if($var == 'dispatchconfirmationnumber') Return '1';
if($var == 'tag_readytimestamp') Return mktime(10, 0, 0, date("m"), date("d")+1, date("Y"));
if($var == 'tag_latesttimestamp') Return mktime(20, 0, 0, date("m"), date("d")+1, date("Y"));
if($var == 'expirationdate') Return date("Y-m-d", mktime(8, 0, 0, date("m"), date("d")+15, date("Y")));
if($var == 'begindate') Return '2014-07-22';
if($var == 'enddate') Return '2014-07-25';
if($var == 'trackingnumber') Return 'XXX';
if($var == 'hubid') Return '5531';
if($var == 'jobid') Return 'XXX';
if($var == 'searchlocationphonenumber') Return '5555555555';
if($var == 'customerreference') Return 'Cust_Reference';
if($var == 'shipper') Return array(
'Contact' => array(
'PersonName' => 'Sender Name',
'CompanyName' => 'Sender ABC Name',
'PhoneNumber' => '1234567890'
),
'Address' => array(
'StreetLines' => array('1202 Chalet Ln'),
'City' => 'Harrison',
'StateOrProvinceCode' => 'AR',
'PostalCode' => '72601',
'CountryCode' => 'US',
'Residential' => 1
)
);
if($var == 'recipient') Return array(
'Contact' => array(
'PersonName' => 'Recipient Name',
'CompanyName' => 'XYZ Recipient Company Name',
'PhoneNumber' => '1234567890'
),
'Address' => array(
'StreetLines' => array('2000 Freight LTL Testing'),
'City' => 'Harrison',
'StateOrProvinceCode' => 'AR',
'PostalCode' => '72601',
'CountryCode' => 'US',
'Residential' => 1
)
);
if($var == 'address1') Return array(
'StreetLines' => array('10 Fed Ex Pkwy'),
'City' => 'Memphis',
'StateOrProvinceCode' => 'TN',
'PostalCode' => '38115',
'CountryCode' => 'US'
);
if($var == 'address2') Return array(
'StreetLines' => array('13450 Farmcrest Ct'),
'City' => 'Herndon',
'StateOrProvinceCode' => 'VA',
'PostalCode' => '20171',
'CountryCode' => 'US'
);
if($var == 'searchlocationsaddress') Return array(
'StreetLines'=> array('240 Central Park S'),
'City'=>'Austin',
'StateOrProvinceCode'=>'TX',
'PostalCode'=>'78701',
'CountryCode'=>'US'
);
if($var == 'shippingchargespayment') Return array(
'PaymentType' => 'SENDER',
'Payor' => array(
'ResponsibleParty' => array(
'AccountNumber' => getProperty('billaccount'),
'Contact' => null,
'Address' => array('CountryCode' => 'US')
)
)
);
if($var == 'freightbilling') Return array(
'Contact'=>array(
'ContactId' => 'freight1',
'PersonName' => 'Big Shipper',
'Title' => 'Manager',
'CompanyName' => 'Freight Shipper Co',
'PhoneNumber' => '1234567890'
),
'Address'=>array(
'StreetLines'=>array(
'1202 Chalet Ln',
'Do Not Delete - Test Account'
),
'City' =>'Harrison',
'StateOrProvinceCode' => 'AR',
'PostalCode' => '72601-6353',
'CountryCode' => 'US'
)
);
}
function setEndpoint($var){
if($var == 'changeEndpoint') Return false;
if($var == 'endpoint') Return 'XXX';
}
function printNotifications($notes){
foreach($notes as $noteKey => $note){
if(is_string($note)){
echo $noteKey . ': ' . $note . Newline;
}
else{
printNotifications($note);
}
}
echo Newline;
}
function printError($client, $response){
printReply($client, $response);
}
function trackDetails($details, $spacer){
foreach($details as $key => $value){
if(is_array($value) || is_object($value)){
$newSpacer = $spacer. ' ';
echo '<tr><td>'. $spacer . $key.'</td><td> </td></tr>';
trackDetails($value, $newSpacer);
}elseif(empty($value)){
echo '<tr><td>'.$spacer. $key .'</td><td>'.$value.'</td></tr>';
}else{
echo '<tr><td>'.$spacer. $key .'</td><td>'.$value.'</td></tr>';
}
}
}
?>
getRates.php
<form action="" method="POST">
<table>
<tr><td>Packaging Type </td><td>
<select name="packagingType">
<option value="FEDEX_BOX">FEDEX BOX</option>
<option value="FEDEX_PAK">FEDEX PAK</option>
<option value="FEDEX_TUBE">FEDEX TUBE</option>
<option value="YOUR_PACKAGING">YOUR PACKAGING</option>
</select>
</td></tr>
<tr><td>Destination City: </td><td><input type="text" name="city"></td></tr>
<tr><td>Destination State: </td><td><input type="text" name="state"></td></tr>
<tr><td>Destination Zip Code: </td><td><input type="text" name="zip"></td></tr>
<tr><td>Weight (lbs): </td><td><input type="text" name="weight"></td></tr>
<tr><td>Length (inches): </td><td><input type="text" name="length"></td></tr>
<tr><td>Width (inches): </td><td><input type="text" name="width"></td></tr>
<tr><td>Height (inches): </td><td><input type="text" name="height"></td></tr>
<tr><td colspan="2"><input type="submit" name="submit" value="Submit"></td></tr>
</table>
</form>
<?php
// Copyright 2009, FedEx Corporation. All rights reserved.
// Version 12.0.0
function addShipper(){
$shipper = array(
'Contact' => array(
'PersonName' => 'Test',
'CompanyName' => 'Sender Company Name',
'PhoneNumber' => '9012638716'
),
'Address' => array(
'StreetLines' => array('7729 Lochlin Dr'),
'City' => 'Brighton',
'StateOrProvinceCode' => 'MI',
'PostalCode' => '48116',
'CountryCode' => 'US'
)
);
return $shipper;
}
function addRecipient($recipient){
$recipient = array(
'Contact' => array(
'PersonName' => 'Recipient Name',
'CompanyName' => 'Company Name',
'PhoneNumber' => '9012637906'
),
'Address' => array(
'StreetLines' => array('Address Line 1'),
'City' => $recipient['city'],
'StateOrProvinceCode' => $recipient['state'],
//'PostalCode' => 'V7C4V4',
'PostalCode' => $recipient['zip'],
'CountryCode' => 'US',
'Residential' => false
)
);
return $recipient;
}
function addShippingChargesPayment(){
$shippingChargesPayment = array(
'PaymentType' => 'SENDER', // valid values RECIPIENT, SENDER and THIRD_PARTY
'Payor' => array(
'ResponsibleParty' => array(
'AccountNumber' => getProperty('billaccount'),
'CountryCode' => 'US'
)
)
);
return $shippingChargesPayment;
}
function addLabelSpecification(){
$labelSpecification = array(
'LabelFormatType' => 'COMMON2D', // valid values COMMON2D, LABEL_DATA_ONLY
'ImageType' => 'PDF', // valid values DPL, EPL2, PDF, ZPLII and PNG
'LabelStockType' => 'PAPER_7X4.75'
);
return $labelSpecification;
}
function addSpecialServices(){
$specialServices = array(
'SpecialServiceTypes' => array('COD'),
'CodDetail' => array(
'CodCollectionAmount' => array(
'Currency' => 'USD',
'Amount' => 150
),
'CollectionType' => 'ANY' // ANY, GUARANTEED_FUNDS
)
);
return $specialServices;
}
function addPackageLineItem1($weight,$length,$width,$height){
$packageLineItem = array(
'SequenceNumber'=>1,
'GroupPackageCount'=>1,
'Weight' => array(
'Value' => $weight,
'Units' => 'LB'
),
'Dimensions' => array(
'Length' => $length,
'Width' => $width,
'Height' => $height,
'Units' => 'IN'
)
);
return $packageLineItem;
}
if(isset($_REQUEST['submit'])){
require_once('../../library/fedex-common.php');
echo '<pre>'; print_r($_REQUEST);
$packagingType = $_REQUEST['packagingType'];
$city = $_REQUEST['city'];
$state = $_REQUEST['state'];
$zip = $_REQUEST['zip'];
$weight = $_REQUEST['weight'];
$length = $_REQUEST['length'];
$width = $_REQUEST['width'];
$height = $_REQUEST['height'];
$recipient['city'] = $city;
$recipient['state'] = $state;
$recipient['zip'] = $zip;
/*
$weight = '2';
$length = '2';
$width = '2';
$height = '3';
*/
$newline = "<br />";
$path_to_wsdl = "RateService_v18.wsdl";
ini_set("soap.wsdl_cache_enabled", "0");
$client = new SoapClient($path_to_wsdl, array('trace' => 1)); // Refer to http://us3.php.net/manual/en/ref.soap.php for more information
$request['WebAuthenticationDetail'] = array(
'ParentCredential' => array(
'Key' => getProperty('parentkey'),
'Password' => getProperty('parentpassword')
),
'UserCredential' => array(
'Key' => getProperty('key'),
'Password' => getProperty('password')
)
);
$request['ClientDetail'] = array(
'AccountNumber' => getProperty('shipaccount'),
'MeterNumber' => getProperty('meter')
);
$request['TransactionDetail'] = array('CustomerTransactionId' => ' *** Rate Request using PHP ***');
$request['Version'] = array(
'ServiceId' => 'crs',
'Major' => '18',
'Intermediate' => '0',
'Minor' => '0'
);
$request['ReturnTransitAndCommit'] = true;
$request['RequestedShipment']['DropoffType'] = 'REGULAR_PICKUP'; // valid values REGULAR_PICKUP, REQUEST_COURIER, ...
$request['RequestedShipment']['ShipTimestamp'] = date('c');
//$request['RequestedShipment']['ServiceType'] = $serviceType;// SPECIFY IT IF YOU WANT A PARTICULAR SERVICETYPE eg'INTERNATIONAL_PRIORITY'; // valid values STANDARD_OVERNIGHT, PRIORITY_OVERNIGHT, FEDEX_GROUND, ...
$request['RequestedShipment']['PackagingType'] = $packagingType; // valid values FEDEX_BOX, FEDEX_PAK, FEDEX_TUBE, YOUR_PACKAGING, ...
/*$request['RequestedShipment']['TotalInsuredValue']=array(
'Ammount'=>100,
'Currency'=>'USD'
);*/
$request['RequestedShipment']['Shipper'] = addShipper();
//$request['RequestedShipment']['Shipper'] = add();
$request['RequestedShipment']['Recipient'] = addRecipient($recipient);
$request['RequestedShipment']['ShippingChargesPayment'] = addShippingChargesPayment();
$request['RequestedShipment']['PackageCount'] = '1';
$request['RequestedShipment']['RequestedPackageLineItems'] = addPackageLineItem1($weight,$length,$width,$height);
try {
if(setEndpoint('changeEndpoint')){
$newLocation = $client->__setLocation(setEndpoint('endpoint'));
}
$response = $client -> getRates($request);
if ($response -> HighestSeverity != 'FAILURE' && $response -> HighestSeverity != 'ERROR'){
$rateReplyArr = $response -> RateReplyDetails;
$count = count($rateReplyArr);
echo '<table border="1">';
echo '<tr><td>Service Type</td><td>Amount</td><td>Delivery Date</td></tr>';
for($i=0;$i<$count;$i++){
$rateReply = $rateReplyArr[$i];
//echo '<pre>'; print_r($rateReply);
$serviceType = '<tr><td>'.$rateReply -> ServiceType . '</td>';
if($rateReply->RatedShipmentDetails && is_array($rateReply->RatedShipmentDetails)){
$amount = '<td>$' . number_format($rateReply->RatedShipmentDetails[0]->ShipmentRateDetail->TotalNetCharge->Amount,2,".",",") . '</td>';
}elseif($rateReply->RatedShipmentDetails && ! is_array($rateReply->RatedShipmentDetails)){
$amount = '<td>$' . number_format($rateReply->RatedShipmentDetails->ShipmentRateDetail->TotalNetCharge->Amount,2,".",",") . '</td>';
}
if(array_key_exists('DeliveryTimestamp',$rateReply)){
$deliveryDate= '<td>' . $rateReply->DeliveryTimestamp . '</td>';
}else if(array_key_exists('TransitTime',$rateReply)){
$deliveryDate= '<td>' . $rateReply->TransitTime . '</td>';
}else {
$deliveryDate='<td> </td>';
}
echo $serviceType . $amount. $deliveryDate;
echo '</tr>';
}echo '</table>';//count ends
printSuccess($client, $response);
}else{
printError($client, $response);
}
writeToLog($client); // Write to log file
} catch (SoapFault $exception) {
printFault($exception, $client);
}
echo '<pre>';// print_r($request);
print_r($response);// die("ABCD");
}
?>
Run the getRates.php, if you get any error message, just google it!
Friday, August 5, 2016
Monday, August 1, 2016
GET THE EMAIL IDs OF ALL MEMBERS THAT HAVE UNSUBSCRIBED TO MAILCHIMP IN THE LAST N DAYS AND UPDATE OUR DATABASE ACCORDINGLY
GET THE EMAIL IDs OF ALL MEMBERS THAT HAVE UNSUBSCRIBED TO MAILCHIMP IN THE LAST N DAYS AND UPDATE OUR DATABASE ACCORDINGLY
<?php
require_once("includes/db.php");
$db = new DB();
$database = "XXXX";
$list_id = 'XXX';
$unsubscribed_members = getList();
$json_array = getJson($unsubscribed_members);
$get_status = check_status($json_array);
echo '<pre>'; //print_r($json_array);
print_r($get_status);
function check_status($array_resp){
//echo '<pre>'; print_r($array_resp);
$message[0] = 'Updated on: '.date("F j, Y, g:i a");
$message[1] = "Updates for Unsubscribed Members ";
$count = count($array_resp['members']);
for($i=0;$i<$count;$i++){
$member = $array_resp['members'][$i];
$email = $member['email_address'];
$status = $member['status'];
$last_changed = $member['last_changed'];
if($status=='unsubscribed'){
$unsubscription_status = 0;
$message[] = updateDatabase($email, $last_changed);
}
}
return $message;
}
function getList(){
global $list_id, $MCAPI;
$username = "XXX";
$password = "XXXX";
$api_key = 'XXXXXX-us3';
$dateSince= date('Y-m-d h:i:s',strtotime("-2 days"));
$api = array
(
'login' => $username,
'key' => $api_key,
'url' => 'https://us3.api.mailchimp.com/3.0/lists/'.$list_id.'/members/?count=50&status=unsubscribed&since_last_changed='.$dateSince
);
//echo '<pre>'; print_r($api);
$result = mc_request( $api,'GET','','');
return $result;
}
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;
}
function getJson($subscribed_members){
$resp = substr(strstr($subscribed_members, '{'), strlen('{'));
$js_result = '{'.$resp;
$jres = json_decode($js_result,true);
return $jres;
}
function updateDatabase($email, $timestamp_opt){
global $database, $db;
$sql_check_unsubscribers = "SELECT enabled FROM $database.subscribe_email WHERE email = '$email'";
$enabled = $db->Query($sql_check_unsubscribers);
if(isset($enabled)&&($enabled!='')){
if($enabled==0)
$message = "Member with email id '$email' is already Unsubscribed! ";
else{
$sql_update_unsubscribers = "UPDATE $database.subscribe_email SET enabled=0
WHERE email = '$email'";
$upd = $db->Command($sql_update_unsubscribers);
$message = "Member with email id '$email' Unsubscribed on: '$timestamp_opt', Updated in table '$upd'";
}
}else{
//echo "NOT IN TABLE"; echo '<br>';
$sql_add_subscribers = "INSERT INTO $database.subscribe_email
(`enabled`,`typeId`,`email`,`date`)
VALUES('0','5','$email',NOW())";
$enabled = $db->Query($sql_add_subscribers);
$message = "Added member with email id '$email' as Unsubscribed! ";
}
return $message;
}
?>
<?php
require_once("includes/db.php");
$db = new DB();
$database = "XXXX";
$list_id = 'XXX';
$unsubscribed_members = getList();
$json_array = getJson($unsubscribed_members);
$get_status = check_status($json_array);
echo '<pre>'; //print_r($json_array);
print_r($get_status);
function check_status($array_resp){
//echo '<pre>'; print_r($array_resp);
$message[0] = 'Updated on: '.date("F j, Y, g:i a");
$message[1] = "Updates for Unsubscribed Members ";
$count = count($array_resp['members']);
for($i=0;$i<$count;$i++){
$member = $array_resp['members'][$i];
$email = $member['email_address'];
$status = $member['status'];
$last_changed = $member['last_changed'];
if($status=='unsubscribed'){
$unsubscription_status = 0;
$message[] = updateDatabase($email, $last_changed);
}
}
return $message;
}
function getList(){
global $list_id, $MCAPI;
$username = "XXX";
$password = "XXXX";
$api_key = 'XXXXXX-us3';
$dateSince= date('Y-m-d h:i:s',strtotime("-2 days"));
$api = array
(
'login' => $username,
'key' => $api_key,
'url' => 'https://us3.api.mailchimp.com/3.0/lists/'.$list_id.'/members/?count=50&status=unsubscribed&since_last_changed='.$dateSince
);
//echo '<pre>'; print_r($api);
$result = mc_request( $api,'GET','','');
return $result;
}
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;
}
function getJson($subscribed_members){
$resp = substr(strstr($subscribed_members, '{'), strlen('{'));
$js_result = '{'.$resp;
$jres = json_decode($js_result,true);
return $jres;
}
function updateDatabase($email, $timestamp_opt){
global $database, $db;
$sql_check_unsubscribers = "SELECT enabled FROM $database.subscribe_email WHERE email = '$email'";
$enabled = $db->Query($sql_check_unsubscribers);
if(isset($enabled)&&($enabled!='')){
if($enabled==0)
$message = "Member with email id '$email' is already Unsubscribed! ";
else{
$sql_update_unsubscribers = "UPDATE $database.subscribe_email SET enabled=0
WHERE email = '$email'";
$upd = $db->Command($sql_update_unsubscribers);
$message = "Member with email id '$email' Unsubscribed on: '$timestamp_opt', Updated in table '$upd'";
}
}else{
//echo "NOT IN TABLE"; echo '<br>';
$sql_add_subscribers = "INSERT INTO $database.subscribe_email
(`enabled`,`typeId`,`email`,`date`)
VALUES('0','5','$email',NOW())";
$enabled = $db->Query($sql_add_subscribers);
$message = "Added member with email id '$email' as Unsubscribed! ";
}
return $message;
}
?>
GET THE EMAIL IDs OF ALL MEMBERS THAT HAVE SUBSCRIBED TO MAILCHIMP IN THE LAST N DAYS AND UPDATE OUR DATABSE ACCORDINGLY
GET THE EMAIL IDs OF ALL MEMBERS THAT HAVE SUBSCRIBED TO MAILCHIMP IN THE LAST N DAYS AND UPDATE OUR DATABSE ACCORDINGLY
<?php
require_once("includes/db.php");
$db = new DB();
$database = "XXX";
$list_id = 'XXXXX';
$subscribed_members = getList();
$json_array = getJson($subscribed_members);
$get_status = check_status($json_array);
echo '<pre>'; print_r($get_status);
function check_status($array_resp){
//echo '<pre>'; print_r($array_resp);
$message[0] = 'Updated on: '.date("F j, Y, g:i a");
$message[1] = "Updates for Subscribed Members ";
$count = count($array_resp['members']);
for($i=0;$i<$count;$i++){
$member = $array_resp['members'][$i];
$email = $member['email_address'];
$status = $member['status'];
$timestamp_opt = $member['timestamp_opt'];
if($status=='subscribed'){
$subscription_status = 1;
$message[] = updateDatabase($email,$subscription_status, $timestamp_opt);
}
}
return $message;
}
function getList(){
global $list_id, $MCAPI;
$username = "XXXX";
$password = "XXXXX";
$api_key = 'XXXXXX-us3';
$dateSince= date('Y-m-d h:i:s',strtotime("-2 days"));
$api = array
(
'login' => $username,
'key' => $api_key,
'url' => 'https://us3.api.mailchimp.com/3.0/lists/'.$list_id.'/members/?count=100&status=subscribed&since_timestamp_opt='.$dateSince
);
//echo '<pre>'; print_r($api);
$result = mc_request( $api,'GET','','');
return $result;
}
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);
//$httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close( $ch );
return $response;
}
function getJson($subscribed_members){
//echo $subscribed_members;
$resp = substr(strstr($subscribed_members, '{'), strlen('{'));
$js_result = '{'.$resp;
$jres = json_decode($js_result,true);
//echo '<pre>'; print_r($jres); die('AAA');
return $jres;
}
function updateDatabase($email,$subscription_status, $timestamp_opt){
global $database, $db;
$sql_check_subscribers = "SELECT * FROM $database.subscribe_email WHERE email = '$email'";
$enabled = $db->getTable($sql_check_subscribers);
if(isset($enabled[0])&&($enabled[0]!='')){
$enabled_val = $enabled[0]['enabled'];
if($enabled_val==1)
$message = "Member with email id '$email' with Opted In Date: '$timestamp_opt' is already Subscribed! ";
else{
$sql_update_subscribers = "UPDATE $database.subscribe_email SET enabled=1
WHERE email = '$email'";
$upd = $db->Command($sql_update_subscribers);
$message = "Updated '$email' with Opted In Date: '$timestamp_opt' in table '$upd' ";
}
}else{
//echo "NOT IN TABLE"; echo '<br>';
$sql_add_subscribers = "INSERT INTO $database.subscribe_email(`enabled`,`typeId`,`email`,`date`)
VALUES('1','5','$email',NOW())";
$enabled = $db->Query($sql_add_subscribers);
$message = "Added member with email id '$email' as Subscribed! ";
}
return $message;
}
?>
<?php
require_once("includes/db.php");
$db = new DB();
$database = "XXX";
$list_id = 'XXXXX';
$subscribed_members = getList();
$json_array = getJson($subscribed_members);
$get_status = check_status($json_array);
echo '<pre>'; print_r($get_status);
function check_status($array_resp){
//echo '<pre>'; print_r($array_resp);
$message[0] = 'Updated on: '.date("F j, Y, g:i a");
$message[1] = "Updates for Subscribed Members ";
$count = count($array_resp['members']);
for($i=0;$i<$count;$i++){
$member = $array_resp['members'][$i];
$email = $member['email_address'];
$status = $member['status'];
$timestamp_opt = $member['timestamp_opt'];
if($status=='subscribed'){
$subscription_status = 1;
$message[] = updateDatabase($email,$subscription_status, $timestamp_opt);
}
}
return $message;
}
function getList(){
global $list_id, $MCAPI;
$username = "XXXX";
$password = "XXXXX";
$api_key = 'XXXXXX-us3';
$dateSince= date('Y-m-d h:i:s',strtotime("-2 days"));
$api = array
(
'login' => $username,
'key' => $api_key,
'url' => 'https://us3.api.mailchimp.com/3.0/lists/'.$list_id.'/members/?count=100&status=subscribed&since_timestamp_opt='.$dateSince
);
//echo '<pre>'; print_r($api);
$result = mc_request( $api,'GET','','');
return $result;
}
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);
//$httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close( $ch );
return $response;
}
function getJson($subscribed_members){
//echo $subscribed_members;
$resp = substr(strstr($subscribed_members, '{'), strlen('{'));
$js_result = '{'.$resp;
$jres = json_decode($js_result,true);
//echo '<pre>'; print_r($jres); die('AAA');
return $jres;
}
function updateDatabase($email,$subscription_status, $timestamp_opt){
global $database, $db;
$sql_check_subscribers = "SELECT * FROM $database.subscribe_email WHERE email = '$email'";
$enabled = $db->getTable($sql_check_subscribers);
if(isset($enabled[0])&&($enabled[0]!='')){
$enabled_val = $enabled[0]['enabled'];
if($enabled_val==1)
$message = "Member with email id '$email' with Opted In Date: '$timestamp_opt' is already Subscribed! ";
else{
$sql_update_subscribers = "UPDATE $database.subscribe_email SET enabled=1
WHERE email = '$email'";
$upd = $db->Command($sql_update_subscribers);
$message = "Updated '$email' with Opted In Date: '$timestamp_opt' in table '$upd' ";
}
}else{
//echo "NOT IN TABLE"; echo '<br>';
$sql_add_subscribers = "INSERT INTO $database.subscribe_email(`enabled`,`typeId`,`email`,`date`)
VALUES('1','5','$email',NOW())";
$enabled = $db->Query($sql_add_subscribers);
$message = "Added member with email id '$email' as Subscribed! ";
}
return $message;
}
?>
Thursday, July 28, 2016
GETTING REPORTS FROM MAILCHIMP
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>
Subscribe to:
Comments (Atom)