Filter by multiple email addresses per row

Use data blending to create many-to-many email filters.

Prerequisites

 

This solution relies on the following concepts / tasks in Data Studio:

Filtering by email address works by comparing the address of the logged in viewer to a field in your data source that contains valid email addresses. For each row in your data, the filter checks to see if the viewer's email matches the address in that row.

Consider the following data:

Email Data
alan@example.com abc
mary@example.com cde
alan@example.com efg
mary@example.com ghi

 

If this data was filtered by email address, and alan@example.com viewed the filtered report, he'd only see the data
"abc" and "efg." If mary@example.com viewed the same report, she'd see the data "efg" and "ghi."

This is fine if you have a 1:1 relationship between viewers and the data. But what if you wanted also wanted manager@example.com to see the data? That is, you want many people to see the same rows of data. (This is known as a many:many relationship.)

Filter by email only works on one address per row, so you can't simply put a list of addresses in your email field. For example, this won't work:

Email Data
alan@example.com, manager@example.com, vp@example.com, bigwig@customer.com abc

Solution: Use data blending

You can create a many:many relationship between email addresses and your data by blending a table of addresses with your data, using any common field as a join key.

Fruit stand example

You manage a produce company, and want show your sales representatives how they are performing at the various fruit stands they service. Multiple sales people can service multiple stands. To filter the data so your sales people only see their data, you could do the following:

Step 1: Create an access control list table

In this step, you'll create an access control list (ACL) table that contains the email addresses of the authorized sales people, paired with a data field (a fruit stand name) that can be used as a join key in the blend.

Sales Rep Email Join key
salesrep1@example.com Fruit Stand A
salesrep2@example.com Fruit Stand A
salesrep1@example.com Fruit Stand B

salesrep2@example.com

Fruit Stand C

Access control list (ACL) table

Note that salesrep1 can see data for Fruit Stand A and Fruit Stand B, while salesrep2 can see data for Fruit Stand A and Fruit Stand C.

Step 2: Create the data table

The data table tracks the sales each representative made to each fruit stand.

Note that this table doesn't need to have the sales rep's email addresses, only the same values as in the join key (the fruit stand name). Also note the name of the join key field is not relevant: blending joins based on the data, not the field name.
Fruit Stand Fruit Sales
Fruit Stand A Apple 50
Fruit Stand A Banana 26
Fruit Stand A Orange 20
Fruit Stand A Pear 93
Fruit Stand B Apple 98
Fruit Stand B Banana 86
Fruit Stand B Orange 7
Fruit Stand B Pear 85
Fruit Stand C Apple 21
Fruit Stand C Banana 61
Fruit Stand C Orange 3
Fruit Stand C Pear 78

Data table

Step 3: Apply the email filter to the ACL table

Edit the data source for the ACL table and choose the Sales Rep Email field as the filter.

Filter by email.

Step 4: Blend the ACL table with the data table

If you blend the ACL table with the data table with no email filter applied, you'll see all the records for both sales representatives. But with the email filter applied, blending the ACL table with the sales data table filters that data according to which sales person is viewing the report. Here's what each representative would see when they view the report:

Sales Rep 1 sees this:

Data Fruit Sales
Fruit Stand A Apple

50

Fruit Stand A Banana

26

Fruit Stand A Orange

20

Fruit Stand A Pear

93

Fruit Stand B Apple

98

Fruit Stand B Banana

86

Fruit Stand B Orange

7

Fruit Stand B Pear

85

Sales Rep 2 sees this:

Data Fruit Sales
Fruit Stand A Banana

26

Fruit Stand A Orange

20

Fruit Stand A Apple

50

Fruit Stand A Pear

93

Fruit Stand C Banana

61

Fruit Stand C Orange

3

Fruit Stand C Apple

21

Fruit Stand C Pear

78

 

Was this helpful?
How can we improve it?

Need more help?

Sign in for additional support options to quickly solve your issue