Gift certificates and promotional coupons can play an important role in your marketing strategy by drawing new and repeat customers to your business. However, how can you tell if they are really working to help you develop your business? To make the most of your time and your marketing budget, you need to track the success of your marketing efforts so that you can determine what marketing tactics are effective for which customers.
One of the easiest ways to track the success of marketing pieces like gift certificates and coupons is to add a tracking number or code to each gift certificate or coupon that you distribute. When gift certificates or coupons are redeemed, you can use these tracking numbers to record and categorize customer responses.
This article explains how you can add tracking numbers to batches of gift certificates or coupons that you create in Microsoft Office Publisher.
In this article
Approaches to tracking
You can take a couple of different approaches when you incorporate tracking information into your publications. For example, you can do either of the following:
- Add unique numbers to gift certificates By using a unique number on each certificate, you can track when gift certificates are redeemed, by whom, and for what product or service. In addition, by adding unique tracking numbers, you can ensure that the gift certificates are not duplicated or redeemed more than once.
- Add specific codes to coupons to track customer responses Use different codes for your different marketing messages, advertisements, or promotions. These codes will enable you to keep track of how customers learned about your offer. The more you learn about who is drawn to your business and what attracts customers' attention, the better you can focus future marketing efforts.
Note You can use the same method when you need to add sequential numbers to admission and raffle tickets, invoices, and any other type of publication.
Top of Page
Create a data source that lists tracking numbers
To use mail merge to create a batch of gift certificates or coupons with tracking numbers, you need to set up a data source that contains a column listing the tracking numbers. If you plan to add only the tracking numbers to your publications, create a data source for the tracking numbers. If you also plan to use mail merge to insert additional information into your publications, such as customer names or addresses, you can add the column of tracking numbers to a data source that also lists the name and address data that you want to use.
You can use Microsoft Office Publisher, Microsoft Office Excel, Microsoft Office Access, Microsoft Office Outlook, or Microsoft Office Outlook with Business Contact Manager to create a data source for your mail merge project. If you want to create a large list of unique, sequential numbers to use as tracking codes for gift certificates or coupons, however, you can use Excel to quickly generate this list.
Use Excel to create a list of unique tracking numbers
- In Microsoft Office Excel, open a new or existing Excel worksheet.
- In the first cell of the column where you want to add tracking numbers, type a label, such as Tracking Number, and then press ENTER to move to the row below.
- In the second cell in the column, type the number for the first tracking number in the series. For example, type 1,000.
- In the cell below, add 1 to the number you entered in the cell above, and then type that number. For example, type 1,001.
This establishes a pattern for the series of numbers that you are creating.
- Select the cells that contain the starting values.
- Drag the fill handle across the range of cells that you want to fill.
- When you have created as many tracking numbers as you need, save your file.
Add specific predetermined tracking codes to a data source
If you have already developed a set of specific code numbers that you want to use to track and categorize customer responses, you can type these numbers into a column of the data source that you plan to use for the mail merge.
- In Microsoft Office Excel, open a new or existing Excel worksheet.
- Click the first cell in the column where you want to enter the data.
- Type a label, such as Tracking Code, and then press ENTER to move to the row below.
- Type a tracking code, and then press ENTER to move to the row below.
- Repeat until you have listed all of the tracking codes that you plan to use.
Note To create multiple copies of a gift certificate or coupon that all share a specific tracking code, you can type the same number multiple times within the column.
- When you have created as many tracking codes as you need, save your file.
Top of Page
Use Publisher to create a gift certificate or coupon
You can create a gift certificate or coupon publication using Publisher in one of two ways. You can:
Start with a pre-designed gift certificate publication
- Start Publisher.
- In the Publication Types list, click Gift Certificates and then click the design that you want.
- Customize your publication by making any changes that you want. For example, you can choose a different color or font set.
- Click Create.
- To replace placeholder text and pictures with your own information, do any of the following:
- To replace placeholder text, click the placeholder text, and then type.
In most cases, the text resizes automatically to fit within the text box. If you want to control the text size, click the text box. On the Format menu, point to AutoFit Text, and then click Do Not Autofit. Select the text, and then choose a new font size from the Font Size list on the toolbar.
Note If you have already created a business information set, your business contact information and logo automatically replaces some of the placeholder text. For more information, see Create, change, or remove business information data.
- To replace placeholder pictures (not publication designs) with your own pictures, right-click the placeholder picture, click Change Picture, and then choose the source of the new picture.
If you don't see Change Picture when you right-click, click the picture again until you see gray circles surrounding it, and then right-click the picture.
Note If you want more information about resizing, moving, and other aspects of working with pictures, see Publisher Help.
- When the gift certificate looks the way you want, save the file.
Start your publication from scratch
If you are designing a gift certificate or coupon from scratch, you can either start with a completely blank publication or adapt an existing publication. Small-sized publication types, such as business cards, postcards, and labels can be adapted to serve as coupons. Flyers or brochures can be adapted for use as gift certificates.
- Start Publisher.
- In the Publication Types list, do one of the following:
In your publication, add text, pictures, and any other objects that you want.
- To start with a blank publication, click Blank Page Sizes, and then click the size that you want.
- To adapt a specific publication design, click the publication type that you want, and then click the design that you want.
- Under Customize, make any changes that you want.
For detailed information about working with text, pictures, and objects, see Publisher Help.
- When the publication looks the way that you want, save the file.
Top of Page
Use Mail Merge to add the tracking codes to your publication
After you set up your list of tracking numbers in the data source and design your publication, you are ready to merge your list of tracking numbers with your publication to create a batch of gift certificates or coupons that each displays a unique tracking number.
This particular mail merge project involves four broad steps:
- Connect your gift certificate or coupon publication to the data source.
- Insert a data field into your publication as a placeholder for the tracking number information.
- Preview the merge to refine the layout
- Print the merged publication to create or print a batch of gift certificates or coupons that display the tracking numbers from the data source.
Connect your publication to the data source
- Save and close the data source that contains your list of tracking numbers.
- Open your Publisher gift certificate or coupon publication.
- On the Tools menu, point to Mailings and Catalogs, and then click Mail Merge.
- In the Mail Merge task pane, under Create a recipient list, click Use an existing list, and then click Next: Create or connect to a recipient list.
- In the Select Data Source dialog box, locate the data source that contains your tracking numbers, select the file, and then click Open.
Note By default, Publisher opens the My Data Sources folder first. If you saved your data source in a location other than the My Data Sources folder, browse to the location where you saved your data source.
- If your data source is an Excel worksheet, select the worksheet that you want from the list in the Select Table dialog box.
- In the Mail Merge Recipients dialog box, you can select the tracking codes that you want to include in the merge. Select the check boxes next to the records that you want to include, and clear the check boxes next to the records that you want to exclude.
If you want to use only certain entries in your list, you can filter your list by a specific field or criterion. After you filter the list, you can use the check boxes to include and exclude records.
Filter items in the list
- Click the arrow next to the column heading of the item that you want to filter by.
- Click any of the following:
- (Blanks) displays all the records in which the corresponding field is blank.
- (Nonblanks) displays all the records in which the corresponding field contains information.
- (Advanced) opens the Filter and Sort dialog box, which you can use to filter using multiple criteria. You also can click Filter under Refine recipient list in the Mail Merge Recipients dialog box to open the Filter and Sort dialog box.
Tip If your data source contains records that share the same information, and there are ten or fewer unique values in the column, you can filter by specific information. For example, if there are multiple records that list Australia as the country/region, you can filter on Australia.
The Mail Merge Recipients dialog box displays only the designated records. To display all the records again, click (All)
Sort items in the list
- In the Mail Merge Recipients dialog box, click the column heading of the item you want to sort by.
- To quickly select or clear all items, select or clear the check box column heading.
- To sort using multiple criteria, click Sort under Refine recipient list in the Mail Merge Recipients dialog box. In the Filter and Sort dialog box, select the criteria you want to sort by.
- Click OK.
- At the bottom of the Mail Merge task pane, click Next: Create merged publications.
Insert a data field into your publication
After you connect your publication to the data source that you want to use, you can insert a data field into your publication in the location where you want the tracking number to appear.
- In your mail merge publication, click where you want to insert the tracking number data field. You may first need to create a text box for the data field location if one does not already exist.
- In the Mail Merge task pane, in the list under Prepare your publication, locate the field name that corresponds to the column heading for the list of tracking numbers in your data source. For example, if you used Tracking Number as the column heading for the list of tracking numbers in your spreadsheet, locate Tracking Number. Drag the tracking number field into the text box that you created for it.
- If you want to insert data fields for additional information in your data source, such as customer names or addresses, drag those field names into your publication.
- If you want to apply formatting to a data field to change the appearance of the merged data, select the field, click Font on the Format menu, and then select the options that you want.
- After you insert all of the data fields, save your publication.
Preview the merge
Before you complete the merge, preview the merge results to make sure that the tracking numbers will display as you want them to in your publications. You can preview the merge in two ways: While you are refining the layout to review the layout of the individual coupon or gift certificate, or when you are getting ready to print, to preview the arrangement of coupons or gift certificates on the printed sheet.
Preview the layout of the coupon or gift certificate
The information from the first record of your data source populates the data fields. You can use the navigation buttons to preview the information from the remaining data source records.
- In the Mail Merge task pane, click the navigation buttons to see how the other entries from your data source look in your publication.
You cannot edit your data source entries here, but you can format, move, or delete data by formatting, moving, or deleting the data fields.
- If the numbers appear to be cut off, adjust the formatting and layout of your publication. For example, you may need to increase the size of the text box that contains the data field for your tracking number, or you might need to decrease the font size of the data field.
- If you are satisfied with the previews, at the bottom of the task pane click Next: Create merged publications.
Preview the printed sheet
You can make a final check of the merge publication before printing by previewing the merge.
- On the File menu, click Print Setup.
- In the Print Setup dialog box, click Multiple pages per sheet, and then click OK.
- In the Mail Merge task pane, under Create merged publications, click Print preview to make sure that the information aligns properly on sheet of gift certificates or coupons.
- Click Close to close the print preview window.
Print the merged publication
- In the Mail Merge task pane, under Create merged publications, click Print.
- In the Print dialog box, select the printing options that you want, and then click OK.
- On the File menu, click Save to save the publication.
Note Keep in mind that what you are saving is not the set of coupons or gift certificates that you have printed. Rather, you are saving the publication with its connection to your data source and its placeholder fields. By saving the publication, you can quickly create another set of coupons or gift certificates when the need arises. The publication maintains the link to the data source that you connected it to so that you can jump ahead to the previewing step.
Top of Page