[Adwords Script] Automate Account Management with Trello

Keeping on top of campaign management in Adwords can be a struggle, especially if you’re managing several accounts in-house or at an agency.

The vast majority of PPC managers don’t have any sort of checklist or workflow to make sure they’re always keeping on top of things, resulting in lost optimisation opportunities.

At Clicteq, we wanted to make sure that every PPC manager has their own tailored process so they can squeeze every last drop of performance out of the accounts that they manage.

So we built this script that integrates Adwords with Trello (free version) and allows you to create repeating tasks for each account that you manage.

You can set up the script so that, on a weekly basis, it produces a little card containing the client name, a list of predefined tasks that need to be completed, and the deadline of each of the tasks, to remind you of which things need to get around quickly and ensuring that nothing ever gets dropped.

The script is completely customisable, and you can change everything from the card name, the items in the checklist, the date the task is due, and the person that it’s been assigned to.

Here’s an example of the card that it outputs on a weekly basis:

You can create your own personal checklists, and then schedule the script to add a card either daily, weekly or monthly. Here’s an example of a checklist that the script creates:

Script settings

  • Board ID: To find this, go to the board in which you want to create cards, and then add .json onto the end of the URL as so: https://trello.com/b/Yed68oFa/wes-to-do-lists.json. It will then show you the ID at the beginning of the first line. Copy this ID and paste it between the quotation marks for the BOARDID. I.e. BOARDID = ‘{Board ID here}’  
  • API key: To use Trello’s API, you need an API key. The API key can be found here: Trello Developer API key. Simply copy the key and insert it between the quotation marks for KEY = ”
  • API Token: You will also need a token to go with your API key. This can be found here: Trello Token Generator. You will have to click to ‘Authorize Trello’ to be able to use the token on your account. Once you’ve got your token, copy it and paste it between the quotation marks for TOKEN = ”
  • List name: This is what you want your checklist to be called (which will be composed of different cards), e.g. ‘to-do list’. Type your list name into the quotation marks, e.g. LISTNAME = ”
  • Card name: This is what you want your cards inside your list to be called (i.e. the task to be completed), e.g. CARDNAME = ‘review search terms report’
  • Due date: This field ads a due-date to your card. Specify each card’s due-date by entering the number of whole days you have to complete the task, e.g. for a task that needs to be completed within 2 days, DUEDATE = 2
  • Assign to a user: To assign the card task to someone, enter their username in between the quotation marks. To find an individual’s username, click on their photo at the top of the board, and copy the username below their name, e.g. ‘@wes1’. Paste this into the quotation marks for USERNAME  = ” (or leave blank if you don’t want to assign it to anyone)
  • Specify items that will be added to the new checklist: Add tasks to a checklist. For example, [‘review search term report’,’review display placements’]
/**
*
* 
*
* 
* 
*
* Version: 1.0
* maintained by Clicteq
*
**/

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

//Options

//Specify board id
BOARDID = ''

//Specify API key 
KEY = ''

//Specify API token
TOKEN = ''

//Select the list where the card should be created, for example 'to do'
LISTNAME = ''

//Specify the card's name, for example 'email client'
CARDNAME = ''

//Specify the card's due date, x days from today (integer)
DUEDATE = 4

//Assign the card to a user, specify username, leave blank ('') to assign to nobody
USERNAME = ''

//Specify items that will be added to the newly created checklist, for example ['x','y','z'], leave blank not to add any checkitems
CHECKITEMS = ['review search terms']

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//

function request(url, method)
{
 var response = UrlFetchApp.fetch(url, {'method' : method}).getContentText();
 var  data = JSON.parse(response)
 
 return data
}

function authenticate(url)
{
  
 var key = KEY
 var token = TOKEN
 
 url = url.replace('%k', key)
 url = url.replace('%t', token)
 url = url.replace('%b', BOARDID)
  
  return url
}  

function findList(listName)
{var urlList = 'https://api.trello.com/1/boards/%b?actions=all&boardStars=none&cards=none&card_pluginData=false&checklists=none&customFields=false&fields=name%2Cdesc%2CdescData%2Cclosed%2CidOrganization%2Cpinned%2Curl%2CshortUrl%2Cprefs%2ClabelNames&lists=open&members=none&memberships=none&membersInvited=none&membersInvited_fields=all&pluginData=false&organization=false&organization_pluginData=false&myPrefs=false&tags=false&key=%k&token=%t'
urlList = authenticate(urlList)

var lists = request(urlList, 'get')['lists']
  
  for(var list in lists)
  {if(lists[list]['name'] == listName)
  {
    var listId = lists[list]['id']
  }
  }  
 
return listId
}

function addCard(cardName, dueDateFormated, listId, memberId)
{
  var urlCard = 'https://api.trello.com/1/cards?name=' +cardName+ '&due=' +dueDateFormated+ '&idList=' +listId+ '&keepFromSource=all&key=%k&token=%t&idMembers='+memberId
urlCard = authenticate(urlCard)
var cardId = request(urlCard, 'post')['id']

return cardId
}

function addChecklist(cardId)
{
  var urlChecklist = 'https://api.trello.com/1/checklists?idCard='+cardId+'&name=checklist_name&key=%k&token=%t'
urlChecklist = authenticate(urlChecklist)
var checklistId = request(urlChecklist, 'post')['id']

return checklistId
}

function addCheckItems(checkItems, checklistId)
{
  
var urlCheckItem = 'https://api.trello.com/1/checklists/'+checklistId+'/checkItems?name=%s&pos=bottom&checked=false&token=%t&key=%k'
urlCheckItem = authenticate(urlCheckItem)

for(var checkItem in checkItems)
{
 request(urlCheckItem.replace('%s', checkItems[checkItem]), method = 'post') 
}

}

function findMember(userName)
{
  
  var urlMembers = 'https://api.trello.com/1/boards/%b?members=all&token=%t&key=%k'
  urlMembers = authenticate(urlMembers)
  var members = request(urlMembers, 'get')['members']

    for(var member in members)
    {if(members[member]['username'] == userName)
    {
      var memberId = members[member]['id']
    }
    }  

  return memberId

}

function main() {
  
 var dueDateFormated = new Date(new Date().getTime() + (DUEDATE * 1000 * 60 * 60 * 24))
 dueDateFormated = Utilities.formatDate(dueDateFormated, 'GMT', 'Y-M-d')
  
  if(USERNAME != '')
  {
  var memberId = findMember(USERNAME)
  }
  else
  {
    memberId = ''
  }
  var listId = findList(LISTNAME)
  var cardId = addCard(CARDNAME, dueDateFormated, listId, memberId)
  if(CHECKITEMS.length > 0)
  {
  var checklistId = addChecklist(cardId)
  addCheckItems(CHECKITEMS, checklistId)
  }

}

 

wesley parker
About wesley parker

Wesley is Founder and CEO at Clicteq. He currently manages a £6 Mil Adwords portfolio across a range of different sectors. He regulally features in leading search publications such as Search Engine Journal, Econsultancy and Certified Knowledge. You can follow him on Twitter or connect with him on Linkedin

Leave a Reply

Your email address will not be published. Required fields are marked *