Google Adwords

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

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.

Data type of 'hevo_report_date' field as 'timestamp'

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