One of the most important tasks in data analysis is filtering and sorting data by its attached date and time information. For example, sales trends can only be analyzed if each sale has an attached date and time; else we have no way of knowing how the business fares or whether marketing campaigns work or not.
Therefore, we need to learn how to use Google’s own SQL to filter and sort its data by its attached date.
This is not a straightforward task, however, and this is partly because a lot of date and time information in several databases are combined into a single entry. Thus, when it is imported into Google Sheets, they are imported as strings instead of dates and times. (If these entries follow a predictable pattern, splitting them into two cells is enough. You can follow this tutorial to learn how to split a cell into two.)
For our example, we imported selected order data from an external database. The order creation date combines the date and time of order creation. This makes the date difficult, if not impossible to analyze. The date and time were split into two separate columns, which was converted by Google Sheets to number variables. This makes them accessible for the QUERY function
If you know how the date is formatted once it is imported to your spreadsheet, finding entries for a specific date is pretty straightforward. The code that we will use will not even require you to split the cells if the date and time are combined into a single entry. The query that you can use is as follows:
select *column_with_needed_data* where *column_containing_the_date* contains *specific_date_enclosed_in_single_quotes*
For our example, we want to look for orders created on September 24, 2020. The query will look like this:
select A, B, C where A contains '2020-09-24'
We can insert it into the query function:
=query(A1:E51,"select A, B, C where A contains '2020-09-24'")
The result would look like this:
This is where our splitting of the date and time into separate cells will enter. Without further ado, here are the query formats for the following:
Use the following query to find entries after a certain date:
select *column_with_needed_data* where *column_containing_the_date* > *specific_date_enclosed_in_single_quotes*
Example:
=query(A1:E51,"select A, B, C where A > '2020-09-24'")
Now, we'll get:
Use the following query to find entries between two dates:
select *column_with_needed_data* where *column_containing_the_date* > *starting_date_in_single_quotes* and *column_containing_the_date* < *ending_date_in_single_quotes*
By default, the starting date is included in the results but the ending date is not. To include the ending date, you can nudge the ending date by one day to get the intended ending date covered.
Example:
=query(A1:E51,"select A, B, C where A > '2020-09-24' and A < '2020-10-02'")
The equation will then show:
Advanced apps and programs that allow selecting data ranges often include a snippet where you can simply click the date range and the graphs are automatically processed to display the data in the specified range. It is possible to format the cells such that they can be converted into drop-down boxes for the date range...
... Do you want to do all these things, though?
If you don’t feel like doing these seemingly routine programming work, all just to prepare for analyzing important business data, consider trying Lido. With a few clicks on your laptop, you can now access all the relevant metrics without going through the hassle of accessing the SQL databases of your eCommerce platforms and then coding the formulas to process them. Let our platform do it all for you!
