February 25, 2014 · Fuel Staff
How To Create Automated Reports With SiteCatalyst Report Builder
Every analytics team has those reports that must be done but don’t exactly go deep into any analysis. Maybe it’s a weekly KPI report for the C-suite or a monthly overview for a client. This blog will show you how through automated rolling dates, publishing lists and scheduled sends; you can create reports that are more efficient and less time consuming.
Let’s get started.
Before switching to fully automated reports, all of our Excel workbooks that had Report Builder had to have a key that looked something like this….
You can see that it is pretty simple. Change the date in the highlighted cell to whatever start date you wanted and the rest of the cells calculated from the highlighted cell. From there, the Report Builder data block would pull from one of these dates. This method of calculating dates is ok, but not great. Automated dates will allow you to pull reports not only in a more efficient way but will also allow the reports to be automatically sent to the end user.
What Are Automated Rolling Dates?
Rolling dates along with customized date expression allows you to specify a time frame that you want data to be pulled from. These expressions calculate the date range automatically based off of today’s date and once these expressions are set they never need to be updated. That’s right, no changing dates, no calculating dates and no entering the wrong dates. It creates efficiency and a systems check all in one easy change.
When To Use Automated Rolling Date
1)When the report is run on a reoccurring basis.
2)For reports that will be automatically sent to users.
3)On any report that is always pulling a consistent amount of days.
Let’s get started.
If you look below in cells B1 and B2 you can see the key and the dates for a weekly report. Right below that in the Report Builder request wizard you can see the new automated way to set rolling dates.
So How Do I Make It Work?
Refer to the picture above.
1)Make sure to choose “rolling dates” from the drop down.
2)Under customized expressions enter a single or a combination of commands to return the start and end date that you need. See commands below.
a) If you need to calculate a time period for previous years you will need to use multiple commands. Ex. If you wanted to run a report every Wednesday to see the previous seven days compared to the same days last year, the calculation to see last year’s data would be as follows. Start date: cd-12m-7d End Date: cd-12m-1d.
Even though rolling automatic dates are very helpful and cool, the next topic will take automation to a whole new level.
Automated reports are only possible with automated dates (see above). Once automated dates are set up, it allows you to automatically send of the reports to the end users. You can either set up the automated reports to send to a specific person or to a list of people.
Creating A Publishing List
If the report will be sent to many people on a reoccurring basis, the most efficient way to achieve this is to create a publishing list. A publishing list is a list you set up in SiteCatalyst that specifies the people receiving the report as well as specific report suites that the list has access to. Here’s how you create one.
1) In the admin section of SiteCatalyst, go to user management.
2) Click on the publishing list manager tab.
3) In the top right corner click on Add Publishing List.
4) Choose the correct report suites that should be included.
5) Edit the email address area and add the email addresses that you want your report to be sent to.
6) Save and exit.
Creating A Scheduled Report
Now that you have your automated dates and a publishing list set up you are ready to schedule the report. Here’s what you do.
1) Make sure all automated rolling dates are set up.
2) Make sure a publishing list is set up (If sending to multiple users on a regular basis)
3) In Excel, go to the Add-Ins tab and click on Schedule.
4) In the schedule manager click on New.
5) Select the report you wish to automate as well as the publishing list from the drop down.
6) Choose a scheduling cycle and hit Ok. Or go to advanced options for more detailed send requests. (Once you click OK, it may take a few minutes for it to complete the setup process)
There you go! You just created a fully automated report.
It’s easy to see how these two things increase the efficiency of reporting. Just by eliminating the manual work of updating, running and sending the reports it will save countless hours of work. I hope this helps you get started on the road to automated and efficient reporting.
For more information on how to better utilize Adobe Analytics, hop over to our guide to building an Adobe Analytics dashboard.