USING FILTERS IN CHURNIQ
Filters are an easy way to quickly look at your data from a variety of different angles. And usually the precise underlying logic of a filter operation will not impact hugely on the questions you are trying to answer
However, it is likely that on some occasions the logic of a particular filter expression will matter a lot to what you are trying to find out. An example of a filter expression is “in the past 3 days”, or “since March 1st”.
So here we will break down the exact meaning of the different filter expressions in the ChurnIQ data platform for you. To quickly jump to different sections, use the links on the right of the page.
Date Filtering
The most used filter in any analytics platform is the date filter, as you try to understand the change in some metric over time. Here we will provide you with a handy reference guide for the logic behind the different types of date filtering.
Data Refresh Schedule
The first thing you need to know is how recently your ChurnIQ analytics have been updated to reflect real-world events. For the most up-to-date refresh schedule and how it affects your data, please refer to this article.
Basic structure of date filters
For the examples we provide here, [n] is a whole number, and [interval] is some increment of time, such as a day or a month.
|
Filter Combination |
Example |
Notes |
|
this {interval} |
this day |
|
|
{n} {interval} |
3 days |
|
|
{n} {interval} ago |
3 days ago |
|
|
{n} {interval} ago for {n} {interval} |
3 months ago for 2 days |
|
|
before {n} {interval} ago |
before 3 days ago |
|
|
before {time} |
before 2018-01-01 12:00:00 |
|
|
after {time} |
after 2018-10-05 |
after is inclusive of the time you specify. So, the expression after 2018-10-05 will return data from 2018-10-05 and all dates later than 2018-10-05. |
|
{time} to {time} |
2018-05-18 12:00:00 to 2018-05-18 14:00:00 |
The initial time value is inclusive. The latter time value is not. So the expression 2018-05-18 12:00:00 to 2018-05-18 14:00:00 will return data with the time “2018-05-18 12:00:00” through “2018-05-18 13:59:59”. |
|
{time} for {n} {interval} |
2018-01-01 12:00:00 for 3 days |
|
|
today |
today |
|
|
yesterday |
yesterday |
|
|
tomorrow |
tomorrow |
|
|
{day of week} |
Monday |
Specifying a day of week with a Dimension Group Date field returns the most recent date that matches the specified day of week. For example, the expression Dimension Group Date matches (advanced) Monday returns the most recent Monday. You can also use {day of week} with the after keyword in this context. For example, the expression Dimension Group Date matches (advanced) after Monday returns the most recent Monday and everything after the most recent Monday. Specifying a day of the week with a Dimension Group Day of Week field returns every day that matches the specified day of week. So the expression Dimension Group Day of Week matches (advanced) Monday returns every Monday. |
|
next {week, month, quarter, fiscal quarter, year, fiscal year} |
next week |
The next keyword is unique in that it requires one of the intervals listed above, and will not work with other intervals. |
|
{n} {interval} from now |
3 days from now |
|
|
{n} {interval} from now for {n} {interval} |
3 days from now for 2 weeks |
Remember date filters can also be combined together using and/or functionality:
To get OR logic: Type multiple conditions into the same filter, separated by commas. For example, today, 7 days ago means “today or 7 days ago”.
To get AND logic: Type your conditions, one by one, into multiple date or time filters. For example, you could put after 2014-01-01 into a Created Date filter, then put before 2 days ago into a Created Time filter. This would mean “January 1st, 2014 and after, and before 2 days ago”.
Absolute Dates
Absolute date filters use specific date inputs to generate your analytics. They give you full control over the time parameters of your data.
Here is a reference guide for the logic of your absolute date filters within ChurnIQ.
|
Example |
Description |
|
2018/05/29 |
some time on 2018/05/29 |
|
2018/05/10 for 3 days |
from 2018/05/10 00:00:00 through 2018/05/12 23:59:59 |
|
after 2018/05/10 |
2018/05/10 00:00:00 and after |
|
before 2018/05/10 |
before 2018/05/10 00:00:00 |
|
2018/05 |
within the entire month of 2018/05 |
|
2018/05 for 2 months |
within the entire months of 2018/05 and 2018/06 |
|
2018/05/10 05:00 for 5 hours |
from 2018/05/10 05:00:00 through 2018/05/10 09:59:59 |
|
2018/05/10 for 5 months |
from 2018/05/10 00:00:00 through 2018/10/09 23:59:59 |
|
2018 |
entire year of 2018 (2018/01/01 00:00:00 through 2018/12/31 23:59:59) |
Relative Dates
Relative date filters allow you to analyse your data with rolling date values relative to the current date. These are useful for scheduled dashboard shares or alerts for example, where you may always want your team to see what has changed over the past number of days or weeks.
For all the examples below, assume today is Friday, 2018/05/18 18:30:02. Also note that in ChurnIQ, weeks start on Monday. Also remember that the data displayed will depend on the most recent data refresh, and will not include events that have happened since then.
Days
|
Example |
Description |
|
today |
the current day (2018/05/18 00:00 through 2018/05/18 23:59) |
|
2 days |
all of yesterday and today (2018/05/17 00:00 through 2018/05/18 23:59) |
|
1 day ago |
just yesterday (2018/05/17 00:00 until 2018/05/17 23:59) |
|
7 days ago for 7 days |
the last complete 7 days (2018/05/11 00:00 until 2018/05/17 23:59) |
|
today for 7 days |
the current day, starting at midnight, for 7 days into the future (2018/05/18 00:00 until 2018/05/24 23:59) |
|
last 3 days |
2 days ago through the end of the current day (2018/05/16 00:00 until 2018/05/18 23:59) |
|
7 days from now |
7 days in the future (2018/05/25 00:00 until 2018/05/25 23:59) |
Weeks
|
Example |
Description |
|
1 week |
top of the current week going forward (2018/05/14 00:00 through 2018/05/20 23:59) |
|
this week |
top of the current week going forward (2018/05/14 00:00 through 2018/05/20 23:59) |
|
before this week |
anytime until the top of this week (before 2018/05/14 00:00) |
|
after this week |
anytime after the top of this week (2018/05/14 00:00 and later) |
|
next week |
the following Monday going forward 1 week (2018/05/21 00:00 through 2018/05/27 23:59) |
|
2 weeks |
a week ago Monday going forward (2018/05/07 00:00 through 2018/05/20 23:59) |
|
last week |
synonym for “1 week ago” |
|
1 week ago |
a week ago Monday going forward 1 week (2018/05/07 00:00 through 2018/05/13 23:59) |
Months
|
Example |
Description |
|
1 month |
the current month (2018/05/01 00:00 through 2018/05/31 23:59) |
|
this month |
synonym for “0 months ago” (2018/05/01 00:00 through 2018/05/31 23:59) |
|
2 months |
the past two months (2018/04/01 00:00 through 2018/05/31 23:59) |
|
last month |
all of 2018/04 |
|
2 months ago |
all of 2018/03 |
|
before 2 months ago |
all time before 2018/03/01 |
|
next month |
all of 2018/06 |
|
2 months from now |
all of 2018/07 |
|
6 months from now for 3 months |
2018/11 through 2019/01 |
Quarters
|
Example |
Description |
|
1 quarter |
the current quarter (2018/04/01 00:00 through 2018/06/30 23:59) |
|
this quarter |
synonym for “0 quarters ago” (2018/04/01 00:00 through 2018/06/30 23:59) |
|
2 quarters |
the past two quarters (2018/01/01 00:00 through 2018/06/30 23:59) |
|
last quarter |
all of Q1 (2018/01/01 00:00 through 2018/03/31 23:59) |
|
2 quarters ago |
all of Q4 of last year (2017/010/01 00:00 through 2017/12/31 23:59) |
|
before 2 quarters ago |
all time before Q4 of last year |
|
next quarter |
all of the following quarter (2018/07/01 00:00 through 2018/09/30 23:59) |
|
2018-07-01 for 1 quarter |
all of Q3 (2018/07/01 00:00 through 2018/09/30 23:59) |
|
2018-Q4 |
all of Q4 (2018/10/01 00:00 through 2018/12/31 23:59) |
Years
|
Example |
Description |
|
1 year |
all of the current year (2018/01/01 00:00 through 2018/12/31 23:59) |
|
this year |
all of the current year (2018/01/01 00:00 through 2018/12/31 23:59) |
|
next year |
all of the following year (2019/01/01 00:00 through 2019/12/31 23:59) |
|
2 years |
the past two years (2017/01/01 00:00 through 2018/12/31 23:59) |
|
last year |
all of 2017 |
|
2 years ago |
all of 2016 |
|
before 2 years ago |
all time before 2016/01/01 |
Number Filtering
Using ChurnIQ, your number filters will support both natural language expressions (eg. 6 to 10) and relational operators (eg. >10). You can also use and/or functionality in this context.
Numerical syntax is not always intuitive, and you should refer to the following reference guide to understand the logic of different expressions.
|
Example |
Description |
|
5 |
is exactly 5 |
|
NOT 5 |
is any value but exactly 5 |
|
1, 3, 5, 7 |
is one of the values 1, 3, 5, or 7, exactly |
|
NOT 66, 99, 4 |
is not one of the values 66, 99, or 4, exactly |
|
1, NOT 2 |
is neither 1 nor 2 |
|
1, NOT 2, >100 |
is neither 1, nor 2, nor greater than 100 |
|
5, NOT 6, NOT 7 |
is 5, is not 6 or 7 |
|
5.5 to 10 >=5.5 AND <=10 |
is 5.5 or greater but also 10 or less |
|
NOT 3 to 80.44 <3 OR >80.44 |
is less than 3 or greater than 80.44 |
|
1 to >=1 |
is 1 or greater |
|
to 10 <=10 |
is 10 or less |
|
>10 AND <=20 OR 90 |
is greater than 10 and less than or equal to 20, or is 90 exactly |
|
>=50 AND <=100 OR >=500 AND <=1000 |
is between 50 and 100, inclusive, or between 500 and 1000, inclusive |
|
NULL |
has no data |
|
NOT NULL |
has some data |