Spoke Rewired Data Dictionary
The following table is designed to help you better understand the data from your texting campaigns, allowing you to analyze campaign performance. Sample scripts for potential analyses can be found below the table. Depending on the platform you use, you may need to adjust schema prefixes (e.g. "message" may need to become "spoke.message").
title | description | key columns |
---|---|---|
campaign | Contains information about each campaign - e.g. when it was created or whether it was archived. Primary key is campaign.id. | |
campaign_contact | Contains information about a contact for each campaign. Joins to campaign on campaign_contact.campaign_id = campaign.id. Primary key is campaign_contact.id. | |
message | Contains each message, inbound and outbound. Joins to campaign and campaign_contact on message.campaign_contact_id = campaign_contact.id. | is_from_contact = true means message is inbound, = false means outbound message |
question_response | Contains "question responses," or Spoke question responses. Joins to campaign and campaign_contact on question_response.campaign_contact_id = campaign_contact.id. Only includes contacts who have a question_response populated. | 'value' contains question response values like support score, activist codes, Wrong Number, etc. |
all_question_response | Contains "question responses", or Spoke question responses. Joins to campaign and campaign_contact on question_response.campaign_contact_id = campaign_contact.id. Includes all contacts, including those who did not respond, where the "value" is null. | |
canned_response | Contains all Spoke canned responses. | |
interaction_step | Contains all of the scripts at each 'interaction step' in the texting script, as well as the hierarchy for how all of the 'interaction steps' in the texting script tree are related to one another. | |
opt_out | Contains all opted out contacts, as well as the campaigns on which they were opted out. | |
campaign_contact_tag | Campaign contacts and associated tags. Must be joined back to all_tag or tag on campaign_contact_tag.tag_id = all_tag.id to get tag titles. | |
all_tag | Contains all tags, including those which have been deleted. | |
tag | A view of all_tag for tags that have not been deleted. | |
user | Contains information about users (texters, supervolunteers, admins, owners). | |
team | Contains team ID and team title for texting teams you may have within your organization. | |
user_team | Crosswalk between team and user. | joins to team on team.id = user_team.team_id |
user_organization | Crosswalk between user and organization. | |
organization | Lists all organizations within your instance of Spoke. For example, you might have different organizations if there are multiple sub-groups / affiliates of a larger organization using your Spoke. | joins to campaign on organization.id = campaign.organization_id |
Sample Scripts
How to get counts of contacts with certain tags within a certain interval:
select at.title, count(distinct cc.id) from campaign_contact cc left join campaign_contact_tag cct on cc.id = cct.campaign_contact_id left join all_tag at on cct.tag_id = at.id where cct.updated_at between now() - interval '12 hours' and now() group by at.title
How to get an export ready for VAN bulk upload, marking null values as 'canvassed, no response':
select distinct cc.external_id, case when aqr.value is null then 'canvassed, no response' else aqr.value end as value, to_char(m.sent_at,'MM-DD-YYYY') as date from campaign c left join campaign_contact cc on c.id = cc.campaign_id left join message m on cc.id = m.campaign_contact_id left join all_question_response aqr on cc.id = aqr.campaign_contact_id
How to find the number of texts sent inbound and outbound, grouped by day:
edit timezone to reflect your timezone
select date(sent_at::timestamptz at time zone 'EDT') as date, case when is_from_contact = true then 'inbound' when is_from_contact = false then 'outbound' end as direction, Count(*) as messages from message group by date(sent_at::timestamptz at time zone 'EDT'), is_from_contact order by date(sent_at::timestamptz at time zone 'EDT') desc;
How to find number of texts sent by a member of a certain team (i.e. "Spanish texting team")
select Count(*) from message where user_id in ( select user_id from user_team join team on team.id = user_team.team_id where team.title ilike '%spanish%' );
How to find number of unique texters in your organization:
select count(*) from user;
How to calculate response rates by script option for Campaign ID 75, for initial text only:
-- Your analysis on messages, grouped by the script_option_hash -- replace campaign_id with actual campaign ID with hash_counts as ( select campaign_id, script_version_hash, count(*) as hash_count from message join campaign_contact on campaign_contact.id = message.campaign_contact_id where campaign_id = 75 group by 1, 2 ), -- Fetch script options as reference scripts as ( select interaction_step.id as istep_id, interaction_step.campaign_id, unnest(interaction_step.script_options) as script_option from interaction_step where campaign_id = 2 -- EDITING HERE WILL CHANGE the 'interaction_step' parent, and for initial texts, the -- interaction step parent will be null and parent_interaction_id is null ), -- Resolve analysis script_option hashs to script_option text script_options as (select scripts.istep_id, scripts.campaign_id, hash_counts.hash_count, scripts.script_option, hash_counts.script_version_hash from scripts join hash_counts on hash_counts.script_version_hash = md5(scripts.script_option) order by 1, 3 desc), -- Response rate = -- of everyone who got a certain initial, how many of them had ANY response script_total_contacts as ( select m.script_version_hash, cc.id as campaign_contact_id, script_option from message m inner join campaign_contact cc on m.campaign_contact_id = cc.id inner join script_options sc on sc.script_version_hash = m.script_version_hash), script_response_contacts as ( select distinct campaign_contact_id from message where is_from_contact = true ), responded_bool as ( select stc.campaign_contact_id, stc.script_option, case when src.campaign_contact_id is null then false else true end as responded from script_total_contacts stc left join script_response_contacts src on src.campaign_contact_id = stc.campaign_contact_id), subtotals as ( select script_option,responded, count(distinct campaign_contact_id) as subtotal_count from responded_bool group by script_option,responded), totals as ( select script_option,count(distinct campaign_contact_id) as total_count from responded_bool group by script_option) select totals.script_option, responded, subtotal_count, total_count, subtotal_count::numeric*100 / total_count::numeric from subtotals left join totals on subtotals.script_option = totals.script_option
How to calculate ID rates by script option for Campaign ID 75, for initial text only:
-- Your analysis on messages, grouped by the script_option_hash with subtotal as (select value, m.script_version_hash, x.script_option, count(distinct qr.campaign_contact_id) as subtotal from campaign c left join campaign_contact cc on c.id = cc.campaign_id left join question_response qr on cc.id = qr.campaign_contact_id left join message m on cc.id = m.campaign_contact_id left join (with hash_counts as ( select campaign_id, script_version_hash, count(*) as hash_count from message join campaign_contact on campaign_contact.id = message.campaign_contact_id where campaign_id in (75) group by 1, 2 ), -- Fetch script options as reference scripts as ( select interaction_step.id as istep_id, interaction_step.campaign_id, unnest(interaction_step.script_options) as script_option from interaction_step where campaign_id in (75) -- EDITING HERE WILL CHANGE the 'interaction_step' parent, and for initial texts, the -- interaction step parent will be null and parent_interaction_id is null ) -- Resolve analysis script_option hashs to script_option text select scripts.istep_id, scripts.campaign_id, hash_counts.hash_count, scripts.script_option, hash_counts.script_version_hash from scripts join hash_counts on hash_counts.script_version_hash = md5(scripts.script_option) order by 1, 3 desc) x on x.script_version_hash = m.script_version_hash where c.id = 75 -- This line limits the question_value responses to those that include ---- numbers only - useful if you are calculating support % and value ~ '[0-9]' group by value, m.script_version_hash,x.script_option), -- Calculate total sent of each script version totals as ( select subtotal.script_version_hash, subtotal.script_option, sum(subtotal.subtotal) as total from subtotal group by script_version_hash, subtotal.script_option) -- Final analysis select st.*, t.total, case when t.total = 0 then t.total else st.subtotal::numeric*100/t.total end as percent from subtotal st left join totals t on t.script_version_hash = st.script_version_hash
How to analyze "A/B" test results using script versions for Campaign ID 75:
-- Your analysis on messages, grouped by the script_option_hash with hash_counts as ( select campaign_id, script_version_hash, count(*) as hash_count from message join campaign_contact on campaign_contact.id = message.campaign_contact_id where campaign_id = 75 group by 1, 2 ), -- Fetch script options as reference scripts as ( select interaction_step.id as istep_id, interaction_step.campaign_id, unnest(interaction_step.script_options) as script_option from interaction_step where campaign_id = 75 ) -- Resolve analysis script_option hashes to script_option text select scripts.istep_id, scripts.campaign_id, hash_counts.hash_count, scripts.script_option, hash_counts.script_version_hash from scripts join hash_counts on hash_counts.script_version_hash = md5(scripts.script_option) order by 1, 3 desc ;
How to analyze reply rates of "A/B" test results for Campaign ID 75:
select ab_test.script_version_hash, responded, total, reply_rate, sample.text as sample_message from ( select script_version_hash, count(*) filter (where message_status <> 'messaged' and message_status <> 'needsMessage') as responded, count(*) filter (where message_status <> 'needsMessage') as total, count(*) filter (where message_status <> 'messaged' and message_status <> 'needsMessage') / (count(*) filter (where message_status <> 'needsMessage'))::float * 100 as reply_rate from message join campaign_contact on campaign_contact.id = message.campaign_contact_id where campaign_id = 75 and not exists ( select 1 from message earlier_message where earlier_message.campaign_contact_id = campaign_contact.id and earlier_message.created_at < message.created_at ) and send_status = 'DELIVERED' group by 1 ) ab_test join message sample on sample.id = ( select id from message where message.script_version_hash = ab_test.script_version_hash limit 1 );
How to analyze opt out rates of "A/B" test results for Campaign ID 75:
select ab_test.script_version_hash, opted_out, total, opt_out_rate, sample.text as sample_message from ( select script_version_hash, count(*) filter (where is_opted_out = true and message_status <> 'needsMessage') as opted_out, count(*) filter (where message_status <> 'needsMessage') as total, count(*) filter (where is_opted_out = true and message_status <> 'needsMessage') / (count(*) filter (where message_status <> 'needsMessage'))::float * 100 as opt_out_rate from message join campaign_contact on campaign_contact.id = message.campaign_contact_id where campaign_id = 75 and not exists ( select 1 from message earlier_message where earlier_message.campaign_contact_id = campaign_contact.id and earlier_message.created_at < message.created_at ) and send_status = 'DELIVERED' group by 1 ) ab_test join message sample on sample.id = ( select id from message where message.script_version_hash = ab_test.script_version_hash limit 1 );