8.1 User Conference Mailing

Compiling a custom mailing list for an upcoming conference is a necessary, but laborious, task. The functions provided by the Data Worksheet simplify this task. Say you are hosting a conference for select customers and you would like to send them printed invitations. Seating is limited, so sending an invitation to every customer would not be cost effective. Therefore, to narrow down the list of the customers to whom you will send the invitation, you can use their order history. Suppose you decide to send an invitation only to the following customers:

The top 3 customers this year

The customers who have purchased at least one of our products in the last two weeks

In this example, you will make use of the following components and methods:

A User-Defined Date Range (see Creating a Date Range)

Advanced conditions (see Defining Advanced Conditions)

Concatenated table options (see Concatenating Tables)

Group and aggregate options (see Grouping)

A table value in the filter conditions (see Filtering)

The completed example is called 'UserConferenceMailing', and can be found in the 'Global Worksheet' > 'Tutorial' folder.

Example: User Conference Mailing  >>

Assume you have two tables: A table of order information for each customer, called 'OrderInfo', and a table of contact information for each customer, called 'ContactInfo'.

To build the desired invitation list, follow these steps:

1. Generate a list of all customers who have purchased at least one of our products in the last two weeks.

To do this, create a User-Defined Date Range, 'Range', and apply this new User-Defined Date Range in a filter condition on the 'OrderInfo' table. Name this new table 'RecentCustomers'. The 'RecentCustomers' table will to have two columns, 'Customer Company' and 'Order Date'. You require the 'Order Date' field to specify the date condition, but the column can then be hidden.

2. Generate a list of the top 3 customers for this year. To do this, apply a ranking condition to the 'OrderInfo' table so that it only lists the top 3 customers based on the sum of the 'Total' sales for each customer.

3. Once you have both of these tables, you can unite them into a single table. Create a Composition table using the 'union' set operation. This requires that both base tables have the same number of columns, so hide all the columns of both tables except for the 'Company Name' columns. Name this new Concatenated table 'CustomerList'. This table contains the list of Company names to whom you wish to send invitations.

4. You now need to retrieve the corresponding contact information for these companies. Specify a subquery-based filtering condition on the 'Company' field of the 'ContactInfo' table, so that a record is only displayed if the company is “one of” the companies in the 'CustomerList' table.

Figure 1. The 'User Conference Mailing' example

 

 

<< 8 Applications © 1996-2013 InetSoft Technology Corporation (v11.4) 8.2 What-If Analysis >>