How to import Google Analytics data to Google BigQuery: comparing the two methods of data collection

Google BigQuery is perfect for analyzing any amount of raw Google Analytics data. To deliver the data from Google Analytics to Google BigQuery, you can use one of the two most common methods:

  1. BigQuery Export from Google Analytics;
  2. Streaming the data with OWOX BI Pipeline.

In this article we compare these two methods, considering the price, the specifics of data collection and the structures of the data.

1. Price and Conditions

Export from Google Analytics to Google BigQuery is only available to Google Analytics 360 accounts. If you’re using the free version of Google Analytics, this feature won’t be available to you.

The price of Google Analytics 360 includes the $500 monthly credit to export and process the data. Google BigQuery also charges for data storage. The price is based on the amount of data stored in your tables. In our experience, the additional monthly cost for a project with 1M unique users per month is about $20.

Streaming with OWOX BI is available to all Google Analytics users and you don’t need a Google Analytics 360 account.

The price for data collection with OWOX BI depends on the number of unique users per month. You can estimate the price for your specific project here. Since the amount of data is approximately the same as in Google BigQuery, the price of data storage will also be about the same.

2. Characteristics Of Data Collection

Methods for data delivery to Google BigQuery differ between BigQuery Export and OWOX BI Pipeline.

With BigQuery Export, the data is initially collected in Google Analytics 360 and then periodically transferred to Google BigQuery. The delay is up to 36 hours for the daily export and up to 8 hours for the intermediate tables. The tables provide the data from Google Analytics as well as the data from the connected Google services, such as AdWords, DFP, AdSense or AdX.

When streaming the data with OWOX BI, the data is sent to BigQuery from your website by a separate request. Because of this, it becomes available in Google BigQuery within just a couple of minutes. Since the data is collected in real time, it’s presented as individual hits. The session data is collected with a delay up to 32 hours, based on the Google Analytics Core API.

Sending the data independently from Google Analytics gives you a number of advantages:

  1. The data is collected in Google BigQuery in real time. This is crucial if the data on user actions on your website is used for triggered emails or frequent dashboard updates;
  2. You can collect and process PII data, such as the user’s IP address, email or phone number. In Google Analytics, sending PII data is prohibited;
  3. The data can be collected on an individual domain , e.g. stat.yourdomain.com, in the protected area of your corporate network;
  4. You can send more than 20 user-defined parameters in comparison with the standard version of Google Analytics.

The unique benefit of collecting data in Google BigQuery with OWOX BI is that, you can access the cost data for each session with the two fields: trafficSource.adCost The cost of the specific ad campaign is distributed proportionally over the sessions if utm-parameters of the campaign precisely match utm-parameters of the session.
Read more...
and trafficSource.AttributedAdCost The cost of the specific session, which consists of the trafficSource.adCost and the cost for campaigns with utm-parameters that don’t have registered sessions. Its value is always greater than or equal to trafficSource.adCost.
Read more...
.

Cost data is needed to analyze the performance of advertising campaigns and to calculate such indicators as ROI, ROAS and CPA.

3. Data Structure

The imported data in Google BigQuery is very similar between the export and the streaming: 25 RECORD fields match, 4 fields are unique to the export from Google Analytics 360, and 1 is unique to OWOX BI.

You can find the full explanation of the data structure in the Google Analytics Help for Google BigQuery Export and in the OWOX BI knowledge base. Below are the fields which are different in the data schemas:

