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.
Note the following about timeframe modifiers:
- Default is 30 days
- Timeframe for behavior modifiers in queries created prior to 8.5 automatically convert to at any time when the org upgrades.
- At any time is restricted by the Organization Settings (typically 450 days - but could be different).
- You can use timeframe modifiers against behavior criteria within 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 amount of 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 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 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 | 7 days - multiplies value by 7 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 calculation. |
| Month | Calendar month - calculates by calendar months, not by set number of days. |
Query operator is within the last 1 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 profile of the user who set up the query, not GMT.
A query evaluates for a 48 hour time span 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.
Web forms and "within 1 month" queries
Why does a "within 1 month" web form query show all of last months data and differ from "between 1st and last of the month"?
When running a behavior query on 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 months data?
When you run a query stating 'within 1 month' the system is subtracting 1 day from the current date and using this as the end date for your query. After this the system is taking 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.
Comments
0 comments
Please sign in to leave a comment.