Best Products Purchased - Drill down reporting

[trochia]

Hello, I am wondering if anyone new of a mod in which intead of while in admin, and using:

Reports > Products Purchased and seeing this, and then "drilling down" and getting a page that shows what the item is... (well, seems kind of redundant really...as we already know this info)

If ?? This could easily be modified instead to "view" the list of customers that did purchase instead please?

Just a thought for improving reporting.

Thank's!!

[pgmarshall]

You will need to do a rewrite of stats_products_purchased.php in catalog/admin/

Not sure how easy this would be since you would need to write a new query to find out who has purchsed what from your store. At the moment this page simply links to the product ...

Not much help ... but at least you know where to start ...

[trochia]

Yes, already knew where to start (lol)..that's where I am at today...and it's including one "heck of a lot of joins", do to it the way the cart works.

The other "project" on my todo list is to also break down and give better reporting for monthly sales also. As just totals into a csv/xls is fine...but really worthless overall. as it doesn't tell "whats hot" so to speak. I mean what really sold and when.

Breaking them down instead of just totals is what I am after.

The export as an xls I am trying to do, and I think I am going to have to just create a "temp table" is to then be able to track and flag...and just download anything NEW since last download.

The other thing on my "mental wish list" is for the mod of "recover carts" in which is implemented here in osCMax, from osCommerce (back in 2002) is from someone that also that is on osCMax.

osCommerce Community Add-Ons

I see now, that there's another "upgrade" to it, and I know ( I really doubt ) that's it's been upgraded into the most current osCMax release...and I know Michael has spent one heck of a lot of time on osCMax, but by the time I do all the translations and hacking to make things work...I'm really just spinning my wheels.

To me, ideally would to also have an "email" situation automatically set up...to let us say that we already know we have "this item" in "Recover carts"... but why not "auto-email" a person, (instantly, once they have LOGGED OUT) as they have taken the time to register, and start shopping etc...but did not complete the transaction, and we have the details of what and when they are after....to possibly even self generate a "coupon or discount" and send it to them.

I guess I am really just venting now....(laugh) as even though osCommerce was king, and osCMax took it up a level, the competition for "carts" today is enormous.

If I run across someone wanting a cart, depending on their needs...and intensity, I find it so much easier to just resell them a http://cs-cart.com cart, in which there is NO PHP hacking really involved, as the the vast majority of defaults are kept in the DB.

And others? Well, they want simple integration into an existing website....and I haven't dropped any $$$ on http://www.squirrelcart.com/ yet, but getting this cart into an existing site, is so much easier than what I keep trying to do.

Again, sorry for venting... just my opinions...as by the time I get a working model for someone (time donated free) of this I have way too much time in too many files....(I am laughing, just saying to myself to KNOW WHEN TO SAY WHEN) so to speak. By then? They're flustered...I'm flustered...etc then more time spent educating and answering questions on how it works....and why doesn't it do this..or do that.

Again, no cut intenionally for osCMax, but keeping on top...and falling behind...hurts.

[pgmarshall]

Have you looked at this mod?

Not tried it myself yet - but from the description it does look like it does some of what you were looking for the sales analysis ... install looks fairly simple as well.

regards,

[trochia]

PG, again...thx for the reply!! And the link. Have not seen it yet, but am there now. Once again, like in my above example, (where release date of 4/15), this one is just yesterday...(laugh) And for sure, not into osCMax release also.

I hadn't even been into osc since last week, when I got the request for better reporting. The one guy I did do this oscmax cart for, really is more interested in getting reports via xls format...and that is where my brain cells (which are pretty trashed at the moment...too much coffee for attention span now).

Almost TOO SIMPLE...(laugh)...and it'll give me a better grip on what/where I need to pull also.

Thank's again..

PS.. If I ever get finished with my report functions...I'll be posting them on the forums, but they'll all just be xls exports.

[pgmarshall]

Just tried install into OSCMAX - does NOT work out of the box - looking at the code now ... I too am keen to get some more detailed info from the dbase.

As you mentioned it could be easier to link excel straight into the dbase and run online queries for the data you need. May have a go at that this weekend.

Will post back if I get it working (and it is useful!)

EDIT - not on WAMP ... doesn't like the $_REQUEST['month'] bit - may work on proper server ...

Regards,

[trochia]

PG, again thx... And here's just one of the "some junk" I have so far. But I want to get it "drill down" further, and export xls also. I have seperate export xls now, but too clumbsy.

PS...Don't mind the multiple connects etc...as just pieces of the puzzle put together.

I really lack on "joins" myself, and in order to get the "recover cart" info...that is what is needed...

Hope this tidbit helps.... for me, courses is "products"

My ultimate goal, is to also have an email immediately sent to admin, details like phone, name product....the minute something is deposited in "recover carts"...and the user has left the site. As heck, they took the time to register, (didn't complete) and no one unless they have too much time on there hands...is looking at this info constantly...yet letting a "bird in the hand" escape, is a lost sale.

Where a simple phone call possibly to them right then and there....could close a sale...with a personal touch.

<center>Total Dollar Amount of sales<br />in cart to date per course</center>
<br />
<?php
// Make a MySQL Connection
include("tcon-ac.php");
// fetchSum.php
$query = "SELECT products_name, SUM(products_price) FROM orders_products GROUP BY products_name"; 
 
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
 echo "Total : ". $row['products_name']. " = $". $row['SUM(products_price)'];
 echo "<br />";
}
?>
<br />
<center>Total Count of Unique courses sold in cart to date</center>
<br />
<?php
// Make a MySQL Connection
include("tcon-ac.php");
// Fetch total count of courses sold by products_id
$query = "SELECT products_name, SUM(products_quantity) FROM orders_products GROUP BY products_name"; 
 
$result = mysql_query($query) or die(mysql_error());
// Print out results of SUM of products_quantity
while($row = mysql_fetch_array($result)){
 echo "Total of: ". $row['products_name']. " = ". $row['SUM(products_quantity)'];
 echo "<br />";
}
?>
<br /><br />
<?php
$result = mysql_query("SELECT * FROM orders");
   $numrows = mysql_num_rows($result);
   print "There have been $numrows Orders to date.\n";
?>
<?php
// Make a MySQL Connection
include("tcon-ac.php");
// Select the table to use
$query = "SELECT * FROM orders ORDER BY orders_id";
 
$result = mysql_query($query) or die(mysql_error());
 
echo "<table border='1'>";
echo "<tr>
<th>Order #</th> <th>Name</th> <th>Order Date-Time</th>
<th>Email</th> <th>Country</th>
</tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
 // Print out the contents of each row into a table
 echo "<tr><td>"; 
 echo $row['orders_id'];
 echo "</td><td>"; 
 echo $row['customers_name'];
 echo "</td><td>"; 
 echo $row['date_purchased'];
 echo "</td><td>";
 echo $row['customers_email_address'];
 echo "</td><td>";
 echo $row['customers_country'];
 echo "</td></tr>"; 
} 
echo "</table>";
exit();
?>