In addition to appending demographics annually, many clients opt to append new households on a quarterly basis. To append your data quarterly, follow the steps below.
Step 1 - Filter New Qualified Households for the Quarter
- Log into to ExecuTrax.
- Set the active fileset to the most recent month-end file.
- Select the Household Level view.
- Navigate to Discovery I Research.
- If an existing filter already exists, open it, edit the Household First Open Date parameter to reflect the current quarter and proceed to Step 2.
- If not, create a filter with the following criteria:
- Household First Open Date is later than or equal to "the first day after the last date that either the full or quarterly append was pulled" and
- Business or Retail Type Flag equals retail and
- Head of Household Age is greater than or equal to 18 and
- Household Name #1 is not an empty field and
- Household Address is not an empty field and
- City Name is not an empty field and
- State Code is not an empty field and
- ZIP Code is not an empty field and
- Undesirable Households for marketing Flag is false (optional)
- Save this filter as "Quarterly MARQUIS Demographics Append Filter".
- Click Summary to see the number of households and confirm this quantity with your Consultant or Relationship Manager.
Step 2 - Export Qualified Households
- Click Export
- If an existing export template already exists, open it, select activate and proceed to Step 3.
- If not, Click Add.
- Name the new template "Demographics Append Template", place it in a Group called "MARQUIS Demographic Exports" and click OK.
- Click Export Type, select FoxPro 2.X data table (.dbf), and click Save
- Click Fields and select the add the following fields to the export:
- Assigned Household Key (located under Account Maintenance)
- Household Name #1 (located under Household Maintenance)
- Household Address (located under Household Maintenance)
- City Name (located under Household Basics)
- State Code (located under Household Basics)
- ZIP Code (located under Household Basics)
- Click Save.
- Click Browse.
- Select the location you'd like to export the file to and name it something that's easily recognizable such as "MQDemographics".
- Click OK.
- Check the Group by Households option.
- Click Activate.
Step 3 - Upload the data file to Marquis
- Locate the export file.
- Upload the file to Marquis using FTP (FileZilla is recommend). For additional information, please contact support.
- Notify your Consultant or Relationship Manager that the file has been uploaded successfully.
Step 4 - Download and Unzip the Appended Demographic File
Once the file has been appended, your Consultant or Relationship Manager will reach out to you.
- Download the file from Marquis using FTP (FileZilla is recommend). For additional information, please contact support.
- Unzip the file and place it in the Import folder on the ExecuTrax server. Note that the file will be encrypted and you will be provided with a password/encryption key. We recommend using 7-Zip to extract the data.
Step 5 - Browse the System Files in ExecuTrax to locate the existing Demographic File
- Contact our support team and request the special password.
- Log into ExecuTrax
- Navigate to System I Browse System Files.
- Enter the special password.
- Click Continue.
- Select Field Types or alias "datad".
- Click Browse.
- Locate the line that contains the full Demographic Append.
- Navigate to the File_Name field.
- Copy the name associated with the append file.
- Close the table by clicking the "X" in the upper right corner of the screen and do not save changes when prompted.
- Close the Systems Table.
Step 6 - Edit and add each Query File into your existing Append File using Microsoft SQL Server Management Studio
- There will be one query file for every 1,000 records you'll be adding to the existing append file.
- Open each file with Notepad or Notepad++. It will look something like this:
- Modify the table where it says “YOURTABLENAME” to what is stored in the back tables, so it looks this:
- Save the file.
- Open Microsoft SQL Server Management Studio.
- Either drag & drop the query file(s) or copy & paste the contents into a new query to execute adding the additional records.
- As long as the table name has been updated with the unique file name of the append file, the query is ready to execute.
- Once executed, deleting the query is recommended.
- Confirm that the total number of records added matches the total number of records in each of your query files.
- Close Microsoft SQL Server Management Studio and the full Append File will now contain the additional quarterly records.
For information related to annual demographic appends, check out the Annual Demographic Append article.