Contoh kueri BigQuery Export

Halaman ini memberikan contoh cara membuat kueri data Layanan Saluran yang diekspor ke set data BigQuery.

Untuk mengetahui informasi selengkapnya tentang ekspor data Layanan Saluran, lihat cara menyiapkan ekspor data Layanan Saluran ke BigQuery. Untuk tips pemecahan masalah tambahan, lihat Memecahkan masalah terkait ekspor BigQuery.

Mengidentifikasi dan mengakses tabel BigQuery

Ekspor Layanan Saluran disiapkan menggunakan Partner Sales Console.

Nama tabel menggunakan format: PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1

Agar pengguna lain dapat melihat ekspor Layanan Saluran, Anda harus menerapkan izin yang benar. Misalnya, Anda dapat menetapkan peran bigquery.dataViewer ke kredensial Anda di project, set data, atau tabel itu sendiri.

Contoh kueri

Contoh berikut mencakup contoh kueri dan penjelasan dasar tentang cara menafsirkan hasilnya. Hasilnya berisi biaya penggunaan Google Cloud untuk setiap Pelanggan atau partner saluran yang dikonfigurasi di Partner Sales Console.

Jumlah biaya partner menurut akun penagihan

Anda dapat menggunakan kueri ini untuk memverifikasi invoice yang Anda terima dari Google. Nilai cost dan credit.amount adalah jumlah untuk semua baris. Hal ini mencakup biaya penggunaan, pajak, penyesuaian, dan error pembulatan.

SELECT
  payer_billing_account_id,
  currency,
  invoice.month,
  SUM(cost)
    + SUM(
      IFNULL(
        (SELECT SUM(c.amount) FROM UNNEST(credits) AS c), 0))
    AS total
FROM `PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1`
WHERE
  export_time BETWEEN TIMESTAMP(START_DATE)
    AND TIMESTAMP(END_DATE)
GROUP BY
  payer_billing_account_id,
  currency,
  invoice.month
ORDER BY
  payer_billing_account_id,
  currency,
  invoice.month;

Jumlah biaya pelanggan menurut sub-akun Penagihan Cloud

Anda dapat menggunakan kueri ini untuk meringkas penggunaan yang dapat ditagih untuk setiap pelanggan. Nilai customer_cost dan credit.customer_amount menunjukkan jumlah semua baris yang mewakili biaya penggunaan. Baris yang mewakili pajak, penyesuaian, dan pembulatan menampilkan nilai null.

Untuk kredit dengan type ditetapkan menjadi RESELLER_MARGIN, customer_amount-nya ditetapkan ke nol untuk memastikan margin tidak terlihat dalam penagihan pelanggan.

Nilai customer_cost dan credit.customer_amount menampilkan konfigurasi Penetapan ulang harga. Misalnya, jika RebillingBasis untuk hak Google Cloud pelanggan tertentu ditetapkan ke Direct Customer Cost minus 5%, nilai customer_cost adalah 0,95 dikalikan dengan cost. Hal ini mencerminkan pengurangan yang dikonfigurasi dari biaya Anda.

Hasil kueri ini berisi setiap sub-akun Penagihan Cloud, nama resource pelanggan Partner Sales terkait, bulan invoice, dan total penggunaan yang dapat ditagih.

SELECT
  customer_name,
  billing_account_id,
  payer_billing_account_id,
  currency,
  invoice.month,
  SUM(customer_cost)
    + SUM(
      IFNULL(
        (SELECT SUM(c.customer_amount) FROM UNNEST(credits) AS c), 0))
    AS total
FROM `PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1`
WHERE
  export_time BETWEEN TIMESTAMP(START_DATE)
    AND TIMESTAMP(END_DATE)
GROUP BY
  customer_name,
  billing_account_id,
  payer_billing_account_id,
  currency,
  invoice.month
ORDER BY
  customer_name,
  billing_account_id,
  payer_billing_account_id,
  currency,
  invoice.month;

Jumlah biaya pelanggan menurut sub-akun Penagihan Cloud, seperti yang ditampilkan kepada pemilik sub-akun

