商業市集程序設計分析的範例查詢
本文提供Microsoft商業市集訂單、使用量和客戶報表的範例查詢。 您可以呼叫 建立報表查詢 API 端點，依您的使用案例來參考這些查詢並建立更多查詢。
如需資料行名稱、屬性和描述的詳細資訊，請參閱下列文章：
客戶回報問題
這些範例查詢會套用至 Customers 報表。
| 查詢描述 | 範例查詢 |
|---|---|
| 列出與合作夥伴相關的活躍客戶及客戶詳細資料，直至您選擇的日期。 | SELECT DateAcquired,CustomerCompanyName,CustomerId FROM ISVCustomer WHERE IsActive = 1 |
| 列出合作夥伴已流失客戶的詳細資訊，直到您選擇的日期 | SELECT DateAcquired,CustomerCompanyName,CustomerId FROM ISVCustomer WHERE IsActive = 0 |
| 過去六個月中特定地理位置的新客戶清單 | SELECT DateAcquired,CustomerCompanyName,CustomerId FROM ISVCustomer WHERE DateAcquired <= ‘2020-06-30’ AND CustomerCountryRegion = ‘United States’ |
使用量報表查詢
這些範例查詢會套用至使用量報告。
| 查詢描述 | 範例查詢 |
|---|---|
| 列出過去 6 個月「透過 Azure 計費」Marketplace 授權類型的虛擬機（VM）詳細的標準化使用量數據。 | SELECT MonthStartDate, NormalizedUsage FROM ISVUsage WHERE MarketplaceLicenseType = ‘Billed Through Azure’ AND OfferType NOT IN (‘Azure Applications’, ‘SaaS’) TIMESPAN LAST_6_MONTHS |
| 列出過去 12 個月「透過 Azure 計費」Marketplace 授權類型的 VM 原始使用量詳細數據。 | SELECT MonthStartDate, RawUsage FROM ISVUsage WHERE MarketplaceLicenseType = ‘Billed Through Azure’ AND OfferType NOT IN (‘Azure Applications’, ‘SaaS’) TIMESPAN LAST_1_YEAR |
| 列出過去 6 個月「攜帶您自己的授權」Marketplace 授權類型的 VM 標準化使用量詳情。 | SELECT MonthStartDate, NormalizedUsage FROM ISVUsage WHERE MarketplaceLicenseType = ‘Bring Your Own License’ AND OfferType NOT IN (‘Azure Applications’, ‘SaaS’) TIMESPAN LAST_6_MONTHS |
| 列出過去六個月「攜帶您自己的授權」Marketplace 授權類型的 VM 原始使用量及使用量詳細數據。 | SELECT MonthStartDate, RawUsage FROM ISVUsage WHERE MarketplaceLicenseType = ‘Bring Your Own License’ AND OfferType NOT IN (‘Azure Applications’, ‘SaaS’) TIMESPAN LAST_6_MONTHS |
| 列出使用量詳細資料，包括上個月付費方案的使用日期、每日總標準化使用量，以及「估算延長費用（PC/CC）」 | SELECT UsageDate, NormalizedUsage, EstimatedExtendedChargePC FROM ISVUsage WHERE SKUBillingType = ‘Paid’ ORDER BY UsageDate DESC TIMESPAN LAST_MONTH |
| 列出使用量詳細數據，包括使用量日期、每日原始使用量總計和上個月付費方案的「估計延長費用（PC/CC）」 | SELECT UsageDate, RawUsage, EstimatedExtendedChargePC FROM ISVUsage WHERE SKUBillingType = ‘Paid’ ORDER BY UsageDate DESC TIMESPAN LAST\_MONTH |
| 列出供應項目名稱，以及「透過 Azure 計費」Marketplace 授權類型在過去 6 個月的 VM 標準化使用量。 | SELECT OfferName, NormalizedUsage FROM ISVUsage WHERE MarketplaceLicenseType = ‘Billed Through Azure’ AND OfferName = ‘Example Offer Name’ TIMESPAN LAST_6_MONTHS |
| 列出優惠名稱及其使用量詳細數據，過去 6 個月的計量使用量 | SELECT OfferName, MeteredUsage FROM ISVUsage WHERE OfferName = ‘Example Offer Name’ AND OfferType IN (‘SaaS’, ‘Azure Applications’) TIMESPAN LAST_6_MONTHS |
| 列出過去6個月所有優惠的使用詳情。 | SELECT OfferType, OfferName, SKU, IsPrivateOffer, UsageReference, UsageDate, RawUsage, EstimatedPricePC FROM ISVUsage ORDER BY UsageDate DESC TIMESPAN LAST_MONTH |
| 列出過去六個月內所有私人優惠的使用量詳細數據 | SELECT OfferType, OfferName, SKU, IsPrivateOffer, UsageReference, UsageDate, RawUsage, EstimatedPricePC FROM ISVUsage WHERE IsPrivateOffer = '1' ORDER BY UsageDate DESC TIMESPAN LAST_MONTH |
訂單報表查詢
這些範例查詢會套用至 Orders 報表。
| 查詢描述 | 範例查詢 |
|---|---|
| 列出過去六個月 Azure 授權類型為「企業」的訂單詳情 | SELECT AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate FROM ISVOrder WHERE AzureLicenseType = 'Enterprise' TIMESPAN LAST_6_MONTHS |
| 列出過去 6 個月的「隨用隨付」Azure 授權類型訂單詳細資訊。 | SELECT OfferName, AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate, OrderStatus, OrderCancelDate FROM ISVOrder WHERE AzureLicenseType = 'Pay as You Go' TIMESPAN LAST_6_MONTHS |
| 列出過去6個月特定供應專案名稱的訂單詳細數據。 | SELECT AssetId, PurchaseRecordId, PurchaseRecordLineItemId , OrderPurchaseDate FROM ISVOrder WHERE OfferName = Contoso test Services' TIMESPAN LAST_6_MONTHS |
| 列出過去六個月內所有活躍訂單的訂單詳細資料 | SELECT OfferName, AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate FROM ISVOrder WHERE OrderStatus = 'Active' TIMESPAN LAST_6_MONTHS |
| 列出過去六個月已取消訂單的詳情 | SELECT OfferName, AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate FROM ISVOrder WHERE OrderStatus = 'Cancelled' TIMESPAN LAST_6_MONTHS |
| 列出過去6個月的訂單詳情，包括數量、合約開始日期、合約結束日期、估計費用和貨幣。 | SELECT AssetId, Quantity, PurchaseRecordId, PurchaseRecordLineItemId, TermStartDate, TermEndDate, BilledRevenue, Currency from ISVOrder WHERE OrderStatus = 'Active' TIMESPAN LAST_6_MONTHS |
| 列出過去 6 個月內仍然活躍的試用訂單的訂單詳細資訊。 | SELECT AssetId, Quantity, PurchaseRecordId, PurchaseRecordLineItemId from ISVOrder WHERE OrderStatus = 'Active' and IsTrial = 'True' TIMESPAN LAST_6_MONTHS |
| 列出過去 6 個月內所有有效優惠的訂單詳細資料。 | SELECT OfferName, SKU, IsPrivateOffer, AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate, BilledRevenue FROM ISVOrder WHERE OrderStatus = 'Active' TIMESPAN LAST_6_MONTHS |
| 列出過去 6 個月有效的私人優惠訂單詳細資訊 | SELECT OfferName, SKU, IsPrivateOffer, AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate, BilledRevenue FROM ISVOrder WHERE IsPrivateOffer = '1' and OrderStatus = 'Active' TIMESPAN LAST_6_MONTHS |
營收報表查詢
這些範例查詢適用於營收報表。
| 查詢描述 | 範例查詢 |
|---|---|
| 列出合作夥伴過去 1 個月的計費收入 | SELECT BillingAccountId, OfferName, OfferType, Revenue, EarningAmountCC, EstimatedRevenueUSD, EarningAmountUSD, PayoutStatus, PurchaseRecordId, LineItemId,TransactionAmountCC,TransactionAmountUSD, Quantity,Units FROM ISVRevenue TIMESPAN LAST_MONTH |
| 列出過去 3 個月內所有具有已寄送狀態之交易的預估收入（以美元計算） | SELECT BillingAccountId, OfferName, OfferType, EstimatedRevenueUSD, EarningAmountUSD, PayoutStatus, PurchaseRecordId, LineItemId, TransactionAmountUSD FROM ISVRevenue where PayoutStatus='Sent' TIMESPAN LAST_3_MONTHS |
| 訂閱型計費模型的非試用版交易清單 | SELECT BillingAccountId, OfferName,OfferType, TrialDeployment EstimatedRevenueUSD, EarningAmountUSD FROM ISVRevenue WHERE TrialDeployment=’False’ and BillingModel=’SubscriptionBased’ |
服務品質報告查詢
此範例查詢適用於服務質量報告。
| 查詢描述 | 示例查詢 |
|---|---|
| 列出過去 6 個月的優惠專案部署狀態 | SELECT OfferId, Sku, DeploymentStatus, DeploymentCorrelationId, SubscriptionId, CustomerTenantId, CustomerName, TemplateType, StartTime, EndTime, DeploymentDurationInMilliSeconds, DeploymentRegion FROM ISVQualityOfService TIMESPAN LAST_6_MONTHS |
客戶留存報告查詢
此範例查詢適用於客戶保留報告。
| 查詢描述 | 範例查詢 |
|---|---|
| 列出過去 6 個月的客戶保留詳細數據 | SELECT OfferCategory, OfferName, ProductId, DeploymentMethod, ServicePlanName, Sku, SkuBillingType, CustomerId, CustomerName, CustomerCompanyName, CustomerCountryName, CustomerCountryCode, CustomerCurrencyCode, FirstUsageDate, AzureLicenseType, OfferType, Offset FROM ISVOfferRetention TIMESPAN LAST_6_MONTHS |
| 列出過去 6 個月內所有客戶的使用量活動和營收詳細數據 | SELECT OfferCategory, OfferName, Sku, ProductId, OfferType, FirstUsageDate, Offset, CustomerId, CustomerName, CustomerCompanyName, CustomerCountryName, CustomerCountryCode, CustomerCurrencyCode FROM ISVOfferRetention TIMESPAN LAST_6_MONTHS |