I am trying to build a report that shows in this month or trend over time:
Number of New Retailer,
Number of New Consignment,
Number of Unsuccessful Consignment,
Number of Successful Consignment also counted as New Retailer. But I need to know Number of Successful Consignment to calculate converting rate over time.
Details of definition:
- How many NEW RETAILER? My definition of NEW RETAILER is first time order, and paid on the same order date (or paid within 7 days after order date)
- How many NEW CONSIGNMENT? My definition of NEW CONSIGNMENT is first time order, and not paid on the same order date (or paid later than 7 days after order date) (or the paid date column is empty also consider as new consignment)
- Over time, if the NEW CONSIGNMENT returns the goods (means they can not sell, and do not want to be my retailer), then consider as NOT SUCCESSFUL CONSIGNMENT.
- Over time, if NEW CONSIGNMENT paid money (after more than 7 days), and order 2nd time, the report as SUCCESSFUL CONSIGNMENT and become NEW RETAILER.
Attachment is the sample file.