Importing Google Analytics Data to Google BigQuery: Comparing Two Methods of Data Collection

10
832

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. Data exported 3 times a day. In this case, 1 file is exported each day with the previous day’s data, and 3 files are exported each day with the current day’s data. 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.
  2. Data exported continuously. In this case, the data exported continuously approximately every 10 to 15 minutes. The data from AdWords, DFA, DFP, etc. is not available. 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.

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
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
socialEngagementType 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
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  
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-icon checking-icon
trafficSource.dclid   checking-icon
trafficSource.isTrueDirect 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.cityId checking-icon  
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  
device.mobileDeviceInfo checking-icon  
device.mobileDeviceMarketingName checking-icon  
device.mobileDeviceModel checking-icon  
customDimensions checking-icon  
customDimensions.index checking-icon  
customDimensions.value checking-icon  
hits.hitId   checking-icon
hits.dataSource checking-icon checking-icon
hits.queueTime   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.refund.localRefundAmount checking-icon  
hits.refund.refundAmount checking-icon  
hits.experiment.experimentId checking-icon checking-icon
hits.experiment.experimentVariant checking-icon checking-icon
hits.sourcePropertyInfo.sourcePropertyDisplayName checking-icon  
hits.sourcePropertyInfo.sourcePropertyTrackingId checking-icon checking-icon
hits.exceptionInfo.description checking-icon checking-icon
hits.exceptionInfo.isFatal 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.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.promotionActionInfo.promoIsView checking-icon  
hits.promotionActionInfo.promoIsClick 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.contentDescription checking-icon 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.index checking-icon checking-icon
hits.customDimensions.value checking-icon checking-icon
hits.customMetrics.index checking-icon checking-icon
hits.customMetrics.value checking-icon checking-icon
hits.contentGroup.contentGroupXX checking-icon checking-icon1
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.productRevenue 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.index checking-icon checking-icon
hits.product.customDimensions.value checking-icon checking-icon
hits.product.customMetrics.index checking-icon checking-icon
hits.product.customMetrics.value 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 checking-icon
hits.exceptionInfo.exceptions checking-icon  
hits.exceptionInfo.fatalExceptions checking-icon  
hits.customVariables.index checking-icon  
hits.customVariables.customVarName checking-icon  
hits.customVariables.customVarValue 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  
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.dfpClickshits.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  

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.