Kueri ini menunjukkan pemetaan jumlah biaya data berdasarkan apa yang dilihat oleh pelanggan reseller (atau Anda sebagai partner) saat membuka konsol Google Cloud untuk sub-akun mereka.

  1. Untuk kredit yang type ditetapkan ke RESELLER_MARGIN, nilai ini disembunyikan dalam tampilan SBA.
  2. Untuk biaya dengan cost_type pajak, biaya ini disembunyikan dalam tampilan SBA. Reseller harus menambahkan Pajak yang berlaku secara terpisah ke invoice Reseller mereka.

Terkadang, cost_at_list dapat bernilai null untuk beberapa data, yang menyebabkan hasil ini berbeda dengan yang Anda lihat di konsol Google Cloud. Jika hal ini terjadi, gunakan IFNULL(cost_at_list, cost) sebagai gantinya. Untuk mengetahui informasi selengkapnya, lihat Tagihan tanpa daftar biaya.

SELECT
  customer_name,
  currency,
  SUM(cost_at_list) AS list_cost,
  SUM(cost - cost_at_list) AS negotiated_savings,
  SUM(cost)
    + SUM(
      IFNULL(
        (
          SELECT
            SUM(c.amount)
          FROM
            UNNEST(credits) AS c
          WHERE
            c.type != 'RESELLER_MARGIN'
        ),
        0)) AS total
FROM
  `PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1`
WHERE
  cost_type != 'tax'
  AND billing_account_id = 'BILLING_SUBACCOUNT_ID'
  AND invoice.month = 'YYYYMM'
GROUP BY
  customer_name,
  currency
ORDER BY
  customer_name,
  currency;

Jumlah biaya partner saluran menurut akun penagihan

Jika Anda adalah distributor, Anda dapat menggunakan kueri ini untuk meringkas penggunaan yang dapat ditagih untuk setiap partner saluran. Nilai channel_partner_cost dan credit.channel_partner_amount menunjukkan jumlah semua baris yang mewakili biaya penggunaan. Baris yang mewakili pajak, penyesuaian, dan pembulatan menampilkan nilai null.

Untuk kredit dengan type ditetapkan menjadi RESELLER_MARGIN, channel_partner_amount-nya ditetapkan ke nol untuk memastikan margin tidak terlihat dalam penagihan partner saluran.

Nilai channel_partner_cost dan credit.channel_partner_amount menunjukkan konfigurasi Penetapan ulang harga. Misalnya, jika RebillingBasis untuk partner saluran tertentu ditetapkan ke Direct Customer Cost minus 5%, nilai channel_partner_cost adalah 0,95 dikalikan dengan cost. Hal ini mencerminkan pengurangan yang dikonfigurasi dari biaya Anda.

Hasil kueri ini berisi setiap akun penagihan, resource_name partner saluran yang disertakan ke pelanggan dalam akun penagihan tersebut, bulan invoice, dan total penggunaan yang dapat ditagih.

SELECT
  channel_partner_name,
  payer_billing_account_id,
  currency,
  invoice.month,
  SUM(channel_partner_cost)
    + SUM(
      IFNULL(
        (SELECT SUM(c.channel_partner_amount) FROM UNNEST(credits) AS c), 0))
    AS total
FROM `PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1`
WHERE
  export_time BETWEEN TIMESTAMP(START_DATE)
    AND TIMESTAMP(END_DATE)
GROUP BY
  channel_partner_name,
  payer_billing_account_id,
  currency,
  invoice.month
ORDER BY
  channel_partner_name,
  payer_billing_account_id,
  currency,
  invoice.month;

Jumlah biaya yang belum ditetapkan ke pelanggan

Anda dapat menggunakan kueri ini untuk meringkas biaya yang tidak tertaut ke pelanggan Layanan Saluran, tetapi mungkin menunjukkan penggunaan yang dapat ditagih kepada pelanggan lain. Nilai cost dan credit.amount menunjukkan jumlah semua baris yang memiliki billing_account_id dan payer_billing_account_id yang berbeda. billing_account_id mewakili sub-akun Penagihan Cloud.

Untuk kredit dengan type yang ditetapkan menjadi RESELLER_MARGIN, kueri ini tidak mencakup jumlah kredit. Jumlah kredit ditujukan untuk alokasi Anda dan tidak dianggap sebagai biaya pelanggan. Jumlah RESELLER_MARGIN disertakan dalam biaya Anda dan tercermin dalam invoice yang dikirim oleh Google.

