Using R to Visualize Google BigQuery Export Schemas

BigQuery is playing an increasingly vital role in the data strategy of many organizations. In addition to importing your own data sources, BigQuery allows you to easily configure automated imports of Google data sources, including AdWords, DoubleClick Bid Manager, DoubleClick for Publishers, YouTube Analytics, and as key data sources – Google Analytics and Firebase.

With your Google Analytics 360 or Firebase data in BigQuery, you run advanced queries to understand, as one example, conversions across multiple sessions, and you can also integrate with CRM data to measure, as another example, customer lifetime value by traffic source of the user’s original conversion.

As you query your Google Analytics or Firebase data in BigQuery, you can use this view of the schema as a quick reference. We hope that it will prove useful for beginners and experts alike.

Solution Map

This visual was built by doing the following with R (source code):

Automation

This solution can be taken one step further and process can be automated by hosting and scheduling the refresh of the R scripts on a Google Compute Engine instance of this can be converted to R Shiny app so that the BigQuery schema data is scraped/refreshed each time a user accesses the app.

Visualizing Other Schemas

Additionally, one could use this approach to visualize and explore the following schemas and nominal data (e.g. – categorical or dimensional):

• OAuth 2.0 Scopes for Google API’s
• DoubleClick, YouTube (via BigQuery Data Transfer Service)