Data Schema Updates

BigQuery Mirror

We have added a whole bunch of fields to your views today to give you full visibility into all the latest features we’ve added to Reach. The new columns are:

• relationship_types
  • locked (boolean): Relationships using locked relationship types cannot be added or removed by users. They will not appear on Relationship Type pickers in the app.
• campaigns
  • mobilize_high_priority_filter (boolean): If true, we will only show Mobilize events in Reach which are flagged in Mobilize as high priority.
  • mobilize_org_only_filter (boolean): If true, we will only show Mobilize events in Reach which are owned by this organization directly. Events from promoted orgs will not be included.
  • show_unmasked_contact_info_to (boolean): Any users with these roles will see full contact info in Reach. Users with lower-level roles will see privacy-masked contact info
  • show_unmasked_addresses_to (boolean): Any users with these roles will see full addresses in Reach. Users with lower-level roles will see privacy-masked addresses that omit house number and apartment number.
  • ugc_default_gallery_id (string): This is the content_gallery that will be pre-selected when an admin is approving user-generated content. If this is null, an admin will need to explicitly select a gallery to move each asset to upon approval
  • ugc_allowed_for (string): Determines which user roles will be able to upload user generated content.
  • ugc_disclaimer (string): Optional legal warning campaigns can choose to display to their users on the user generated content form.
• users
  • phone_is_verified (boolean): Confirmed that the user has authenticated using their phone number or verified it and can receive SMS at this phone number.
  • country (string): Users may specify their county upon sign-up and edit it at any time. If a user is outside the US or US Territories, Reach will not show them other address fields. Country may or may not be consistent with phone_country_code
  • deleted_on (timestamp): If a user has deleted their account, this column will contain the date and time they did so, and any other columns with PII will be cleared.
• users_to_campaign_associations
  • is_chat_moderator (boolean): Chat moderators can delete and edit chat messages in all chat channels of they are members of on this campaign. Admins and Owners are automatically chat moderators.
• action_cards
  • asset_label_ids : Determines which labels will be assigned to content uploaded via a Content Upload action card.
• user_action_card_interactions
  • content_asset_id (string): For content upload interactions, this is the ID of the content asset uploaded by the user.

Exports

New columns in the “Users” export:

• Phone Country Code
• Phone Verified
• Email Verified
• Country
• Chat Moderator

BigQuery Mirror

No changes.

Scheduled Exports

The “Content Feedback” and “Content Actions” exports now contain rows new columns helping you better understand which campaigns are accessing your content and which campaigns you’re accessing content from.

New columns are:

• Content Actions
  • Shared Gallery from Campaign
  • Action Taken in Campaign
• Content Feedback
  • Shared Gallery from Campaign
  • Feedback Left in Campaign

BigQuery Mirror

This afternoon, we introduced three columns:

• content_galleries.sharing tells which campaigns a gallery is shared to. Possible values are ‘campaign_wide’, ‘app_wide’, ‘org_wide’, and ‘select_campaigns’. Campaign_wide galleries are the default and are only accessible by the campaign that owns them.
• content_galleries.shared_with_campaign_ids If the gallery is shared with select campaigns, the specific campaigns the gallery is shared with are stored here as repeated integer of campaign IDs.
• campaigns.default_homescreen_tab is a string describing the default home screen tab set for the campaign.

Scheduled Exports

The “Content Feedback” and “Content Actions” exports now contain rows for actions and feedbacks created by users on other campaigns who are accessing assets in one of your campaign’s shared galleries.

BigQuery Mirror

This afternoon, we introduced two columns:

• question_sets.show_in_accordion: If true, this question set will display in a collapsible accordion with the set name or display label in the header.
• user_to_campaign_associations.myc_vanid: For campaigns using our My Campaign VAN Integration, it is now possible to sync users to My Campaign and when they have been synced, this column will give you their My Campaign VAN ID.

Scheduled Exports

New “My Campaign VAN ID” column in Users .

BigQuery Mirror

This afternoon, we introduced several new columns:

• Content Feedback: Now that users can leave feedback after sharing, we have added new columns which will house responses to the feedback form’s questions and link feedbacks to specific content actions.
  • content_feedback.share_audience
  • content_feedback.share_engagement
  • content_feedback.share_sentiment
  • content_feedback.content_action_id
