|Microsoft® Office Publisher 2003
|Microsoft Office Excel 2003
Gift certificates and promotional coupons can play an important role in your marketing strategy by drawing new and repeat customers to your business. But how can you tell if they are really working to help you grow your business? To make the most of your time and your marketing budget, you need a way to track the success of your marketing efforts so that you can determine what marketing tactics are effective for what customers.
One of the easiest ways to track the success of marketing pieces like gift certificates and coupons is by adding 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.
There are a couple of different approaches you can take when incorporating tracking information into your publications. For example, you can do either of the following:
- Add unique numbers to gift certificates A unique tracking number will enable you to keep track of when gift certificates are redeemed, by whom, and for what product or service. Also, by adding unique tracking numbers to your gift certificates, 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 of your offer. The more you learn about who is drawn to your business and what attracts customers' attention, the better you can pinpoint the focus of future marketing efforts.
This article explains how you can add tracking numbers to batches of gift certificates or coupons that you create in Publisher. There are three basic steps in the overall process:
- Use Microsoft Office Excel to create a spreadsheet listing unique tracking numbers or your specific codes.
- Create the gift certificate or coupon in Publisher.
- Use Publisher's Mail Merge feature to merge the list of tracking numbers with your publication to create a batch of gift certificates or coupons featuring the tracking codes.
Use Excel to create tracking codes
In order to use mail merge to create a batch of gift certificates or coupons with tracking numbers or codes, you will need to set up a Microsoft Office Excel spreadsheet that contains a column listing 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 this column to a spreadsheet that also lists the name and address data you want to use. If you plan to add only the tracking numbers to your publications, create a spreadsheet for the tracking numbers.
To use Excel to create a list of unique tracking numbers
If you want to create a large list of unique, sequential numbers to use as tracking codes for gift certificates or coupons, you can use Excel to quickly generate this list.
- Open a new or existing Excel spreadsheet.
- 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 sequence. 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 will establish a pattern for the series of numbers that you are creating.
- Select the cells that contain the starting values.
- Drag the fill handle over the range of cells you want to fill.
To fill the cells in the column in increasing order, drag down.
- When you have created as many tracking numbers as you need, save your file.
To add specific pre-determined tracking codes to an Excel spreadsheet
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 spreadsheet that you plan to use to use for the mail merge.
- Open a new or existing Excel spreadsheet.
- 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.
Back to top
Use Publisher to create a gift certificate or coupon
There are several ways to create a gift certificate or coupon publication using Publisher. You can:
To start with a pre-designed gift certificate publication
- Start Publisher. In the New Publication task pane, under New from a design, click Publications for Print, click Gift Certificates and then click the design you want.
- In the Gift Certificate Options task pane:
To replace placeholder text with your own text, click the placeholder text, and then type.
- Click Color Schemes and then choose the color scheme you want.
- Click Font Schemes and then choose the font scheme you want.
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.
- In most cases, the text resizes automatically to fit within the text box. If you want to control the text size yourself, 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.
- If you have already created a personal information set, your business contact information and logo will automatically replace some of the placeholder text.
When the gift certificate looks the way you want, save the file.
- 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.
- If you want more information about resizing, moving, and other aspects of working with pictures, see Publisher Help.
To 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 New Publication task pane, do one of the following:
In your publication, add text, pictures, and any other objects you want.
- To start with a blank publication, under New, click Blank Print Publication.
- To adapt a specific publication design, under New from a design, click Publications for Print, and then click the publication type that you want.
For detailed information about working with text, pictures, and objects, see Publisher Help.
- When the publication looks the way you want, save the file.
To start with a template from Office Online
- On the Help menu in Publisher, click Microsoft Office Publisher Help.
Note The Publisher Help task pane opens.
- Under Search for, type "templates" and then click the green arrow.
- Click the link at the top of the list called Templates for business reports, cards and banners, databases, and more.
- On the Templates site, search for the publication type you want and download it to your template location.
Back to top
Use Mail Merge to add the tracking codes to your publications
Back to top
After you have set up your list of tracking numbers in Excel and designed 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 display unique tracking numbers.
There are three general steps involved in this particular mail merge project:
- Connect your gift certificate or coupon publication to your data source (the Excel spreadsheet that lists the tracking numbers).
- Insert a merge field into your publication as a placeholder for the tracking number information.
- Complete the merge to create or print a batch of gift certificates or coupons that display the tracking numbers from the spreadsheet.
To connect your publication to the data source
- Save and close the Excel spreadsheet that contains your list of tracking numbers.
- Open your Publisher gift certificate or coupon publication.
- On the Tools menu, point to Mail and Catalog Merge, and then click Mail and Catalog Merge Wizard.
- In the Mail and Catalog Merge Wizard task pane, under Select a merge type, click Mail Merge and then click Next: Select data source at the bottom of the task pane.
- In the Mail and Catalog Merge task pane, under Select data source, click Use an existing list, and then click Browse.
- Locate the Excel spreadsheet containing 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 Excel spreadsheet to a location other than the My Data Sources folder, you will need to browse to the location where you saved your spreadsheet.
- In the Select Table dialog box, select the sheet that contains your list of tracking codes and then click OK.
- In the Mail Merge Recipients dialog box, use the check boxes or buttons to add or remove any tracking codes from the merge, if necessary, and then click OK.
- At the bottom of the Mail and Catalog Merge task pane (Step 2 of 5), click Next: Create your publication.
To insert a merge field into your publication
After you connect your publication to the data source that you want to use (in this case, your spreadsheet), you can insert a merge field (merge field: A placeholder for text or pictures that you insert into your publication. Information from a data source (such as a name, address, or image) is inserted in each merge field. You can format, copy, move, or delete a merge 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 merge field. You may first need to create a text box for the merge field location if one does not already exist.
- In the Mail and Catalog Merge task pane (Step 3 of 5), in the list box under Create your publication, locate the field name that corresponds to the column heading for the list of tracking numbers in your Excel spreadsheet. For example, if you used Tracking Number as the column heading for the list of tracking numbers in your spreadsheet, locate Tracking Number.
- Click the arrow next to the field that you want to insert.
- Click Insert as Text.
- If you want to insert merge fields for additional information in your data source, such as customer names or addresses, repeat steps 3 and 4 until you have inserted all of the fields that you want.
- If you want to, apply formatting to the merge field to change the appearance of the merged data.
To format the merged data, you must format the merge fields in your mail merge publication.
- In your mail merge publication, select the field containing the information that you want to format.
- On the Format menu, click Font, and then select the options that you want.
- After you have inserted all of the merge fields, save your publication.
- Click Next: Preview your publication.
To preview and complete the merge
Before you complete the merge, it is a good idea to preview the merge results to make sure that the tracking numbers will display as you want them to in your publications.
- In the Mail and Catalog Merge task pane (Step 4 of 5), click the navigation buttons to see what the other entries from your data source look like in your publication.
The information from the first record of your data source populates the merge fields. You cannot edit your data source entries here, but you can format, move, or delete data.
- If the numbers appear to be cut off, adjust the formatting and layout of your publication. For example, you may need increase the size of the text box that contains the merge field for your tracking number. Or you might need to decrease the font size of the merge field.
- Click Next: Complete the merge.
- In the Mail and Catalog Merge task pane (Step 5: Complete the merge), under Merge, click Print.
- In the Print Merge dialog box, select the options that you want, and then click OK.
- If your publication is set up to print multiple copies per page and you click Print Preview on the File menu to preview how your merged publications will print, the last entry you selected to preview in the Mail and Catalog Merge task pane will display on each of the publications. This is not a preview of the actual merge results. When you complete the merge and print your gift certificates, each publication will display a unique entry from your data source.
- If your publication is set up to print multiple copies per page, do not complete your mail merge by clicking Create new publication or Add to existing publication, as these options will produce an entire page of gift certificates for each entry in your data source, rather than one gift certificate per entry.