If you have Analytics Export enabled for your organization, you can use Google Data Studio to visualize your raw analytics data by connecting it to your Amazon S3 bucket.

Set Up Google BigQuery

Google BigQuery is a cloud-hosted database service that will sync the raw analytics data from your Amazon S3 bucket.

  1. Under your BigQuery project, create a New Dataset that will be used to sync and store your analytics data.

2. Create a new table inside the dataset. Select "Empty table" as the source.

3. In the table's "schema" section, enable "Edit as text" and paste the following JSON schema.

[
{
"fields": [
{
"mode": "NULLABLE",
"name": "Name",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "Id",
"type": "STRING"
}
],
"mode": "NULLABLE",
"name": "Network",
"type": "RECORD"
},
{
"fields": [
{
"mode": "NULLABLE",
"name": "Slug",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "Name",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "Id",
"type": "STRING"
}
],
"mode": "NULLABLE",
"name": "Program",
"type": "RECORD"
},
{
"fields": [
{
"fields": [
{
"mode": "NULLABLE",
"name": "MajorVersion",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "Family",
"type": "STRING"
}
],
"mode": "NULLABLE",
"name": "UserAgent",
"type": "RECORD"
},
{
"mode": "NULLABLE",
"name": "Source",
"type": "STRING"
},
{
"fields": [
{
"mode": "NULLABLE",
"name": "Family",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "Brand",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "Type",
"type": "STRING"
}
],
"mode": "NULLABLE",
"name": "Device",
"type": "RECORD"
},
{
"mode": "NULLABLE",
"name": "IpAddress",
"type": "STRING"
},
{
"fields": [
{
"mode": "NULLABLE",
"name": "MajorVersion",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "Family",
"type": "STRING"
}
],
"mode": "NULLABLE",
"name": "OS",
"type": "RECORD"
},
{
"mode": "NULLABLE",
"name": "RawDownloadUrl",
"type": "STRING"
},
{
"description": "bq-datetime",
"mode": "NULLABLE",
"name": "DownloadedAtUtc",
"type": "TIMESTAMP"
},
{
"mode": "NULLABLE",
"name": "EmbeddedUrl",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "RawReferrerUrl",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "RawUserAgent",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "MemberId",
"type": "STRING"
}
],
"mode": "NULLABLE",
"name": "Download",
"type": "RECORD"
},
{
"fields": [
{
"mode": "NULLABLE",
"name": "Slug",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "Title",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "Id",
"type": "STRING"
}
],
"mode": "NULLABLE",
"name": "Playlist",
"type": "RECORD"
},
{
"fields": [
{
"mode": "NULLABLE",
"name": "AudioDuration",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "Slug",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "AudioDurationSeconds",
"type": "FLOAT"
},
{
"mode": "NULLABLE",
"name": "Title",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "Id",
"type": "STRING"
}
],
"mode": "NULLABLE",
"name": "Clip",
"type": "RECORD"
},
{
"mode": "NULLABLE",
"name": "EventId",
"type": "STRING"
}
]

Once the table has been created, you can then set up a BigQuery Data Transfer to automatically sync data from Amazon S3

Set up the BigQuery Data Transfer

  1. Select "Data Transfers" from the BigQuery menu, and click Create Transfer.

  2. Select "Amazon S3" as the Data Source and set up the transfer schedule of your choice.

  3. Populate the Destination Settings as follows, replacing your S3 URI, Access Key ID and Secret access key appropriately. Note that the Destination Table should match the table name you created previously.

Once you create the data transfer, it will automatically run and import data from your S3 bucket into the BigQuery table. You can verify this import once its complete by navigating to the Table Preview

Once you have verified that your table is getting populated, you can start querying the data in Google Data Studio

Setting Up Google Data Studio

  1. Create a Blank Report using the BigQuery connector. Find the table that you created inside your dataset through the Big Query browser, then select Add.

  2. You can now create new Charts and Tables from the BigQuery data source in Google Data Studio

Did this answer your question?