Epoch.com Data Plus integration guide

To provide you with the transaction data (containing new joins, cancellations, credits, chargebacks, recurrings, conversions ) on 15 minute delayed basis.
The data can be sent to you in a variety of ways.
Database of your choice ( e.g. MySQL )
A script using HTTPS protocol (e.g https://www.yourdomain.com/dataplus.php)
SSH
SFTP

Database setup:

You will need to create a table in accordance with the table schema below.

CREATE TABLE EpochTransStats (
ets_transaction_id int(11) NOT NULL default '0',
ets_member_idx int(11) NOT NULL default '0',
ets_transaction_date datetime default NULL,
ets_transaction_type char(1) NOT NULL default '',
ets_co_code varchar(6) NOT NULL default '',
ets_pi_code varchar(32) NOT NULL default '',
ets_reseller_code varchar(16) default 'a',
ets_transaction_amount decimal(10,2) NOT NULL default '0.00',
ets_payment_type char(1) default 'A',
ets_username varchar(32) default NULL,
ets_ref_trans_ids int(11) default NULL,
ets_password_expire varchar(20) default NULL,
ets_email varchar(64) default NULL,
PRIMARY KEY (ets_transaction_id),
KEY idx_reseller (ets_reseller_code),
KEY idx_product (ets_pi_code),
KEY idx_transdate (ets_transaction_date),
KEY idx_type (ets_transaction_type)
)

Create a username (e.g epoch_stats) with select, insert privileges on the EpochTransStats table allowing %.epoch.com (anywhere from epoch.com) server.
Provide us with the IP of the database server and the database name, username and the password.
Also you will need to open your firewall to allow our class Cs (https://epoch.com/ip_list.php) to connect to the IP/port of your database server that we will be writing to.

HTTPS setup:

The data is posted in standard name value pairs.
The names of the parameters are:

  • ets_transaction_id
  • ets_member_idx
  • ets_transaction_date
  • ets_transaction_type
  • ets_co_code
  • ets_pi_code
  • ets_reseller_code
  • ets_transaction_amount
  • ets_payment_type
  • ets_username
  • ets_ref_trans_ids
  • ets_password_expire
  • ets_email

Provide us with the URL (script path), allow our class Cs to post the data.

SSH/SFTP setup

Provide us with the IP, port, username, and password to transfer/upload a file.
The file will be named:
EpochTransStats_mmddyyyyhhMMss.txt where mmddyyyyhhMMss is the timestamp when the file was generated (e.g 05132006121704 meaning that the file was generated on 13th May 2006 @ 12:17:04 PM)
The file will contain a header record stating the column names and one line per record.

ets_transaction_id|ets_member_idx|ets_transaction_date|ets_transaction_type|ets_co_code|ets_pi_code|ets_reseller_code|ets_transaction_amount|ets_paymen_type|ets_username|ets_ref_trans_ids|ets_password_expire|ets_email

Pipe (’|’) is used to delimit the values.
Once you have the setup ready, please contact support@epoch.com to manually test and verify your setup. Upon verification, your information can be setup to automatically transfer data.

Transaction Type Codes

  • C = Credit to Customers Account
  • D = Chargeback Transaction
  • F = Initial Free Trial Transaction
  • I = Standard Initial Recurring Transaction
  • N = NonInitial Recurring Transaction
  • O = NonRecurring Transaction
  • S = One Time Re-Load Transaction
  • T = Initial Paid Trial Order Transaction
  • U = Initial Trial Conversion
  • X = Returned Check Transaction
  • A = Adjustments Initiated Internally / Rejected Check Transaction

Payment Method Codes

  • V = Visa
  • M = MasterCard
  • O = Other
  • B = ACH

ets_ref_trans_ids : Is only populated if the transaction is of type (C,D,X,A) and contains the original transaction ID that was credited, chargedback, returned or adjusted respectively.

Member Cancel Stats:

The cancellation stats can also be received on a periodic basis by using MemberCancelStats.
Currently this only works with MySQL database. To do a setup you will need to create a table named MemberCancelStats, as per the schema defined below:

CREATE TABLE MemberCancelStats (
mcs_or_idx int(11) NOT NULL default '0',
mcs_canceldate datetime default NULL,
mcs_signupdate datetime default NULL,
mcs_cocode varchar(16) NOT NULL default '',
mcs_picode varchar(32) NOT NULL default '',
mcs_reseller varchar(16) default NULL,
mcs_reason varchar(64) default NULL,
mcs_memberstype char(1) default NULL,
mcs_username varchar(32) default NULL,
mcs_email varchar(64) default NULL,
mcs_passwordremovaldate datetime default NULL,
PRIMARY KEY (mcs_or_idx)
)

We strongly recommend creating indexes on this table as per your business and technical requirements.