Stream a Google Ads account

A Google Ads stream lands an account's spend, clicks and conversions in your warehouse, day by day, with the calls its ads brought in and its Local Services leads. Each run reads the last few weeks again, because Google keeps adding conversions to the day of the click that led to them.

Google Ads is a published source, like Postgres and Bite: sanda wrote the code that reads it and runs it as its own, so there is no connector for sam to write or for anyone to approve. Someone who can see the account connects a Google sign-in, you choose the account and what to read, and you set a schedule.

Before you start

• Someone who can see the account signs in. Whoever connects signs in to Google with the Google account they use for Google Ads, and allows sanda in. They need access to the Google Ads account itself, or to the manager account above it (an agency's, say). In sanda, only owners and admins connect a sign-in and make a stream on it.
• One sign-in, many accounts. A Google sign-in is the person, not the account: one sign-in can see several Google Ads accounts, directly or through a manager account. Each stream reads one account, so a business with two accounts makes two streams on the same sign-in.
• Google asks for a passkey. Whoever connects an app to Google Ads signs in with a passkey, with 2-Step Verification on, every time they connect. A passkey made that day can take up to a week before Google trusts it, so set one up early.
• Google asks to manage the account. Google Ads has one permission for apps, and it covers changing the account as well as reading it, so that is what Google's screen asks for. sanda only ever reads: every request it sends asks for figures or lists accounts, and nothing else is let through.
• Local Services leads need Local Services Ads. An account with no Local Services campaign has no leads, and its leads tables stay empty.

Connect a Google sign-in

sanda keeps the connection encrypted, and it is never shown, in the console or to sam. Connect again signs in again: sign in as the same person, and every stream on the sign-in carries on. Disconnect forgets the connection in sanda; Google keeps sanda's access listed in the person's Google Account until they remove it there.

Make a stream

What it reads

"The last days again" counts back from the last day the previous run read, up to today in the account's own time zone. A first run reads from Read history from, or the last 90 days.

Spend, clicks and conversions by day

Campaigns by day, Ad groups by day, Keywords by day and Search terms by day each hold one row a day for each campaign, ad group, keyword or search term that had any: cost, clicks, impressions and conversions, with the name, status and type beside them. Campaigns by day and Ad groups by day also count calls from ads. A search term's row is the term's whole day, whichever keyword matched it.

Google dates a conversion by the click that led to it, and keeps adding conversions to past days for weeks, so each run reads the last 30 days again (or what Days read again says, up to 90). A day read again replaces its row. metrics_conversions_by_conversion_date counts the same conversions by the day they happened instead.

Conversions by action splits each campaign's conversions by the conversion action that counted them, so a call, a form and a booking can be told apart. Google won't give cost in the same report, so it is a table of its own: join it to Campaigns by day on campaign_id and segments_date.

Calls

Calls has a row for each call from a call ad or a call asset: when it started and ended, how long it lasted, whether it was answered, and the caller's area code (empty when the call was very short). Calls from Local Services ads are in Local Services conversations instead.

Local Services leads and conversations

Local Services leads has a row for each lead from Local Services Ads (Google Guaranteed): its type (a call, a message or a booking), its service category, its status, whether you were charged and whether a credit is pending or given, and the customer's name and phone number. A lead's status, charge and credit change after it arrives, and Google doesn't say when, so each run reads every lead made in the last 90 days again (or what Lead days read again says, from 7 to 365).

Local Services conversations has a row for each call, message or booking on a lead, with lsa_conversation_lead naming its lead: a call's length and recording link, a message's text and attachments. Conversations are only ever added, so each run reads the last 7 days again. A recording or an attachment opens only for someone signed in to Google with access to the account.

Both tables hold customers' names, phone numbers and words, so treat them as personal information.

Conversion actions and the account

Conversion actions lists the account's conversion actions with their category, type, status, and whether each counts toward conversions. Account is one row: the account's name, currency, time zone and status, and whether it is a manager or a test account.

Clicks

Clicks has a row for each ad click with its Google click ID (click_view_gclid), the campaign, ad group and keyword it came from, and its day. When your booking form keeps the GCLID of the visit that booked a job, joining the two says what each job cost to win. Google answers clicks one day at a time and keeps the last 90 days, so a first read asks once for each day it reads, up to 90 of them, and each later run asks once for each day from 2 days before the last day it read. Every one of those asks counts toward Google's daily allowance (see limits), which is why Clicks starts unticked.

Left out

• Performance Max search terms. Search terms by day is Google's search terms report, which leaves Performance Max campaigns out.
• Ads and assets. What each ad and asset said and how it did is not read yet.
• The charge for each Local Services lead. Google doesn't give the amount per lead, only whether you were charged and credited. Campaign cost for your Local Services campaign is in Campaigns by day.
• Change history, and breakdowns by device, network or place. Not read yet.

What lands in your warehouse

Every field becomes a column, and the whole row is kept in _record, as for every pull. A column is named after the field it holds, as Google names it: campaign_id, segments_date, metrics_cost_micros. The Local Services tables shorten their resource's name to lsa_lead_… and lsa_conversation_….

• Money is in micros, millionths of the account's currency: Google sends A$12.34 as 12340000. Divide by 1000000 in a view. The currency is customer_currency_code in Account.
• Days are the account's own, in its time zone, as Google reports them.
• Times carry no time zone. The Local Services times (lsa_lead_creation_date_time and the rest) are the account's own clock time, and a call's start and end in Calls are kept as Google writes them, so they land without a time zone. Convert them in a view if you need another.
• Counts are whole numbers and conversions are decimals: clicks, impressions and cost land as whole numbers, conversions and conversion values with their fractions.
• Ids are text, as Google sends them. A row's own name, such as campaign_resource_name, is the row's key with its day.
• What leaves. A day read again that no longer has a row (a campaign removed with its figures, a lead Google withdrew) is marked _deleted_at and kept, and so is a conversion action Google no longer lists. A row older than what a run reads is never touched.

Change a stream

Edit, on the stream's page, changes everything but the account and the warehouse. A stream keeps the account it reads, but it may move to another Google sign-in that can see the same account (when the person who connected it leaves, say). Something you take off keeps its table, with what it landed. A new Read history from date takes effect the next time everything is read from the start: press Load everything again on the stream's page.

When something goes wrong

A run that stops early because Google asked it to is not a failure: it lands what it read, says why under its status, and the next run carries on.

A failed run counts toward the stream pausing itself, as any stream's does. See runs.

Limits

• A Google Ads stream runs at most every 6 hours, because Google limits how many requests sanda's Google project makes in a day, and every workspace shares them. A run sam or an agent starts sooner is skipped, and Run now asks before it runs one sooner. sanda counts the requests every workspace makes, and stops for the day at 2,880.
• Each table a run reads counts as one request toward that, however many pages it takes, and a page holds up to 10,000 rows. Clicks counts one for each day it reads, and the account list one for each account it asks under.
• A run makes at most 2 requests a second.
• Read history from goes back 1120 days at the most: Google keeps daily figures for 37 months. Clicks go back 90 days.
• The account list asks under at most 25 of the accounts a sign-in opens itself. Type any other.
• One run lands at most 20,000,000 records, so a long history is several runs.

A Google Ads stream is billed like any pull: a charge for each run that does its work, and the rows it lands past what your edition includes. See what streams cost.