Understanding reconciliation files as a Reseller

Understanding reconciliation files as a Reseller

Downloading reconciliation files

In CloudCockpit you can access reconciliation files by pressing the Administration menu and then clicking on Invoices.
You can then apply a filter to the type of reconciliation file you want to download as shown next:


General notes

Whenever a column is not applicable on a reconciliation file for a given charge or we don't have the information to populate it, its value will appear as empty in the file.
You can choose the regional format of the reconciliation file:


The format affects the following:
  1. How decimal values are separated, for example if values are separated with a "." or a ","
  2. The format of date columns
  3. The separator between each column
Each column that refers to a cost will be impacted by the margin / discount / margin over ERP for RIs limits defined at a reseller level.


Opening reconciliation CSV files in Excel

Long Numbers
When opening a csv file through Excel, the last digits in a long number after the 15th digit are turned to zeros.
Example: 569142413664018751 is turned into 569142413664018000 

Dates Incorrectly formatted
Despite the dates being correctly formatted in the csv file, in Excel they sometimes can fail to appear correctly as some dates are sometimes formatted in the "General" format while others are in "Custom".



Onetime reconciliation files

Onetime reconciliation files contain the charges of Azure Plan, Azure Savings Plan, Azure Reservations, New Commerce Licenses (such as Office 365), Software Subscriptions, Perpetual Software Subscriptions and Marketplace Subscriptions (not yet supported) for all your customers.

Column
DescriptionExample value
CustomerId
The ID of the customer in CloudCockpit associated with the charge. Can be empty, for example when the CSP removed the relationship with the customer on the provider side and the customer wasn't in our system when that happened
2C741C83-E111-4A77-BC5F-C2F065275FA9
CustomerName
The company name of the customer
Customer name
CustomerDomainName
The domain associated to the customer in Microsoft for the line item
CustomerCountry
The country of the customer associated with the line item (two-letter code - ISO 3166-1 alpha-2)
IE
ResellerMpnId
The MPN id of the reseller associated with the line item, if applicable (otherwise, it it is not set)
3267
OrderDate
The date when the order was created
1/31/2023 11:59:59 PM
ProductId
The product unique identifier associated with the charge
DZH318Z0BNZF
SkuId
The SKU unique identifier associated with the charge
004V
AvailabilityId
The availability unique identifier associated with the charge
DZH318Z0NRL7
SkuName
The SKU name
Standard Page Blob - LRS
ProductName
The product's name
Standard Page Blob
ChargeType
The type of charge on this line item. Possible values for the Microsoft provider include:
  • new
  • renew
  • cycleCharge
  • addQuantity
  • removeQuantity
  • moveQuantity
  • cancelImmediate
  • convert
  • changeBillingPlan
  • customerCredit
  • extendTerm
cycleCharge
Quantity
The quantity of the subscription associated with the charge
1
Currency
The billing currency used for the line item (three-letter code - ISO 4217)
EUR
PublisherName
The publisher name associated with this purchase
Microsoft
PublisherId
The publisher id associated with this purchase
12
SubscriptionDescription
The subscription description associated with this purchase
Azure Plan
SubscriptionId
The ID of the subscription associated with the charge. Can be empty, for example when the subscription is no longer in our system
03DE5ECE-EC93-42A9-8F15-9A770AC1F832
ChargeStartDate
The billing start date for this charge in the chosen format
2/2/2023 12:00:00 AM
ChargeEndDate
The billing end date for this charge in the chosen format
3/1/2023 12:00:00 AM
TermAndBillingCycle
The term and billing cycle associated with this purchase
10K Read Operations
UnitType
The charging unit type
1 Hour
AlternateId
The alternate ID of the order associated to the line item in the provider
532f0b0f8ee3
ResellerId
The ID of the reseller in CloudCockpit associated with this charge (can be empty)
B8E08E60-19F7-4F95-AE29-A82D3CD53F84
ResellerName
The company name of the reseller associated with this charge
Reseller name
CustomerInternalId
The internal ID of the customer associated with this charge. Use this to identify a customer in your systems
CustomerTest
SubscriptionInternalId
The internal ID of the subscription associated with this charge. Use this to identify a subscription in your systems
SubscriptionTest
UnitPriceForReseller
The price per single quantity that the reseller pays
30.95
UnitPriceForCustomer
The price per single quantity that the customer pays
30.95
SubtotalForReseller
The cost the reseller pays for the subscription on this charge before taxes.
119.07
SubtotalForCustomer
The cost the customer pays for the subscription on this charge before taxes.
119.07
TaxTotalForReseller
The taxes the reseller pays for the subscription on this charge
4.76
TaxTotalForCustomer
The taxes the customer pays for the subscription on this charge
4.76
TotalForReseller
The cost the reseller pays for the subscription on this charge after taxes.
TotalForReseller = ROUND(SubtotalForReseller + TaxTotalForReseller, 2)
123.83
TotalForCustomer
The cost the customer pays for the subscription on this charge after taxes.
TotalForCustomer = ROUND(SubtotalForCustomer + TaxTotalForCustomer, 2)
123.83
CustomerPriceMargin
The value of the margin applied at the customer level
5
CustomerPriceMarginRule
The margin rule applied at the customer level. The possible values are: markup, margin, splitmargin and erpminusdiscount
markup
SubscriptionPriceMargin
The margin value applied at the subscription level. May not be set.

