SQL Mirror Data Docs

Last updated: September 18, 2026

Available Tables and Fields

`actblue_donations`

entity_id - ActBlue Entity ID for the donation.
order_number - ActBlue order number for the donation, consistent across recurring donations in the same sequence of donations.
lineitem_id - ActBlue Line Item ID, unique to this particular donation
paid_at - ISO 8601 timestamp recorded by ActBlue for when the donation occurred.
recurring_period - Frequency of recurrence for recurring ActBlue donations. One of once, weekly, or monthly.
amount - Dollar amount of the donation.
donor_firstname - Donor first name
donor_lastname - Donor last name
donor_addr1 - Donor street address
donor_city - Donor city
donor_state - Donor state
donor_zip - Donor postal code
donor_phone - Donor phone number
donor_email - Donor email
donor_employer - Donor employer name
donor_occupation - Donor occupation
donor_employer_addr1 - Donor employer street address
donor_employer_city - Donor employer city
donor_employer_state - Donor employer state
donor_employer_country - Donor employer country
refcode_values - Array with any refcodes recorded for the donation
refcode - Value for the refcode parameter for this donation. Also available in above array, but pulled out for convenience.
token - Switchboard tracking token for this donation. Can be used to link donations back to Switchboard Broadcasts.
committee_name - ActBlue committee name.
contribution_form_name - Source ActBlue contribution form for the donation.
form_managing_entity_name - Name of the ActBlue entity that created and manages the source form for this donation.
form_managing_entity_committee_name - Name of the ActBlue committee that created and manages the source form for this donation.
ab_test_name - AB test name, if any.
ab_test_variation - AB test variant identifier.
is_paypal - Whether this was a PayPal donation.
is_mobile - Whether this was a mobile donation.
is_express - Whether the donor is an ActBlue Express user.
donor_is_eligible_for_express_lane - Whether the donor is eligible to donate through Express Lane.
is_with_express_lane - Whether the contribution was made through ActblueExpress Lane.
is_express_signup - Whether the donor chose to sign up for ActBlue express after making the contribution.

`broadcasts`

id - Unique identifier for the broadcast.
created_at - ISO 8601 timestamp for when the broadcast was created.
modified_at - ISO 8601 timestamp for when the broadcast was last modified (such as a status update).
start_time - ISO 8601 timestamp for when the broadcast started.
scheduled_time - ISO 8601 timestamp for the broadcast’s scheduled start time, if any.
phone_list_id - The unique identifier of the phone list used in this broadcast, or null if no phone list associated.
title - Name of the broadcast. This is shown in the Switchboard app to help identify broadcasts similar to a file name.
description - Description of the broadcast. This is shown in the Switchboard app when you open a broadcast as a place to store notes and additional details that the title does not cover.
creator - Identifier for the user who created the phone list. Generally an email address.

status - The current state of the broadcast.
- draft: The broadcast has been created but not scheduled or sent.
- error: The broadcast failed to send.
- scheduled: The broadcast has been scheduled for sending at a later date and/or time.
- sending: The broadcast is actively sending.
- paused: The broadcast was paused during sending.
- sent: The broadcast has completed sending its messages.
- stopped: The broadcast has been paused permanently.
clicks - The number of times someone has clicked on a Switchboard-tracked link originating from this broadcast.
donations - The number of times someone has donated to the campaign using a Switchboard-tracked donation link originating from this broadcast. You must have an integration with a donation provider configured in Switchboard for this to work.
amount_raised - The amount of money donated to the campaign that Switchboard was able to tie to this broadcast in US dollars. You must have an integration with a donation provider configured in Switchboard for this to work.
cost_estimate - The amount Switchboard estimates this broadcast to cost the organization if sent in US dollars.
total_messages - The number of messages sending or sent for this broadcast.
previously_opted_out - The number of phones who opted out before the broadcast and whom Switchboard will not attempt to send messages to.
skipped - The number of phones who Switchboard will skip for reasons other than opt-out (e.g. being a landline).
failed_to_deliver - The number of messages that could not be delivered.
delivered - The number of messages that were successfully delivered.
opt_outs - The number of phones who have opted out after receiving this broadcast but before receiving another.
replies - The number of phones who have replied since this broadcast was sent.
message_text - The text template that is being sent for this broadcast.
media_urls - Public urls that were sent in this broadcast.

`charges_daily`

date_eastern - The date of the charges in Eastern timezone (YYYY-MM-DD format).
- NOTE: This is currently specifically in "America/New_York" time to match the way that charges display in our Organization Settings -> Billing page, it is not yet set per account timezone.
modified_at - ISO 8601 timestamp for when the record was last modified
message_type - The type of message charged
- sms: SMS message
- mms: MMS message
broadcast_id - The public ID of the broadcast associated with the charges (null for non-broadcast messages)
description - Description of the charge (only populated when not associated with a broadcast, e.g., inbox or test messages)
count_messages_charged - The number of messages charged for this day/type/broadcast combination
count_segments_charged - The number of message segments charged. If message_type is mms, will match count_messages_charged
count_messages_refunded - The number of messages refunded for this day/type/broadcast combination
count_segments_refunded - The number of message segments refunded. If message_type is mms, will match count_messages_refunded

amount_charges_gross - The gross amount charged (before refunds)
amount_charges_refunded - The amount that has been refunded
amount_charges_net - The net amount charged (gross minus refunded)

