bigquery

In this post, we’re sharing report templates that you can build with SQL queries to Google BigQuery data.

在本文中，我們將分享您可以使用SQL查詢為Google BigQuery數據構建的報告模板。

First, you’ll find out about what you can calculate with the standard export from GA360 to GBQ.

首先，您將了解從GA360到GBQ的標準導出可以計算出的內容。

Next, you’ll discover what unique metrics can be added to the standard Google Analytics ones, with the help of OWOX BI Pipeline.

接下來，您將發現借助OWOX BI Pipeline可以將哪些獨特的指標添加到標準Google Analytics(分析)中。

基于從Google Analytics 360到BigQuery的標準導出的報告 (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.

盡管您可能會遇到抽樣 ，數據匯總和其他限制 ，但Google Analytics(分析)報告既方便又方便。 小心！ 有一個出路：使用標準數據導出到Google BigQuery來規避限制并通過SQL請求構建更全面的報告。

1.跨任何維度的用戶行為 (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:

假設您已實施新指標或更新了網站上的現有指標，以衡量對您的業務很重要的KPI。 您可以使用網站上命中數變化的報告來查看數據是否正確發送，并對任何分析錯誤及時作出React。 您需要此報表類型的以下維度：

• device.deviceCategory.
  device.deviceCategory。
• device.browser.
  設備瀏覽器。
• hits.type.
  hits.type。
• eventCategory.
  eventCategory。
• eventAction.
  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.

常規的Google Analytics(分析)報告不會為您提供上述所有信息，因為它只能同時選擇2個維度，而自定義報告則建議多一些-5個維度。 SQL查詢沒有這樣的限制，可以吸引您提取報表中想要的所有指標和維度。

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:

我們在PDF文件中提供SQL查詢模板將幫助您了解會話，用戶和匹配數據如何在瀏覽器，設備和匹配類型之間分配。 如果需要，可以向查詢添加更多維度。 例如：

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

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

通過添加這些尺寸，您將獲得下表：

Image courtesy of the author圖片由作者提供

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.

您還可以將信息從CRM或ERP系統導入Google BigQuery。 因此，您將能夠通過所需的任何指標來分析用戶行為：產品目錄，利潤，產品類別，用戶描述，訂單完成等。例如，您發送查詢以獲取交易數據，然后將其與從CRM中還清訂單，并計算在線完成訂單的百分比。 這將幫助您了解從下訂單到付款或交付的過程中是否存在任何問題。

2.關鍵用戶動作統計 (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.

如果要建立用戶群并設置個性化新聞通訊，則需要有關在線用戶行為的信息。 詳細信息越多，細分的選項就越多。 Google Analytics(分析)不會為您提供有關所有點擊類型的所有用戶操作(如瀏覽量，事件，社交，時間安排以及增強型電子商務事件(點擊，詳細信息，添加，刪除，結帳，購買，退款))的報告。 您也無法查看特定用戶的點擊類型的平均值，最大值或最小值。 但是，由于您可以在本文所附的PDF文件中找到一個SQL查詢，因此所有這些信息都將可用。

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:

使用相同SQL查詢，您還可以為每個會話計算以下值：

• 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.選擇訪問某些產品頁面的用戶 (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.

要完善您的銷售渠道，您需要在購買前對用戶在您網站上的行為給予適當的評價。 GA中的“用戶資源管理器”報告將為您提供有關所有類型的用戶操作(網頁瀏覽，事件，社交，時間安排)的信息。 但是，這可能很復雜且很耗時，因為您將僅看到單個用戶的此類統計信息，為此應用了高級細分和產品ID過濾器。

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.

但是，根據從GA360到Google BigQuery的標準導出來建立報告，將為您提供有關網站上所有用戶操作的數據，并通過GA代碼進行跟蹤。 例如，我們之前討論SQL查詢將幫助您對查看過某些產品頁面的用戶進行分組。 因此，您將提醒那些沒有付款就將商品留在購物車中的用戶，他們會推薦他們感興趣的產品，并推薦其他產品，甚至進行客戶分析。

4.購買某種產品的用戶的行為 (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.

前段中的所有前述內容也適用于本報告。 使用我們PDF文件中SQL查詢，您將構建一個報告，其中包含購買了確定商品的用戶的姓名。 這些名稱將使您可以向用戶提供與已購買產品相似的產品或附件。

5.設置程序，使其更適合您的業務 (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.

假設您已經在博客中發布了一篇文章，以激勵您的客戶訂閱新聞通訊。 要跟蹤這種轉換，您將需要兩個事件：閱讀文章和訂閱。 事實是您不能在Google Analytics(分析)中將這兩者結合在一起，因為它們是兩種不同的目標類型-Pageview和Event。

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

此外，在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.
  每個GA視圖最多可以配置20個目標。 這些目標無法刪除，您只能停止為它們收集數據。
• 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.

將數據導出到Google BigQuery和簡單SQL查詢將幫助您規避上述限制。 您可以設置任何必要步驟的渠道，以在網站上找到任何窄軌，并了解用戶最容易流失的階段。

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:

1.產品頁面視圖→2.產品描述視圖→3.將產品添加到購物車。 不過，您可以將任何用戶操作設置為跟蹤網站上的渠道步驟。 結果，您將得到一個類似的圖形：

Image courtesy of the author圖片由作者提供

6.網站內部搜索的效率 (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.

GA報告中的維度和指標必須處于同一級別：匹配級別，會話級別，用戶級別或產品級別范圍。 這意味著，在具有維度的單個報告中，例如，您無法查看跨會話ID的產品SKU或搜索查詢排名。

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:

SQL查詢還將為您提供以下數據：

• 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.
  整個搜索結果中搜索查詢的排名。

基于從OWOX BI Google Analytics到Google BigQuery的管道數據的報告 (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.

如果您尚未訂閱GA 360，但確實愿意用您的業務所需的其他信息來豐富您的GA數據，那么OWOX BI Pipeline將為您提供必要的原始數據，并為您提供必要的原始數據。 事實是，Google Analytics(分析)僅允許跨廣告系列導入匯總的費用數據，而標準導出架構則根本不提供此類信息。

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.

使用OWOX BI Pipeline，您可以合并來自所有廣告平臺的數據并將其發送到Google BigQuery，甚至可以查看所有會話的費用。 這將使您可以將支出和收入分為用戶級別，細分受眾群或同類群組，甚至是登陸頁面級別。

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.

下面，我們提供無法通過標準導出來計算的指標，但是使用OWOX BI。 以下所有報告均可通過SQL查詢或OWOX BI Smart Data的簡單問題獲得。

1.歸因廣告成本如何按來源，媒介和天數變化？ (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:

這是OWOX BI Smart Data的報告示例：

Image courtesy of the author圖片由作者提供

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.

從上圖可以看出，6月7日的facebook / cpc費用急劇下降。 這可能是市場營銷專家檢查確定來源的哪些廣告系列，廣告組和關鍵字開始帶來較少點擊的標志。 可能是由于競爭對手的廣告導致廣告觀看次數減少了。 為解決此問題，營銷專家可以調整出價，更新廣告內容等。

2.按城市劃分的訪問者平均費用是多少？ (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.

如前所述，Google Analytics(分析)和向BigQuery的標準導出不允許您計算每次會話，用戶和同類群組的費用。 但是，OWOX BI一定會讓您這樣做。

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:

借助下面的報告，您可以了解各個城市和時段的平均客戶獲取成本。 在OWOX BI Smart Data界面中查看外觀：

Image courtesy of the author圖片由作者提供

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.

通常，廣告系列對每個區域都有不同的設置。 上述報告將使您知道要注意的區域。 例如，從上圖可以看到達拉斯的客戶獲取成本(CAC)最高。 這意味著您應該仔細研究該地區的廣告系列，計算每個廣告系列的每位用戶平均收入，CAC，并將其與客戶生命周期價值(LTV)進行比較。 如果廣告系列無法帶來回報，您可以調整出價或僅停用某些廣告。

3.按廣告系列劃分的毛利潤的廣告支出回報率是多少？ (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:

這樣的報告可以幫助您評估廣告的效果，并通過CRM的訂單完成率和產品主要成本來查看廣告系列的貢獻。 要獲取此報告，您需要首先將有關訂單的數據從CRM或ERP系統導入到Google BigQuery。 在下面，您可以查看報告在OWOX BI Smart Data界面中的外觀：

Image courtesy of the author圖片由作者提供

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.按交易付款類型和交易交付類型，CRM中的交易計數是多少？ (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:

有了這份報告，您將發現有關下訂單或付款方式的任何問題。 要生成報告，您必須將有關已完成訂單的CRM數據上傳到Google BigQuery。 接下來，您將能夠使用我們SQL模板詢問智能數據問題以及所需的指標，并最終獲得以下報告：

Image courtesy of the author圖片由作者提供

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.

此氣泡圖顯示用戶使用信用卡(onlineCard)支付三到四次，每次四次：選擇客戶提貨或郵局提貨遞送選項時。 但是，客戶也可以通過卡付款以選擇快遞方式。 如果不使用此替代方法，則您的網站上可能會遇到一些問題。

5.每個城市的平均交貨時間是多少？ (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.

對于此報告，您還需要有關CRM系統中已完成訂單的數據。 該報告將使您檢查某些城市是否有任何運送問題。

Image courtesy of the author圖片由作者提供

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:

當克服默認的Google Analytics(分析)限制并創建用于深入分析的報告時，將標準數據導出到Google BigQuery和SQL查詢以及OWOX BI工具非常有用。 例如，您可以：

• 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 as help you see:

OWOX BI Pipeline將豐富您的Google BigQuery數據，并幫助您查看：

• 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.
  毛利率ROAS如何取決于廣告系列的來源和媒介。
• How the order number in the CRM system depends on the payment and delivery options.
  CRM系統中的訂單號如何取決于付款和交貨選項。
• How the average delivery time depends on the city.
  平均交貨時間如何取決于城市。

翻譯自: https://towardsdatascience.com/templates-of-sql-queries-to-google-bigquery-that-will-take-your-reports-to-another-level-d0ebc1b8957d

bigquery

總結

以上是生活随笔為你收集整理的bigquery_到Google bigquery的sql查询模板，它将您的报告提升到另一个层次的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。