Importing Google Analytics Data to Google BigQuery: Comparing 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 methods:

  1. BigQuery Export for Analytics, or
  2. OWOX BI Pipeline: Google Analytics to Google BigQuery.

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

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

Google BigQuery charges for data storage and query processing. The price is based on the number of bytes processed, and 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. Google Analytics 360 provides the $500 per-month credit to export and process the data.

OWOX BI Pipeline can be used for all types of Google Analytics accounts, no matter free or paid. The price for data collection with OWOX BI depends on the number of active users per month. You can estimate the price for your specific project here. Since the amount of data stored in Google BigQuery is approximately the same as with the BigQuery Export, the price of data storage and query processing 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. There are two export options you can choose between:

  1. Daily data is imported every 8 hours. Each time daily data is imported, any previous data for that day is overwritten in the table. The tables provide the data from Google Analytics as well as the data from the connected Google services, such as Google Ads, DFP, AdSense or AdX.
  2. Data exported continuously. In this case, the data exported continuously approximately every 10 to 15 minutes. With this option, data from other advertising services (Google Ads, Ad Manager, AdSense, etc.) is available on the following day. This option costs additional $0.05 per GB of data (approximately 600,000 Google Analytics hits) and the cost can be covered by the $500-per-month coupon for Analytics 360 users.

With OWOX BI Pipeline, the hit data is sent to BigQuery directly from your website by a separate request. Because of this, it becomes available in Google BigQuery within a matter of minutes. The data is collected in near real time and presented as individual hits. Session data tables are computed within 9 hours on the following day based on the OWOX BI sessionization algorithm and updated within 24 hours (more information about how we compute sessions can be found in our Help Center).

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 custom parameters in comparison with the standard version of Google Analytics.
  5. With the help of the additional OWOX User ID identifier, you can track the intersection of audiences of any sites for which you have set up OWOX BI data streaming, even if these sites are not connected to each other by direct links.
  6. For the OWOX BI stream, the maximum hit payload size is 16 KB. The limit in Google Analytics 360 is 8 KB.

The unique benefit of collecting data in Google BigQuery with OWOX BI is that, you can access the cost data for each particular session generated by paid traffic sources, with the two fields: trafficSource.adCost The cost of the session, derived from ad campaigns, if utm-parameters of the campaigns exactly match the utm-parameters of the session.
Read more...
and trafficSource.AttributedAdCost Session cost that includes trafficSource.adCost plus the costs of campaigns with utm-parameters that have no registered sessions. Always greater than or equal.
Read more...
.

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

3. Data Structure

