Exporting data – transactions, user data and hybrid formats

After you’ve run your data export query, or if you are using our Bulk Export API, you can export your data into a spreadsheet to view or manipulate the data outside of the Engaging Networks account.

You can export the data into XLS and CSV (Comma Separated Values) file types. Additionally, the following data formats are available: Transaction, User data and Hybrid. Briefly:

  • The Transaction format is the most detailed export format, and will have a row for each piece of activity by the supporter that is selected by the query. You can get more than one row exported per supporter since each row represents a type of activity.

  • The User data format only contains supporter data columns. You will only get one row per supporter and no information on the transaction. 

  • The Hybrid format will provide you with the list of personal data columns with additional columns that give information of participation in the selected campaigns or answers to selected questions. You will only get one row per supporter and no information on the transaction as this format “rolls up” transactions into a single row.

Read more about these formats below.

Independently of the format you use, the default supporter record fields will be presented exactly as they are set up in Hello > Account settings > Account Data Structure. Alternatively you can create a customized list of fields using the ‘Create export group‘ function in Hello > Account settings > Account Data Structure. Please see the relevant file on that page for further details.

Files larger than 65K rows must be downloaded as CSV.

Downloading data

Once you confirm the export, the process will run in the job monitor until it is ready to be downloaded. Within the monitor, you can check the percentage completed of the export, and when ready download the exported file by clicking the cloud download icon.

Three types of data formats

1) Transaction format

A “transaction” is created when a supporter participates in an action, answers a question or opt-in, donates, opens an email and so on.

In this export format, each transaction selected by the query is displayed as a separate row of data. 

This offers the most detailed and complete data export.

For example, if a single user made a donation and answered two questions on a page, that will result in three rows of data for this specific user: one row providing donation details and two rows providing question answers correspondingly. You would need to include the donation page and questions within the query (if using the query builder) for this to be exported.

The data file is divided into several main sections: basic data, transaction data, external reference fields, origin source, and default supporter record. The transactional data section “reuses” columns to represent different pieces of information depending on the campaign type. 

2) User data format

The “user data” format only provides static default supporter record data of all supporters who fulfilled your selected export criteria. Transactional information and question answers will not be included in the file.

The data will be presented by default in the same format as your default user record, as displayed in the example below:

First name

Last name

Address 1

City

Postcode

Email

Josh

Miller

Engaging Networks

Washington

20036

josh@engagingnetworks.net

James

Seddon

Engaging Networks

London

WC2H 9NJ

james@engagingnetworks.net

3) Hybrid data format (legacy)

In this format, the exported file will combine default supporter record data of all supporters who fulfilled your selected export criteria with some basic transactional information.

Hybrid combines data for each supporter into a single row, with the final columns giving information about the transaction or transactions associated with the query.

Please see the ‘Campaign status values’ table below for explanation of values in the columns related to transactions.

Here is an example Hybrid file:

First name

Last name

City

Postcode

Email

Campaign X

Opt in

Marta

Fornal

London

SW9 7AB

marta@engagingnetworks.net

:R:06/01/2012::P:06/01/2012

Y

In this example, they have taken “Campaign X” twice, which is why two dates are in the single cell. The R means registered, and the P means fully participated.

Transaction format layout

As the most complex and detailed version of the data export, we’ll go into the transaction export format in more detail. This export is split into several sections, detailed below. The titles in bold will appear as column headers.

Transaction format section 1 – basic data

This section gives you basic data identifying the supporter who the record relates to, and by default makes up the first five columns in the file.

Account ID: The unique ID of the subaccount from which the data are being exported.

Supporter ID: A unique ID number identifying the supporter in the database.

Supporter Email: The email address of the supporter the activity record relates to (this acts as a unique identifier in the software).

Date Created: The date on which the user data record for the supporter (not the specific activity record) was first created.

Date Modified: The date of the last occasion on which the data of the supporter was modified. Data is counted as being modified when any transaction is created (for example, taking action, donating, opening an email), if data is imported, if data is updated via the Supporter Lookup tool, if Data Mapping updates a supporter record, and if data is updated via our APIs.

Transaction format section 2 – transactional data

This section contains data relating to the specific activity involving the supporter.

Campaign number: This column gives a unique number that refers to the campaign that the transaction relates to. Note that this is not the same as the page ID you see in page-builder’s URLs. You can search for the page in the system using this number by searching for cid:XXX, e.g. cid:12345
Campaign type: This column identifies which type of activity the record relates to. For campaign type codes, see the table ‘Campaign type codes’ below. Note: Imported QCB updates are not included in export API data.
Campaign ID: This identifies the specific campaign the record relates to. By default this will be the reference name you have given the campaign in your account.
Campaign date and campaign time: These two columns give the specific time and date the supporter’s transaction took place. Note that Engaging Networks displays times in the US Eastern timezone. For email broadcast transactions, the date and time refers to when the page was submitted or when the first email transaction occurred, e.g. Open. Subsequent activity, e.g. a click, does not update the time but will record a click in the relevent column (see Campaign Data columns 1 to 6 on the next pages)
Campaign status: This gives information about whether an activity has been successfully completed or not. The precise value that appears depends on the type of activity. See the ‘Campaign status values’ table below.
Campaign data columns 1 to 35: The content of these 35 columns also depends on the type of activity. Check the attached Excel document for more information.

Transaction format section 3 – external references

