Skip to main content

How to merge external data and create a campaign

ExecuTrax allows you to merge information used to flag records. Once the records are flagged, a marketing campaign can be created and used for tracking. However, there are a few requirements that need to be met for the merge to operate successfully.

File Requirements

The merge file must be generated in either a Fixed Length or DBF (.dBASE) format. The file must contain one of the following fields:

  • Account Number
  • Account Number and Account Type
  • Social Security Number
  • Member Number

In addition to one of the fields above, the file must contain an indicator field used to flag the records. An indicator simply needs to be at least a single digit character that can be defined as True within the merge file settings.

 

Pre-Merge UDF Setup

If this is the first time you’re merging in a list, you’ll want to create a user defined field (UDF) to use as your flag.

 

  • Within ExecuTrax, click on Setup from the main menu and click File Shop.
  • Click the Add button and choose User Defined Fields.

  • The Group should be on Existing, and it should reflect your Institution User Defined Fields.
  • Fill out the Field Info:
    • Enter a unique Description
    • Provide a short, unique identifier within the Name field
    • File needs to be Account
    • Use the drop-down arrow for Type and select Logical

 

  • The “Include in Field Group” at the bottom of the setup should be set to Existing and Account User Defined Fields must be selected.
  • Once the selections are made, click Save.

 

Create & Process Merge

Just a reminder, the merge file must be generated in either a Fixed Length or DBF (.dBASE) format.

 

If you are provided a file in Excel format, simply save the spreadsheet to a .PRN format. A PRN format is fixed length, so it enables the file to be used for a merge.

Ensure your file has one of the four fields required for matching during a merge:

  • Account Number
  • Account Number and Account Type
  • Social Security Number
  • Member Number

 Also, make sure an indicator field is present that will be used to flag the record within ExecuTrax. Once the file is ready, the merge can be created.

  1. Click File from the main menu and select Merge.
  2. To begin, click the Add button.
  3. Enter a Name for the merge and click Save.
  4. In the setup box, click the Browse button. Navigate to the location of your merge file and select it.
  5. Click Save.

The Field Locations box will appear which is where the fields will be mapped to the appropriate ExecuTrax fields.

  • Map or assign the fields using one of the two options below:
  • If merging a DBF file, use the drop-down arrows to select the appropriate field from the list.

  • If merging a Fixed Length file, enter in the numeric value for the starting position of the field and the width.

When using a Fixed Length format, if you do know the starting position, the information can be determined using the following steps.

  • Click the Toggle Edit button to toggle it on and click once in the Position box for the desired field. Within the sample data box, click where the field starts, hold, and drag to the end of the field.
  • Let go of the mouse for the position and width to populate.

 

  • Be sure to click the Toggle Edit button to toggle it back off when done.
  • Locate the UDF created in ExecuTrax within the list and map the field.
  • Because it is a logical field, the Format must be provided that identifies the flag as True. Click the UDF in the list and click the Format button.
  • Enter the value(s) that identify the flag as True into the box and click Done.

 

Note: If the flag will be true if the field contains any value and is not empty, enter NONBLANK into the box.

  • Once all the fields have been mapped, click Save Changes.
  • The FileSpec button contains settings important to the file structure and process. Click the FileSpec button.
  • Enter in the number of header lines present in the file, if applicable.
  • To only allow matching for those records with a specific product, click on the desired product within the list (optional). Be aware, if a match is made to a record that does not contain the selected product, the flag will not come over.
  • Once the necessary selections are made, click Save.

If all the fields are mapped and the FileSpec settings are in place, the merge is ready to be processed.

  • Click Merge, verify the path of the merge file and file set it is being merged into. If both are correct, click Yes to continue.

Within the Options box, there are only two options that should be enabled, if desired:

  • Update Multiple Matches: if enabled, it will update all records that match rather than just the first match it encounters.
  • Create Orphan File: If enabled, a file will be generated of those records the system was unable to find a match on.
  • Place a check mark in the desired options and click Continue.

 

Upon clicking Continue, the merge will begin processing the merge file and updating the flag inside ExecuTrax. A message will appear identifying the number of records that were updated as well as where to locate the orphan file if you enabled that option.

 

Create the Campaign

After the flag is merged in, you are ready to identify those records and create the campaign.

  • Close out of the Merge Setup Manager, click Discovery from the main menu and select Research.
  • To build a filter on the account UDF, click the Add button.
  • Under Step One, click Account User Defined Fields and click Next step.
  • Under Step Two, click on the UDF you merged the flag into from within the list and click Next step.
  • Under Step 3, click on “Is true” and click Done.
  • The filter within the Research window should now reflect “UDFNAME is true”.
  • With the filter in place, click the Campaign button located on the Output Menu of the Research screen.

 

The Campaign Processor is broken up into two sections: Campaign Groups and Campaigns. The campaigns are placed within the Campaign Groups.

  • To place the campaign in an existing Group, click on the Group within the Campaign Groups list.
  • Click Add to begin setting up the campaign details.
  • If a new Campaign Group is desired, simply click in the Group box and enter in the new group name.
  • Within the Campaign box, enter in the desired campaign name.
  • Fill in the necessary information for the campaign setup page:
    • Marketing Group Size: Define the value that will be sent the marketing offer
    • Control Group Size (optional): Define the value that will be tracked but will not receive the marketing offer
    • Campaign Costs (optional): Enter the cost of the marketing effort
    • Campaign Dates: Define the start and end date for tracking
    • Campaign Proof (optional): Attach an image or PDF of the email or print that was sent
    • Product/Account Type: Select up to three Product/Account Types to track on
    • Averages: Enter Avg Balance, Rate and net NII-NIE for each new account
    • Goals: Enter the value for the number of new accounts. The Goals Balance of New Accounts will auto populate based on the Avg balance and number of new account goal values.
    • Once the settings are in place, click Activate.

The records will be flagged as a part of the campaign and the system will begin tracking based on the date ranges. Tracking results can be viewed with the Campaign reports found within Research as well as the Campaign Tracking available through the MDP.

 

Related articles