BigQuery export

BigQuery export lets you download the analytics data found on the Overview and Transactions analytics pages.

Enabling BQ Export

1. Enable the BigQuery Sandbox (or upgraded BigQuery)

If BigQuery has not already been enabled, please enable BigQuery Sandbox for your Ordering End-to-End Cloud project. This is where data will be exported.

This must be the same Google Cloud project associated with your Ordering End-to-End integration.

If you need assistance confirming the Cloud project that is linked to your Ordering End-to-End integration, please file a case.

2. Go to BigQuery on Google Cloud console

Once enabled, please open BigQuery on Google Cloud and select the necessary Cloud project using the dropdown in the top left of the screen. https://console.cloud.google.com/bigquery

Bigquery page on cloud console

3. Create Dataset for BigQuery

Please create a dataset in BigQuery called actions_analytics to which Google will export data. The name of the dataset must match exactly actions_analytics.

Create dataset button actions_analytics set as Dataset ID

4. Share access to Dataset

Please share BigQuery Data Editor access to partner-data-exporter-robots@google.com for the dataset. This is needed for Google's pipelines to export data to the BigQuery dataset. To do this, locate the Sharing button for the dataset, open Permissions, and then click Add Principal. Enter the following information and click Save:

  • New Principals: partner-data-exporter-robots@google.com
  • Role: BigQuery Data Editor
Sharing dropdown on actions_analytics dataset.
Permissions button on sharing dropdown. Add Principle button highlighted.
Permissions.">

5. Share Access to Cloud Project

Please share BigQuery Job User access to partner-data-exporter-robots@google.com for the Cloud Project. This enables Google to load data into BigQuery for exporting. To do this, locate IAM & Admin in the sidebar and click IAM. Click Grant Access in the permissions tab. Enter the following information and click Save:

  • New Principals: partner-data-exporter-robots@google.com
  • Role: BigQuery Job User
partner-data-exporter-robots@google.com added as principal Grant Access button highlighted.

6. Notify Order with Google support

Once all of the above steps are complete, please file a case. As the final step, the support team will enable BigQuery export so that you can begin collecting the data. This will begin automatically exporting data for the previous day.

Data schema and usage

The downloaded data is organized as tables under the actions_analytics tag in the opened tab. There are five tables for each day, as described in the following sections:

daily_food_ordering_business_stats

Field name Type Description
Date DATE Date associated with the transaction.
Week INTEGER Date associated with the transaction.
Month INTEGER Date associated with the transaction.
Restaurant3pId STRING Date associated with the transaction.
RestaurantName STRING Name of the restaurant associated with the transaction.
CurrencyCode STRING Currency code associated with the transaction.
InteractionType STRING Interaction type can be one of the following:
  • CHECKOUT_CLICKED
  • SUBMIT_CLICKED
  • SUBMIT_SUCCESS
  • CHECKOUT_SUCCESS
  • ORDER_FULFILLED

The first four interaction types specify the anonymized user clicks for the respective interactions. The ORDER_FULFILLED interaction type specifies the successful order.
NumInteractions INTEGER Represents the number of interactions associated with that interaction type. For the ORDER_FULFILLED interaction type, the NumInteractions field represents the number of orders.
NumOrders INTEGER Represents the number of orders associated with the restaurant for that day.
TotalOrderValueNanos INTEGER Represents the total order value associated with that restaurant for that day in Nanos.
TotalCartValueNanos INTEGER Represents the total cart value associated with that restaurant for that day in Nanos.

daily_food_ordering_interaction_stats_anonymized

Field name Type Description
Date DATE Date associated with the transaction.
Week INTEGER Week associated with the transaction.
Month INTEGER Month associated with the transaction.
InteractionType STRING InteractionType in this table is limited to PARTNER_SELECTED. This represents the action of selecting the provider associated with the GCP/AoG project.
NumInteractions INTEGER Represents the number of times the provider was selected.

daily_food_ordering_transactions_error_stats

Field name Type Description
Date DATE Date associated with the transaction.
Hour INTEGER Hour associated with the transaction.
Minute INTEGER Minute associated with the transaction.
CurrencyCode STRING Currency code associated with the transaction.
Channel STRING Represents the environment in which the order was placed. Because we only provide access to production data, the channel field is always PRODUCTION.
ActionId STRING Represents whether the transaction is checkout or submit. actions.foodordering.intent.CHECKOUT represents checkout and actions.intent.TRANSACTION_DECISION represents submit.
Function STRING Same as the Actionid column.
OrderType STRING Represents whether the order is Pick up or Delivery. 1 is used for DELIVERY, and 2 is used for PICKUP.
FulfillmentTimeType STRING Represents whether the order is an ASAP order or a scheduled order. 1 is used for ASAP, and 2 is used for ORDER_AHEAD.
ApiResponseStatus STRING Represents the status of the response sent to Google:
  • 1: Successful checkout
  • 2: Unrecoverable checkout error
  • 3: Checkout errors that need user edits
  • 4: Successful submit order
  • 5: Order has been rejected
  • 6: Submit response is invalid
  • 7: Non-blocking warnings during checkout (recoverable errors)
  • 8: Blocking checkout errors
  • 9: Invalid checkout response
  • 10: Invalid cart at checkout
  • 11: Invalid promotion at checkout