The structure of imported data in Google BigQuery is very similar in BigQuery Export and OWOX BI Pipeline: 23 RECORD fields are the same, 5 fields are unique to the export from Google Analytics 360, and 2 are 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 Standard BigQuery export OWOX BI
userId checking-icon2 checking-icon
clientId checking-icon checking-icon
sessionId   checking-icon1
fullVisitorId checking-icon  
visitId checking-icon  
visitStartTime checking-icon  
visitNumber checking-icon checking-icon
newVisits   checking-icon1
user   checking-icon
user.phone   checking-icon
user.email   checking-icon
user.owoxId   checking-icon
date checking-icon checking-icon
socialEngagementType checking-icon  
totals checking-icon checking-icon
totals.visits checking-icon checking-icon
totals.hits checking-icon checking-icon
totals.transactions checking-icon checking-icon
totals.events checking-icon checking-icon
totals.pageviews checking-icon checking-icon
totals.screenviews checking-icon checking-icon
totals.transactionRevenue checking-icon  
totals.sessionQualityDim 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.isInteraction   checking-icon1
totalsStreaming   checking-icon1
totalsStreaming.hits   checking-icon1
totalsStreaming.events   checking-icon1
totalsStreaming.transactions   checking-icon1
totalsStreaming.pageviews   checking-icon1
totalsStreaming.screenviews   checking-icon1
totalsStreaming.isInteraction   checking-icon1
trafficSource checking-icon checking-icon
trafficSource.adGroup   checking-icon1
trafficSource.keywordMatchType   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 checking-icon
trafficSource.campaignCode checking-icon  
trafficSource.gclid   checking-icon1
trafficSource.dclid   checking-icon1
trafficSource.isTrueDirect checking-icon checking-icon
trafficSource.channelGrouping checking-icon checking-icon
trafficSource.adCost   checking-icon1
trafficSource.attributedAdCost   checking-icon1
trafficSource.adwordsClickInfo checking-icon  
trafficSource.adwordsClickInfo.customerId checking-icon  
trafficSource.adwordsClickInfo.campaignId 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  
trafficSource.adwordsClickInfo.adNetworkType checking-icon checking-icon
trafficSource.adwordsClickInfo.adMatchedQuery   checking-icon1
trafficSource.adwordsClickInfo.isVideoAd checking-icon  
trafficSource.adwordsClickInfo.targetingCriteria checking-icon  
trafficSource.adwordsClickInfo.targetingCriteria.boomUserlistId checking-icon  
geo   checking-icon
geo.id   checking-icon
geoNetwork checking-icon  
geoNetwork.country checking-icon checking-icon
geoNetwork.region checking-icon checking-icon
geoNetwork.city checking-icon checking-icon
geoNetwork.cityId checking-icon  
geoNetwork.continent checking-icon  
geoNetwork.subContinent checking-icon  
geoNetwork.metro checking-icon  
geoNetwork.latitude checking-icon checking-icon
geoNetwork.longitude checking-icon checking-icon
geoNetwork.networkDomain checking-icon  
geoNetwork.networkLocation checking-icon  
device checking-icon checking-icon
device.ip   checking-icon1
device.userAgent   checking-icon1
device.flashVersion checking-icon  
device.javaEnabled checking-icon  
device.language checking-icon checking-icon
device.screenColors checking-icon  
device.screenResolution checking-icon  
device.browser checking-icon checking-icon
device.browserVersion checking-icon checking-icon
device.browserSize checking-icon  
device.deviceCategory checking-icon checking-icon
device.operatingSystem checking-icon checking-icon
device.operatingSystemVersion checking-icon checking-icon
device.mobileDeviceBranding checking-icon  
device.mobileInputSelector checking-icon  
device.mobileDeviceInfo checking-icon checking-icon
device.mobileDeviceMarketingName checking-icon  
device.mobileDeviceModel checking-icon  
customDimensions checking-icon checking-icon
customDimensions.index checking-icon checking-icon
customDimensions.value checking-icon checking-icon
hits checking-icon checking-icon
hits.hitId   checking-icon1
hits.dataSource checking-icon checking-icon
hits.device   checking-icon
hits.device.ip   checking-icon
hits.device.userAgent   checking-icon
hits.device.flashVersion   checking-icon
hits.device.javaEnabled   checking-icon
hits.device.language   checking-icon
hits.device.screenColors   checking-icon
hits.device.screenResolution   checking-icon
hits.geo   checking-icon
hits.geo.id   checking-icon1
hits.customGroups   checking-icon
hits.customGroups.index   checking-icon
hits.customGroups.value   checking-icon
hits.contentGroups checking-icon checking-icon
hits.contentGroups.index   checking-icon
hits.contentGroups.value   checking-icon
hits.hitNumber checking-icon  
hits.type checking-icon checking-icon
hits.time checking-icon checking-icon
hits.timestamp   checking-icon1
hits.queueTime   checking-icon1
hits.currency   checking-icon1
hits.referralPath   checking-icon1
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.pagePath   checking-icon1
hits.pageType   checking-icon1
hits.eCommerceActionType   checking-icon1
hits.refund checking-icon  
hits.refund.localRefundAmount checking-icon  
hits.refund.refundAmount checking-icon  
hits.experiment checking-icon checking-icon
hits.experiment.experimentId checking-icon checking-icon
hits.experiment.experimentVariant checking-icon checking-icon
hits.sourcePropertyInfo checking-icon  
hits.sourcePropertyInfo.sourcePropertyDisplayName checking-icon  
hits.sourcePropertyInfo.sourcePropertyTrackingId checking-icon  
hits.exceptionInfo.description checking-icon checking-icon
hits.exceptionInfo.isFatal checking-icon checking-icon
hits.eventInfo checking-icon checking-icon
hits.eventInfo.eventCategory checking-icon checking-icon
hits.eventInfo.eventAction checking-icon checking-icon
hits.eventInfo.eventLabel checking-icon checking-icon
hits.eventInfo.eventValue checking-icon checking-icon
hits checking-icon  
hits.social 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 checking-icon 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 checking-icon 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  
hits.promotionActionInfo.promoIsView checking-icon  
hits.promotionActionInfo.promoIsClick checking-icon  
hits.page checking-icon checking-icon
hits.page.searchCategory checking-icon  
hits.transaction checking-icon 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  
hits.transaction.localTransactionRevenue checking-icon checking-icon
hits.transaction.localTransactionTax checking-icon checking-icon
hits.transaction.localTransactionShipping checking-icon checking-icon
hits.contentInfo checking-icon checking-icon
hits.contentInfo.contentDescription checking-icon checking-icon
hits.item checking-icon  
hits.item.transactionId checking-icon  
hits.item.productName checking-icon  
hits.item.productCategory checking-icon  
hits.item.productSku checking-icon  
hits.item.itemQuantity checking-icon  
hits.item.itemRevenue checking-icon  
hits.item.currencyCode checking-icon  
hits.item.localItemRevenue checking-icon  
hits.customDimensions checking-icon checking-icon
hits.customDimensions.index checking-icon checking-icon
hits.customDimensions.value checking-icon checking-icon
hits.customMetrics checking-icon checking-icon
hits.customMetrics.index checking-icon checking-icon
hits.customMetrics.value checking-icon checking-icon
hits.contentGroup.contentGroupX checking-icon  
hits.contentGroup.previousContentGroupX checking-icon  
hits.contentGroup.contentGroupUniqueViewsX checking-icon  
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 checking-icon checking-icon
hits.product.isImpression checking-icon checking-icon
hits.product.isClick checking-icon  
hits.product.impressionList   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.productRevenue 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  
hits.product.v2ProductName checking-icon  
hits.product.productListPosition checking-icon  
hits.product.productCategory   checking-icon
hits.product.productName   checking-icon
hits.product.position   checking-icon
hits.product.coupon   checking-icon
hits.product.localProductPrice checking-icon checking-icon
hits.product.localProductRefundAmount checking-icon  
hits.product.localProductRevenue checking-icon  
hits.product.productRefundAmount checking-icon  
hits.product.customDimensions checking-icon checking-icon
hits.product.customDimensions.index checking-icon checking-icon
hits.product.customDimensions.value checking-icon checking-icon
hits.product.customMetrics checking-icon checking-icon
hits.product.customMetrics.index checking-icon checking-icon
hits.product.customMetrics.value checking-icon checking-icon
hits.eCommerceAction 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.exceptionInfo checking-icon checking-icon
hits.exceptionInfo.exceptions checking-icon  
hits.exceptionInfo.fatalExceptions checking-icon  
hits.customVariables checking-icon  
hits.customVariables.index checking-icon  
hits.customVariables.customVarName checking-icon  
hits.customVariables.customVarValue checking-icon  
hits.latencyTracking checking-icon  
hits.latencyTracking.domainLookupTime checking-icon  
hits.latencyTracking.domContentLoadedTime checking-icon  
hits.latencyTracking.domInteractiveTime checking-icon  
hits.latencyTracking.domLatencyMetricsSample checking-icon  
hits.latencyTracking.pageDownloadTime checking-icon  
hits.latencyTracking.pageLoadSample checking-icon  
hits.latencyTracking.pageLoadTime checking-icon  
hits.latencyTracking.redirectionTime checking-icon  
hits.latencyTracking.serverConnectionTime checking-icon  
hits.latencyTracking.serverResponseTime 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  
hits.publisher.adsenseBackfillDfpClicks checking-icon  
hits.publisher.adsenseBackfillDfpImpressions checking-icon  
hits.publisher.adsenseBackfillDfpMatchedQueries checking-icon  
hits.publisher.adsenseBackfillDfpMeasurableImpressions checking-icon  
hits.publisheradsenseBackfillDfpPagesViewed checking-icon  
hits.publisher.adsenseBackfillDfpQueries checking-icon  
hits.publisher.adsenseBackfillDfpRevenueCpc checking-icon  
hits.publisher.adsenseBackfillDfpRevenueCpm checking-icon  
hits.publisher.adsenseBackfillDfpViewableImpressions checking-icon  
hits.publisher.adxBackfillDfpClicks checking-icon  
hits.publisher.adxBackfillDfpImpressions checking-icon  
hits.publisher.adxBackfillDfpMatchedQueries checking-icon  
hits.publisher.adxBackfillDfpMeasurableImpressions checking-icon  
hits.publisher.adxBackfillDfpPagesViewed checking-icon  
hits.publisher.adxBackfillDfpQueries checking-icon  
hits.publisher.adxBackfillDfpRevenueCpc checking-icon  
hits.publisher.adxBackfillDfpRevenueCpm checking-icon  
hits.publisher.adxBackfillDfpViewableImpressions checking-icon  
hits.publisher.dfpAdGroup checking-icon  
hits.publisher.dfpAdUnits checking-icon  
hits.publisher.dfpClicks checking-icon  
hits.publisher.dfpImpressions checking-icon  
hits.publisher.dfpMatchedQueries checking-icon  
hits.publisher.dfpMeasurableImpressions checking-icon  
hits.publisher.dfpNetworkId checking-icon  
hits.publisher.dfpPagesViewed checking-icon  
hits.publisher.dfpQueries checking-icon  
hits.publisher.dfpRevenueCpc checking-icon  
hits.publisher.dfpRevenueCpm checking-icon  
hits.publisher.dfpViewableImpressions checking-icon  
hits.timingInfo   checking-icon
hits.timingInfo.timingCategory   checking-icon
hits.timingInfo.timingVariable   checking-icon
hits.timingInfo.timingLabel   checking-icon
hits.timingInfo.timingValue   checking-icon
hits.timingInfo.pageLoad   checking-icon
hits.timingInfo.DNS   checking-icon
hits.timingInfo.pageDownload   checking-icon
hits.timingInfo.redirectResponse   checking-icon
hits.timingInfo.TCPConnect   checking-icon
hits.timingInfo.serverResponse   checking-icon
hits.timingInfo.DOMInteractive   checking-icon
hits.timingInfo.contentLoad   checking-icon

1— in OWOX BI Pipeline session data tables.
2— only for properties with the enabled User ID feature where there is no other data.

The choice is up to you!

Each of the above methods has its own unique advantages. In our experience, Google Analytics 360 users sometimes choose to use both methods at the same time, as they allow for solving different tasks. However, if you’re not yet ready to invest in Google Analytics 360, exporting your website data with OWOX BI Pipeline is already an excellent way to benefit from collecting and analyzing raw, unsampled data in Google BigQuery.

14 DAY FREE TRIAL