Audit Project

audit.md

Data

Old data examples

https://docs.google.com/spreadsheets/d/1r_RiWJZp0wcbpziNyioP3JHEe3VfEuL6/edit#gid=1266710575 https://docs.google.com/spreadsheets/d/150eAXOIvYjM1FLk8hmPDZSqKWnO2OWaX/edit#gid=468795636

Convos to be audited

• Spoke message ID
• Campaign contact ID (check if inbound messages have the campaign contact ID)
• Date assigned to be audited
• Assigned auditor -- NULL or email of auditor

When an auditor logs in, they will be randomly assigned a batch of unassigned conversations from the pool. (Will figure out with ops.)

Several pieces of data will be joined in from the Redshift database (or perhaps a local Redis cache) so the auditor knows relevant information about the conversation:

• Survey questions and responses
• Opt out status
• Actual content of text messages

Admin-only pages

Summary reports

From the audit, we will generate summary reports. (To be continued... Amy is resposible for describing what she'd like.)

Email templates

Possibly, a member of the team could review the audits and send out templated email messages to texters.

(Need confirmation from team whether this is desired.)

Assignment logic

When an auditor logs in to the website, there can be a webpage where they can click a button and request a batch of conversations to audit. (Number of convos will of course be configurable.)

Batch will prefer to give as few campaigns as possible to facilitate efficiency from the auditor.

Additionally, the batch should prefer more recent conversations to less recent conversations.

If an auditor fails to audit an assigned conversation after (say) 24 hours, it will be unassigned and released back to the pool.

Finally, to keep db usage from growing indefinitely, the convo pool can be trimmed automatically every so often.

Database

Tables:

texter_audit_date

• user_id int
• date_last_audited text (ISO-8601 string YYYY-MM-DD)

audit

• campaign_contact_id int
• question int
• tag int
• optout int
• tone int
• auditor text (Or maybe join to another table)
• time int (unix time)

(question, tag, optout, and tone should be all treated as bools, though sqlite doesn't have bools. But if we end up using Redshift that of course is not relevant.)

convo

• campaign_contact_id int
• convo blob (Zipped CSV of all data needed to perform audit)

The "convo" CSV will contain:

• contactnumber
• campaign_id
• campaign
• isfromcontact
• text
• texter[cell]
• tags
• Numerous questions columns
• opt_out

auditors

Maybe there will also be a table of auditors.

We'll want something like a redis cache that pulls the survey question & response pairs for all campaigns involved in conversations

{
    campaign_id: [
        {
            'question': question,
            'responses': [response, response]
        },
        {
            'question': question,
            'responses': [response, response]
        },
    ],
    campaign_id: [
        {
            'question': question,
            'responses': [response, response]
        },
        {
            'question': question,
            'responses': [response, response]
        },
    ],
}

as well as a campaign_id associated with each conversation in the audit table.