Some weeks ago, one of our reporting teams needed to have an Eloqua Form submission report to be sent out every month containing the last month's worth of submission data. For people who use Insight Reporting on a daily basis, it’s very common to choose a time span date filter instead of a date range filter when running a report (e.g. Last 7 days). But what happens when you want to schedule that report for a distribution list to receive it containing the last month worth of data? Since it’s a scheduled report, there is no prompt option to select the date range, and usually when configuring a fixed filter on a report, the filter automatically gives you the option to use a range instead of a span. So, how do you do it? If you took the Insight for Analyzers course and have an Analyzer license, this solution will work for you.
When creating a date filter on an Insight report, these are the options that you usually see -- options to specify an exact date value for the report.
In our case, we want a time span instead of a date range or value, and to configure it we need to click the calendar icon beside the date box.
As you can see, bellow the calendar, there is an option that says, “Switch to dynamic date.” After clicking this option, you will be prompted to configure the dynamic date.
In this screen, you can specify the dates you are interested in, using simple math. For example, if you want to include the last 7 days of data, on this screen you will select Today “minus” “7” Day(s). After that you will notice that below there is a “Preview” box where you can see to which day the configuration will resolve to. Once you click OK, you will notice the “resolved” date appearing in the date box.
Now, it’s just a matter of changing the operator that validates this “resolved” date from “Equals” to “Greater Than or equal to” to obtain the last 7 days of data. After clicking OK, you will see something like this:
So now, every time you run this report it will check today's date, subtract seven days from it, and bring you back any record that has a date equal to or greater than the 7 day subtracted date.
Now, it’s just a matter of saving the report and configuring a schedule to send it every Monday. Now each Monday, you will get a report on the inbox of all the specified emails with a report containing the data of the last seven days automatically.
As you saw in the previous screenshots, this tool gives you different configuration options, so you can do different date spans using hours, days, weeks, months and years. This allows you to build more complex date spans. For example, if we want to send a report every month containing the data of the last full month, assuming that today’s date is September 29, we’d like the filter to bring data from August 1st to August 31st.
Such report configuration should look something like this:
So as you can notice, the report will provide you a resolved date for you to verify the span works as expected using today’s date, so this will dynamically change as time goes by.
In conclusion, by configuring this kind of dynamic date filters, we can save a lot of time running a report periodically and just programming it to be delivered continuously to a set of email addresses without worrying about including an excessive amount of data. Before using this approach, we had the business stakeholders go into Insight and run a report each week or month to pull the data they needed. Now they receive this report automatically in their inbox without the need to filter the dates in Insight or Excel. This is advantageous not only because of the time saved filtering the report dates, but also by being able to restrict Insight access to users that just needed to pull a periodical report, therefore putting less stress on the Insight’s server and having a better control of the User’s access level.
In order to learn how to properly use Insight and obtain the maximum advantage from this solution, I recommend you take the following Eloqua courses found in the Oracle Marketing Cloud Academy:
- Insight for Reporters
- Working with insight Reports
- Customizing and Sharing Insight Reports
- Insight for Analyzers