`count_on_me_accounts`

id - Unique identifier for the contributions account (committee).
created_at - ISO 8601 timestamp for when the account was created.
modified_at - ISO 8601 timestamp for when the account was last modified.
committee_name - The name of the committee.
candidate_full_name - The full name of the candidate associated with this account, if applicable.
is_active - Whether the account is currently active and able to accept contributions.
political_party_affiliation - The political party affiliation associated with this account, if applicable.
office_sought - The office sought by the candidate associated with this account, if applicable.
statement_descriptor - The statement descriptor shown on a donor's card or bank statement for contributions to this account.
fec_id - The FEC ID associated with this account, if applicable.
ein - The EIN associated with this account, if applicable.

`count_on_me_contributions`

All donors who provide a non-US address are required to provide a passport number. These are not exposed in the table. They are exposed in the Compliance Center.

id - Unique identifier for the contribution.
contributions_account_id - Unique identifier for the contributions account (committee) that received this contribution. See contributions_accounts.
payout_id - Unique identifier for the payout this contribution was included in, or null if it has not yet been paid out. See contributions_payouts.
created_at - ISO 8601 timestamp for when the contribution was created.
modified_at - ISO 8601 timestamp for when the contribution was last modified (such as a status update).
contribution_at - ISO 8601 timestamp for when the contribution occurred.
scheduled_charge_at - ISO 8601 timestamp for when a scheduled contribution is set to be automatically charged, if applicable.
status - The current state of the contribution.
- succeeded: The contribution payment has been successfully processed.

NOTE: This table only includes successful contributions today, so status will always be succeeded. The field is included in case that scope changes in the future.

amount_in_cents - The total contribution amount, in cents.
processing_fee_in_cents - The payment processing fee charged on this contribution, in cents.
pending_recurring_contribution_frequency - The pending recurring frequency for this contribution, to be used once the contribution completes.
- once: A one-time, non-recurring contribution.
- weekly: A contribution that recurs weekly.
- monthly: A contribution that recurs monthly.
is_mobile - Whether the contribution was made from a mobile device.
token - Switchboard tracking token passed to the form that created this contribution. Can be used to link contributions back to Switchboard Broadcasts, similar to token in actblue_donations.
refcode - Referral code captured with the contribution, if any.
is_recurring - Whether this contribution is part of a recurring series.
recurring_frequency - The frequency of this contribution, if recurring.
- once: A one-time, non-recurring contribution.
- weekly: A contribution that recurs weekly.
- monthly: A contribution that recurs monthly.
is_recurring_cancelled - Whether the recurring series this contribution belongs to has since been cancelled.
is_new_donor - Whether this is the donor's first contribution to this organization.
contribution_form_name - The internal name of the form the contribution was made through. See forms.
committee_name - The name of the committee that received the contribution.
donor_first_name - The donor's first name.
donor_last_name - The donor's last name.
donor_email - The donor's email address.
donor_phone - The donor's phone number.
donor_address - The donor's street address.
donor_city - The donor's city.
donor_state - The donor's state.
donor_postal_code - The donor's postal code.
donor_country - The donor's country.
donor_employer - The donor's employer, as collected for campaign finance compliance purposes.
donor_occupation - The donor's occupation, as collected for campaign finance compliance purposes.
donor_employment_status - The donor's employment status, as collected for campaign finance compliance purposes.
- employed: The donor reported being currently employed.
- retired: The donor reported being retired.
- not_collected: Employment status was not collected for this donor.

`count_on_me_disputes`

id - Unique identifier for the dispute.
contribution_id - Unique identifier for the contribution this dispute applies to. See switchboard_contributions.
created_at - ISO 8601 timestamp for when the dispute was created.
modified_at - ISO 8601 timestamp for when the dispute was last modified (such as a status update).
dispute_at - ISO 8601 timestamp for when the dispute was filed.
status - The current state of the dispute.
- warning_needs_response: An early-stage dispute warning has been issued and needs a response.
- warning_under_review: A response to an early-stage dispute warning is under review.
- warning_closed: An early-stage dispute warning was closed without escalating to a full dispute.
- needs_response: The dispute needs a response.
- under_review: A response to the dispute is under review.
- won: The dispute was resolved in the organization's favor.
- lost: The dispute was resolved in the donor/bank's favor.
- other: The dispute is in a state Switchboard could not map to one of the above.
reason - The reason given for the dispute.
- bank_cannot_process: The donor's bank was unable to process the payment.
- check_returned: A check payment was returned.
- credit_not_processed: The donor claims a credit or refund was not processed.
- customer_initiated: The dispute was initiated directly by the donor.
- debit_not_authorized: The donor claims they did not authorize the debit.
- duplicate: The donor claims they were charged more than once.
- fraudulent: The donor claims the charge was fraudulent.
- general: No more specific reason was given.
- incorrect_account_details: The payment method's account details were incorrect.
- insufficient_funds: There were insufficient funds to cover the payment.
- product_not_received: The donor claims they did not receive what was promised.
- product_unacceptable: The donor claims what was received was not as described.
- subscription_canceled: The donor claims they canceled the underlying recurring contribution.
- unrecognized: The donor did not recognize the charge.
- other: The dispute reason is one Switchboard could not map to one of the above.
amount_in_cents - The disputed amount, in cents.

`count_on_me_forms`

