Skip to Content

Wiki source code of DEXTR -- Converting Your Data

Last modified by Admin User on 2025/05/29 16:38
Show last authors
1 = Introduction =
2
3 DExTr is ONEcount’s data exchange and transformation engine. This tool enables data exchangs between any data source or third-party vendor. Establish a data map once and, going forward, easily import and export data in and out of ONEcount’s Customer Data Platform.
4
5 This tool the capabilities to map and sync first- or third-party data to ONEcount questions/data fields.
6
7 If there are multiple databases with similar fields, DExTr can consolidate the similar datapoints into a single ONEcount question or keep the fields separate.
8
9 //For Example: if the field for ‘Customer ID’ in a legacy database is “CUST_ID” and the field name in a CRM is “CID”, there is an option to have the data consolidated in to the single ONEcount question that may be named “CustomerID”.//
10
11 There are three types of mapping in the Mapper: Demographic Mapping, Transaction Mapping and Engagement Mapping.
12
13 * **Demographic Mapping:** Demographic mapping maps external demographic questions/data fields and responses to ONEcount demographic questions and responses.
14 * **Transaction Mapping:** Transaction mapping maps external transaction data, e.g. product purchase activity, status, and date formats.
15 * **Engagement Mapping:** Activity mapping maps external activity headers, e.g. time on page, URL, impressions, etc. This mapping is typically used to import and export data from the ad hoc Engagement Metrics.
16
17 One or all of these types of mapping may be required for a single file, depending on the type of automated feed you want to generate or the data import you'll be performing.
18
19 The easiest way to create a DExTR mapping is to import your data file into the platform, and let the system help you. Or, you can manually enter the demographics, transactions, and activities you wish to map to ONEcount questions/data fields, or do a combination of both.
20
21 **Please Note:**
22
23 **ONEcount questions, response values, products and packages must be created prior to performing mapping.**
24
25 ONEcount maps a CSV file’s COLUMN HEADINGS into demographic questions, transaction data, and enagement data. ONEcount can then map the VALUES from a CSV into multiple-choice ONEcount values for the appropriate data points.
26
27
28
29
30
31
32
33
34 [[image:new-dextr-doc_387f0c278f1e2969.png||height="173" width="665"]]
35
36 To get started, go to Data Management → DExTR from the menu on the left side. You’ll see a list of existing DExTR mappings, and have the opportunity to create a new mapping by clicking the button on the upper right side.
37
38 [[image:new-dextr-doc_ca0183ab0ec25820.png||height="557" width="665"]]
39
40
41
42
43 == Click Create Mapper ==
44
45 You will be asked to give the mapper a title, and a description if needed. You should plan on creating one mapper per data source, although there may be examples where you need more than one mapper. For example, you may export data from SalesForce, but in two different ways: one for webinar registrations, one for sales data. Each format could require a separate mapper to handle the data.
46
47 The easiest way to build a mapper is to upload your file and let DExTR pre-populate the mapping. The file must be in CSV format in order to import into ONEcount, so we recommend using that for building the mapper.
48
49 [[image:new-dextr-doc_68c2dcabfcf1448b.png||height="349" width="665"]]
50
51 Select the file and hit upload. The system will load all of the column headings from your file into the DExTR UI.
52
53
54
55 [[image:new-dextr-doc_49c7e86855c5f89f.png||height="554" width="665"]]
56
57 DExTR Maps Demographic, Transaction and Engagement Data. The pull-down next to each column heading lets you choose what type of data is in that column.
58
59
60 [[image:new-dextr-doc_fa3f55daf981a1e7.png||height="542" width="665"]]
61
62
63 If you select Demographic, a new column pops up with all of the Demographic Questions you have configured in the ONEcount system. The pull-down only contains the first 20 or so demographic questions. If the one you are looking for is not there, click the back-space button and start typing the demo, and the system will auto-complete as you type.
64
65 [[image:new-dextr-doc_eb4c8f499e373c8.png||height="556" width="665"]]
66
67
68
69 For transactions, DExTR shows the transaction fields that can be mapped into the system.
70
71 [[image:new-dextr-doc_df0894c16d4a01b0.png||height="556" width="665"]]
72
73 If you select an option in the third column, demo/trans/engagment that has fielded data, like check-box, select, multi-select, etc., a fourth column will automatically appear that says, “Map Values.” Clicking on this link will open a pop-up that allows you to map the column values from your file into the appropriate values in ONEcount. The value from the file will appear in the box on the right, the ONEcount value to map it to will appear in the pull-down on the left.
74
75 Please note that when importing files for building a DExTR mapping, DExTR only imports the first 20,000 rows of the file. This means it is possible some of the values to not appear on this screen. If you need to add additional values, just click Add and a new row will appear.
76
77
78
79 [[image:new-dextr-doc_e3e1a93820e15af4.png||height="313" width="491"]]
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98 These are the responses defined for the ONEcount Question paired to the field from the database.  On the right, enter the values that will appear in the import file.  It is very important to map all values, because if a value in the input file is not found in ONEcount, the response for that field will be dropped. ONEcount will not allow external imports to add values to its response table. Also, if the values in your import file exactly match the values that ONEcount has (ie., for the State question, both the import/export file and ONEcount use the two-letter abbreviations) then //you do not need to create a mapping.//
99
100 Define how ONEcount should translate each response using the fields in the right column, ‘Field Response’. DExTr can use the same responses, or you may choose to translate or ‘transform’ data to another format.
101
102 //(Example: states may be abbreviated with 4 letters in a POS database [Conn., Mass.], while the ecommerce database has two letter abbreviations. You can map both to the two-letter abbreviation used by default in ONEcount)//
103
104 Finally the fifth column is generally optional. You can add validations for the field to either check for duplicates, require an entry, or verify the format entered to the field.
105
106 //**Checking for duplicates is critical, and ONEcount may not import your file if a de-dupe field has not been defined!**//
107
108
109 **Additional notes:**
110
111 Transactional Fields** - **if the source/import file has a product field called “PUB_TITLE” and the value for current subscribers is “YES” on the import file, but the ONEcount product is “Active Subscriber”, the Package Status Value from the import file should be matched with the Package Status in ONEcount.
112
113 Engagement Fields** - **Map external engagement (activity) headers, e.g. time on page, URL, impressions, email opens, registrations, link clicks, etc. Engagement field mapping is used primarily for exporting feed reports.
114 If the Engagement Field Value is a metric that is not a count, click the [] icon to map the metric field value to the ONEcount metric //(i.e. if the source/import file has an ‘Engagement Field Name’ that is “NEWSLETTER_ACTIVITY_TYPE” the ‘ONEcount Engagement’ would be “Metric >> Activity Metric Name” and then map the values of metric type [“OPEN”, “CLICK”] to the ONEcount metric [“Email opens”/”Email clicks”]). //
115
116 For engagement metrics, you MUST map something to the engagement Name field in ONEcount. This is the unique identifier for that action in the system.
117
118 **TRANSACTION ELEMENTS, INCLUDING PRODUCTS, PRODUCT STATUSES, AND SUBSCRIPTION TYPES, SOURCE CODES AND PROMO CODES, MUST BE CREATED PRIOR TO PERFORMING TRANSACTION MAPPING.**
119
120 ***__Product Mapping Area__ *
121 For automated data feed reports, the Third Party Product column shows what the ONEcount product name will be displayed as on output.**
122
123 * For example, a user is subscribed to/registered for ABCXY Pharma Aug. Webinar(207) in ONEcount. The third party requires that on the report output show a value of August Webinar rather than ABCXY Pharma Aug. Webinar. Mapping ONEcount's ABCXY Pharma Aug. Webinar(207) product to August Webinar(400) will display the Third Party Product value of August Webinar on the output.
124
125 **Product Column Field:** This identifies what to output as the header for the product field. Enter the heading that's required for the report output.
126
127 **For imports,** the Third Party Product column identifies the names/values from the import file that will be mapped to the ONEcount product.
128
129 * For example, there's "product" column in the import file that identifies what product a particular record is subscribed to or registered for. In the import file, August Webinar in that colum indicates the record is subscribed to/registered for the August Webinar. In ONEcount, the product name is ABCXY Pharma Aug. Webinar. Any record in the import file that has August Webinar in the product column will be subscribed to the ABCYXY Pharma Aug. Webinar product in ONEcount.
130
131 **Product Column Field:** This identifies the column header for the product column in the import file. In the above example, you would enter "product".
132
133 **__Product Status Mapping Area (Optional)__**
134 **For automated data feed reports,** the Third Party Product Status column shows what the ONEcount product status will be displayed as on output.
135
136 * For example, a user has a product status of "Not Active(405)" in ONEcount. The third party requires that the report output show a value of Inactive, rather than Not Active. Mapping ONEcont's "Not Active(405)" status to "Inactive(203)" will display the Third Party Product Status of Inactive on the output.
137
138 **Product Status Column Field:** This identifies what to output as the header for the product status field. Enter the heading that's required for the report output.
139
140 **For Imports**, the Third Party Product Status column identifies what name/value from the import file will be mapped to the ONEcount product.
141
142 * For example, there's a "product status" column in the import file that identifies the product status a particular record has for a particular product. In the import file, "Inactive(203)"in that column indicates the record has a product status of inactive. In ONEcount, the product status is "Not Active(405)". Any record in the import file that has a product status of "Inactive" in the product status column will have the product status of "Not Active" applied to the record in ONEcount.
143
144 **Product Status Column Field:** This identifies the column header for the product status column in the import file. In the above example, you would enter "product status".
145
146 **__Subscription Mapping Area__**
147 **For automated data feed reports,** the Third Party Subscription column shows what the ONEcount subscription type will be displayed as on output.
148
149 * For example, a user has a subscription type of "n" in ONEcount. The third party requires that the report output show a value of new, rather than "n". Mapping ONEcont's "n" status to "new(new)" will display the Third Party Product Status of new on the output.
150
151 **Subscription Column Field:** This identifies what to output as the header for the subscription type field. Enter the heading that's required for the report output.
152
153 **For Imports**, the Third Party Subscription column identifies what name/value from the import file will be mapped to the ONEcount product.
154
155 * For example, there's a "Subcription Type" column in the import file that identifies the subscription type a particular record has for a particular product. In the import file, "New" in that column indicates the record has a subscription type of new. In ONEcount, the subscription type is "n". Any record in the import file that has a product status of "New" in the subscription type column will have the product status of "n" applied to the record in ONEcount.
156
157 **Subscription Column Field:** This identifies the column header for the subscription type column in the import file. In the above example, you would enter "Subscription Type".
158
159 **__Date Mapping Area__**
160 **For automated data feed reports,** enter the format you would like dates to be exported as. For example MM/dd/yyyy will export as 07/05/2023 and yy/MM/dd will export as 23/07/05. If you want to import time along with date add hh:mm:ss:a along with date. For example MM/dd/yyyy hh:mm:ss:a will be exported as 07/23/2023 11:45:32:AM
161
162 MM : months, dd: days, yyyy: years, hh: hours (12 hour format), HH: hours (24 hour format), mm: minutes, ss: seconds, a:AM/PM
163
164 **Date Column Field:** This identifies what to output as the header for the date field. Enter the heading that's required for the report output.
165
166 **For Imports,** enter the format of dates in the import file. For example, if a date is 07/05/2016, enter the format MM/dd/yyyy. If you want to import time along with date add hh:mm:ss along with date. For example MM/dd/yyyy hh:mm:ss:a will be exported as 07/23/2023 11:45:32:AM
167
168 MM : months, dd: days, yyyy: years, hh: hours (12 hour format), HH: hours (24 hour format), mm: minutes, ss: seconds, a:AM/PM
169
170 **Date Column Field:** This identifies the column header for the date column in the import file.
171
172 When done with mapping, click "Next".
173 You will then be brought either to Activity Mapping or Save, depending on selection made in 1 – Information.**SAVE MAPPING**
174
175 When done with mapping(s), click "Next".
176
177 You then have the option to save the mapping (Save) or return to mapping (Previous).
178
179 Once mapping is saved, you can find it on the Manage Mapping listing page.
180
181 A saved mapping is ready to be used for generating automated feed reports in the Dashboard. See Dashboard Reports – Create Feed Report for more information.
182
183 == **MAPPING ENGAGEMENT DATA** ==
184
185 This section outlines the types of engagement data that DExTr can import/export. Please note that Generic and Engagement data fields can be used for both web, banner and newsletter engagement data.
186
187 If you need data points imported or exported that are not on this list, check with the ONEcount team to see if those fields can be accommodated.
188
189 Generic Engagement Fields
190
191 * Activity Date: Date of the Activity
192 * Activity Time: Time of the Activity, Mandatory for Import.
193 * Channel Codes: Codes for Email, Banner and Web page views, CMI needed it in export.
194 * End Date: End date of export, mainly used in aggregate export.
195 * Unique External ID: Unique ID for each transaction.
196 * Month: Month of the Activity.
197 * OCID:
198 * OCID Hash: Unique hash of the user. This is the cookie set on user’s browser.
199 * Platform Viewed: Platform of the user in which Impression or Page view happened.
200 * Resource Details: This is used for export. It has Page title for Web activity, Banner name for Banner Activity and Message title for Email data.
201 * Start Date: Start date of export, mainly used in aggregate export.
202
203 Metric Engagement Fields
204
205 * Activity Metric code: Metric codes for Activity data. It has codes for Email sent, delivered, opened, clicked, banner impression, banner clicked and page view. It is mandatory for Import and export.
206 * Activity Metric Name: Metric Names for Activity data (CMI report needs Metric code and Name). It has codes for Email sent, delivered, opened, clicked, banner impression, banner clicked and page view.
207 * Activity Metric count: Mainly used for export. It has counts for each activity.
208 * Aggregate Metric code: Metric codes for Aggregate data. It has codes for Email sent, delivered, opened, clicked, banner impression, banner clicked and page view. Mandatory for aggregate export.
209 * Aggregate Metric Name: Metric Names for Aggregate data (CMI report needs Metric code and Name). It has codes for Email sent, delivered, opened, clicked, banner impression, banner clicked and page view.
210 * Aggregate Metric count: Mainly used for export. It has counts for each activity.
211
212 Banner Engagement
213
214 * Ad Server Name: Name of the Ad Server, i.e., DFP
215 * Advertiser ID: ID of the Advertiser
216 * Advertiser Name: Name of the Advertiser
217 * Banner ID: ID of the Banner
218 * Banner Name: Name of the Banner
219 * Campaign ID: ID of the Campaign
220 * Campaign Name: Name of the Campaign
221 * Viewable Impression: If Impression is viewed or not, Boolean (0, 1 accepted)
222
223 Newsletter Engagement Fields
224
225 * Click URL: URL of the Clicked link. Mandatory for Importing Email clicks.
226 * List ID: ID of the newsletter List of Group
227 * List Name: Name of the newsletter list or group
228 * Message ID: Campaign ID of the newsletter
229 * Message Name: Campaign name of the newsletter
230 * Recipient ID: Unique User Id in the Email service provider
231 * Message Subject: Subject of the Campaign
232
233 Web Engagement Fields
234
235 * Referrer: Referrer for the page view
236 * Site name: Website URL
237 * Page URL: URL of the Page.
238
239 == **HOW DExTr MAPS DATA** ==
240
241 The following information is being provided to help you understand how DExTr will map/transform your data on import.
242
243 1. On import, ONEcount will always check if fields in the DExTr mapping are present in the file or not; if any field is not present in the file, then we fail the job. This is to ensure that we won’t miss mandatory data or dedupe fields and possibly corrupt the data. If you are importing data and the file does not have all of the fields in the DExTr mapping you are using, you can create a blank column with that field name in it–//as long as that field is not a de-dupe field for the import.//
244 1. If there are multiple dedupe fields in mapping then we dedupe the data based on order that the fields appear in the mapping. For Example if mapping has dedupe fields as “Email” (1^^st^^ field in order) and “AccountID” (2^^nd^^ field in order) then DExTr will first query the datase for a match on “Email,” if match is not found then it will query on “AccountID”. It always an OR match. You //can not match// on Email AND AccountID.
245 1. If OCID is present as a dedupe field, then it always takes precedence irrespective of order. If you import a file with OCID as a dedupe field, and a record does not have an OCID, then a new user will be created in ONEcount and a unique OCID will be generated.
246 1. There are a variety of validations which can be added to DExTr mapping like, “Numeric”, “Required” etc.. If any record/row doesn’t satisfy these validations, then that record won’t be imported into ONEcount. It will be saved into a failed record file and mailed to the email address supplied with the import job.
247 1. If there are any passwords that are being imported, they should be imported as plain text. ONEcount will encrypt the field based on encrypt function for your installation.
248 1. For Package transactions: Term ID, Package status, source code and transaction type are mandatory.
249 11. If Term ID is not mapped, then the transaction won’t be imported.
250 11. IF Package status is not mapped then default Package status from term will be added.
251 11. If source code is not mapped. Then default source code from import job will be selected.
252 11. If transaction type is not added, then default type from Import job will be selected.
253 1. For Product transaction: Product ID, source code and transaction type are mandatory.
254 11. If Product ID is not mapped, then the transaction won’t be imported.
255 11. If source code is not mapped. Then default source code from import job will be selected.
256 11. If transaction type is not added, then default type from Import job will be selected.
257 1. If Request Date is not mapped or not formatted correctly, then current date will be used as request date.
258 1. If Expiration date is not mapped or not formatted correctly, then expiration date will be calculated based on the duration of the term and the request date.
259 1. For Engagement imports, Activity type is mandatory.
260 1. If Engagement date is not formatted, then current date will be used as activity date.
261 1. If Data and campaign ID, name, List ID and name then they will be imported as well.
262 1. If dedupe is not added/matched for an engagement import then new users will be created in the system just like Users and transaction import.
263
264
265
266
267 ~*~**Remember to SAVE the values mapping, and SAVE the DExTR mapping when you are through.