Overview

Skill Level: Any

Prerequisites

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

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

  • Download event data from UBX
  • Import event data into a specified local database

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

The UBX 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

  1. Downloading events from UBX

    The UBX Toolkit provides the eventsDownload script to download event data from UBX to a to a file in your local environment. You can run the script manually or as a scheduled job.

    Log in to UBX 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 UBX 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://<server-name>:<port>. The service URL for UBX public APIs. The path must include the server and port that IBM assigns to your UBX account.
      • ubx.endpoint.eventfiles.numFilesInList=<number> 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 <config properties file>] or eventsDownload.sh [-c <config properties file>] By default, UBX 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 UBX toolkit downloads event data from UBX 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 UBX server.

    UBX event types

    IBM defines several types of recognized events and organizes them into classes that are based loosely on the channel in which the events are observed.

    IBM also recognizes custom events that are defined by IBM Business Partners, but only after consultation and agreement on the event name, event code, and support for event attributes. Such consultation helps ensure that UBX can correctly recognize and process the data that the custom event promises to deliver.

    For more information about UBX event types, see UBX event type directory and Dynamic Event Library

     

    JSON format for download event data
     
    The UBX events are serialized JSON objects in the downloaded files. Each file can contain data for multiple events.

    The UBX 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.

    JSON TSV header
    provider provider
    source source
    channel channel
    x1id x1id
    Identifiers[i].name identifiers_<string_value>
    event.code event_code
    event.timestamp event_timestamp
    event.attributes[i].value event_attributes_<string_value>

     

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

     

    {
    "provider": "IBM",
    "source" : "Digital Analytics",
    "channel": "Web",
    "x1Id": "81147632-a3fe-43a5-b0c8-6289ca9302bc"
    "identifiers":
    [
    { "name": "email", "value": "slmoore@example.com" },
    { "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" }
    ] }
    ]}

     Stucture of the corresponding TSV file.

    SV Header Event 1 Event 2 Event <n>
    provider IBM IBM  
    source Digital Analytics Digital Analytics  
    channel web web  
    x1id 81147632-a3fe-43a5- b0c8-6289ca9302bc 81147632-a3fe-43a5- b0c8-6289ca9302bc  
    identifier_email slmoore@example.com slmoore@example.com  
    identifier_cookieid 098098-adfsfd-9323ad-78sdfs 098098-adfsfd-9323ad-78sdfs  
    event_code cartPurchase cartPurchase  
    event_timestamp 2015-03-18T13:55:06+00:00 2015-03-18T14:08:36+00:00  
    event_attribute_productid d12345 d123456  
    event_attribute_productname Widget ABC Widget 123  
    event_attribute_baseprice 200.00 13.00  
    event_attribute_quantity 2 1  

     

  2. 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 UBX 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 UBX HTTP API to download the event data from UBX and stores the data as a tab-separated (TSV) file in the client file system. UBX provides events files in JSON format. The Events Downloader component of the UBX 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 <mapping file> -i <source TSV file> -f <source directory> [-c <config properties file> -j <jdbc properties file>

    For Linux or UNIX, run eventsImport.sh -m <mapping file> -i <source TSV file> -f <source directory> [-c <config properties file> -j <jdbc properties file>

    Required parameters

    • -m <mapping file> The default mapping file is EventsDBTableMappings.
    • -i <input TSV file> 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 <input folder> 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 <alternate config.properties> 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 <alternate jdbc.properties> 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 UBX 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 UBX 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 UBX 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 UBX 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>
  3. Updating event download

    You can use the UBX 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 UBX destination endpoint. If you add event data to a local SQL database using the example mapping file that the UBX Toolkit provides, you must update the mapping file and the database tables.

    1. In the UBX 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.
    2. If you use the UBX Toolkit to add event data to a local database, complete the following steps.
      1. Update the local database tables to receive the new event data.
      2. Update EventToDBTableMapping.xml to include a new section that maps fields from the new event to the new fields in the database.

     

  4. Custom event downloads

    To use the UBX Toolkit to download modified events or events other than the events that are supported by default, you must update your UBX 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 UBX Toolkit is based on the standard definition of specific recognized events. IBM 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 UBX 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.

3 comments on"Downloading and importing event data for event destination endpoints"

  1. I have question about eventDownload engine:
    Is the any way exists to automatically remove *.json files from File System right after conversion to *.tsv was happened ?

    • BrianPersichitte April 10, 2017

      There is no script currently included with the toolkit. You will need to write a simple script to periodically delete the json files.

  2. Ravikiran Allu August 17, 2017

    There are many tables created using SQL Script provided in UBX Toolkit. I understood that these tables are needed to store the event data.
    Does the tables specify all possible events (Mapped from IBM Watson Campaign Automation endpoint) ?

Join The Discussion

Your email address will not be published. Required fields are marked *