How To Export Data From Oracle Eloqua To Amazon Redshift, Amazon Athena or Google BigQuery

Oracle Eloqua allows you to export contacts, accounts, or activities like bounces, clickthroughs, sends, opens, etc. Those exports can be sent to your Openbridge data pipeline for loading into your preferred data warehouse such as Amazon Redshift, Amazon Athena, Redshift Spectrum or Google BigQuery. This may be useful if you are looking to:

• Join marketing campaign data from Eloqua with data from other marketing, sales, social media, finance, etc. data sources
• Discover insights using Eloqua activities data within data analytics and visualization tools such as Tableau, Power BI, Qlik and many others

This guide is meant to kick-start your data efforts and solve analytics workflow challenges. As always, please refer to the latest Eloqua docs as they may differ slightly from the process described below.

Getting Started: Prerequisites

First of all, if you want to export activities from Eloqua, your administrator must set up Oracle Eloqua to capture the activity data to an internal queue. To set up the external call to an internal queue, log in to My Oracle Support and create a service request. After this configuration is complete, Oracle Eloqua will begin collecting the activity data going forward.

Oracle Eloqua Navigation. Credit: Oracle

Activity data typically covers the following types of data:

• customer
• account
• campaign

Those are among the many possible types of data feeds you may export from Eloqua. You can then create a data pipeline that will automatically load those exports to a chosen data warehouse, so you can use tools like Tableau, Power BI, Qlik, Looker, Mode Analytics, AWS QuickSight and many others for unified data analysis, visualizations and reporting.

Let’s use an example of exporting campaign data.

Step 1: Export Settings

The first step is to get a sample CSV of your campaign data. This will allow you to verify that it includes the data you need and save it for setting up the Openbridge Batch system in Step 2. Follow these steps to export a sample CSV:

• Navigate to Audience > Tools, then click Data Export and Import
• Click the Data Export tab and then Add button
• On the Export Setting tab, configure the export: select the Compress File (.zip format) check box to compress the export file; click the Append Date Stamp check box and select the date format to add a date stamp to the export file.

Step 2: Set Up an Openbridge Batch Subscription

Now you are ready to set up an Openbridge Batch subscription for campaign data. Follow the Guide to Set Up a Batch Data Pipeline For CSV Files. At the end of the setup process, you will receive an instructions file with the delivery location and login info to complete the Eloqua setup in Step 3.

For this example, we will assume you setup an “eloqua_campaign” pipeline via the process outlined in the link above.

IMPORTANT: To complete this step you should have a sample “campaign” CSV that reflects the exported data. We use this sample data to “train” our system how to process your data. To get a sample export, take a look at this Eloqua doc to download a sample file.

Next, we will configure the delivery location.

Step 3: Configure Delivery Destination

The next step is to configure your export from Eloqua. One of the first steps in that process is defining where you want Eloqua to deliver the exports. In this case, we will want to enter the connection details you received at the end of Step 2. Those connections details from Openbridge will look like this:

• Host: pipeline-01.openbridge.io
• Port: 22 or 443 (Note: port 443 is often used in the event a corporate firewall blocks outbound connections on port 22)
• Database destination: athena-customer-database
• Table destination: eloqua_campaign
• Username: yourusername
• Password: yourpassword

After entering the connection info into Eloqua you will need to:

• Click “Test Connection” to ensure the information you entered is correct
• Specify where to store the file export on the SFTP server in the Path text box. This is your delivery location. In this case, you must enter the name that you configured from Step 2 which is /eloqua_campaign/.

Openbridge also provides a “testing” directory /eloqua_campaign/testing for you to practice uploads to the system. Anything you send to /eloqua_campaign/ is assumed to be in production mode.

Lastly, you will notice that the path AND the table name are the same. We use the /eloqua_campaign/path to route, process and load data to a target table in your destination data warehouse. This aligns with what you configured in Step 2.

Step 4: Schedule Your Export

You have the option to run a one-time export or schedule an ongoing delivery:

• To run a on-time export, click the On Demand option
• To schedule the export to run on a regular basis, click On these days and specify the schedule.

Step 5: Define The Data You Want In Your Export

Now you can configure which data you want to export. Click the Data tab to to start configuration:

• Specify what to include in the export, either all records or only records that change.
• From the Data type drop-down list specify whether you are exporting contact, account, or activity data.
• If you selected contact data, configure additional options about the data to export: contact fields (all contact fields or only a specific contact view), contacts in a list, segment or all contacts.
• Click the Format tab to configure the format for the exported file. The default file extension should be CSV. The default structure of the file is column1,column2,column3.
• Click Save

Step 6: Run Your Export!

To perform a one-time export click the Overview tab and click Run now. If you setup a schedule, the export will run according to the schedule you specified on the Export Settings tab.

Now your eloqua_campaign data will be automatically delivered to your destination warehouse. For each additional data feed you want to upload, you would need repeat this process.

With your Eloqua data exported and successfully loaded into your data warehouse, access it within your favorite data visualization tool and enjoy the data exploration journey.

Did we miss anything? Feel free to leave a comment, we always want to hear how other companies solve data access challenges.

Get started with Openbridge by signing up for a simple, no cost account. If you have any questions about Oracle Eloqua data pipelines, we are here to help, reach out to hello@openbridge.com.