Reference Cell Data for a filter

536 views
Skip to first unread message

Zach Moss

unread,
Jan 25, 2016, 4:29:25 PM1/25/16
to Supermetrics
Hi,

Is it possible to reference a cell data to be used as a filter.

For Example I track all of my email revenue for each campaign by line, I only want to show the email revenue for that exact email based on the source UTM tag of the email which I have in a column. Right now, I copy the query and manually enter the UTM Source, but I would like to be able to just say source=Sheet1!$O$29 for example and then each line after would be O30, O31, etc..,

Sorry for not being clear, happy to elaborate. 

Zach

Mikael Thuneberg

unread,
Jan 26, 2016, 2:29:04 AM1/26/16
to automate...@googlegroups.com
Yes, you can set any parameter dynamically from a cell value. Open the SupermetricsQueries sheet (Add-ons: Supermetrics: Manage queries), locate the column marked "Filters", and add a formula that constructs the filter based on the value in your cell.

--
You received this message because you are subscribed to the Google Groups "Supermetrics" group.
To unsubscribe from this group and stop receiving emails from it, send an email to automateanalyt...@googlegroups.com.
Visit this group at https://groups.google.com/group/automateanalytics.
To view this discussion on the web visit https://groups.google.com/d/msgid/automateanalytics/23c9956e-6e59-4409-8c53-0035d52fa06d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Becky Avilla

unread,
Apr 6, 2016, 1:11:03 PM4/6/16
to Supermetrics
Hi there, I too am trying to reference a cell for a filter. Can I get an example of how to do this?  Thank you!

Zach Moss

unread,
Apr 12, 2016, 4:29:46 PM4/12/16
to Supermetrics
I tried to do it like this, but it didnt work :-/ [{"field":"Source","operator":"=@","value":"='Sheet1'!N93","combineToPrev":";"}]

Mikael Thuneberg

unread,
Apr 13, 2016, 7:01:12 AM4/13/16
to automate...@googlegroups.com
Try this:

="source=@"&Sheet1!N93

To view this discussion on the web visit https://groups.google.com/d/msgid/automateanalytics/ac3d0239-2723-4931-944a-ff84986dfd23%40googlegroups.com.

Zach Moss

unread,
Apr 18, 2016, 3:27:23 PM4/18/16
to Supermetrics
Tried that, no luck. Here is the error I get:

Last status: Error: 400 invalidParameter Invalid value 'ga:"operator":"=@",ga:"value":"source=@"&Sheet1!N93"' for filters parameter.

Any idea?

Mikael Thuneberg

unread,
Apr 18, 2016, 3:30:15 PM4/18/16
to automate...@googlegroups.com
You're inserting it wrong somehow, use the example you posted
To view this discussion on the web visit https://groups.google.com/d/msgid/automateanalytics/9b87c26e-8326-4829-90ec-b42129d13adc%40googlegroups.com.

Zach Moss

unread,
Apr 18, 2016, 4:59:43 PM4/18/16
to Supermetrics
Which example?
To unsubscribe from this group and stop receiving emails from it, send an email to automateanalytics+unsubscribe@googlegroups.com.

Mikael Thuneberg

unread,
Apr 19, 2016, 1:31:02 AM4/19/16
to automate...@googlegroups.com
Sorry, I wasn't clear enough. Run the query without any filter, then locate it in the SupermetricsQueries sheet, and add this to the filters column:

="source=@"&Sheet1!N93

Then refresh the query.

--
You received this message because you are subscribed to the Google Groups "Supermetrics" group.
To unsubscribe from this group and stop receiving emails from it, send an email to automateanalyt...@googlegroups.com.
Visit this group at https://groups.google.com/group/automateanalytics.
To view this discussion on the web visit https://groups.google.com/d/msgid/automateanalytics/83e5860a-8f49-4551-b569-55314624d473%40googlegroups.com.

Zach Moss

unread,
Apr 19, 2016, 9:25:54 AM4/19/16
to Supermetrics
That did the trick! Thanks!!!
To unsubscribe from this group and stop receiving emails from it, send an email to automateanalytics+unsubscribe@googlegroups.com.

Chrilles

unread,
Apr 19, 2016, 9:33:39 AM4/19/16
to Supermetrics
Thank a lot. This is very helpful.

Can this method be used if I want to apply multiple filters in the same query? 

If no. What is the method to use (if there is one), if I want to use multiple filters?

/C
To unsubscribe from this group and stop receiving emails from it, send an email to automateanalytics+unsubscribe@googlegroups.com.

Zach Moss

unread,
Apr 19, 2016, 9:38:30 AM4/19/16
to Supermetrics
The only other query I have to figure out is how I can do the same thing when there is also an additional filter for product name. Currently I have this in the filter field: [{"field":"Source","operator":"=@","value":"CJ_Fibers_l_e3","combineToPrev":";"},{"field":"ProductName","operator":"=@","value":"kit","combineToPrev":";"}] 

Where CJ_Fibers_1_e3 is at N95

Thanks in advance,
Zach

Mikael Thuneberg

unread,
Apr 19, 2016, 9:39:24 AM4/19/16
to automate...@googlegroups.com
Yes, it can. Follow the syntax at https://developers.google.com/analytics/devguides/reporting/core/v3/reference#filters. For example, two filters combined with AND:

