Tie offline conversions and revenue to your ad spend through Google Ads

On this page

Many of our clients have conversions that happen at partner sites, via a phone call, or outside of the browser.  This conversion data is often stored in tables with no easy way to measure the effectiveness of the Google ad that initialized the conversion.  

Sometimes, an ad doesn't lead directly to a sale or conversion on your site or in your app, but instead starts a customer down a path that ultimately leads to a conversion event not captured immediately after the ad was clicked.

For example, you might want to import data for closed sales deals that initially entered your CRM system through a search ad on Google.  You might have an instance where the final sale isn’t completed within an app or browser and you want to send Google the conversion once the total has been finalized.  You also could have partner sites where you send your customer to complete a conversion on that site, such as opening a recommended credit card, and you receive the conversion via a server transaction from your partner.  

Some clients offer a trial that lasts 7 days with a small initial revenue value.  Once the customer converts to a paid plan, they need to send the full conversion value into Google to properly represent the final conversion.  Another option might be a sale that starts on the web, but completes on the phone.  That final conversion value is often stored but cannot be easily attributed to the initial Google ad.  

Segment Reverse ETL (rETL) can eliminate manual uploads and help you correctly attribute conversions back to the source.  You’ll learn how to unlock conversion data stored in a table and stream data directly into Google as well as Segment where it can become actionable and help you get a better picture of return on ad spend and first touch attribution.

Step 1: Set up a conversion action within Google to import your conversion data

For guidance in this step, this Google Support answer will help.

Step 2: Set up Snowflake as a source for rETL

• In your Segment workspace, navigate to Reverse ETL -> Sources and click Add Source. 

• Select Snowflake and then name your source on the Basic Settings tab.

• Have your Snowflake Account ID, Database Name, Warehouse, Username, and Password availableand follow the instructions on the Connection Settings tab to connect your Snowflake source.

Step 3: Build a model to pull data from your Snowflake table

• In your Segment workspace, under Reverse ETL -> Sources, click into your newly created Snowflake source and select Add Model -> SQL Editor.

• Create your SQL query to pull the data from your Snowflake table.  An example query for this model would look like this: SELECT *transactionId, orderDate, gclid, email, order_id, value, currency FROM  ACME_MDWHO.ACME_PRODUCTION.OFFLINE_CONVERSIONS

• Enter a unique identifier column (in this example, it's the transactionId) *or if you don’t have a unique column in the table, remove the transactionId from the query and add this at the end of your select parameters in your SQL statement: ROW_NUMBER() OVER (ORDER BY order_id) AS id Then, if you are using the ROW_NUMBER option, add this to the end of your SQL Statement: ORDER BY id, orderDate, gclid, email, order_id, value, currency Then use id as the unique identifier column in the box below the query.  The example below also includes braid and wbraid as options
• Preview the results and make sure there are no errors.

• Enter a name for the model and set how frequently you would like it to be performed.

Step 4: Setup Google Ads Conversions as a destination and map the table data as Upload Click Conversions

• In your Segment workspace, navigate to Reverse ETL -> Destinations and select Add Destination.

• Select Google Ads conversions and connect it to the Snowflake source.

• Click Sign in with Google and sign into the Google Ads account where you created the conversion action in Step 1.

• Name the Conversion and follow the instructions to add your Conversion ID and Customer ID.

• Enable the destination and Save Changes.

• Navigate to the Mappings tab of the Google Ads destination and select Add Mapping. 

• Select your model at the top, and then select the Upload Click Conversion mapping.

   

• Select Added records in section 1, choose a test record in section 2, toggle “Show test record preview” to the on position in section 3 and then map each of the columns to the relevant Google Ads fields.
• For the Conversion Action ID mapping, enter the ID of the conversion action associated with this conversion. To find the Conversion Action ID, click on your conversion in Google Ads and get the value for ctId in the URL. For example, if the URL is https://ads.google.com/aw/conversions/detail?ocid=00000000&ctId=570000000, your Conversion Action ID is 570000000.
• For the Conversion Timestamp, we will map the orderDate.
• For the GCLID, we will map the gclid.  Please note that GBRAID and WBRAID entries are for iOS14+ app measurement in place of the GCLID.  In this recipe we will be using GCLID but you can also map GBRAID and WBRAID if you have that available in your table.
• For this recipe, we will also map email, order_id, value, and currency.  There are additional mapping elements available for your convenience.
• Once mapping is complete in sections 1-3, click Test Mapping in section 4.  If there are any issues with your mapping, you will receive the information you need to correct the issues appearing within section 4, Send test record, above the Test Mapping button. Once your test is successful, click Create Mapping.
• Test the mapping and once you confirm it’s working, click Save.
• Toggle Mapping State to Enabled to enable the mapping.

Wrapping up

Here’s what we’ve done in this growth recipe:

• Reviewed Google Ads Attribution topics

• Connected to our table in Snowflake

• Mapped the data from our table to a Google Click Conversion