Import offline conversions to Bing Ads automatically (Full script solution)

This post will show you how to setup the complete process automation to bring offline conversions to your Microsoft Advertising account, just by using Google Ads Scripts and Google Sheets. In other words, you need no developer skills to solve this automation task and all required tools are completely free.

What are offline conversions?

You easily record your customers’ orders and actions on the site with traditional conversion tracking methods. However, what about their actions outside of your site? The purchases they complete by paying at a point of sale? Offline conversions come to our rescue here. With offline conversions, you can link customers’ actions on the site with their offline actions, and have conversions that occur offline also be factored in.

Importing offline conversions

Imagine collecting offline conversions in Google Ads. When you want to see these conversions in Microsoft Advertising, you’ll need to import them to an extent. Fortunately, Microsoft Advertising makes it easy for us and offers an import feature. However, what if you need to do this on a regular basis, not just once? In fact, you’ll need it in many cases. Taking a few hours a day to import your daily offline conversions, exporting these conversions from Google Ads, formatting your file, and uploading it to Microsoft Advertising will be a laborious and time-consuming activity. No need to worry: You can automate this with our solution.

Setting up Google Analytics

When you want to add your offline conversions to Microsoft Advertising, the columns you will need Microsoft Click ID, Date/Time, Offline Conversion Name, and Offline Conversion Value. So make sure you add the Microsoft Click ID as Custom Dimension to Google Analytics and store it. If you aren’t collecting Microsoft Click IDs on your Google Analytics already, you can follow the guide below to start.

Create custom dimension to store Click ID

Create new custom dimension on Google Analytics.

1. Visit **Google Analytics home page**.
2. Click “Configure” on the left panel.
3. Select the “Custom definitions” menu item.
4. Create new custom dimension for Microsoft Click ID.
5. Type User Property/Parameter to use it on Google Tag Manager (“dim_msclkid” > for example)
6. Note your User Property/Parameter to use it on next steps.

Finally, your new custom dimension should look as below:

Google Analytics interface.

Create variable on Google Tag Manager

Go to Google Tag Manager to start making necessary adjustments.

1. Navigate to “Variables” on the left panel.
2. Click “New” to create new variable to store Microsoft Click ID.
3. Choose “URL” as variable type.
4. Choose “Query” as component type.
5. Type “msclkid” for query key.
6. Name your variable and save it. (“Microsoft Click ID” for example)

Finally, your new variable should look as below:

Google Tag Manager configuration.

Now that variable is ready, we should create new tag to send this URL query to the Google Analytics as custom dimension.

Create tag on Google Tag Manager

Follow steps below, if you don’t have any GA4 Configuration tag already. If you have one, you can skip creating new tag and simply start from 5th list item below.

1. Navigate to “Tags” on the left panel.
2. Click “New” to create new tag.
3. Click “Tag Configuration” panel and select “Google Analytics: GA4 Configuration” (Assuming you are using GA4)
4. Type your Google Analytics tracking ID for measurement ID field.
5. Click on the “User Properties” and click “Add Row” to add new user property.
6. For the property name field, place your “User Property/Parameter” name you created before on Google Analytics (“dim_msclkid” for example)
7. For the value field, click the plus button next to the textbox and select the variable you created before for Microsoft Ciick ID (“Microsoft Click ID” for example)
8. Click the “Triggering” panel below and choose the trigger you need. (“All pages” for example)
9. Name your new tag, and save it.

Finally, your new tag should look as below :

Tag configuration.

Remember, you may want to edit your tag settings according to your needs.

Automating Microsoft Advertising offline conversions uploads

Create Google Sheet

Go to Google Sheets and create a new empty sheet which will store your reports in next steps.

Create new sheet in your document, and name it “Export” as below.

After you create “Export” sheet, copy URL on the address bar of your browser and store your <SpreadSheet ID> and <‘Export’ sheet ID> somewhere. Next steps, you’ll need that. URL should look something as below:

Google Ads script for fetching and storing conversion reports

Thanks to Google Ads Scripts, we can easily and effortlessly fetch our Google Analytics reports with the help of a few lines of code. That is, the first thing we’ll need is a Google Ads Script that will bring us our Google Analytics reports.

Important: Remember editing the parameters on script:

This script simply works like this:

1. It fetches the Google Analytics report with the metrics and dimensions we specified.

2. Format this report as required in Microsoft Advertising and save it daily in Google Sheet.

Schedule your script to run on daily basis.

Important: Make sure that the script will run each day BEFORE Microsoft Advertising’s upload process. To make sure of it, you can simply schedule this script to run 1 or 2 hours before Bing Ads Scheduled upload.

Create offline conversion goals on Microsoft Advertising if they don’t exist

The conversions in the file to be uploaded must also be included in Microsoft Advertising. If you have missing conversion goals, be sure to visit the conversion goals page and create them.

Setting up Microsoft Advertising offline conversions scheduled upload

Visit the offline conversions page in Bing Ads and switch to the “Schedule” tab. Start creating a new schedule by clicking the “+Schedule” button.

Set necessary fields before saving.

Important :

• Make sure URL for your file is as below and replace <SpreadSheet ID> and <‘Export’ Sheet ID> with the IDs you stored previous steps.
• docs.google.com/spreadsheets/d/**<SpreadSheet ID>**/export?format=csv#gid=**<’Export’ Sheet ID>**
• Example:docs.google.com/spreadsheets/d/1NiTkXD1FxYwjDADcGLyr8GMozzNR7z0vA-5fG78pAvM/export?format=csv#gid=1936801127
• Make sure Google Sheet document is publicly accessible.
• Make sure your upload frequency time is later then your Google Ads Script’s frequency. At least 1–2 hours, because Google won’t promise that script will run exactly on the desired hour.

Congratulations! You automated your Bing offline conversion reports

Once all is done, you’ll now be able to automatically export your Google Analytics offline conversions to Microsoft Advertising. On a daily basis, this process will be completed at the times you specify. Remember, the data for the previous day will be loaded each day.

The whole process will work as below:

Are you interested in more PPC Automations like that? Feel free to schedule a meeting with us to talk about your use case.

This story was originally published on PEMAVOR.