The process outlined in this document described how to configure Openbridge to automate reception of Adobe export feeds, processing them and load them into a database.
The process of defining the extracts and scheduling delivery occurs within the Adobe website. In the Adobe management site you can generate ad hoc data reports for your historical reporting data. The reports are delivered as CSV files via SFTP from Adobe to Openbridge.
In the Adobe UI you build a query to filter your data and isolate specific feeds. The vast majority of requests take less than a day to process, but depending on the complexity of your query and the amount of data it can take longer to process.
The following are a few Adobe documents which describe how to setup Adobe to deliver exports:
Openbridge will need to configure a private delivery location to receive files from Adobe. This will also include the appropriate credentials (user/pass) needed by Adobe to log into our system.
Also, see the data pipeline for additional context on the Adobe Data Warehouse Feed process. The data pipeline document describes the additional considerations and requirements for delivery of data feeds into Openbridge.
Adobe defaults delivery to FTP. If you want to use SFTP, check to make sure a special request to the Adobe support team to setup is not needed.
Delivery of files to the following:
- SFTP Hostname =
pipeline.openbridge.io
The following are the PORT numbers you will need to make sure are used by the system responsible for file delivery:
- SFTP Port =
22
A report type is a user defined export. It defines the structure and data that was configured in the Adobe system for export. For example, it might be a bounces export or it may be related to channels, products or visits. You have the flexibility to define these exports in Adobe.
The following shows a collection of user defined report exports each has data that was delivered by Adobe to Openbridge. For example;
../my-suite-id/adob_channel/adob_channel_20150101.csv
../my-suite-id/adob_pages/adob_pages_20150101.csv
../my-suite-id/adob_products/adob_products_20150101.csv
../my-suite-id/adob_site_totals/adob_site_totals_20150101.csv
../my-suite-id/adob_video/adob_video_20150101.csv
../my-suite-id/adob_visits/adob_visits_20150101.csvFeeds must have specific directories on the Openbridge system that correspond to a reporting suite. The output from Adobe should be delivered to a unique destination and use a filemask that corresponds to the report suite and report type being sent. For example: "../(report_suite_id)/(report_type)/(report_type_YYYY-mm-dd).csv"
This would result in a unique destination and filemask would like this: "../my-suite-id/adob_site_totals/adob_site_totals_20150101.csv"
The delivery format from Adobe will be uncompressed CSV files. The raw CSV files from Adobe will have headers that look like this:
| Date | Visits | Unique Visitors | Something (23) (event23) |The headers as supplied are technically not valid for import into a database like Redshift. In above example there are mixed case, special characters and spacing used for column names. Our system will automatically standardize the headers supplied by Adobe to ensure that downstream databases can properly import the data.
Our cleanaup process will generate a resulting header and table that look like this:
| date | visits | unique_visitors | something_23_event23 |The a sample row of values associated with the header should follow typical CSV conventions:
"January 1, 2015",111292,12513,8139Once the directories have been configured and delivery from Adobe has commenced feeds will arrive daily. For example, this shows daily files being delivered to a user defined report called adob_site_totals;
"../my-suite-id/adob_site_totals/adob_site_totals_20150101.csv"
"../my-suite-id/adob_site_totals/adob_site_totals_20150102.csv"
"../my-suite-id/adob_site_totals/adob_site_totals_20150103.csv"The system will process each CSV file according to the organizational structure the files are being delivered. For example, based on the above structure a table would be created called adob_site_totals. This is based on the report type directory name.
Based on the CSV header residing in the report type folder adob_site_totals columns are created which align with the schema of the file. In this example, the CSV contained the header:
date, visits, unique_visitors, something_23_event23The CSV file will have values that would align to those headers. In this example the CSV file has following row values:
"January 8, 2015",111292,12513,8139
associated with each header item
date, visits, unique_visitors, something_23_event23With each subsequent file delivery of for the adob_site_totals report suite the same process would occur.This would result in new rows being added to adob_site_totals:
date,visits,unique_visitors,something_23_event23
"January 1, 2015",111292,3256,2243
"January 2, 2015",171540,12385,5654
"January 3, 2015",131791,23423,8453
"January 4, 2015",81292,4513,1296
Adobe supports standard username and password credential for SFTP. Openbridge will supply these to you to supply to Adobe technical support team.
Adobe will only have access to the specific directory path mentioned previously. They are "locked" there and only have access to deliver files to the target directory.
You have the option of scheduling the frequency of your data warehouse with the Adobe website. We suggest that the feeds are scheduled daily, delivered between 3AM and 6AM daily.
Having trouble with something? Contact us at http://openbridge.zendesk.com or contact support@openbridge.com and we’ll help you sort it out.