Usage reporting

1. Create a BigQuery dataset

To enable usage report for Essentials, you need to create a BigQuery dataset.

A dataset is contained within a specific Cloud project. Datasets are top-level containers that are used to organize and control access to your tables and views. A table or view must belong to a dataset, so you need to create at least one dataset before loading data into BigQuery.

To create a BigQuery dataset, do the following:

  1. Sign in to the Google Cloud Console and go to the BigQuery page.
  2. In the project drop down at the top of the Google Cloud Console page, select or create a project to contain your BigQuery dataset. Recommendation: We recommend that you configure a separate Cloud project to contain your usage report for Essentials.
  3. In the BigQuery navigation panel, click on your project name.
  4. Click CREATE DATASET. The Create dataset panel opens.

    a. Enter a Dataset ID. It can be any ID of your choice (e.g. “essentials_usage_report”).

    b. Leave the Data location to Default.

    c. Select the Default table expiration. To set an expiration longer than 60 days you need to enable billing for your project (you will not be charged until you exceed 10GB of stored BigQuery data for the whole project, see Costs below). Recommendation: We recommend that you either set the expiration to Never or download the data regularly before it expires, so you don’t lose any data.

    d. Set the Encryption option to Google-managed key.

    e. To save, click Create dataset.

You then need to allow Google to write to the dataset you created:

  1. In the BigQuery navigation panel, click on the dataset you just created.
  2. Click SHARE DATASET. The permissions panel opens.

    a. Add the member android-enterprise-essentials@system.gserviceaccount.com with the role BigQuery Data Editor.

    b. To save, click Done.

2. Enable usage report

To enable the usage report, use the Android Enterprise Essentials Integrations form to submit the following information:

  • The BigQuery dataset ID you created in the previous step.
  • The ID of your Cloud Project, which can be found in the Home page of the Google Cloud Console. Go to the Home Page. The Essentials team will then enable the export to your BigQuery dataset and it will be visible starting the next day.

Structure of the usage report

The usage report contains one row per day and per device that has Essentials enabled. Each row contains details about the device (customer ID, IMEI, etc) and the cost of the Essentials service for that device for that day. You can use the usage report to compute the total amount Google charges you for each of your customers.

Essentials is enabled for a device from the time the service is enabled for the device by the reseller using the reseller portal or API, up to the time the service is disabled by the reseller using the reseller portal or API, or up to the time the service is disabled by the customer using the customer portal. The time the device is set up by the user is not taken into account for billing.

The UTC time zone is used for the calendar. The transition from one billing day to the next happens at midnight UTC. Rows in the usage reports are from midnight UTC to midnight UTC the next day.

The usage report is loaded every day into your BigQuery dataset. The usage report for a given billing day is loaded between midnight and 2 a.m. UTC the next day.

The usage report is loaded into a table named EssentialsUsageReport. This table is partitioned based on the billing date, this improves query performance when querying only specific days (with a WHERE clause).

The following table describes the content of the usage report:

Field Type Description
start_time Timestamp The start time of the daily usage window within which the given cost was calculated. Formatted as a timestamp, example: 2020-03-12 00:00:00 UTC
end_time Timestamp The end time of the daily usage window within which the given cost was calculated. Formatted as a timestamp, example: 2020-03-13 00:00:00 UTC
reseller_id String The Reseller ID that the usage is associated with.
vendor_id String The Vendor ID that the usage is associated with, if any. This is only present if the service is activated by a vendor (sub-reseller).
customer_id String The Customer ID that the usage is associated with. The Customer ID is assigned by the server when the customer account is created.
customer_name String The name of the account associated with the Customer ID.
device_id String The Device ID that the usage is associated with. This Device ID is assigned by the server when a device is claimed.
imei String The device’s IMEI number, if set when the device was claimed.
meid String The device’s MEID number, if set when the device was claimed.
serial_number String The device’s serial number, if set when the device was claimed.
cost Float The cost charged to the reseller for this usage.
currency String The currency that the cost is billed in, as a 3 letter currency code. Until pricing is finalized, a fictive price of 1.0 XYZ per device per month is used for generating the report.
order_number String The value of the ordernumber metadata assigned to the device, if any.
eligible_since Timestamp The time when Essentials was first enabled on this device, for this customer. Will be used for tracking eligibility for certain refunds or discounts.
deactivated Boolean Set to TRUE on the day when Essentials is deactivated, either by the reseller or by the customer. This indicates that the device will not appear in the usage report the following day, unless action is taken by the reseller to re-enable Essentials.
deactivated_by_customer Boolean Set to TRUE if Essentials was deactivated by the customer. Set to FALSE if Essentials was deactivated by the reseller. If the device was not deactivated this field will be NULL.
usage_type Integer A number indicating the type of usage:
1 = charge for device on a consumption plan
2 = charge for device with an active prepaid plan
3 = charge for a device with an inactive prepaid plan, which has either lapsed or has not started yet.
Other numbers will be used to indicate refunds or discounts.
description String This field will be populated with a description of the refund or discount as appropriate. The field will be empty for general usage.
plan Integer 1 = device is on the consumption plan, incurring charges on a daily basis.
2 = device is on the prepaid plan, incurring charges on its first day of usage only.
plan_first_date Date If the device is on the prepaid plan, the date on which the prepaid period starts. If the devices is on the consumption plan this field is NULL.
plan_last_date Date If the device is on the prepaid plan, the date after which the prepaid period ends. If the devices is on the consumption plan this field is NULL.
report_revision Integer Set to 1 the first time usage report data is exported for a particular start_time. If the report has to be re-exported for any reason then the report_revision is incremented and any entries for the same start_time with a lower report_revision can be considered invalid and can be discarded.

