In this tutorial, we’ll see how to dynamically filter a Data Studio report by the viewer’s email address using a parameter in BigQuery.
Hello, today we’re going to talk about row-level security in Data Studio. We’ll see how to filter a report based on who the logged in user is so users will only see data associated with their account. Let’s start with this table in BigQuery. It only has two columns, message and email. There’s two rows that are associated with my email address here and two rows that are associated with other email addresses, so let’s flip back over to Data Studio and I’m going to add a new data source here. We’re gonna go down to custom query and I’m gonna paste in select. We’re gonna go down to enable email parameter. Wait a second for it to get active. Where clause and we got a parameter. Click add. We are going to allow sharing and we will add that to the report. All right so you can see instead of seeing the whole table, I just see the two records that are associated with my email. so I already allowed my email address to be shared with Data Studio, but let’s see what happens when I revoke my email address. If I go back out to the main Data Studio menu I can click on the cog here and I’ll go down to revoke consent. Click revoke all and then under accountant and privacy I’ll click Save now. Let’s go back to the report and I have to refresh the page. I get a prompt here to grant consent again so this is what users will see the first time if they haven’t granted consent for Data Studio to access their email address. If I click allow, now I’ll be able to just see those rows that are associated with my email again. Normally this will just be a one-time thing. This will persist until I decide to revoke access again, and if I decide to revoke it I can always grant it again. It’ll last as long as I want it to. Thanks for watching, and if you like please subscribe for more information on Data Studio, Google Analytics and Google Tag Manager.