Mobile Commons Data Dictionary

This is a list of tables and columns generated by the Bluelink MobileCommons to Redshift sync.

In addition to the below columns, column "insert_timestamp" is added to all tables. This is a timestamp of when Bluelink inserted the row into the table. It can be used to see when the table was last updated.

The data in these tables comes from the Mobile Commons API responses which are documented in a Mobile Commons subscriber protected article accessible through this link. (Note that this article is password protected and should be accessible to Mobile Commons clients.)

attachments

(bigint) id
(varchar) name
(varchar) thumbnail_link

broadcasts

(bigint) id
(varchar) status
(varchar) name
(varchar) body
(varchar) delivery_time
(boolean) include_subscribers
(boolean) throttled
(boolean) localtime
(boolean) automated
(integer) estimated_recipients_count
(integer) replies_count
(integer) opt_outs_count
(bigint) campaign_id
(boolean) campaign_active
(varchar) campaign_name
(varchar) tags

broadcasts_included_groups

(bigint) parent_id -- ID of the related Broadcast
(bigint) id
(varchar) name
(varchar) type

broadcasts_excluded_groups

(bigint) parent_id -- ID of the related Broadcast
(bigint) id
(varchar) name
(varchar) type

calls

(bigint) id
(varchar) start_time
(varchar) end_time
(integer) duration
(varchar) call_status
(varchar) connection_source
(varchar) email
(varchar) profile_status
(varchar) profile_status_at_call_time
(varchar) handset_number
(varchar) carrier_name
(varchar) voip_number
(varchar) destination_number
(varchar) transcription
(integer) voicemail_duration
(varchar) voicemail
(bigint) mconnect_id
(bigint) profile_id
(varchar) legislator_first_name
(varchar) legislator_last_name
(varchar) legislator_party
(varchar) legislator_district
(varchar) legislator_state

campaigns

(bigint) id
(boolean) active
(varchar) name
(varchar) description
(varchar) tags

campaigns_opt_in_paths

(bigint) parent_id -- ID of the related Campaign
(bigint) id
(boolean) active
(varchar) name

campaign_subscribers

(bigint) id
(bigint) profile_id
(varchar) phone_number
(varchar) activated_at
(varchar) opted_out_at
(bigint) campaign_id
(bigint) opt_in_path_id

groups

(bigint) id
(varchar) type
(varchar) status
(varchar) name
(integer) size
(varchar) hint

group_members

(bigint) id -- the ID of the Profile of the group member
(bigint) group_id
(varchar) first_name
(varchar) last_name
(varchar) phone_number
(varchar) email
(varchar) status
(varchar) created_at
(varchar) updated_at
(varchar) opted_out_at
(varchar) opted_out_source
(varchar) source_type
(varchar) source_name
(bigint) source_id
(bigint) source_opt_in_path_id
(bigint) source_message_id
(varchar) address_street1
(varchar) address_street2
(varchar) address_city
(varchar) address_state
(varchar) address_postal_code
(varchar) address_country
(float) last_saved_location_latitude
(float) last_saved_location_(bigint)itude
(varchar) last_saved_location_precision
(varchar) last_saved_location_city
(varchar) last_saved_location_state
(integer) last_saved_location_postal_code
(varchar) last_saved_location_country
(varchar) last_saved_districts_congressional_district
(varchar) last_saved_districts_state_upper_district
(varchar) last_saved_districts_state_lower_district
(varchar) last_saved_districts_split_district

keywords

(bigint) id
(boolean) active
(varchar) name
(bigint) opt_in_path_id
(varchar) ended_at
(varchar) created_at
(varchar) updated_at

mconnects

(bigint) id
(boolean) active
(varchar) name
(varchar) destination_number
(varchar) destination_description
(varchar) voip_number
(varchar) call_type
(boolean) autoroute
(varchar) subscriber_follow_up
(varchar) nonsubscriber_follow_up
(integer) call_timeout
(varchar) failover_number
(varchar) failover_text
(boolean) voicemail
(boolean) transcribe
(varchar) shortcode
(bigint) crm_alert_id
(varchar) tags

