How To Filter Alli Data Reports by Date to Power Actions - Tips & Tricks
Overview
The purpose of this document is to highlight how to properly filter an Alli Data report by date with tips & tricks to ensure you properly account for things like timezones and daylight savings.
If your Alli Data report is powered by another datasource (like a Google Sheet datasource, for example) we highly recommend you review the specific datasource’s Run Time details, which can be found here. It is important to note, for new datasources Automatic refresh times begin after Midnight on the following day.
This is important! For example: if you set up a Google Sheet Datasource to be leveraged in an action on Monday, and have an action that is running at 2 am on Tuesday, the automatic refreshes do not begin until Tuesday morning, so your data may not load before 2 am depending on the datasousource > so you may need to ensure you manually load your datasource so it contains all the data you want to action on.
Instructions
If you are creating a new action, follow the instructions listed in the Getting Started With Actions tutorial, and when you are setting your data source, select Alli Data.
Use Case:
A typical use case for utilizing an Alli Data report is when you are managing a Google Sheet that you need to filter down to only the rows you want to send on a specific date. Like in the Google Sheet below, if today was 3/13/2024, and you want the items with 3/14/2024 to run tonight at midnight CDT.
To accomplish this, you would create an Alli Datasource (we’ll name ours demo.example_sheet), and then set up a Alli Data report to filter on the date_running
column.
![image-20240314-234825.png](../../__attachments/3268837443/image-20240314-234825.png?inst-v=9d2044ed-f5d2-41ad-960f-0e53811769aa)
Below are a few ways you can filter your data to ensure you capture the right rows.
Good:
select id, status
from demo.example_sheet
where date_runing = current_date
This is good because we are ensuring we are filtering to the current date, but you should remember
current_date
uses the time zone of the computer server where your data lives, and not necessarily your timezone - meaning it could be already "tomorrow" in the server's time zone but still "today" for you or your Action schedule.
Better:
select id, status
from demo.example_sheet
WHERE date_runing = date(timezone('EST', current_date))
This is better because you are accounting for the timezone you want to filter to, in filtering to todays date in EST aka Eastern Standard Time.
However, this does not account for daylight savings and could cause issues. If your action runs at 11 PM CDT on 3/13/2024, then the current date in EST is also 11 pm on 3/13/2024, and the rows you want to run (rows with a date of 3/14) will not. (see chart below)
Time (CDT) | EST | EDT |
---|---|---|
11 PM 3/13/2024 | 11 PM 3/13/2024 | 12 AM 3/14/2024 |
Best:
select id, status
from demo.example_sheet
WHERE date = date(timezone('America/New_York', current_date))
This is best because not only are we accounting for timezones,
America/New_York
being eastern timezone, but utilizing this format instead ofEST
does account for daylight savings according to documentationTime zone name -- The full time zone name, such as America/New_York. Full time zone names can include daylight savings rules.
You can also use
Eastern Standard Time (EST):
America/New_York
(New York)Central Standard Time (CST):
America/Chicago
(Chicago)Mountain Standard Time (MST):
America/Denver
(Denver)Pacific Standard Time (PST):
America/Los_Angeles
(Los Angeles)
Put It All Together
Using the above tutorial, I know that the correct SQL to utilize is likely the below, because if I run my action at 11 pm central, no matter if it runs 11 PM CDT or 11 PM CST, that daylight savings is accounted for, and date(timezone('America/New_York', current_date))
will always pull 12 AM of the next day. In this case, when it runs at 11 PM CDT on 3/13/2024, it will pull the 2 rows with 3/14.
select id, status
from demo.example_sheet
WHERE date = date(timezone('America/New_York', current_date))
Related articles
- How To Handle Common Types of Actions Issues
- How To Filter Alli Data Reports by Date to Power Actions - Tips & Tricks
- How To Run an Action One-Off with Manual File
- How To Connect your Google Sheet to Actions
- How To Set Up a Manual Upload Action
- How To Test Your Action
- How To Schedule Your Action to Run
- How To Disable or Remove an Action
- How To QA Your Action Runs
- How To View Error Messages on an Action That Ran
- How Manage Partner Platform Authentication in Actions
- How-To Add Locations to Campaigns in Google Ads
- How-to Pause or Activate Meta (Facebook) Ads
- How To Add Negative Mobile Placements in Google Ads
- How To Add Negative Keywords to Google Ads