How can we report on click tracking for all emails sent as part of a recruitment campaign?

GraemeHarper

Problem Description: Our recruitment campaign includes two emails:

1. Email A: Contains buttons such as "Option A" and "Option B," linked to campaign goals. The goal response labels (e.g., "Option A") are reportable via the “Goal ID” column.
2. Email B: Contains buttons such as "Option X," "Option Y," and "Option Z," but these were created directly in the email instead of as campaign goals, because adding new goal responses after campaign activation isn't possible.

Challenge: While Email A allows clear click tracking via the goal response labels, Email B does not. I located unique URL_IDs for each button in Email B, but there’s no table or subject area linking these IDs to the button labels. As a result, reporting for Email B only shows numeric URL IDs without clarity on their corresponding labels (e.g., "Option X," "Option Y," etc.). How can I resolve this and link the numeric IDs to their respective labels for reporting purposes?

John Hung-Oracle

Hi Graeme, you can also try posting your question in the Reporting and Analytics for HCM forum since its specific to HCM subject areas/use cases. Thank you.

Riyaz Ali-Oracle

Since Email B’s buttons were created directly in the email without defined campaign goals, the challenge is mapping URL_IDs to their corresponding button labels for reporting clarity.

Try below steps to resolve this:

1. Manually Create a Mapping Table:

Since the reporting table only contains numeric URL_IDs, you’ll need a custom mapping table to associate each URL_ID with its correct button label.

Example:

sql
CREATE TABLE URL_Label_Mapping (
URL_ID VARCHAR2(50),
Button_Label VARCHAR2(100)
);

INSERT INTO URL_Label_Mapping VALUES ('12345', 'Option X');
INSERT INTO URL_Label_Mapping VALUES ('67890', 'Option Y');
INSERT INTO URL_Label_Mapping VALUES ('54321', 'Option Z');

Join this mapping table in your report query to display correct button labels.

2. Use a CASE Statement in OTBI or BI Publisher:

If creating a separate table isn’t feasible, use a CASE statement in your OTBI report or BI Publisher SQL query:

sql
SELECT
URL_ID,
CASE
WHEN URL_ID = '12345' THEN 'Option X'
WHEN URL_ID = '67890' THEN 'Option Y'
WHEN URL_ID = '54321' THEN 'Option Z'
ELSE 'Unknown'
END AS Button_Label
FROM Campaign_Click_Data;

This allows you to assign labels dynamically in the report.

3. Extract Button Labels via HTML Parsing:

If button names are embedded inside the email’s HTML structure:
a) Check if the email source contains identifiable button metadata.
b) Extract labels by parsing the HTML stored in a database.

4. Store URL IDs & Labels in a Custom OTBI Column:

If possible, update OTBI report criteria by manually mapping URL_IDs to labels in a calculated field or filter.