mdatas

(bigint) id
(varchar) name
(varchar) type
(varchar) uuid

mdata_queries

(bigint) mdata_id
(varchar) created_at
(varchar) keyword
(varchar) input
(varchar) output
(varchar) result
(varchar) error_log_body
(varchar) query_location_street1
(varchar) query_location_city
(varchar) query_location_state
(varchar) query_location_postal_code
(varchar) query_location_country
(float) query_location_latitude
(float) query_location_(bigint)itude
(varchar) query_location_map
(varchar) profile_status
(varchar) profile_email
(varchar) profile_first_name
(varchar) profile_last_name
(varchar) profile_source_type
(varchar) profile_source_name
(varchar) profile_phone_phone_number
(varchar) profile_phone_carrier_name
(varchar) profile_address_street1
(varchar) profile_address_street2
(varchar) profile_address_city
(varchar) profile_address_state
(varchar) profile_address_postal_code
(varchar) profile_address_country
(float) profile_address_latitude
(float) profile_address_(bigint)itude
(varchar) profile_address_geocoded_city
(varchar) profile_address_geocoded_state
(varchar) profile_address_geocoded_postal_code
(varchar) profile_address_geocoded_country

messages

(bigint) id
(varchar) type
(boolean) approved
(varchar) phone_number
(varchar) carrier_name
(bigint) profile
(varchar) body
(bigint) message_template_id
(varchar) keyword
(varchar) received_at
(bigint) previous_id_id
(bigint) next_id_id
(bigint) campaign_id
(boolean) campaign_active
(varchar) campaign_name
(bigint) keyword_id
(varchar) keyword_name
(bigint) keyword_opt_in_path_id

profiles

(bigint) id
(varchar) first_name
(varchar) last_name
(varchar) phone_number
(varchar) email
(varchar) status
(varchar) created_at
(varchar) updated_at
(varchar) opted_out_at
(varchar) opted_out_source
(varchar) source_type
(varchar) source_name
(bigint) source_id
(bigint) source_opt_in_path_id
(bigint) source_message_id
(varchar) address_street1
(varchar) address_street2
(varchar) address_city
(varchar) address_state
(varchar) address_postal_code
(varchar) address_country
(float) last_saved_location_latitude
(float) last_saved_location_(bigint)itude
(varchar) last_saved_location_precision
(varchar) last_saved_location_city
(varchar) last_saved_location_state
(varchar) last_saved_location_postal_code
(varchar) last_saved_location_country
(varchar) last_saved_districts_congressional_district
(varchar) last_saved_districts_state_upper_district
(varchar) last_saved_districts_state_lower_district
(varchar) last_saved_districts_split_district

profiles_clicks

(bigint) parent_id -- ID of the related Profile
(bigint) id
(varchar) created_at
(varchar) url
(varchar) remote_addr
(varchar) http_referer
(varchar) user_agent

profiles_custom_columns

(bigint) parent_id -- ID of the related Profile
(varchar) name
(varchar) created_at
(varchar) updated_at
(varchar) value

profiles_integrations

(bigint) parent_id -- ID of the related Profile

(varchar) constituent_id
(varchar) type
(varchar) synchronized_at

profiles_subscriptions

(bigint) parent_id -- ID of the related Profile
(bigint) campaign_id
(varchar) campaign_name
(varchar) campaign_description
(bigint) opt_in_path_id
(varchar) status
(varchar) opt_in_source
(varchar) created_at
(varchar) activated_at
(varchar) opted_out_at
(varchar) opt_out_source

sent_messages

(bigint) id
(varchar) type
(varchar) status
(varchar) phone_number
(bigint) profile
(bigint) broadcast_id
(varchar) body
(varchar) sent_at
(bigint) message_template_id
(bigint) campaign_id
(boolean) campaign_active
(varchar) campaign_name
(bigint) previous_id_id
(bigint) next_id_id