With and without code.
The query() function is an extremely handy function within Google Sheets, that makes it incredibly easy to filter data within your sheet. Let's take three minutes to see how it works.
Case in Point: Pulling data from a Facebook Ads export based on multiple criteria
Let's say we have an export of data from Facebook Ads, something like the screenshot below and we want to answer the following question:
Which days did we receive more than 1,000 impressions and spent more than $10?
Sounds like a simple ask? It should be, but personally I don't think it is as simple as it sounds.
Here's the spreadsheet we will be working with:
Notice how there are multiple rows for every date. Unfortunately, the simplest way to do this is therefore a two-step process. This is how we will achieve our result:
- Step 1: Pull the above data into a new sheet that aggregates the Impressions and Amount Spent data, using the query () function
- Step 2: Run the query() function again to filter the dates according to impressions.
Step 1: Pull aggregated data into an interim sheet
We are going to add a new tab on the sheet and run this query() formula on cell A1.
The query() function works as shown in this Google article. It takes a little getting used to, but generally follows this format:
QUERY(data, query, [headers])
Once you run this function, you should get this result on your new tab.
Here, we have managed to aggregate the impressions and amount spent by each date. You can see that we now have one date per row. This is going to make our next step easier.
Step 2: Run the query() function again to only show the rows with more than 1,000 impressions
This time, we are going to run a simpler query with two criteria.
This is going to look at your new sheet from the previous step, import all the three columns but only if they match the two conditions. This should give you the following result.
You're done! See how all the impressions are greater than 1,000 and amount spent is higher than $10?
Note: This process assumes that your raw data is in the same sheet as your analysis sheet. If you want to import in the data from another spreadsheet, you will have to use a more complex mix of IMPORTRANGE and QUERY. Let's leave that for another day!
But can you do this without using code?
Sure you can! With Airboxr, you can pull this data from any sheet in a few simple steps without writing any code. Instead of explaining it to you, let me show you.
First, we add the raw data as a new source on Airboxr by simply copying and pasting the link.
Once you've added the raw file as a data source, simply click on the data source and select the columns you'd like to import into your new sheet.
Then, click Next and choose as many filters as you'd like on the next step, then—on the next screen—hit import!
And you're done! ⚡️
Easy, isn't it? If you haven't given the Airboxr query builder plugin a shot yet, use the link below to try it out.