• Now that users can get push notifications with Content Feedback reminders, we have added:
  • content_actions.feedback_notification_sent
  • user_to_campaign_associations.notifications_content_feedback_updates_enabled
• You can now see when a user first launched Reach with your campaign active or switched to your campaign in user_to_campaign_associations.first_seen_on. Please note that we are only starting to collect this data today, so you will never see a date older than 11/9/2023
• Values in user_to_campaign_associations.last_seen_on should be updating much more consistently and reliably to show you the most recent time a user launched or app-switched to Reach with your campaign active, or switched to your campaign in the app.

Scheduled Exports

The Content Feedback export now has the following new columns:

• Share Audience
• Share Engagement
• Share Sentiment
• Content Action ID

BigQuery Mirror

This afternoon, we introduced several new columns to your BigQuery views:

• based_on_template_id: In all cases where admins can create new objects based on our Template Libraries, we are now storing the IDs of the template objects which the new objects are based on in the following new columns:
  • action_cards.based_on_template_id
  • contact_scripts.based_on_template_id
  • questions.based_on_template_id
  • question_choices.based_on_template_id
  • tags.based_on_template_id
• You can now see the status of the new Default Chat Messages Notification setting we added in version 23.4.0 in the new campaigns.default_chat_messages_notification_setting field.
• You can now see any Mobilize tag filters in the new campaigns.mobilize_tag_filter field.

Scheduled Exports

No changes.

BigQuery Mirror

No Changes

Scheduled Exports

We have added two new exports: Content Actions and Content Feedback. Details on these here.

BigQuery Mirror

This afternoon, we were able to seamlessly roll out a vastly smaller/faster/cheaper version of your added_people view. It has gone from something like 320GB to 83 MB for a typical client. That’s about 3900x smaller! So hopefully your syncs today took another major step forward.

Scheduled Exports

No changes.

BigQuery Mirror

Today, we have introduced new versions of all your views except `added_people` and `person_tag_associations` to make them smaller, faster, and cheaper to query. For views that used to be massive, you will see the biggest differences. For example, a query of the `actions` view used to process about 160GB. Now it will process about 233 MB. That is about 700x smaller! Similarly, a query to `responses` used to process 160GB and now processes about 1GB. 

We have now replaced almost all of your views with the new cheaper versions seamlessly in the background. The only action you have to take is increasing the frequency at which you query them!

Scheduled Exports

No changes.

BigQuery Mirror

• The new Content Sharing tables were using the wrong datatype for their user id columns. So the following fields changed from INTEGER to STRING:
  • content_actions.user_id
  • content_assets.created_by_user_id
  • content_bookmarks.user_id
  • content_feedback.created_by_user_id
  • content_galleries.created_by_user_id
  • content_labels.created_by_user_id
  • content_shared_galleries.created_by_user_id

Scheduled Exports

No changes.

BigQuery Mirror

• Added tables to support many new upcoming features around content sharing:
  • content_actions
  • content_assets
  • content_bookmarks
  • content_feedback
  • content_galleries
  • content_label_associations
  • content_labels
  • content_shared_galleries
  • social_platforms
• New columns on existing tables
  • action_cards.content_asset_id
  • campaigns.enable_content_library

Scheduled Exports

Possible new value of share_content in the Interaction Type column of the Action Card Interactions export.

BigQuery Mirror

• Added table: user_devices with the following columns:
  • id
  • user_id
  • status
  • platform
  • notification_permissions
  • created_on
  • updated_on
  • reach_native_version
  • locale
• Added table push_notification_actions with the following columns:
  • id
  • user_device_id
  • push_notification_id
  • action_type
  • created_on
  • updated_on
  • campaign_id
  • notification_campaign_id

Scheduled Exports

No changes

BigQuery Mirror

• Added field: user_to_campaign_associations.last_seen_on

Scheduled Exports

No changes

BigQuery Mirror

No changes

Scheduled Exports

• Three new columns in the Action Card Interactions export:
  • Upload URL
  • Comment
  • Upload Type