File size: 7,958 Bytes
1ad1eea
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
# Amazon knowledge pack: what the assistant reads before it queries an Amazon database.
# loopy_skills.py serves it, filtered to the databases the caller can open.
# Amazon columns are measured from each report header at sync (amazon_schema PROVENANCE), so no
# static column list exists. Field keys here are names the code already types: amazon_schema
# TABLE_SPECS identities and relationships, connector_spec links, and amazon_relational's
# date, money and quantity name sets. verify_agent_knowledge.py gates this.
# Plain ASCII only. The assistant repeats this text to users.
source: amazon
connector: amazon_sp
title: Amazon

scope:
  orders:
    database: ut_amzn_orders
    date_field: purchase_date
    rule: "For orders by day, week or month, count rows of ut_amzn_orders grouped by the day of purchase_date. One row is one order."
  cancelled:
    field: order_status
    values: [Cancelled]
    rule: "Exclude rows whose order_status is Cancelled when you count orders or sum sales."
  read_only:
    rule: "Amazon data is read only. Never create, change or send anything to Amazon."

traps:
  - id: field_list_first
    text: "Read the database's field list before a query. A field missing from the list does not exist in this workspace yet, because Amazon columns appear at the first sync."
  - id: dates_are_text
    text: "Amazon dates such as purchase_date are ISO 8601 text in UTC. The day is the first 10 characters. Say the days are UTC days."
  - id: currency
    text: "Amounts are in the currency field of each row. Do not add amounts across currencies."
  - id: sku_keys
    text: "The seller SKU is sku on orders, order items, settlements and fee estimates, msku on inventory, and seller_sku on listings and referral fees. Join on these, not on the product name."
  - id: amazon_vs_odoo
    text: "Odoo also carries the Amazon channel as the GIFTWARE DEALS partner. For an Amazon order or fee question use these databases, not Odoo."

databases:
  ut_amzn_orders:
    label: Amazon orders
    row: "One Amazon order, from the all orders report. The first line of the order supplies its item columns."
    fields:
      money: [item_price, item_tax, shipping_price]
      quantity: [quantity]
      date: [purchase_date, last_updated_date]
      status: [order_status]
    joins:
      - {database: ut_amzn_order_items, from: amazon_order_id, to: amazon_order_id}
      - {database: ut_amzn_settlements, from: amazon_order_id, to: order_id}
      - {database: ut_amzn_listings, from: sku, to: seller_sku}
    traps:
      - id: orders_by_day
        text: "This is the database for how many orders arrived per day. Count rows by the day of purchase_date and exclude order_status Cancelled."
      - id: first_line_only
        text: "item_price, quantity and sku here belong to the order's first line only. For units or sales of a whole order use ut_amzn_order_items."
      - id: pending_price
        text: "A Pending order has no item_price yet. Do not read a blank price as zero sales."

  ut_amzn_order_items:
    label: Amazon order items
    row: "One line of one Amazon order: one SKU with its quantity and price."
    fields:
      money: [item_price, item_tax, shipping_price, item_promotion_discount]
      quantity: [quantity]
      date: [purchase_date, last_updated_date]
      status: [order_status, item_status]
    joins:
      - {database: ut_amzn_orders, from: amazon_order_id, to: amazon_order_id}
      - {database: ut_amzn_listings, from: sku, to: seller_sku}
    traps:
      - id: line_total
        text: "item_price is the total for the line's quantity, not a unit price. Sum item_price for sales and quantity for units."
      - id: count_distinct
        text: "amazon_order_id repeats on every line of an order. Count distinct amazon_order_id for an order count, or use ut_amzn_orders."

  ut_amzn_settlements:
    label: Amazon settlements
    row: "One line of an Amazon settlement report: one charge, fee or payment inside one payout."
    fields:
      money: [amount, total_amount]
      quantity: [quantity_purchased]
      date: [posted_date, settlement_start_date, settlement_end_date, deposit_date]
      status: [amount_type, amount_description]
    joins:
      - {database: ut_amzn_orders, from: order_id, to: amazon_order_id}
      - {database: ut_amzn_listings, from: sku, to: seller_sku}
    traps:
      - id: signed_amounts
        text: "amount is signed. Fees and refunds are negative, so the sum of amount is net proceeds. Group by amount_type and amount_description to split sales from fees."
      - id: summary_line
        text: "The first line of each settlement carries total_amount and deposit_date and no order. Never add total_amount to amount."
      - id: payout_date
        text: "posted_date is when a line was posted. deposit_date is when the payout reached the bank. Use posted_date for activity by day."

  ut_amzn_inventory:
    label: Amazon inventory
    row: "One product at one disposition and location for one day, from the FBA inventory ledger summary."
    fields:
      money: []
      quantity: [starting_warehouse_balance, receipts, customer_shipments, customer_returns, ending_warehouse_balance]
      date: [date]
      status: []
    joins:
      - {database: ut_amzn_listings, from: msku, to: seller_sku}
      - {database: ut_amzn_listings, from: asin, to: asin1}
    traps:
      - id: one_day_only
        text: "Each day repeats the stock. Sum ending_warehouse_balance for one date only, never across dates."
      - id: sellable
        text: "Filter disposition SELLABLE for stock a customer can buy. Other dispositions are damaged or held stock."

  ut_amzn_fba_fee_estimates:
    label: Amazon FBA fee estimates
    row: "One SKU in one Amazon store on one snapshot day, with Amazon's estimated fees per unit."
    fields:
      money: [estimated_fee_total, estimated_referral_fee_per_unit, expected_fulfillment_fee_per_unit, your_price]
      quantity: []
      date: [_snapshot_date]
      status: []
    joins:
      - {database: ut_amzn_listings, from: sku, to: seller_sku}
      - {database: ut_amzn_listings, from: asin, to: asin1}
    traps:
      - id: latest_snapshot
        text: "Each sync adds a new _snapshot_date. Use the latest _snapshot_date for current fees."
      - id: estimate_not_charge
        text: "These are estimates per unit, not fees Amazon charged. Charged fees are in ut_amzn_settlements."

  ut_amzn_referral_fees:
    label: Amazon referral fee preview
    row: "One seller SKU on one snapshot day, with its price and Amazon's referral fee preview."
    fields:
      money: [price]
      quantity: []
      date: [_snapshot_date]
      status: []
    joins:
      - {database: ut_amzn_listings, from: seller_sku, to: seller_sku}
      - {database: ut_amzn_listings, from: asin, to: asin1}
    traps:
      - id: latest_snapshot
        text: "Each sync adds a new _snapshot_date. Use the latest _snapshot_date for current fees."

  ut_amzn_storage_fees:
    label: Amazon storage fees
    row: "One product at one fulfillment center for one month, with its estimated monthly storage fee."
    fields:
      money: [estimated_monthly_storage_fee]
      quantity: []
      date: [month]
      status: []
    joins:
      - {database: ut_amzn_listings, from: asin, to: asin1}
    traps:
      - id: per_center
        text: "A product has one row per fulfillment_center each month. Sum across centers for its monthly fee."

  ut_amzn_listings:
    label: Amazon listings
    row: "One active or inactive listing for one seller SKU."
    fields:
      money: [price]
      quantity: [quantity]
      date: [open_date]
      status: []
    joins:
      - {database: ut_amzn_orders, from: seller_sku, to: sku}
      - {database: ut_amzn_inventory, from: seller_sku, to: msku}
    traps:
      - id: one_snapshot
        text: "Listings keeps one copy per snapshot day. Use the latest day for the current catalogue."