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:
- BigQuery Export for Analytics, or
- 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:
- 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.
- 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:
- 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.
- 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.
- The data can be collected on an individual domain , e.g. stat.yourdomain.com, in the protected area of your corporate network.
- You can send more than 20 custom parameters in comparison with the standard version of Google Analytics.
- 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.
- 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 |
 2 |
|
clientId |
|
|
sessionId |
1 |
|
fullVisitorId |
|
|
visitId |
|
|
visitStartTime |
|
|
visitNumber |
|
|
newVisits |
1 |
|
user |
|
|
user.phone |
|
|
user.email |
|
|
user.owoxId |
|
|
date |
|
|
socialEngagementType |
|
|
totals |
|
|
totals.visits |
|
|
totals.hits |
|
|
totals.transactions |
|
|
totals.events |
|
|
totals.pageviews |
|
|
totals.screenviews |
|
|
totals.transactionRevenue |
|
|
totals.sessionQualityDim |
|
|
totals.timeOnSite |
|
|
totals.bounces |
|
|
totals.totalTransactionRevenue |
|
|
totals.newVisits |
|
|
totals.UniqueScreenViews |
|
|
totals.timeOnScreen |
|
|
totals.isInteraction |
1 |
|
totalsStreaming |
1 |
|
totalsStreaming.hits |
1 |
|
totalsStreaming.events |
1 |
|
totalsStreaming.transactions |
1 |
|
totalsStreaming.pageviews |
1 |
|
totalsStreaming.screenviews |
1 |
|
totalsStreaming.isInteraction |
1 |
|
trafficSource |
|
|
trafficSource.adGroup |
1 |
|
trafficSource.keywordMatchType |
|
|
trafficSource.referralPath |
|
|
trafficSource.campaign |
|
|
trafficSource.source |
|
|
trafficSource.medium |
|
|
trafficSource.keyword |
|
|
trafficSource.adContent |
|
|
trafficSource.campaignCode |
|
|
trafficSource.gclid |
1 |
|
trafficSource.dclid |
1 |
|
trafficSource.isTrueDirect |
|
|
trafficSource.channelGrouping |
|
|
trafficSource.adCost |
1 |
|
trafficSource.attributedAdCost |
1 |
|
trafficSource.adwordsClickInfo |
|
|
trafficSource.adwordsClickInfo.customerId |
|
|
trafficSource.adwordsClickInfo.campaignId |
|
|
trafficSource.adwordsClickInfo.adGroupId |
|
|
trafficSource.adwordsClickInfo.creativeId |
|
|
trafficSource.adwordsClickInfo.criteriaId |
|
|
trafficSource.adwordsClickInfo.page |
|
|
trafficSource.adwordsClickInfo.slot |
|
|
trafficSource.adwordsClickInfo.criteriaParameters |
|
|
trafficSource.adwordsClickInfo.gclId |
|
|
trafficSource.adwordsClickInfo.adNetworkType |
|
|
trafficSource.adwordsClickInfo.adMatchedQuery |
1 |
|
trafficSource.adwordsClickInfo.isVideoAd |
|
|
trafficSource.adwordsClickInfo.targetingCriteria |
|
|
trafficSource.adwordsClickInfo.targetingCriteria.boomUserlistId |
|
|
geo |
|
|
geo.id |
|
|
geoNetwork |
|
|
geoNetwork.country |
|
|
geoNetwork.region |
|
|
geoNetwork.city |
|
|
geoNetwork.cityId |
|
|
geoNetwork.continent |
|
|
geoNetwork.subContinent |
|
|
geoNetwork.metro |
|
|
geoNetwork.latitude |
|
|
geoNetwork.longitude |
|
|
geoNetwork.networkDomain |
|
|
geoNetwork.networkLocation |
|
|
device |
|
|
device.ip |
1 |
|
device.userAgent |
1 |
|
device.flashVersion |
|
|
device.javaEnabled |
|
|
device.language |
|
|
device.screenColors |
|
|
device.screenResolution |
|
|
device.browser |
|
|
device.browserVersion |
|
|
device.browserSize |
|
|
device.deviceCategory |
|
|
device.operatingSystem |
|
|
device.operatingSystemVersion |
|
|
device.mobileDeviceBranding |
|
|
device.mobileInputSelector |
|
|
device.mobileDeviceInfo |
|
|
device.mobileDeviceMarketingName |
|
|
device.mobileDeviceModel |
|
|
customDimensions |
|
|
customDimensions.index |
|
|
customDimensions.value |
|
|
hits |
|
|
hits.hitId |
1 |
|
hits.dataSource |
|
|
hits.device |
|
|
hits.device.ip |
|
|
hits.device.userAgent |
|
|
hits.device.flashVersion |
|
|
hits.device.javaEnabled |
|
|
hits.device.language |
|
|
hits.device.screenColors |
|
|
hits.device.screenResolution |
|
|
hits.geo |
|
|
hits.geo.id |
1 |
|
hits.customGroups |
|
|
hits.customGroups.index |
|
|
hits.customGroups.value |
|
|
hits.contentGroups |
|
|
hits.contentGroups.index |
|
|
hits.contentGroups.value |
|
|
hits.hitNumber |
|
|
hits.type |
|
|
hits.time |
|
|
hits.timestamp |
1 |
|
hits.queueTime |
1 |
|
hits.currency |
1 |
|
hits.referralPath |
1 |
|
hits.hour |
|
|
hits.minute |
|
|
hits.isEntrance |
|
|
hits.isExit |
|
|
hits.isSecure |
|
|
hits.isInteraction |
|
|
hits.referer |
|
|
hits.pagePath |
1 |
|
hits.pageType |
1 |
|
hits.eCommerceActionType |
1 |
|
hits.refund |
|
|
hits.refund.localRefundAmount |
|
|
hits.refund.refundAmount |
|
|
hits.experiment |
|
|
hits.experiment.experimentId |
|
|
hits.experiment.experimentVariant |
|
|
hits.sourcePropertyInfo |
|
|
hits.sourcePropertyInfo.sourcePropertyDisplayName |
|
|
hits.sourcePropertyInfo.sourcePropertyTrackingId |
|
|
hits.exceptionInfo.description |
|
|
hits.exceptionInfo.isFatal |
|
|
hits.eventInfo |
|
|
hits.eventInfo.eventCategory |
|
|
hits.eventInfo.eventAction |
|
|
hits.eventInfo.eventLabel |
|
|
hits.eventInfo.eventValue |
|
|
hits |
|
|
hits.social |
|
|
hits.social.hasSocialSourceReferral |
|
|
hits.social.socialInteractionAction |
|
|
hits.social.socialInteractionNetwork |
|
|
hits.social.socialInteractionNetworkAction |
|
|
hits.social.socialInteractions |
|
|
hits.social.socialInteractionTarget |
|
|
hits.social.socialNetwork |
|
|
hits.social.uniqueSocialInteractions |
|
|
hits.appInfo |
|
|
hits.appInfo.name |
|
|
hits.appInfo.version |
|
|
hits.appInfo.id |
|
|
hits.appInfo.installerId |
|
|
hits.appInfo.screenName |
|
|
hits.appInfo.landingScreenName |
|
|
hits.appInfo.exitScreenName |
|
|
hits.appInfo.screenDepth |
|
|
hits.promotion |
|
|
hits.promotion.promoCreative |
|
|
hits.promotion.promoId |
|
|
hits.promotion.promoName |
|
|
hits.promotion.promoPosition |
|
|
hits.promotionActionInfo |
|
|
hits.promotionActionInfo.promoIsView |
|
|
hits.promotionActionInfo.promoIsClick |
|
|
hits.page |
|
|
hits.page.searchCategory |
|
|
hits.transaction |
|
|
hits.transaction.transactionId |
|
|
hits.transaction.transactionRevenue |
|
|
hits.transaction.transactionTax |
|
|
hits.transaction.transactionShipping |
|
|
hits.transaction.transactionCoupon |
|
|
hits.transaction.affiliation |
|
|
hits.transaction.currencyCode |
|
|
hits.transaction.localTransactionRevenue |
|
|
hits.transaction.localTransactionTax |
|
|
hits.transaction.localTransactionShipping |
|
|
hits.contentInfo |
|
|
hits.contentInfo.contentDescription |
|
|
hits.item |
|
|
hits.item.transactionId |
|
|
hits.item.productName |
|
|
hits.item.productCategory |
|
|
hits.item.productSku |
|
|
hits.item.itemQuantity |
|
|
hits.item.itemRevenue |
|
|
hits.item.currencyCode |
|
|
hits.item.localItemRevenue |
|
|
hits.customDimensions |
|
|
hits.customDimensions.index |
|
|
hits.customDimensions.value |
|
|
hits.customMetrics |
|
|
hits.customMetrics.index |
|
|
hits.customMetrics.value |
|
|
hits.contentGroup.contentGroupX |
|
|
hits.contentGroup.previousContentGroupX |
|
|
hits.contentGroup.contentGroupUniqueViewsX |
|
|
hits.page.pagePath |
|
|
hits.page.pagePathLevel1 |
|
|
hits.page.pagePathLevel2 |
|
|
hits.page.pagePathLevel3 |
|
|
hits.page.pagePathLevel4 |
|
|
hits.page.hostname |
|
|
hits.page.pageTitle |
|
|
hits.page.searchKeyword |
|
|
hits.product |
|
|
hits.product.isImpression |
|
|
hits.product.isClick |
|
|
hits.product.impressionList |
|
|
hits.product.productListName |
|
|
hits.product.productSKU |
|
|
hits.product.productPrice |
|
|
hits.product.productRevenue |
|
|
hits.product.productQuantity |
|
|
hits.product.productBrand |
|
|
hits.product.productVariant |
|
|
hits.product.v2ProductCategory |
|
|
hits.product.v2ProductName |
|
|
hits.product.productListPosition |
|
|
hits.product.productCategory |
|
|
hits.product.productName |
|
|
hits.product.position |
|
|
hits.product.coupon |
|
|
hits.product.localProductPrice |
|
|
hits.product.localProductRefundAmount |
|
|
hits.product.localProductRevenue |
|
|
hits.product.productRefundAmount |
|
|
hits.product.customDimensions |
|
|
hits.product.customDimensions.index |
|
|
hits.product.customDimensions.value |
|
|
hits.product.customMetrics |
|
|
hits.product.customMetrics.index |
|
|
hits.product.customMetrics.value |
|
|
hits.eCommerceAction |
|
|
hits.eCommerceAction.action_type |
|
|
hits.eCommerceAction.option |
|
|
hits.eCommerceAction.step |
|
|
hits.eCommerceAction.list |
|
|
hits.exceptionInfo |
|
|
hits.exceptionInfo.exceptions |
|
|
hits.exceptionInfo.fatalExceptions |
|
|
hits.customVariables |
|
|
hits.customVariables.index |
|
|
hits.customVariables.customVarName |
|
|
hits.customVariables.customVarValue |
|
|
hits.latencyTracking |
|
|
hits.latencyTracking.domainLookupTime |
|
|
hits.latencyTracking.domContentLoadedTime |
|
|
hits.latencyTracking.domInteractiveTime |
|
|
hits.latencyTracking.domLatencyMetricsSample |
|
|
hits.latencyTracking.pageDownloadTime |
|
|
hits.latencyTracking.pageLoadSample |
|
|
hits.latencyTracking.pageLoadTime |
|
|
hits.latencyTracking.redirectionTime |
|
|
hits.latencyTracking.serverConnectionTime |
|
|
hits.latencyTracking.serverResponseTime |
|
|
hits.latencyTracking.speedMetricsSample |
|
|
hits.latencyTracking.userTimingCategory |
|
|
hits.latencyTracking.userTimingLabel |
|
|
hits.latencyTracking.userTimingSample |
|
|
hits.latencyTracking.userTimingValue |
|
|
hits.latencyTracking.userTimingVariable |
|
|
hits.publisher.adsenseBackfillDfpClicks |
|
|
hits.publisher.adsenseBackfillDfpImpressions |
|
|
hits.publisher.adsenseBackfillDfpMatchedQueries |
|
|
hits.publisher.adsenseBackfillDfpMeasurableImpressions |
|
|
hits.publisheradsenseBackfillDfpPagesViewed |
|
|
hits.publisher.adsenseBackfillDfpQueries |
|
|
hits.publisher.adsenseBackfillDfpRevenueCpc |
|
|
hits.publisher.adsenseBackfillDfpRevenueCpm |
|
|
hits.publisher.adsenseBackfillDfpViewableImpressions |
|
|
hits.publisher.adxBackfillDfpClicks |
|
|
hits.publisher.adxBackfillDfpImpressions |
|
|
hits.publisher.adxBackfillDfpMatchedQueries |
|
|
hits.publisher.adxBackfillDfpMeasurableImpressions |
|
|
hits.publisher.adxBackfillDfpPagesViewed |
|
|
hits.publisher.adxBackfillDfpQueries |
|
|
hits.publisher.adxBackfillDfpRevenueCpc |
|
|
hits.publisher.adxBackfillDfpRevenueCpm |
|
|
hits.publisher.adxBackfillDfpViewableImpressions |
|
|
hits.publisher.dfpAdGroup |
|
|
hits.publisher.dfpAdUnits |
|
|
hits.publisher.dfpClicks |
|
|
hits.publisher.dfpImpressions |
|
|
hits.publisher.dfpMatchedQueries |
|
|
hits.publisher.dfpMeasurableImpressions |
|
|
hits.publisher.dfpNetworkId |
|
|
hits.publisher.dfpPagesViewed |
|
|
hits.publisher.dfpQueries |
|
|
hits.publisher.dfpRevenueCpc |
|
|
hits.publisher.dfpRevenueCpm |
|
|
hits.publisher.dfpViewableImpressions |
|
|
hits.timingInfo |
|
|
hits.timingInfo.timingCategory |
|
|
hits.timingInfo.timingVariable |
|
|
hits.timingInfo.timingLabel |
|
|
hits.timingInfo.timingValue |
|
|
hits.timingInfo.pageLoad |
|
|
hits.timingInfo.DNS |
|
|
hits.timingInfo.pageDownload |
|
|
hits.timingInfo.redirectResponse |
|
|
hits.timingInfo.TCPConnect |
|
|
hits.timingInfo.serverResponse |
|
|
hits.timingInfo.DOMInteractive |
|
|
hits.timingInfo.contentLoad |
|
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.
