In this article, we explain how you can find the data you need for reporting. Specifically you’ll learn:
- What data is available for reporting
- Definitions of the data
- The GQL objects and variables that the data can be found in
- Basic formulas to combine data
Marketplacer contains hundreds of GQL objects and variables that are accessible to operators. A mapping of these can be found here. Most data an operator would use to report on the day-to-day operations of the marketplace is contained in the 99 fields of the All Sales Report in the operator portal. The All Sales Report is Marketplacer’s most comprehensive report available in the platform, containing 99 fields of data about customer orders. For the purposes of this article, we will use these fields as a basis for the information to retrieve from GQL.
All Sales Report and Associated GQL Endpoints
The following table gives a full list of all the data that can be found in Marketplacer’s All Sales Report and the associated objects and variables to use to retrieve the data through GQL.
Field Name | Definition | GQL Object(s) | GQL Variable(s) and Associated Formula |
Created at | The date and time the invoice was created in Marketplacer. | invoice | createdAt |
Paid at | The time that the invoice was marked as paid in Marketplacer. This should be almost identical to createdAt, since all invoices that are created as a result of an operator submitting an orderCreate mutation are considered to be paid. It is possible to see a variation of a second. | invoice | paidAt |
Completed at | This is the date the seller entered as the date the lineItem on the invoice was shipped. | shipment | dispatchedAt |
Advert Image | The image of the specific variant that was purchased. | Variant > image | url |
Order # | The Marketplacer-generated order number. These numbers start from 1 in GQL, and will contain up to 0 leading zeros to form a 5 digit number in the UI. | order | legacyId |
Message to seller | A message that goes out to all sellers present on a specific orderCreate mutation. | order | messageToSeller |
Invoice # | The Marketplacer-generated invoice number. These numbers start from 1 in GQL, and will contain up to 0 leading zeros to form a 5 digit number in the UI. | invoice | legacyId |
Remittance Amount | This is the amount that should be remitted to the seller for the invoice. | invoice > remittance | amountFormatted |
Remittance including refunds | The amount that should be remitted to the seller, inclusive of any invoiceAmendments. |
remittance1 invoiceAmendments2 |
amountFormatted1 + remittanceFormatted2 |
Total including refunds | The total amount of the invoice, subtracting any amounts refunded to the customer. |
invoice1 refundRequest2 |
totalFormatted1 - refundAmountFormatted2 |
Remit after | This is used by the Marketplacer remittance batch. It can be derived by adding the Remittance Delay value (Configuration > Seller Settings > Remittance Delay) to the value of the Shipment Date in the All Sales Report or the createdAt variable on the shipment object in GQL. Please ensure all lineItems on the invoice have been shipped and use the latest createdAt date for the calculation. | shipment | createdAt + Remittance Delay |
Including shipping remittance | Denotes whether to include the shipping cost in the remittance. Can be found in the Operator Portal (Sellers > Manage Sellers > Edit > Remittance Details > “Include shipping cost in remittance”). | N/A | N/A |
Withhold item tax from seller remittance | Denotes whether to withhold item tax from the remittance to the seller. Can be found in the Operator Portal (Configuration > Finance Settings > Tax Settings > “Withhold item tax from being remitted to sellers?”). | N/A | N/A |
Withhold postage tax from seller remittance | Denotes whether to withhold postage tax from the remittance to the seller. Can be found in the Operator Portal (Configuration > Finance Settings > Tax Settings > “Withhold postage tax from being remitted to sellers?”). | N/A | N/A |
Account Code | The seller’s account code set in the seller profile. | seller | accountCode |
Account External Id | The external ID set in the seller profile. | seller | externalId |
Note | A note the seller can send on the shipment. | shipment | note |
Shipment date | The date the line item on the invoice shipped out. | shipment | createdAt |
Remitted At | The date the remittance advice was emailed to the operator. | invoice | remittedAt |
Refunds | The Marketplacer-generated refund ID. | refundRequest | legacyId |
Refund Totals | The total amount of money refunded to the customer on the invoice. | refundRequest | refundAmountFormatted |
Recovered Amounts | The total amount of the invoice, inclusive of any amounts refunded to the customer and any commission given back to the seller in the case of returns. |
Invoice1 refundRequest2 InvoiceAmendment3 |
totalFormatted1 - refundAmountFormatted2 + remittanceFormatted3 |
Refund Dates | The date the refund was approved by the operator. | refundRequest | refundedAt |
Payment Action | Denotes whether the operator has approved the release of the remittance advice. | N/A | N/A |
Items in Invoice | The total number of items in the invoice. | invoice > lineItems | quantity |
Line Item Status | The status of the lineItem. | lineItem | status |
Invoice Status | The status of the invoice. | invoice | statusFlags |
Order Status | The status of the order. | order | status |
Name | The name of the customer on the order. | order | firstName and surname |
Email address | The email address of the customer on the order. | order | emailAddress |
Phone | The phone number of the customer on the order. | order | phone |
Company name | The company name of the customer on the order. | order | companyName |
Address | The address of the customer on the order. | order | address |
City | The city associated with the address on the order. | order > address | city |
Buyer country | The country code associated with the address on the order. | order > address | country > code |
Zip code | The zip code associated with the address on the order. | order > address | postcode |
State | The two-letter state abbreviation associated with the address on the order. | order > address | state > short |
IP address | The IP address the order was submitted to Marketplacer from. | N/A | N/A |
Retailer | The name of the seller the invoice corresponds to. | seller | businessName |
Retailer address | The business address of the seller, as input in the seller profile. | seller > address | address |
Retailer city | The city associated with the address in the seller’s profile. | seller > address | city |
Retailer country | The country code associated with the address in the seller’s profile. | seller > address | country > code |
Retailer zip code | The zip code associated with the address in the seller’s profile. | seller > address | postcode |
Retailer state | The two-letter state abbreviation associated with the address in the seller’s profile. | seller > address | state > short |
Listing # | The Marketplacer-generated product number. | lineItem | advertLegacyId |
Listing name | The title of the product. | lineItem | advertTitle |
Variant name | The title of the variant. | lineItem | variantName |
Sku | The sku associated with the variant. | lineItem | variantSku |
Weight | The weight associated with the variant. | lineItem > variant > shippingParcel | weight |
Mass unit | The mass unit associated with the variant. | lineItem > variant > shippingParcel | massUnit |
Taxon | The lowest level category associated with the lineItem. | lineItem > advert | taxon > displayName |
Brand | The brand associated with the lineItem. | lineItem > advert | brand > name |
Account Manager | The manager of the seller’s account. Can be found by going to Sellers > Manager Sellers > Account Manager. | N/A | N/A |
Sales Agent | The sales agent associated with the seller’s account. Can be found by going to Sellers > Manager Sellers > Account Manager. | N/A | N/A |
Quantity | The quantity of the lineItem being purchased. | lineItem | quantity |
Item Price | The price of the lineItem being purchased. This is the price of the individual item if there are multiple quantities. | lineitem | totalCents/quantity |
Tax on Invoice | The total tax on the invoice | invoice | taxTotalFormatted |
Discount Per Item | The discount per individual item on the invoice. | invoice > lineItems | discountFormatted/quantity |
Discount Per Item (cost borne by marketplace) | The discount per individual item on the invoice that is paid for by the operator. Need to ensure the adjustmentType is discount. |
invoice > lineItems > adjustments1 lineItems2 |
(amountFormatted1 + amountToWithholdFromSellerRemittanceFormatted)1 / quantity2 |
Discount Per Invoice (cost borne by marketplace) | The discount on an invoice level that is paid for by the operator. Need to ensure the adjustmentType is discount. | invoice > lineItems > adjustments1 | (amountFormatted + amountToWithholdFromSellerRemittanceFormatted) |
Discount Per Item (cost borne by retailer) | The discount per individual item on the invoice that is paid for by the seller. Need to ensure the adjustmentType is discount. |
invoice > lineItems > adjustments1 lineItems2 |
(amountToWithholdFromSellerRemittanceFormatted)1 / quantity2 |
Discount Per Invoice (cost borne by retailer) | The discount on an invoice level that is paid for by the seller. Need to ensure the adjustmentType is discount. |
invoice > lineItems > adjustments1 lineItems2 |
amountToWithholdFromSellerRemittanceFormatted |
Discount Per Invoice | The total discount at an invoice level. | invoice | discountFormatted |
Discount Types Per Invoice | The types (descriptions) of the discounts on the invoice. Need to ensure the adjustmentType is discount. | invoice > lineItems > adjustments1 | description |
Postage before shipping discount | The postage cost at an invoice level. | invoice | shippingCostFormatted |
Invoice postage (ex Tax) | The postage cost at an invoice level, excluding tax. | invoice | shippingCostFormatted |
Tax on Postage | The tax amount on the postage cost at an invoice level. | invoice | taxShippingFormatted |
Total Postage | The total postage amount on the invoice, including tax. | invoice | shippingCostFormatted + taxShippingFormatted |
Fees Per Invoice | The fees on an invoice level. Need to ensure the adjustmentType is fee. | invoice > lineItems > adjustments1 | amountFormatted |
Fee Type Per Invoice | The types (descriptions) of the discounts on the invoice. Need to ensure the adjustmentType is discount. | invoice > lineItems > adjustments1 | description |
Consumer Paid | The amount of money the consumer has paid for the items on the invoice. | invoice | totalFormatted |
Invoice Total | The amount of money the goods on the invoice cost before being discounted. |
invoice1 invoice > lineItems > adjustments2 |
totalFormatted1 + amountFormatted2 |
Commission $ | The total commission on the invoice. | invoice | commissionAmountFormatted |
Commission Tax | The total tax on the commission on the invoice. | invoice | commission AmountTaxFormatted |
Commission $ (inc Tax) | The total commission amount on the invoice, including the commission tax. | invoice |
commissionAmountFormatted1 + commissionAmountTaxFormatted2 |
Commission (exc Tax) | The total commission amount on the invoice, excluding the commission tax. | invoice | commissionAmountFormatted |
Merch Fees $ | The total merchant fees on the invoice. | invoice | merchantFeeTotalFormatted |
Merch Fees Tax | The total merchant fee tax on the invoice. | invoice | merchantFeeTaxTotalFormatted |
Merch Fees $ (inc Tax) | The total merchant fees on the invoice, including the merchant fee tax. | invoice | merchantFeeTotalFormatted + merchantFeeTaxTotalFormatted |
Merch Fees (exc Tax) | The total merchant fees on the invoice, excluding the merchant fee tax. | invoice | merchantFeeTotalFormatted |
Commission & Merch Fees $ | The total merchant fees and commission fees on the invoice. | invoice | commissionAmountFormatted + merchantFeeTotalFormatted |
Commission Per Item (%) | The commission percentage per item on the invoice. | invoice | (commissionAmountFormatted / totalFormatted) |
Commission Per Item ($) | The commission amount per item on the invoice. | invoice > lineItems | (commissionAmountFormatted / quantity) |
Commission Per Item Tax | The commission tax amount per item on the invoice. | lineItems | (commissionAmountTaxFormatted / quantity) |
Commission Per Item ($) (inc Tax) | The commission amount per item on the invoice, including the commission tax amount. | lineItems | (commissionAmountFormatted + commissionAmountTaxFormatted / quantity) |
Commission Per Item (exc Tax) | The commission amount per item on the invoice, excluding the commission tax amount. | invoice > lineItems | (commissionAmountFormatted / quantity) |
Tracking numbers | The tracking numbers for each shipment on the invoice. | invoice > lineItems > shipments | trackingnumber |
Tracking carriers | The tracking carriers for each shipment on the invoice. | invoice > lineItems > shipments | carrier |
Action | The action to release the invoice to be sent to the remittance advice batch. | N/A | N/A |
External ID | The external ID on the order. | order | externalIds |
Other Considerations
Each All Sales Report looks different depending on the fields the operator has selected, in addition In addition to the country the marketplace is set up in, the currency that is being used, the configuration of the individual marketplace and which fields are selected as visible. However, the GQL endpoints are available to all operators, regardless of their UI setup.
Example Queries
Full discussion on querying with GraphQL is outside the scope of this article, there are some good learning resources on the official GraphQL site if you’ve not worked with GraphQL before. Especially important to be familiar with is how to order.
This section provides some example queries that contain information found in the All Sales Report.
Postman Collection
Our Postman collection contains many more query and mutation examples for your use. You can download the collection here.
Example 1: Basic Invoices Query
This query returns basic information for the invoices on the marketplace. You’ll be able to see information about the line items on the invoice, when the seller states the items were dispatched, and when the invoices were created in Marketplacer.
query {
invoices {
pageInfo {
hasNextPage
endCursor
}
edges{
node{
id
legacyId
createdAt
updatedAt
order{
updatedAt
id
}
statusFlags
lineItems{
id
variantId
variantSku
variantBarcode
variantName
totalCents
}
shipments{
id
dispatchedAt
shippedItems{
id
lineItem{
id
advertTitle
}
}
}
}
}
}
}
Example 2: Detailed Invoice Query
This query gives more detailed information about each invoice, including detailed financial information like shipping cost, refund amounts, and commission amounts. The majority of the fields contained in the above table are present in this query.
query {
invoices {
pageInfo {
hasNextPage
endCursor
}
edges {
node {
legacyId
id
totalCents
shippingCostCents
subtotalCents
legacyId
createdAt
updatedAt
statusFlags
remittedAt
lineItems {
commissionAmountCents
postageCents
itemAmountCents
id
}
annotations {
id
createdAt
invoiceAnnotationType {
id
createdAt
}
}
seller {
businessName
accountName
accountCode
address {
address
city
country {
code
}
postcode
state {
short
}
}
}
order {
id
legacyId
status
firstName
surname
emailAddress
phone
companyName
metadata {
key
value
updatedAt
}
address {
address
city
state {
short
}
country {
code
}
}
}
lineItems {
id
metadata {
key
value
}
advertLegacyId
advertTitle
variantName
variantSku
variantBarcode
variant {
barcode
}
shipments {
carrier
dispatchedAt
trackingNumber
trackingLink
}
inventory {
seller {
address {
address
}
}
}
totalCents
subtotalCents
postageCents
commissionAmountCents
quantity
}
amendments {
id
remittanceCents
lineItems {
id
}
}
refundRequests {
id
refundAmountCents
recoveredAmountCents
status
cashAmountCents
lineItems {
id
amountCents
}
}
shipments {
id
dispatchedAt
shippedItems {
id
lineItem {
id
advertTitle
}
}
}
}
}
}
}
Deprecated Functionality
We have now deprecated the following functionality, which is why you are not able to see these fields in GQL:
- Customisation
- Comments
- Reservation number
- Tax rate used
- Coupon used
- Abandonment email
- Abandonment coupon email
- Payment gateways
- Transaction IDs
- Transaction ID links
Related articles