Adobe Campaign, Marketing Automation

ACC | How export hot clicks in workflow

Adobe campaign classic hot clicks extract workflow

In the world of email marketing, tracking recipient activity is essential to understanding the effectiveness of your campaigns. One valuable report that marketers often rely on is the “Hot Clicks Report,” which provides insights into recipient interactions, such as email clicks.

The report is included with the Adobe Campaign Classic installation, and you can simply click on any delivery report to open it.

  1. Open any delivery and navigate to the Reports tab
  2. Select Hot clicks

Many times, data analysts require this report to be automatically exported as raw data. To accomplish this, we will create a workflow that exports click counts for every delivery within a specific time span.

  1. Scheduler – scheduler to set as needed.
  2. JS - init – Simply calculating start and end date of tracking logs to extract
  3. Query – tracking logs
  4. Extract – extract tracking log aggregates to the SFTP (Alternatively you can ZIP and send it over email)

JavaScript activity

In our case I am getting entire year worth of aggregated hot clicks report. But you can also export only daily deltas similarly and then connect them in the external analytical tool.

//var d = new Date(); // for if daily extract is needed
//d.setDate(d.getDate() - 1);
//vars.dateStart = formatDate(d, "%4Y-%2M-%2D 00:00:00");
//vars.dateEnd = formatDate(d, "%4Y-%2M-%2D 23:59:59");
var d = new Date('2023-01-01');
vars.dateStart = formatDate(d, "%4Y-%2M-%2D 00:00:00");
vars.dateEnd = formatDate(new Date(), "%4Y-%2M-%2D 00:00:00");
  1. var d = new Date('2023-01-01');: This line creates a new JavaScript Date object, d, with the date set to January 1, 2023.
  2. vars.dateStart = formatDate(d, "%4Y-%2M-%2D 00:00:00");: Here, it uses the formatDate function to format the date stored in d. The formatted date is then assigned to the variable vars.dateStart.
    • %4Y represents the year with 4 digits (e.g., 2023).
    • %2M represents the month with 2 digits (e.g., 01 for January).
    • %2D represents the day of the month with 2 digits (e.g., 01 for the 1st day).
    • 00:00:00 represents the time as midnight (00 hours, 00 minutes, 00 seconds).
    So, vars.dateStart will contain the formatted date “2023-01-01 00:00:00.”
  3. vars.dateEnd = formatDate(new Date(), "%4Y-%2M-%2D 00:00:00");: This line creates another Date object using the current date and time (i.e., new Date()), formats it in the same way as before, and assigns it to the variable vars.dateEnd. This will give you the current date and time in the format “YYYY-MM-DD 00:00:00.”

In summary, this code initializes two variables, dateStart and dateEnd, with formatted date values. dateStart is set to January 1, 2023, at midnight, and dateEnd is set to the current date and time at midnight, both in the “YYYY-MM-DD 00:00:00” format. These variables can be useful for various purposes, such as setting date ranges for queries or reports.

Query tracking logs

The main activity of this extract workflow is a query activity, where certain special settings have to be configured.

We select our targeting dimension Recipient tracking logs.

Create query conditions as needed:

  • [url/@type] = Email click
  • [delivery/@FCP] = No – This means the delivery was not run in proof mode.
  • [delivery/scheduling/@contactDate]: Remove unwanted deliveries, e.g., deliveries that were created during proofing in the delivery activity.

(Optionally, you can add a date range.)

Next we create our aggregating select by Add data

We will select first option – Data linked to the filtering dimension and on second screen we select Data of the filtering dimension.

  1. Add data.. Data of filtering dimension. And add from left side any fields to the “Group by” clause especially [url/@Label] and counts by adding count(@id)
  2. Finish the wizard, when all fields are added
  3. Click on Edit additional data..
  4. VERY IMPORTANT:  Disable automatic adding of the primary keys of the targeting dimension
  5. After aggregated data query activity, you can simply extract data to SFTP or anywhere.

NOTE: Step 2 and 3 is needed as while adding data the additional settings are not accessible they are only accessible when editing data.

Extract activity

Set the fields to extract as needed, but remember to map your enumerations.

Oh hi there 👋
I have a FREE e-book for you.

Sign up now to get an in-depth analysis of Adobe and Salesforce Marketing Clouds!

We don’t spam! Read our privacy policy for more info.

#JavaScript #SQL #workflow
Marcel Szimonisz
Marcel Szimonisz
MarTech consultant As a marketing automation consultant, I specialize in solving problems, automating processes, and driving innovation in my clients' marketing platforms.

I hold certifications in Adobe Campaign v6 (3x certified) and Salesforce Marketing Cloud (5x certified).

Additionally, I serve as a community advisor for Adobe Campaign, offering expert insights and guidance.

In addition to my professional pursuits, I also enjoy exploring various programming languages, CMSs, and frameworks, further enhancing my technical expertise and staying at the forefront of industry advancements.
Take a look at our subscription offering in case you are looking for a Marketing Automation Consultant.

Leave a comment

Your email address will not be published. Required fields are marked *

Similar posts that you may find useful

Adobe Campaign Classic REST over SOAP
Adobe Campaign, Marketing Automation

ACC TIP | Build REST over SOAP API

4 minutes read

If you’re familiar with Adobe Campaign Classic, you may have noticed that it utilizes the SOAP (Simple Object Access Protocol) API. We live in an age where REST is taking over, and the good old XML SOAP is slowly being forgotten. To me, as an old-timer, I can confidently say that it doesn’t make any … Read more

Continue reading
SFMC tips and tricks
Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

SFMC TIP | Google Analytics Integration 1

less than a minute read

When you integrate with Google Analytics, marketing cloud will add tags to all your links. When this happens, all dynamic links will be broken. Links that are defined programmatically as variable, field or attribute with GA integrated will look as if the tracking link also appends google analytics tags. This will make such link not … Read more

Continue reading
SFMC tips and tricks
Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

SFMC TIP | Data views for transactional emails

1 minute read

Data views in Salesforce Marketing Cloud are very handy, when it comes to getting data insights from various sources inside the platform. I will give you a quick start on how to get data insights for you transactional emails using data views. We will use data from _Sent, _Job, and _Subscriber view s.TriggererSendDefinitionObjectID – object … Read more

Continue reading
Salesforce marketing cloud journey exit criteria and goals
Marketing Automation, Salesforce Marketing Cloud

SFMC | How to use journey exit criteria and goals

3 minutes read

I might be really slow learning but I learned about this feature two years into working with salesforce marketing cloud, and I said to myself what a nice feature to have. Let me tell you what it is and give you some real life examples when to use it. Exit criteria and goals share a … Read more

Continue reading
Adobe campaign tips and tricks
ACC Tips & Tricks, Adobe Campaign, Marketing Automation

ACC TIP | Escalate user rights

1 minute read

Normally web apps run under the web app user (which is anonymous and has very little to zero rights by default) and for certain operations you would need to require to grant additional access, or even grant full admin. To grant full admin for web app user is not solution to go with. Instead I … Read more

Continue reading