The SFTP event publisher leverages the SFTP server as the cloud storage system for the consumption and publishing of event data.
Overview
The SFTP server for publishing events and event data provides a secure and encrypted method/platform for transferring data (which is usually encrypted during the transfer process) through a single connection. Also, an SFTP server is easily accessible as you only need your username and password as credentials.
In this integration, Acoustic Exchange pulls event data stored as batch JSON payloads, TSV, or CSV files.
- Create your mapping file and add it to your SFTP server. (Optional).
Note: If you do not create a mapping file, then you can upload batch event JSON files only. The events in the JSON file must be events that are recognized by Acoustic Exchange. - Upload data to your SFTP server.
- Register the SFTP endpoint in Acoustic Exchange.
- Subscribe your SFTP endpoint to events.
Prerequisites
- An active connection to an SFTP server
- A mapping file (preferably having a .xml extension)
- Data file (event file)
1. Configure your mapping file (Optional)
Acoustic Exchange can consume TSV, CSV, or batch JSON files. Other files in your SFTP server are ignored. For Acoustic Exchange to be able to read and share the contents of those files, you must create a mapping file to map the data to a naming convention and syntax that Acoustic Exchange can read. Note: If you do not create a mapping file, then you can upload batch event JSON files only. The events in the JSON file must be recognized Acoustic Exchange events.
Create your mapping file
Your mapping file must be in XML format and must be hosted in your SFTP server, the same location as the data files that you want to share with Acoustic Exchange. When you create the mapping file, make sure to name it something memorable or significant. You will need the mapping file name during the endpoint registration process.
When you create a mapping file to map event data from a CSV file, it can look similar to this example:
<?xml version="1.0" encoding="UTF-8"?>
<UBX_EVENT_MAPPING dataFileFormat="CSV" sourceEvent="offerAccepted">
<UBX_EVENT sourceEvent="offerAccepted">
<code>offerAccepted</code>
<channel>Paid Media / AdTech</channel>
<timestamp sourceIndex="1" sourceType="timestamp" sourceFormat="yyyy-MM-dd HH:mm:ss"></timestamp>
<attributes>
<attribute>
<name>eventName</name>
<value>Accepted Offer</value>
<type>String</type>
</attribute>
<attribute sourceIndex="3">
<name>USERAGENT_TYPE</name>
<type>String</type>
</attribute>
<attribute sourceIndex="4">
<name>USERAGENT_FAMILY</name>
<type>String</type>
</attribute>
<attribute sourceIndex="5">
<name>USERAGENT_DEVICETYPE</name>
<type>String</type>
</attribute>
<attribute sourceIndex="6">
<name>USERAGENT_OS</name>
<type>String</type>
</attribute>
<attribute sourceIndex="7">
<name>LANGUAGE</name>
<type>String</type>
</attribute>
<attribute sourceIndex="8">
<name>GEO_CITY</name>
<type>String</type>
</attribute>
<attribute sourceIndex="9">
<name>GEO_CITYCONF</name>
<type>String</type>
</attribute>
<attribute sourceIndex="10">
<name>GEO_POSTAL</name>
<type>String</type>
</attribute>
<attribute sourceIndex="11">
<name>GEO_POSTALCONF</name>
<type>String</type>
</attribute>
<attribute sourceIndex="12">
<name>GEO_AREACODE</name>
<type>String</type>
</attribute>
<attribute sourceIndex="13">
<name>GEO_REGION</name>
<type>String</type>
</attribute>
<attribute sourceIndex="14">
<name>GEO_COUNTRY</name>
<type>String</type>
</attribute>
<attribute sourceIndex="15">
<name>GEO_GMTOFFSET</name>
<type>String</type>
</attribute>
<attribute sourceIndex="16">
<name>GEO_DMA</name>
<type>String</type>
</attribute>
<attribute sourceIndex="17">
<name>QUERYSTRING</name>
<type>String</type>
</attribute>
</attributes>
<identifiers>
<identifier sourceIndex="2">
<name>USERID</name>
<type>String</type>
</identifier>
</identifiers>
</UBX_EVENT>
</UBX_EVENT_MAPPING>
In the root element <UBX_EVENT_MAPPING>
, you define the input data file format and where the source event type can be found. Because the event mapping is based on the source event type, Acoustic Exchange needs to know where to get the source event type. If the data file format is UBXEventBatchJSON
(Batch event JSON payloads), then Acoustic Exchange reads the JSON field. If the data file format is TSV or CSV, the source event type can come from bucket name, file name, column name or column index.
Each <UBX_EVENT>
element represents the mapping from a source event type to an Acoustic Exchange event. It contains an <attributes>
block which represents mapping for the attributes and an <identifiers>
block which represents mapping for the identifiers.
Attribute | Description | Accepted Value | Example |
---|---|---|---|
dataFileFormat |
Defines the input data file format. |
UBXEventBatchJSON TSV CSV |
<UBX_EVENT_MAPPING dataFileFormat="CSV"> |
dataFileHeader |
Defines whether the CSV or TSV file contains a column header. | True | False | dataFileHeader=”true” |
sourceEvent |
Defines where Exchange can get the event type in the source data. |
|
sourceEvent="{FILE_NAME}" |
sourceEventColumnIndex |
Defines the index number of the column for the source. | <Number> | sourceEventColumnIndex=”3” |
sourceEventColumnName |
Defines the name of the column for the source event type value. | <String> | sourceEventColumnName=”event” |
Attribute | Description | Accepted Value | Example |
---|---|---|---|
sourceEvent |
Defines the source event name. This attribute looks up the event that is being mapped for the source event. | <String> | <UBX_EVENT sourceEvent="offerAccepted"> |
Attribute | Description | Accepted Value | Example |
---|---|---|---|
<code> |
Defines the event code. | <String> | <code>adImpression</code> |
<channel> |
Defines the event channel | <String> |
|
<timestamp> |
Defines the mapping of the timestamp value. It converts the timestamp from the source format into format consumable by Exchange. | Attributes:
|
<timestamp sourceIndex="1" sourceType="timestamp" sourceFormat="yyyy-MM-dd
HH:mm:ss"></timestamp> |
<attributes> |
Defines the attribute mapping. It contains a list of <attribute> blocks. Each represents the mapping from a source attribute to an Exchange attribute. |
Attributes:
Elements:
Entry: sourceValue defines the source value;
The node value defines the Exchange value. |
Maps the source attribute aa.productId to the attribute productid :<attribute sourceIndex="10">
<name>subChannel</name>
<valueMap>
<entry sourceValue="1">web</entry>
<entry sourceValue="4">web</entry>
<entry sourceValue="2">video</entry>
<entry sourceValue="3">social</entry>
<entry sourceValue="10">social</entry>
<entry sourceValue="6">search</entry>
<entry sourceValue="7">email</entry>
<entry sourceValue="8">app</entry>
</valueMap>
</attribute>
|
<identifiers> |
Defines the identifier mapping. It contains a list of <identifier> blocks. Each represents the mapping from a source identifier to an identifier. |
Attributes
Element
Entry
The node value defines the Exchange value. |
Maps from source identifier cid to the identifier connected_id :
<identifier sourceName="cid" sourceMinLength="5">
<name>connected_id</name>
</identifier>
|
Aggregated Events
For Exchange, aggregated events, such as cart purchase and cart abandonment events, are required to be sent with their corresponding item events (e.g. cart purchase item and cart abandonment item events) in a single event batch. Since in TSV/CSV files, one line represents a single event, we need a way to group multiple lines together to support the aggregated events. Such event groups can appear anywhere in the data file, however, events that belong to the same group must be together (sequential lines). Within the group, the first line should be the aggregated event, followed by the member (item) events. The member event line should have a dedicated column (defined by mapping header attribute memberEventColumn) to indicate this line is a member event (when the column value equals to what's defined by mapping header attribute memberEventColumnValue).
For example, in the following TSV file. we use the column "memberFlag" to indicate the member event line. The line is considered a member event line when the value is set to "M".
Identifier column | Other attributes column | sourceEventType | memberflag | |
1 | productView | |||
2 | abandonment | |||
3 | abandonmentItem | M | ||
4 | abandonmentItem | M | ||
5 | abandonmentItem | M | ||
6 | abandonment | |||
7 | abandonmentItem | M | ||
8 | pageView | |||
9 | productView | |||
10 | abandonment | |||
11 | abandonmentItem | M |
In the mapping file, we set the following in the <UBX_EVENT_MAPPING> element:
<UBX_EVENT_MAPPING dataFileFormat="TSV" sourceEvent="{COLUMN_NAME}" sourceEventColumnName="sourceEventType" memberEventColumn="{COLUMN_NAME}" memberEventColumnName="memberFlag" memberEventColumnValue="M" dataFileHeader="true">
When this data file is processed, line 2-5, line 6-7, line 10-11, each will be grouped together in separated event batches.
3. Upload data to your SFTP server
If you created a mapping file, you're ready to add the mapping file and other objects to your SFTP server. An object can be batch JSON payloads, TSV, or CSV files. Note: If you did not create a mapping file, then you can upload batch event JSON files only.
Batch event JSON payloads
This code cannot be formatted, you must use raw JSON.
A batch event JSON payload is multiple events in a JSON payload as a series of event descriptions. Each event description must define the identifiers that Acoustic Exchange can use to identify the specific individual who is associated with the event. For example, an email address or cookieId
is a typical identifier. Each line must contain one complete event batch JSON payload.
Each event description must also specify the event code that indicates the type of event, a time stamp, and various attributes that describe the event. The event description can also identify the channel in which you detected the event. You can also define the channel at the batch level if all of the events in the batch are observed in the same channel.
Batch event JSON payload example:
{ "events":[ { "code":"<Exchange event code>", "timestamp":"<time ISO-8601 format>", "channel":"<channel type>", "attributes":[ { "name":"<attribute name>", "value":"<value detected during the event>", "type":"<data type>" }, { "name":"<attribute name>", "value":"<value detected during the event>", "type":"<data type>" } ], "identifiers":[ { "name":"<Exchange identifier name>", "value":"<value detected during the event>" }, { "name":"<Exchange identifier name>", "value":"<value detected during the event>" } ] }, { "code":"<Exchange event code>", "timestamp":"<time ISO-8601 format>", "channel":"<channel type>", "attributes":[ { "name":"<attribute name>", "value":"<value detected during the event>", "type":"<data type>" }, { "name":"<attribute name>", "value":"<value detected during the event>", "type":"<data type>" } ], "identifiers":[ { "name":"<Exchange identifier name>", "value":"<value detected during the event>" } ] } ] }
TSV and CSV files
TSV and CSV files can be configured as they would normally, no special configuration is required within the TSV or CSV files themselves. However, as stated above, you will need to create a mapping file for the content of the TSV or CSV files.
Example of a CSV file:
"DATETIME","USERID","USERAGENT_TYPE","USERAGENT_FAMILY","USERAGENT_DEVICETYPE","USERAGENT_OS","LANGUAGE","GEO_CITY","GEO_CITYCONF","GEO_POSTAL","GEO_POSTALCONF","GEO_AREACODE","GEO_REGION","GEO_COUNTRY","GEO_GMTOFFSET","GEO_DMA","QUERYSTRING"
"2017-10-16 22:29:26","452fa06fd0bf55f4777f2c2033525eb7","SM-J700T","Chrome Mobile","Smartphone","Android","","los angeles","65","90009","35","310/424","CA","US","-25200","803","p=XXX&g=Y&cpid=123&plid=456&stid=789&crid=101112&j=0"
"2017-10-16 22:29:26","36d17521808c1452a7de76c5e38b2421","SM-G955U","Chrome Mobile","Smartphone","Android","","los angeles","60","90044","30","323","CA","US","-25200","803","p=XXX&g=Y&cpid=123&plid=456&stid=789&crid=101112&j=0"
"2017-10-16 22:31:9","f2743713a4f97075e6ea55ad755d7f28","Browser","Chrome","Personal computer","Mac OS X","","ft worth","65","76137","30","817/682","TX","US","-18000","623","p=XXX&g=Y&cpid=123&plid=456&stid=789&crid=101112&j=0"
"2017-10-16 22:32:12","5712ee8ecee794715a7bd3e9c3cbed1a","Browser","Chrome","Personal computer","Windows","","college station","70","77840","40","979","TX","US","-18000","625","p=XXX&g=Y&cpid=123&plid=456&stid=789&crid=101112&j=0"
"2017-10-16 22:31:10","362e5148306a6294ae92fc4f07ae16a4","SAMSUNG SM-G930A","Chrome Mobile","Smartphone","Android","","san marcos","60","78666","30","512/737","TX","US","-18000","635","p=XXX&g=Y&cpid=123&plid=456&stid=789&crid=101112&j=0"
"2017-10-16 22:32:14","14e6e35bf5c47fd540d9041f6dac9732","iPhone","Mobile Safari","Smartphone","iOS","","coral gables","65","33134","35","305/786","FL","US","-14400","528","p=XXX&g=Y&cpid=123&plid=456&stid=789&crid=101112&j=0"
"2017-10-16 22:29:28","cedb350ba0f334eeeba416156e03f196","iPhone","Mobile Safari","Smartphone","iOS","","van nuys","95","91405","90","818","CA","US","-25200","803","p=XXX&g=Y&cpid=123&plid=456&stid=789&crid=101112&j=0"
"2017-10-16 22:31:13","85331dbfbb736a9ffc84fb3d99531d2f","SM-G930P","Chrome Mobile","Smartphone","Android","","jarrettsville","90","21084","90","410/443/667","MD","US","-14400","512","p=XXX&g=Y&cpid=123&plid=456&stid=789&crid=101112&j=0"
"2017-10-16 22:29:29","dc19b0b414773aa1aa5d2cd6e0236c0f","SAMSUNG SM-G890A","Chrome Mobile","Smartphone","Android","","moreno valley","65","92555","35","951","CA","US","-25200","803","p=XXX&g=Y&cpid=123&plid=456&stid=789&crid=101112&j=0"
4. Register your endpoint
To exchange data from your SFTP server through Acoustic Exchange, you must register your SFTP server as an endpoint with Acoustic Exchange for use in your Acoustic Exchange account. You must have a valid license and access credentials for products and solutions that you register.
Register the endpoint with the endpoint registration wizard on the Endpoints tab or through the navigation menu.
The SFTP server can deploy business solutions on one or more servers or data centers. You must specify a deployment when you register your SFTP server as an Acoustic Exchange endpoint for your account.
Use the registration wizard to provide required address and credential information about your organization and Acoustic Exchange account.
Acoustic Exchange accepts the address and credential information as permission from you that the endpoint provider can interact with Exchange on your behalf. During the registration process, you must provide the user credentials that the endpoint provider requires.
- On the Endpoints tab in Acoustic Exchange, select Register new endpoint.
- Select SFTP as the endpoint that you want to register and click Next.
- Provide the information that the endpoint provider requires as part of the registration request. To register the endpoint your will need to provide your:
- Deployment
- SFTP Domain Name
- SFTP Port
- SFTP User ID
- SFTP Password
- Mapping File Name
- Optional: Specify the SFTP Folder Path if the files are not at the root level.
By default, the endpoint assumes all requisite files are located at the root level of the SFTP server. If your batch of mapping and data files that are in a different directory, you can redirect the endpoint to it by defining the SFTP folder path. - Optional: If your business needs require it, enter an Endpoint alias for your endpoint. An endpoint alias is a designation that differentiates one endpoint from another. Defining an endpoint alias is useful in cases where you have multiple endpoints with the same configuration but have different purposes. If you do not define an endpoint alias, one is generated for you.
- Optional: Enter a description for the endpoint. By entering a description for the endpoint, you can provide better insight to the purpose or goal of the endpoint. Again, this is useful when you have multiple endpoints of similar configuration, but different purpose. If you do not define an endpoint description, then Acoustic Exchange uses the default endpoint description.
- Click Register.
If you entered registration information into the registration wizard, Acoustic Exchange transmits the information to the endpoint provider so that the provider can complete the registration. The endpoint displays on the Endpoints tab as Pending. When Acoustic Exchange and the endpoint provider completes the registration, the endpoint status changes to Active.
Note: If the registration did not complete successfully, the endpoint status is Failed. Contact Acoustic Exchange Support for assistance.
5. Subscribe to events in Acoustic Exchange
You must register the publishing and subscribing endpoints. As part of the endpoint registration, the endpoint provider registers the events that it can provide. Depending on the information that the provider supplies, Acoustic Exchange can display a description of the event and related event attributes.
When you subscribe to events, you select an event and a destination for the event. You can select multiple events and multiple destinations.
- On the Events tab, click Subscribe to events. The Event subscription window displays.
- In the Select events column, select a provider > endpoint > event. View details about the event.
- In the Select destinations column, select one or more endpoints to receive the event notification. The endpoint displays in the Pending subscriptions column.
- Review the pending subscriptions. You can change the subscriptions as necessary.
- Click Subscribe. Refresh the list of available event syndications.
When will Acoustic Exchange pull event data from the SFTP server?
Every 8 hours, or three times over a 24 cycle, Acoustic Exchange looks into your SFTP server to see if there is any new event data that has not been previously shared. If it finds new event data, it downloads it, maps the data by using a mapping file that you provide, and sends the data to Acoustic Exchange. The event files are then moved to a processed folder in your SFTP server. Acoustic Exchange generates a report file for each JSON, TSV, or CSV file that was processed.