Best Customers report KLUGE

[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.

[Aalst]

Works great, love it... great idea!

[bodyfeelings]

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 :)

[wizardsandwars]

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.

[Aalst]

Now it would be very interesting to sort the columns.

SLAP :D

[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

[Paycheck]

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.

[poolie]

nice one ;)

[azer]

thanks , do u think to release it as a contribution ?

[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

[John Doswell]

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

[Guest]

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

 

 

Jeff