Exporting KPI Data From Adobe Campaign Standard

February 23, 2021
Developer

Adobe Campaign Standard (ACS) has a wealth of Dynamic Reporting options. From delivery dashboards to customizable reports, there are many ways for marketers to gauge the results of their ACS campaigns. Even with this wealth of options, marketers may still wish to export raw data for further analysis, for reasons ranging from internal analysis methods to "Big Data" platforms such as Google Analytics.

Let's review a method for exporting raw data from ACS. We'll cover methods to view and export:

  • Delivery logs – For each delivery, contains a row with the status (such as success, errors, and bounces) of each attempted send to a Profile in that audience.
  • Tracking logs – Entries indicate engagement data such as clicks and opens.
  • KPI logs – These include Key Performance Indicators (KPIs), such as open rate and click rate, which are automatically calculated behind the scenes in ACS. These records are calculated across an entire delivery.

For simplicity, performance terminology related to the email channel will be used. Most of these concepts apply equally well to other channels such as SMS, and only require selecting the indicators appropriate to that channel.

Where to Find the Data

Raw numbers in ACS cannot be accessed through the normal Dynamic Reporting dashboards. Dynamic Reporting performs certain heuristics and enrichments on the data before presenting it to the user. In order to access the raw data in a way that's usable by outside services, a developer must set up Workflows to directly query the corresponding tables:

  • Delivery logs: Profiles > Delivery logs (nms:broadLogRcp:profileBroadLog)
  • Tracking logs: Profiles > Tracking Logs (nms:trackingLogRcp:profileTrackingLog)
  • KPI logs: Deliveries (delivery) > Indicators (kpis)

Exporting the Data

In this example, a CSV (Excel) format file generated by ACS will be exported to an external SFTP location. The file can be picked up from that SFTP server and the data processed within the desired platform.

The way the ACS interface works, navigating to the Indicators(kpis) channel is a little more challenging than the rest. For this reason, the Indicators table will be the main focus of the examples here.

Create the External Account

ACS allows several types of external accounts which you can use for exporting data:

ACS allows SFTP, Amazon S3, and Microsoft Azure Blob storage

 

Setup of all accounts is similar, so this post will show only how to set up an external SFTP account.

A user with admin access can navigate to ACS Button > Administration > Instance Settings > External Accounts. Click Create. Enter the information, such as IP address and Port. Click Create.

Entering the SFTP external account information in ACS

 

The account is now ready to use in a Workflow.

Create the Workflow

A developer can set up a Workflow to export the data from ACS. The example workflow includes some common options that can really enhance the ability to interact with the workflow, such as piping errors out to their own transitions for further processing.

When creating/editing workflows, save the workflow as each activity is modified to avoid losing work! Saving early and often is a good rule of thumb.

The end result will look something like this:

An example workflow in ACS. This has options for scheduling the workflow to run periodically, and piping errors out to their own transitions for further processing.

 

Here is a brief summary of the Activities in the order of the workflow, and why the activity was used:

1. Start

Workflows without a scheduler commonly use a Start activity to begin the workflow, but it's good practice to use a Start activity even for workflows on a scheduler.

If there is an error in a scheduled workflow, the workflow could be manually re-run without waiting for the next scheduled trigger. Toggle the Scheduler and Start activities between Do Not Execute and Normal Execution to run the Workflow using the desired method.

2. Scheduler

Data exports are often performed on a periodic basis to ensure current data. While it's recommended to run a daily export during off-hours, the schedule should be configured so that the extract can be completed in time for other processes and systems to ingest the data so it's available for timely reporting. Configure:

  • Execution frequency: Daily
  • Time: (choose an appropriate time)
  • Repetition frequency (days): 1

3. Query Based on the Data You're Trying to Export in This Workflow.

On the Properties tab, select the object to query:

  • Delivery logs: Profiles > Delivery logs (nms:broadLogRcp:profileBroadLog)
  • Tracking logs: Profiles > Tracking Logs (nms:trackingLogRcp:profileTrackingLog)
  • KPI logs: Deliveries (delivery)

Note that at this stage, the KPI logs are only targeting the Deliveries object.

On the Target tab, refine the Target, usually by excluding any objects that should not be included in the query.

Configuring the filters in the Query

 

Example filters might exclude proofs generated by ACS, or exclude deliveries with fewer than 100 recipients, which would exclude any small test deliveries. These examples can be refined and other filters included as desired.

On the Additional Data tab, select the data to extract.

Selecting various fields to be exported from the Indicators(kpis) table

 

The desired fields from the Indicators(kpis) table can be seen/selected. To select Additional Data fields from this table for export, click on the Indicators(kpis) object to expand and display the data columns on that table.

Indicators (kpis) can be seen in Additional Data

 

Fields from other tables can be selected in a similar fashion. Once all desired fields are selected, save the Workflow and continue.

4. Extract File. Will Generate the File to Be Extracted.

Pick an easily identifiable name that reflects the contents, such as daily_indicator_extract.csv.

Checking the Add date... box will automatically append a timestamp, such as daily_indicator_extract_20210101_120203.csv, which will make it easier to identify files processed, especially when running on a scheduler.

Click Add an element to add the fields previously selected in the Additional Data tab.

If a header is desired, use the Label field to change the header to be used on the file.

  • Under File Structure, select CSV (Excel).
  • Under Properties, select Do not generate a file if the inbound transition is empty.
  • Under Properties, select both options to enable transitions.

5. Transfer File. Will Transfer the File to the External Account.

Select Generate a transition to process errors.

Under the Protocol tab, select the Protocol (SFTP). Click Use an external account. Select the account setup previously. Enter the desired file path, if any, in the Path field. To upload to the user’s home directory, enter "."

Under Advanced Options, select Generate an error if no files are found.

6. End. Terminates the Workflow.

The normal, happy path through the workflow should be terminated with an End activity, and any error transitions that have been enabled should also be connected to an End activity. If desired, an error transition can also be used to send an error notification to a pre-defined error notification Audience.

Exporting KPI Data

We've reviewed how to access raw data inside ACS, including KPIs, for analysis beyond the normal functionality of the ACS dashboards and reporting, and how to export the raw data from ACS, enabling custom analysis to understand campaign performance.