Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Archived

This topic is now archived and is closed to further replies.

Guest

Best Customers report KLUGE

Recommended Posts

Guest

Wow, I know who spent the most at my store... that's real unuseful. What is useful in knowing who my best customers are is not who had the biggest sales figure, but also how many times did they order from me. That one big order is nice, but a customer that places 10 orders and is just below that one big order is nicer and I really want to contact them and make them know how much I appreciate their CONTINUED business.

 

Kluge Changes made to: stats_customers.php

 

Headers Change:

 

Old:

 

<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_CUSTOMERS; ?></td>
<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TOTAL_PURCHASED; ?> </td>

 

New:

 

<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_CUSTOMERS; ?></td>
<td class="dataTableHeadingContent" align=right><?php echo "Order Count"; ?></td>
<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TOTAL_PURCHASED; ?> </td>

 

SQL Query Change:

 

Old:

 

 $customers_query_raw = "select c.customers_firstname, c.customers_lastname, sum(op.products_quantity * op.final_price) as ordersum from " . TABLE_CUSTOMERS . " c, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where c.customers_id = o.customers_id and o.orders_id = op.orders_id group by c.customers_firstname, c.customers_lastname order by ordersum DESC";

 

New:

 

$customers_query_raw = "select c.customers_firstname, c.customers_lastname, sum(op.products_quantity * op.final_price) as ordersum, c.customers_id as cid from " . TABLE_CUSTOMERS . " c, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where c.customers_id = o.customers_id and o.orders_id = op.orders_id group by c.customers_firstname, c.customers_lastname order by ordersum DESC";

 

New SQL Query:

 

Old:

 

 

  while ($customers = tep_db_fetch_array($customers_query)) {
   $rows++;

 

New:

 

  while ($customers = tep_db_fetch_array($customers_query)) {
 $customers_query_numords = tep_db_query("select orders_id from " . TABLE_ORDERS . " where customers_id ='".$customers['cid']."' ");
 $ocnt = tep_db_num_rows($customers_query_numords);
   $rows++;

 

Table Data Change:

 

Old:

 

 

<td class="dataTableContent"><?php echo $rows; ?>.</td>
<td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CUSTOMERS, 'search=' . $customers['customers_lastname'], 'NONSSL'). '">' . $customers['customers_firstname'] . ' ' . $customers['customers_lastname'] . '</a>'; ?></td>
<td class="dataTableContent" align="right"><?php echo $currencies->format($customers['ordersum']); ?> </td>

 

New:

 

<td class="dataTableContent"><?php echo $rows; ?>.</td>
<td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CUSTOMERS, 'search=' . $customers['customers_lastname'], 'NONSSL') . '">' . $customers['customers_firstname'] . ' ' . $customers['customers_lastname'] . '</a>'; ?></td>
<td class="dataTableContent" align=right><?php echo $ocnt; ?></td>
<td class="dataTableContent" align="right"><?php echo $currencies->format($customers['ordersum']); ?> </td>

 

That's it!

 

What this does is add a simple counter in the line letting you know the total number of orders that customer placed to get that total. Now you know that the $1000 total order from Jones was a single order whereas that $1000 total order from Smith was made up of 6 orders.

 

How is this useful?

 

I want to send an end-of-year GV for 2% of all sales to those top 20 orders, but only if they ordered at least three times. I want to reward my best customers. A "best customer" isn't that one-time big order, but someone who comes back to you as a consistant customer.

Share this post


Link to post
Share on other sites

Hallo Met00,

 

thanks... works very fine! The small things make live better :D

 

Now it would be very interesting to sort the columns.

 

In germany we say:

I you give somebody the small finger he takes your whole hand :)


Thanks Matthias

Share this post


Link to post
Share on other sites

A quick glance, it looks like you'd have to merge the $customers_query_numords query with the $customers_query_raw in order to be able to order this by the number of times ordered. (say that three times real fast).

 

If I get some time, I might have a go.


-------------------------------------------------------------------------------------------------------------------------

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Share this post


Link to post
Share on other sites
Guest
Now it would be very interesting to sort the columns.

SLAP :D

No, I agree, it would be very interesting. That would have made this a full scale contribution rather than a fast hack. :P

Share this post


Link to post
Share on other sites
Wow, I know who spent the most at my store... that's real unuseful. What is useful in knowing who my best customers are is not who had the biggest sales figure, but also how many times did they order from me. That one big order is nice, but a customer that places 10 orders and is just below that one big order is nicer and I really want to contact them and make them know how much I appreciate their CONTINUED business.

 

