Legacy Data Import

Outline

When migrating from another CRM or Gateway you will need to get in contact with the CRM/Gateway that you are running the billings through to let them know that you will be migrating your customers to a new CRM. Let them know that you will need the card numbers and/or rebill tokens to validate our rebill requests (dependent on how the gateway operates).

Once you receive the data from the gateway, you will need to adjust their file so that it contains the headers and data that the Konnektive import is expecting to see. Follow the instructions below to determine which files/data you will need to prepare.

Please be aware that the are the merchantId, campaignId, and productId are all required values on the Transactions file, and these values are found within your Konnektive CRM. The merchantId is the ID found on the gateway list, the campaignId is the ID found on the campaign list, and the productId is the ID found on the offers/upsells INSIDE the campaign.

When you are ready with the file (or you can do this sooner), please place a request to our migration team as that is the team who will pull in the data. They are also a great resource if you have any questions about the files.

When it has been determined that the file is ready, my team will send SFTP credentials for the data to be posted into – where then we will pull it from the SFTP and into Konnektive. When the data has been pulled into Konnektive we will request that you look at the imported customers to ensure everything is looking as it should. We will also ask for you to Force Bill a few customers (force the next rebill) so we can confirm that the transaction reaches the gateway properly. It is typically suggested to do a smaller sample file first – and then we can proceed with uploading the rest after it is confirmed that the sample upload was successful.

Once it has been confirmed that everything is looking good on the imports, and after coordinating with you to ensure the billings in the old CRM/Gateway have been stopped – the last step is for the migration team to enable those migrated customers so that they get picked up on the rebill task moving forward.


Instructions

Legacy Data can be imported into Konnektive via .csv formatted import files.

Konnektive CRM staff will provide support and guidance on creating the file, however, Konnektive CRM staff will not be responsible for manipulating data or modifying files. The accuracy of the data provided in the import files is 100% the responsibility of the Konnektive client. Any request to modify data that was imported as defined in the import files , may result in a billable fee.

It is absolutely critical that the file format provided matches in terms of:

  • Data Type - The data type must match to the specifications defined below. If data format does not meet the specifications, the import will fail.

  • Required - Required fields must be provided in the file. If they are not provided, the import will fail.

Some Common data things to look for:

1. .csv file format is required:
2. dateCreated field needs to be modified to be: (YYYY-MM-DD HH:MM:SS)
3. isActiveRecurring column needs to be defined if they are providing an active account and specifying the next bill date
4. responseType can only be one of these two options: SUCCESS or DECLINE (Exact case and exact spelling.)
5. responseText can only be one of these two options: SUCCESS or DECLINE (Exact case and exact spelling.)
6. billShipSame column needs to be defined.



Below are the formats for the 3 possible files involved in the process:

  • Transactions*

  • Customer Notes

  • Additional Customer Details

*Required

Depending on the information you intend to retain, you may choose to import customer notes and additional customer details.



- Transactions - The transactions file is the most important file. This holds information on all the individual transactions that have been issued against customer cards. It also contains data related to recurring continuity and upcoming billing dates as well was tracking numbers related to order shipments.

Field Definitions:

Col

Name

Type

Required

Description

Col

Name

Type

Required

Description

A

customerId

varchar(20)

Y

Your internal customer id for this customer

B

orderId

varchar(20)

Y

Your internal order id for the order associated with this transaction

C

dateCreated

datetime (YYYY-MM-DD HH:MM:SS)

Y

The timestamp of the transaction

D

campaignId

integer

Y

The Konnektive campaignId that indicates which campaign to import this order into.

E

productId

integer

Y

The Konnektive campaign productId that indicates which product within the campaign, the transaction was for. (Found on the campaign page)

F

isActiveRecurring

bit
0=false
1=true

Y

Whether this transaction was made on a currently active continuity product

G

nextBillDate

date (YYYY-MM-DD)

Y - IF isActiveRecurring=1

The next scheduled billing date for the active continuity product

H

paySource

ENUM (CREDITCARD, CHECK, or PREPAID)

Y

How the customer paid for the product

I

cardNumber

integer (max 19)

Y - IF paySource=CREDITCARD

Full credit card number used for payment

J

cardExpiryMonth

integer (MM)

Y - IF paySource=CREDITCARD

Month in which the card expires (must include leading zeros)

K

cardExpiryYear

integer (YYYY)

Y - IF paySource=CREDITCARD

Year in which the card expires (must be 4 digits)

L

achRoutingNumber

integer

Y - IF paySource=CHECK

Routing number of the customer's bank

M

achAccountNumber

integer

Y - if paySource=CHECK

Account number of the customer's bank account

N

achAccountType

ENUM
(SAVINGS or CHECKING)

Y - IF paySource=CHECK

Bank Account Type

O

merchantId

integer

Y

Konnektive Id for the merchant account the transaction was billed against

P

merchantTxnId

varchar(30)

Y

transactionId returned by the merchant processor

Q

txnType

ENUM (SALE, AUTHORIZE, CAPTURE, REFUND)

Y

The type of transaction

R

subTotal

decimal

Y

product price before shipping and taxes applied

S

shipTotal

decimal

Y

shipping charged for the order (total amount charged for shipping & handling)

T

salesTax

decimal

Y

sales tax charged for the order

U

totalAmount

decimal

Y

Sum of salesTax / subTotal / shipTotal (Must add up correctly)

V

responseType

ENUM (SUCCESS or DECLINE)

Y

Result of the transaction, was it successful?

W

responseText

ENUM (SUCCESS or DECLINE)

Y

Any additional response information returned by the processor

X

authCode

varchar(20)

