Adobe Campaign, Marketing Automation

How to redo hot clicks report 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

Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

The Top 5 Stress-Busting Tips I have Learnt in 2023

3 minutes read

Working with any marketing automation platform, especially Salesforce Marketing Cloud, can provide a great user experience and a sense of accomplishment. However, it can also entail many stressful hours dedicated to solving seemingly easy issues. Here are my top 5 tips that made my daily work less stressed and saved me lots of cursing this […]

Continue reading
Adobe Campaign Classic tips
ACC Tips & Tricks, Adobe Campaign, Marketing Automation

Column must appear in GROUP BY clause

1 minute read

Creating aggregated data reports may not be a daily task, but there are times when you might be tasked with generating such reports. For instance, you could group and track log clicks to create the coveted ‘Hot Clicks Report’. You tell yourself, ‘It’s nothing major,’ and believe you have an idea of how to do […]

Continue reading
While loop automation
Automation studio, Salesforce Marketing Cloud

SFMC | How to Run an Automation on a Loop

2 minutes read

As many of us are aware, automation in the Salesforce Marketing Cloud can only be scheduled to run at minimum once per hour. While this frequency may suffice for most automations created within the Automation Studio, there are instances where we may require an automation to run continuously, immediately after it completes its previous run. […]

Continue reading
Salesforce Marketing Cloud vs Adobe Campaign
Adobe Campaign, Marketing Automation, Salesforce Marketing Cloud

Comparing Salesforce Marketing Cloud & Adobe Campaign

1 minute read

Both Salesforce Marketing Cloud and Adobe Campaign Classic are powerful marketing automation platforms that offer a wide range of features to help businesses manage their marketing campaigns. The choice between these two tools ultimately depends on your specific business needs and preferences. Here are some factors to consider when deciding between Salesforce Marketing Cloud and […]

Continue reading
Adobe Campaign Classic tips
ACC Tips & Tricks, Adobe Campaign, Marketing Automation

Fast select field in query builder

less than a minute read

Working with Adobe Campaign Classic often involves frequent mouse movements and clicks. However, after a while, you’ll discover certain tricks to work with this tool more efficiently and quickly. Today, I’ll show you a simple trick that will help you enter fields in the query builder faster and without having to move your mouse across […]

Continue reading