SubscriptionPriceMarginRule
The margin rule applied at the subscription level. May not be set.

ERPPrice
The ERP for the whole term of the subscription associated with the charge, at the time indicated in SubscriptionStartDate
10.10
BillableQuantity
The billed quantity of the subscription. This may value be different than Quantity
4
BillingFrequency
The billing cycle of the subscription associated with the charge, can also be empty
Monthly
PricingCurrency
The currency of the UnitPriceForReseller and UnitPriceForCustomer. This currency may differ from the invoice billing currency (three-letter code - ISO 4217)
USD
MeterDescription
The meter description for the consumption line item
1/31/2023 Microsoft Defender for Servers - Standard P2 Node/Hour
ProductType

The type of the offer associated with this charge.
Possible values:

  • license
  • softwaresubscription
  • perpetualsoftware
  • azureplan
  • azurereservation
  • azurelegacy
  • azuresavingsplan
license
ReservationOrderId
For Azure Savings Plan this property corresponds to the savings plan order id on the Azure platform. For Azure reservations this is the reservation order id
8A05BC26-0542-4B6F-B97F-ECA5BAC54E9E
SubscriptionStartDate
The start date of billing for the subscription in the chosen format. This value can change once a subscription renews
12/2/2022 12:00:00 AM
SubscriptionEndDate
The end date of the term of the subscription in the chosen format
12/2/2023 12:00:00 AM
Id
The ID that uniquely identifies an invoice line item
7828D90D-2AC6-4F20-A95B-EE850BCD32A0
OfferProviderId
The ID on the provider's side that identifies the offer associated with the charge
DZH318Z0BQJ7:01DJ
CustomerProviderId
The ID of customer on the provider's side associated with this charge
CB0A371A-046A-46A9-A299-96BD755E67A8
SubscriptionProviderId
The ID of the subscription associated to this charge on the provider's side
FA2A91FD-7286-4DCD-9718-20048CCA832A
SubscriptionPONumber
The purchase order number of the subscription associated with this charge. Use this to identify a subscription in your systems
8543943821


License reconciliation files

License reconciliation files contain the charges of license legacy product charges, for example Office 365, for all your customers.

Column
DescriptionExample value
CustomerIdThe ID of the customer in CloudCockpit associated with the charge. Can be empty, for example when the CSP removed the relationship with the customer on the provider side and the customer wasn't in our system when that happened2C741C83-E111-4A77-BC5F-C2F065275FA9
CustomerNameThe company name of the customerCustomer name
ResellerMpnIdThe MPN ID of the reseller associated with the line item, if applicable (otherwise, it it is not set)3267
OrderIdThe ID that identifies the order associated with the charge from the provider side569423888785251957
SubscriptionProviderId
The ID of the subscription associated to this charge on the provider's side
FA2A91FD-7286-4DCD-9718-20048CCA832A
OfferProviderId
The ID on the provider's side that identifies the offer associated with the charge
B4D4B7F4-4089-43B6-9C44-DE97B760FB11
OfferNameThe name of the offer associated with the subscriptionVisio Plan 2
SubscriptionStartDate
The start date of billing for the subscription in the chosen format. This value can change once a subscription renews
12/2/2022 12:00:00 AM
SubscriptionEndDate
The end date of the term of the subscription in the chosen format
12/2/2023 12:00:00 AM
ChargeStartDate
The billing start date for this charge in the chosen format
2/2/2023 12:00:00 AM
ChargeEndDate
The billing end date for this charge in the chosen format
3/1/2023 12:00:00 AM
ChargeType
The type of charge on this line item. Possible values for the Microsoft provider include:
  • Activation fee
  • Cancel fee
  • Cancel instance prorate
  • Cycle fee
  • Cycle instance prorate
  • Prorate fees when cancel
  • Prorate fees when convert away from current offering
  • Prorate fees when convert to a new offering
  • New purchase cycle fee
  • Prorate fees when purchase
  • Prorate fee when renew
  • Prorate fees when activate

