Creating and maintaining a custom advertising budget for ROAS

Nic Zangre Updated by Nic Zangre

This article is for CaliberMind customers wishing to deploy "non-standard" digital Platforms.

Standard ads platforms include the most popular platforms with well-established APIs. For an integration to be considered standard it must be supported by our ETL provider, Fivetran. If Fivetran doesn't have a given connector in it's library we'd consider it non-standard or custom.

Out-of-the-Box Supported vs Non-standard ads integrations

Standardized Ads Platforms

  1. Google Ads
  2. Facebook
  3. LinkedIn

Non-Standard Platforms

  1. Boutique Ads Platforms
    1. https://www.buysellads.com/
    2. https://pontiac.media/
  2. Influencers
  3. Affiliates and Content Syndication vendors
  4. TV & Billboards

How ads cost aggregation works:

We export ad spend data from APIs like Google Ads in this format:

Day

Platform

Campaign Name

Campaign ID

Ad Name

Ad ID

Impressions

Cost

2020-01-01

Google Ads

ABM Webinar

123

Blue CTA

ABC

1,000

$250

2020-01-01

Google Ads

ABM Webinar

123

Red CTA

ABD

500

$150

2020-01-02

Google Ads

ABM Webinar

123

Blue CTA

ABC

2.000

$300

2020-01-02

Google Ads

ABM White paper

124

White CTA

XYZ

5,000

$500

Each row of data forms a three part key we can use to match to URL parameters

ad id &
campaign id &
day (date of spend / date of page view)

So if a url streams into CaliberMind we can match it, to person, and later to attribution to calculate Return on Ad Spend:

company.com/webinar-signup?utm_source=google&utm_medium=display&campaign_id=123&ad_id=ABC

How non-standard ads cost aggregation works:

First you'll need to decide the method to get us the Ad Spend via a flat file. Here are some options:

- SFTP

- Spreadsheet Tab in Google Sheet

- Amazon S3 File Bucket

Once you have a process in place to capture the ad spend, you'll want to set up the format in your file. If possible, provide us all the standard parameters as your column headers:

Day, Platform, Campaign Name, Campaign ID, Ad Name, Ad ID, Impressions, Cost, URL

A "nice-to-have" is also to include a column(s) containing a URL example contain the landing path and UTM parameters. This will help QA on both sides-- in setup and reporting.

A few tips to ensure continuity of reporting:

  1. Don't sort the spreadsheet as it can break the column header names
  2. Only append data to the bottom of the spreadsheet if using Google Sheets
  3. You can either use a DAY column and update the data weekly with adding all rows OR update the spreadsheet at the beginning and end of the month to "prorate" the spend. Then manually update it at the end of month with the actuals
  4. Dates should be formatted like: YYYY-MM-DD
    1. Option 1: Date column header should be "DAY" -- each row is an AD+CAMPAIGN+DAY combo** recommended, most exact.
    2. Option 2: Date column header should be "DAY" -- each row is an CAMPAIGN+DAY combo, with exact spend, but no ad-level granularity
    3. Option 3: Date column header should be "Month" -- each row is an AD+CAMPAIGN+MONTH combo** (use same date format, ex) "2021-01-01" = January 2021. Spend is pro-rated for each ad for each day of the month.
    4. Option 4: Date column header should be "Month" -- each row is an CAMPAIGN+MONTH combo** (use same date format, ex) "2021-01-01" = January 2021. Spend is pro-rated for each campaign for each day of the month.

Example: Google Sheets Connection

When there isn't a native connection available inside of CaliberMind, data from a custom Ad Platform(s) can be imported directly into the CaliberMind system by connecting a Google Sheet. The Google Sheet template available here can be used as an example of how to setup the Google Sheet prior to import.

At a minimum, the column headers and campaign data required is noted below.

Required Columns:
  • Campaign Name: Unique name for the campaign - this will be the same UTM "campaign" Parameter that is within the URL
  • Campaign ID: Unique ID for the campaign (numeric ID preferred) - this will be the same UTM "campaign_id" Parameter that is within the URL
  • Campaign Type: The primary tactic used in a campaign (see KB Article available here)
  • Day (or Month): The single day (or month) that the campaign was active - formatted as "YYY-MM-DD"
  • Cost: The actual cost of the campaign on that specific day (do not include currency symbols in the values input)
  • Clicks: The actual number of clicks received from the campaign on that specific day
  • Impressions: The actual number of impressions from the campaign on that specific day
Named Range

When connecting a Google Sheet to CaliberMind, one of the requirements is to select and define the "Named Range". In order to account for additional rows being appended to the worksheet in the future, we don't necessarily want to specify row numbers. To account for this, in the template above, the range is defined as "A:H".

*NOTE: Sorting the data by any of the field headers IS NOT recommended or required - simply append any new data to the current worksheet

When you're ready with the Ad Spend data and the URL setup in your ad platform, contact you Customer Success Manager to finalize your implementation!

How did we do?

Channel ROI Overview

Best Practices for Tracking Return-on-Ad-Spend (ROAS)

Contact