Fetch the phone or email contact associated with a Count On Me form response

Last updated: October 14, 2026

Using the SQL Mirror, you can match a Count On Me form response to the phone or email contact your message was sent to ("original recipient"). The /v1/form_responses API does not return this today.

What you get

The original recipient is the contact you messaged, which can differ from what the supporter types into the form. For example, if you text 555-0100 and the supporter enters 555-0199 on the form, the response's data holds 555-0199. This method returns 555-0100, through a join.

  • Texts: the broadcast ID and the to_number the text was sent to.

  • Emails: the email blast ID and the to_email the email was sent to.

You'll need

  • SQL Mirror access to your organization's dataset in BigQuery (switchboard-mirror.<your_dataset>).

  • The form ID (starts with wf_) if you want results for one form only.

Good to know: Attribution works only when the supporter reached the form through a tracked link in a text or email your organization sent from Switchboard.

Run the query

  1. Paste the query below and replace <your_dataset> (three places) with your dataset name.

  2. Set the sent_at date to the earliest send you care about. This narrows the results, but BigQuery still reads the HTML of every email in your mirror, so for large email programs each run can be computationally expensive. If you only need texts, use the text-only version below.

  3. Optional: replace wf_... with your form ID, or delete that line to include every form.

  4. Run it. Each row is one form response, with the text recipient (to_number) or email recipient (to_email) filled in when one was found.

with email_tokens as (
  select
    tok as token,
    any_value(email_blast_id) as email_blast_id,
    any_value(to_email) as to_email
  from `switchboard-mirror.<your_dataset>.email_messages`,
    unnest(regexp_extract_all(html_content, r'(?:/l/|[?&](?:amp;)?t=)([0-9A-Za-z]{4,12})')) as tok
  where sent_at >= timestamp '2026-09-01'  -- earliest send you care about
  group by 1
)
select
  r.id as form_response_id,
  r.form_id,
  r.submitted_at,
  t.broadcast_id,
  t.to_number,
  e.email_blast_id,
  e.to_email
from `switchboard-mirror.<your_dataset>.count_on_me_form_responses` r
left join `switchboard-mirror.<your_dataset>.token_data` t
  on t.token = regexp_extract(json_value(r.data.refcodeSB), r'^b-\d+-(.+)$')
left join email_tokens e
  on e.token = regexp_extract(json_value(r.data.refcodeSB), r'^eb-\d+-(.+)$')
where r.form_id = 'wf_...'  -- optional: limit to one form

Only need texts? Delete the with email_tokens as (...) block, the left join email_tokens e ... lines, and the e.email_blast_id and e.to_email columns.

Limitations and FAQ

Why are some rows blank? to_number and to_email stay empty when the response did not come from one of your tracked texts or emails. Common causes: a direct visit to the form, a link shared without tracking.

What about form links a partner organization sent? If a partner texts or emails a link to a form owned by you, those come back blank. When another organization links to your form, the recipients belong to the sending organization, so they aren't in your data.

Is the recipient the person who submitted? Not always. The query shows who your message was sent to. If that person forwarded the link, someone else may have filled out the form.

Can I get this from the API? Not today. /v1/form_responses does not include the original recipient, so use SQL Mirror for now.