DExTr Import Files

Version 4.1 by Admin on 2022/09/19 12:40

DExTr is a data mapping tool that can be used to transform data coming into or out of ONEcount. DExTr mappings are capable of mapping almost every type of data ONEcount uses. For all of the data types discussed below, ONEcount can both ingest this data from outside sources, and export files like this to be consumed by outside sources.

Below are some sample formats for data coming into ONEcount via DExTr. These samples are meant as examples only. DExTr is capable of manipulating data in multiple formats, and variations in these formats are usually acceptable.

All files should be in CSV format, with fields enclosed in quotes. If you have quotes within the data of a field and do not enclose the fields in quotes, you will have problems importing your data.

There are three main types of data that you can exchange with ONEcount: User Demographic Data, Transactional Data, and Engagement Data.

User Demographic Data

Typically data about the person, including NAME, ADDRESS, CITY, STATE, ZIP, JOB TITLE, E-MAIL, HAIR COLOR, ETC. If the system you are bringing the data in from contains a unique identifier, such as a user or customer ID, it is important to bring that data in so that it can be used for matching and de-duping records later. ONEcount can use ANY field to de-dupe, records, not just e-mail. Typically, records are de-duplicated across different fields depending on the source system (ie., records from your CRM system are de-duped based on the CRM ID, records from your e-mail system may be de-duped on the email system ID).


ACCOUNT NUMBER

FIRST NAME

LAST NAME

MIDDLE NAME

TITLE

SUFFIX

COMPANY

JOB TITLE

ADDRESS1

ADDRESS2

CITY

STATE

ZIP

ZIP4

COUNTRY

TELEPHONE NUMBER

FAX NUMBER

E-MAIL ADDRESS

E-MAIL CONTACT

CUSTOM1

CUSTOM2

CUSTOM3

LAST UPDATED

OCID

14010000431

Graham

O'Gorman




Graham O'Gorman Landscape


192 Olmstead Hill Rd


Wilton

CT

6897

1816

United States




N




20141113211102


14010000449

Dale S

Galbo




Law Offices

Owner

15 W Hill Rd


Bloomfield

CT

6002

5016

United States

2032709821



N




20141113211111


14010000648

Patricia

Cannon




Employee Assistance Program

Director

887 Bridgeport Ave Ste C


Shelton

CT

6484

7602

United States

2039290681



N




20141121163017


14010001843

Susan

Kent






38110 5th St E Apt 1


Los Angeles

CA

93550

2317

United States




N




20141212210537


14010001858

Brendan

Eppley






20 Driftway Ln


Darien

CT

6820

6130

United States




N




20141211230144


14010001862






Orchard Tree Care Llc


184 Ave


Darien

CT

6820


United States

2036550618



N




20150211133156


14010001870

Avis Z

Gray




Designers 5 Incorporated

President

96 5 Mile River Rd


Darien

CT

6820

6234

United States

2036552033



N




20150212210227


14010001889

John

Hertz




Hertz Construction Co

Partner

12 Hillside Ave


Darien

CT

6820

5010

United States

2036564900



N




20141121163018


14010001891






Niche Home Furnishings Ll


25 Old Kings Hwy N Ste 17


Darien

CT

6820

4608

United States

2036566674



N




20141121163018


14010001901

Wendy

Cole


Ms.


Wendy C Cole Interiors

Principal

3 Outlook Dr


Darien

CT

6820

5616

United States

2036552954



N




20150212210227


14010001917

Frank

Stone




Stones Landscape

Owner

258 West Ave


Darien

CT

6820

4111

United States

2036569674



N




20150212210227


14010002429

Bruce

Pomerance


Mr.


P L V Inc

President

1076 Post Rd


Darien

CT

6820

5414

United States

2036554668



N




20150212210227


14010002438

Joanne

Salvatore




Nassau Interests Llc

Member

42 N Meadows Ln


Stamford

CT

6903

5151

United States

2036550303


Y




20141212210537


NOTE: Dates need to be in YYYY-mm-dd format

Sample File:

demo-sample.csv

Transactional Data

Transaction data includes subscriptions, opt-in actions, product purchases, etc. In the case of magazine subscriptions, there is a difference between a Request Date (the date the user subscribed) and the Transaction Date (the date the transaction was entered into the system). In cases where only one date is provided, it will be used as both the Request Date and the Transaction Date.

There are two main types of transactions, Product Transactions and Package Transactions.  A product is an item, like a coffee cup, a gated content area on a web site, a newsletter subscription, etc. A package is a collection of one or more products and one or more Terms. A Term is a time-period and a price, for example 1 year for $50, 2 years for $75, etc. You can read more about these in other parts of this documentation. The main difference for imports and exports is that Products are imported directly, while Packages are imported by their "Term." Each Package must have at least one term, but Packages can have multiple Terms. Term ID #s are unique across the system, so we use them for importing Package transactions.

