An Example Conversion Upload Script for a Google Sheet to upload conversions to AdStage

_README.md

Example Custom Conversion Upload

This example script takes a Google Spreadsheet that is visible to anybody with the URL (example here: https://docs.google.com/spreadsheets/d/1D2_QWqMkELL2H-iSSi53VXJfGHoz9VXVqexRS4T1t4M/edit?usp=sharing), downloads it, converts to AdStage conversion format, then uploads to the AdStage custom conversion endpoint (as documented here: http://docs.adstageapi.apiary.io/#reference/0/custom-conversions/step-2.-post-data)

1. To run this script, save this file as conversion_upload_script.rb
2. Edit the variables

• ADSTAGE_ORG must be set to your Organization number
• ADSTAGE_DESCRIPTOR_ID is set to the descriptor id (the process to generate a descriptor id is described here: http://docs.adstageapi.apiary.io/#reference/0/custom-conversions/step-1.-create-a-custom-metric)
• ADSTAGE_TOKEN is set to your token
• SHEETS_URL is set to the sharing url for your Google Sheet

3. Run the script:

/home/David/Downloads $ ruby conversion_upload_script.rb 

conversion_upload_script.rb

require 'csv'
require 'open-uri'
require 'json/ext'
require 'uri'
require 'net/http'

ADSTAGE_API = 'https://platform.adstage.io/api'
# This is AdStage's test organization, your id will be different:
ADSTAGE_ORG = 40
# Just the number in the id you get back creating the Column:
# custom_conversions:14:conversions
# This is an example on AdStage's test organization, your id will be different:
ADSTAGE_DESCRIPTOR_ID = 14 
# You can get the token from AdStage support: 
ADSTAGE_TOKEN = 'FILL_ME_IN'

# Note:
# We can only pull the first tab as a file from Google Sheets
#
# For this to work without requesting Google Drive access and making the script a lot more complicated,
# the sheet must be set with sharing settings: "visible to anyone with the link".
SHEETS_URL = 'https://docs.google.com/spreadsheets/d/1D2_QWqMkELL2H-iSSi53VXJfGHoz9VXVqexRS4T1t4M/edit#gid=1328673339'


headers = {
  'Content-Type' => 'application/json',
  'Authorization' => "Bearer #{ADSTAGE_TOKEN}",
  'Accept' => 'application/json'
}

# This takes a single line from the sheet and turns into the expected format for the 
# custom conversions endpoint
def format_conversion(line, conversion_name = 'Conversion', conversion_value = 'Value')
  entity_id = line['Entity ID']
  timestamp = line['Date']
  attribution = line.fetch(conversion_name, 0).to_f
  ret = {timestamp: timestamp, attribution: {entity_id => attribution}}
  ret[:value] = line['Value'].to_f if line['Value']
  ret
end

# This takes a Google Sheets CSV Export URL, downloads the CSV, 
# turns rows into conversions via the #format_conversion function,
# And returns a blob of JSON format data to send to AdStage
def read_conversions(url)
  output = []
  dates = []
  CSV.new(open(url), :headers => :first_row).each do |line|
    dates << line.to_hash['Date']
    output << format_conversion(line.to_hash)
  end
  dates = dates.map{|d| Time.parse(d) }
  date_min = dates.min.strftime('%F')
  date_max = dates.max.strftime('%F')
  {conversions: output, date_range: "#{date_min}..#{date_max}"}.to_json
end



# Here's where we convert the sheets URL export CSV for easy parsing:
sheets_url = SHEETS_URL.split('#').first.gsub(/\/(edit|)(\?usp=sharing|)\/?$/, "/export?format=csv")
# Convert the conversions to JSON:
values = read_conversions(sheets_url)
# Output JSON so we can see what will be sent:
puts "Conversion data:"
puts "-----"
puts values
puts "-----"

conversion_url = "#{ADSTAGE_API}/organizations/#{ADSTAGE_ORG}/metric_descriptors/#{ADSTAGE_DESCRIPTOR_ID}/conversions"
uri = URI.parse(conversion_url)
http = Net::HTTP.new(uri.host, uri.port)
http.use_ssl = true

request = Net::HTTP::Post.new(uri.request_uri, headers)

request.body = values
puts "Uploading conversions to: #{conversion_url}"
puts "Result: " + http.request(request).inspect