Timespan-based Reporting With Eloqua Insight

With Oracle Eloqua's Insight, there's a lot more you can do than meets the eye. Simply ask your Customer Administrator to add an Analyzer license to your user, and you can get started. In this video, I will show you how to take advantage of an advanced technique to create timespan-based reports that would not otherwise be possible.  Normally, you can't automate reports like "Campaign Analysis Overview - Last Month", but this technique lets you get around that restriction. 

The video references some SQL statements that you'll need to use when setting up these reports.  Here are those statements: 

Use these statements to set up reports for last month: 

  • First day of current month (use with "less than" filter):
    • TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE) +1 , CURRENT_DATE)
  • First day of previous month (use with "greater than or equal" filter):
    • TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Use these statements to set up reports for last year:

  • First day of current year (use with "less than" filter):
    • TIMESTAMPADD(SQL_TSI_DAY, EXTRACT(DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)
  • First day last year (use with "greater than or equal" filter):
    • TIMESTAMPADD(SQL_TSI_YEAR , -1, TIMESTAMPADD(SQL_TSI_DAY, EXTRACT(DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

About the Author

Andy Shaw

Andy is a Lead Technology Consultant at Tegrita with over twenty years of experience in marketing automation and software product management. He is an Eloqua Implementation Specialist, and focuses on using technology to find practical solutions to customer needs. Andy has a bachelor's degree from Princeton University and an MBA from UCLA's Anderson School.

More Content by Andy Shaw
Previous Video
Foundation of the Sports Marketing Tech Stack: A Lesson from the Three Little Pigs
Foundation of the Sports Marketing Tech Stack: A Lesson from the Three Little Pigs

Budget and resources are often limited for sports marketers. You must choose technology to increase your ti...

Next Video
What CX Industry Trend Keeps You Up at Night? CX Heroes Series
What CX Industry Trend Keeps You Up at Night? CX Heroes Series

At Modern Customer Experience 2018, CX Heroes share the industry trends that keep them up at night, or prov...