The Sales Report sample application shows how to build a report that details all the items sold between two dates.
The Sales Report sample application summarizes all the items sold and provides sales data for each item sold, such as:
- A description of the item.
- The number of items purchased.
- The price per item.
- The total sales amount for the item.
- The inventory on hand for the item.
The Python sample application also includes a test data seeding script that uploads sample catalog items and customers to your Square account in the Sandbox environment. The script generates orders, payments, and inventory counts based on the uploaded data.
This scenario shows how to download, install, and run the sample. The second part of the scenario provides a detailed look at the Python code in the application.
By the end of this scenario, you'll know:
- How to filter order data by time period and completion status.
- How to retrieve product catalog data for items that have been purchased.
- How to determine the current inventory for an item.
The prerequisites for running the test data seeding script and sales reporting script in the Sandbox environment are minimal, while running the Sales Report application in production imposes normal production prerequisites.
Before you begin, you'll need to do the following:
- Create a Square account, if you don't already have one. For more information, see Create an Account and Application.
- Install the following:
The Sales Report application is available at https://github.com/square/squaredev-sales-report/. To download it, choose Code and then choose Download ZIP. When the download is complete, expand the .zip file and follow the installation instructions in the readme.md file.
To configure the application, create a new file named .env by copying the contents of the provided .env.example file. In the new .env file, make the following changes:
Your_Square_Access_Token with your Square access token and replace
Your_Square_Location_Id with your Square location ID.
The application .zip file includes a Python program to generate sample data. Run the following command:
This script does the following:
- Reads the seed_customers.json file and calls
CreateCustomerfor each item in the file.
- Reads the seed_catalog.json file and calls
BatchUpsertCatalogObjectsto create all the catalog items at once.
- Calls BatchChangeInventory to generate item counts for each of the catalog items.
- Calls CreateOrder to generate an order for each of the catalog items.
You're now ready to run the sales reporting script. You need to provide a start date and end date for the report.
To see a usage summary, run the following command:
The output looks like the following:
Suppose that the current date is 2024-01-18 and that you want a report showing sales data for the previous three months. You can run the following command to generate this report:
The program walks through all the completed orders between the two dates and then generates the order IDs and associated line items for each:
Next, the program generates the sales report:
The columns in the report are as follows:
- Item - The item name and variation name, retrieved from the item catalog.
- SKU - The stock keeping unit.
- Price - The unit price for the item.
- QtySold - How many of these items were sold during the specified time period.
- TotalSales - The Price x QtySold total.
- QtyRemaining - The number of items still in inventory.
The last line of the report shows the grand total sales during the time period.
When you've finished running the report, you can optionally remove the test data. To do this, run the following command:
The application requires a start date and an end date for the report. It retrieves only the orders that were fully paid and completed between these two dates.
Each order is represented by an Order object. Within each
Order object, there's an array of OrderLineItem objects that comprise the order. Each entry in the array contains basic data (such as name, quantity, and price) about one item. The Sales Report application keeps a running tally of item details and uses this tally to generate the final sales report at the end of the program.
If an item appears in the seller's catalog, it has a
catalog_object_id associated with it. The application uses this ID to retrieve a CatalogVariation with further details about the item.
CatalogVariation object contains several fields that aren't visible in the
Order, such as its stock-keeping unit identifier (
sku) and unit price (
price_money). As the Sales Report application runs, it adds these details to the item tally. It also accumulates the total number of each item variation sold during the specified time period, so that these totals appear on the report.
If an item has inventory tracking enabled, the application retrieves the current quantity remaining for the item and adds it to the tally.
When all the data has been gathered, the application generates the results. Each item from the tally is listed, along with a summary of the money received during the time period.
The following sections present call flow details in the order that the application calls the Square API. The flow starts when you run the
The program creates and maintains a Python dictionary named
item_tally. As it runs, the program adds objects to
item_tally that represent the output of the sales report and captures sales data for that item. When all the data has been retrieved, the program iterates through
item_tally and generates each item's data.
The program calls SearchOrders to retrieve all the completed orders for the specified time period. By default,
SearchOrders retrieves all the orders for that location, so two filters are used to narrow the results:
state_filter- Orders that have been
date_time_filter- Orders that were completed between the two dates.
SearchOrders endpoint supports pagination, so that programs can retrieve one page of orders at a time. The
limit variable is hard-coded at 25 in the code, but you can change it to a different value if needed.
Each order has a
line_items array containing OrderLineItem objects with basic data about each item in the order. The program captures this data in the
item_tally dictionary and uses the Catalog API to get details about each item. For more information, see Retrieve catalog data.
The program continues to retrieve orders, page by page, until there are no more orders that match the
SearchOrders filtering criteria.
For each item in an order, the program calls RetrieveCatalogObject to get more details about the item.
The resulting CatalogItemVariation object contains further information about the item, including
sku. These fields are captured in the
item_tally dictionary, so that they can appear on the final sales report.
track_inventory attribute of a CatalogItemVariation is set to
true, Square keeps track of changes to that variation's inventory. In the seed-data-catalog.json file (used by the
seed-data.py script), inventory tracking is enabled for each item variation.
The current item count is saved in the
When all the orders have been processed and all the item data has been saved to
item_tally, it's time to generate the sales report.
First, the report header is printed. This consists of the start date and end date, along with the column headings. Next, the program loops through
item_tally and prints the details for each item. Finally, the grand total is printed.
This scenario shows you how to use the Orders, Catalog, and Inventory APIs to build a simple sales report. You can use the code as is or modify it to suit your needs.
The sample uses read operations on these APIs to get data. You should explore their full capability by reading the following topics: