Hopkins

How to create, update & schedule Google Ads Customer Match Audiences based on MailChimp lists

Updated 6.11.2024

Web analytics

Do you update your Google Ads Customer Match Audiences manually with csv uploads? In this blog post, Panu Kuuluvainen shows you an easy, scheduled and low cost way to automate the process using standard tools.

In order to update your remarketing lists for maximum impact, you need to act fast. Using GA events is a great way to achieve speed but this comes at the cost of precision, as identifying leads is cookie based. Using email and mobile number based remarketing lists simultaneously with cookie based lists gives you the best results.

How to automatically update your Google Ads audience with emails from your CRM

Caveat: in order to update Google Ads audiences automatically, you will need access to the Google Ads API and some basic skills in Python. This guide will show you how to run the updates. To access Google Ads API talk to the coolest person in your organisation or refer to this guide.

In this example I will help you build a MailChimp / Zapier / Google Ads Audience integration. However, please note that you can use almost any CRM as long as there is a way to get your emails into Google Sheets. Zapier supports sending contacts to Google Sheets with Active Campaign, Salesforce and many others.

Alternatively, you can probably poll your CRM using its API and fetch emails directly via Python. However, Zapier is an easy and maintainable way to achieve the same result.

Set up your Zapier to feed emails to Google Sheets

  1. Head to google sheets and create a spreadsheet, titlted “Mailchimp emails”. Copy the sheet id from URL  somewhere (it is in the URL: docs.google.com/spreadsheets/d/1xAx4xOxcx4xzxq_PxfxgxXxlZxoxrxKx9xBx-xMx2Xs/edit?usp=sharing).
  2. Give the spreadsheet a header row with Email in A1
  3. Login to Zapier and select “Mailchimp” and “Google Sheets” from the app explorer
  4. Select “Save new MailChimp subscribers to a Google Sheets spreadsheet”.
  5. Connect your MailChimp account and select the appropriate MailChimp list.
  6. Connect Google Sheets and select the sheet you just created. When you select “Sheet 1”, your header should appear in Zapier.

Select “Emails” to feed under “Emails” header, save and turn on your Zap. Your MailChimp subscribers should now feed to the spreadsheet you just created. This is quite easy to verify, simply by adding yourself to the list.

Using Supermetrics?

Supermetrics provides a simpler process to export emails from MailChimp.  Open Supermetrics sidebar and select the following:

  1. Data source: Mailchimp
  2. Range: Today
  3. Split by (rows): member_email
  4. Do not select a metric. On SupermetricsQueries sheet your Dimensions should be [“member_email”] and Metrics [].

Pull emails from Google Drive Spreadsheet using Python

I will not dwell too much on how this is done but a full guide can be found at https://developers.google.com/sheets/api/quickstart/python should you need it.

If you haven’t already, open CMD/Terminal/Bash

pip install google-api-python-client
pip install googleads

Next, create the necessary authentication to communicate with your spreadsheet.

  1. Use the wizard at https://console.developers.google.com/flows/enableapi?apiid=sheets.googleapis.com&pli=1 to create a project, for example “MailChimp to Google Ads”.
  2. On the Add credentials to your project page, click the Cancel button. Select “OAuth consent screen”.
  3. Set email address and product name. Click on save.
  4. Select the Credentials tab, click the Create credentials button and select OAuth client ID.
  5. Select other and give your project a name “MailChimp to Google Ads Spreadsheet App”
  6. Download the JSON file. Store it in where you intend to keep your Python script and save it as ‘client_secret.json’

 

Code sample for pulling data from Drive Spreadsheet via Python

The authentication bit is 100% from Google documentation as is most of the other code as well, slightly modified for the purpose. There are 3 things in this bit.

Store the following code as sheets.py.

[sourcecode language=”python”]
import os
from oauth2client.file import Storage
from oauth2client import client
from oauth2client import tools
from apiclient import discovery
import httplib2

SCOPES = ‘https://www.googleapis.com/auth/spreadsheets’
CLIENT_SECRET_FILE = ‘client_secret.json’ # rename to our client_secret.json file
APPLICATION_NAME = ‘MailChimp to Google Ads’ # rename to your application name
spreadsheetId = ‘1xAx4xOxcx4xzxq_PxfxgxXxlZxoxrxKx9xBx-xMx2Xs’ # change to your spreadsheet id

try:
import argparse
flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args()
except ImportError:
flags = None

def get_credentials():
"""Gets valid user credentials from storage.

If nothing has been stored, or if the stored credentials are invalid,
the OAuth2 flow is completed to obtain the new credentials.

Returns:
Credentials, the obtained credential.
"""

home_dir = os.path.expanduser(‘~’)
credential_dir = os.path.join(home_dir, ‘.credentials’)
if not os.path.exists(credential_dir):
os.makedirs(credential_dir)
credential_path = os.path.join(credential_dir,
‘sheets.googleapis.com-python-mailchimp-adwords.json’)

store = Storage(credential_path)
credentials = store.get()
if not credentials or credentials.invalid:
flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
flow.user_agent = APPLICATION_NAME
if flags:
credentials = tools.run_flow(flow, store, flags)
else: # Needed only for compatibility with Python 2.6
credentials = tools.run(flow, store)
print(‘Storing credentials to ‘ + credential_path)

return credentials

def pullEmails():
""" poll data from your mailchimp spreadhseet
returns a list of emails """
credentials = get_credentials()
http = credentials.authorize(httplib2.Http())
discoveryUrl = (‘https://sheets.googleapis.com/$discovery/rest?’
‘version=v4’)
service = discovery.build(‘sheets’, ‘v4’, http=http,
discoveryServiceUrl=discoveryUrl)

rangeName = ‘Sheet1!A2:A’
result = service.spreadsheets().values().get(
spreadsheetId=spreadsheetId, range=rangeName).execute()
values = result.get(‘values’, [])

emails = []
if not values:
print(‘No data found.’)
emails.append(‘No data found.’)
else:
print(‘Email:’)
for row in values:
# Print columns A and E, which correspond to indices 0 and 4.
print(‘%s’ % (row[0]))
emails.append(row[0])

return emails

def cleanSheet():
""" cleans the spreadsheet so you don’t pull same values twice. """
credentials = get_credentials()
http = credentials.authorize(httplib2.Http())
discoveryUrl = (‘https://sheets.googleapis.com/$discovery/rest?’
‘version=v4’)
service = discovery.build(‘sheets’, ‘v4’, http=http,
discoveryServiceUrl=discoveryUrl)

clear_values_request_body = {}

rangeName = ‘Sheet1!A2:A’

request = service.spreadsheets().values().clear(spreadsheetId=spreadsheetId, range=rangeName,
body=clear_values_request_body)
response = request.execute()

return response

[/sourcecode]

Automatically send a list of emails to Google Ads Audiences using Google Ads API

To communicate with Google Ads API, you need an API token and a yml file. Hopefully someone in your organisation has these. A guide:
https://developers.google.com/adwords/api/docs/guides/first-api-call.

Your yml file will look like this.

[sourcecode language=”python”]
adwords:
developer_token: you can find this under Adwords, Settings, Api Acess in your MCC account
client_id: see https://developers.google.com/adwords/api/docs/guides/first-api-call
client_secret: see https://developers.google.com/adwords/api/docs/guides/first-api-call
refresh_token: see https://developers.google.com/adwords/api/docs/guides/first-api-call
client_customer_id: the client ID of the AdWords account containing the audience you wish to update.

[/sourcecode]

Once you do have access and a yml file called adwords-api.yml in the same folder as your Python script, you can run the following to update your Google Ads audience.

adEmailsList() takes an Google Ads client, a list of emails and a user_list_id (ie. your Google Ads Audience id) and pushes the emails to said audience, returning the upload status. Save this bit as awords.py

[sourcecode language=”python”]
import hashlib

def adEmailsList(client, emails, user_list_id):
user_list_service = client.GetService(‘AdwordsUserListService’, ‘v201708’)

members = [{‘hashedEmail’: NormalizeAndSHA256(email)} for email in emails]

mutate_members_operation = {
‘operand’: {
‘userListId’: user_list_id,
‘membersList’: members
},
‘operator’: ‘ADD’
}

response = user_list_service.mutateMembers([mutate_members_operation])

if ‘userLists’ in response:
for user_list in response[‘userLists’]:
print (‘%d users sucesffully added to list "%s" and ID "%d".’
% (len(emails), user_list[‘name’], user_list[‘id’]))

return response

def NormalizeAndSHA256(s):
"""Normalizes (lowercase, remove whitespace) and hashes a string with SHA-256.

Args:
s: The string to perform this operation on.

Returns:
A normalized and SHA-256 hashed string.
"""
return hashlib.sha256(s.strip().lower()).hexdigest()

[/sourcecode]

Pull it all together

All that remains is to add a main.py that calls adwords.py and sheets.py.  Schedule this to run every 12 hours or every 15 minutes, depending on your needs. In my next post, I will demonstrate how you can do the same with Facebook, using Facebook API.

[sourcecode language=”python”]
from sheets import pullEmails, cleanSheet
from googleads import adwords
from adwords import adEmailsList
import sys

# pull emails from spreadsheet and store in emails

emails = pullEmails()
print emails

if emails[0] == ‘No data found.’:
sys.exit(‘No emails in spreadsheet, terminate.’)

# clean emails from spreadsheet after polling

resp = cleanSheet()
print resp

adwordsAudienceId = 500000001 # to get this, go to AdWords, Shared Libraries, Audiences and open your Audience.

# initiate API client.

adwords_client = adwords.AdWordsClient.LoadFromStorage(‘adwords-api.yml’)

# push data to Adwords using adwords.py

resp = adEmailsList(adwords_client, emails, adwordsAudienceId)

try:
print (‘Upload status : ‘ + resp[0][0][‘dataUploadResult’][‘uploadStatus’])
except Exception as e:
sys.exit(e)
[/sourcecode]

Now your integration should be ready to go! If you have any questions, feel free to comment below and I’ll do my best to assist you.

Do you need help in implementing advanced Google Ads campaigns? Contact us for more information!

Show all articles

Interested in this topic?

Call us 020 788 8120 or fill out the form below and let's continue the conversation!