Dynamic date ranges in Eloqua Insight allow you to flexibly analyze data based on specific time frames that automatically adjust according to the current date. These dynamic date ranges can be particularly useful for generating reports and analyzing campaign performance over periods such as the last 7 days, current month-to-date, previous month, or custom ranges.

With the help of TIMESTAMP SQL expressions, analyzers can build customized reports with dynamic date ranges, not currently offered as a built-in feature in Eloqua Insight.

TIMESTAMPADD manipulates data of the data types of DATE and DATETIME based on a calendar year.

Syntax: TIMESTAMPADD(interval, expr, timestamp)

Example: TIMESTAMPADD(SQL_TSI_MONTH, -12, CURRENT_DATE)

Timestamp Interval (TSI) Options:

• SQL_TSI_SECOND
• SQL_TSI_MINUTE
• SQL_TSI_HOUR
• SQL_TSI_DAY
• SQL_TSI_WEEK
• SQL_TSI_MONTH
• SQL_TSI_QUARTER
• SQL_TSI_YEAR

Here are two use cases where the TIMESTAMP function has been employed to generate reports showcasing dynamic date range outcomes.

Use Case 1: Retrieve all contacts created in the past 12 months (Rolling 12 months)

Steps:

1. Create a filter on Contact Created Date/Time

2. Choose the operator: is greater than

3. From the Add More Options drop-down, select SQL Expression

4. Enter the expression: TIMESTAMPADD(SQL_TSI_MONTH, -12, CURRENT_DATE) and OK

5. If we want the data to be displayed by month, add Month (Contact Created Calendar) to your selected columns. Make sure to delete Contact Created Date / Time from the selected columns.

The results will be displayed as follows:

Use Case 2: Retrieve email delivery metrics in the past 12 months (Rolling 12 months)

1. Apply steps similar to the example before, but this time filter on Email Send Date. Use the same SQL expression: TIMESTAMPADD(SQL_TSI_MONTH, -12, CURRENT_DATE)

2. Remove Email Send Date from your columns and add Month (Email Send Calendar). Apply Sorting to this column to display months in a preferred order, in this case descending.

The results will be displayed as follows:

Other Examples of Dynamic Date Expressions

Yesterday: TIMESTAMPADD(SQL_TSI_DAY, -1, CURRENT_DATE)

Rolling 30 days: TIMESTAMPADD(SQL_TSI_DAY, -30, CURRENT_DATE)

Since the 1st of the current month: TIMESTAMPADD(SQL_TSI_DAY, -(DAYOFMONTH (CURRENT_DATE)-1), CURRENT_DATE)

Since the beginning of the year: TIMESTAMPADD (SQL_TSI_DAY, -(DAYOFYEAR (CURRENT_DATE)-1), CURRENT_DATE)

Final Thoughts

By leveraging dynamic date ranges, marketers can effectively track trends, monitor performance, and make data-driven decisions without the hassle of manually updating date filters. This functionality streamlines the reporting process, ensuring that insights are always timely and actionable.

Ready to explore more on this topic? Visit our Eloqua reporting dedicated articles or contact us with any Eloqua inquiry. We are always ready to help!