Exemplos de consulta do BigQuery Export

Nesta página, mostramos exemplos de como consultar dados do Channel Services exportados para um conjunto de dados do BigQuery.

Para mais informações sobre a exportação de dados do Channel Services, consulte como configurar a exportação de dados do Channel Services para o BigQuery. Para mais dicas de solução de problemas, consulte Resolver problemas com as exportações do BigQuery.

Como identificar e acessar a tabela do BigQuery

As exportações do Channel Services são configuradas usando o Partner Sales Console.

O nome da tabela usa o formato: PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1

Para permitir que outros usuários acessem uma exportação do Channel Services, aplique as permissões corretas. Por exemplo, atribua o papel bigquery.dataViewer às suas credenciais no projeto, no conjunto de dados ou na própria tabela.

Exemplos de consultas

Os exemplos a seguir incluem um exemplo de consulta e uma explicação básica de como interpretar os resultados. Os resultados contêm os custos de uso do Google Cloud para cada Cliente ou parceiro de canal configurado no Partner Sales Console.

Soma dos custos do parceiro por conta de faturamento

Você pode usar esta consulta para verificar uma fatura recebida do Google. Os valores cost e credit.amount são as somas de todas as linhas. Isso inclui custos de uso, tributos, ajustes e erros de arredondamento.

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;

Soma dos custos do cliente por subconta do Cloud Billing

É possível usar esta consulta para resumir o uso faturável de cada cliente. Os valores customer_cost e credit.customer_amount mostram as somas de todas as linhas que representam os custos de uso. As linhas que representam tributos, ajustes e arredondamentos mostram um valor null.

Para créditos em que type está definido como RESELLER_MARGIN, o customer_amount é definido como zero para garantir que a margem não esteja visível no faturamento do cliente.

Os valores customer_cost e credit.customer_amount mostram suas configurações de mudanças no preço. Por exemplo, se o RebillingBasis do direito de acesso do Google Cloud de um cliente específico for definido como Direct Customer Cost minus 5%, o valor de customer_cost será 0,95 multiplicado por cost. Isso reflete a redução configurada do seu custo.

Os resultados dessa consulta contêm cada subconta do Cloud Billing, o nome do recurso de cliente de Vendas de parceiros associado, o mês da fatura e o uso faturável total.

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;

Soma dos custos do cliente por subconta do Cloud Billing, conforme mostrado para o proprietário da subconta

Esta consulta mostra como os números de custo de dados são mapeados com o que um cliente de revenda (ou você como parceiro) vê ao abrir o console do Google Cloud para a subconta dele.

  1. Para créditos em que type está definido como RESELLER_MARGIN, isso fica oculto na visualização da spa.
  2. Para custos em que cost_type é tributos, esse valor fica oculto na visualização da SPA. O Revendedor precisa adicionar os Tributos aplicáveis separadamente às faturas da revenda.

Às vezes, cost_at_list pode ser nulo para alguns registros, fazendo com que esse resultado seja diferente do que você vê no console do Google Cloud. Se isso acontecer, use IFNULL(cost_at_list, cost). Para mais informações, consulte Cobranças sem custos de lista.

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;

Soma dos custos do parceiro de canal por conta de faturamento

Se você é um distribuidor, pode usar esta consulta para resumir o uso faturável de cada parceiro de canal. Os valores channel_partner_cost e credit.channel_partner_amount mostram a soma de todas as linhas que representam custos de uso. As linhas que representam tributos, ajustes e arredondamentos mostram um valor null.

Para créditos em que type está definido como RESELLER_MARGIN, o channel_partner_amount é definido como zero para garantir que a margem não esteja visível no faturamento dos parceiros de canal.

Os valores channel_partner_cost e credit.channel_partner_amount mostram suas configurações de mudanças no preço. Por exemplo, se o RebillingBasis de um parceiro de canal específico for definido como Direct Customer Cost minus 5%, o valor de channel_partner_cost será 0,95 multiplicado por cost. Isso reflete a redução configurada do seu custo.

Os resultados dessa consulta contêm cada conta de faturamento, o parceiro de canal resource_name anexado aos clientes dessa conta, o mês da fatura e o uso faturável total.

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;

Soma dos custos não atribuídos a um cliente

É possível usar essa consulta para resumir os custos não vinculados a um cliente do Channel Services, mas que podem representar o uso faturável para outros clientes. Os valores cost e credit.amount mostram a soma de todas as linhas em que billing_account_id e payer_billing_account_id são diferentes. O billing_account_id representa a subconta do Cloud Billing.

Para créditos em que type está definido como RESELLER_MARGIN, essa consulta exclui o valor do crédito. O valor do crédito é destinado à sua alocação e não é considerado um custo do cliente. O valor de RESELLER_MARGIN está incluído no seu custo e refletido nas faturas enviadas pelo 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;

Visualização de dados do Google Workspace semelhante ao CSV da fatura

Se você é um revendedor do Google Workspace, é possível visualizar os dados de faturamento do Google Workspace com colunas semelhantes às dos arquivos CSV da fatura.

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;

Outras colunas de interesse que podem ser adicionadas às cláusulas SELECT e GROUP BY:

  • billing_account_id: representa o ID do Cloud Identity do cliente.
  • customer_name: representa o recurso do cliente que pode ser usado em chamadas de API.
  • channel_partner_name: (para distribuidores) representa o parceiro de canal.
  • entitlement_name: nome do recurso do direito no Channel Services.
  • customer_correlation_id: um ID de gestão de relacionamento com o cliente (CRM) que você pode definir para um cliente.
  • usage.amount: para uso baseado em licenças do Google Workspace, isso indica licenças pagas. Por exemplo, licenças adquiridas para compromissos ou número de licenças atribuídas para assinaturas flexíveis.

O resultado da consulta difere da fatura em CSV das seguintes maneiras:

  • A coluna "Descrição" do CSV usa uma string legível, enquanto a exportação do BigQuery usa valores de tipos enumerados.
  • As colunas "Data de início/término" do CSV não são preenchidas com zero (por exemplo, 1o de maio), enquanto a consulta do BigQuery usa valores preenchidos (por exemplo, 1o de maio). Também é possível usar os valores de carimbo de data/hora diretamente. O usage_start_time é inclusivo, mas o usage_end_time é exclusivo.
  • O CSV tem uma única linha "Tributo" no final, enquanto a exportação do BigQuery tem linhas fiscais no nível da assinatura que somam o mesmo valor que a linha "Tributos" do CSV.