id - Unique identifier for the form.
created_at - ISO 8601 timestamp for when the form was created.
modified_at - ISO 8601 timestamp for when the form was last modified.
published_at - ISO 8601 timestamp for when the form was last published.
closed_at - ISO 8601 timestamp for when the form was closed.
archived_at - ISO 8601 timestamp for when the form was archived.
name - Name of the form for internal use.
title - Title of the form shown publicly.
slug - Slug used to generate form URLs.
form_type - Type of form.
- basic - Form where people can fill out custom-configured fields.
- contribution - Form where people can make contributions.
layout - Layout of form. Always NULL unless form_type = 'contribution'.
- classic - Original, desktop-first contributions form design.
- mobile_first - Updated, mobile-first contributions form design.
is_published - If true, the form is currently published and visible.
is_closed - If true, the form was closed and is no longer accepting submissions.
is_archived - If true, the form was archived so it doesn't show up in most internal form listings.
has_unpublished_changes - If true, the form has changes that have yet to be published.
label_id - If present, the label to attach to contacts generated by this form.

`count_on_me_form_responses`

id - Unique identifier for the response.
created_at - ISO 8601 timestamp for when the response was created.
modified_at - ISO 8601 timestamp for when the response was last modified.
submitted_at - ISO 8601 timestamp for when the response was submitted.
form_id - Unique identifier of the form this was in response to.
data - Raw data submitted in the form response.

`count_on_me_payouts`

id - Unique identifier for the payout.
created_at - ISO 8601 timestamp for when the payout was created.
modified_at - ISO 8601 timestamp for when the payout was last modified (such as a status update).
payout_at - ISO 8601 timestamp for when the payout was made.
status - The current state of the payout.
- paid: The payout has been successfully paid out.
- pending: The payout has been created but has not yet been sent.
- in_transit: The payout has been sent and is on its way to the destination account.
- canceled: The payout was canceled before completing.
- failed: The payout failed to complete.
- other: The payout is in a state Switchboard could not map to one of the above.
payout_method_type - The method used for the payout.
- card: The payout was sent to a debit card.
- bank: The payout was sent to a bank account.
- other: The payout method is one Switchboard could not map to one of the above.
payout_method_last_4 - The last 4 digits of the card or bank account the payout was sent to.
amount_in_cents - The gross payout amount, in cents, before fees.
fee_in_cents - The fee deducted from the payout, in cents.
net_in_cents - The net amount paid out, in cents, after fees.

`count_on_me_refunds`

id - Unique identifier for the refund.
contribution_id - Unique identifier for the contribution this refund applies to. See switchboard_contributions.
created_at - ISO 8601 timestamp for when the refund was created.
modified_at - ISO 8601 timestamp for when the refund was last modified (such as a status update).
refund_at - ISO 8601 timestamp for when the refund was issued.
status - The current state of the refund.
- pending: The refund has been initiated but has not yet completed.
- requires_action: The refund requires additional action before it can complete.
- succeeded: The refund has completed successfully.
- failed: The refund failed to process.
- canceled: The refund was canceled before completing.
- other: The refund is in a state Switchboard could not map to one of the above.
reason - The reason the refund was issued.
- wrong_amount: The contribution was refunded because it was for the wrong amount.
- duplicate: The contribution was refunded because it was a duplicate.
- fraudulent: The contribution was refunded because it was identified as fraudulent.
- radar_event: The contribution was refunded due to a Stripe Radar fraud/risk event.
- requested_by_donor: The contribution was refunded at the donor's request.
- other: The refund reason is one Switchboard could not map to one of the above.
amount_in_cents - The refunded amount, in cents.

`credits_daily`

payment_date - The date the credit was applied in UTC (YYYY-MM-DD format)
credit_type - The type of credit applied
- ACH Payment: Payment via ACH transfer
- Wire Payment: Payment via wire transfer
- Check Payment: Payment via check
- Plastiq Payment: Payment via Plastiq
- Stripe Payment: Payment via Stripe
- Refund from Switchboard: Refund issued by Switchboard
- Credit from Switchboard: Credit issued by Switchboard
- Balance Withdrawal: Withdrawal from balance
- Undelivered Messages: Credit for undelivered messages
- Fractional Adjustment Credit: Fractional adjustment
description - Additional description for the credit
modified_at - ISO 8601 timestamp for when the record was last modified
amount_credits - The total credit amount for this day/type/description combination

`emails`

email_address - The email address
created_at - ISO 8601 timestamp for when the email was added in this organization.
modified_at - ISO 8601 timestamp for when the email was last modified.
valid_email - Whether this switchboard has determined this email is valid or not. A valid email has a higher deliverability.
unsubscribed - Whether this email unsubscribed from receiving emails
first_name - The first name associated with the email address
middle_name - The middle name associated with the email address
last_name - The last name associated with the email address
preferred_name - The preferred name associated with the email address
city - The city associated with the email address
street_address - The street address associated with the email

state_abbr - US 2 letter state abbreviation associated with the email address
zip_code - US zip code associated with the email address
custom_source - The custom source associated with the email address
custom_score - The custom score associated with the email address
custom_id - The custom id associated with the email address

`email_blasts`