Field Name Standart BigQuery export OWOX BI
userId checking-icon2 checking-icon
clientId   checking-icon
sessionId   checking-icon1
fullVisitorId checking-icon  
visitId checking-icon  
visitStartTime checking-icon  
visitNumber checking-icon checking-icon1
newVisits   checking-icon1
user.phone   checking-icon
user.email   checking-icon
date checking-icon checking-icon
dataSource   checking-icon
queueTime   checking-icon
socialEngagementType checking-icon  
isTrueDirect checking-icon  
trafficSource.referralPath checking-icon checking-icon
trafficSource.campaign checking-icon checking-icon
trafficSource.source checking-icon checking-icon
trafficSource.medium checking-icon checking-icon
trafficSource.keyword checking-icon checking-icon
trafficSource.adContent checking-icon  
trafficSource.campaignCode checking-icon  
traffic.campaignId   checking-icon
traffic.gclId checking-icon checking-icon
traffic.dclid   checking-icon
trafficSource.channelGrouping checking-icon checking-icon1
trafficSource.adCost   checking-icon1
trafficSource.attributedAdCost   checking-icon1
trafficSource.adwordsClickInfo checking-icon  
trafficSource.adwordsClickInfo.customerId checking-icon  
trafficSource.adwordsClickInfo.campaignId checking-icon checking-icon
trafficSource.adwordsClickInfo.adGroupId checking-icon  
trafficSource.adwordsClickInfo.creativeId checking-icon  
trafficSource.adwordsClickInfo.criteriaId checking-icon  
trafficSource.adwordsClickInfo.page checking-icon  
trafficSource.adwordsClickInfo.slot checking-icon  
trafficSource.adwordsClickInfo.criteriaParameters checking-icon  
trafficSource.adwordsClickInfo.gclId checking-icon checking-icon
trafficSource.adwordsClickInfo.adNetworkType checking-icon checking-icon1
trafficSource.adwordsClickInfo.adMatchedQuery   checking-icon1
trafficSource.adwordsClickInfo.isVideoAd checking-icon  
trafficSource.adwordsClickInfo.targetingCriteria checking-icon  
trafficSource.adwordsClickInfo.targetingCriteria.boomUserlistId checking-icon  
geo.id   checking-icon
geoNetwork.country checking-icon checking-icon1
geoNetwork.region checking-icon checking-icon1
geoNetwork.city checking-icon checking-icon1
geoNetwork.continent checking-icon  
geoNetwork.subContinent checking-icon  
geoNetwork.metro checking-icon  
geoNetwork.latitude checking-icon  
geoNetwork.longitude checking-icon  
geoNetwork.networkDomain checking-icon  
geoNetwork.networkLocation checking-icon  
device.ip   checking-icon
device.userAgent   checking-icon
device.flashVersion checking-icon checking-icon
device.javaEnabled checking-icon checking-icon
device.language checking-icon checking-icon
device.screenColors checking-icon checking-icon
device.screenResolution checking-icon checking-icon
device.browser checking-icon checking-icon1
device.browserVersion checking-icon checking-icon1
device.browserSize checking-icon  
device.deviceCategory checking-icon checking-icon1
device.operatingSystem checking-icon checking-icon1
device.operatingSystemVersion checking-icon checking-icon1
device.mobileDeviceBranding checking-icon checking-icon1
device.mobileInputSelector checking-icon  
hits.hitId   checking-icon
hits.hitNumber checking-icon  
hits.type checking-icon checking-icon
hits.time checking-icon checking-icon
hits.hour checking-icon checking-icon
hits.minute checking-icon checking-icon
hits.isEntrance checking-icon checking-icon
hits.isExit checking-icon checking-icon
hits.isSecure checking-icon checking-icon
hits.isInteraction checking-icon checking-icon
hits.referer checking-icon checking-icon
hits.refund checking-icon  
hits.sourcePropertyInfo.sourcePropertyDisplayName checking-icon  
hits.sourcePropertyInfo.sourcePropertyTrackingId checking-icon checking-icon
hits.exceptionInfo.exceptions checking-icon checking-icon
hits.exceptionInfo.fatalExceptions checking-icon checking-icon
hits.eventInfo checking-icon checking-icon
hits.social.hasSocialSourceReferral checking-icon  
hits.social.socialInteractionAction checking-icon checking-icon
hits.social.socialInteractionNetwork checking-icon checking-icon
hits.social.socialInteractionNetworkAction checking-icon  
hits.social.socialInteractions checking-icon  
hits.social.socialInteractionTarget checking-icon checking-icon
hits.social.socialNetwork checking-icon  
hits.social.uniqueSocialInteractions checking-icon  
hits.appInfo.name checking-icon checking-icon
hits.appInfo.version checking-icon checking-icon
hits.appInfo.id checking-icon checking-icon
hits.appInfo.installerId checking-icon checking-icon
hits.appInfo.screenName checking-icon  
hits.appInfo.landingScreenName checking-icon  
hits.appInfo.exitScreenName checking-icon  
hits.appInfo.screenDepth checking-icon  
hits.promotion.promoCreative checking-icon checking-icon
hits.promotion.promoId checking-icon checking-icon
hits.promotion.promoName checking-icon checking-icon
hits.promotion.promoPosition checking-icon checking-icon
hits.promotionActionInfo checking-icon checking-icon
hits.page.searchCategory checking-icon  
hits.transaction.transactionId checking-icon checking-icon
hits.transaction.transactionRevenue checking-icon checking-icon
hits.transaction.transactionTax checking-icon checking-icon
hits.transaction.transactionShipping checking-icon checking-icon
hits.transaction.transactionCoupon checking-icon checking-icon
hits.transaction.affiliation checking-icon checking-icon
hits.transaction.currencyCode checking-icon checking-icon
hits.transaction.localTransactionRevenue checking-icon checking-icon1
hits.transaction.localTransactionTax checking-icon checking-icon1
hits.transaction.localTransactionShipping checking-icon checking-icon1
hits.contentInfo checking-icon checking-icon
hits.item checking-icon  
hits.customDimensions checking-icon checking-icon
hits.customMetrics checking-icon checking-icon
hits.contentGroup.contentGroupXX checking-icon checking-icon
hits.contentGroup.previousContentGroupXX checking-icon  
hits.contentGroup.contentGroupUniqueViewsXX checking-icon  
hits.page.pageType   checking-icon1
hits.page.pagePath checking-icon checking-icon
hits.page.pagePathLevel1 checking-icon  
hits.page.pagePathLevel2 checking-icon  
hits.page.pagePathLevel3 checking-icon  
hits.page.pagePathLevel4 checking-icon  
hits.page.hostname checking-icon checking-icon
hits.page.pageTitle checking-icon checking-icon
hits.page.searchKeyword checking-icon  
hits.product.isImpression checking-icon checking-icon
hits.product.isClick checking-icon  
hits.product.productListName checking-icon checking-icon
hits.product.productSKU checking-icon checking-icon
hits.product.productPrice checking-icon checking-icon
hits.product.productQuantity checking-icon checking-icon
hits.product.productBrand checking-icon checking-icon
hits.product.productVariant checking-icon checking-icon
hits.product.v2ProductCategory checking-icon checking-icon
hits.product.v2ProductName checking-icon checking-icon
hits.product.productListPosition checking-icon checking-icon
hits.product.coupon   checking-icon
hits.product.localProductPrice checking-icon checking-icon1
hits.product.localProductRefundAmount checking-icon  
hits.product.localProductRevenue checking-icon  
hits.product.productRefundAmount checking-icon  
hits.product.customDimensions checking-icon checking-icon
hits.product.customMetrics checking-icon checking-icon
hits.eCommerceAction.action_type checking-icon checking-icon
hits.eCommerceAction.option checking-icon checking-icon
hits.eCommerceAction.step checking-icon checking-icon
hits.eCommerceAction.list   checking-icon
hits.eCommerceAction.list   checking-icon
hits.experiment checking-icon checking-icon
hits.latencyTracking.domainLookupTime checking-icon checking-icon
hits.latencyTracking.domContentLoadedTime checking-icon  
hits.latencyTracking.domInteractiveTime checking-icon checking-icon
hits.latencyTracking.domLatencyMetricsSample checking-icon  
hits.latencyTracking.pageDownloadTime checking-icon checking-icon
hits.latencyTracking.pageLoadSample checking-icon  
hits.latencyTracking.pageLoadTime checking-icon checking-icon
hits.latencyTracking.redirectionTime checking-icon checking-icon
hits.latencyTracking.serverConnectionTime checking-icon checking-icon
hits.latencyTracking.serverResponseTime checking-icon checking-icon
hits.latencyTracking.speedMetricsSample checking-icon  
hits.latencyTracking.userTimingCategory checking-icon  
hits.latencyTracking.userTimingLabel checking-icon  
hits.latencyTracking.userTimingSample checking-icon  
hits.latencyTracking.userTimingValue checking-icon  
hits.latencyTracking.userTimingVariable checking-icon  
totals.timeOnSite checking-icon  
totals.bounces checking-icon  
totals.totalTransactionRevenue checking-icon  
totals.newVisits checking-icon  
totals.UniqueScreenViews checking-icon  
totals.timeOnScreen checking-icon  
totals.visits checking-icon checking-icon1
totals.hits checking-icon checking-icon1
totals.transactions checking-icon checking-icon1
totals.events   checking-icon1
totals.pageviews checking-icon checking-icon1
totals.screenviews checking-icon checking-icon1

1—in session streaming;
2—only for properties with enabled User ID feature where there is no other data.

4. What to choose?

Each of the above methods has its own advantages. In our experience, Google Analytics 360 users often use both methods at the same time, as they allow solving different tasks. However, if Google Analytics 360 now seems too expensive for you, streaming your data with OWOX BI is already an excellent way to benefit from collecting and analyzing unsampled data in Google BigQuery.