N

Authorization Code returned by the processor

Y

parentTxnId

varchar(30)

Y - If txnType=REFUND or txnType=CAPTURE

The parent transaction against which a refund or capture transaction was run

Z

firstName

varchar(30)

Y

Customer's first name

AA

lastName

varchar(30)

Y

Customer's last name

AB

address1

varchar(30)

Y

Billing Address Line 1

AC

address2

varchar(30)

Y

Billing Address Line 2

AD

city

varchar(30)

Y

Billing Address City

AE

state

varchar(30)

Y - If country=US or country=CA

Billing Address State

AF

country

char(2)
-standard ISO 2 country codes

Y

Billing Address Country

AG

postalCode

varchar(20)

Y

Billing Address Postal Code

AH

phoneNumber

numeric (no dashes or parentheses)

Y

Primary Phone number of customer

AI

homePhone

numeric (no dashes or parentheses)

N

Home telephone number

AJ

cellPhone

numeric (no dashes or parentheses)

N

Cellphone number

AK

workPhone

numeric (no dashes or parentheses)

N

Work telephone number

AL

emailAddress

emailAddress (must be valid format)

Y

Customer's email address

AM

billShipSame

bit
0=false
1=true

Y

Whether or not the shipping address is the same as the billing address

AN

shipFirstName

varchar(30)

N

Shipping First Name

AO

shipLastName

varchar(30)

N

Shipping Last Name

AP

shipAddress1

varchar(30)

Y - If billShipSame=0

Shipping Address Line 1

AQ

shipAddress2

varchar(30)

Y - If billShipSame=0

Shipping Address Line 2

AR

shipCity

varchar(30)

Y - If billShipSame=0

Shipping Address City

AS

shipState

varchar(30)

Y - If country=US or country=CA

Shipping Address State

AT

shipCountry

char(2)
-standard ISO 2 country codes

Y - If billShipSame=0

Shipping Address Country

AU

shipPostalCode

varchar(20)

Y - If billShipSame=0

Shipping Address Postal Code

AV

affId

varchar(20)

N

Konnektive affId as defined in the CRM

AW

subAffId

varchar(20)

N

Sub affilaite id associated with order

AX

shipDate

date (YYYY-MM-DD)

N

Date on which the order was shipped

AY

shipCarrier

varchar(20) (ex. USPS, FedEx, etc)

N

Carrier responsible for shipment

AZ

shipMethod

varchar(20)

N

Carrier's shipment method (ex. Express, 3-day Ground, etc)

BA

trackingNumber

varchar(20)

N

Order Tracking # assigned by the carrier

BB

isChargedback

Boolean('0' or '1')

N

Indicates that the specific transaction has been charged back by the card holder.

BC

chargebackTotal

Decimal (xx.xx format)

Y-if isChargedback

The amount of the chargeback.

BD

chargebackDate

Date (YYYY-MM-DD)

Y-if isChargedback

The date on which the chargeback was issued.

BE

chargebackReasonCode

varchar(10)

Y-if isChargedback

Card issuer's chargeback code.

BF

externalToken

varchar(50)

N

A token identifying the customer payment source at the 3rd party gateway. This is commonly used to run existing rebills when the full card numbers are unavailable and when gateway-supported. -note: if provided, cardNumber column may be masked with either x's or asterisks.

BG

quantity

integer

N

The quantity of the product.  If not provided or not greater than 0, then it will be set to 1.

BH

variantDetailId

integer

N

The Konnektive variantDetailId that indicates which variant of the product the transaction was for. (Found on the product page).  The variant must exist on the Konnektive product.

BI

password

varchar(30)

N

Membership password for a third-party site

BJ

companyName

varchar(50)

N

Customer's company

*NOTE: The final file should be a .csv file. We highly recommend quoting all cells to avoid formatting issues with strings that contain commas or quotation marks. Pay extra attention to the Card Number field as spreadsheet files have a bad habit of truncating card numbers to 15 significant digits.


- Customer Notes - The Customer Notes file contains exactly that -- notes on customer service actions that affect customer orders.

Field Definitions:

Col

Name

Type

Required

Description

Col

Name

Type

Required

Description

A

customerId

varchar(20)

Y

Your internal customer id for this customer.
This must match the customerId in the transactions file

B

dateCreated

datetime (YYYY-MM-DD HH:MM:SS)

Y

the date the note was taken

C

agentName

varchar(20)

N

Name of the individual customer service agent.
(Leave empty for system notes)

D

noteText

varchar(500)

Y

The note itself



- Additional Customer Details - So you store as much information about every customer as possible? We do to! This file is for all the additional information you collect on customers, such as custom fields, club memberships and website username / passwords.

Field Definitions:

Col

Name

Type

Required

Description

Col

Name

Type

Required

Description

A

customerId

varchar(20)

Y

Your internal customer id for this customer.
This must match the customerId in the transactions file

B

loginId

varchar(30)

N

loginId or username for a third-party site

C

password

varchar(30)

N

password for a third-party site

D

custom1

varchar(50)

N

standard custom field 1

E

custom2

varchar(50)

N

standard custom field 2

F

custom3

varchar(50)

N

standard custom field 3

G

custom4

varchar(50)

N

standard custom field 4

H

custom5

varchar(50)

N

standard custom field 5



Providing the files to Konnektive:

  • Since these files contain credit card numbers and bank account numbers, they cannot be transferred over email. Your Konnektive Legacy Import Specialist will provide you with a secure sftp account to which to can post the files. Once your files are transferred to the Konnektive sftp site, the Konnektive team will work with you to import your data into your CRM instance.

PGP Key found here