id - Unique identifier for the email blast.
created_at - ISO 8601 timestamp for when the email blast was created.
modified_at - ISO 8601 timestamp for when the email blast was last modified (such as a status update).
start_time - ISO 8601 timestamp for when the email blast started.
scheduled_time - ISO 8601 timestamp for the email blast’s scheduled start time, if any.
email_list_id - The unique idenfier of the email list used in this blast, or null if no email list associated.
title - Name of the email blast. This is shown in the Switchboard app to help identify email blasts similar to a file name.
description - Description of the email blast. This is shown in the Switchboard app when you open an email blast as a place to store notes and additional details that the title does not cover.
creator - Identifier for the user who created the phone list. Generally an email address.
status - The current state of the email blast.
- draft: The email blast has been created but not scheduled or sent.
- error: The email blast failed to send.
- scheduled: The email blast has been scheduled for sending at a later date and/or time.
- sending: The email blast is actively sending.
- paused: The email blast was paused during sending.
- sent: The email blast has completed sending its messages.
- stopped: The email blast has been paused permanently.
email_sender - The email address to send the email blast from.
email_sender_reply_to - The email address to tell email clients to reply to instead of the sender, if applicable.
opens - The number of times someone has opened an email that we detected.
unique_opens - The same as opens but not counting the same person opening the same email more than once.
clicks - The number of times someone has clicked on a Switchboard-tracked link originating from this email blast.
unique_clicks - The same as clicks but not counting clicking the link from the same email more than once.
spam_reports - The number of times someone has reported an email from this blast as spam that we detected.
donations - The number of times someone has donated to the campaign using a Switchboard-tracked donation link originating from this email blast. You must have an integration with a donation provider configured in Switchboard for this to work.
amount_raised - The amount of money donated to the campaign that Switchboard was able to tie to this email blast in US dollars. You must have an integration with a donation provider configured in Switchboard for this to work.
total_messages - The number of messages sending or sent for this email blast.
previously_unsubscribed - The number of emails who were unsubscribed before the email blast and whom Switchboard will not attempt to send messages to.
skipped - The number of emails who Switchboard will skip for reasons other than being unsubscribed (e.g. invalid emails).
failed_to_deliver - The number of messages that could not be delivered.
delivered - The number of messages that were successfully delivered.
unsubscribes - The number of emails who have unsubscribed after receiving this email blast.
subject - The subject line template of the email being sent for this email blast.
html_content - HTML template of the email being sent for this email blast.
text_content - Text template of the email being sent for this email blast.

`email_labels`

label_id - ID of the label applied to this email - see labels
email_address - The email address that the label was applied to.
created_at - ISO 8601 timestamp for when the label was applied to the email.

`email_lists`

id - Unique identifier for the email list.
created_at - ISO 8601 timestamp for when the list was created.
modified_at - ISO 8601 timestamp for when the list was last modified (such as a status update).
name - Shown in the Switchboard app to help identify an email list, similar to a file name.
description - Shown in the Switchboard app when you open an email list. A place to store notes and additional details that the name does not cover.
creator - Identifier for the user who created the email list. Generally an email address.
status - The current state of the email list.
- processing: The email list is currently being processed.
- error: The email list failed to process.
- ready: The email list has completed processing and is ready for sending.
num_contacts - Total number of contacts in the phone list.

`email_messages`

email_blast_id - Unique identifier for the email blast that sent this message.
to_email - Email address of the receiver of the message.
from_email - Email address of the sender of the message.
status - The current state of the message.
- created: The message has been created in the system but sending has not begun.
- presend: The message is being prepared for send.
- optedout: The message is not going to be sent because the receiver opted out.
- skipped: The message is not going to be sent (e.g. because the receiver does not exist).
- paused: The message send process has been paused.
- queued: The message is on the queue waiting to be sent.
- accepted: The message has been accepted by the vendor.
- deferred: The receiver is not yet ready to receive the message. The vendor will retry shortly.
- failed: The message failed to send. See error_code for details.
- delivered: The message was delivered successfully.
- infer_delivered: We believe the message was delivered successfully but have not seen definitive proof.
- undelivered: The message was not delivered. See error_code for details.
error_code - If the message failed to send, this is the error code indicating what happened.
sent_time - When the message was sent.
unsubscribed - If true, the user unsubscribed using a link in this message.
opens - How many times this message has been opened that we could detect.
clicks - The number of times a Switchboard-tracked link in this message has been clicked.
spam_reports - The number of times someone has reported this email as spam that we detected.
donations - The number of times someone has donated via a Switchboard-tracked link in this message that we detected. You must have an integration with a donation provider configured in Switchboard for this to work.
donation_amount - The amount of money donated to the campaign using a Switchboard-tracked link in this message in US dollars. You must have an integration with a donation provider configured in Switchboard for this to work.
subject - Subject line for this email.
html_content - Raw HTML content for this email.
text_content - Raw plaintext content for this email.

`labels`

id - Unique identifier for the label.
created_at - ISO 8601 timestamp for when the label was created.
modified_at - ISO 8601 timestamp for when the label was last modified.
archived_at - ISO 8601 timestamp for when the label was archived.
name - Label name

`messaging_clients`

id - Unique identifier for the messaging client.
created_at - ISO 8601 timestamp for when the messaging client was created.
modified_at - ISO 8601 timestamp for when the messaging client was last modified.

