Download and import event data for event destination endpoints

Prerequisites

To use the Acoustic Exchange Toolkit to download event data from Acoustic Exchange, update certain toolkit configurations and run various scripts in the bin folder of the Acoustic Exchange Toolkit installation. Some scripts run as scheduled jobs.

Event destination endpoints are also called event subscribers or event consumers. Run the Acoustic Exchange Toolkit eventsDownload and eventsImport scripts to perform the following tasks.

• Download event data from Acoustic Exchange
• Import event data into a specified local database

The scripts call Acoustic Exchange public APIs. Before you begin, contact Acoustic Exchange to verify the URL that Acoustic Exchange assigned to your Acoustic Exchange account for submitting API calls.

The Acoustic Exchange Toolkit provides an example mapping file that you can use to specify how to add downloaded event data into a local database. Modify the mapping file as necessary to match your local tables. Adding the event data to a local table makes it available for analysis and contact retargeting.

Step-by-step

Downloading events from Acoustic Exchange Toolkit provides the eventsDownload script to download event data from Acoustic Exchange to a to a file in your local environment. You can run the script manually or as a scheduled job.
Log in to Acoustic Exchange to confirm that you have configured an event subscription to the destination endpoint.

The eventsDownload script calls the v1/eventfiles API to download the events files. Calling the API initiates a secure file download. The event data is downloaded in JSON format to the directory that you specify in the lzocal.download.dir property in config.properties.

The Acoustic Exchange Toolkit transforms the downloaded JSON object into a TSV file.

The default local download directory is $CU_HOME/AppData/eventsDownload, but it can be customized in the config.properties file.

1. In the conf directory, verify the following settings in the config.properties file.
   ubx.api.service.url=http://:. The service URL for Acoustic Exchange public APIs. The path must include the server and port that Acoustic Exchange assigns to your Acoustic Exchange account.

ubx.endpoint.eventfiles.numFilesInList= The number of files in the list returned when the eventfiles API is called.
local.download.dir=%CU_HOME%/AppData/eventsDownload. The directory where the downloaded files are saved.
dbinsert.keep.processed.files=true. If set to true, after event import, the TSV file are moved to the dataProcessed directory. If set to False, after import, the TSV file is deleted and the JSON is not moved.

2. From the %CU_HOME%/bin folder, run eventsDownload.bat [-c ] or eventsDownload.sh [-c ] By default, Acoustic Exchange downloads the event data to %CU_HOME%/AppData/eventsDownload.

Use the –c option to define alternate configurations. When you specify this option, properties in the alternate configuration file override the toolkit default configuration. Specify the path to the alternate location and file name. Do not rename the default properties file.

The Acoustic Exchange toolkit downloads event data from Acoustic Exchange in JSON format. When the event data has finished downloading, the eventsDownload script converts JSON objects to TSV format.

After the eventsDownload script finishes downloading the event data, the event files are deleted from the Acoustic Exchange server.

Acoustic Exchange event types

Acoustic Exchange defines several types of recognized events and organizes them into classes that are based loosely on the channel in which the events are observed. Acoustic Exchange also recognizes custom events that are defined by Acoustic Exchange Business Partners, but only after consultation and agreement on the event name, event code, and support for event attributes. Such consultation helps ensure that Acoustic Exchange can correctly recognize and process the data that the custom event promises to deliver.

For more information about Acoustic Exchange event types, see the Acoustic Exchange event type directory and Dynamic Event Library.

JSON format for download event data

The Acoustic Exchange events are serialized JSON objects in the downloaded files. Each file can contain data for multiple events.

The Acoustic Exchange Toolkit downloads event data in the following JSON format.

{
 "provider" : "<string>",
 "source" : "<string>",
 "channel" : "<string>",
 "x1id" : "<string>",
 "identifiers" : 
 [
 { "name" : "<string>","value" : "<string>" },
 { "name" : "<string>","value" : "<string>" }
 ],
 "events" : 
 [
 {
 "code" : "<string>",
 "timestamp" : "<timestamp>",
 "attributes" : 
 [
 {"name" : "<string>", "value" : "<value>", "type" : "<type>" } ,
 {"name" : "<string>", "value" : "<value>", "type" : "<type>" } 
 ] }
 ] 
}

After it downloads the event data, the eventsDownload script transforms the JSON object into a TSV file.

The first line in the TSV file contains the header names. The identifiers and attributes might be different for different event types, but all identifiers and attributes are added to the header. The data value of the identifier or attribute that does not belong to an event code is blank for that row.

The following table illustrates an example of the relationship between identifiers and attributes in the JSON to the first line of the TSV file.

