Timeframe modifiers for behavior criteria in queries
Before using timeframe modifiers, the organization must have (new) queries enabled at the organization level. Timeframe modifiers are available only for behavior criteria in queries.
Using time frame modifiers for behavior criteria gives you much more control.
Essential facts about timeframe modifiers:
- The default is 30 days.
- Timeframe for behavior modifiers in queries created before 8.5 automatically converts to at any time when the org upgrades.
- At any time is restricted by the Organization Settings (typically 450 days - but it could be different).
- You can use timeframe modifiers against behavior criteria within the decision diamond rules of your program.
- You cannot build timeframe modifiers against behavior criteria within the entrance rules of your program.
- You cannot set up a modifier that extends beyond the time that Acoustic Campaign retains data for the behavior. For example, if the Contact Tracking Retention Days setting is at the default of 450 days, and if you try to use "within the last 451 days" for an email behavior time frame, you cannot save this.
Note: A valid value is required, and the error message tells you whether the setting can be extended. The maximum setting for Contact Tracking Retention Days is 450 and cannot be increased.
Timeframe modifiers available for behavior criteria in queries
You can choose from multiple time frame modifiers for behavior criteria in queries.
| Modifier | Description |
|---|---|
| at any time | When did the contact ever have this behavior within the retention setting in the Acoustic Campaign? |
| on today | What contacts had this behavior today? |
| before today | What contacts had this behavior before today (not including today)? |
| on mm/dd/yyyy | What contacts had this behavior exactly on the named date (excluding all other dates)? |
| before mm/dd/yyyy | What contacts had this behavior before the named date (excluding the named date)? |
| after mm/dd/yyyy | What contacts had this behavior after the named date (excluding the named date)? |
| between mm/dd/yyyy and mm/dd/yyyy | What contacts had this behavior between the named dates (including the named date)? |
| within the last x [days/weeks/months] | What contacts had this behavior within the named period (including the named period)? |
| exactly x [days/weeks/months] ago | What contacts had this behavior exactly on the named period (excluding all other dates)? |
| more than x [days/weeks/months] ago | What contacts had this behavior beyond the named period (excluding the date named)? |
| less than x [days/weeks/months] ago | What contacts had this behavior within the named period (excluding the date named)? |
Query timeframe options for the day, week, and month.
| Time-released | Definition |
|---|---|
| Day | Date value - calculates by full calendar days - the number of times that midnight is crossed by the user that executes the query. |
| Week | Seven days - multiplies the value by seven days for calculation. Looks for values on that day or within that range of days. Does not look earlier in the week than the date in the analysis. |
| Month | Calendar month - calculates by calendar months, not by a set number of days. |
The query operator is within the last day
The query operator is within the last 1 day pulls contacts from yesterday and today for date and timestamp fields. By default, the time zone is based on the time zone in the user's profile, who set up the query, not GMT.
A query evaluates for 48 hours as in, yesterday at midnight up through today at 23:59:59.
Below is a sample snippet of SQL where 'Opt-In Date is within the last 1 day' is being used:
The range being evaluated includes the previous day beginning at midnight (12:00:00 AM) through the then-current day ending at 11:59:59 AM for a total of 48 hours (technically 47 hours 59 minutes 59 seconds).
For example, when you use time frame elements in behavioral queries, Acoustic Campaign uses 24-hour increments rather than using calendar date.
Jim sends a promotional email at 3 PM on Wednesday and wants to run a query the following day using the time frame within the last [1] day(s) in his query to return contacts who received the email. If the query is run on Thursday before 3 PM, contacts who received the specified template are returned in the query. If Jim runs the query using the 1 day time frame after 3 PM, but still on Thursday, the query is not expected to return contacts who received the email on 3 PM the previous day as more that 24 hours have elapsed.
Webforms and "within 1 month" queries
When running a behavior query on a recipient submitting or visiting a web form, why do the results differ when I have a 'within 1-month' timescale versus an 'is between 1st and 31st' timescale? Isn't 'within the last 1 month' looking at the whole of the previous month's data?
When you run a query stating 'within 1 month,' the system subtracts 1 day from the current date and uses this as the end date for your query. After this, the system takes that same date but from the previous month as the start date. e.g. If today is April 8 and I ran that query it would be doing the following:
- today is April 8, therefore 8-1 = 7.
- Set April 7 as the query end date.
- 1 month before is March.
- Set March 7 as the start date.
The above does not select the information for the entire previous month. As you can see, this query would only provide the same information as 'is between 1 March 2021 and 31 March 2021' if it were run on April 1st.