Cycle fee
QuantityThe quantity of the subscription associated with the charge4
CurrencyThe billing currency used for the line item (three-letter code - ISO 4217)EUR
DomainNameThe domain associated to the customer in Microsoft for the line itemtest@pte.onmicrosoft.com
SubscriptionName
The name of the subscription associated with the line item
Visio Plan 2 subscription
SubscriptionDescriptionThe subscription description associated with this purchaseVisio Plan 2
BillingCycleTypeThe billing cycle of the subscription associated with the charge, for example, Monthly, AnnualMonthly
ResellerIdThe ID of the reseller in CloudCockpit associated with this charge (can be empty)B8E08E60-19F7-4F95-AE29-A82D3CD53F84
ResellerNameThe company name of the reseller associated with this chargeReseller name
CustomerInternalIdThe internal ID of the customer associated with this charge. Use this to identify a customer in your systemsCustomerTest
SubscriptionInternalIdThe internal id of the subscription associated with this charge. Use this to identify a subscription in your systemsSubscriptionTest
UnitPriceForResellerThe price per single quantity that the reseller pays12.27
UnitPriceForCustomerThe price per single quantity that the customer pays13.11
AmountForReseller
The total price per quantity, not including taxes, for the reseller
12.87
AmountForCustomer
The total price per quantity, not including taxes, for the customer
13.27
SubtotalForReseller
The cost the reseller pays for the subscription on this charge before taxes.
SubtotalForReseller = ROUND(UnitPriceForReseller x Quantity, 2)
49.08
SubtotalForCustomer
The cost the customer pays for the subscription on this charge before taxes.
SubtotalForCustomer = ROUND(UnitPriceForCustomer x Quantity, 2)
52.44
TaxForResellerThe taxes the reseller pays for the subscription on this charge6.23
TaxForCustomerThe taxes the customer pays for the subscription on this charge6.83
TotalForReseller
The cost the reseller pays for the subscription on this charge after taxes.
TotalForReseller = ROUND(SubtotalForReseller + TaxForReseller, 2)
55.31
TotalForCustomer
The cost the customer pays for the subscription on this charge after taxes.
TotalForCustomer = ROUND(SubtotalForCustomer + TaxForCustomer, 2)
59.27
TotalOtherDiscountForReseller
The discount included in the charge on the reseller context
1.31
TotalOtherDiscountForCustomer
The discount the customer receives for the subscription on this charge
1.35
CustomerPriceMarginThe value of the margin applied at the customer level5
CustomerPriceMarginRuleThe margin rule applied at the customer level. The possible values are: markup, margin, splitmargin and erpminusdiscountmarkup
SubscriptionPriceMarginThe margin value applied at the subscription level. May not be set.
SubscriptionPriceMarginRuleThe margin rule applied at the subscription level. May not be set.
ERPPriceThe ERP for the whole term of the subscription associated with the charge, at the time indicated in SubscriptionStartDate10.10
SubscriptionId
 The ID of the subscription associated with the charge. Can be empty, for example when the subscription is not in our system
03DE5ECE-EC93-42A9-8F15-9A770AC1F832
Id
The ID that uniquely identifies a license invoice line item
26158c5a-6eee-4ada-910e-b245947bf1e0
CustomerProviderIdThe ID of customer on the provider's side associated with this chargeCB0A371A-046A-46A9-A299-96BD755E67A8
SubscriptionPONumber
The purchase order number of the subscription associated with this charge. Use this to identify a subscription in your systems
8543943821
OfferId
The ID of the offer in CloudCockpit to which the line item is associated with
A1D4B7F4-4089-43B6-9C44-DE97B760FB22


