How to Use BigQuery in Your Digital Marketing Workflow - Portent
Bringing big data into your life as a marketer in small doses.
“Big data” analysis and the insights that come from it can seem untouchable to all but the largest organizations with extensive teams of analysts and data scientists. But there are many ways marketers with some basic understanding of SQL and smaller teams can take advantage of larger data sets without a lot of resources. One of those ways is BigQuery. Let’s dive into a few of the ways anybody can access this technology and build on it.
GA 360 Integration
One of the biggest benefits for shelling out the $150K/year it takes to get a Google Analytics 360 license is being able to liberate your web analytics data from the Analytics UI and perform SQL-style queries on it in BigQuery. There’s also plenty of acceleration to be found in connecting BigQuery tables to Google Data Studio. Visualizing millions of rows becomes lightning fast.
Go to Admin > Property > Product Linking > All Products and choose BigQuery.
Then it allows you to choose which BigQuery project and which GA views to link along with the streaming frequency (i.e., how often you want data sent from GA to BigQuery).
Once the integration is enabled, it can take up to 24 hours to populate the BigQuery tables with all the historical data in your Google Analytics account.
Firebase Integration
If your company has an app or several apps, Firebase has plenty of data you can also ship out to BigQuery for more comprehensive analysis. Firebase’s built-in retention reporting can be limiting, and having that raw data in BigQuery gives app marketers a lot more flexibility.
Go to Admin > Integrations and choose BigQuery to start the connection.
The ensuing settings menu allows you to choose which apps represented in Firebase you want to include in the export. It also lets you decide whether you want to include advertising IDs in your export, which can be really handy for matching up app download ad campaigns with usage.
Once the connection is created, you can find not just the analytics tables in BigQuery, but also a few separate Firebase-specific data sets around crashes and predictive models.
Flat File Integration
Lastly, Google Cloud Storage allows you to upload large .csv files and port them to BigQuery as tables. As marketers, we are often sent large, unwieldy files from vendors or other internal stakeholders that would absolutely melt our laptops if we tried to just open them in Excel, let alone do any meaningful analysis. BigQuery gives us a great way around that by essentially turning these files into a database we can parse through quickly.
Once you upload your flat files to a cloud storage bucket, you can add them to a BigQuery table. Under the advanced options, there’s also a way to tell the uploader if your column headings start several rows down in the file so it can build the schema correctly.
As an example, I’ve uploaded a customer file and want to do some analysis on who checked out as a guest and who created an account.
With the data in BigQuery, I can write a simple SQL query to isolate customers with an account and a domestic address.
After the query runs, I can one-click export that subset of my customer data to Google Data Studio to work on some dashboards or visualizations on that specific, more manageable slice of the data.
BigQuery Resources
You might be reading through this post and are enticed by the prospect of using BigQuery to solve similar problems in your business, but don’t know where to start, or need more learning resources to feel comfortable enabling and working with these integrations. Don’t worry! We’ve got you covered.
GA 360 BigQuery Cookbook
Johan van de Werken from Towards Data Science has a bunch of SQL recipes you can take and repurpose once you get your GA data into BigQuery. This resource was instrumental for me as I was first working with GA 360 data in a database context, and it recreated a lot of the most common reports you would find in the GA UI with some helpful added customizations.
Firebase BigQuery Unnest Function
Todd Kerpelman from the Firebase Developers blog has a great write-up on unpacking Firebase’s nested table structure which will make writing queries into your data a heck of a lot easier once you understand it!
Visualizing BigQuery Tables in Google Data Studio
Shameless plug: I’ve also written a step-by-step walkthrough using flat files with weather data on how to visualize your BigQuery tables. You can find that over on Big Data Made Simple.
Start Querying!
Getting into databases and SQL querying can be really intimidating for marketers, but it’s incredibly empowering not to have to rely on data science and IT teams to get at data sets and start deriving actionable insights out of them. Carve a half-hour of time out of your schedule each week to learn about this technology and find out how you can practically apply it to your business.
This content was originally published here.