With each transaction, you can enable up to 10 external reference fields. These are intended for generic use, but may also be assigned to use to record universal tag manager information. For more details about these fields, please see our article on using external references for campaigns and utm codes

Transaction format section 4 – Origin Source

‘Origin Source’ can be stored in a supporter’s data record to help identify where the record originated from and compare different channels on performance. The Origin Source is not output by default, so needs to be enabled if you wish to see it in your API Options. To set up this feature, please see the following article.

Transaction format section 5 – supporter record

The final section of the data export contains the details of the particular supporter taking the action. The columns that appear correspond to the form fields you have defined in the account data structure (default supporter record) in your account, so the number of columns appended to the export will vary between clients.

Transaction export columns

Campaign type codes

Campaign

Transactional code

Campaign

Transactional code

Email to Target

ETT

Tweet to Target

TWT

Click to call

CTT

Data Capture Form

DCF

Email Subscribe

EMS

Advocacy Petition

PET

Survey

SVY

Ecard

ECF

Email subscription management page

UNS

Broadcast Email Email sends do not generate this transaction. Only email interactions do, e.g. opens, clicks

EBC

Marketing Automation Email

MBC

Opt-ins and checkbox questions

Radio opt-ins are also recorded as QCB.

Note: Imported QCB updates are not included in export API data.

QCB

Question Confirmation

QCS

Question Multiple Response

QMR

Fundraising Credit/Debit Single

FCS

Fundraising Credit/Debit Recurring
A recurring card payment managed by Engaging Networks

FCR

Unmanaged recurring payment
A recurring payment not managed in Engaging Networks e.g. RSM PDD

FUR

Fundraising – Recurring Update 

FRU

Fundraising Bank Single
Payments made using bank to bank methods. Also includes payments made to Donor Advised Funds.  

FBS

Fundraising Bank Recurring

FBR

Fundraising In Memoriam

FIM

Fundraising Cash

FCA

Fundraising Check

FCH

Fundraising One Click

FOC

Refund transaction

RFD

Memberships payments

MSP

Memberships – member association

MMR

Memberships
Legacy only

MEM

Manage Supporters Update

MSU

Manage Supporters Create

MSC

Manual Supporter Update

MSU

Manual Supporter Create

MSC

Legacy Peer-to-peer Registration 

AMR

Legacy Peer-to-peer Credit Single

ACS

Legacy Peer-to-peer Credit Recurring

ACR

Legacy Peer-to-peer Bank Single

ABS

Legacy Peer-to-peer Bank Recurring

ABR

Legacy Peer-to-peer Cash or Check

ACC

Legacy Peer-to-peer Registration Credit Fee

ACF

Legacy Peer-to-peer Registration Bank Fee

ABF

Event Purchase – Credit/Debit Single

ECS

Event Refund Single/Recurring

EFD

Event Purchase – Bank Single

EBS

Event Purchase – Free/Cash tickets

ECC

Event Ticket Purchase

ETK

Event Ticket Attendee

ETA

Premium Item

PTM

Symbolic giving Item

ETM

Manage Supporters Update

MSU

Manage Supporter Create

MSC

Hub Supporter Update

HSU

ENS – Supporter Service Create

SSC

ENS – Supporter Service Update

SSU

SMS Broadcast

SBC

Campaign status values

Campaign type

Value

Description

Campaign type

Value

Description

Email to Target and Tweet to Target

R

Registered – Supporter has completed the first page of the action, but has not submitted the message

P

Participated – Supporter has completed the action and submitted a message to their target

Data Capture Form

P

Participated

Email Subscribe Form

P

Participated

Advocacy Petition

P

Participated

Engaging Networks tell-a-friend

P

Participated

Broadcast email

For a transactional export, the Campaign Status is always R. Campaign data columns 1-6 will contain information on the interactions with the email (open, click, formsub, hbounce, sbounce, unsub)

For a hybrid export, these different values will be contained within the single cell value in the email campaign column. O means message opened, CT means link clicked, SB means message soft bounced, HB hard bounced, U means unsubscribed. 

R

Message received by supporter

Membership

New Membership

This indicates a brand new membership purchase

Renewal Term

The amount of days remaining in the membership

Lapsed

The membership has expired

Question checkbox

Y

Box ticked

N

Box not ticked

Question Confirmation

Y

Box checked

N

Box unchecked

P

Pending – Constituent needs to click the link in the email

D

Declined – The subscription was not approved

Question multiple

Y

Question answered (whatever the response). The response will be 

N

Question left unanswered

Fundraising Credit/Debit Single

Success

The payment was successful

Reject

The payment gateway rejected the payment

Pending

The payment has not yet been verified by the payment gateway

Fundraising Credit/Debit Recurring

Success

The payment was successful

Reject

The gateway rejected the payment

Fundraising Bank Single

Success

The payment was successful

Reject

The gateway rejected the payment

Fundraising Bank Recurring

Success

The payment was successful

Reject

The gateway rejected the payment

Fundraising In Memoriam

Success

The payment was successful

Reject

The gateway rejected the payment

Fundraising Cash/Check

Success

The payment was successful

Reject

The gateway rejected the payment

Peer-to-Peer Credit Single

P

The payment was successful

R

The gateway rejected the payment

Peer-to-Peer Credit Recurring

P

The payment was successful

R

The gateway rejected the payment

Campaign data columns 1 to 35

“Campaign data” columns will contain transaction specific data. See this template of the transactional export file for more details on each transaction type.