sending_number_type - The type of phone number used to send an message.
- local_10dlc - 10-digit long code registered number
- tollfree_verified - Verified Toll-Free number
- shortcode - 5- or 6-digit short code number
- local_unregistered - 10-digit long-code unregistered number (unavailable on Switchboard after 2024).
- tollfree_unverified - Unverified Toll-Free number (unavailable on Switchboard after 2023)
is_active - True if this is the current default messaging client that will be used for new sends.
use_sticky_sender - If set to True, when you text an individual from a specific sending number, all subsequent broadcasts to that same individual will come from the same number. Only applicable to 10DLC messaging clients; null for other sending number types.

`phones`

phone_number - E.164 Formatted number
created_at - ISO 8601 timestamp for when the phone was added in this organization.
modified_at - ISO 8601 timestamp for when the phone was last modified.
carrier_name - The name of the carrier who owns the to number.
is_opted_out - Is this phone opted out
phone_type - The type of phone number.
- landline: Landline
- mobile: Mobile phone
- voip: Voice over Internet Protocol
- unknown: Could not be determined
is_valid - Phone number has been validated as real
first_name - The first name associated with the phone
middle_name - The middle name associated with the phone
last_name - The last name associated with the phone
preferred_name - The preferred name associated with the phone
city - The city associated with the phone
street_address - The street address associated with the phone
state_abbr - US 2 letter state abbreviation associated with the phone
zip_code - US zip code associated with the phone
custom_source - The custom source associated with the phone
custom_score - The custom score associated with the phone
custom_id - The custom id associated with the phone

`phone_labels`

label_id - ID of the label applied to this phone - see labels
phone_number - The phone number that the label was applied to.
created_at - ISO 8601 timestamp for when the label was applied to the phone.

`phone_lists`

id - Unique identifier for the phone list.
created_at - ISO 8601 timestamp for when the list was created.
modified_at - ISO 8601 timestamp for when the list was last modified (such as a status update).
name - Shown in the Switchboard app to help identify phone lists, similar to a file name.
description - Shown in the Switchboard app when you open a phone list. A place to store notes and additional details that the name does not cover.
creator - Identifier for the user who created the phone list. Generally an email address.
status - The current state of the phone list.
- processing: The phone list is currently being processed.
- error: The phone list failed to process.
- ready: The phone list has completed processing and is ready for sending.
num_contacts - Total number of contacts in the phone list.

`phone_list_phones`

phone_list_id - Parent phone list
phone_number - Phone number included on this list. Unique per phone_list_id.
created_at - ISO 8601 timestamp for when the mapping record was generated.

`phone_messages`

unique_key - Unique identifier for a phone message.
modified_at - ISO 8601 timestamp for when the phone message was last modified.
broadcast_id - Unique identifier for the broadcast that sent this message.
to_number - Phone number of the receiver of the message in E.164 format.
from_number - Phone number of the sender of the message in E.164 format.
status - The current state of the message.
- created: The message has been created in the system but sending has not begun.
- presend: The message is being prepared for send.
- optedout: The message is not going to be sent because the receiver opted out.
- skipped: The message is not going to be sent (e.g. because the receiver is a landline).
- paused: The message send process has been paused.
- holding: The message is being held for some reason.
- accepted: The message has been accepted by the vendor.
- scheduled: The message has been scheduled to be sent at a later date.
- queued: The message is on the queue waiting to be sent.
- sending: The message is actively sending.
- sent: The message has been sent but we have not received a delivery notification.
- receiving: This is an incoming message that we are processing.
- received: This is an incoming message that we have processed.
- delivered: The message was delivered successfully. Not all phones send this information, so the phone may stay in sent.
- undelivered: The message was not delivered. See error_code for details.
- failed: The message failed to send. See error_code for details.
- read: The message was read by the receiver. Not all phones send this information.
- cancelled: The message send was cancelled.
error_code - If the message failed to send, this is the error code indicating what happened. These are Switchboard-maintained error codes. Learn more →
has_expired_callback - A boolean flag that indicates if we timed out waiting for a delivery receipt from our messaging provider(s). This does not indicate that a message failed to deliver, but that either the carrier or an intermediate provider timed out in waiting for the device to confirm delivery. This waiting period is usually 2-3 days.
message_type - The type of message.
- unknown: The message type is unknown. This can only happen for incoming messages.
- mms: This is an MMS message.
- sms: This is an SMS message.
message_direction - Either "inbound" or "outbound", indicates an incoming or an outgoing message.
"inbound" indicates a message sent to one of your organization's numbers. "outbound" messages can be a few different types of messages:
- Outgoing broadcast message (broadcast_id will be populated).
- A test send from the broadcast page.
- Manual sends from the inbox.
- Switchboard-default auto responses, like "You have been opted out" (Note: you are never charged for these).
- Automated responses from custom keywords. Learn more →
- Automated new donor/subscriber messages. Learn more →

sending_number_type - The type of phone number used to send an "outbound" message.
- local_10dlc - 10-digit long code registered number
- tollfree_verified - Verified Toll-Free number
- shortcode - 5- or 6-digit short code number
- local_unregistered - 10-digit long-code unregistered number (unavailable on Switchboard after 2024).
- tollfree_unverified - Unverified Toll-Free number (unavailable on Switchboard after 2023)
count_message_segments - Number of message segments sent for this message. Will always be 1 if message_type = 'mms', but may be more than 1 for message_type = 'sms'.
NOTE: This field is only updated nightly (~1-2AM), when we confirm and finalize the correct number of segments to be billed.
sent_at - When the message was sent.
received_at - When the message was received, if an inbound message.
is_opt_out - If true, this is an incoming opt-out message that resulted in opting out this phone (if not already opted out).

