TOC: Preparing Data Tables | Matching and Updating Temporary ID Fields | Replacing the Temporary ID Codes
Preparing Custom Tables for Importing Interactions
Preparing Data Tables
Once you have planned and defined the custom Interactions and reviewed the organization's existing data sources, you will have identified tables that need to be imported for custom Interactions. These Interactions will be created as part of the import process. Each record in a custom Interaction table must correspond to a single contact record and a single Interaction definition record. To learn how to build and link your import tables, see Preparing Data for Import and Planning Custom Interactions.
In this example, we will show you how to import existing data in order to create custom Interactions. We will be using an example that assumes that you have an import underway, and that you have moved contact, location, and Interaction definition data into Metrix. In order to import custom table data, we will need to use three separate datasheets:
- InteractionDefinitions.xls : This is a table that captures the name of the Interaction, as well Category 1 and Category 2 values.
- Import_tblInteractions.xls : This serves as a junction table linking key fields from the custom data table (CustomData_Membership.xls), the Interaction definition data sheet (InteractionDefinitions.xls), as well as the ContactID field for the contacts being imported.
- CustomData_Membership.xls : This is the datasheet that contains the actual data that will be migrated and associated with the appropriate contact through the linking of key fields. This is what becomes the custom data table.
In addition to the datasheets, you will also be working with Metrix Import Logs, which provide you with the actual ID numbers that were generated during import. Essentially, much of the work of preparing custom data for import involves "translating" ID's from the temporary values to permanent ones. The Metrix Import Logs provide you with the translation.
In the following example, we will be importing membership data. As we can see from the Interaction Definition datasheet (InteractionDefinition.xls), the primary key value for "Membership" in the column tmpInteractionDefinitionID is "5". This is the temporary ID value
Figure: Interaction Definitions Ready for Import
A custom Interaction data table has columns for the custom data, as discussed in Understanding Interactions and Custom Tables. It also must have three foreign keys, linking it to tblContacts, tblInteractions, and tblInteractionDefinitions. Your custom table links to these standard data tables by sharing their temporary ID codes in the fields tmpContactID, tmpInteractionID and tmpInteractionDefinitionID. These temporary ID's will then be replaced by the permanent Metrix ID codes which are generated by the import process.
In addition to these ID fields, the custom Interaction data table also contains the data that will comprise the custom Interaction. Sometimes there will not be much change here from the organization's original data. On the other hand, this is an ideal time to reformat data so that once it is in Metrix it is easier to use.
Back To Top
Matching and Updating Temporary ID Fields
Below is our custom data table, prepared for import. Note that every record has a tmpInteractionDefinitionID value of "5", a foreign-key value for the Interaction Definition datasheet. Each custom Interaction's datasheet will have a different value in this column.
Figure: Custom Interaction Data Formatted with a Temporary Interaction Definition ID
Note: Lookup tables will be generated automatically from existing values later in the process. Look over your data in fields that will need a lookup, and decide whether it needs some cleaning. For example, note that in the third record of the table shown above, the MemberLevel value is "Gold level." This should be changed to "Gold," to match the other data. You will want to limit such variations in the data before import, to avoid a tedious cleanup later in the process.
As you prepare to replace the temporary ID's with new values, you will find it convenient to have the Interactions table color coded by Interaction definition, as in the example below.
Figure: Color-coding Interactions for Import
Back To Top
Replacing the Temporary ID Codes
At this stage, you will be underway in the Metrix import process. If you have not actually started the import, then you will need to hold off on creating custom Interactions until you are actually importing, as you will be replacing the temporary ID fields with the real ones generated by Metrix during import.
- You can't import custom data until you move your standard source data from the Preview Area into the database, as discussed in Importing Standard Data. When this is done, go back to the Import Manager and click View Metrix Import Log.
Figure: The Import History Log Window
The import process has generated the permanent Metrix ID codes for your data records. You will now get these ID's from the Import Logs and match them to your temporary ID's.
- Click on the Import drop-down list.
Figure: Viewing the Import Log
- Click on your Contacts import, and the corresponding pair of ID code columns will be shown in the Import window. Select these columns and copy them to the clipboard.
Figure: Copying the Paired Contact ID Columns
- Paste the paired ID's into your Contacts datasheet, right next to the tmpContactID column.
Figure: Pasting Paired Contact ID's into the Source Data
- Spot-check the tmpContactID columns to be sure that they are in the same order.
If they are, you can delete one of them. Now you have matched the temporary ID with the Metrix-generated permanent one. Change the column header of the new ID from MetrixContactID to ContactID.
You will have to repeat the above steps to replace the ID's in your Interaction Definitions and Interactions source data, copying the paired ID values from the Import Log and pasting them to the datasheets.
- Open your Interactions datasheet and create a new column labeled ContactID, next to the temporary Contact ID (tmpContactID) column. Find some empty space in this sheet and paste in the paired ID's from the Contacts sheet. For this example, we will assume that your data is set up as shown below, with the paired ID's highlighted.
Figure: Setting-up for Contact ID Translation Using the Interactions Datasheet (Import_tblInteractions.xls)
- In the first data cell of the new ID column, enter the following formula: =VLOOKUP(B2,$H:$I,2,FALSE)
where cell B2 contains the first tmpContactID record, and columns H and I contain the paired list of temporary and permanent Contact ID's. Briefly, you are looking up a value in the second column of paired ID's based on the first-column value that matches the temporary Contact ID in column B.
- Copy this formula down the column. Select this column of results, copy it, and then right-click on the cell range to open Paste Special, and select Values. This will replace the functions with their resulting values.
- Repeat the above process in the Interactions datasheet with your new Interaction Definition ID's. If you have only a few definitions, it may be simpler to just copy and paste the values.
- Now open your custom datasheet and create three new columns next to your existing ID code columns. Switch back to the Interactions sheet and highlight the data range corresponding to your custom datasheet. (This is where color-coding comes in handy.)
Figure: Copying Permanent ID's from the Modified Interactions Datasheet (Import_tblInteractions.xls)
- Paste the data into your custom datasheet.
Make sure the columns are labeled InteractionID, ContactID, and InteractionDefinitionID.
Figure: Pasting Permanent ID's into the Custom Membership Datasheet (CustomData_Membership.xls)
- Repeat the above steps for every custom Interaction datasheet.
Now that your data has been prepared, you are ready to import the data tables into Metrix. You will be using the developer module (devmodule130 ) to import tables into Metrix.
The next step is Importing Custom Tables into Metrix.
Back To Top