Understanding the Upsert Writing Mode behavior in BigQuery

What is the Upsert Writing Mode

Upsert is a write mode available in our BigQuery product, which has a special and somewhat more complex behavior than Append and Replace. In this article, you will learn everything you need to know to use Upsert in your transfers.
  • Learn more about the Dataslayer BigQuery platform and the other writing modes in this article.

How the Upsert mode works

Upsert updates the old data and adds the new data at the end of the table. It will always update and add the data within the date range selected in the query configuration.

This option takes longer to complete as it must update everything in the date range selected.

It is important to take into account that Upsert updates the metrics of all the information previously inserted, within the date range, but NOT the dimensions when updating the old data.


This means that if a campaign has changed its name, for example, an extra row of data will be added to this campaign as Upsert updates the metrics of the original dimensions, but doesn't detect if a dimension (like campaign name) has changed. So if any dimension changes, it will detect it as a new one and add a new line instead of updating the old one.

The system cannot detect if there has been a change in the dimension's data or if it is really new data. So, any change in the dimensions will be inserted as new rows in the table.

Exceptions

Some dimensions CAN be updated as well as the metrics are updated, these are Status dimensions.

If you want these dimensions to be updated you must select the "Update special dimensions in upsert operation" option in the query configuration.
Once the option is selected the Upsert mode will update the Status dimensions that you have selected in the query as well as the metrics of the previously inserted data, in addition to adding the new data at the end of the table.

Update special dimensions in upsert section

What are the Status dimensions?

In the following table, you can see all the Status dimensions for the different connectors.

Status Dimensions in Google Ads

Google Ads

Campaign State
Serving Status
Ad group status
Keyword Status
Criterion serving status
Budget Status
Budget Usage
Ad Status
Ad Approval Status
Asset Group Status
Asset Status
Asset Approval Status
Asset Review Status
Search term Status
Call status
Audience Status
Bidding Strategy Status

Status Dimensions in Amazon Ads

Amazon Ads
Ad Keyword Status
Ad Group Status
Campaign Status

Status Dimensions in Apple Search Ads

Apple Search Ads
Campaign status
Campaign serving status
Campaign display status
Ad Group status
Ad Group serving status
Keyword status
Keyword display status
Creative Set display status
Creative Set status

Status Dimensions in Microsoft Advertising

Microsoft Advertising (Bing ads)
Account Status
Campaign Status
Budget Status
Budget Association Status
Ad Group Status
Ad Status
Keyword Status
Association Status

Status Dimensions in Criteo

Criteo
Campaign Status

Status Dimensions in DV360

DV360
Advertiser Status
Creative Status
Insertion order status
Line item status
Partner Status
Trueview iar parental status
Trueview parental status

Status Dimensions in DCM

DCM
Ad status
Activity delivery status
Click delivery status
Impression delivery status

Status Dimensions in Facebook Insights

Facebook Insights
Post status type
Video live status
Video post publish status

Status Dimensions in Facebook Ads

Facebook Ads
Account status
Campaign status
Campaign Configured Status
AdSet status
AdSet Configured status
Ad status
Ad Configured Status

Status Dimensions in Facebook Public Data

Facebook Public Data

Verification status

Status Dimensions in Google Ad Manager

Google Ad Manager
Order Status
Order is archived
Ad Unit status

Status Dimensions in Hubspot

Hubspot
Current State

Status Dimensions in Klaviyo

Klaviyo
Flow status
Campaign status

Status Dimensions in LinkedIn Ads

Linkedin Ads
Campaign Group Status
Campaign Status
Creative Status
Creative Review Status

Status Dimensions in Mailchimp

Mailchimp
Campaign Status
Delivery status enable
Delivery status name
Member status
Automation status

Status Dimensions in Pinterest

Pinterest
Campaign Status
Ad group status
Ad group summary status
Ad status
Ad summary status

Status Dimensions in Search Ads 360

Search Ads 360

Account Status
Engine Status
Campaign Status
Ad Group Status

Status Dimensions in Shopify

Shopify
Order Fulfillment Status

Status Dimensions in Snapchat

Snapchat

Account Status
Campaign Status
Ad squad Status
Ad Status
Ad review status
Creative Packaging Status
Creative Review Status
Creative Status
Media Status

Status Dimensions in Stripe

Stripe
Charge Status
Transaction Status
Payout status
Invoice Status
Subscription Status
Refund Status

Status Dimensions in TikTok Ads

TikTok Ads
Advertiser status
Campaign status
Campaign operation status
Ad group status
Ad group operation status
Ad status
Ad operation status
Page status
Playable Status

Status Dimensions in Twitter Ads

Twitter Ads
Funding Instrument Status
Campaign status
Ad Group Status
Tweet Status
Tweet Schedule Status

Status Dimensions in Twitter Insights

Twitter Insights (organic)
Tweet scheduled status

Status Dimensions in Woocommerce

Woocommerce

Orders status
Products status
Products stock status

Status Dimensions in YouTube

Youtube
Subscribed Status
Video Privacy

 

As always, please contact us via our live chat on our website or via email if you still have doubts or questions. We are happy to help!