Esempi di query BigQuery Export

Questa pagina fornisce esempi di come eseguire query sui dati di Channel Services esportati in un set di dati BigQuery.

Per ulteriori informazioni sull'esportazione dei dati di Channel Services, consulta come configurare l'esportazione dei dati di Channel Services in BigQuery. Per ulteriori suggerimenti per la risoluzione dei problemi, consulta Risolvere i problemi relativi alle esportazioni di BigQuery.

Identificazione e accesso alla tabella BigQuery

Le esportazioni di Channel Services vengono configurate utilizzando la Partner Sales Console.

Il nome della tabella utilizza il formato PROJECT_ID.DATASET_NAME.reseller_billing_detailed_export_v1

Per consentire ad altri utenti di visualizzare un'esportazione di Channel Services, devi applicare le autorizzazioni corrette. Ad esempio, puoi assegnare il ruolo bigquery.dataViewer alle tue credenziali nel progetto, nel set di dati o nella tabella stessa.

Esempi di query

Gli esempi riportati di seguito includono una query di esempio e una spiegazione di base su come interpretare i risultati. I risultati contengono i costi di utilizzo di Google Cloud per ogni cliente o partner di canale configurato in Partner Sales Console.

Somma dei costi del partner per account di fatturazione

Puoi utilizzare questa query per verificare una fattura che ricevi da Google. I valori cost e credit.amount sono le somme per tutte le righe. Sono inclusi i costi di utilizzo, le tasse, gli aggiustamenti e gli errori di arrotondamento.

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;

Somma dei costi per i clienti per subaccount di fatturazione Cloud

Puoi utilizzare questa query per riepilogare l'utilizzo fatturabile per ciascun cliente. I valori customer_cost e credit.customer_amount mostrano le somme di tutte le righe che rappresentano i costi di utilizzo. Le righe che rappresentano le imposte, gli aggiustamenti e l'arrotondamento mostrano un valore null.

Per i crediti in cui type è impostato su RESELLER_MARGIN, customer_amount è impostato su zero per assicurarsi che il margine non sia visibile nella fatturazione al cliente.

I valori customer_cost e credit.customer_amount mostrano le configurazioni di Repricing. Ad esempio, se il valore RebillingBasis per il diritto di un determinato cliente a Google Cloud è impostato su Direct Customer Cost minus 5%, il valore customer_cost è 0,95 moltiplicato per cost. Questo valore riflette la riduzione configurata del costo.

I risultati di questa query contengono ogni subaccount di fatturazione Cloud, il nome della risorsa associata del cliente Partner Sales, il mese della fattura e l'utilizzo totale fatturabile.

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;

Somma dei costi per i clienti per subaccount di fatturazione Cloud, come mostrato al proprietario del subaccount

Questa query mostra come i numeri relativi al costo dei dati corrispondono a ciò che un cliente di rivendita (o tu in qualità di partner) vede quando apre la console Google Cloud per il suo subaccount.

  1. Per i crediti in cui type è impostato su RESELLER_MARGIN, questo valore è nascosto nella visualizzazione SBA.
  2. I costi in cui cost_type è tax (imposta) non vengono visualizzati nella visualizzazione SBA. Il rivenditore deve aggiungere le imposte applicabili separatamente alle fatture del prodotto rivenduto.

A volte, cost_at_list potrebbe essere nullo per alcuni record, facendo sì che questo risultato sia diverso da quello visualizzato nella console Google Cloud. In questo caso, utilizza invece IFNULL(cost_at_list, cost). Per ulteriori informazioni, consulta Addebiti senza costi di listino.

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;

Somma dei costi del partner di canale per account di fatturazione

Se sei un distributore, puoi utilizzare questa query per riepilogare l'utilizzo fatturabile per ciascun partner di canale. I valori channel_partner_cost e credit.channel_partner_amount mostrano la somma di tutte le righe che rappresentano i costi dell'utilizzo. Le righe che rappresentano le imposte, gli aggiustamenti e l'arrotondamento mostrano un valore null.

Per i crediti in cui type è impostato su RESELLER_MARGIN, il valore di channel_partner_amount è impostato su zero per assicurarsi che il margine non sia visibile nella fatturazione del partner di canale.

I valori channel_partner_cost e credit.channel_partner_amount mostrano le configurazioni di ricalcolo dei prezzi. Ad esempio, se RebillingBasis per un determinato partner di canale è impostato su Direct Customer Cost minus 5%, il valore channel_partner_cost è 0,95 moltiplicato per cost. Questo valore riflette la riduzione configurata del costo.

