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."
|