The following JSON sample and table illustrate how Acoustic Exchange maps the JSON data to a tab-separated file.

{
 "provider": "Acoustic",
 "source" : "Digital Analytics",
 "channel": "Web",
 "x1Id": "81147632-a3fe-43a5-b0c8-6289ca9302bc"
 "identifiers": 
 [
 { "name": "email", "value": "[email protected]" },
 { "name": "DA_cookie_id", "value": "098098-adfsfd-9323ad-78sdfs" }
 ],
 "events": 
 [
 {
 "code" : "cartPurchase",
 "timestamp": "2015-03-18T13:55:06+00:00",
 "attributes": 
 [
 { "name": "productId", "value": "d12345" },
 { "name": "productName", "value": "Widget ABC" },
 { "name": "basePrice", "value": "200.00", "type" : "number" },
 { "name": "quantity", "value": "2", "type" : "number" } 
 ] },
 {
 "code" : "cartPurchase",
 "timestamp": "2015-03-18T13:55:06+00:00",
 "attributes": 
 [
 { "name": "productId", "value": "d123465" },
 { "name": "productName", "value": "Widget 123" },
 { "name": "basePrice", "value": "13.00", "type" : "number" },
 { "name": "quantity", "value": "1", "type" : "number" } 
 ] }
 ]}

Structure of the corresponding TSV file.

Importing event data into a database
Run the eventsImport script to add event data from the TSV file that is created by the eventsDownload script to a local SQL database. You can run the script manually or as a scheduled job.

You can insert a single file or a single directory into the database. The Acoustic Exchange Toolkit provides a mapping file that you use to specify how the event data is added to the database.

A. The Events Downloader calls the Acoustic Exchange HTTP API to download the event data from Acoustic Exchange and stores the data as a tab-separated (TSV) file in the client file system. Acoustic Exchange provides events files in JSON format. The Events Downloader component of the Acoustic Exchange Toolkit converts the JSON to a tab-separated value (TSV) format. The JSON data and the .tsv file are stored on the client.

B. The Events DBImporter processes the TSV file and inserts individual events into the respective database tables, based on the event type.

C. Use the sample mapping file to specify how the events data is stored in the local database.

Procedure