I risultati di questa query contengono ogni account di fatturazione, il partner di canaleresource_name associato ai clienti nell'account di fatturazione, il mese della fattura e l'utilizzo fatturato totale.

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;

Somma dei costi non assegnati a un cliente

Puoi utilizzare questa query per riepilogare i costi non collegati a un cliente di Channel Services, ma che potrebbero rappresentare un utilizzo fatturato ad altri clienti. I valori cost e credit.amount mostrano la somma di tutte le righe in cui billing_account_id e payer_billing_account_id sono diversi. billing_account_id rappresenta il subaccount di fatturazione Cloud.

Per i crediti in cui type è impostato su RESELLER_MARGIN, questa query esclude l'importo del credito. L'importo del credito è destinato alla tua allocazione e non viene considerato un costo per il cliente. L'importo di RESELLER_MARGIN è incluso nel costo e riportato nelle fatture inviate da 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;

Visualizzazione dei dati di Google Workspace simile al file CSV della fattura

Se sei un rivenditore di Google Workspace, potresti visualizzare i dati di fatturazione di Google Workspace con colonne simili a quelle dei file CSV della fattura.

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,
  SUM(cost_at_list) AS list_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;

Altre colonne di interesse da aggiungere alle clausole SELECT e GROUP BY:

  • billing_account_id: rappresenta l'ID Cloud Identity del cliente.
  • customer_name: rappresenta la risorsa del cliente che può essere utilizzata nelle chiamate API.
  • channel_partner_name: (per i distributori) rappresenta il partner di canale.
  • entitlement_name: il nome della risorsa del diritto in Channel Services.
  • customer_correlation_id: un ID CRM (Customer Relationship Management) che puoi definire per un cliente.
  • usage.amount: per l'utilizzo basato su licenze di Google Workspace, indica le licenze a pagamento. Ad esempio, utenze acquistate per gli impegni o numero di utenze assegnate per gli abbonamenti flessibili.

Il risultato della query è diverso dalla fattura CSV nei seguenti modi:

  • La colonna CSV "Descrizione" utilizza una stringa leggibile, mentre l'esportazione BigQuery utilizza i valori dell'enum.
  • Le colonne CSV "Data inizio/fine" non sono con zeri iniziali (ad es. 1 maggio), mentre la query BigQuery utilizza valori con zeri iniziali (ad es. 01 maggio). Puoi anche utilizzare direttamente i valori timestamp. usage_start_time è inclusivo, mentre usage_end_time è esclusivo.
  • Il file CSV contiene una singola riga "Imposte" alla fine, mentre l'esportazione BigQuery contiene righe relative alle imposte a livello di abbonamento che sommate danno lo stesso importo della riga "Imposte" del file CSV.

Visualizzazione dei dati di fatturazione degli ordini offline simile al PDF della fattura (anteprima)

Se sei un rivenditore che effettua anche ordini offline (ordini per i quali non utilizzi la Partner Sales Console), puoi visualizzare i dati di fatturazione con colonne simili a quelle dei file CSV delle fatture.

SELECT
  billing_account_id AS customer_name,
  service.description AS sku_name,
  sku.id AS sku_id,
  FORMAT_TIMESTAMP('%b %d', usage_start_time, 'America/Los_Angeles')
    AS start_date,
  FORMAT_TIMESTAMP('%b %d', usage_end_time, 'America/Los_Angeles')
    AS end_date,
  SUM(usage.amount_in_pricing_unit) AS quantity,
  SUM(cost) AS amount,
FROM `PROJECT_ID.DATASET_NAME.reseller_billing_offline_orders_detailed_export_v0`
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
  customer_name,
  sku_name,
  sku_id,
  start_date,
  end_date
HAVING amount != 0
ORDER BY
  customer_name,
  start_date,
  end_date,
  sku_id;

Il risultato della query è diverso dalla fattura PDF nei seguenti modi:

  • Puoi convertire il valore del timestamp in un valore data nel fuso orario "America/Los_Angeles". Sia usage_start_time sia usage_end_time sono esclusivi.
  • Il PDF contiene una suddivisione dettagliata delle imposte sulle vendite locali rispetto a quelle statali, dove l'esportazione BigQuery ha righe fiscali a livello di abbonamento che si sommano all'importo dell'imposta per l'intero abbonamento.