Search code examples
filtergoogle-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-language

Query conditional unique cases for Google Sheet Mailing List

For maintaining a research project I'm setting up a Google Sheet to create mailing lists using logical operators.

The basic structure is a "contacts" sheet with an ever-growing list of email addresses, names, etc. I then add a new sheet for each new email-campaign (i.e., a mass email to be sent out). There is also an "unsubscribe" list to suppress email address inclusion in any future campaigns.

I struggle to come up with a solution that allows me to identify email addresses that have been e.g.,

  • (a) never contacted as yet
  • (b) twice contacted already
  • (c) not contacted through "campaign1"
  • AND are not part of "unsubscribe"

My logical approach has been to establish a "history" sheet that lists all email addresses of any campaigns so far. I'm challenged to find a solution that compares "contacts" and "history" and correctly returns the rows that align with my search parameters, yet returns a given email address no more than once (so as to not email people multiple times per campaign).

I've tried to various configurations and nesting of QUERY, FILTER, SORTN including UNIQUE to establish a universal approach that uses "contacts" as a central source from which to logically extract rows as illustrated above.

This sheet reflects the above (no functions) and invites for suggestions: https://docs.google.com/spreadsheets/d/1NRY-udwepbFahVryX7FF6WIQ0vtFJkjuuhRWFJVAWnI

Solution

  • 1. never contacted as yet

    =FILTER(contacts!A2:D1000, NOT(COUNTIF(contacts!B2:B1000, history!B2:B1000)))

    0

    2. twice contacted already

    =UNIQUE(FILTER(history!A2:D1000, COUNTIF(history!B2:B1000, history!B2:B1000)>1))

    3. not contacted through "campaign1"

    =FILTER(campaign2!A2:D1000, NOT(COUNTIF(campaign1!B2:B1000, campaign2!B2:B1000)))

    0

    4. are not part of "unsubscribe"

    =FILTER(contacts!A2:D1000, NOT(COUNTIF(unsubscribe!A1:A999, contacts!B2:B1000)))

    0

    5. twice contacted already AND are not part of "unsubscribe"

    =UNIQUE(FILTER(history!A2:D1000, COUNTIF(history!B2:B1000, history!B2:B1000)>1, 
 NOT(COUNTIF(unsubscribe!A1:A999, history!B2:B1000))))

    2

    6. define the formulas using named ranges.

    =UNIQUE(FILTER(History, COUNTIF(historyB, historyB)>1, 
 NOT(COUNTIF(unsubscribe, historyB))))

    0