Templates of SQL Queries to Google BigQuery That Will Take Your Reports to Another Level

If you’re not new to our blog, you probably know that we recommend Google BigQuery as a cloud data warehouse for building an end-to-end analytics system. BigQuery allows you to build reports of any complexity and structure based on complete data, and to tackle tasks that are difficult to handle even with the help of Google Analytics 360.

In this post, we’re sharing report templates that you can build with SQL queries to Google BigQuery data. First, you’ll find out about what you can calculate with the standard export from GA360 to GBQ. Next, you’ll discover what unique metrics can be added to the standard Google Analytics ones, with the help of OWOX BI Pipeline. Finally, you’ll get a chance to download a PDF with all of the query templates. For that, simply enter your email address, and you’ll get the download link.

Reports based on the standard export from Google Analytics 360 to BigQuery

Google Analytics reports are user-friendly and convenient, though you may face sampling, data aggregation, and other limitations. Heads up! There’s a way out: using the standard data export to Google BigQuery to circumvent restrictions and build more comprehensive reports via SQL requests.

1. User behavior across any dimension

Say you’ve implemented new metrics or updated the existing ones on your website, to measure KPIs important to your business. You can use the report on hit number changes on your website to see if the data is sent correctly and to react timely to any analytics errors. You’ll need the following dimensions for this report type:

• device.deviceCategory.
• device.browser.
• hits.type.
• eventCategory.
• eventAction.
• Content Grouping.

A regular Google Analytics report wouldn’t provide you with all the aforementioned information, as it allows to simultaneously choose 2 dimensions only, while a custom report suggests a little more — 5 dimensions. SQL queries don’t have such restrictions, engaging you to pull all metrics and dimensions you’d like in a report.

The SQL query template we’re providing in the PDF file will help you find out about how sessions, users, and hits are distributed among browsers, devices, and hit types. If needed, you can add any more dimensions to the query. For instance:

• device.operatingSystem.
• device.mobileDeviceInfo.
• device.language.
• geoNetwork.region.

By adding these dimensions, you’ll get the following table:

You can also import the information from your CRM or ERP systems to Google BigQuery. Thus, you’ll be able to analyze user behavior across any metrics required: product catalog, margin, product category, user description, order completion, etc. For example, you send a query to get data on transactions, then combine them with the paid off orders from CRM, and calculate the percentage of online completed orders. This will help you understand if there are any issues on the way from placing an order to payment or delivery.

2. Statistics on key user actions

If you want to build up user segments and set up personalized newsletters, you’ll need info on the online user behavior. The more details, the more options for segmentation. Google Analytics won’t provide you with a report on all user actions like pageview, event, social, timing, as well as Enhanced Ecommerce events (click, detail, add, remove, checkout, purchase, refund), across hit types. You can’t also view the average, maximum or minimum values across hit types for a certain user. However, all of that information will be available thanks to one of the SQL queries you’ll find in the PDF file attached to this post.

The aforementioned query will also help you calculate the following average, maximum and minimum values for each user:

• Page views according to a certain search query.
• Page views per visit.
• Number of times adding products to cart.
• Number of times removing products from cart.
• Number of products added to cart.
• Number of products removed from cart.
• Total price of products added to cart.
• Total price of products removed from cart.

Using the very same SQL query, you can also calculate these values for each session:

• Page views according to a certain search query.
• Page views per session.
• Number of times adding to / removing products from cart.
• Number of products added to / removed from cart.
• Total price of products added to / removed from cart.

With such information at hand, you can predict repeated purchases and micro conversions.

3. Selecting users that visited certain product pages

To refine your sales funnel, you need to give a proper credit to what users do on your website before making a purchase. «User Explorer» report in GA will provide you with the info on all types of user actions (pageview, event, social, timing). However, it may be complicated and time consuming, as you’ll get to see this kind of statistics per a single user only, applying advanced segments and product ID filters for that purpose.

However, building up a report, based on the standard export from GA360 to Google BigQuery, will provide you with the data about all user actions on your website, tracked via the GA code. For example, the SQL query we’ve talked about earlier, will help you group users that viewed some product pages. Thus, you’ll get to remind the users, who left the items in cart without paying, of the products they were interested in, as well as recommend other products, and even develop customer profiling.

4. Actions of users who bought a certain product

All the aforementioned in the previous paragraph applies to this report as well. Using the SQL query from our PDF file, you’ll get to build a report that includes names of users who bought a definite item. These names will allow you to offer users similar products or accessories to the products already bought.

5. Setting up a funnel with steps better suited for your business

Say, you’ve posted an article in your blog to motivate your customers to subscribe to a newsletter. To track such a conversion, you’ll need both events: reading the article and the subscription. The thing is that you can’t combine these two in Google Analytics, as they are two different goal types — Pageview and Event.

Moreover, there are other limitations when setting up goals in GA:

• You can configure 20 goals max per one GA view. These goals can’t be deleted, you can only stop collecting data for them.
• In the report, you’ll see the data collected after creating a goal. This means you can’t apply the goal to the data for the previous period.

Exporting data to Google BigQuery and a simple SQL query will help you circumvent the aforementioned restrictions. You can set up a funnel with any steps necessary, to find any narrow-gauges on the website, and learn at what stages users drop off the most.

You’ll also be able to discover how often users view the product description and how such views affect conversion. In our example, the funnel looks this way:

1. Product page view → 2. Product description view → 3. Adding the product to cart. Though, you can set up any user action as a funnel step to track on the website. As a result, you’ll get a similar graph:

6. Efficiency of the internal search on the website