Daily rated usage reconciliation files

Daily rated reconciliation files contain the charges of azure plan charges, for all your customers, grouped by resource group, by azure entitlement of each azure plan subscription, as well as if Partner Earned Credit was granted.

Column
DescriptionExample value
BillingCurrencyThe billing currency code (three-letter code - ISO 4217)EUR
CustomerIdThe ID of the customer in CloudCockpit associated with the charge. Can be empty, for example when the CSP removed the relationship with the customer on the provider side and the customer wasn't in our system when that happened2C741C83-E111-4A77-BC5F-C2F065275FA9
CustomerNameThe company name of the customerCustomer name
CustomerInternalId
The internal ID of the customer associated with this charge. Use this to identify a customer in your systems
CustomerTest
SubscriptionIdThe ID of the subscription associated with the charge. Can be empty, for example when the subscription is not in our system03DE5ECE-EC93-42A9-8F15-9A770AC1F832
SubscriptionProviderIdThe ID of the Azure Plan subscription associated to this charge on the provider's sideFA2A91FD-7286-4DCD-9718-20048CCA832A
SubscriptionName
The name of the subscription associated with the line item
Azure plan subscription
SubscriptionInternalIdThe internal id of the subscription associated with this charge. Use this to identify a subscription in your systemsSubscriptionTest
EntitlementId
 The ID of the azure subscription associated to the charge
fd666eca-477d-4f30-95ac-9ff265ce22aa
EntitlementDescription
The name of the azure subscription associated to the charge
Subscription-Demo
ResourceGroup
The name of the resource group associated to the charge
Demo-RG
PecAwarded
A boolean value indicating if the PEC was awarded on this charge
TRUE
SubtotalForReseller
The cost the reseller pays for the subscription on this charge before taxes.
49.08
SubtotalForCustomer
The cost the customer pays for the subscription on this charge before taxes.
52.44
Id
The ID that uniquely identifies the invoice line item
7828D90D-2AC6-4F20-A95B-EE850BCD32A0
CustomerProviderId
The ID of the customer on the provider's side associated with this charge
CB0A371A-046A-46A9-A299-96BD755E67A8
ResellerPriceMargin
The value of the margin applied by the CSP at the reseller level.
5
CustomerPriceMarginRuleThe margin rule applied at the customer level. The possible values are: markup, margin, splitmargin and erpminusdiscountmarkup
CustomerPriceMargin
The value of the margin applied at the customer level
5
SubscriptionPriceMarginRuleThe margin rule applied at the subscription level. May not be set.
SubscriptionPriceMargin
The margin value applied at the subscription level. May not be set.

SubscriptionPONumberThe purchase order number of the subscription associated with this charge. Use this to identify a subscription in your systems8543943821





    • Related Articles

    • Understanding reconciliation files as a CSP

      Downloading reconciliation files In CloudCockpit you can access reconciliation files by pressing the Administration menu and then clicking on Invoices. You can then apply a filter to the type of reconciliation file you want to download as shown next: ...

    • Reconciliation and Invoices Overview

      What is reconciliation? Reconciliation is a process that involves analysing each product charge and confirming that the data is accurate. You configure price margins for your resellers, customers, subscriptions and expect that to be reflected in the ...

    • Understanding reconciliation files as a Customer

      Downloading reconciliation files In CloudCockpit you can access reconciliation files by pressing the Company menu and then clicking on Invoices. You can then apply a filter to the type of reconciliation file you want to download as shown next: ...

    • Create Reseller CloudCockpit Portal

      Resellers can have a personalized CloudCockpit portal with its own custom domain. Take advantage of this feature to strengthen your brand presence and provide a better user experience for clients. The setup process has been streamlined and no longer ...

    • Migrate Reseller CloudCockpit Portal

      The CloudCockpit infrastructure that enables Resellers (Indirect Resellers) to have portals with their own custom domains is being upgraded. If your Resellers are using the old DNS configuration, please follow these steps to migrate their portals to ...