Fivetran has years of experience engineering data connectors for cloud applications, and one thing we’ve learned is that advertising APIs are … different.
Start with the big qualitative difference: Ad services like Yahoo Gemini, Google Ads and Facebook Ads don’t offer access to raw data. Instead, they offer access to APIs that pre-aggregate results into dimensions and metrics. That means you have to structure reports just to get your data.
But ad service APIs have a host of other quirks and limitations that can complicate querying, distort data, and ultimately make it impossible to understand how your campaigns are performing along the length of the marketing funnel. Fivetran has invested thousands of engineering hours in solving these issues because we know what’s at stake: accurate, full-funnel analysis that uncovers the true ROI of your campaigns.
Here’s how Fivetran connectors ensure you’re getting exactly what you need from your advertising services.
1. Provide Predefined Schemas and Recommended Configurations
With Fivetran connectors, you don’t have to puzzle out which dimensions and metrics will give you the data you need from a particular advertising data source — we’ve already thought it through for you.
For the most complex and powerful ad services (Google Ads, Facebook Ads, etc.), Fivetran recommends dimensions and metrics that yield all relevant performance data. These recommendations will help you avoid a big pitfall: selecting too many dimensions and metrics. Do that, and many of the big ad services will deliver zeros instead of actual data in an attempt to protect privacy. (The more granular the data, the easier it becomes to identify individuals.)
For simpler services like Bing Ads and Twitter Ads, we offer predefined schemas, so you don’t have to think about structuring reports at all:
If you use multiple advertising sources, you now have a single source of truth and can easily query across all of them. Find any Fivetran schema on our docs page.
2. Automatically Combat Sampling Error
Try to query hundreds of millions of events in Google Analytics and Google will probably scan only a very small percentage of those events — as little as 1%. Google claims its sampling yields accurate results, but Fivetran comparative analyses show that the numbers can be off by as much as 500%.
So we’ve devised an array of techniques to protect customer data against sampling. Querying by “day,” for example, is a simple, extremely effective approach. Select “month” as a dimension for an event-heavy period, and you’ll almost certainly encounter sampling error. But query by day and you can dramatically reduce the amount of data you’re querying, usually eliminating sampling outright. Using this and other techniques, Fivetran can entirely eliminate sampling error for customers querying massive data volumes in Google Analytics.
3. Account for Restatements and Attribution Windows
Advertising APIs have yet another data integrity problem: data changes over time due to restatements and attribution windows.
Restatements. Ad networks often log fraudulent behavioral data; they may also over-count behaviors, like a twice-counted tap from a mobile device that temporarily loses connectivity and then reconnects. Fivetran automatically detects when ad services correct these errors, and we support upsert semantics, so we continually fix existing data instead of appending tables with the corrected data, which would add unnecessary complexity.
Attribution windows. Ad networks use attribution windows to measure the impact of user browsing activity on downstream action (downloads, purchases, webinar attendance, etc.). Fivetran connectors automatically detect attribution windows and keep resyncing data until the window closes, so you’ll always capture the ultimate impact of your campaigns.
4. Sync Accounts Into a Single Table
To simplify and accelerate querying, Fivetran syncs all the accounts you’re pulling data from into a single table, adding “account” as a dimension. Now you can easily query across accounts — no need to create a join table for all of them.
This is particularly valuable because modern advertising systems often auto-generate ads while placing a limit on the number of ad configurations for any one account. That means customers have to continually add accounts to accommodate automatic ad generation. By adding them to one table, Fivetran ensures that querying remains easy. The alternative — adding a table for each new account — results in huge frustration for analysts, who have to continually edit queries to account for the influx of new tables.
5. Capture Metadata — Including Historical Metadata
To definitively measure campaign success, you need to know the URL of every ad, attribute user activity to that URL, follow traffic to every landing page, and join that data to your web analytics and conversion tracking data. Then you’ll need to evaluate that complete picture of performance against spend. That’s impossible without metadata, which contains key URL and spending information, so Fivetran always delivers metadata tables alongside reporting data.
“It’s just not good enough to say, This is how many clicks we got, and this is how much we spent,” says Fraser Harris, Director of Product at Fivetran. “It’s about joining those clicks to your website tracking data and through to sales data and conversions, and bringing all that forward so you can say: This is what the real results were for us. That’s the Holy Grail.”
Fivetran also captures metadata as history, which is critical, because modern platforms dynamically update ad URLs. If you don’t capture those changes, you’ll be attributing activity to obsolete URLs.
Effortlessly Analyze the Full Funnel
Ad service APIs present really tricky challenges for data teams, and the cost of mishandling those APIs isn’t just muddy data here and there — it’s an inability to measure campaign ROI. After years of doing battle with advertising APIs, we’ve developed connectors that offer a ready-to-query, single source of truth for every service you use, so your analysts can accurately quantify the impact of your most complex multi-channel campaigns — from first touch to final decision.