Zen Cart 360 Sales Reporting
For many Zen Cart users, the Best Customers report (Admin->Reports->Customer Orders-Total) gives sufficient data, especially when my Lifetime Customer Value modification is added.
However, it does not handle the following situations:
- Transactions which occur outside of the store (direct bank transfers and sales prior to the store being set up).
- Business Accounts which have multiple payers: if firstname.lastname@example.org and email@example.com both make payments, it's not immediately clear what the total value of the BigCorp account is.
360 Sales Reporting addresses both of these needs in the following way:
- Customer records are amended with a new field to identify a master customer id. In this way, customers can be grouped.
- Two tables are provided to handle both direct deposits by customers and customer transactions which occur outside the store.
Once 360 Sales Reporting is installed, entries on the Best Customers report change from this:
(The customer_id, company name and email address are added.)
The customers sidebar, as shown on the Admin->Customers->Customers page, is also modified from this:
The lifetime value is shown from the cart data for this customer, but also the Master Lifetime Value, which is the sum of:
- sales to this customer
- sales to other customers where this customer is the master account
- direct deposits by this customer
- sales to this customer prior to the store being set up
- deposits and pre-store sales by other email addresses which are linked to this email address.
Run the SQL files in the "sql" directory from Admin->Tools->Install SQL Patches.
Set the master account where needed for customers you wish to group together. For example, if customer 288 is the master account for customer 276, use the command
UPDATE customers SET master_account = 288 where customers_id = 276;
Install the files in the
Merge the files in the
If needed, import data to the
Importing Data from PayPal
Here are some recommendations for importing data from a PayPal export to import into the pre_store table:
Clean the data as follows to remove unwanted rows as follows: Sort the data by the following fields
- total, to find and remove remove withdrawls
- status, to find and remove refunds
- type, to find and remove currency conversions
remove all columns except Date, Name, Email, Gross
format Date as YYYY-MM-DD.
format Gross as number no comma
remove header, strip commas.
You will then be able to import the resultant file directly to the pre_store table. When importing Excel data to the pre_store table, follow these steps:
- Be sure the column order of your data matches the column order of the target table.
- From Excel, use File->Export->Change File Type, and set the file type to CSV, and save the file.
- Edit the resulting .csv file with a text editor like Notepad, and
0,to each row of your data.
- In PHPMyAdmin, click import, select file
- Set format to csv, set columns enclosed with to blank
- Check "Update data when duplicate keys found on import"