Google Adwords
ON THIS PAGE
Hevo can replicate your Google Adwords’ data to your data warehouse using Google’s AdWords API.
Setup
Access to Google Adwords Accounts
Hevo needs “view” permissions on your Google AdWords account to pull campaign and reports data. Hevo does not make any change to the Google AdWords account, so read-only permissions are sufficient. You can read more about these permissions here.
Connection Settings
You need to configure a few basic settings:
- Pipeline Name: A unique name for your Google Adwords Pipeline
- Select Objects & Reports: Select objects and reports which need to be replicated. By default, all objects and reports are replicated
- Select Accounts: Select accounts for which objects and reports need to be replicated
Supported Objects and Reports
Hevo provides support for replicating following objects and reports
Objects
- Accounts
- Ad Campaigns
- Ad Groups
- Ads
Reports
- All (see Google Adwords Report Types for complete list)
Replication Window and Schedule
Objects
On the initial setup, Hevo replicates objects’ data once. Subsequently, objects are synced up once on a daily basis.
Reports
On the initial setup, Hevo replicates reports’ data for the last one year. Subsequently, reports are synced up on an hourly basis. Since Google reports do not provide data at hourly grain, Hevo pulls current day’s data and performs upserts on the Destination.
Reports sync frequency can be adjusted by changing the Pipeline’s scheduled frequency.
Hevo does not fetch data for zero impressions.
Refresher job
For each report, a refresher job (‘reportname’ Report Refresher) is created to poll the last two days’ (Today-1 and Today-2) data by default. This job ensures that your data is up to date and any data freshness issues at Google’s end are overcome.
Read more about your data’s freshness.
As of Release 1.45, you can increase the default duration for polling data from two days to a maximum of 35 days, by contacting Hevo Support.
Schema Mapping
Note: In Release 1.45, the data type of the __hevo_report_date field is changed to timestamp from the earlier type of long to make it easier to query by. This change is applicable for all new Pipelines created from Release 1.45 onwards.
Objects
Hevo attaches following fields to all objects data pulled from Google APIs
- __hevo_report_date: represents the date on which the data was pulled by Hevo
- __hevo_id: represents a unique id of Source data. Hevo derives this by hashing Source data’s primary key columns
Reports
Hevo attaches following fields to all reports data pulled from Google APIs
- __hevo_report_date: represents the date for which the data was pulled by Hevo
-
__hevo_id: represents a unique id of Source data. Hevo derives this by hashing Source data’s primary key columns along with the date and customer-id
Example: For KEYWORDS_PERFORMANCE_REPORT, __hevo_id is generated using AdGroupId, Id, __hevo_report_date, and ExternalCustomerId. Doing so, Hevo ensures that if another record gets replicated with the same AdGroupId, Id, ExternalCustomerId, and __hevo_report_date, it will be considered as an update to the Destination table.
Mapping Considerations
- There are few metrics for which the values can be of different data types. For those metrics, another meta column is added to the Destination with the additional details.
Example: if the value of the metric cost is auto:1000, Hevo would create 2 columns: cost and cost meta with values 1000 and “auto:” respectively
For following values of an attribute, Hevo will create 2 columns, 1 with queryable value and another with the meta-information
| Actual Value | Queryable Value | Meta Information |
|---|---|---|
| 10% | 10 | % |
| <10% | 10 | <% |
| >10% | 10 | >% |
| auto:x | x | auto: |
| auto | null | auto |
| 10,000.5 | 10000.5 | , |
- MySQL/Postgres Destination: In case of Account, AdGroups, Ads, Ad Campaigns objects, if the table or column name exceeds 64 characters (max limit for MySQL and Postgres), then Hevo maps them to shortened name. See appendix for shortened names mapping.
Limitations
-
Hevo does not replicate the following attributes in reports
- ProductTitle
- StoreId
- ConversionAdjustment
Appendix
Objects Primary Key
| Object | Primary Key |
|---|---|
| ACCOUNTS | CustomerId |
| ADS | AdGroupId, Id, CustomerId |
| AD_GROUPS | Id, CustomerId |
| CAMPAIGNS | Id, CustomerId |
Reports Primary Key
| Report | Primary Key |
|---|---|
| KEYWORDS_PERFORMANCE_REPORT | AdGroupId, Id, Date, ExternalCustomerId |
| AD_PERFORMANCE_REPORT | AdGroupId, Id, Date, ExternalCustomerId |
| URL_PERFORMANCE_REPORT | Url, CriteriaParameters, Date, ExternalCustomerId |
| ADGROUP_PERFORMANCE_REPORT | AdGroupId, Date, ExternalCustomerId |
| CAMPAIGN_PERFORMANCE_REPORT | CampaignId, Date, ExternalCustomerId |
| ACCOUNT_PERFORMANCE_REPORT | AccountDescriptiveName, Date, ExternalCustomerId |
| GEO_PERFORMANCE_REPORT | CampaignId, MostSpecificCriteriaId, Date, ExternalCustomerId |
| SEARCH_QUERY_PERFORMANCE_REPORT | Query, Date, ExternalCustomerId |
| AUTOMATIC_PLACEMENTS_PERFORMANCE_REPORT | Domain, Date, ExternalCustomerId |
| CAMPAIGN_NEGATIVE_KEYWORDS_PERFORMANCE_REPORT | Campaign Id, Id, Date, ExternalCustomerId |
| CAMPAIGN_NEGATIVE_PLACEMENTS_PERFORMANCE_REPORT | Campaign Id, Id, Date, ExternalCustomerId |
| SHARED_SET_REPORT | SharedSetId, Date, ExternalCustomerId |
| CAMPAIGN_SHARED_SET_REPORT | CampaignId, SharedSetId, Date, ExternalCustomerId |
| SHARED_SET_CRITERIA_REPORT | SharedSetId, Id, Date, ExternalCustomerId |
| CREATIVE_CONVERSION_REPORT | CriterionId, AdGroupId, Date, ExternalCustomerId |
| CALL_METRICS_CALL_DETAILS_REPORT | AdGroupId, ExternalCustomerId, CallStartTime, Date |
| KEYWORDLESS_QUERY_REPORT | CriterionId, Date, ExternalCustomerId |
| KEYWORDLESS_CATEGORY_REPORT | Category0, Category1, Category2, Date, ExternalCustomerId |
| CRITERIA_PERFORMANCE_REPORT | AdGroupId, Id, Date, ExternalCustomerId |
| CLICK_PERFORMANCE_REPORT | GclId, Date, ExternalCustomerId |
| BUDGET_PERFORMANCE_REPORT | BudgetId, Date, ExternalCustomerId |
| BID_GOAL_PERFORMANCE_REPORT | Id, Date, ExternalCustomerId |
| DISPLAY_KEYWORD_PERFORMANCE_REPORT | AdGroupId, Id, Date, ExternalCustomerId |
| PLACEHOLDER_FEED_ITEM_REPORT | AdGroupId, AdId, FeedId, FeedItemId, Date, ExternalCustomerId |
| PLACEMENT_PERFORMANCE_REPORT | Id, Date, ExternalCustomerId |
| CAMPAIGN_NEGATIVE_LOCATIONS_REPORT | Campaign Id, Id, Date, ExternalCustomerId |
| GENDER_PERFORMANCE_REPORT | Id, Date, ExternalCustomerId |
| AGE_RANGE_PERFORMANCE_REPORT | Id, Date, ExternalCustomerId |
| CAMPAIGN_LOCATION_TARGET_REPORT | Id, Date, ExternalCustomerId |
| CAMPAIGN_AD_SCHEDULE_TARGET_REPORT | Id, Date, ExternalCustomerId |
| PAID_ORGANIC_QUERY_REPORT | AdGroupId, CampaignId, KeywordId, Date, ExternalCustomerId |
| AUDIENCE_PERFORMANCE_REPORT | Id, Date, ExternalCustomerId |
| DISPLAY_TOPICS_PERFORMANCE_REPORT | Id, Date, ExternalCustomerId |
| USER_AD_DISTANCE_REPORT | CampaignId, DistanceBucket, Date, ExternalCustomerId |
| SHOPPING_PERFORMANCE_REPORT | OfferId, Date, ExternalCustomerId |
| PRODUCT_PARTITION_REPORT | Id, Date, ExternalCustomerId |
| PARENTAL_STATUS_PERFORMANCE_REPORT | Id, Date, ExternalCustomerId |
| PLACEHOLDER_REPORT | AdGroupId, ExtensionPlaceholderCreativeId, Date, ExternalCustomerId |
| AD_CUSTOMIZERS_FEED_ITEM_REPORT | FeedItemId, Date, ExternalCustomerId |
| LABEL_REPORT | LabelId, Date, ExternalCustomerId |
| FINAL_URL_REPORT | EffectiveFinalUrl, Date, ExternalCustomerId |
| VIDEO_PERFORMANCE_REPORT | AdGroupId, CampaignId, CreativeId, VideoChannelId, VideoId, Date, ExternalCustomerId |
| TOP_CONTENT_PERFORMANCE_REPORT | AdGroupId, Id, Date, ExternalCustomerId |
| CAMPAIGN_CRITERIA_REPORT | CampaignId, Id, Date, ExternalCustomerId |
| CAMPAIGN_GROUP_PERFORMANCE_REPORT | Id, Date, ExternalCustomerId |
| LANDING_PAGE_REPORT | UnexpandedFinalUrlString, Date, ExternalCustomerId |
| MARKETPLACE_PERFORMANCE_REPORT | CampaignId, AdGroupId, Date, ExternalCustomerId |
MySQL/Postgres Object Name Mapping
| Attribute Name | Compressed Name |
|---|---|
| conversionoptimizereligibility | coe |
| adservingoptimizationstatus | aos |
| advertisingchanneltype | adchantype |
| advertisingchannelsubtype | adchansubtype |
| biddingstrategyconfiguration | bsc |
| forwardCompatibilityMap | fwdcompmap |
| urlCustomParameters | urlcusparam |
| universalappcampaigninfo | univappcmpinfo |
| selectiveOptimization | selopt |
| targetgooglesearch | tgs |
| targetsearchNetwork | tsn |
| targetContentNetwork | tcn |
| targetPartnerSearchNetwork | tpsn |
| biddingstrategyid | bsid |
| biddingStrategyName | bsnm |
| biddingStrategyType | bstype |
| biddingStrategySource | bssource |
| biddingscheme | bidsch |
| biddingschemetype | bidschtype |
| vanityPharmaDisplayUrlMode | vanphmode |
| vanityPharmaText | vanphtxt |
| biddingstrategygoaltype | bsgoaltype |
| contentBidCriterionTypeGroup | contbidcrittypegrp |
| adGroupAdRotationMode | adgrprotmod |
| adRotationMode | adrotmod |
| systemManagedEntitySource | sysmgentso |
| adStrengthInfo | asinfo |
| policysummary | polsum |
| policytopicentries | poltopentry |
| policyTopicEntryType | poltopentrytype |
| policytopicevidences | poltopevid |
| policyTopicEvidenceType | poltopevidtype |
| policyTopicEvidenceDestinationMismatchUrlTypes | poltopdestmisurltypes |
| policytopicconstraints | poltopconst |
| policyTopicConstraintType | poltopconsttype |
| policytopichelpcenterurl | poltophelpurl |
| denormalizedStatus | denstat |
| combinedApprovalStatus | comapprstat |
| constrainedcountries | conctries |
| comparablevaluetype | cvt |
| enhancedcpcenabled | enhcpcenabl |
| dynamicsearchadssetting | dynset |
| geotargettypesetting | geoset |
| positivegeotargettype | posgeotype |
| negativegeotargettype | neggeotype |
| universalappcampaignsetting | univcampset |
| mobileapplicationvendor | mobappvendor |
| youtubevideomediaidsops | ytmediaidsops |
| adspolicydecisions | adspoldec |
| universalappcampaignadspolicydecisions | univappcamppoldec |
| universalappCampaignasset | univappcampasset |
| certificatedomainmismatchconstraint | certdomconst |
| certificatemissingconstraint | certmissconst |
References
- Google Ads Official Documentation for reports, their attributes and metrics : All reports | AdWords API