DExTr Import Files

Last modified by Admin User on 2024/12/27 09:51

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 (See FULFILLEMENT SAMPLE below), 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.)

Fulfillment Sample: Advantage/Omeda/PCS/CDS

PUBCDE

CUSTNUMB

OCID

CRCSTS

FIRST

LAST

COMPANY

ADD1

ADD2

CITY

ZIPCDE

STATE

ADRCDE

KEYCDE

QUALSRC

EMAIL

CTMDTE

FINDTE

PHONE

LISTIND

County

COUNTRY

AAI

404304

33675

E

FRED

SMITH

 

57 CHURCH ST

 

ROXBURY

67831704

CT

M

TREN68B

5F

 

20161205

20190101

8005551234

NONACTIVE

LITCHFIELD

USA

AIB

445771

 

Q

JOSEPH

JONES

JONES & CO

7632 WILDCAT PIKE

 

NEW BLOOMINGTON

433419532

OH

M

E3CC1220

5F

20201201

20231201

9546783214

QUAL

MARION

USA

AXW

 

3324

G

MARK

ROBERTS

TRF Inc.

5201 WOODWARD AVE

 

DETROIT

482024093

MI

M

RRT6GT

1A

 

20201207

20210401

 

PAID

WAYNE

USA

PUBCODE maps to Package TERM

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.

NOTE: For e-mail engagement activity date, message id, recipient id, activity type, email click url are required.
 

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

Swapcard (Webinar/Virtual Event Platform)

Title

Start date

Format

Topics

Group

Email

First

Last

Title

Company

Is COVID-19 FOR REAL?

2-11-21 12:55 PM

PRE_RECORDED

Other, Human Health, Policy, Business

Premium Members

Ryan

Brown

 

2XXX Group

BREAKING NEWS SESSION: Extreme 

2-17-21 1:15 PM

PRE_RECORDED

 

Premium Members

Ryan

Brown

 

2XXX Group

Trade Show Registration Data

Event Series

Event Edition

Source System

Reg Number

First Name

Last Name

Title

Company 1

Address 1

Address 2

City

State

Zip Code

Country

Phone Number

Fax Number

Email Address

Internet Address 2

Phone Area Code

Phone Number Only

Reg Code

Membership Flag

Member ID

Reg Code 3

Text 1

Credential

Amount Paid

Amount Refunded

Reg Code 2

Source Code

Link Reg ID

Business type:  Check all that apply.

Age range

Primary business

Is this your first time to attend the Expo?

Please keep me up-to-date with the latest events and services

I would like to hear from  directly about products and services

What is your gender?

Buying decisions

Job function

Primary Market

What is your job title/function?

What is your company's primary media type?

Products/services

Do you want to receive a free subscription to PVV Magazine?

Do you want to receive a free subscription to RXY Magazine?

Sales Volume

Do you want to receive a free subscription to our e-newsletter?

What services does your company provide?

Annual Trade Show

EXPO 2022

CSI

1100339

Fred

Jones

Owner

Jones Construction Ent.

690 E Highway 90

690 e HWY 90

Tampa

FL

32425

 

1  8505551001

 

fred@myaddress.com

officemgr@notmyaddress.com

850

5471001

Conference Attendee

Y

412351

 

 

 

396

 

Super Pass

 

1100002

Contractor

51-60

Contractor

No

Yes

 

Male

Yes

Owner/CEO/President/Executive

Both

 

 

This, That, A little of the other, Something Else

Yes

Yes

$1,000,000-$4,999,999

Yes

Roofing, Pool Repair, Driveway Sealing, Electrical Contracting

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Annual Trade Show

EXPO 2022

CSI

1100005

Jim

Nordstrom

Spouse

Nordstrom Chain, LLC

690 E Highway 90

 

Miami

FL

33736

 

1  8502588247

 

jimnordstrom@something.com

 

850

3998247

Attendee Expo Only

N

 

 

 

 

66

 

Expo Only

 

1100002

No Response

41-50

Spouse/Guest

Yes

Yes

 

Male

No Response

Spouse/Guest

 

 

 

Rope, Twine, Chain

No

No

No Response

No

No Response [DB ONLY]