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.
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.
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.
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.
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.
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.
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!
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.
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.
I’ve thought a lot recently about what it means to have a truly comprehensive, integrated…
Every marketer understands the crucial role high-quality content plays in their marketing strategy. Whether it's…
You have probably heard the phrase “don’t judge a book by its cover.” Yet, 48%…
We, the people who work at Portent, have been pushing our clients to pay close…
This series was originally written by Ian Lurie in 2015 and updated by Chad Kearns…
This series was originally written by Ian Lurie in 2015 and updated by Chad Kearns…