Building a data warehouse integration

Follow the steps below to build a data warehouse integration with Toast.

To follow these instructions, you must have the following scopes:

  • cashmgmt:read

  • config:read

  • labor.employees:read

  • labor:read

  • menus:read

  • restaurants:read

  • orders:read

This document describes how to build a general data warehouse. For more detailed information about how to report on specific types of data, see Building a sales report and Building labor reports.

  1. Complete initial integration setup 

    Review and implement the instructions on the Getting Started section.

  2. Determine closeout hour 

    The closeoutHour value in the General object returned by the restaurants API contains the restaurant's closeout hour.

    The default closeout hour is 4:00 a.m. local time unless a Toast employee changes this setting. The businessDate value on API data changes after the closeoutHour.

    Consider Daylight Savings Time when interacting with the closeout hour.

  3. Review menu and order structure 

    Reporting on sales depends on restaurants' usage of menu items and the overall structure of orders. To understand Toast menu concepts before you begin development, review menu hierarchy information. To review order structure, see Orders API overview.

  4. Plan your display 

    The following table provides suggested information that you can display in different types of reports. For more information about the difference between orders, checks, item selections, and payments, see Orders API overview.

    Report type

    Object

    Values

    Order data

    Orders

    Consider displaying the following reporting information for orders:

    • Dining option name

    • Dining option behavior

    • Revenue center name

    • Server name

    • Service area name

    • Table name

    • Numbers of guests

    • Order source

    • Restaurant service name

    • Opened date

    • Modified date

    • Paid date

    • Closed date

    • Voided status

    Checks

    Consider displaying the following reporting information for checks:

    • Display number

    • Tab name

    • Total amount

    • Tax amount

    • Refunded amount

    • Applied discount name(s)

    • Applied discount amount(s)

    • Applied service charge name(s)

    • Applied service charge amount(s)

    Menu item selections

    Consider displaying the following reporting information for item selections:

    • Menu item name (use displayName value on Selection object)

    • Applied modifier names (modifiers may be nested arbitrarily deep on a menu item)

    • Price

    • Applied tax rate names

    • Applied tax values

    • Applied discount name(s)

    • Applied discount amount(s)

    • Sales category name

    • Item tags (retrieve from menus API and cache this data)

    Payments

    Consider displaying the following reporting information for payments:

    • Amount

    • Tip amount

    • Refund information

    • Type

    • Card entry mode

    • Payment status

    Cash transactions

    Cash entries

    Consider displaying the following reporting information for cash entries:

    • Cash entry type

    • Cash entry date

    • Amount

    • Reason

    • Cash drawer name

    • Payout reason name

    • No sale reason name

    • Employee 1 name

    • Employee 2 name

    • Reversed cash transaction

    Deposits

    Consider displaying the following reporting information for cash deposits:

    • Cash entry date

    • Amount

    • Employee name

    • Reversed cash transaction

    Labor data

    Time entries

    Consider displaying the following reporting information for time entries:

    • Employee name

    • Job name

    • In date

    • Out date

    • Regular hours

    • Overtime hours

    • Declared cash tips

    • Non-cash tips

    • Break information: break type name, paid, in time, out time, missed

    • Cash gratuity service charges

    • Tips withheld

    • Hourly wage

    • Deleted

  5. Set up a recurring retrieval of transactional information 

    After deciding what to display in your reports, build a retrieval of transactional information. Below are recommendations for how to retrieve transactional data.

    Information type

    Transactional information

    Additional information

    Orders

    Retrieve all orders from the previous day using the /ordersBulk endpoint of the orders API. More information

    Use the startDate and endDate query parameters of the /ordersBulk endpoint in order to retrieve orders by their modifiedDate timestamp.

    Payments

    Retrieve all payments from the previous day using the /payments endpoint of the orders API. More information

    Use the paidBusinessDate, voidBusinessDate, and refundBusinessDate query parameters in order to retrieve all payment transactions from the previous business date. Retrieve payments at least six hours after the restaurant's closeoutHour in order to retrieve all credit card payments and fees from the previous day.

    Time entries

    Retrieve all time entries from the previous day using the /timeEntries endpoint of the labor API. More information

    Use the modifiedStartDate and modifiedEndDate query parameters of the /timeEntries endpoint in order to retrieve all time entries created or updated during the previous business day.

    Cash transactions

    Retrieve all cash transactions from the previous business day using the /deposits and /entries endpoints of the cash management API. More information

    Use the cash management API to retrieve all cash transactions from the previous business day, such as when employees open the cash drawer, add cash to the cash drawer, and retrieve their day's tips in cash.

  6. Consider a one-time historical information backfill per location 

    Consider loading historical order information for each restaurant location one time when that location starts using your integration. Toast support recommends retrieving twelve weeks of historical data.

  7. Retrieve restaurant configuration information 

    Consider including the configuration information shown in the table below in your reporting integration. After deciding which configuration information you want to display, set up a daily poll to the configuration API and menus API to retrieve it. To reduce the amount of data you receive when polling the configuration API, use the lastModified query parameter so that you only retrieve entities modified after the specified timestamp. In addition, only retrieve a new menu when you determine that your existing menu is outdated. For more information about when to retrieve a new menu, see Determining if a restaurant's menu data has gone stale.

    Consider including the following configuration in your analytics integration.

    Information type

    Configuration

    Additional information

    Reporting categories

    See Toast Central for more information about revenue centers and sales categories.

    Order configuration

    These pieces of configuration are often associated with orders. They describe how the order was placed and how its price was calculated.

    Menu information

    • Menu information, such as menu group names, menu item names, and item tags from the menus API. More information

    The menus API returns menu item names and other menu-related information you may want to display in your reporting platform.

    Restaurant information

    This restaurant information allows your integration to report on when and where orders were placed in your restaurant. See Toast Central for more information about hours and services and service areas and tables.

    Cash configuration

    Cash configuration allows you to display metadata about the cash transactions at a restaurant. For more information about working with cash entries, see Cash management overview.

    Labor configuration

    This labor information allows you to display information about restaurant employees, the jobs they do, and the breaks they take.

  8. Add additional business logic 

    Consider suppressing voided orders and deleted time entries from your reports by default and allowing users to actively choose to display these transactions. Use the voided value on an Order object to determine if an order was voided, and use the deleted value on TimeEntry objects to determine if a time entry was deleted.

  9. Build your data warehouse display and summaries 

    After building a basic data warehouse display of transactional data, consider summarizing Toast information in any of the following ways:

    • Order data:

      • Total sales value

      • Total number of orders

      • Total sales per dining option behavior

      • Total sales per revenue center

      • Total sales per sales category

      • Total sales per item tag

      • Total sales per service period (such as lunch or dinner)

      • Total amount discounted from all orders

      • Total amount discounted as a percentage of total sales

      • Number of voided orders

      • Number of voided orders as a percentage of all orders

    • Labor data:

      • Number of total labor hours

      • Total spent on labor

      • Missed breaks

    • Cash data:

      • Cash transactions by type

      • Cash drawer value

Congratulations on building a data warehouse integration with the Toast platform, providing restaurants greater visibility into key information that will help them run their business.