Client Product Purchase Lapsed

Client Product Purchase Lapsed

This article is to show how to find clients that purchased a particular product (or product category) in a one period but have not purchased again in another. Eg. Which Client purchased pet food in first 6 months of the year but have not purchased it again in the second 6 months of the year.

STEP 1: CREATE EXPORT TEMPLATE

  1. Go to Reports -> Export Spreadsheets
  2. Click Add New and then Yes to design new Report
  3. Fill in Report Properties
    1. Name: As required. E.g. Clients Product Purchases
    2. Category: Usually under Client
    3. Printer: Reports1
    4. Table Link: Cln->Bil->Anm
    5. Use Following Tables: Tick Clients, Client addresses, Client Contact, Bill, Bill Items, Products.
    6. One Entry Per: Set to Client
    7. Leave other setting as defaults
  4. Save and Exit.


STEP 2: RUNNING EXPORTS

1: Export for Clients Who Purchased Item in Period 1

  1. Go to Reports -> Export Spreadsheets
  2. Select Category you have saved export under
  3. Double click Export Name. E.g. Client Product Purchases
  4. The filters page will open. Select filters as required, eg. Bill Dates from 1/1/2019 to 30/6/2019 and Product Category
  5. Click Next and Details for Export File will open
  6. Fill in Export Settings. Depending on preference on combo can be used but standard setting are;
    1. Fieldnames in File: Tick
    2. Surrounds fields with: Double Quote
    3. Separate Fields with: Comma
    4. Folder: Location to save file.
    5. FileName: Filename to save file as (no extension required, it will save as CSV).
  7. Click Export to Export file. 
 

2: Export for Clients Who Purchased Item in Period 2

  1. Go to Reports -> Export Spreadsheets
  2. Select Category you have saved export under
  3. Double click Export Name. E.g. Client Product Purchases
  4. The filters page will open. Select filters as required, eg. Bill Dates from 1/7/2019 to 31/12/2019 and Product Category
  5. Click Next and Details for Export File will open
  6. Fill in Export Settings. Depending on preference on combo can be used but standard setting are;
    1. Fieldnames in File: Tick
    2. Surrounds fields with: Double Quote
    3. Separate Fields with: Comma
    4. Folder: Location to save file.
    5. FileName: Filename to save file as (no extension required, it will save as CSV).
  7. Click Export to Export file. 

   STEP 3: USE EXCEL TO FIND CLIENTS WHO HAVE NOT BROUGHT ITEM/S AGAIN

  1. Open both files in excel.
  2. Copy everything from "clients who purchased item in period 2" workbook to new worksheet in "Clients who purchased item in period 1" workbook.
  3. Remove columns from both worksheets that are not needed to make it easier to manage. Make sure you leave the Client Number field.
  4. On "Clients who purchased item in period 1" worksheet add a new column to the end called Bought Item Again.
  5. Use Vlookup function to see if client is also on the clients who purchased item in period 2 worksheet.
    1. Eg. =VLOOKUP(A2,'Purchases in Period 2'!A:K,1,0)
  6. Copy formula down entire column
  7. Sort the Bought Item Again column alphabetically.
  8. If there is a client number then the client has bought the item again in second period, if the value is #N/A the client has not bought the item again.