商業市集程序設計分析的範例查詢
本文提供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’ |
使用量報表查詢
這些範例查詢會套用至使用量報告。
| 查詢描述 | 範例查詢 |
|---|---|
| 列出過去 6M 的「透過 Azure 計費」Marketplace 授權類型的虛擬機 （VM） 標準化使用量詳細數據 | SELECT MonthStartDate, NormalizedUsage FROM ISVUsage WHERE MarketplaceLicenseType = ‘Billed Through Azure’ AND OfferType NOT IN (‘Azure Applications’, ‘SaaS’) TIMESPAN LAST_6_MONTHS |
| 列出過去 12M 的「透過 Azure 計費」Marketplace 授權類型的 VM 原始使用量詳細數據 | SELECT MonthStartDate, RawUsage FROM ISVUsage WHERE MarketplaceLicenseType = ‘Billed Through Azure’ AND OfferType NOT IN (‘Azure Applications’, ‘SaaS’) TIMESPAN LAST_1_YEAR |
| 列出過去 6M 之「攜帶您自己的授權」Marketplace 授權類型的 VM 標準化使用量詳細數據 | SELECT MonthStartDate, NormalizedUsage FROM ISVUsage WHERE MarketplaceLicenseType = ‘Bring Your Own License’ AND OfferType NOT IN (‘Azure Applications’, ‘SaaS’) TIMESPAN LAST_6_MONTHS |
| 列出過去 6M 的 VM 原始使用量「攜帶您自己的授權」Marketplace 授權類型的使用量詳細數據 | 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 |
| 列出供應專案名稱、過去 6M 的「透過 Azure 計費」Marketplace 授權類型的 VM 標準化使用量 | SELECT OfferName, NormalizedUsage FROM ISVUsage WHERE MarketplaceLicenseType = ‘Billed Through Azure’ AND OfferName = ‘Example Offer Name’ TIMESPAN LAST_6_MONTHS |
| 列出供應項目名稱的使用量詳細數據，過去 6M 的計量使用量 | SELECT OfferName, MeteredUsage FROM ISVUsage WHERE OfferName = ‘Example Offer Name’ AND OfferType IN (‘SaaS’, ‘Azure Applications’) TIMESPAN LAST_6_MONTHS |
| 列出過去 6M 所有供應專案的所有供應專案使用量詳細數據 | SELECT OfferType, OfferName, SKU, IsPrivateOffer, UsageReference, UsageDate, RawUsage, EstimatedPricePC FROM ISVUsage ORDER BY UsageDate DESC TIMESPAN LAST_MONTH |
| 列出過去 6M 私人供應專案的所有供應專案使用量詳細數據 | SELECT OfferType, OfferName, SKU, IsPrivateOffer, UsageReference, UsageDate, RawUsage, EstimatedPricePC FROM ISVUsage WHERE IsPrivateOffer = '1' ORDER BY UsageDate DESC TIMESPAN LAST_MONTH |
訂單報表查詢
這些範例查詢會套用至 Orders 報表。
| 查詢描述 | 範例查詢 |
|---|---|
| 列出過去 6M 的 Azure 授權類型為「企業」的訂單詳細數據 | SELECT AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate FROM ISVOrder WHERE AzureLicenseType = 'Enterprise' TIMESPAN LAST_6_MONTHS |
| 列出過去 600 萬個「隨用隨付」的 Azure 授權類型訂單詳細數據 | SELECT OfferName, AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate, OrderStatus, OrderCancelDate FROM ISVOrder WHERE AzureLicenseType = 'Pay as You Go' TIMESPAN LAST_6_MONTHS |
| 列出過去 6M 特定供應專案名稱的訂單詳細數據 | SELECT AssetId, PurchaseRecordId, PurchaseRecordLineItemId , OrderPurchaseDate FROM ISVOrder WHERE OfferName = Contoso test Services' TIMESPAN LAST_6_MONTHS |
| 列出過去 6M 使用中訂單的訂單詳細數據 | SELECT OfferName, AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate FROM ISVOrder WHERE OrderStatus = 'Active' TIMESPAN LAST_6_MONTHS |
| 列出過去 6M 訂單已取消訂單的訂單詳細數據 | SELECT OfferName, AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate FROM ISVOrder WHERE OrderStatus = 'Cancelled' TIMESPAN LAST_6_MONTHS |
| 列出最後 6M 貨幣的數量、字詞開始、字詞結束日期和估計圖表的訂單詳細數據 | SELECT AssetId, Quantity, PurchaseRecordId, PurchaseRecordLineItemId, TermStartDate, TermEndDate, BilledRevenue, Currency from ISVOrder WHERE OrderStatus = 'Active' TIMESPAN LAST_6_MONTHS |
| 列出過去 6M 使用中試用版訂單的訂單詳細數據 | SELECT AssetId, Quantity, PurchaseRecordId, PurchaseRecordLineItemId from ISVOrder WHERE OrderStatus = 'Active' and IsTrial = 'True' TIMESPAN LAST_6_MONTHS |
| 列出過去 6M 使用中所有供應專案的訂單詳細數據 | SELECT OfferName, SKU, IsPrivateOffer, AssetId, PurchaseRecordId, PurchaseRecordLineItemId, OrderPurchaseDate, BilledRevenue FROM ISVOrder WHERE OrderStatus = 'Active' TIMESPAN LAST_6_MONTHS |
| 列出過去 6M 使用中私人供應專案的訂單詳細數據 | 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 |