We have created list of all soft bounces when we are trying to export we are not able to get reasons. Tried using SQL and Salesforce report, in both case we are not able to get reason for softbounces.
In Pardot, the "Bounced" activity type indicates that an email was not delivered to the recipient's inbox. However, Pardot does not explicitly distinguish between "Soft Bounces" and "Hard Bounces" in the VISITOR_ACTIVITY.TYPES table. To differentiate between these two types of bounces, you'll need to consider additional information and logic.
Here's a general approach to deriving "Soft Bounces" and "Hard Bounces" from Pardot tables in Power BI:
1. Identify Bounces:
First, filter the VISITOR_ACTIVITY table to include only records with the activity type 'Bounced'.
2. Gather Additional Information:
In order to differentiate between "Soft Bounces" and "Hard Bounces," you'll need to analyze the information available in the VISITOR_ACTIVITY table. Look for any relevant fields that might indicate the nature of the bounce.
3. Determine Soft Bounces and Hard Bounces:
You might need to examine bounce codes, error messages, or any other relevant fields to categorize bounces as "Soft Bounces" or "Hard Bounces." Typically, "Soft Bounces" are temporary delivery failures (mailbox full, server temporarily unavailable, etc.), while "Hard Bounces" are permanent delivery failures (invalid email address, domain doesn't exist, etc.).
4. Create Measures:
Once you have identified the criteria to differentiate between the bounce types, you can create custom measures in Power BI. These measures will count the occurrences of "Soft Bounces" and "Hard Bounces" based on your categorization.
For example, assuming you've identified that certain bounce codes correspond to "Soft Bounces" and others to "Hard Bounces," you could modify your SQL query like this:
SQL QUERY:-
SELECT
le.name,
CASE
WHEN va.type = 1 THEN 'Click'
-- other activity type cases
WHEN va.type = 13 AND va.bounce_code IN ('soft_bounce_code_1', 'soft_bounce_code_2') THEN 'Soft Bounce'
WHEN va.type = 13 AND va.bounce_code IN ('hard_bounce_code_1', 'hard_bounce_code_2') THEN 'Hard Bounce'
ELSE '?'
END AS VA_TYPE,
COUNT(*)
FROM
VISITOR_ACTIVITY va
INNER JOIN EMAIL e ON
e.ID= va.EMAIL_ID
INNER JOIN LIST_EMAIL le ON
le.ID= va.LIST_EMAIL_ID
WHERE
le.name= 'xxxxxxxxxxxxxxxxxx'
GROUP BY 1, 2
ORDER BY 3 DESC;
Please note that the specific bounce codes and criteria will depend on your Pardot instance's configuration and the information available in the VISITOR_ACTIVITY table. You'll need to adapt the query accordingly.
Remember to replace the placeholder `'soft_bounce_code_1'`, `'soft_bounce_code_2'`, `'hard_bounce_code_1'`, and `'hard_bounce_code_2'` with actual bounce codes that you identify as indicative of "Soft Bounces" and "Hard Bounces.
