Google Analytics premium BiqQuery ga_sessions table export fields changelog

gistfile1.sh

#!/bin/bash

# Somehow Google does not provide a changelog for when they add fields to the ga_sessions table
# export you get with Google Analitics premium. Here's some of it by trial and error... :(

COLUMNS="
visitorId,
visitNumber,
visitId,
visitStartTime,
date,
totals.visits,
totals.hits,
totals.pageviews,
totals.timeOnSite,
totals.bounces,
totals.transactions,
totals.transactionRevenue,
totals.newVisits,
trafficSource.referralPath,
trafficSource.campaign,
trafficSource.source,
trafficSource.medium,
trafficSource.keyword,
trafficSource.adContent,
device.browser,
device.browserVersion,
device.operatingSystem,
device.operatingSystemVersion,
device.isMobile,
device.flashVersion,
device.javaEnabled,
device.language,
device.screenColors,
device.screenResolution,
device.deviceCategory,
customDimensions.index,
customDimensions.value,
hits.hitNumber,
hits.time,
hits.hour,
hits.minute,
hits.isSecure,
hits.isInteraction,
hits.referer,
hits.page.pagePath,
hits.page.hostname,
hits.page.pageTitle,
hits.page.searchKeyword,
hits.page.searchCategory,
hits.transaction.transactionId,
hits.transaction.transactionRevenue,
hits.transaction.transactionTax,
hits.transaction.transactionShipping,
hits.transaction.affiliation,
hits.transaction.currencyCode,
hits.transaction.localTransactionRevenue,
hits.transaction.localTransactionTax,
hits.transaction.localTransactionShipping,
hits.transaction.transactionCoupon,
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.contentInfo.contentDescription,
hits.appInfo.name,
hits.appInfo.version,
hits.appInfo.id,
hits.appInfo.installerId,
hits.exceptionInfo.description,
hits.exceptionInfo.isFatal,
hits.eventInfo.eventCategory,
hits.eventInfo.eventAction,
hits.eventInfo.eventLabel,
hits.eventInfo.eventValue,
hits.product.productSKU,
hits.product.v2ProductName,
hits.product.v2ProductCategory,
hits.product.productVariant,
hits.product.productBrand,
hits.product.productRevenue,
hits.product.localProductRevenue,
hits.product.productPrice,
hits.product.localProductPrice,
hits.product.productQuantity,
hits.product.productRefundAmount,
hits.product.localProductRefundAmount,
hits.product.isImpression,
hits.promotion.promoId,
hits.promotion.promoName,
hits.promotion.promoCreative,
hits.promotion.promoPosition,
hits.refund.refundAmount,
hits.refund.localRefundAmount,
hits.eCommerceAction.action_type,
hits.eCommerceAction.step,
hits.eCommerceAction.option,
hits.customVariables.index,
hits.customVariables.customVarName,
hits.customVariables.customVarValue,
hits.customDimensions.index,
hits.customDimensions.value,
hits.customMetrics.index,
hits.customMetrics.value,
hits.type,
hits.social.socialInteractionNetwork,
hits.social.socialInteractionAction,
fullVisitorId,
"

EXTRA_CULUMNS_NULL="
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
"

EXTRA_CULUMNS_20140701="
geoNetwork.continent,
geoNetwork.subContinent,
geoNetwork.country,
geoNetwork.region,
geoNetwork.metro,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
"

EXTRA_CULUMNS_20140916="
geoNetwork.continent,
geoNetwork.subContinent,
geoNetwork.country,
geoNetwork.region,
geoNetwork.metro,
totals.screenviews,
totals.UniqueScreenViews,
totals.TimeOnScreen,
hits.appInfo.appName,
hits.appInfo.appVersion,
hits.appInfo.appId,
hits.appInfo.screenName,
hits.appInfo.landingScreenName,
hits.appInfo.exitScreenName,
hits.appInfo.screenDepth,
NULL,
NULL
"

EXTRA_CULUMNS_20141106="
geoNetwork.continent,
geoNetwork.subContinent,
geoNetwork.country,
geoNetwork.region,
geoNetwork.metro,
totals.screenviews,
totals.UniqueScreenViews,
totals.TimeOnScreen,
hits.appInfo.appName,
hits.appInfo.appVersion,
hits.appInfo.appId,
hits.appInfo.screenName,
hits.appInfo.landingScreenName,
hits.appInfo.exitScreenName,
hits.appInfo.screenDepth,
device.mobileDeviceBranding,
NULL
"

EXTRA_CULUMNS_20150423="
geoNetwork.continent,
geoNetwork.subContinent,
geoNetwork.country,
geoNetwork.region,
geoNetwork.metro,
totals.screenviews,
totals.UniqueScreenViews,
totals.TimeOnScreen,
hits.appInfo.appName,
hits.appInfo.appVersion,
hits.appInfo.appId,
hits.appInfo.screenName,
hits.appInfo.landingScreenName,
hits.appInfo.exitScreenName,
hits.appInfo.screenDepth,
device.mobileDeviceBranding,
totals.totalTransactionRevenue
"

# GA added fields changelog... not to be found on the interwebs... :(

# 20140701
# geoNetwork.continent,
# geoNetwork.subContinent,
# geoNetwork.country,
# geoNetwork.region,
# geoNetwork.metro

# 20140916
# totals.screenviews,
# totals.UniqueScreenViews,
# totals.TimeOnScreen,
# hits.appInfo.appId,
# hits.appInfo.appName,
# hits.appInfo.appVersion,
# hits.appInfo.appId,
# hits.appInfo.screenName,
# hits.appInfo.landingScreenName,
# hits.appInfo.exitScreenName,
# hits.appInfo.screenDepth,

# 20141106
# device.mobileDeviceBranding

# 20150423
# totals.totalTransactionRevenue

# handy to do an export of ga_sessions to csv and load into an DWH
# can be used with a flatten query like:

table_date=${TABLE_DATE:-'20140915'}  # some date
columns="${COLUMNS}"
if [[ ${table_date} -lt '20150423' ]]; then
    if [[ ${table_date} -lt '20141106' ]]; then
        if [[ ${table_date} -lt '20140916' ]]; then
            if [[ ${table_date} -lt '20140701' ]]; then
                columns="${columns}${EXTRA_CULUMNS_NULL}"
            else
                columns="${columns}${EXTRA_CULUMNS_20140701}"
            fi
        else
            columns="${columns}${EXTRA_CULUMNS_20140916}"
        fi
    else
        columns="${columns}${EXTRA_CULUMNS_20141106}"
    fi
else
    columns="${columns}${EXTRA_CULUMNS_20150423}"
fi
echo bq query "select ${columns} from flatten(flatten(flatten(flatten(flatten(flatten([YOUR_ACCOUNT_ID_HERE.ga_sessions_${table_date}],hits),customDimensions),hits.product),hits.promotion),hits.customVariables),hits.customDimensions)";
# extra params to use for exporting to another table (create the ga_sessions_flattened schema first): --allow_large_results --format=csv --destination_table=ga_sessions_flattened.${table_date}