Kluge Changes made to: stats_customers.php

 

Headers Change:

 

Old:

 

<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_CUSTOMERS; ?></td>
<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TOTAL_PURCHASED; ?> </td>

 

New:

 

<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_CUSTOMERS; ?></td>
<td class="dataTableHeadingContent" align=right><?php echo "Order Count"; ?></td>
<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TOTAL_PURCHASED; ?> </td>

 

SQL Query Change:

 

Old:

 

 $customers_query_raw = "select c.customers_firstname, c.customers_lastname, sum(op.products_quantity * op.final_price) as ordersum from " . TABLE_CUSTOMERS . " c, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where c.customers_id = o.customers_id and o.orders_id = op.orders_id group by c.customers_firstname, c.customers_lastname order by ordersum DESC";

 

New:

 

$customers_query_raw = "select c.customers_firstname, c.customers_lastname, sum(op.products_quantity * op.final_price) as ordersum, c.customers_id as cid from " . TABLE_CUSTOMERS . " c, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where c.customers_id = o.customers_id and o.orders_id = op.orders_id group by c.customers_firstname, c.customers_lastname order by ordersum DESC";

 

New SQL Query:

 

Old:

 

 

 ?while ($customers = tep_db_fetch_array($customers_query)) {
? ?$rows++;

 

New:

 

 ?while ($customers = tep_db_fetch_array($customers_query)) {
?$customers_query_numords = tep_db_query("select orders_id from " . TABLE_ORDERS . " where customers_id ='".$customers['cid']."' ");
?$ocnt = tep_db_num_rows($customers_query_numords);
? ?$rows++;

 

Table Data Change:

 

Old:

 

 

<td class="dataTableContent"><?php echo $rows; ?>.</td>
<td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CUSTOMERS, 'search=' . $customers['customers_lastname'], 'NONSSL'). '">' . $customers['customers_firstname'] . ' ' . $customers['customers_lastname'] . '</a>'; ?></td>
<td class="dataTableContent" align="right"><?php echo $currencies->format($customers['ordersum']); ?> </td>

 

New:

 

<td class="dataTableContent"><?php echo $rows; ?>.</td>
<td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CUSTOMERS, 'search=' . $customers['customers_lastname'], 'NONSSL') . '">' . $customers['customers_firstname'] . ' ' . $customers['customers_lastname'] . '</a>'; ?></td>
<td class="dataTableContent" align=right><?php echo $ocnt; ?></td>
<td class="dataTableContent" align="right"><?php echo $currencies->format($customers['ordersum']); ?> </td>

 

That's it!

 

What this does is add a simple counter in the line letting you know the total number of orders that customer placed to get that total. Now you know that the $1000 total order from Jones was a single order whereas that $1000 total order from Smith was made up of 6 orders.

 

How is this useful?

 

I want to send an end-of-year GV for 2% of all sales to those top 20 orders, but only if they ordered at least three times. I want to reward my best customers. A "best customer" isn't that one-time big order, but someone who comes back to you as a consistant customer.

This is the contrib I was talking about

 

 

John M

 

I am the guy who pm'd you earlier this eve.


Always remember, we need patience, guidance and most of all understanding.

 

My Contributions

Share this post


Link to post
Share on other sites
Guest

I am looking for a way to see customers that never purchased anything from my site. They might not have left a cart hanging around or I already reset it. I see accounts with 1 or 0 logons who have never bought. I would like to easly identify them and contact and/or remove them. why have them around if they don't buy or come back?

 

Are you guys still working on this contrib? I like what is shows so far but would like all of these features as well. I searched the cotrib list for customer cleanup and came up empty.

 

Thanks,

Jeff

Share this post


Link to post
Share on other sites
I am looking for a way to see customers that never purchased anything from my site. They might not have left a cart hanging around or I already reset it. I see accounts with 1 or 0 logons who have never bought. I would like to easly identify them and contact and/or remove them. why have them around if they don't buy or come back?

 

Are you guys still working on this contrib? I like what is shows so far but would like all of these features as well. I searched the cotrib list for customer cleanup and came up empty.

 

Thanks,

Jeff

there is a constribution called inactive customer report.

there you can see all things and delete them.....

 

greetz john

Share this post


Link to post
Share on other sites
Guest

Great I am looking at it now, thank you for pointing me in the right direction.

 

 

Jeff

Share this post


Link to post
Share on other sites

×