Run eventsImport.bat -m -i -f [-c -j

For Linux or UNIX, run eventsImport.sh -m -i -f [-c -j

Required parameters

-m The default mapping file is EventsDBTableMappings.
-i Path to the input TSV file. If you specify a single .tsv file with the -i option, only that file is processed and the -f option will be ignored, even it is used.
-f Path to the folder that contains the TSV files to be processed. If you specify a directory with the -f option, all .tsv files in that directory are imported.
Optional parameters

-c Use to define alternate configurations. When specified, properties in this file override the default configuration. Specify the path to the alternate location and file name. Do not rename the default properties file.
-j Use to define an alternate data source. When specified, properties in this file override the default configuration. Specify the path to the alternate location and file name. Do not rename the default properties file.

Results
After the eventsImport script imports the event data to the specified database, the script moves the TSV files to the $CU_HOME/AppData/dataProcessed directory. The dataProcessed folder is created under the downloads folder that you specify in config.properties.

Sample database script for database table creation

The Acoustic Exchange Toolkit provides a sample SQL script for creating the local database tables that you can use to store downloaded events data.

In the $CU_HOME/ddl directory, the Exchange Toolkit provides a sample script for MSSQL, DB2 and Oracle. The table and field names can be used with the sample mapping file.

You can use this sample script as a solution that is ready for immediate use and that you can modify as needed to meet your business need.

If you are upgrading from an earlier version of the Acoustic Exchange Toolkit, you can use upgrade scripts that are provided in the ddl directory.

The toolkit also provides an example mapping file to match event data to the field names in the tables that you create with the sample EventToDBTableMapping.xml file.

Events data to database table mapping

The Acoustic Exchange Toolkit provides the EventDBTableMapping.xml file. You can use this file to map the fields in each event type to the columns in the event type database table. A sample file is located in the $CU_HOME/mapping folder.

You can customize the sample file as needed. You can map multiple event codes to the same database tables. Event identifiers or attributes that are not mapped are ignored.

The following example of how a recognized event is represented in the sample mapping file is based on the Cart Purchase recognized event.

<?xml version="1.0"?>
<EventToDBTableMappings>
 <TableMapping>
		<EventCode>ibmcartPurchase</EventCode>
		<Table>UBX_IBMCARTPURCHASE</Table>
		<Column>
			<Name>Provider</Name>
			<EventField>provider</EventField>
		</Column>
		<Column>
			<Name>EndpointSource</Name>
			<EventField>source</EventField>
		</Column>
		<Column>
			<Name>Channel</Name>
			<EventField>channel</EventField>
		</Column>
		<Column>
			<Name>X1ID</Name>
			<EventField>x1id</EventField>
		</Column>
		<Column>
			<Name>Email</Name>
			<EventField>identifier_email</EventField>
		</Column>
		<Column>
			<Name>Cookie</Name>
			<EventField>identifier_cookieid</EventField>
		</Column>
		<Column>
			<Name>EventCode</Name>
			<EventField>event_code</EventField>
		</Column>
		<Column>
			<Name>EventTimeStamp</Name>
			<EventField>event_timestamp</EventField>
		</Column>
		<Column>
			<Name>EventNameSpace</Name>
			<EventField>event_namespace</EventField>
		</Column>
		<Column>
			<Name>EventVersion</Name>
			<EventField>event_version</EventField>
		</Column>
		<Column>
			<Name>EventName</Name>
			<EventField>event_name</EventField>
		</Column>
		<Column>
			<Name>EventDescription</Name>
			<EventField>event_description</EventField>
		</Column>
		<Column>
			<Name>OrderID</Name>
			<EventField>event_attribute_orderid</EventField>
		</Column>
		<Column>
			<Name>InteractionID</Name>
			<EventField>event_attribute_interactionid</EventField>
		</Column>
		<Column>
			<Name>OrderSubTotal</Name>
			<EventField>event_attribute_ordersubtotal</EventField>
			<EventFieldType>number</EventFieldType>
		</Column>
		<Column>
			<Name>OrderShipping</Name>
			<EventField>event_attribute_ordershipping</EventField>
			<EventFieldType>number</EventFieldType>
		</Column>
		<Column>
			<Name>OrderDiscount</Name>
			<EventField>event_attribute_orderdiscount</EventField>
			<EventFieldType>number</EventFieldType>
		</Column>
		<Column>
			<Name>OrderPromo</Name>
			<EventField>event_attribute_orderpromo</EventField>
			<EventFieldType>number</EventFieldType>
		</Column>
		<Column>
			<Name>OrderTax</Name>
			<EventField>event_attribute_ordetax</EventField>
			<EventFieldType>number</EventFieldType>
		</Column>
		<Column>
			<Name>OrderTotal</Name>
			<EventField>event_attribute_ordetotal</EventField>
			<EventFieldType>number</EventFieldType>
		</Column>
		<Column>
			<Name>Currency</Name>
			<EventField>event_attribute_currency</EventField>
		</Column>
		<Column>
			<Name>ShippingType</Name>
			<EventField>event_attribute_shippingtype</EventField>
		</Column>
		<Column>
			<Name>Quantity</Name>
			<EventField>event_attribute_quantity</EventField>
			<EventFieldType>number</EventFieldType>
		</Column>
		<Column>
			<Name>ProductList</Name>
			<EventField>event_attribute_productlist</EventField>
		</Column>
 </TableMapping>

Updating event download

You can use the Acoustic Exchange Toolkit to download events that are not included by default in the example mapping file. To do so, you must update the mapping file and the local database tables.

To download other events you must update the configuration of your Acoustic Exchange destination endpoint. If you add event data to a local SQL database using the example mapping file that the Acoustic Exchange Toolkit provides, you must update the mapping file and the database tables.

In the Acoustic Exchange user interface, update the subscriber endpoint to subscribe to the new events. After you update the event registration for the subscribing endpoint, the eventsDownload script processes the new event files.

1. If you use the Acoustic Exchange Toolkit to add event data to a local database, complete the following steps.
2. Update the local database tables to receive the new event data.
3. Update EventToDBTableMapping.xml to include a new section that maps fields from the new event to the new fields in the database.

Custom event downloads

To use the Acoustic Exchange Toolkit to download modified events or events other than the events that are supported by default, you must update your Acoustic Exchange endpoint subscriptions. If you add event data to a local SQL database, you must update the mapping file and the database tables.

The example mapping file that is provided with the Acoustic Exchange Toolkit is based on the standard definition of specific recognized events. Acoustic recognized events contain some attributes that are optional. Depending on the source of the event data, the downloaded events might not contain data for all possible attributes.

To download event data that does not contain all of the standard event attributes, you must remove the missing attributes from the mapping file and update the design of the local destination database to match.

You can also configure the Acoustic Exchange Toolkit to download event types other than the default events. To download event data for recognized events other than the events specified in the mapping file, you must add a section to the mapping file that defines the added event data.