Timespan-based Reporting With Eloqua Insight

January 17, 2019 Andy Shaw

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))
Previous Video
TegTalk: Opportunities and Obstacles of Remote vs In Office Marketing Teams (Episode 11)
TegTalk: Opportunities and Obstacles of Remote vs In Office Marketing Teams (Episode 11)

Many CMOs and marketing leaders are finding it more challenging to find top talent. Historically, marketing...

Next Article
Don’t Stop at Hello!  Avoid Personalization Pitfalls
Don’t Stop at Hello! Avoid Personalization Pitfalls

80% of consumers are more likely to do business with a company if it offers a personalized experience so wh...