You can increase conversion rates by improving the inner search on the website. For example, analyze the percentage of zero search results and update the website content, add similar products, special offers, and so on. Another option is to switch the category of the top queried products to a higher position, using the report on search queries.

Dimensions and indicators in the GA report need to be of the same level: hit-level, session-level, user-level or product-level scope. This means that in a single report with dimensions, you can’t view, for instance, the product SKU or search query rank across session ID.

The template will help evaluate the performance of the sessions with search queries and to check hypothesis on website usability.

The SQL query will also provide you with this data:

• Number of sessions with a certain query.
• Average number of search results according to the search query.
• Total revenue across sessions with a definite search query.
• Rank of the search query across sessions.
• Rank of the search query across search results.

Reports based on the pipeline data from the OWOX BI Google Analytics to Google BigQuery

If you don’t yet have a GA 360 subscription, but really are willing to enrich your GA data with the additional information needed for your business, OWOX BI Pipeline will help and provide you with the necessary raw data to do that. The thing is that Google Analytics allows importing only aggregated cost data across ad campaigns, while the standard export schema doesn’t provide such information at all.

Using OWOX BI Pipeline, you can combine the data from all of the ad platforms and send it to Google BigQuery, and even see costs across all sessions. This will allow you to group your expenses and revenue user-level, segment or cohort-level, and even landing-page-level.

Below we provide metrics that can’t be calculated by means of standard export, but with OWOX BI. All of the following reports can be obtained with SQL queries or with simple questions to OWOX BI Smart Data.

1. How did attributed ad cost change by sources and mediums and by days?

The report will give you information on costs for a certain period, as well as on metrics changes within a set time. You’ll be able to compare the obtained info with the previous period and evaluate the performance of your ad efforts across sources.

Here’s a report example from OWOX BI Smart Data:

From the graph above you can see that there was a sharp decrease in the facebook/cpc costs on June 7th. This could be a sign for a marketing specialist to check which campaigns, ad groups, and keywords for a definite source started driving fewer clicks. Probably, the number of ad views decreased due to the competitors’ ads. To handle this issue, the marketing expert can adjust bids, update ad content, and so on.

2. What was the average cost of visitor acquisition by city?

As mentioned before, Google Analytics and the standard export to BigQuery won’t allow you to calculate your costs per each session, user, and cohort. However, OWOX BI will surely let you do that.

With the help of the report below, you can learn about the average customer acquisition cost across cities and time periods. Check out how it looks in the OWOX BI Smart Data interface:

Usually, ad campaigns have different settings for each of the regions. The aforementioned report will let you know which region to pay attention to. For example, from the graph above you can see that customer acquisition cost (CAC) for Dallas is the highest. This means that you should probably give a closer look at ad campaigns in this region, calculate the average revenue per user, CAC across ad campaigns, comparing it to the customer lifetime value (LTV). In case ad campaigns don’t pay off, you can adjust bids or simply deactivate some of the ads.

3. What was the ROAS by gross profit by campaign?

Such a report can help you evaluate the performance of your ads and see the campaign contribution with account of order completion rates and product prime cost from CRM. To obtain this report, you’ll need to first import the data about orders from your CRM or ERP systems to Google BigQuery. Below you can see how the report looks within the OWOX BI Smart Data interface:

From the chart you can see how campaigns perform, ranging from the best to the worst ones. Marketers will be able to give a proper credit to each campaign, getting to know why some of them succeed or fail, and to choose the most efficient channels for the future marketing efforts.

4. What was the transaction count in CRM by transaction payment type and transaction delivery type?

With this report at hand, you’ll get to find any issues with placing an order or payment options. To build up the report, you’ll have to upload the CRM data about completed orders to Google BigQuery. Next, you’ll be able to use our SQL template to ask Smart Data questions with the metrics you need and finally getting the following report:

This bubble chart shows that users pay by credit card (onlineCard) three or two times of four: when choosing a customer pick-up or post office pick-up delivery option. However, customers can also pay by card for the courier delivery option. If this alternative is not used, you’ve probably got some issues with it on your website.

5. What was the average delivery time by city?

For this very report, you’ll also need data about completed orders from your CRM system. The report will allow you to check if there are any delivery problems in certain cities.

For example, you can see that delivery in London takes the most time. If the average delivery time is more than stated on your website, you’ll possibly need to check if there’s a local offline store or a warehouse in that region. It could be a good idea to find one, if not in there yet, as it will decrease the delivery time. Another good idea would be to check how satisfied your customers are in this region.

Conclusion

The standard data export to Google BigQuery and SQL queries along with the OWOX BI tools can come in really handy when overcoming the default Google Analytics limitations and creating reports for in-depth analysis. For example, you can:

• Analyze user behavior across any number of parameters.
• Get statistics on key user actions, as well as average, maximum and minimum values for hit types per user.
• Group users according to certain pages they visited, or see user behavior of people who bought a certain product.
• Set up a funnel with all steps necessary.
• Evaluate the performance of inner website search.

OWOX BI Pipeline will enrich your Google BigQuery data, as well help you see:

• What were the changes in the attributed costs across sources and mediums by days.
• How the average customer acquisition cost depends on the city.
• How gross margin ROAS depends on the campaign source and medium.
• How the order number in the CRM system depends on the payment and delivery options.
• How the average delivery time depends on the city.

For greater convenience, we’ve combined all of the aforementioned SQL queries in a PDF file. Simply fill in a short form to get it. Don’t forget to ask any questions you may have in the comment section, we’ll be happy to reply ;)