Data Schema Updates

Current Schemas

You can find our existing BigQuery Mirror Schema here and our existing Scheduled Exports schema here.

Schema Change Log

January 31, 2024

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

December 20, 2023

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.

November 30, 2023

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 .

November 9, 2023

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

October 26, 2023

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.

September 28, 2023

BigQuery Mirror

No Changes

Scheduled Exports

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

September 20, 2023

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.

September 19, 2023

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.

August 31, 2023

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.

August 29, 2023

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.

August 11, 2023

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

July 20, 2023

BigQuery Mirror

  • Added field: user_to_campaign_associations.last_seen_on

Scheduled Exports

No changes

June 12, 2023

BigQuery Mirror

No changes

Scheduled Exports

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