Query the usage report with BigQuery

To query the usage report with BigQuery you can use:

For example, the following query outputs all the rows for a given billing day:

SELECT
  *
FROM EssentialsUsageReport
WHERE
  DATE(start_time) = "2020-03-12"

Export the usage report outside of BigQuery

To export the usage report outside of BigQuery, you can either:

Costs

The daily load of the usage report into your BigQuery dataset is free. Storing the usage report data is free up to 10GB, see Storage pricing for costs beyond that. Querying the usage report data is free up to 1 TB per month, see Query pricing for costs beyond that. Exporting the usage report outside of BigQuery is free. When you export data from BigQuery to Cloud Storage, you are not charged for the export operation, but you do incur charges for storing the data in Cloud Storage. See Data storage on the Cloud Storage pricing page for details.

Example This example shows the usage report for the time period from 2020-03-12 to 2020-03-15 included, for 3 devices:

  • Device 1
    • imei: 1223,
    • customer ID: 708,
    • Device registered: sometime before the beginning of the report period,
    • Essentials disabled: sometime after the end of the report period,
    • Plan: consumption,
    • Days charged: 2020-03-12, 2020-03-13, 2020-03-14, 2020-03-15
  • Device 2
    • imei: 4955,
    • customer ID: 708,
    • Device registered: 2020-03-14 13:00 UTC,
    • Essentials disabled: sometime after the end of the report period,
    • Plan: consumption,
    • Days charged: 2020-03-14, 2020-03-15
  • Device 3
    • imei: 6678,
    • customer ID: 904,
    • Device registered: sometime before the beginning of the report period,
    • Essentials disabled: 2020-03-14 18:00 UTC,
    • Plan: consumption,
    • Days charged: 2020-03-12, 2020-03-13, 2020-03-14
  • Device 4
    • imei: 7479
    • customer ID: 904,
    • Device registered: sometime before the beginning of the report period,
    • Essentials disabled: 2020-03-13 05:00 UTC,
    • Essentials enabled: 2020-03-13 18:00 UTC,
    • Essentials disabled: sometime after the end of the report period,
    • Plan: consumption,
    • Days charged: 2020-03-12, 2020-03-13, 2020-03-14, 2020-03-15
  • Device 5
    • imei: 8812
    • customer ID: 904,
    • Device registered: 2020-03-15 05:00 UTC,
    • Essentials disabled: sometime after the end of the report period,
    • Plan: prepaid
    • Prepaid first date: 2020-03-15
    • Prepaid last date: 2020-09-14
  • Device 6
    • imei: 9912
    • customer ID: 904,
    • Device registered: 2020-03-13 05:00 UTC,
    • Essentials disabled: sometime after the end of the report period,
    • Plan: prepaid
    • Prepaid first date: 2020-03-15
    • Prepaid last date: 2021-03-14

The following CSV snippet shows the corresponding usage export for this example (columns end_time, reseller_id, vendor_id, customer_name, device_id, meid, serial_number, order_number, deactivated_by_customer, description and report_revision are excluded for readability, and timestamps are truncated to show date only):

start_time, customer_id, imei, cost, currency, eligible_since, deactivated usage_type plan plan_first_date plan_last_date
2020-03-12, 708, 1223, 0.0323, XYZ, 2020-02-12, FALSE, 1, 1, ,
2020-03-12, 904, 6678, 0.0323, XYZ, 2020-03-07, FALSE, 1, 1, ,
2020-03-12, 904, 7479, 0.0323, XYZ, 2020-01-30, FALSE, 1, 1, ,
2020-03-13, 708, 1223, 0.0323, XYZ, 2020-02-12, FALSE, 1, 1, ,
2020-03-13, 904, 6678, 0.0323, XYZ, 2020-03-07, FALSE, 1, 1, ,
2020-03-13, 904, 7479, 0.0323, XYZ, 2020-01-30, FALSE, 1, 1, ,
2020-03-13, 904, 9912, 0, XYZ, 2020-03-13, FALSE, 3, 2, 2020-03-15, 2021-03-14
2020-03-14, 708, 1223, 0.0323 XYZ, 2020-02-12, FALSE, 1, 1, ,
2020-03-14, 708, 4955, 0.0323 XYZ, 2020-03-14, FALSE, 1, 1, ,
2020-03-14, 904, 6678, 0.0323 XYZ, 2020-03-07, 1, 1, ,
2020-03-14, 904, 7479, 0.0323 XYZ, 2020-01-30, FALSE, 1, 1, ,
2020-03-14, 904, 9912, 0, XYZ, 2020-03-13, FALSE, 3, 2, 2020-03-15, 2021-03-14
2020-03-15, 708, 1223, 0.0323 XYZ, 2020-02-12, FALSE, 1, 1, ,
2020-03-15, 708, 4955, 0.0323 XYZ, 2020-03-14, FALSE, 1, 1, ,
2020-03-15, 904, 7479, 0.0323 XYZ, 2020-01-30, FALSE, 1, 1, ,
2020-03-15, 904, 8812, 6, XYZ, 2020-03-15, FALSE, 2, 2, 2020-03-15, 2021-03-14
2020-03-15, 904, 9912, 12, XYZ, 2020-03-13, FALSE, 2, 2, 2020-03-15, 2021-03-14