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
);