Again, when you import Transaction Data, it is important to import whatever customer or account ID your remote system has for the user so transactions can be mapped appropriately.

When syncing ONEcount with a remote system like a fulfillment database or e-commerce system on a regular basis, say every day or every week, it is best to import and export both the remote system ID, and the ONEcount ID, or OCID. This will ensure new customers added by either system are handled appropriately.

This will create the following work-flow:

Subscriber ID

OCID

ACTION

YES

NO

User was created by the fulfillment system and is new to ONEcount, create a new record in ONEcount

NO

YES

User was created in ONEcount and is new to the fulfillment system user, create a new record in the fulfillment system

YES

YES

User exists in both systems, update record with incoming data


Transaction Data Sample:

SubscriberID

ProductID/code

RequestDate

ExpirationDate

TransactionType

Quantity

Price

SourceCode

StatusCode

14010114326

3345

2015-02-05

2016-02-05

N

1

15



14010114326

243

2015-02-04

2016-02-04

R

1

24.95



14010114326

107

2015-02-04

2016-02-04

R

1

13



14010116098

4215

2015-02-08

2015-02-08

U

1




Transaction type must be one of the following values:

N - New (includes product purchases)

R - Renew

U - Unsubscribe

Source Code: Source Codes will map to ONEcount Source Codes

Status Code: This for subscriber status changes (Current Subscriber, Expired, Pended Add, etc.)


Engagement Data

Engagement Data covers a broad range of activities, including banner views and clicks, e-mail opens and clicks, webinar attendance, video plays, etc. The key metric for ALL of these engagement data types is: the user who did it, the date/time of the engagement, and the engagement activity itself (open, click, view, etc.) Other data can be added to these records as well, including the name of the advertiser or banner campaign, the subject of the e-mail campaign, etc.  This data is important to each engagement and can be imported into ONEcount using DExTR.

Here are a couple of examples:

E-Mail Engagement (combined, from Eloqua)

ActivityDate

ActivityType

AssetId

AssetName

CampaignId

ContactId

EmailAddress

EmailClickedThruLink

EmailRecipientId

ExternalId

SmtpErrorCode

SmtpMessage

SmtpStatusCode

VisitorId











































ContactID is an Eloqua-specific ID 

VisitorID is a custom field in the e-mail system containing the user's GUID from the customer's internal data lake

E-mail Engagement (Split Files)

email-clicks.csv:

ActivityDate

ActivityType

MessageID

MessageName

MessageSubject

ListID

ListName

OCID

LinkURL

LinkID

Firstclick

Latestclick

clicked














email-opens.csv

ActivityDate

ActivityType

MessageID

MessageName

MessageSubject

ListID

ListName

OCID









email-sends.csv

ActivityDate

ActivityType

MessageID

MessageName

MessageSubject

ListID

ListName

OCID










LiveIntent Data Sample:

Liveintentuserid

Advertiserid

Campaignid

Creativeid

Devtype

Emailasmdfive

Eventtype

Lineitemid

Liveintentdecisionid

Publisherid

Sectionid

Templateid

Timestamp

28427698

39771

246944

787604

PC

d57dccaadbba6c4ad8f143de8c571771

impression

10001043

66233848-4580-3d7f-92b2-beb4741e8f7

36479

253729

19263

2021-03-10

28422453

39771

246944

787604

PC

2dece7448530fdf799254a875ebe0a77

impression

10001043

b8c41396-b896-333d-9945-522c21174e2

36479

277132

19263

2021-03-10

28421392

39771

246944

787604

PC

a4c0388d679348e467d27391b3fd456c

impression

10001043

844ebc51-0a02-3783-99c8-8ac96a29bc3

36479

277132

19263

2021-03-10

28499279

39771

246944

787604

PC

96032eb8da86e394b23c2a9647477d66

impression

10001043

69a0d6fd-7cb3-301d-b24a-04144d9ec74

36479

253729

19263

2021-03-10

28421344

39771

246944

787604

PC

fd2d53dbe2e7aa2198c19afce5a35cc6

impression

10001043

75c72eca-6cf1-3144-b00c-04ca49471363

36479

253729

19263

2021-03-10


BrightCove Data Sample:

Contactid

Country

Createdat

Device

Id

Metriccode

Pageurl

Playerid

Timewatched

Updatedat

Utmcampaign

Utmmedium

Utmsource

Videoid

Videoname

Watched

3B07C282-170D-46C4-B8CC-AAADFD

United States

2021-02-11

desktop

309529

V1

https://preview.CUSTOMER.com/education-lab/2019/01_january/treatment-considerations-for-symptomatic-neurogenic-orthostatic-hypotension/video

Sy80k7Mtg

0

2021-02-11




5982181673007

What is symptomatic neurogenic orthostatic hypotension?

0

228DA837-9358-43AB-A6E6-DFSF

United States

