How to associate the broadlogid with the content of the mail?

[apec_abn]

Hello community,

I generate a delivery with duplicates :
email 1, 2 & 3 for recipient A,

email 4 for recipient B.
An offer or order number differs in each email.

In a workflow, how can I retrieve the broadlogid of each email corresponding to the correct offer or order number?

Thank you in advance for your help.

[akshaaga]

Hi @apec_abn,

To retrieve the broadlogid of each email corresponding to the correct offer or order number, you can use a query activity in your Adobe Campaign workflow.

Firstly, create a query activity in your workflow that joins the delivery logs and the recipient table based on the recipient ID. This will allow you to link the delivery logs to the correct recipient and retrieve their email and offer/order number.

Next, you can use a data loading activity to load the email and offer/order number data into a new table or update an existing one.

In the data loading activity, you can use a SQL statement to select the necessary fields from the joined delivery logs and recipient table, and insert or update the data in the target table.

The SQL statement -

SELECT dl.broadLogId, r.email, dl.offerNumber

FROM deliveryLogs dl

JOIN recipient r ON dl.recipientId = r.recipientId

In this example, the query selects the broadLogId, email, and offerNumber fields from the deliveryLogs table and recipient table, and joins them based on the recipientId field.

Once the data has been loaded into the target table, you can use it in subsequent workflow activities as needed.

[apec_abn]

Thanks for your answer.

Sorry, I was off, and I did not express my problem well.

In fact, it's not the email address that I want to retrieve, but the content of the email because there is a custom field in it that I hope will allow me to link the broadlog associated with a line of a functional table.

In your SQL statement, in my case, I don't have offerNumber (custom field in fact) in my deliveryLogs table (nmsbroadlogrcp).

[akshaaga · Accepted solution]

Hi @apec_abn ,

To retrieve the content of the email and match it to a custom field in a delivery, you can follow these steps:

1. Set up a query to retrieve all the deliveries sent within the timeframe of interest, including the delivery ID, recipient email address, and the custom field value.

2. Add a join activity to join the query results with the delivery logs table (nmsbroadlogrcp) on the delivery ID.

3. Add a filter activity to filter the results to only include the desired custom field value.

4. Add a query activity to retrieve the email content for each matching delivery. This can typically be done by joining the delivery logs table with the delivery content table (nmsdelivery), and selecting the appropriate columns.

5. Send the email content values to a target data extension or other destination for further analysis or processing.

Note that the exact SQL statements and logic may depend on the specific marketing automation platform or system you are using, as well as the schema of your database. However, the general steps outlined above should apply in most cases.

[apec_abn]

Thanks a lot.

[Sukrity_Wadhwa]

Hi @akshaaga,

Could you please help @apec_abn further with the query?

Thanks!