="source=@"&Sheet1!N93&";medium=@"&Sheet1!N94

/C

--
You received this message because you are subscribed to the Google Groups "Supermetrics" group.
To unsubscribe from this group and stop receiving emails from it, send an email to automateanalyt...@googlegroups.com.
Visit this group at https://groups.google.com/group/automateanalytics.

--
You received this message because you are subscribed to the Google Groups "Supermetrics" group.
To unsubscribe from this group and stop receiving emails from it, send an email to automateanalyt...@googlegroups.com.
Visit this group at https://groups.google.com/group/automateanalytics.
To view this discussion on the web visit https://groups.google.com/d/msgid/automateanalytics/3bb98a9d-efdc-4c6f-a256-3bed83cca8be%40googlegroups.com.

Zach Moss

unread,
Apr 19, 2016, 9:54:39 AM4/19/16
to Supermetrics
Perfect thanks!
/C
To unsubscribe from this group and stop receiving emails from it, send an email to automateanalytics+unsubscribe@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Supermetrics" group.
To unsubscribe from this group and stop receiving emails from it, send an email to automateanalyt...@googlegroups.com.
Visit this group at https://groups.google.com/group/automateanalytics.
Message has been deleted

jo...@bluewheelmedia.com

unread,
Apr 25, 2016, 10:00:24 AM4/25/16
to Supermetrics


Can someone tell me what I' am doing wrong here?

I' am trying to run a report on a cell that is a channel group of email, and a campaign that equals a dynamic cell

[{"field":"channelGroup","operator":"==","value":"Email","combineToPrev":";"},{"field":"Campaign","operator":"=@","value":"&Data!A97","combineToPrev":";"}]

My cell location ends up in my filter field, but I know that is incorrect.

Supermetrics Support

unread,
Apr 28, 2016, 8:35:38 AM4/28/16
to Supermetrics

You can now easily use cell references in filters. Just type "=" and the cell address to the filter value field in the add-on sidebar. Example below. 



Joel Samonie

unread,
Apr 28, 2016, 8:36:10 AM4/28/16
to automate...@googlegroups.com
You are the best!!



-- 
You received this message because you are subscribed to a topic in the Google Groups "Supermetrics" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/automateanalytics/kAu6iwmpJGI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to automateanalyt...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

Joel Samonie | Blue Wheel Media

1950 Stephenson Highway Troy, MI 48083 

Chrilles Wybrandt

unread,
Apr 28, 2016, 8:46:10 AM4/28/16
to automate...@googlegroups.com
Fantastic!

Mvh. 

Chrilles 


Zach Moss

unread,
May 11, 2016, 9:53:53 PM5/11/16
to Supermetrics
Hi,

I have been noticing that when using this method to filter, when my daily refreshes run it either ignores the filter or the query fails and it says no data found. When I open the sidebar and refresh an individual cell manually it returns the correct amount. I'd also note that refreshing seems a bit longer when defining a filter using the =Cell method.

Anyone else experiencing this?
Zach

Mikael Thuneberg

unread,
May 12, 2016, 5:13:48 AM5/12/16
to automate...@googlegroups.com
We found out the reason for this, our code didn't always get the right cell when it ran from a trigger. It's now been fixed.

--
You received this message because you are subscribed to the Google Groups "Supermetrics" group.
To unsubscribe from this group and stop receiving emails from it, send an email to automateanalyt...@googlegroups.com.

Visit this group at https://groups.google.com/group/automateanalytics.
To view this discussion on the web visit https://groups.google.com/d/msgid/automateanalytics/9f656a5a-3d2c-4b42-bc9f-fd7e2a77f2c4%40googlegroups.com.

Zach Moss

unread,
May 12, 2016, 7:24:33 AM5/12/16
to automate...@googlegroups.com
Thanks for the quick fix!!

Best,
Zach

Sent from my iPhone
You received this message because you are subscribed to a topic in the Google Groups "Supermetrics" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/automateanalytics/kAu6iwmpJGI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to automateanalyt...@googlegroups.com.

emi...@rascalsdepot.com

unread,
Jun 6, 2018, 11:04:13 PM6/6/18
to Supermetrics
Great stuff SuperMetrics team! Helpful and straight forward answer!

Further scenarios for anyone looking for it > in the case where 1st condition is static value (ex- autopilot) and 2nd one is referencing a cell (ex- Sheet1!C7), query looks like this:

="campaign_group_name=@autopilot;campaign_name=@"&Sheet1!C7

=@ stands for contains

Cheers!

Email Support

unread,
Jun 6, 2018, 11:04:29 PM6/6/18
to automate...@googlegroups.com

Hi,


Thanks for contacting us. We're looking into your message and will assign the best person to handle your ticket. We'll get back to you as soon as possible (our usual response time is between 1-2 working days). You may view and respond to the ticket here https://support.supermetrics.com/helpdesk/tickets/20925 


While awaiting the answer, you can try to search for the solution from our solution database and support forum.




Best regards,

Supermetrics Team

supermetrics.com


Twitter: @Supermetrics

Support forum: support.supermetrics.com

image

Supermetrics Customer Support is open from Monday to Friday. Supermetrics related critical issues have support 7 days a week.



20925:463602
Reply all
Reply to author
Forward
0 new messages