I have an excel file listing all customers to whom I sent a marketing message - daily I will now upload a transaction extract so I can measure the purchasing behaviour of these customers (what percentage of them made a purchase, etc.).
LOAD Company, Product, [Account No], [Posted Date], [Effective Date], [Store No], [Merchant Code], [Store Name], [Store Country], [Card No], [Transaction Type], [Tran Code], [Trans Desc], [Trans Amount], [Trans Auth] FROM (txt, codepage is 1252, embedded labels, delimiter is '~', no quotes, header is 1 lines)
WHERE ([Tran Code]=35 or [Tran Code]=36) and exists([Account No])
I have a few questions on this and will have many in the days to come... not sure if I should start a new post for each separate question, as I then have to explain the entire situation each time - please advise on this.
For now, the pertinent questions are:
I need to differentiate between purchases ([Tran Code] = 35) and purchase reversals ([Tran Code] = 36). In a summary boxes (or in any way) I need to show the amount of each, and when I determine the total turnover it must be calculated as Purchuses MINUS Reversals. Is the best way to do this to make a separate field of each? Please tell me how to do this, or any other way to do what I need.
I will be uploading the new, daily transaction file every day (or I can do a weekly file every Monday, but I want to practise on so long, as my first report is due on Monday). What I usually do for there reports (in excel) is to compare the same customers' purchasing behaviour BEFORE and AFTER the message was sent. So using vLookup and the transactions extracts, on Monday I would compare their behaviour after the message (11 April to 17 April) with before the message (4 April to 10 April), comparing the following:
Total Turnover from the group
Percentage of the customers (who got a message) that made a purchase during the period
Average purchase value,
etc.
I know this is a lot to ask, but any and all help will be appreciated.
