--- title: "Special considerations for transaction data" date: "2024-03-18T13:36:25+00:00" summary: "Learn how to handle complex transaction data scenarios, including returns, discounts, and cancellations. Discover best practices for accurate revenue tracking and customer behavior analysis in e-commerce and retail environments." image: type: "page" url: "/customer-data-platform/special-considerations-transaction-data" id: "a96aae11-179d-4f35-ad01-fb63f64955c5" --- Transaction counts and returns ------------------------------ Customer Transaction Count is calculated as a distinct count of Transaction IDs. Standard transaction item sub-types are “Shipped” and “Returned”. Additional sub-types such as “Demand” or “Canceled” must be added only if there are clear use cases for these sub-types. Ideally “Shipped” and “Returned” lines of the same purchase must have the same Transaction ID. If so, all the transaction lines linked to the same Transaction ID are counted under the same transaction. Otherwise, they create different transactions and increase the transaction count separately. Base scenario - a customer makes a purchase on January 1, 2023 and spends $125 ------------------------------------------------------------------------------ The following conditions apply to the base scenario: 1. Customer makes a transaction on January 1, 2023 and purchases two products, T-Shirt and Short. 2. The product number for T-Shirt is AF93 and the product number for Short is AC04. 3. Customer purchases 2 T-Shirts (AF93) for $100 and 1 Short (AC04) for $25. 4. Customer spends $125 in total. **Master Customer Number** **Transaction Date** **Source Transaction Number** **Subtype** **Source Transaction Line Number** **Transaction Date Line** **Source Product Number** **Product Quantity** **Sales Revenue** XXX 1/1/23 1234567 Shipped 1234567\_AF93\_Shipped 1/1/23 AF93 2 $100 XXX 1/1/23 1234567 Shipped 1234567\_AC04\_Shipped 1/1/23 AC04 1 $25 ### Results of the base scenario The following table displays the resulting counts and revenue related to the base scenario: **Parameter** **Number** **Description** **Buyer Count** 1 (Customer Number) **Transaction Count** 1 (# of distinct Master Customer Number) **Transaction Line Count** 2 (# of distinct Source Transaction Line Number) **Product Quantity** 3 (Total of Product Quantity) **Item Count** 2 (# of distinct Source Product Number) **Total Revenue** $125 (Total of Sale Revenue) Scenario #1 with full return ---------------------------- In this scenario, the customer returns all products that were added into the original transactions on January 5, 2023. Client order system links all returns to the original transaction. **Master Customer Number** **Transaction Date** **Source Transaction Number** **Subtype** **Source Transaction Line Number** **Transaction Date Line** **Source Product Number** **Product Quantity** **Sales Revenue** XXX 1/1/23 1234567 Shipped 1234567\_AF93\_Shipped 1/1/23 AF93 2 $100 XXX 1/1/23 1234567 Shipped 1234567\_AC04\_Shipped 1/1/23 AC04 1 $25 XXX 1/1/23 1234567 _Returned_ 1234567\_AF93\_Returned _5/1/23_ AF93 _\-2_ _\-$100_ XXX 1/1/23 1234567 _Returned_ 1234567\_AC04\_Returned _5/1/23_ AC04 _\-1_ _\-$25_ ### Results of scenario #1 with full return The following table displays the resulting counts and revenue related to scenario #1: **Parameter** **Number** **Description** **Buyer Count** 1 (Customer Number) **Transaction Count** 1 (# of distinct Master Customer Number) **Transaction Line Count** 4 (# of distinct Source Transaction Line Number) **Product Quantity** 0 (Total of Product Quantity) **Item Count** 2 (# of distinct Source Product Number) **Total Revenue** $0 (Total of Sale Revenue) In this table: * Transaction Count is 1 because all Transaction Line Items refer to the same Source Transaction Number. * Transaction Line Count is 4 because it includes both the shipping and return of two different products (Item Count). * Product Quantity is 0 because all purchased items in this transaction were returned. Scenario #2 with partial return ------------------------------- In this scenario, the customer returns 1 T-Shirt (ABC) and 1 Short on January 5, 2023, and keeps 1 T-Shirt. Client order system links all returns to the original transaction. **Master Customer Number** **Transaction Date** **Source Transaction Number** **Subtype** **Source Transaction Line Number** **Transaction Date Line** **Source Product Number** **Product Quantity** **Sales Revenue** XXX 1/1/23 1234567 Shipped 1234567\_AF93\_Shipped 1/1/23 AF93 2 $100 XXX 1/1/23 1234567 Shipped 1234567\_AC04\_Shipped 1/1/23 AC04 1 $25 XXX 1/1/23 1234567 Returned 1234567\_AF93\_Returned 5/1/23 AF93 _\-1_ _\-$50_ XXX 1/1/23 1234567 Returned 1234567\_AC04\_Returned 5/1/23 AC04 _\-1_ _\-$25_ ### Results of scenario #2 with partial return The following table displays the resulting counts and revenue related to scenario #2: **Parameter** **Number** **Description** **Buyer Count** 1 (Customer Number) **Transaction Count** 1 (# of distinct Master Customer Number) **Transaction Line Count** 4 (# of distinct Source Transaction Line Number) **Product Quantity** 1 (Total of Product Quantity) **Item Count** 2 (# of distinct Source Product Number) **Total Revenue** $50 (Total of Sale Revenue) In this table: * Product Quantity is 1 because the original purchase of 3 items (2 T-Shirt, 1 Short) followed by the return of 2 of them (1 T-Shirt, 1 Short) resulted in the customer retaining 1 T-Shirt. Scenario #3 with partial return ------------------------------- In this scenario, the customer returns 1 T-Shirt (ABC) and 1 Short January 5, 2023, and keeps 1 T-Shirt. Client order system creates a **separate** transaction for returns. **Master Customer Number** **Transaction Date** **Source Transaction Number** **Subtype** **Source Transaction Line Number** **Transaction Date Line** **Source Product Number** **Product Quantity** **Sales Revenue** XXX 1/1/23 _1234567_ Shipped 1234567\_AF93\_Shipped 1/1/23 AF93 2 $100 XXX 1/1/23 _1234567_ Shipped 1234567\_AC04\_Shipped 1/1/23 AC04 1 $25 XXX 1/1/23 _987654_ Returned 987654\_AF93\_Shipped 5/1/23 AF93 _\-1_ _\-$50_ XXX 1/1/23 _987654_ Returned 987654\_AC04\_Shipped 5/1/23 AC04 _\-1_ _\-$25_ ### Results of scenario #3 with partial return The following table displays the resulting counts and revenue related to scenario #3: **Parameter** **Number** **Description** **Buyer Count** 1 (Customer Number) **Transaction Count** 2 (# of distinct Master Customer Number) **Transaction Line Count** 4 (# of distinct Source Transaction Line Number) **Product Quantity** 1 (Total of Product Quantity) **Item Count** 2 (# of distinct Source Product Number) **Total Revenue** $50 (Total of Sale Revenue) In this table: * Transaction Count is 2 because the Client order system creates a separate transaction for returns. * Transaction Line Count is 4 because there are two Transaction Lines per transaction (Source Transaction Number). * Product Quantity is 1 because the customer retained 1 T-Shirt. In the following use cases, only the relevant feeds, lines, and columns are shown. Additionally, columns shown may or may not include all “required” columns from the feeds. To confirm which columns are required, refer to the documentation specific to each feed. Scenario #4 with product and order discounts, and a product return ------------------------------------------------------------------ In this scenario, all products are shipped on Day 1, one product has a discount of $10, the whole order has a $5 discount, and one product is returned on Day2. The transaction feed does not need to be updated, since neither the customer nor the date of the initial transaction changed. However, the transaction item needs to reflect those changes. Note that those changes will be represented incrementally and only the new/updated lines will be shown. You can provide either the increment as shown in each case or provide a full history every time, but you should follow the same conventions. The transaction-level discount will be spread evenly between the two items in the transaction in this example, but you can spread discounts based on the relative price of each item, or other means. **Feed: TransactionItem** **Day 1** **SourceTransactionItemNumber** **SourceTransactionNumber** **Type** **SubType** **InvoiceDate** **ShipDate** **ListPrice** **Discount** **SaleRevenue** **SourceProductNumber** **Quantity** **DateCreated** ABC1-Shipped ABC Purchase Shipped Day0 Day1 10 2.5 7.5 P1 1 Day1 ABC2-Shipped ABC Purchase Shipped Day0 Day1 30 12.5 17.5 P2 1 Day1 **Day 2** **SourceTransactionItemNumber** **SourceTransactionNumber** **Type** **SubType** **InvoiceDate** **ShipDate** **ListPrice** **Discount** **SaleRevenue** **SourceProductNumber** **Quantity** **DateCreated** ABC1-Returned ABC Purchase Returned Day0 Day2 \-10 \-2.5 \-7.5 P1 \-1 Day2 Transactions including “Demand” and “Cancel” Transaction Item data sub-types ---------------------------------------------------------------------------- As a reminder, standard Transaction Item sub-types are “Shipped” and “Returned”. Additional sub-types like “Demand” or “Canceled” should be added only if there are clear use cases for these sub-types. Storing “Demand” and “Cancel” transaction sub-types may introduce the risk of double-counting data from “Demand” and “Shipped” lines. The best way to avoid double-counting is to consider “Shipped” and “Returned” for the summary measures. Scenario #5 with partial order cancelation ------------------------------------------ In this scenario, an order is placed on Day 0, one product is canceled on Day 1, and one product is shipped on Day 2. The transaction feed does not need to be updated, since neither the customer nor the date of the initial transaction changed. However, the transaction item needs to reflect those changes. Note that those changes will be represented incrementally and only the new/updated lines will be shown. You can provide either the increment as shown in each case, or provide a full history every time, but you should follow the same conventions. **Feed: TransactionItem** **Day 0** **SourceTransactionItemNumber** **SourceTransactionNumber** **Type** **SubType** **InvoiceDate** **ShipDate** **SaleRevenue** **SourceProductNumber** **Quantity** **DateCreated** ABC1-Demand ABC Purchase Demand Day0   10 P1 1 Day0 ABC2-Demand ABC Purchase Demand Day0   30 P2 1 Day0 **Day 1** **SourceTransactionItemNumber** **SourceTransactionNumber** **Type** **SubType** **InvoiceDate** **ShipDate** **SaleRevenue** **SourceProductNumber** **Quantity** **DateCreated** ABC1-Canceled ABC Purchase Canceled Day0 Day1 \-10 P1 \-1 Day1 **Day 2** **SourceTransactionItemNumber** **SourceTransactionNumber** **Type** **SubType** **InvoiceDate** **ShipDate** **SaleRevenue** **SourceProductNumber** **Quantity** **DateCreated** ABC2-Shipped ABC Purchase Shipped Day0 Day2 30 P2 1 Day2