This article will help you use the Google Sheets AdStage Add-on, specifically for Google Ads Direct data. By design, below is a collection of parameters that you can add to functions.
For information about the installation of the add-on and sample templates go here.
Getting Started
The first thing to do when getting started with Google Ads Direct is taking a look at the available metrics here:
Pay special attention to the ID (use this) column. Copy and paste those fields exactly into your formula or sheet. Capitalization and spacing matters. For example, when making an Google Ads Direct call and looking to see spend make sure to write Cost, or to see account name make sure to write AccountDescriptiveName.
To start off you'll need two formulas. The first is AdStage's cross-network formula, the second is Google Ads Direct. We'll use the first formula to help us target the proper account in the Google Ads Direct formula.
Step 1: Create a formula to call in your Account ID's, also known as Entity ID's. Place this formula next to the space you want your performance data. A best practice is far off to the right, potentially cell 02.
=accounts("last week", {"name", "entity_id", "spend"},{"networks","adwords"})This formula will gather a list of your entity ID's which your next formula will target.
Note: the date range and spend are needed in the formula to gather your Entity ID's, but won't provide much value for you, as your main performance data table will be built from your next formula.
Step 2: Create your main formula. Typically this formula is started in the main view of a sheet, somewhere around cell A2.
To ensure your data is pulled from the Google Ads endpoint you need to add {"provider", "adwords"}, {"targets", P2}
This addition tells the formula to pull from the Google Ads endpoint, {"provider", "adwords"}, and target/use the account in cell P2, {"targets", P2}.
P2 should reference where your Entity ID is for the account you'd like to target. If you set up you first formula in 02, the Entity ID would be found in P2.
=accounts("last week", {"AccountDescriptiveName", "Cost"}, {"provider", "adwords"}, {"sort by", "Cost"}, {"targets", P2})
=campaigns("last week", {"CampaignName", "Cost"}, {"provider", "adwords"}, {"sort by", "Cost"}, {"targets", P2})
=adGroups("this year", {"AdGroupName", "Cost", "Impressions"}, {"provider", "adwords"}, {"sort by", "Impressions"}, {"targets", P2})
=ads("2018-07-04..2018-07-05", {"Id", "Cost"}, {"provider", "adwords"}, {"sort by", "Cost"}, {"targets", P2})
The Basics
Start with an entity level
Start with what entity level you want to target. Use the " = " and then state the entity level. Options are accounts, campaigns, adgroups, and ads.
=accounts("last week", {"AccountDescriptiveName", "Cost"}, {"provider", "adwords"}, {"sort by", "Cost"}, {"targets", P2})
=campaigns("last week", {"CampaignName", "Cost"}, {"provider", "adwords"}, {"sort by", "Cost"}, {"targets", P2}) Insert parameters to your function
Timeframes
The functions allow both relative ranges (phrases like "last 30 days") and specific date ranges. When specifying specific date ranges you can use a cell range of dates (ex:D1:E1) or a date range string of format: "2016-02-28..2016-03-03"
Specify columns
Functions must be supplied the column names to fetch data for. You can make this a list (ex: {"AccountDescriptiveName", "Cost"}) or a range of cells - usually your header row (ex: A1:H1)
Note: You can find a full list of available metrics and metadata here.
*Use additional Options
Additional options are added to the end of any of the supported custom functions in a list. You may just tack them on as additional arguments, wrap in a list, or even use a range of cells for configuration as the final parameter (ex: =accounts("this week", {"AccountDescriptiveName" "Cost"}, J7:K12))
Available Options
Add the below options as additional parameter in your formula.
"sort_by". Use this parameter to sort your data by metric. The sort is defaulted to Descending. Popular options are Cost and Clicks.
=adGroups("this year", {"AdGroupName", "Cost", "Impressions"}, {"provider", "adwords"}, {"sort by", "Impressions"}, {"targets", P2}) "timeframes". Filter down to certain timeframes, such as "today", "this_month", "last_year". Example:
=accounts("last week", {"AccountDescriptiveName", "Cost"}, {"provider", "adwords"}, {"sort by", "Cost"}, {"targets", P2})timeframes:
Today: this 1 day
Yesterday: last 1 day
Single date: data for the single day entered (ex: "2019-08-01" )*
"X" days ago: single day data, from a number of days ago ( ex: "7 days ago" )**
Last 3 days: last 3 days
Last 3 days, including today: this 3 days
This month: this month
This month and last month: this 2 months
Last month: last month
Last month and the month before it: last 2 months
This year: this year
This year and last year: this 2 years
Last year: last year
Year to date: this year, up to end of yesterday
Year to now: this year, including today’s data (last sync)
Month/week to date/note
notes about timeframes:
THIS vs. LAST
If you want to include today, use THIS x days/weeks/months/years. If you don’t want to include today use LAST x days/weeks/months/years.
"...to date" vs. "...to now" If you want to look include the last full day of data use, "...to date". If you want to include today's data (last import sync) use, "...to now".
aggregate by day. Get back data broken down by day by adding the special column name "Date". Example:
=accounts("last 3 days", {"Date", "AccountDescriptiveName", "Cost"}, {"provider", "adwords"}, {"targets", P2})
filters. Filters to apply to your data, such as campaign name matching (ex: "CampaignName ~ Brand" for campaign names containing brand) and impression filtering (ex: "Impressions > 0"). Combine multiple filters with a semicolon: ";" Example:
=campaigns("last week", {"CampaignName", "Cost", "Impressions"}, {"filters", "CampaignName ~ Brand; Impressions > 0"}, {"provider", "adwords"}, {"targets", P2})
Valid filters are:
> (greater than)
< (less than)
~ (text match)
!~ (text does not match)
= (numbered string includes)
= 1 | 2 (numbered strings include 1 or 2)
Dimensions. Add dimensions to your formulas by simply adding the dimension as one of the metrics in the query - the formula will automatically break out performance by the dimension. Popular options are Device, AdNetworkType1 (network), and ConversionTypeName (conversion name). More options in the Google Ads Direct Metrics List. Example:
=campaigns("last 7 days", {"CampaignName", "Cost", "Device"}, {"provider", "adwords"}, {"targets", "P2"})
Looking for cross-network parameters? Check out our cross-network article, click the button below!