This report works like the "Special Searches->Customers->By Amount Spent" search, but it allows you to generate a report that actually shows you how much each customer spent.
Select "File->Custom Query Report" from the TRS menu. When you get to the big empty box, paste in the following:
Select custtimestamp,CUSTID,CUSTKEY,CUSTFNAME,
CUSTLNAME,CUSTCOMPANY,CUSTADDRESS,CUSTCITY,
CUSTSTATE,CUSTZIP,CUSTCOUNTRY,CUSTTYPE,CUSTPHONE1,
CUSTPHONE2,CUSTINTCLASS,CUSTNOTES,CUSTSPARE1,CUSTSPARE2,
CUSTDATE1,CUSTDATE2,CUSTEMAIL,CUSTTOTALVOLUME,CUSTTOTALMONEY,
CUSTBADCHECK,CUSTCREDITLIMIT,CUSTTERMS,CUSTPHONEUNF,
customer.STOREID,CUSTDISCOUNT,CUSTDISOUNTCEXPIRES,CUSTTAXEXEMPT
,CUSTTAXEXEMPT2,CUSTTAXEXEMPT3,CUSTINACTIVE,CUSTBDAY,CUSTPOPUPNOTES
,sum(postotal)
from customer inner join possales on customer.custkey=possales.custkey
where posdate >= '1/1/06' and posdate <= '8/31/09'
group by custtimestamp,CUSTID,CUSTKEY,CUSTFNAME,CUSTLNAME,CUSTADDRESS,
CUSTCOMPANY,CUSTCITY,CUSTSTATE,CUSTZIP,CUSTCOUNTRY,CUSTTYPE,CUSTPHONE1,
CUSTPHONE2,CUSTINTCLASS,CUSTNOTES,CUSTSPARE1,CUSTSPARE2,CUSTDATE1,
CUSTDATE2,CUSTEMAIL,CUSTTOTALVOLUME,CUSTTOTALMONEY,CUSTBADCHECK,
CUSTCREDITLIMIT,CUSTTERMS,CUSTPHONEUNF,customer.STOREID,CUSTDISCOUNT,
CUSTDISOUNTCEXPIRES,CUSTTAXEXEMPT,CUSTTAXEXEMPT2,CUSTTAXEXEMPT3,
CUSTINACTIVE,CUSTBDAY,CUSTPOPUPNOTES
having sum(postotal) > 1000 order by 1 descending
In the 11th line (the one that begins with "where"), change the two dates to match the date range that you are interested in. In the last line, change the "1000" to the desited threshhold of money spent (i.e. if you don't change it, it will find customers who have spent at least $1000 over the chosen time period).
Click "Execute".
On the next screen, check off the fields that you want to see on the report. The last field in the list ("SUM") is the field that will show how much money they have spent.
Please note that the amounts are based only on the total of each POS transaction. It will include things that would not normally apply toward accumulations (customer reward program), such as non-accumulating items, classes, discounted merchandise, gift cards, layaway/account payments, etc.
|