ApiResponseError STRING Represents the error type sent to Google:
  • 1:NOT_FOUND
  • 2:INVALID
  • 3:AVAILABILITY_CHANGED
  • 4:PRICE_CHANGED
  • 5:INCORRECT_PRICE
  • 6:REQUIREMENT_NOT_MET
  • 7:TOO_LATE(Fulfillment option expired)
  • 8:NO_CAPACITY
  • 9:INELIGIBLE
  • 10:OUT_OF_SERVICE_AREA
  • 11:CLOSED
  • 12:PROMO_NOT_APPLICABLE
  • 13:PROMO_NOT_RECOGNIZED
  • 14:PROMO_EXPIRED
  • 15:PROMO_USER_INELIGIBLE
  • 16:PROMO_ORDER_INELIGIBLE
  • 17:UNAVAILABLE_SLOT
  • 18:PAYMENT_DECLINED
  • 19:UNPARSEABLE_JSON
  • 20:JSON_VALIDATION_FAILED
  • 21:MERCHANT_UNREACHABLE
  • 801:NO_COURIER_AVAILABLE
NumErrors INTEGER Represents the number of errors.

daily_food_ordering_transactions_stats

Field name Type Description
Date DATE Date associated with the transaction.
Hour INTEGER Hour associated with the transaction.
Minute INTEGER Minute associated with the transaction.
CurrencyCode STRING Currency code associated with the transaction.
Channel STRING Represents the environment in which the order was placed. Because we only provide access to production data, the channel field is always PRODUCTION.
ActionId STRING Represents whether the transaction is checkout or submit. actions.foodordering.intent.CHECKOUT represents checkout and actions.intent.TRANSACTION_DECISION represents submit.
Function STRING Same as the Actionid column.
OrderType STRING Represents whether the order is Pick up or Delivery. 1 is used for DELIVERY, and 2 is used for PICKUP.
FulfillmentTimeType STRING Represents whether the order is an ASAP order or a scheduled order. 1 is used for ASAP, and 2 is used for ORDER_AHEAD.
ApiResponseStatus STRING Represents the status of the response sent to Google:
  • 1: Successful checkout
  • 2: Unrecoverable checkout error
  • 3: Checkout errors that need user edits
  • 4: Successful Submit Order
  • 5: Order has been rejected
  • 6: Submit response is invalid
  • 7: Non-blocking warnings during checkout (recoverable errors)
  • 8: Blocking checkout errors
  • 9: Invalid checkout response
  • 10: Invalid cart at checkout
  • 11: Invalid promotion at checkout
NumResponse INTEGER Represents the number of responses.

food_ordering_user_stats_daily

Field Name Type Description
Date DATE Date associated with the transaction.
Restaurant3pId STRING The restaurant ID sent by the partner in the feed.
RestaurantName STRING Name of the restaurant associated with the transaction.
CurrencyCode STRING Currency code associated with the transaction.
InteractionType STRING Represents the interaction type associated with the transaction. The ORDER_FULFILLED interaction type specifies the successful order.
ApproximateTotalUsers INTEGER Represents the total number of users who ordered from the restaurant on that day.
ApproximateRepeatUsers INTEGER Represents the number of repeat users who placed orders from that particular store on that particular day.

Use the exported data

You can either export the data to other tools, like Google Sheets, Data Studio, or GCS, or write queries within the Google Cloud Platform console to get specific insights from the data.

Example query

// To get the conversion rate from Partner selection to order placement
SELECT
  (
    SELECT NumInteractions
    FROM
      aog
      - project
      - id.actions_analytics.daily_food_ordering_business_stats_20200620
    WHERE InteractionType = 'SUBMIT_SUCCESS'
  )
  / (
    SELECT NumInteractions
    FROM
      aog
      - project
      - id.actions_analytics.daily_food_ordering_interaction_stats_anonymized_20200620
    WHERE InteractionType = 'PARTNER_SELECTED'
  )
Previous
Inventory Viewer
Next
Stackdriver logs