Overview
Square offers a complete set of business tools for merchants, including payments, order management, inventory management, and much more. They provide services to businesses of any size, from the independent corner coffee shop to the largest arenas. With this integration, you can pull data from Square into DWA for analysis.
KORE uses the Square API to import your data. To set up the integration:
- Visit dwa.koresoftware.com and click to open the login page.
- Select "Built with Square".
- Sign in using your Square credentials.
- Click "Allow" to grant KORE the necessary permissions.
- Inform your KORE Success Manager of the date and time you granted the permissions.
Screenshots
ERD and data dictionary
(This information is provided on a best-effort basis without guarantees. For clarity, some column names are omitted from this ERD.)
Download data dictionary (Excel file)
Table descriptions
Note: Data is incrementally updated every 15 minutes except where noted otherwise. View the warehouse status report on Tableau to check when data was updated.
| Orders | This table returns the order details. Each record represents a complete order for a customer at a location. |
| Orders_Line_Items | This table returns list of items in an order. Each record represents an unique item from a particular order. |
| Orders_Discounts | This table returns discount information. Each record represents a unique discount applied either at the order level or at an object/line item level. |
| Orders_Taxes | This table returns various taxes information. Each record represents a unique tax applied either at the order level or at an object/line item level. |
| Customers | This table returns the information about a shop's customers, such as their name information, contact details, address details, and whether they've agreed to receive email marketing. |
| Payments | This table returns a list of payments taken by the account making the request. Results are eventually consistent, and new payments or changes to payments might take several seconds to appear. |
| Payments_Processing_Fee |
This table returns the information on payment processing fees and fee adjustments assessed by Square for this payment. |
| Refunds |
This table returns the refunds information for the account making the request. Results are eventually consistent, and new refunds or changes to refunds might take several seconds to appear. |
| Refunds_Processing_fee |
This table returns the refund processing fees and the fee adjustments assessed by Square for this refund. |
| Catalog_Items |
This table returns the information about every item in catalog. Updated hourly. |
| Catalog_Categories | This table returns the information about different inventory categories. Updated hourly. |
| Locations | This table returns the information about a location where the store exists. Updated hourly. |
| Merchants | This table returns the information about Merchants associated with the shops. Updated hourly. |
| Orders_Refunds | This table returns the information about all the refunds for an order. Order ID is referenced from Order table. |
| Orders_Returns | This table returns a collection of items from sale orders being returned. Normally part of an itemized return or exchange. There is exactly one Return object per sale Order being referenced. Include the “ID” field from the Orders table. |
| Orders_Returns_Line_Items | This table returns a list of the line items associated with the return portion of the order. Include the “ID” field from the Orders table. |
| Orders_Returns_Discounts | This table returns a list of the line items associated with the return portion of the order and their discount details. Include the “ID” field from the Orders table. |
| Orders_Returns_Taxes | This table returns a list of the taxes associated with the return portion of the order. Include the “ID” field from the Orders table. |
| Orders_Tenders | This table returns a list of tenders that were used to pay for the order. Include the “ID” field from the Orders table. |
Example queries
Calculate the total numbers of sale orders, items sold, and revenue—ordered by date—for the past year:
SELECT
TRUNC(o.closed_at) Sale_Date,
COUNT(DISTINCT o.id) Sales_Orders,
COUNT(DISTINCT oli.catalog_object_id) Distinct_Items_Sold,
SUM(oli.quantity) Total_ItemsSold,
SUM(oli.total_money_amount) Revenue
FROM
square.orders o
LEFT JOIN square.orders_line_items oli
ON o.id = oli.order_id
WHERE
o.closed_at BETWEEN GETDATE()-365 AND GETDATE()
GROUP BY
TRUNC(o.closed_at)
ORDER BY
1,2,3
Report location-wise total sale orders, returned orders, and refunds for the past year:
SELECT
o.location_id,
l.name Location_Code,
l.business_name Business_Name,
COUNT(DISTINCT o.id) Total_Orders,
SUM (o.total_money_amount) Total_Amount,
o.total_money_currency Currency,
COUNT(DISTINCT or2.order_id) Returned_Orders,
SUM (orli.gross_return_money_amount) Total_Amount,
orli.gross_return_money_currency Currency,
COUNT(DISTINCT or3.order_id) Refunded_Orders,
SUM (or3.amount_money) Total_Amount,
or3.amount_money_currency Currency
FROM square.orders o
LEFT JOIN square.locations l
ON o.location_id = l.id
LEFT JOIN square.orders_returns or2
ON o.id = or2.order_id
LEFT JOIN square.orders_refunds or3
ON o.id = or3.order_id
LEFT JOIN square.orders_returns_line_items orli
ON or2.order_id = orli.order_id
WHERE
o.closed_at BETWEEN GETDATE()-365 AND GETDATE()
GROUP BY
o.location_id, l.name, l.business_name, o.total_money_currency,
orli.gross_return_money_currency,or3.amount_money_currency
ORDER BY 1,2,3