text - The message text sent (or received).
media_urls - Public urls that were sent in the message.
NOTE: These last four fields are included for easy compatibility with our exports and API, but are not always fully up-to-date. They increment for about 2 weeks after a message is sent, but then are only refreshed once per week. This statistically captures 99.97% of actions, but if you want the the most recent counts, please aggregate yourself using joins to phone_message_actions or actblue_donations.
clicks - The number of times a Switchboard-tracked link in this message has been clicked.
donations - The number of times someone has donated via a Switchboard-tracked link in this message that we detected. You must have an integration with a donation provider configured in Switchboard for this to work.
donation_amount - The amount of money donated to the campaign using a Switchboard-tracked link in this message in US dollars. You must have an integration with a donation provider configured in Switchboard for this to work.
replies - The number of replies received to this message.

`phone_message_actions`

broadcast_id - Unique identifier for the broadcast that sent the message that prompted this action.
to_number - Phone number of the recipient that took this action.
created_at - ISO 8601 timestamp for when the action was recorded.
action - What action was recorded.
- CLICK - Message recipient clicked on tracked link in the message.
- BOT_CLICK - A click on tracked link in the message was recorded, but Switchboard has identified it as a non-human click.
- DONATION - Message recipient donated via tracked link in the message.
- FORM_RESPONSE - Message recipient responded to Switchboard form linked in the message.
- REPLY - Message recipient replied to a message.
user_agent - Any recorded user agent data recorded when receiving clicks or form responses. Can be parsed to extract information on user device types.
Note: user_agent data only available for clicks & form responses

`phone_opt_outs`

phone_number - The phone number that the label was applied to.
created_at - ISO 8601 timestamp for when this phone opt-out was logged.
opt_out_source - Where or how the phone was opted out.
- CONTACT_REQUEST - Opt-out via keyword from a incoming text.
- USER_LIST - Opt-outs via CSV list upload from Switchboard UI.
- SB_USER - Manual opt-out by Switchboard user from the Inbox or All Phones pages.
- NGPVAN - Opt-out synced via integration with NGPVAN.
- ACTBLUE_DONATION - Opt-out synced via integration with ActBlue.
- ADMIN_PANEL - Manual opt-out by Switchboard administrator or engineer.
  NOTE: Logging SB_USER opt-outs began in June 2025, before that any manual opt-outs in product were classified as CONTACT_REQUEST

`saved_searches`

id - Unique identifier for the saved search.
created_at - ISO 8601 timestamp for when the saved search was created.
modified_at - ISO 8601 timestamp for when the saved search was last modified (such as a status update or name change).
archived_at - ISO 8601 timestamp for when the saved search was archived.
name - Name of the saved search.
audience_type - Type of audience for the search, either phone or email.
creator - Email of the original creator of the saved search.
description - User-added description for the search, if any.
last_refreshed_at - ISO 8601 timestamp for when the most recent evaluation of the saved search was initiated.
num_contacts - Count of contacts in the latest evaluation of the search, if any.

`saved_search_result_emails`

NOTE: This model has all emails from the LATEST successful saved search evaluation. That means that rows CAN be deleted from this tables when you re-run a saved search. This may affect the way you sync this table, you may want to avoid syncing incrementally.

saved_search_id - Identifier for the saved search that this email address maps to.
email_address - Email address included in the saved search.
created_at - ISO 8601 timestamp for when this saved search result was created.

`saved_search_result_phones`

NOTE: This model has all phones from the LATEST successful saved search evaluation. That means that rows CAN be deleted from this tables when you re-run a saved search. This may affect the way you sync this table, you may want to avoid syncing incrementally.

saved_search_id - Identifier for the saved search that this phone maps to.
phone_number - Phone number included in the saved search.
created_at - ISO 8601 timestamp for when this saved search result was created.

`shortlink_domains`

id — Unique identifier for the shortlink domain.
created_at — ISO 8601 timestamp for when the domain was created.
modified_at — ISO 8601 timestamp for when the domain was last modified.
archived_at — ISO 8601 timestamp for when the domain was archived. Null if the domain is not archived.
is_primary — Whether this is the organization's default domain for newly created shortlinks. At most one domain per organization is primary.
base_url — The domain used as the prefix for shortlinks (e.g. go.example.org).
default_url — The URL visitors are redirected to when they hit the domain without an active valid shortlink slug.
is_provisioned — Whether the domain's DNS record has been set up and the domain is live.

`token_data`

token - Tracking token used to link phone numbers to their donations
created_at - ISO 8601 timestamp for when the token was generated.
broadcast_id - Unique identifier for the broadcast that generated this tracking token
to_number - Phone number of the recipient that this token was generated for and sent to.

`invoices`

