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.
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.
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.
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.
Now that the basic stats from each day are logged, they can be grouped together by month.
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.
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.
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:
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:
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.