Fully automated, code-free Adobe Analytics data pipelines to Redshift, BigQuery or Amazon Athena
Openbridge supports the creation of data pipelines from the Adobe Data Warehouse to a target warehouse or data lake. Who is this for?
- This is for teams that want to fuel sales and BI efforts using Adobe Data Warehouse data in tools like Tableau, Looker, Periscope Data, Mode Analytics, and Grow
- This is for teams that want to “own” their Adobe Data Warehouse data and have it stored in your private cloud data warehouses like AWS Redshift, AWS Athena, Google BigQuery and Snowflake
Our batch data processing pipeline is a perfect compliment to receive these warehouse exports from Adobe. The Openbridge batch process is described in a separate post:
In this article, we will walk through how to configure an export within Adobe. If you have questions or are unsure how to proceed, just contact us to discuss.
What Are Adobe Data Warehouse Exports?
Adobe Warehouse exports reflect a summarized view of your analytics data. The contents of exports are set by the end user via the Adobe Analytics user interface. Here is how Adobe describes these exports;
Data Warehouse refers to the copy of Analytics data for storage and custom reports, which you can run by filtering the data. You can request reports to display advanced data relationships from raw data based on your unique questions. Data warehouse reports are emailed or sent via SFTP, and may take up to 72 hours to process. Processing time depends on the complexity of the query and the amount of data requested.
Let’s get started with setup up an export!
Step 1: Go to Tools -> Data Warehouse
First, log in to your Adobe Analytics account. In the top navigation, you want to get to the Data Warehouse tool.
Once you are in the Data Warehouse tool, the screen will look something like this:
Step 2: Setting Up You Export
The first step with export is setting a Request Name. This is the unique report name that will describe the collection of data that will be included in the export. Pick a name that helps you understand what the contents of the report are. A bad name would be the one shown on the screen below:
The next elements are Reporting Date. This defines the window of time that will be covered in the export. For example, if you want to send “go-forward” data, you would likely want to use a preset data of “yesterday”. If a report was run on June 14th, the data would reflect yesterdays data or everything for June 13th.
If you want to run a one-time historical export, you can set a custom date range to include the desired window of time to be included.
Please be cognizant we will process the data we receive for an export. If you are not cognizant of the data flowing over there is a risk of duplicate data being sent and loaded. While we deduplicate data, there may be a case where you send the same data twice one week and then resend 2 weeks later. There may be subtle changes in the data for a given date that where we no longer detect a record as a duplicate.
Step 3: Setting Report Metrics and Segments
Select the desired metrics that should be included. There are a wide variety of options available. The combination of segments and metrics will be unique to your report suite. As a result, we suggest you experiment and review samples prior to a formal export to your Openbridge pipeline. This will allow you to validate the contents of export prior to formalizing delivery and loading to your target destination.
Be aware that different combinations of segments and metrics can generate different values. For example, the metric “Clicks to Page” will have different number reported for Segment X then Segment Y. This is why we strongly suggest previewing your data prior to pushing it into your data pipelines for delivery. If you deliver it to us, we will process and load it.
As a result, if you don’t have a sense of what you are sending to us, you will be surprised by what ends up in your destination warehouse or data lake. Spend a little time reviewing the contents of your report definition first!
Step 4: Advanced Delivery Options For Adobe Warehouse Exports
Assuming you have tested and confirmed the contents of your desired export, you are ready to set your delivery options. The following information is resident under Advanced Delivery Options settings.
The screen will look like this:
Here are the settings you will need to care for:
- File Name: Give your report a unique name. Something that reflects the contents of the export. Also, we strongly suggest you add the date range, include %R in the filename. This value represents the date values requested in the report. For example, if you request data from May 1, 2013, through May 7, 2013, the %R shows a filename including the date range of 20130501–20130507.
- Format: Select CSV as the format. Leave the number of rows to 0
- Contents: The language for the header and footer should be English
- Additional Documents: Leave blank
When you set up an Openbridge batch Data Pipeline, you will receive a customized delivery location for the SFTP Report Destination. It will include the following information:
- Protocol: SFTP (or FTP within Adobe)
- Host: pipeline.openbridge.com
- Port: 22 or 443
- Directory: <provided by Openbridge>
- Username: <provided by Openbridge>
- Password: <provided by Openbridge>
Each export from Adobe requires a unique Openbridge batch data pipeline. 1 Adobe Warehouse Export = 1 Openbridge batch data pipeline. if you have 10 Adobe exports, you should have setup 10 Openbridge batch data pipelines.
NOTE: More than one Adobe export cannot be sent to the same destination Directory. If you attempt to reuse a location and dump many unique exports to a common Directory within Openbridge they will fail to process or load. However, if these different exports do process and load on a given occasion they risk corrupting the data in your destination.
Step 5: Scheduling Your Delivery
Schedule requests for automatic delivery at selected intervals, or as a one-time report. This also occurs under Advanced Delivery Options. The screen will look like this:
For example, if you want to set a daily export process, select Daily every 1 day. Set your start date and time and from that point forward automated deliveries will be set to your Openbridge batch data pipeline for this export. Since it is an ongoing process, no end date should be set.
We suggest setting a time early in the day for Time of Day, but not too early. A 3 AM to 7 AM window works well. If you set it too early, Adobe sometimes may send empty or missing values.
You can read more about scheduling about this here at the Adobe site.
Step 6: Review Your Settings
Give your export settings one final review. Is everything as expected? If so, make sure everything is saved and schedule. Your exports will start running per your config. Once they arrive at Openbridge, we will process and load the exports according to your pipeline definition.
Manage Data Warehouse requests
Within the Adobe interface is something called the Request Manager. It lets you view, duplicate, and re-prioritize requests. This can be helpful with management or creating duplicates of current exports to revisions.
In Data Warehouse, select the Request Manager tab:
- View recent report requests by report name, segment applied, requestor, request date, and status.
- Duplicate requests. Click Duplicate next to the request.
- This action duplicates only the request, not the schedule or the delivery details.
- Search for reports by report name or by the login name of the requestor.
- Re-prioritize reports by dragging and dropping them to a new location within the queue.
- To see when a request started processing, click on a scheduled request ID and examine the pop-up that opens.
To cancel a Data Warehouse request, follow these steps.
- Read about the Adobe Data Warehouse best practices here.
- Data Warehouse automatically zips any file that exceeds 1 MB in size. The maximum email attachment size is 10 MB.
- Data Warehouse can process an unlimited number of rows in a single request for individually scheduled and downloaded reports.
- Different dimensions cannot be used in Data Warehouse segments. Read more about this here: https://docs.adobe.com/content/help/en/analytics/export/data-warehouse/dw-dimensions-incompatible-dwsegments.html