Creating a Custom Report in Salesforce Marketing Cloud to Track Account Health
Creating a Custom Report in Salesforce Marketing Cloud to Track Account Health
Ed Gray
This resource was originally posted on DEV by LearningCurv Marketing Automation Consultant Ed Gray.
Reporting in Salesforce Marketing Cloud can be challenging for new users. The “reports” available through Analytics Builder are limiting and difficult to work with. But with a little knowledge of SQL and Data Views, creating data tables tailored to a business use case becomes possible.
Below is an example of a report created to track the basic “health” of a business unit: subscriber growth and the number of emails sent, opened, clicked and bounced. It’s completely based upon Marketing Cloud’s Data Views, so the exact same SQL queries should work for any account.
Some queries could be combined or separated depending on the number of records in the business unit—this was developed for a client with more than 1.2 million records and never took more than a few minutes.
Follow the steps below to learn how to go beyond the standard reports provided in Marketing Cloud.
1. Run Server-Side JavaScript
Using this SSJS will ensure the fields are the exact same name and data type needed for the SQL queries below to work without modifications.
For more information on how you can create data extensions using SSJS and WSProxy, read Ivan Razine’s exceptional guide here.
Run a SSJS activity to create the 3 data extensions needed for the report using this code:
2. Append Data Extension with SQL Activity
Why append? Because many Data Views only track the last 180 days of activity.
By appending new records, we can keep some basic information about the account longer. This could be useful if we wanted to make year-over-year comparisons, etc.
Create a SQL activity that appends the Account_Health_Daily data extension with the following code:
3. Overwrite Data Extension to Sort by Day
Unfortunately, appending Account_Health_Daily means the most recent day’s stats will be added to the end of the data extension.
Since SQL can’t overwrite the same data extension used as a source, a second data extension will be made to sort the records by most recent day first.
Use the following code to overwrite the data extension Account_Health_Daily_Sorted:
4. Group Stats by Month
Now that the basic stats from each day are logged, they can be grouped together by month.
Start with the following code to overwrite the Account_Health_Monthly data extension:
5. Record Active Subscribers
Next, record the number of active subscribers on the 1st of the month using the following SQL code to update Account_Health_Monthly:
6. Calculate Monthly Engagement
Finally, calculate the open, click and bounce rate for the month by updating Account_Health_Monthly with this SQL query:
7. Schedule Daily Automation to Update Data
Now an Automation can be created to append and update the data extensions. Schedule an Automation to run every day, with the SQL activities in the following order:
- Appends Account_Health_Daily with Data View info
- Overwrites Account_Health_Daily_Sorted
- Overwrites Account_Health_Monthly with Account_Health_Daily info
- Updates Account_Health_Monthly with Active Subscribers
- Updates Account_Health_Monthly with Open, Click, Bounce Rates
Adding Previous Days
The SQL queries appending the daily log are carefully written to only include data from the previous day (and only that one day).
To append Account_Health_Daily with previous days, use the following code:
The above query will look into the data extension, find the oldest day on record, and append the next oldest. Keep running it manually or place it in an automation to run until Account_Health_Daily has enough information to be useful.
Presenting
Reporting Data
Data extensions are the basis of all custom reporting in Marketing Cloud, but they can be difficult for humans to sift through for useful information.
Below are two examples of HTML blocks to build inside Content Builder and place into an email or cloud page:
They group together the business unit’s basic stats for the previous week—and three more weeks for context. It’s a good start to presenting the information from the data extensions in a way that is actually helpful.
Example Marketing Cloud Reports
Below are a few snapshots of what these HTML block reports look like in practice.
We can pull a number of insights from a visual report like this, for example:
- The week of September 29th had a much smaller number of sends, so the resulting stats are absurd and should be ignored—the open/click rate is likely explained by people opening emails sent weeks prior.
- There is a high enough jump in bounces for the week of September 22nd to warrant further investigation. We found Comcast decided to block the client’s emails and were able to resolve the issue as a result.
- We should work to figure out what was done to triple subscriber growth between October 6th and the 13th—and replicate it if possible.
Additional Considerations for Reporting
- Subscribers can open and click on emails days or months after they were sent. Don’t be surprised if there are single days where a business unit has stats that appear impossible.
- Reporting in Marketing Cloud is rarely 100% accurate. Look for trends not exactness in your data.
- The code needed for this report can be found in a GitHub repository here.
Conclusion
With the above code, you can set up the necessary data extensions, an automation to refresh them, and an HTML block to neatly present the data in less than an hour.
It will help answer some common questions like:
- When did our open or click rates start to increase or decrease?
- When did our account grow or shrink?
- When did our bounce rate suddenly increase?
This “Account Health” report is just one example of the awesome things you can develop with a little SQL in Marketing Cloud.
If you need some guidance for customizing your Marketing Cloud instance, explore LearningCurv’s Marketing Cloud consulting services. Our certified experts will help you get the most out of the platform.
Need help mastering Marketing Cloud?