2021-01-25

desktop

306311

V1

https://www.CUSTOMER.com/education-lab/2020/07_july/in-the-treatment-of-ted-its-time-for-tepezza/video

Sy80k7Mtg

1

2021-01-25




6171615690004

TED Risk Factors & Symptoms

1

1EFC18BF-E9A4-44D9-849F-FDSFS

United States

2021-01-29

desktop

307009

V1

https://preview.CUSTOMER.com/education-lab/2020/07_july/in-the-treatment-of-ted-its-time-for-tepezza/video

Sy80k7Mtg

1

2021-01-29




6171615690003

TED Risk Factors & Symptoms

1

228DA837-9358-43AB-A6E6-DFASFDASF

United States

2021-02-12

desktop

309633

V1

https://preview.CUSTOMER.com/education-lab/2020/07_july/in-the-treatment-of-ted-its-time-for-tepezza/video#isi

Sy80k7Mtg

0

2021-02-12




6171632240005

TED Pathophysiology

0

5835554F-9B8A-4BA2-BB31-FDSAF

United States

2021-02-09

desktop

309052

V1

https://www.CUSTOMER.com/education-lab/2020/06_june/droxidopa-for-the-treatment-of-symptomatic-noh/video?utm_source=selligent&utm_medium=email&utm_campaign=cardiology educationlab&autostart=1&playlistVideoId=6185538439001&M_BT=4838969175605

Sy80k7Mtg

40

2021-02-09

cardiology educationlab

email

selligent

6185538439007

Chapter 5 — NORTHERA Safety Results

19

5835554F-9B8A-4BA2-BB31-68C51F5632F7

United States

2021-02-09

desktop

309058

V1

https://www.CUSTOMER.com/education-lab/2020/06_june/droxidopa-for-the-treatment-of-symptomatic-noh/video?utm_source=selligent&utm_medium=email&utm_campaign=cardiology educationlab&autostart=1&playlistVideoId=6185538439001&M_BT=4838969175605

Sy80k7Mtg

10

2021-02-09

cardiology educationlab

email

selligent

6185538439004

Chapter 5 — NORTHERA Safety Results

5

5835554F-9B8A-4BA2-BB31-68C51F5632F7

United States

2021-02-09

desktop

309064

V1

https://www.CUSTOMER.com/education-lab/2020/06_june/droxidopa-for-the-treatment-of-symptomatic-noh/video?utm_source=selligent&utm_medium=email&utm_campaign=cardiology educationlab&autostart=1&playlistVideoId=6185538439001&M_BT=4838969175605

Sy80k7Mtg

30

2021-02-09

cardiology educationlab

email

selligent

6185538439008

Chapter 5 — NORTHERA Safety Results

14

ContactID is a GUID assigned by the Customer's Internal Data Warehouse


Raw Web Impressions

VisitContactId

EventTime

PlatformViewed

RefererID

SessionId

SiteName

Url

59DA834C-803E-4C14-9C49-FDSA

2021-03-09 01:35:12

mobile


2407411196308715909161529690620210309

www.CUSTOMER.com

http://www.CUSTOMER.com/endocrinology/education-lab/ckd-knowledge-challenge/interactive/drivers-of-ckd-progression

D0717BB2-CCDB-4DBE-ADD6-REWQREQW

2021-03-06 01:43:53

tablet


2419995085411609985161503823320210306

www.CUSTOMER.com

http://www.hCUSTOMER.com/endocrinology/education-lab/ckd-knowledge-challenge/interactive/inflammation-and-fibrosis

1AFE97E9-9931-4427-9C4E-VZCXVZ

2021-02-22 11:04:20

tablet


3663669472315156695161403488520210222

www.CUSTOMER.com

http://www.CUSTOMER.com/endocrinology/education-lab/ckd-knowledge-challenge/interactive/inflammation-and-fibrosis

59DA834C-803E-4C14-9C49-YHDGT

2021-03-09 01:35:14

mobile


2407411196308715909161529690620210309

wwwCUSTOMER.com

http://www.CUSTOMER.com/endocrinology/education-lab/ckd-knowledge-challenge/interactive/drivers-of-ckd-progression

31C5F8C6-3EFF-4DE7-A899-JGVMHGY

2021-02-25 05:11:38

mobile


8233752448896666794161427278020210225

www.CUSTOMER.com

http://www.CUSTOMER.com/endocrinology/education-lab/ckd-knowledge-challenge/interactive/scope-of-ckd-in-t2d

68E8551F-AD4E-4AAF-B4E6-RTSDFSAFA

2021-03-15 09:57:00

desktop



www.CUSTOMER.com

http://www.CUSTOMER.com/endocrinology/education-lab/ckd-knowledge-challenge/interactive/scope-of-ckd-in-t2d?ecp=~contactid~

VisitContactID is a GUID assigned by the Customer's Internal Data Warehouse