id - Unique identifier for the invoice.
created_at - ISO 8601 timestamp for when the invoice was created.
modified_at - ISO 8601 timestamp for when the invoice was last modified.
invoice_sent_at - ISO 8601 timestamp for when the invoice was sent to the customer.
invoice_reminder_sent_at - ISO 8601 timestamp for when the most recent payment reminder was sent.
receipt_sent_at - ISO 8601 timestamp for when the payment receipt was sent.
due_at - ISO 8601 timestamp for when payment is due.
due_date - Calendar date (UTC) on which payment is due.
service_period_start_at - ISO 8601 timestamp for the start of the billing service period the invoice covers. Null for non-Stripe invoices (e.g. Wave) and any invoice without line-item period data.
service_period_end_at - ISO 8601 timestamp for the end of the billing service period the invoice covers. Null for non-Stripe invoices (e.g. Wave) and any invoice without line-item period data.
invoice_number - Human-readable invoice number shown to the customer.
provider - Payment provider that issued the invoice.
- stripe - Invoice issued through Stripe.
- wave - Invoice issued through Wave.
status - Current status of the invoice. Values follow the issuing provider's native statuses.
- paid - Paid in full.
- open - Finalized and awaiting payment.
- overdue - Past its due date and not paid in full.
- partial - Partially paid.
- unpaid - Sent but not yet paid.
- overpaid - Paid more than the amount due.
- sent - Sent to the customer.
- viewed - Viewed by the customer.
- saved - Created but not yet sent.
- void - Voided and no longer collectible.
- uncollectible - Marked as uncollectible.
- collected - Collected.
payment_terms - Payment terms for the invoice. Null if no terms are set.
- prepayment_required - Payment required before service is provided.
- due_on_receipt - Payment due upon receipt.
- net_30 - Payment due within 30 days.
amount_due - Amount still owed on the invoice, in US dollars.
amount_paid - Amount paid on the invoice so far, in US dollars.
amount_total - Total amount of the invoice, in US dollars.
is_overdue - Whether the invoice is currently past due and not yet paid or voided. This is a point-in-time value refreshed roughly hourly; because an invoice becoming overdue does not update modified_at, replicators syncing incrementally on modified_at may see a stale value. To evaluate it live, use: status not in ('paid', 'void') and (status = 'overdue' or due_date < current_date()).
view_url - Link to the hosted invoice page where the invoice can be viewed and paid. This link is unauthenticated and does not expire, so anyone with the URL can view and pay the invoice — treat it as sensitive.

`webhook_endpoints`

id - Unique identifier for the webhook endpoint.
created_at - ISO 8601 timestamp for when the endpoint was created.
modified_at - ISO 8601 timestamp for when the endpoint was last modified.
name - Human-readable name for the endpoint. May be empty.
destination_url - The URL that receives webhook POST requests.
is_enabled - Whether the endpoint is enabled. Events are not delivered to a disabled endpoint.
username - HTTP Basic auth username sent with each request. Empty when Basic auth is not configured.
has_password - Whether an HTTP Basic auth password is configured. The password itself is never mirrored.
version - Webhook API version, which determines the event types and payload format the endpoint uses. One of v1 or actblue_compat.
creator - Email address of the user who created the endpoint, if available.

`webhook_endpoint_subscriptions`

One row per event type an endpoint is subscribed to. An endpoint with no rows here receives nothing.

id - Unique identifier for the subscription.
webhook_endpoint_id - Identifier of the endpoint this subscription belongs to. Joins to webhook_endpoints.id.
created_at - ISO 8601 timestamp for when the subscription was created.
modified_at - ISO 8601 timestamp for when the subscription was last modified.
is_enabled - Whether the subscription is active. Always matches the parent endpoint's is_enabled: enabling or disabling an endpoint updates all of its subscriptions.
version - Webhook API version. Always matches the parent endpoint's version.
event_type - The event type subscribed to.
- * - Every event available in the endpoint's version, including event types added later.
- broadcast.* - Every broadcast event.
- broadcast.created - A broadcast was created.
- broadcast.status_changed - A broadcast began sending, paused, stopped, resumed, or completed.
- broadcast.schedule_changed - A broadcast was scheduled, or an existing schedule was updated.
- broadcast.error_occurred - A broadcast encountered an error.
- canned_response.* - Every canned response event.
- canned_response.created, canned_response.updated, canned_response.deleted - A canned response was created, updated, or deleted.
- keyword.* - Every keyword event.
- keyword.created, keyword.deleted - A keyword was created or deleted.
- keyword.triggered - An inbound message triggered a keyword.
- messaging_client.* - Every messaging client event.
- messaging_client.created, messaging_client.updated - A messaging client was created or updated.
- phone_message.received - An inbound phone message was received.
- form_response.created - A form response was submitted.
- unified_contribution.created - A contribution was recorded.
- contribution.succeeded, contribution.refunded, contribution.cancelled - A contribution succeeded, was refunded, or was cancelled. These appear only on actblue_compat endpoints.
- job.* - Every job event.
- job.completed, job.errored - An asynchronous job finished or failed. The kind of job is in the payload's job_type, not the event name.

NOTE: Event types ending in .* are wildcards. A single broadcast.* row means the endpoint receives all four broadcast events, so counting rows in this table understates how many event types an endpoint actually covers. Expand wildcards before counting.

NOTE: version constrains which event types can appear. A v1 endpoint carries the broadcast, canned response, keyword, messaging client, phone message, form response, and unified contribution types; an actblue_compat endpoint carries the contribution.* types.

`switchboard-mirror.catalog` Tables

Unlike the rest of the SQL Mirror, the catalog dataset is a single shared dataset secured with BigQuery row-level security. Every mirror user will query the same table, for example:

select *
from `switchboard-mirror.catalog.organizations`

and the query will only return the rows that the querying user is allowed to see.

`catalog.organizations`

Contains one row per organization that has an active SQL Mirror dataset, mapping the organization to the BigQuery dataset holding its data. Lets you look up and continually discover which dataset(s) to query instead of hard-coding dataset names.

id - Unique identifier for the organization. Same value the public API returns as organization_id.
organization_name - Display name of the organization.
dataset_name - BigQuery dataset name holding this organization's mirrored data. Prefix with the switchboard-mirror project for a fully qualified reference (switchboard-mirror.<dataset_name>).
created_at - ISO 8601 timestamp for when the organization was created in Switchboard.
modified_at - ISO 8601 timestamp for when the organization's data was last modified.
dataset_created_at - ISO 8601 timestamp for when the SQL Mirror dataset was created for this organization.

Note: only your organizations with active SQL Mirrors are shown here, not all of the organizations that your user can access.

Query Examples

How many messages am I actually sending out, month over month?

select
	datetime_trunc(
			datetime(start_time, "America/New_York"), -- Or your timezone
	    month
    ) as month,
    sum(delivered) as count_messages_delivered,
from `switchboard-mirror.barbie_for_president.broadcasts`
group by 1
order by 1 desc

Example Output:

In the last month, how many new phones have been added to my account each day?

select
    date(created_at, "America/New_York") as day,
    count(*) as count_phones_added,
from `switchboard-mirror.barbie_for_president.phones`
where created_at > current_timestamp() - interval 30 day
group by 1
order by 1 desc

Example Output:

For my recent broadcast titled “Help Elect Barbie Today!”, which states were donors most often from?

select
    donations.donor_state,
    count(*) as count_donors,
from `switchboard-mirror.barbie_for_president.broadcasts` as broadcasts
left join `switchboard-mirror.barbie_for_president.token_data` as token_data
    on broadcasts.id = token_data.broadcast_id
left join `switchboard-mirror.barbie_for_president.actblue_donations` as donations
    on token_data.token = donations.token
where broadcasts.title = 'Help Elect Barbie Today!'
    and donations.donor_state is not null -- State info is not always available
group by 1
order by 2 desc

Example Output:

When were my most recent phone opt-outs, and how were they opted out?

with

most_recent_opt_outs as (

    select *,
    from `switchboard-mirror`.`barbie_for_president`.`phone_opt_outs`
    qualify row_number() over (partition by phone_number order by created_at desc) = 1

)

select
    phones.phone_number,
    phones.carrier_name,
    phones.phone_type,
    phones.created_at as phone_added_at,
    most_recent_opt_outs.opt_out_source,
    most_recent_opt_outs.created_at as opt_out_at,
from `switchboard-mirror`.`barbie_for_president`.`phones` as phones
left join most_recent_opt_outs
    on phones.phone_number = most_recent_opt_outs.phone_number
where most_recent_opt_outs.phone_number is not null
order by opt_out_at desc

Example Output:

What is the outstanding balance for my organization?

with

charges_total as (

    select sum(amount_charges_net) as amount_charges_total from `switchboard-mirror`.`barbie_for_president`.`charges_daily`

),

credits_total as (

    select sum(amount_credits) as amount_credits_total from `switchboard-mirror`.`barbie_for_president`.`credits_daily`

)

select
    charges_total.amount_charges_total,
    credits_total.amount_credits_total,
    charges_total.amount_charges_total - credits_total.amount_credits_total as outstanding_balance,
from charges_total
cross join credits_total

Example Output:

What are my monthly charges by message type?

select
    date_trunc(date_eastern, month) as month,
    message_type,
    sum(count_messages_charged) as count_messages_charged,
    sum(count_segments_charged) as count_segments_charged,
    sum(amount_charges_gross) as amount_charges_gross,
    sum(amount_charges_refunded) as amount_charges_refunded,
    sum(amount_charges_net) as amount_charges_net,
from `switchboard-mirror`.`barbie_for_president`.`charges_daily`
group by 1, 2
order by 1 desc, 2

Example Output:

SQL Mirror Data Docs

Available Tables and Fields

actblue_donations

broadcasts

charges_daily

count_on_me_accounts

count_on_me_contributions

count_on_me_disputes

count_on_me_forms

count_on_me_form_responses

count_on_me_payouts

count_on_me_refunds

credits_daily

emails

email_blasts

email_labels

email_lists

email_messages

labels

messaging_clients

phones

phone_labels

phone_lists

phone_list_phones

phone_messages

phone_message_actions

phone_opt_outs

saved_searches

saved_search_result_emails

saved_search_result_phones

shortlink_domains

token_data

invoices

webhook_endpoints

webhook_endpoint_subscriptions

switchboard-mirror.catalog Tables

catalog.organizations

Query Examples

`actblue_donations`

`broadcasts`

`charges_daily`

`count_on_me_accounts`

`count_on_me_contributions`

`count_on_me_disputes`

`count_on_me_forms`

`count_on_me_form_responses`

`count_on_me_payouts`

`count_on_me_refunds`

`credits_daily`

`emails`

`email_blasts`

`email_labels`

`email_lists`

`email_messages`

`labels`

`messaging_clients`

`phones`

`phone_labels`

`phone_lists`

`phone_list_phones`

`phone_messages`

`phone_message_actions`

`phone_opt_outs`

`saved_searches`

`saved_search_result_emails`

`saved_search_result_phones`

`shortlink_domains`

`token_data`

`invoices`

`webhook_endpoints`

`webhook_endpoint_subscriptions`

`switchboard-mirror.catalog` Tables

`catalog.organizations`