Hi,
Can ChartFactor visualize nested table structures in BigQuery? I am considering using ChartFactor and some of my tables include nested arrays of objects.
Thanks.
Visualizing nested structures
Jack
#1
jorge
#2
Hi @Jack,
Yes, you can visualize nested structures in BigQuery. To get a sense of the depth of ChartFactor’s support for nested structures, use the public Google Analytics dataset. The easiest is to prototype using Studio.
For example, you can aggregate and visualize the total visits stored within the totals object. You can also aggregate and visualize array elements within arrays, for example hits.product.productSKU. I am copying the structure of a Google Analytics record below with the nested fields I mentioned.
{
"visitNumber": "1",
"visitId": "1470117657",
"visitStartTime": "1470117657",
"date": "20160801",
"totals": {
"visits": "1",
"hits": "3",
"pageviews": "3",
"timeOnSite": "54",
"newVisits": "1"
},
"trafficSource": {
"referralPath": "/yt/about/",
"campaign": "(not set)",
"source": "youtube.com",
"medium": "referral",
"adwordsClickInfo": {
"criteriaParameters": "not available in demo dataset"
}
},
"device": {
"browser": "Internet Explorer",
"browserVersion": "not available in demo dataset",
"browserSize": "not available in demo dataset",
"operatingSystem": "Windows",
"operatingSystemVersion": "not available in demo dataset",
"isMobile": false,
"mobileDeviceBranding": "not available in demo dataset",
"mobileDeviceModel": "not available in demo dataset",
"mobileInputSelector": "not available in demo dataset",
"mobileDeviceInfo": "not available in demo dataset",
"mobileDeviceMarketingName": "not available in demo dataset",
"flashVersion": "not available in demo dataset",
"language": "not available in demo dataset",
"screenColors": "not available in demo dataset",
"screenResolution": "not available in demo dataset",
"deviceCategory": "desktop"
},
"geoNetwork": {
"continent": "Americas",
"subContinent": "Northern America",
"country": "United States",
"region": "not available in demo dataset",
"metro": "not available in demo dataset",
"city": "not available in demo dataset",
"cityId": "not available in demo dataset",
"networkDomain": "telia.net",
"latitude": "not available in demo dataset",
"longitude": "not available in demo dataset",
"networkLocation": "not available in demo dataset"
},
"customDimensions": [
{
"index": "4",
"value": "North America"
}
],
"hits": [
{
"hitNumber": "1",
"time": "0",
"hour": "23",
"minute": "0",
"isInteraction": true,
"isEntrance": true,
"referer": "https://www.youtube.com/yt/about/",
"page": {
"pagePath": "/home",
"hostname": "shop.googlemerchandisestore.com",
"pageTitle": "Home",
"pagePathLevel1": "/home",
"pagePathLevel2": "",
"pagePathLevel3": "",
"pagePathLevel4": ""
},
"appInfo": {
"screenName": "shop.googlemerchandisestore.com/home",
"landingScreenName": "shop.googlemerchandisestore.com/home",
"exitScreenName": "shop.googlemerchandisestore.com/signin.html",
"screenDepth": "0"
},
"exceptionInfo": {
"isFatal": true
},
"product": [],
"promotion": [],
"eCommerceAction": {
"action_type": "0",
"step": "1"
},
"experiment": [],
"customVariables": [],
"customDimensions": [],
"customMetrics": [],
"type": "PAGE",
"social": {
"socialNetwork": "YouTube",
"hasSocialSourceReferral": "Yes",
"socialInteractionNetworkAction": " : "
},
"contentGroup": {
"contentGroup1": "(not set)",
"contentGroup2": "(not set)",
"contentGroup3": "(not set)",
"contentGroup4": "(not set)",
"contentGroup5": "(not set)",
"previousContentGroup1": "(entrance)",
"previousContentGroup2": "(entrance)",
"previousContentGroup3": "(entrance)",
"previousContentGroup4": "(entrance)",
"previousContentGroup5": "(entrance)"
},
"publisher_infos": []
},
{
"hitNumber": "2",
"time": "30507",
"hour": "23",
"minute": "1",
"isInteraction": true,
"page": {
"pagePath": "/google+redesign/gift+cards",
"hostname": "shop.googlemerchandisestore.com",
"pageTitle": "Gift Cards",
"pagePathLevel1": "/google+redesign/",
"pagePathLevel2": "/gift+cards",
"pagePathLevel3": "",
"pagePathLevel4": ""
},
"transaction": {
"currencyCode": "USD"
},
"item": {
"currencyCode": "USD"
},
"appInfo": {
"screenName": "shop.googlemerchandisestore.com/google+redesign/gift+cards",
"landingScreenName": "shop.googlemerchandisestore.com/home",
"exitScreenName": "shop.googlemerchandisestore.com/signin.html",
"screenDepth": "0"
},
"exceptionInfo": {
"isFatal": true
},
"product": [
{
"productSKU": "GGOEGGCX056299",
"v2ProductName": "Gift Card - $25.00",
"v2ProductCategory": "Home/Gift Cards/",
"productVariant": "(not set)",
"productBrand": "(not set)",
"productPrice": "25000000",
"localProductPrice": "25000000",
"isImpression": true,
"customDimensions": [],
"customMetrics": [],
"productListName": "Category",
"productListPosition": "1"
},
{
"productSKU": "GGOEGGCX056499",
"v2ProductName": "Gift Card - $50.00",
"v2ProductCategory": "Home/Gift Cards/",
"productVariant": "(not set)",
"productBrand": "(not set)",
"productPrice": "50000000",
"localProductPrice": "50000000",
"isImpression": true,
"customDimensions": [],
"customMetrics": [],
"productListName": "Category",
"productListPosition": "2"
},
{
"productSKU": "GGOEGGCX056199",
"v2ProductName": "Gift Card- $100.00",
"v2ProductCategory": "Home/Gift Cards/",
"productVariant": "(not set)",
"productBrand": "(not set)",
"productPrice": "100000000",
"localProductPrice": "100000000",
"isImpression": true,
"customDimensions": [],
"customMetrics": [],
"productListName": "Category",
"productListPosition": "3"
},
{
"productSKU": "GGOEGGCX056399",
"v2ProductName": "Gift Card - $250.00",
"v2ProductCategory": "Home/Gift Cards/",
"productVariant": "(not set)",
"productBrand": "(not set)",
"productPrice": "250000000",
"localProductPrice": "250000000",
"isImpression": true,
"customDimensions": [],
"customMetrics": [],
"productListName": "Category",
"productListPosition": "4"
}
],
"promotion": [],
"eCommerceAction": {
"action_type": "0",
"step": "1"
},
"experiment": [],
"customVariables": [],
"customDimensions": [],
"customMetrics": [],
"type": "PAGE",
"social": {
"socialNetwork": "YouTube",
"hasSocialSourceReferral": "Yes",
"socialInteractionNetworkAction": " : "
},
"contentGroup": {
"contentGroup1": "(not set)",
"contentGroup2": "(not set)",
"contentGroup3": "(not set)",
"contentGroup4": "(not set)",
"contentGroup5": "(not set)",
"previousContentGroup1": "(not set)",
"previousContentGroup2": "(not set)",
"previousContentGroup3": "(not set)",
"previousContentGroup4": "(not set)",
"previousContentGroup5": "(not set)"
},
"publisher_infos": []
},
{
"hitNumber": "3",
"time": "53670",
"hour": "23",
"minute": "1",
"isInteraction": true,
"isExit": true,
"page": {
"pagePath": "/signin.html",
"hostname": "shop.googlemerchandisestore.com",
"pageTitle": "The Google Merchandise Store - Log In",
"pagePathLevel1": "/signin.html",
"pagePathLevel2": "",
"pagePathLevel3": "",
"pagePathLevel4": ""
},
"appInfo": {
"screenName": "shop.googlemerchandisestore.com/signin.html",
"landingScreenName": "shop.googlemerchandisestore.com/home",
"exitScreenName": "shop.googlemerchandisestore.com/signin.html",
"screenDepth": "0"
},
"exceptionInfo": {
"isFatal": true
},
"product": [],
"promotion": [],
"eCommerceAction": {
"action_type": "0",
"step": "1"
},
"experiment": [],
"customVariables": [],
"customDimensions": [],
"customMetrics": [],
"type": "PAGE",
"social": {
"socialNetwork": "YouTube",
"hasSocialSourceReferral": "Yes",
"socialInteractionNetworkAction": " : "
},
"contentGroup": {
"contentGroup1": "(not set)",
"contentGroup2": "(not set)",
"contentGroup3": "(not set)",
"contentGroup4": "(not set)",
"contentGroup5": "(not set)",
"previousContentGroup1": "(not set)",
"previousContentGroup2": "(not set)",
"previousContentGroup3": "(not set)",
"previousContentGroup4": "(not set)",
"previousContentGroup5": "(not set)"
},
"publisher_infos": []
}
],
"fullVisitorId": "7194065619159478122",
"channelGrouping": "Social",
"socialEngagementType": "Not Socially Engaged"
}