SELECT
  billing_account_id,
  payer_billing_account_id,
  currency,
  invoice.month,
  SUM(cost)
    + SUM(
      IFNULL(
        (SELECT SUM(c.amount) FROM UNNEST(credits) AS c WHERE c.type != 'RESELLER_MARGIN'), 0))
    AS total
FROM `PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1`
WHERE
  export_time BETWEEN TIMESTAMP(START_DATE)
    AND TIMESTAMP(END_DATE)
  AND customer_name IS NULL
GROUP BY
  billing_account_id,
  payer_billing_account_id,
  currency,
  invoice.month
ORDER BY
  billing_account_id,
  payer_billing_account_id,
  currency,
  invoice.month;

Tampilan data Google Workspace serupa dengan CSV invoice

Jika Anda adalah reseller Google Workspace, Anda mungkin akan melihat data penagihan Google Workspace dengan kolom serupa seperti file CSV invoice.

SELECT
  (
    SELECT
      ws_labels.value
    FROM
      UNNEST(system_labels) AS ws_labels
    WHERE
      ws_labels.key = 'workspace.googleapis.com/domain_name'
  ) AS domain_name,
  billing_account_id AS customer_id,
  sku.description AS sku_name,
  sku.id AS sku_id,
  (
    SELECT
      ws_labels.value
    FROM
      UNNEST(system_labels) AS ws_labels
    WHERE
      ws_labels.key = 'workspace.googleapis.com/usage_type'
  ) AS description,
  (
    SELECT
      ws_labels.value
    FROM
      UNNEST(system_labels) AS ws_labels
    WHERE
      ws_labels.key = 'workspace.googleapis.com/order_id'
  ) AS order_name,
  FORMAT_TIMESTAMP('%b %d ', usage_start_time, 'America/Los_Angeles')
    AS start_date,
  FORMAT_TIMESTAMP(
    '%b %d',
    TIMESTAMP_SUB(usage_end_time, INTERVAL 1 MINUTE),
    'America/Los_Angeles')
    AS end_date,
  SUM(usage.amount_in_pricing_unit) AS quantity,
  (
    SELECT ws_labels.value
    FROM UNNEST(system_labels) AS ws_labels
    WHERE ws_labels.key = 'workspace.googleapis.com/purchase_order_id'
  ) AS po_number,
  SUM(cost) AS amount,
FROM `PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1`
WHERE
  export_time BETWEEN TIMESTAMP(START_DATE)
    AND TIMESTAMP(END_DATE)
  AND payer_billing_account_id = 'EXTERNAL_BILLING_ACCOUNT_ID'
  AND invoice.month = 'INVOICE_MONTH'
GROUP BY
  domain_name,
  customer_id,
  sku_name,
  sku_id,
  description,
  order_name,
  start_date,
  end_date,
  po_number
HAVING amount != 0
ORDER BY
  domain_name,
  order_name,
  start_date,
  end_date;

Kolom menarik lainnya untuk ditambahkan ke klausul SELECT dan GROUP BY:

  • billing_account_id: Menampilkan ID Cloud Identity Pelanggan.
  • customer_name: Mewakili resource pelanggan yang dapat digunakan dalam panggilan API.
  • channel_partner_name: (Untuk distributor) Mewakili partner saluran.
  • entitlement_name: Nama resource Hak dalam Layanan Saluran.
  • customer_correlation_id: ID Pengelolaan Hubungan Pelanggan (CRM) yang dapat Anda tentukan untuk pelanggan.
  • usage.amount: Untuk penggunaan berbasis slot Google Workspace, kolom ini menunjukkan slot berbayar. Misalnya, membeli slot untuk komitmen atau jumlah slot yang ditetapkan untuk langganan fleksibel.

Hasil kueri berbeda dengan invoice CSV dalam hal berikut:

  • Kolom "Description" CSV menggunakan string yang dapat dibaca manusia, sedangkan BigQuery Export menggunakan nilai enum.
  • Kolom "Start/End date" CSV tidak diberi padding nol (misalnya 1 Mei), sedangkan kueri BigQuery menggunakan nilai dengan padding (misalnya, 1 Mei). Anda juga dapat menggunakan nilai stempel waktu secara langsung. usage_start_time bersifat inklusif, tetapi usage_end_time bersifat eksklusif.
  • CSV memiliki satu baris "Pajak" di akhir, sedangkan BigQuery Export memiliki baris pajak di tingkat langganan yang berjumlah sama dengan baris "Pajak" CSV.