[HOW TO] Coupon Usage Report - PHP Script
Posted: Thu Feb 10, 2011 2:00 pm
All,
Here is a little script I created to show me all active coupons, the amount of times they have been used, the total dollar amount of the orders they were used on and the average order size.
<?php
$hostname_dbConnection = "yourshost";
$database_dbConnection = "yourdb";
$username_dbConnection = "youruser";
$password_dbConnection = "yourpw";
$dbConnection = mysql_connect($hostname_dbConnection, $username_dbConnection, $password_dbConnection) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db('yourdb');
$getcoupons = mysql_query("SELECT distinct(coupon_id) FROM `order`;");
while ($outputcoupons = mysql_fetch_array($getcoupons))
{
$coupon_id = $outputcoupons['coupon_id'];
$getcouponinfo = mysql_query("select * from coupon where coupon_id = $coupon_id") or die(mysql_error());
while($outputcouponinfo = mysql_fetch_array($getcouponinfo))
{
$code = $outputcouponinfo['code'];
$discount = $outputcouponinfo['discount'];
$total = $outputcouponinfo['total'];
$getcouponinfo1 = mysql_query("select * from order where coupon_id = $coupon_id") or die(mysql_error());
$uses = mysql_num_rows($getcouponinfo1);
$amountused = $uses * $discount;
$amountpercoupon = $amountused / $uses;
echo "
<span style='text-decoration:underline; font-weight:bold;'>$code</span><br>
Total Uses: $uses<br>
Sum of Total Orders: ";
$totalorderdollar = mysql_query("select sum(total) from order where coupon_id = $coupon_id") or die(mysql_error());
while($ouputdollarmaount = mysql_fetch_array($totalorderdollar))
{
$totalamount = number_format($ouputdollarmaount['sum(total)'], 2);
echo "$totalamount<br>";
}
$average = number_format($totalamount / $uses, 2);
echo "Average Order Amount: $average<hr>";
}
}
?>
Here is a little script I created to show me all active coupons, the amount of times they have been used, the total dollar amount of the orders they were used on and the average order size.
<?php
$hostname_dbConnection = "yourshost";
$database_dbConnection = "yourdb";
$username_dbConnection = "youruser";
$password_dbConnection = "yourpw";
$dbConnection = mysql_connect($hostname_dbConnection, $username_dbConnection, $password_dbConnection) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db('yourdb');
$getcoupons = mysql_query("SELECT distinct(coupon_id) FROM `order`;");
while ($outputcoupons = mysql_fetch_array($getcoupons))
{
$coupon_id = $outputcoupons['coupon_id'];
$getcouponinfo = mysql_query("select * from coupon where coupon_id = $coupon_id") or die(mysql_error());
while($outputcouponinfo = mysql_fetch_array($getcouponinfo))
{
$code = $outputcouponinfo['code'];
$discount = $outputcouponinfo['discount'];
$total = $outputcouponinfo['total'];
$getcouponinfo1 = mysql_query("select * from order where coupon_id = $coupon_id") or die(mysql_error());
$uses = mysql_num_rows($getcouponinfo1);
$amountused = $uses * $discount;
$amountpercoupon = $amountused / $uses;
echo "
<span style='text-decoration:underline; font-weight:bold;'>$code</span><br>
Total Uses: $uses<br>
Sum of Total Orders: ";
$totalorderdollar = mysql_query("select sum(total) from order where coupon_id = $coupon_id") or die(mysql_error());
while($ouputdollarmaount = mysql_fetch_array($totalorderdollar))
{
$totalamount = number_format($ouputdollarmaount['sum(total)'], 2);
echo "$totalamount<br>";
}
$average = number_format($totalamount / $uses, 2);
echo "Average Order Amount: $average<hr>";
}
}
?>