This document describes how to export customer information into a file. The data can then be used to create marketing materials, sent to third party customer marketing companies and imported into Microsoft Excel.
From the StockTrac® main menu, go into the Customers module.
Once Customers is selected, click the Search Customer Data button as shown below.
The following screen will display:
This screen will list any previously created profiles, if applicable. Click the New button to create a new profile. The Customer Data Search screen will display.
Enter a title for this search. In the example above, the search is named “Exporting Customers”.
When creating a Customer Data Search, information will be entered in two different series of tabs.
The upper tabs are: Search For, Output, Options, and Fields.
The lower tabs are: Customers, Vehicles, Invoices, Services, Parts, Loyalty and Promotions.
The information entered on ALL tabs are used together to determine which customers are included in the search and how the information is outputted.
The lower tabs will be discussed first, followed by the upper tabs. Note that in order to get to the lower tabs, the upper tab labelled Search For must be selected.
Lower Tab 1: Customers
(To get to this tab, click the upper tab Search For and then click the lower tab Customers.)
This tab is primarily used when trying to find a specific customer or send a marketing piece to anyone in a specific City, State, or Postal Code. It is also possible to limit the customer search to A/R Accounts Only by checking the box shown above.
If data is entered in any of these fields, the search will only include customers that match the entered criteria. For example, if “Jones” is entered in the Last Name field, only customers with that last name will be included in the search. Similarly, if “MI” was selected for the State, only customers with a Michigan address will be included.
Lower Tab 2: Vehicles
(To get to this tab, click the upper tab Search For and then click the lower tab Vehicles.)
This tab can be used to filter vehicles by Make, Model, Years, and Miles. This can be a very effective tool if one type of vehicle needs to be searched for.
For example, to generate a list of every Ford Taurus that has been invoiced in the shop, use the drop down arrows to select the proper Make and Model. In the example below, the Years date range fields have also been used to only include Ford Taurus vehicles from 1993 to 1997.
Lower Tab 3: Invoices
(To get to this tab, click the upper tab Search For and then click the lower tab Invoices.)
This tab will allow the search to be limited by the number of Invoices, invoice Date ranges, invoice Amount ranges, and by the customer’s payment method.
Date is a field that is commonly used when creating a customer output file. For instance, in order to generate a list of customers who have been invoiced from January 1, 2015 until June 30, 2015, enter the date range as shown on the following screen:
Another example, shown below, shows the search being refined for customers that have spent between $500.00 and $5000.00 within the same time period. By selecting both Dates and Amounts, it is possible to fine tune the search criteria.
Payment is another search parameter. If selected, only customers that have used that payment type will be included. Use the drop-down menu to select this field.
The remaining field on this screen, Reversed Invoice, will include reversed invoices that meet the other criteria specified for the invoice.
Lower Tab 4: Services
(To get to this tab, click the upper tab Search For and then click the lower tab Services.)
This tab allows the search to be customized based on services the customer has had performed. A specific Sale Code can be selected by using the drop-down menu. For example, the following screen shows how to select only customers that have had brake work done:
The search can be further defined to include a Minimum Amount and Maximum Amount for Job, Parts and Labor:
The remaining fields on this screen are Guaranties and Declined Jobs. Use the radio buttons below the fields to Include or Exclude customers with this criteria. If ONLY is specified for either of these options, only customers with Guaranties and/or Declined Jobs will be included.
HINT: A common search would be to enter a Date range on the Invoice Tab (see page 6) along with a Sale Code on this tab. By entering these parameters, a list of customers that had brake work during a specific month can be targeted.
Lower Tab 5: Parts
(To get to this tab, click the upper tab Search For and then click the lower tab Parts.)
This tab can be used to limit data included in the search by Category, Part Number, Sale Type, Product Code, a specific DOT/Serial #, Only Parts With Core Cost, and Declined Parts.
Selecting the Category of Wagner Brakes would cause only customers that had Wagner Brake parts invoiced out to be included in the search.
Note that Declined Parts can be either excluded, included or ONLY declined parts can be looked at.
Lower Tab 6: Loyalty
(To get to this tab, click the upper tab Search For and then click the lower tab Loyalty.)
This tab is used to filter customers based on their last visit, how much they have spent, and various rewards options. This information is very useful when creating a report for customer retention.
EXAMPLE: To create a list of customers that have spent and least $500 so far this year, enter the following:
HINT: This could be helpful when deciding who to send Holiday cards to.
Lower Tab 7: Promotions
(To get to this tab, click the upper tab Search For and then click the lower tab Promotions.)
In the lower series of tabs, Promotions is the last choice for filtering. By using this tab, it is possible to select customers who have received a discount, were referred by a user-defined referral method (Postcard, Radio Ad, etc.) and even customers that have used a specific coupon code (also user defined).
There are many different filtering combinations that can be used in order to target particular customers. After going through the lower tabs, here is an example of what a Customer Data Search could look like:
The filters that have been defined are listed in the white box below the tabs. When changes are made to the search parameters on one of the tabs, they are kept track of in this space.
This particular example will search for everyone who has a vehicle manufactured from 1995-2015 and who has come into the shop from the 1st of January until the 30th of June. The search was also refined to only include brake jobs (Sale Code = B). Therefore, customers who have had a brake job invoiced at this location since during the specified date range will be included when this data search has been setup.
**Now that the lower row of tabs has been covered, this document will focus on the upper series of tabs: Search for, Output, Options, and Fields.**
Upper Tab 1: Search For
As the previous examples have shown, this tab is selected to allow access to any of the lower tabs. That is solely the function of this tab.
Upper Tab 2: Output
The customer data search results can have the following Output Files:
Report, Output File, Mailing Labels, Postcards, Form Letters, Email, Form Letters & Labels and and Postcards & Labels.
For this example, the Output Type will be “Output File”, as shown above. In addition, customers that do not want to be contacted via both email and mail have been excluded.
The Delimiter field will default to “Tab”. Most 3rd party companies prefer to receive data in a tab delimited text file. In addition, do not select a Text Qualifier unless instructed to do so by the company the file is being sent to.
Clicking on the three dots to the right of Output File will allow the directory for that file to be selected and the name of the file to be specified.
To make the process simple, select Desktop on the left hand side of the screen as shown below, and then enter a name for the output file:
In this example, the file was named “Customer Marketing File“. Windows will append a .txt file extension to the file automatically. Later on, when the list is generated, a tab delimited text file named “Customer Marketing File.txt” will be placed on the Desktop. That file can then be imported into Microsoft Excel, modified or even emailed.
Upper Tab 3: Options
Select either One per Customer or One per Invoice.
One per Customer – this will export one record per customer. Note that if this option is chosen, the customer’s first vehicle information will be used in any applicable output fields. Using this option will ensure that customers aren’t duplicated in the results.
One per Invoice – this option will output one line of data per invoice. There are times when this option will be the best choice. For example, use this if you want to see detailed invoice information per customer.
Upper Tab 4: Fields
NOTE: This tab is only used if you specified either Report or Output file on the Output tab. If you are outputting to Mailing Labels, Postcards, Form Letters, Email, Form Letters & Labels or Postcards & Labels, you do not need to use this tab. If outputting to Report or Output File, information must be entered here or your report or file will be blank.
The Available Fields are listed on the left, and the Selected Fields (the ones that will be outputted) are on the right. Highlight a field on the left and click the Add button to move it to the right. The fields on the right can also be moved up and down to determine the order in the file or report. Once the fields are set, click Save.
The program will return to the Customer Search Profile List screen, where you can see a list of your searches.
The Customer Search can now be edited further or run.
To run the search, Highlight it and click the Run button.
After clicking Run, the profile will start generating the information. In the example used in this document, the list generated would be for any customer that meets the following criteria:
- Has a vehicle with the year between 1995 and 2015
- Had at least one invoice from January 1, 2015 through June 30, 2015
- Had a brake job (Sale Code of B) on one of those invoices
When the search finishes, close or minimize all windows on the computer to go to the Desktop to access the file created:
The Customer Marketing File can now be opened and edited in Excel, using the Import from Text option under the Data tab. It can also be emailed as is to a marketing company, if desired.
This concludes this tutorial on Customer Exports. For more information, contact Janco Technical support at 800-782-5872 (US) or 800-323-5872 (Canada).
— end —