Relationships - email and survey feedback to support mass affiliation of limited companies

[JohnamLane]

COLIN has a list of companies that have a company email on file.

This ticket is to estimate the effort needed to use scripts to achieve the following.

1. query COLIN database (CPRD) to retrieve identifier, company email and legal name for all email addresses that are linked to more than 20 companies.
2. For each email address, create and fill a Google Sheet that lists the businesses they are linked to.
3. For each Google Sheet, create/retrieve a unique survey URL.
4. Survey to have a field for them to enter their BCROS account number and checkboxes next to each company.
5. For each email address, from a 'no reply' inbox, send an email that contains a short message and a link to their unique survey.
6. After responses have been gathered, run a script that adds an affiliation between the account in question and the businesses that have checkboxes next to them.

Timing:

• dependent on our research spike into being able to affiliate ahead of LTD go-live.