FlexQuery includes Customer Relationship Management (CRM)-based queries. This allows highly detailed analysis of your customer data collected by Retail Pro, and ultimately can export a file for use with email merge applications or for sending to a third party bulk email specialist.

The CRM category includes the following source queries:

Since there are a variety of ways companies use to encourage customers to spend more, how you would utilize FlexQuery will vary. However, the basic principles apply and here we will walk through one example.

CASE 1: Finding the top twenty customers by spend

Query: Customer Invoices

Description of Query: Shows every invoice for customers during the date range specified (eg Last Month)

How to:

1)      Select the query ‘Customer Invoices’ (see above) and set a date range.

2)      Click the dropdown arrow on the ‘View’ Button and select ‘View as Pivot’

This will immediately launch a pivot table.

3)      If you don’t have a predefined ‘Top Twenty Layout’, set up the pivot like this:

4)      Tick ‘Show Options’ and ensure that the pivot is fully expanded and that the expand buttons are hidden. The screen should look like this:

5)      Now we need to sort the customer totals. Tick ‘Show Sorting’ and configure as below:

Sally Smith is our top customer.

6)      Tick ‘Show Details’. The screen changes to:

7)      Now we can export. Select the appropriate export type from the Export button drop-down:

The usual export type is Excel. From there, if your bulk emailer requires CSV, you can load the file and Save As CSV.

HINT: If you export a pivot without selecting ‘Show Details’, the Excel spreadsheet will be a copy of the pivot itself. Email Merge requires every cell in every row of the exported file to contain data.

The procedure could be assisted by adding the ‘Allow Email’ field to the pivot’s filter area. This would ensure you can easily filter out those who specifically have not approved being on your email list.

You can then change the filtering by clicking on the down arrow of the ‘Allow Email’ field.

HINT: Remember to save any changes to the layout.

HINT: Click the ‘Add to My FlexQuery’ button to ensure your entire Custom View is saved ready for future use. The Custom View screen which appears looks like this:

If you have already predefined a filter for the source query, you can select it from the Filter selector to always apply it to the view.

Much more detailed analysis of items bought by customers (as shown in the Customer Invoices with Item Detail source query) is available. Using this you can identify customers in a number of different ways, including (but not limited to):

• All customers who bought a specific product
• All customers who bought more than $500 of product from one vendor
• All customers who bought more than $500 of product from one department and one vendor
• All customers who returned items
• All customers who bought more than X quantity
• All customers who had more than X discount applied to their purchase
• Comparison of sales volume for customers by store
• Summary of purchases over time (eg each quarter or month)

This allows you to get creative with your customer promotions. If you offer to include a customer in a draw for a weekend getaway if they buy a certain product, FlexQuery can extract the information for you. If you want to email a customer if they buy a certain product, you can get the email addresses extracted. If you offer customer discounts, you can monitor your margins to ensure your profitability is not adversely affected.

And, since Retail Pro’s PoS flags are included, you can use these for analysing how effective an email campaign is.