Introduction

This document outlines the data included in the Engage Warehouse Client Share for client Snowflake Reader accounts. The Engage Client Share schema is currently on Version 2 (released Friday, May 14, 2021).

Note: Snowflake Data Share eligibility requires the brand’s Snowflake instance to align with Olo’s Snowflake instance, which is hosted on AWS us-east-1. Olo may also provide Snowflake Reader access in this region upon request and in accordance with your contract deliverables.

For more details on the Snowflake Client Share, see this doc:

Engage Data Warehouse

In This Document:

• Data Dictionary
  • Table and column definitions -- use this reference to determine what data exists in each table, and how data can be joined together across tables
• Sample Queries
  • At the bottom of this document are some samples of how this data can be used to generate simple analyses. These queries can be adapted to suit your own analytical needs, and can serve as a starting point for exploring the data.

Data Dictionary

The views below are included in the Client Share Schema (v2) in the Engage Warehouse.

Automations

wisely_share_v2.automations

Contains one row for every Marketing Automation

Updated: Hourly

• id - primary key, unique identifier for an automation
• name - display name in the Automations Dashboard
• is_active - if TRUE, the automation is currently active
• created_at - time the automation was created (in UTC)
• updated_at - time the automation was most recently updated (in UTC)
• deleted_at - time the automation was deleted (in UTC)

Automation Action Types

wisely_share_v2.automation_action_types

Contains one record for each distinct type of action (eg. 'email', 'sms', 'delay', etc.).

Updated: Every 3 Hours

• id - primary key, unique identifier for an action type
• name - name of this action type
• created_at - time the record was created (in UTC)
• updated_at - time the record was most recently updated (in UTC)
• deleted_at - time the record was deleted (in UTC)

Automation Actions

wisely_share_v2.automation_actions

Contains one record for each action tied to an automation

Updated: Every 3 Hours

• id - primary key, unique identifier for an action tied to an automation
• next_action_id - foreign key, references automation_actions.id, represents the next action in the sequence of actions for this automation
• automation_id - foreign key, references automations.id, represents the automation that this action belongs to
• action_type_id - foreign key, references action_types.id, represents the type of this action (eg. 'email', 'sms', 'delay', etc.)
• is_pre_entry - if TRUE, this action is a pre-entry conditional field, and guests must pass the conditional in order to move forward in the automation
• is_transactional - if TRUE, this action is "transactional", which means it can be carried out even if the guest is not opted-in to email/sms marketing
• created_at - time the record was created (in UTC)
• updated_at - time the record was most recently updated (in UTC)
• deleted_at - time the record was deleted (in UTC)

Automation History

wisely_share_v2.automation_history

Contains one record for each historical event triggered by an automation.

Updated: Hourly

• id - primary key, unique identifier for this history record
• automation_id - foreign key, references automations.id, represents the automation this history record belongs to
• guest_id - foreign key, references guests.id, represents the guest with which this history record is associated
• current_action_history_id - foreign key, references automation_action_history.id, represents the step of the automation this guest is currently at
• completed_at - time at which this historical event completed (null if not yet completed, or if it failed)
• failed_at - time at which this historical event failed (null if not yet completed, or if it did not fail)
• is_pre_entry - if TRUE, this guest was blocked from progressing in the automation due to a pre-entry filter
• event_data - semi-structured column representing various additional fields related to the event that triggered this historical record
• created_at - time the record was created (in UTC)
• updated_at - time the record was most recently updated (in UTC)
• deleted_at - time the record was deleted (in UTC)

Automation Action History

wisely_share_v2.automation_action_history

Contains one record for each action tied to a historical event triggered by an automation. You can use this table to associate a given action_id to a given history_id.

Updated: Hourly

• id - primary key, unique identifier for this action history record
• history_id - foreign key, references automation_history.id, represents the historical event record associated with this unique history/action pair
• action_id - foreign key, references automation_actions.id, represents the action associated with this unique history/action pair
• created_at - time the record was created (in UTC)
• updated_at - time the record was most recently updated (in UTC)
• deleted_at - time the record was deleted (in UTC)

Automation Action Email History

wisely_share_v2.automation_action_email_history

• id - primary key, unique identifier for this action email history record
• action_history_id - foreign key, references automation_action_history.id, represents the action history associated with this action email history record
• delivered_at - time at which the email was successfully delivered to the guest (in UTC); NULL if never delivered
• opened_at - time at which the email was first opened by the guest (in UTC); NULL if never opened
• clicked_at - time at which a link in the email was first clicked on by the guest (in UTC); NULL if never clicked on a link
• unsubscribed_at - time at which the user unsubscribed after receiving the email (in UTC); NULL if never unsubscribed
• dropped_at - time at which Sendgrid dropped this email (in UTC) due to the email belonging on one of the designated blacklists (bounces, spam reports, and unsubscribes); NULL if never dropped
• group_unsubscribe_at - time at which the recipient opted out from a specific type of email (in UTC) via the Unsubscribe Groups link in the email; NULL if never group unsubscribed
• deferred_at - time at which the receiving email server delayed acceptance of the message (in UTC); NULL if never deferred
• spamreport_at - time at which the recipient reported this message as spam (in UTC); NULL if never reported as spam
• group_resubscribe_at - time at which the recipient opted back in to a specific type of email (in UTC); NULL if never group resubscribed
• bounced_at - time at which the receiving email server denied the message (in UTC); email address is suppressed moving forward; NULL if never bounced
• blocked_at - time at which the receiving email server denied the message (in UTC); email address is not suppressed moving forward; NULL if never blocked
• created_at - time the record was created (in UTC)
• updated_at - time the record was most recently updated (in UTC)
• deleted_at - time the record was deleted (in UTC)

Automation Action Push Notifications

wisely_share_v2.automation_action_push_notifications

Contains one record for each distinct push notification and the action it is associated with.

Updated: Hourly

• id - primary key, unique identifier for an action push notification config record
• action_id - foreign key, references automation_actions.id, represents the automation action this push notification is associated with
• created_at - time the record was created (in UTC)
• updated_at - time the record was most recently updated (in UTC)
• deleted_at - time the record was deleted (in UTC)

Automation Action Push Notification History

wisely_share_v2.automation_action_push_notification_history

Contains one record for each historical event triggered by an automation that resulted in sending a push notification to a guest.

Updated: Hourly

Automation Action SMS History

wisely_share_v2.automation_action_sms_history

Contains one record for each historical event triggered by an automation that resulted in sending a text message to a guest.

Updated: Every 4 Hours

• id - primary key, unique identifier for this action sms history record
• action_history_id - foreign key, references automation_action_history.id, represents the action history associated with this action sms history record
• twilio_sid - external key from Twilio, the Message SID is the unique ID for any message successfully created by Twilio’s API. It is a 34 character string that starts with 'SM' for text messages and 'MM' for media messages
• delivered_at - time at which the message was successfully delivered to the guest (in UTC); NULL if never delivered
• sent_at - time at which the message was sent to the guest (in UTC)
• created_at - time the record was created (in UTC)
• updated_at - time the record was most recently updated (in UTC)
• deleted_at - time the record was deleted (in UTC)

Email Campaigns

wisely_share_v2.email_campaigns

Contains one row for every Email Campaign.

Updated: Every 6 hours

• id - primary key, unique identifier for an email campaign
• program_id - foreign key, references programs.id
• name - the name for an email campaign
• subject - the subject line for the email campaign
• preheader - the preheader created by the brand
• created_at - time that the record was created
• updated_at - time the email campaign was most recently updated
• updated_by_user_id - the user_id of the last user to update the email campaign
• deleted_at - time the email campaign was deleted (in UTC)

Email Campaign Guest History

wisely_share_v2.email_campaign_guest_history

Contains one row for every record of an individual guest's engagement with an individual instance of an Email Campaign.

Updated: Every 2 hours

• id - primary key, ID for the guest's activity with the campaign email
• campaign_id - foreign key, references email_campaigns.id
• guest_id - foreign key, references guests.id
• created_at - time that the record was created in UTC
• updated_at - time that the record was most recently updated in UTC
• delivered_at - time that the campaign email was delivered to this guest in UTC
• opened_at - time that the campaign email was opened by this guest in UTC
• clicked_at - time that a link in the campaign email was clicked on by this guest in UTC
• unsubscribed_at - time that the guest unsubscribed from this email in UTC
• deleted_at - time this record was deleted from our system in UTC
• is_holdout - if TRUE, guest was held out of from specific campaign id

Feedback Automation History

wisely_share_v2.feedback_automation_history

Contains one row for every action for Feedback Form sent (currently Email or SMS) through Marketing Automations.

Updated: Hourly

• id - foreign key representing history_id, unique identifier for a given message(associated with 1 or more action_history.id, see AUTOMATION_ACTION_HISTORY)
• automation_id - foreign key, references automations.id
• feedback_form_id - foreign key, references feedback_forms.id
• guest_id - foreign key, references guests.id
• merchant_id - foreign key, references merchants.id
  • Will be the merchant visited for post-visit automations
• channel - 'EMAIL' or 'SMS'
• created_at - time the message was created/sent (in UTC)
• delivered_at - time the message was delivered to guest (in UTC)

Feedback Forms

wisely_share_v2.feedback_forms

If the restaurant group has this feature, the feedback forms and their structure are included in this view. Otherwise, this view will be empty.

Updated: Every 2 Hours

• id - primary key, unique identifier for a feedback form
• name - name of the feedback form
• program_id - foreign key, references programs.id
• merchant_id - foreign key, references merchants.id
• title - descriptive title displayed on the feedback form
• description - description of the feedback form
• questions_json - json defining the questions in the feedback form in its current state
• success_text - text shown to the responder when the form is submitted
• enabled_at - if null, the form is disabled (in UTC)
• deleted_at - if not null, the form is disabled (in UTC)
• created_at - time the form was created (in UTC)
• updated_at - time the form was most recently updated (in UTC)

Feedback Form Responses

wisely_share_v2.feedback_form_responses

Contains each individual response of a Feedback Form, linked by the feedback form id.

Updated: Every 4 Hours

• id - primary key, unique identifier for a given response
• feedback_form_id - foreign key, references feedback_forms.id
• guest_id - foreign key, references guests.id
• merchant_id - foreign key, references merchants.id
• response_json - json with the responses to each question defined in feedback_forms.questions_json
• overall_score - rating pulled from the feedback response
• started_at - time the form was received and started (in UTC)
• completed_at - time the form was completed and submitted (in UTC), if null, this response was not submitted
• created_at - time the form was generated and sent to the guest (in UTC)
• updated_at - time the response record was most recently updated (in UTC)

Guests

wisely_share_v2.guests

This view contains a row for each guest that has been seen for each program in a restaurant group. Guests are always scoped per-program. Guests could be saved from multiple different sources and this data is usually found in other views.

Updated: Every 4 Hours

• id - primary key, unique identifier for a guest in a given program
• program_id - foreign key, references programs.id, indicates which program this guest belongs to
• guest_selected_merchant_id - foreign key, references merchants.id, indicates which merchant the guest has set as their preferred location
• fname - guest's first name
• lname - guest's last name
• phone - guest's phone number
• email - guest's email address
• birth_year - year of this guest's birthday (if provided)
• birth_month - month of this guest's birthday (if provided)
• birth_day - day of this guest's birthday (if provided)
• created_at - time this record was created (in UTC)
• updated_at - time this record was most recently updated (in UTC)

Guest Custom Fields

wisely_share_v2.guest_custom_fields

This table represents custom fields applied to guests within a given program. Each record represents a specific custom field and its value for a given guest.

Updated: Every Hour

• id - primary key, unique identifier for this custom field for this guest
• guest_id - foreign key, references guests.id
• program_custom_field_id - foreign key, references program_custom_fields.id
• value - the value for this custom field for this guest
• created_at - the time this record was created (in UTC)
• updated_at - the time this record was most recently updated (in UTC)
• deleted_at - the time this record was deleted (in UTC) - if not null, this custom field record no longer applies to this guest

Guest Interactions

wisely_share_v2.guest_interactions

Contains one row for each interaction (Online Order, In-House Party, or Other) that each guest takes at each location in your group.

Updated: Every 4 Hours

• id - primary key, unique identifier for a given interaction between a guest and an individual restaurant
• merchant_id - foreign key, references merchants.id
• guest_id - foreign key, references guests.id
• interaction_source - type of interaction:
  • ‘parties’ if the interaction was creating a party (waitlist, reservation)
  • ‘orders’ if the interaction was creating an order (online, Wisely Order Management)
  • ‘wifi’ if the interaction was logging into the social WiFi
  • ‘other’ for all other types of tracked interaction
• interaction_source_id - foreign key referencing:
  • parties.id (when interaction_source = ‘parties’)
  • orders.id (when interaction_source = ‘orders’)
• created_at - time this record was created (in merchant’s local timezone)
• updated_at - time this record was most recently updated (in UTC)

Guest Opt-In Status

wisely_share_v2.guest_optin_status

Contains Guest, Target, and Source information on marketing opt-ins. The table represents a full history of each guest's opt-ins. To get each guest's current opt-in status for a given Target Marketing Channel, see the is_current column.

If a guest does not have a record in this table for a given Target Marketing Channel, their opt-in status has not yet been set for that channel.

Updated: Every 4 Hours

• id - primary key, unique identifier for an opt-in record
• program_id - foreign key, references programs.id
• guest_id - foreign key, references guests.id
• target - Target Marketing Channel
• is_current - true for the current opt-in status for a given Target Marketing Channel. Each Target has one and only one current record at a given time.
• is_optin - true for a guest that has opted-in, false for a guest that has explicitly opted-out
• is_double_verified - for SMS Target Channel, true when guest has verified Opt-In status. Default true for Email.
• source - Source Channel where opt-in status was recorded
• created_at - time the status record was created (in UTC)
• updated_at - time the status record was most recently updated (in UTC)

Merchants

wisely_share_v2.merchants

Contains one row for each individual restaurant in your group. It contains the name of the restaurant, its identifier, and metadata associated with it. Most other data views link to the Merchants identifier in order to know what restaurant it belongs to.

Updated: Every 4 Hours

• id - primary key, unique identifier for a given restaurant
• program_id - foreign key, references programs.id
• name - restaurant's visible name
• slug - used to generate a Wisely embedded reservation or waitlist form
• address - restaurant's physical address
• phone - restaurant’s phone number
• city - restaurant's city
• state - restaurant's state
• zip - restaurant's zip code
• timezone - restaurant's timezone (can be used to convert UTC timestamps to restaurant’s local timezone, or vice versa)
• store_number - optional field which can be entered into the Wisely dashboard for each restaurant
• olo_vendor_id - identifier for this restaurant in Olo, if applicable
• is_closed - marked TRUE if merchant is registered as closed
• created_at - time this record was created (in UTC)
• updated_at - time this record was most recently updated (in UTC)

Merchant Rooms

wisely_share_v2.merchant_rooms

Each restaurant is made up of several “rooms” that define the floorplan that is displayed to the Host in our iPad app. These rows serve for linking data between a Merchant and Merchant Room Tables (discussed below).

Contains one row for each room in the Host floorplan for each restaurant in your group. "Invisible" rooms (is_visible = FALSE) are groupings of tables that are not a physical room on the floorplan (such as a specific section preference on the floorplan).

Updated: Every 4 Hours

• id - primary key, unique identifier for a given room
• merchant_id - foreign key, references merchants.id
• name - name of the room (or section preference)
• is_visible - TRUE indicates a room on the floorplan, while FALSE indicates an invisible grouping of tables (most typically a section preference option for the tables within)
• created_at - time this record was created (in UTC)
• updated_at - time this record was most recently updated (in UTC)

Merchant Room Tables

wisely_share_v2.merchant_room_tables

Contains one row for each table on the Host floorplan for each restaurant in your group, including which room it belongs to. Every individual table that exists in a restaurant’s floor plan. A Merchant Room contains multiple tables, so each row in this view links back to a Merchant Room identifier. 

Updated: Every 4 Hours

• id - primary key, unique identifier for a given floorplan table
• merchant_room_id - foreign key, references merchant_rooms.id
• name - the current name assigned to this table, as entered in the Floorplan Builder
• min_capacity - smallest party size for this table, as entered in the Floorplan Builder
• max_capacity - largest party size for this table, as entered in the Floorplan Builder
• created_at - time this record was created (in UTC)
• updated_at - time this record was most recently updated (in UTC)
• deleted_at - time this record was deleted (in UTC), if not null, then the floorplan table is considered "deactivated"

Merchant Room Table Status

wisely_share_v2.merchant_room_table_status

Each row contains information about each seated table. This links directly back to a Merchant Room Table identifier. The data in this view contains a row every time a guest or party sat at a table, or when a table is marked "on bus" (in which case there is no party associated). It primarily consists of metadata relating to the times between meal stages for the sat table. A Merchant Party row will also link back to at least one record in this view (multiple if seated at multiple tables).

Updated: Every 4 Hours

• id - primary key, unique identifier for a given table status record
• merchant_room_table_id - foreign key, references merchant_room_tables.id
• party_id - foreign key, references parties.id
• assigned_at - time the party was assigned to the table (in merchant’s local timezone)
• restored_at - time the party was restored back to the waitlist, if applicable (in merchant’s local timezone)
• seated_at - time the party was seated at the table (in merchant’s local timezone)
• appetizers_at - time the first meal stage was reached (in merchant’s local timezone)
• entrees_at - time the second meal stage was reached (in merchant’s local timezone)
• dessert_at - time the final meal stage was reached (in merchant’s local timezone)
• check_dropped_at - time the table was marked “check dropped” (in merchant’s local timezone)
• paid_at - time the table was marked “paid” (in merchant’s local timezone)
• done_at - time the table was marked finished (in merchant’s local timezone)
• dirty_at - time the table was marked "on bus" (in merchant’s local timezone)
  • note: when dirty_at is set, the party is no longer associated to the table, so a new row is created in table_status whenever a table is marked "on bus"
• created_at - time this record was created (in merchant’s local timezone)
• updated_at - time this record was most recently updated (in merchant’s local timezone)

Merchant Sections

wisely_share_v2.merchant_sections

Contains one row for each section of tables in the Host floorplan. These sections can be designated as a section preference when creating a party. Sections consist of one or more tables, and a given table can belong to multiple sections. This view does not represent server sections (which are 1-to-1 table assignments to a specific server).

Updated: Every 4 Hours

• id - primary key, unique identifier for a given section
• merchant_id - foreign key, references merchants.id
• merchant_room_id - foreign key, references merchant_rooms.id
  • This represents an “invisible” room, treated by the Host App as a section of tables. This invisible room is associated with a group of tables, so by joining to this view, you can identify tables associated with a given section
• name - name of the section
• section_type - represents the type of section, visible as an icon next to the section name in the Wisely Host App
• created_at - time this record was created (in UTC)
• updated_at - time this record was most recently updated (in UTC)
• deleted_at - time this record was deleted (in UTC), if null, this section is no longer active

NLP Primary Categories

wisely_share_v2.nlp_primary_categories

Contains details on Natural Language Processing (NLP) categories used for sentiment review analysis. This view is a reference table for the nlp_primary_category_id column in the sentiment_review_analysis table.

Updated: Every 2 Hours

• id - primary key, unique identifier for a given NLP category
• category_name - name of the NLP category

Orders

wisely_share_v2.orders

Contains one row for each order created online or through Olo's order management.

Updated: Every 4 Hours

• id - primary key, unique identifier for a given order
• merchant_id - foreign key, references merchants.id
• guest_id - foreign key, references guests.id
• olo_order_id - external ID for any orders created via an Olo integration
• external_order_id - external ID for any orders created via a non-Olo integration
• name - name entered for the order, typically the name of the guest who placed the order
• order_source - indicates the integrated order partner that created this order (eg. ‘olo’, ‘toast’, ‘omnivore’, ‘host’, ‘shopify’, etc.)
• delivery_address - delivery address for the order
• delivery_method - delivery method for the order, determined by the integrated order provider
• platform_used - platform used for the order, determined by the integrated order provider
• time_placed - time the order was placed (in merchant’s local timezone)
• time_ready - time the order was marked ready (in merchant’s local timezone), if this order was created via the Olo webhook, this is identical to the Olo Promise Time
• notified_at - time the "Notify Guest" button was tapped in the iPad and the guest was sent a notification text stating that the order is ready (in merchant's local timezone)
• cancelled_at - time the order was cancelled (in merchant’s local timezone)
• items - json blob including details on items included in the order
• totals - json blob including details on total amounts for the order
• payments - json blob including details on all payments for the order
• created_at - time this record was created (in UTC)
• updated_at - time this record was most recently updated (in UTC)
• channel_name - name of the Olo channel associated with restaurant group program id

Order Custom Fields

wisely_share_v2.order_custom_fields

Contains a row for each custom field associated with an order record.

Updated: Every 4 Hours

• id - primary key, unique identifier for a given order custom field record
• order_id - foreign key, references orders.id
• key - key (name) for the custom field
• value - value for the custom field
• created_at - time this record was created (in UTC)
• updated_at - time this record was most recently updated (in UTC)

Olo Pay Transactions

wisely_share_v2.olopay_transactions

Contains a row for each Olo Pay Transaction that is sent through our Payments Data Enrichment process to tie transactions to guest records. This view will only contain data if you have an active Olo Pay integration with Engage.

Updated: Updated Daily

• stripe_charge_id - primary key, unique identifier for the transaction
• merchant_id - foreign key, references merchants.id
• card_id - hashed identifier for this card, based on available stripe_fingerprint and card_token identifiers found for this card
• stripe_fingerprint - hashed PAN from Stripe, uniquely represents this card
• card_token - hashed token from Tokenex or directly from the processor, references processor_card_tokens.card_token
• cc_last_four - last four digits of the credit card
• total_amount - total amount of the transaction in dollars
• currency_code - currency code for the transaction (expected 'USD' only)
• settled_at - time in the merchant's local time that this transaction was settled
• created_at - time in UTC that this record was created
• updated_at - time in UTC that this record was updated

Parties

wisely_share_v2.parties

Each row contains a “party” which includes either a Waitlist record or Reservation record. This can be mapped back to a User record and a Merchant record. If a row has a reserved_ts value set, that indicates it is a Reservation visit. Otherwise, it’s a Waitlist or Quick-Seat visit.

Updated: Every 4 Hours

• id - primary key, unique identifier for a given party
• merchant_id - foreign key, references merchants.id
• reservation_type_id - foreign key, references reservation_types.id
• guest_id - foreign key, references guests.id
• name - name of the party
• phone - phone number used to create the party
• email - email used to create the party
• party_size - number of guests (covers) in the party
• wait_min - lower bound of the estimated wait time range in minutes, as quoted to the party (either directly from algorithm or from manual human override)
• wait_max - higher bound of the estimated wait time range in minutes, as quoted to the party (either directly from algorithm or from manual human override)
• origin - channel through which the party was created
  • guest = Custom Guest App
  • web = Wisely Web Embedded Form 
  • api = Custom API Creation
  • dashboard = Wisely Dashboard
  • pos = POS Integration (check opened at table and party created in app)
  • staff = Wisely Host App
  • google = Google Waitlist or Google Reservations 
• quote_token - foreign key, references quote_estimation_history.quote_token
• section_preferences - array of foreign keys, referencing merchant_sections.id, represents this party’s preferred sections of the restaurant
• notes - custom visit notes for this party
• created_at - time the party was created (in merchant’s local timezone)
• notified_at - time the party was first notified via “Notify Party” button in the Host App
• partial_checkin_at - time the party was marked partially checked in by the host (in merchant’s local timezone)
• checkin_at - time the party was marked checked in by the host (in merchant’s local timezone)
• reserved_at - time the party booked a reservation for (in merchant’s local timezone)
• seated_at - time the party was seated (in merchant’s local timezone)
• restored_at - time the party was brought back to waitlist, if applicable (in merchant’s local timezone)
• deleted_at - time the party was deleted (in merchant’s local timezone), usually due to:
  • “Party Finished” button was tapped in the Host App
  • Party cancelled or was marked “no-show”
  • A new party was seated at the table this party was previously seated at
• updated_at - time the party record was most recently updated (in UTC)
• is_callahead - TRUE if party joined the waitlist remotely or was marked as a callahead in the Host App
• is_quick_seat - TRUE if the party was quick-seated in the Host App
• party_removed_reason - specific reason the party was deleted (if null, party was removed through normal course of party creation and deletion)
  • ‘seat at bar’ - party marked “Seated at Bar” in the Host App
  • ‘leave waitlist with reply action’ - party deleted themselves by replying to waitlist notification with action keyword
  • ‘leave waitlist thru webview’ - party deleted themselves by leaving through the Guest Webview
  • ‘no show’ - party marked “No Show” in the Host App
  • ‘cancelled’ - party cancelled remotely or was marked “Cancelled” in the Host App
  • ‘other’ - used for edge-case manual deletions (usually from data cleansing post hoc)
• tags - party tags
  • Gluten Free = 1
  • Dairy Free = 2
  • No Peanuts = 3
  • No Shellfish = 4
  • Anniversary = 5
  • Friend of Owner = 6
  • No Red Meat = 8
  • Vegan = 9
  • Vegetarian = 10
  • Alert The Chef = 11
  • Blogger = 12
  • Media = 13
  • Employee = 14
  • Friend of Employee = 15
  • Investor = 16
  • Regular = 17
  • VIP = 18
  • Birthday = 19
  • Business Meal = 20
  • Date Night = 21
  • Graduation = 22
  • Pre-Theatre = 23
  • Special Occasion = 24
  • No Tree Nuts = 25
  • Rewards Member = 594
  • Member = 709
  • First Time Guest = 738
  • Private Party = 739

Party Custom Fields

wisely_share_v2.party_custom_fields

Contains one row for each custom field registered for a given party. This most commonly corresponds to the Special Occasion/Special Request field of the reservation/waitlist party creation flow.

Updated: Every 4 Hours

• id - primary key, unique identifier for this party custom field record
• party_id - foreign key, references parties.id
• key - internal key (eg. special_occasion, special_request)
• value - name of the custom field selected for this party
• created_at - time the record was created (in UTC)
• updated_at - time the record was most recently updated (in UTC)
• deleted_at - time the record was deleted (in UTC), if not null, this custom field no longer applies to this party

Processor Card Tokens

wisely_share_v2.processor_card_tokens

This view contains data for restaurant groups that have a Credit Card Processor integration enabled with Wisely. Each row contains a card token from the integrated processor and a mapping to an Olo  guest, based on matching records from your integrated order management system (eg. Olo, Toast). There is a many-to-many relationship between card tokens and guests, as a single guest can have multiple cards, and cards can be shared between guests.

Updated: Updated Daily

• card_token - tokenized credit card from the integrated processor
• program_id - foreign key, references programs.id
• guest_id - foreign key, references guests.id
  • If null, this card token has not yet been mapped to a Wisely guest

Processor Enriched Guest Info

wisely_share_v2.processor_enriched_guest_info

Each record in this table represents a guest (either a recognized guest with contact information or an anonymous card token) that has credit card activity from an integrated processor. The record contains "enriched" information, including calculated and predicted fields which can be used to better understand your guestbook at large, including CLV and RFM.

Updated: Updated Daily

• guest_id - foreign key, references guests.id, if null this record pertains to an anonymous card token
• anonymous_card_token - foreign key, references processor_card_tokens.card_token, if null this record pertains to a guest that has contact information and can be found in the guests view
• first_visit_at - time of the first card swipe recorded for this guest
• latest_visit_at - time of the latest card swipe recorded for this guest
• days_since_last_visit - number of days (from the current date) since this guest's latest visit
• avg_days_between_visits - average number of days between visits (ie. the guest's frequency)
• total_visits - total number of card swipes (visits) for this guest
• total_spend_all_time - total monetary spend from card swipes from this guest
• total_spend_last_year - total monetary spend from card swipes from this guest in the last year (used to calculate historical CLV)
• recency_quantile - relative recency of this guest compared to all other guests with processor
• activity (including anonymous card tokens), 1 = top quantile, 4 = bottom quantile
• frequency_quantile - relative frequency of this guest compared to all other guests with
• processor activity (including anonymous card tokens), 1 = top quantile, 4 = bottom quantile
• monetary_quantile - relative monetary spend of this guest compared to all other guests with processor activity (including anonymous card tokens), 1 = top quantile, 4 = bottom quantile
• rfm_quantile_segment - string that represents combined quantiles of recency, frequency, and monetary quantiles (in that order). For example, '124' would be top quantile of recency, second highest quantile of frequency, and bottom quantile of monetary spend
• historical_clv - historical annualized spend from card swipes for this guest
• predictive_clv - predicted annual spend for the next year for this guest, based on a predictive model using recency, frequency, and monetary spend as inputs
• clv_percentile_rank - relative rank of this guest's historical CLV, compared to all other guests with processor activity (including anonymous card tokens), 1 = 95th percentile (top 5%), 2 = 90th percentile, ... 20 = 0th percentile (bottom 5%)
• six_month_churn_risk - predicted likelihood that this guest will churn six months from now without intervention, based on a predictive model specific to your program
• primary_merchant_id - foreign key, references merchants.id, represents the merchant that this guest has visited most frequently, most recently, and has spent the most money at
• clv_percentile_rank_by_merchant - relative rank of this guest's historical CLV, compared to all other guests with processor activity with the same primary_merchant_id (including anonymous card tokens), 1 = 95th percentile (top 5%), 2 = 90th percentile, ... 20 = 0th percentile (bottom 5%)

Processor Transactions

wisely_share_v2.processor_transactions

This view contains data for restaurant groups that have a Credit Card Processor integration enabled with Olo. Each row contains an individual transaction with a reference to a payment source (which includes the tokenized credit card information)

Olo Pay transactions are excluded and can be see on olopay_transactions share view. If program has only OloPay enabled, this share view will return current 0 data(except historical if any).

Updated: Updated Daily

• id - primary key, unique identifier for a given transaction
• merchant_id - foreign key, references merchants.id
• card_token - credit card used for this transaction, tokenized by the integrated processor, references processor_card_tokens.card_token
• cc_last_four - last four digits of the credit card used for this transaction
• total_amount - total amount for this transaction
• currency_code - currency code for this transaction
• settled_at - time this transaction was settled (in merchant’s local timezone)
• created_at - time this record was created (in UTC)
• updated_at - time this record was updated (in UTC)

Processor Transaction Orders

wisely_share_v2.processor_transaction_orders

This table represents credit card processor transactions and the order/ticket record they have been matched to. The source_table field indicates whether the transaction was matched via the tickets table or the orders table.

Updated: Updated Daily

• transaction_id - foreign key, references
• order_source_id - foreign key, references either tickets.id or orders.id (depends on the value in source_table)
• source_table - indicates whether the transaction was matched to a record in orders or a record in tickets
• created_at - time this record was created (in UTC)
• updated_at - time this record was updated (in UTC)

Programs

wisely_share_v2.programs

A restaurant group will be organized into one or more programs, typically based on brand differences or specific internal user management requirements. Most single-brand restaurant groups have just one program.

Updated: Every 2 Hours

• id - primary key, unique identifier for a given program
• name - name of the program as entered in the Wisely Dashboard
• created_at - time this program record was created (in UTC)
• updated_at - time this program record was most recently updated (in UTC)

Program Custom Fields

wisely_share_v2.program_custom_fields

This table represents custom fields created within a given program. Each record represents a specific custom field, defined within a program.

Updated: Every 2 Hours

• id - primary key, unique identifier for this custom field for this program
• program_id - foreign key, references programs.id
• display_name - display name as it appears in the Wisely application suite
• key - back-end key for this custom field
• type - type of value expected for this custom field (eg. string, boolean, number, date, etc.)
• created_at - the time this record was created (in UTC)
• updated_at - the time this record was most recently updated (in UTC)
• deleted_at - the time this record was deleted (in UTC) - if not null, this program custom field is no longer considered active

Push Campaigns

wisely_share_v2.push_campaigns

Contains one row for every Push Campaign.

Updated: Updated Daily

• id - primary key, unique identifier for a push notification campaign
• program_id - foreign key, references programs.id
• name - the name for a push notification campaign
• notification_title - the title is the banner the push notification carries to the endpoint
• notification_body - the body of the push notification, entails the message that was delivered to the endpoint
• created_at - the time this record was created (in UTC)
• updated_at - the time this record was most recently updated (in UTC)
• updated_by_user_id - the user_id of the last user to update the push notification campaign
• deleted_at - time this record was deleted (in UTC)
• image_url - the URL image for the push notification
• expiration_date - the time that the push notification will hide or end

Push Campaign Guest History

wisely_share_v2.push_campaign_guest_history

Contains one row for every record of an individual guest's engagement with an individual instance of  Push Notification Campaign.

Updated: Every 8 Hours

• id - primary key, ID for the guest's activity with the campaign email
• campaign_id - foreign key, references push_campaigns.id
• guest_id - foreign key, references guests.id
• created_at - time that the record was created in UTC
• updated_at - time that the record was most recently updated in UTC
• delivered_at - time that the push notification was delivered to this guest in UTC
• failed_at_reason - A direct response from firebase on why the push notification failed
• failed_at - time when the push notification failed in UTC

Quote Estimation History

wisely_share_v2.quote_estimation_history

This view contains the history of the quote estimates that Olo Engage has generated for a restaurant. This view has a field called quote_token that can be linked to a Merchant Party record via the quote_token field on that row. The Quote Estimation History table is useful if you want to analyze and report on the queue length and quote times historically for certain restaurants.

Certain quote tokens will have multiple rows in this view, since quotes according to multiple different party sizes or table sections can be generated at once for a given party. The token which was ultimately provided to the guest is the row where is_provided = TRUE. This is helpful if you’re looking to compare the algorithm-estimated quote and the actual quoted wait time for a given party (parties.wait_min to parties.wait_max).

Updated: Every 4 Hours

• id - unique identifier for this estimation
• merchant_id - foreign key, references merchants.id
• section_id - foreign key, references merchant_sections.id, represents the section of the restaurant this estimated wait time applies to
• quote_token - token generated for a quote or set of quotes, for a given party, referenced by parties.quote_token
• party_size - party size for this quote (for some channels, quote estimates are generated for multiple party sizes at once for the same token)
• queue_length - length of the waitlist at the time this quote was generated
• nb_parties_seated - number of parties seated at the time this quote was generated
• raw_quote - raw estimated quote from the algorithm (bucketed based on the restaurant's quote bucket configuration
• pre_buffer_wait_est_min - min of the range of the estimated wait time before buffer is applied (based on the restaurant's quote bucket configuration)
• pre_buffer_wait_est_max - max of the range of the estimated wait time before buffer is applied (based on the restaurant's quote bucket configuration)
• quote_buffer - number of minutes added to the raw_quote before bucketing (based on “padding” setting in the Host app)
• wait_est_min - min of the range of the estimated wait time (based on the restaurant's quote bucket configuration)
• wait_est_max - max of the range of the estimated wait time (based on the restaurant's quote bucket configuration)
• is_provided - if TRUE, this is the record ultimately provided to the party in the form of a suggested quote time in the app or algorithm-provided quote time for remote joiners
  • Use this condition when joining to parties, to ensure only one qeh record is returned per party
• created_at - time this record was created (in merchant’s local timezone)
• updated_at - time this record was most recently updated (in merchant’s local timezone)

Reservation Types

wisely_share_v2.reservation_types

Contains one row for each reservation type set up within each restaurant’s reservation availability configurations.

Updated: Every 2 Hours

• id - primary key, unique identifier for a given reservation type
• merchant_id - references merchants.id
• name - name of the reservation type
• description - description of the reservation type
• room_ids - array of ids referencing merchant_rooms.id, indicates which room(s) are available for auto-assignment if is_table_based = TRUE
• price - if a paid reservation type, the price of booking the reservation
• currency - currency code for the price of a paid reservation
• is_flat_fee - if TRUE, the reservation type has the same fee regardless of party size; if FALSE, price is multiplied by the size of the party
• is_table_based - if TRUE, reservations booked under this type will be automatically assigned to a table, and table availability will be taken into account
• is_default - if TRUE, this is this merchant’s default reservation type
• created_at - time this record was created (in UTC)
• updated_at - time this record was most recently updated (in UTC)
• deleted_at - time this record was deleted (in UTC), if null, this reservation type is no longer active

Sentiment Reviews

wisely_share_v2.sentiment_reviews

The view contains data for restaurant groups that subscribe to the Engage Guest Sentiment product. Each row contains a guest review for a restaurant. The rows contain identifiers that link back to the Merchants view.

Updated: Every 4 Hours

• id - primary key, unique identifier for a given review
• merchant_id - foreign key, references merchants.id
• program_id - foreign key, references programs.id
• feedback_form_response_id - foreign key, references feedback_form_responses.id
• content - content of the review
• source - content provider for the review (eg. Yelp, Google, TripAdvisor, etc.)
• rating - overall rating for the review
• calculated_sentiment_score - score on a scale from 0 to 100, combined sentiment of all annotated phrases (takes into account any manual sentiment adjustments by staff)
• guest_id - foreign key, references guests.id if guest record already exists for the reviewer
• reviewer_name - name of the reviewer
• reviewer_pic_url - image of the reviewer if provided by content provider
• review_url - original url for the review on the content provider's website
• response_url - original url for the most recent response on the content provider's website
• engagement_status - indicates whether this review has been directly engaged or marked as engaged in the Wisely Dashboard
• processed_status - indicates whether this review has been processed and annotated by Wisely Guest Sentiment yet
• review_posted_at - time the review was originally posted on the content provider’s site
• review_added_at - time the review was ingested by Wisely (in UTC)
• processed_at - time the review was analyzed and annotated by Wisely (in UTC)
• created_at - time this record was created (in UTC)
• updated_at - time this record was most recently updated (in UTC)
• deleted_at - time this record was deleted (in UTC)

Sentiment Review Analysis

wisely_share_v2.sentiment_review_analysis

The view contains data for restaurant groups that subscribe to the Engage Guest Sentiment product. Each row in this view contains the analysis done for phrases in a guest review. For example, if a guest review contained the phrase “Great service”, this row would contain information about how Engage determined the sentiment of that phrase. These each contain an identifier linked back to a Review record.

Updated: Every 8 Hours

• id - primary key, unique identifier for a given analysis
• sentiment_review_id - foreign key, references sentiment_reviews.id
• sentiment - algorithm-generated sentiment of this review, expressed as a decimal
  • < 0 = negative
  • 0 = neutral
  • > 0 = positive
• adjusted_sentiment - adjusted sentiment of this review, expressed as a decimal - this value exists only if sentiment was manually overridden in the Dashboard
  • < 0 = negative
  • 0 = neutral
  • > 0 = positive
• phrase - analyzed phrased parsed into words
• nlp_primary_category_id - foreign key, references nlp_primary_categories.id
• created_at - time this record was created (in UTC)
• updated_at - time this record was most recently updated (in UTC)

Sentiment Review Responses

wisely_share_v2.sentiment_review_responses

The view contains data for restaurant groups that subscribe to the Engage Guest Sentiment product. Our product allows restaurant managers to reply to Reviews in our web dashboard, and these replies are stored in this view. These link directly back to a Review identifier.

Updated: Every 2 Hours

• id - primary key, unique identifier for a given review response
• sentiment_review_id - foreign key, references sentiment_reviews.id
• content - content of the response
• responder_name - name of the responder
• responder_title - title of the responder, if applicable (eg. Owner, Manager, etc)
• posted_at - time the response was originally posted on the content provider's website (in UTC)
• created_at - time this record was created (in UTC)
• updated_at - time this record was most recently updated (in UTC)

SMS Campaigns

wisely_share_v2.sms_campaigns

Contains one row for every SMS Campaign.

Updated: Updated Daily

• id - primary key, unique identifier for the sms campaign
• program_id - foreign key, references programs.id
• name - the name of the sms campaign
• author_user_id - the user_id of the user that authored the sms campaign
• created_at - time this record was created (in UTC)
• updated_at - time this record was most recently updated (in UTC)
• deleted_at - time this record was deleted (in UTC)

SMS Campaign Guest History

wisely_share_v2.sms_campaign_guest_history

Contains one row for every record of an individual guest's engagement with an individual instance of an SMS Campaign.

Updated: Every 4 Hours

• id - primary key, ID for the guest's activity with the campaign email
• campaign_id - foreign key, references sms_campaigns.id
• segment_id - foreign key, references a Wisely-internal Segment ID
• template_id - foreign key references a Wisely-internal sms_message_templates ID which is assigned to the desired template attributes
• guest_id - foreign key, references guests.id
• twilio_sid - Twilio ID for the sms sent, prefixed with a message classification identifying SMS/MMS in the first two characters of the string
• created_at - time that the record was created in UTC
• updated_at - time that the record was most recently updated in UTC
• sent_at - time when the SMS/MMS was sent to the guest in UTC

Sparkfly Guest Data

wisely_share_v2.interactions_sparkfly

Contains Sparkfly interactions data.

Updated: Updated Daily

• program_id - Identifier for the program associated with the event
• event - The type of event that occurred, common events are
  • Reward Issued
  • Reward Redeemed
  • Reward Expired
  • Reward Voided
  • Loyalty Guest Checked In
  • Loyalty Program Joined
  • Points Issued
• event_id - Unique identifier for the event
• message_id - Primary Key for the table, unique identifier for the message associated with the event
• user_uuid - Unique identifier for the user associated with the event
• group_id - Identifier for the group associated with the event
• received_dttm - The date and time the event was received
• event_dttm - The date and time the event occurred
• properties - Additional properties or metadata related to the event
• guest - Details about the guest involved in the event

Thanx Guest Data

wisely_share_v2.interactions_thanx

Contains Thanx interactions data.

Updated: Updated Daily

• program_id - Identifier for the program associated with the event
• event - The type of event that occurred, common events are
  • Reward Expired
  • Reward Issued
  • Points Issued
  • Reward Redeemed
  • Thanx Digital Order
  • Thanx In-Store Order
  • Points Exchanged
  • Points Expired
  • Loyalty Program Joined
  • Reward Activated
  • Reward Voided
• event_id - Unique identifier for the event
• message_id - Primary Key for the table, unique identifier for the message associated with the event
• user_uuid - Unique identifier for the user associated with the event
• group_id - Identifier for the group associated with the event
• received_dttm - The date and time the event was received
• event_dttm - The date and time the event occurred
• properties - Additional properties or metadata related to the event
• guest - Details about the guest involved in the event

Spendgo Guest Data

wisely_share_v2.interactions_spendgo

Contains Spendgo interactions data.

Updated: Updated Daily

• destination_config - Configuration details for the destination
• destination_id - Unique identifier for the destination
• event - The type of event that occurred
• event_dttm - The date and time the event occurred
• event_id - Unique identifier for the event
• event_type - The category or type of the event
• group_id - Identifier for the group associated with the event
• guest - Details about the guest involved in the event
• identifiers - Additional identifiers related to the event
• last_update_dttm - The date and time the record was last updated
• message_id - Unique identifier for the message associated with the event
• message_key - Key for the message associated with the event
• program_id - Identifier for the program associated with the event
• properties - Additional properties or metadata related to the event
• received_dttm - The date and time the event was received
• source - The source system or application of the event
• user_uuid - Unique identifier for the user associated with the event

Tickets

wisely_share_v2.tickets

This view contains data for restaurant groups that have a Point of Sale or order management system integrated with Olo. Each row contains “check” data that is ingested from the POS/order management integration. If the ticket can be associated with a Merchant Party record, an identifier will be present for that row. Many ticket rows may not have this identifier because the guest visit counts as a “walk-in” and no guest data was captured by Olo.

Updated: Every 4 Hours

• id - primary key, unique identifier for a given ticket
• merchant_id - foreign key, references merchants.id
• order_id - foreign key, references orders.id
• party_id - foreign key, references parties.id
• external_ticket_id - identifier for this ticket from the integrated POS/order management system (Olo, Omnivore, etc)
• order_type - type of order, determined by integrated POS/order management system 
• ticket_name - name of the ticket
• is_open - TRUE if the ticket is still open
• is_voided - TRUE if the ticket was voided
• employee_id - unique identifier from the POS/order management system for the employee who opened the ticket
• employee_fname - first name of the employee who opened the ticket
• employee_lname - last name of the employee who opened the ticket
• guest_count - number of guests on the ticket as entered into the POS/order management system
• table_name - name of the table as entered into the POS/order management system
• item_sales_total - sum of the individual item totals for this ticket
• other_charges - differently categorized charges, determined by the integrated POS/order management system
• ticket_promos_total - total of ticket-level promotions
• sub_total - subtotal amount for this ticket (item_sales_total + other_charges + ticket_promos_total)
• tax - total tax for this ticket (sub_total * local tax rate)
• total - total amount for this ticket (sub_total + tax + service charges)
• paid - total amount paid on this ticket
• due - amount currently due on this ticket (0 for all fully paid tickets)
• ticket_source - POS/order management source for this ticket (eg. Omnivore, Olo, Toast, etc.)
• opened_at - time the ticket was opened (in merchant’s local timezone)
• closed_at - time the ticket was closed (in merchant’s local timezone)
• created_at - time this record was created (in merchant’s local timezone)
• updated_at - time this record was most recently updated (in merchant’s local timezone)

Sample Queries

The queries below show some simple ways to get results from data in this warehouse with which you can perform further analyses. When adapting these queries or creating your own, please be aware that further data cleansing may be necessary depending on your data needs, and the quality of the data may depend on the specific nature of your business's operations.

Queries on Guest Data

Get Full List of Guests

SELECT
  *
FROM wisely_share_v2.guests
;

Get Full List of Guests Who are Currently Opted In to Email Marketing

SELECT
  A.*
FROM wisely_share_v2.guests A
JOIN wisely_share_v2.guest_optin_status B 
  ON A.id = B.guest_id 
  AND A.program_id = B.program_id
WHERE B.is_optin = TRUE
  AND B.is_current = TRUE
  AND B.target = 'email'
;

Get Full List of Guests Who Have a Given Custom Field Value

SELECT
  A.*,
  C.display_name,
  B.value
FROM wisely_share_v2.guests A
JOIN wisely_share_v2.guest_custom_fields B
  ON A.id = B.guest_id 
JOIN wisely_share_v2.program_custom_fields C
  ON B.program_custom_field_id = C.id
  AND A.program_id = C.program_id
WHERE B.deleted_at IS NULL
  AND C.deleted_at IS NULL
  AND C.key = 'nut_allergy' -- replace this with any key and/or display_name you're looking for
  AND B.value = 'true' -- replace this with any expected value you're looking for
;

Get Count of Guests

SELECT
  COUNT(*) AS ttl_count
FROM wisely_share_v2.guests
;

Get Count of Guests by Week (of Creation)

SELECT
  DATE_TRUNC('week', created_at)::DATE AS week_created,
  COUNT(*) AS ttl_count
FROM wisely_share_v2.guests
GROUP BY 1
ORDER BY 1
;

Queries on Host (Party) Data

Get Full List of Parties

SELECT
  *
FROM wisely_share_v2.parties
;

Get Count of Seated Parties by Location, by Month

SELECT
  B.name AS merchant_name,
  B.store_number,
  DATE_TRUNC('month', A.created_at)::DATE AS month_created,
  COUNT(*) AS ttl_parties,
  SUM(party_size) AS ttl_covers
FROM wisely_share_v2.parties A
JOIN wisely_share_v2.merchants B ON A.merchant_id = B.id
WHERE A.seated_at IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 2, 3
;

Get Count of Parties (Seated and Created) by Quoted Wait Range Within Given Time Frame

SELECT
  A.wait_min,
  A.wait_max,
  COUNT(*) AS ttl_guests_created,
  COUNT(CASE WHEN A.seated_at IS NOT NULL THEN 1 END) AS ttl_guests_seated
FROM wisely_share_v2.parties A
JOIN wisely_share_v2.merchants B ON A.merchant_id = B.id
WHERE A.wait_min IS NOT NULL
  AND A.wait_max IS NOT NULL
  AND A.wait_max > 0
  AND A.created_at BETWEEN '2022-01-01' AND '2022-06-30' -- replace these dates with the range you're looking for
GROUP BY 1, 2
ORDER BY 1
;

Queries on Sentiment Data

Get Full List of Reviews (Excluding Deleted Reviews)

SELECT
  *
FROM wisely_share_v2.sentiment_reviews
WHERE deleted_at IS NULL
;

Get Count of Reviews and Average Rating by Source

SELECT
  source,
  COUNT(*) AS ttl_reviews,
  AVG(rating) AS avg_rating
FROM wisely_share_v2.sentiment_reviews
WHERE deleted_at IS NULL
GROUP BY 1
ORDER BY 1
;

Queries on Processor-Enriched Data

Get Full List of Guests with Contact Information in Top 20% (By CLV)

SELECT
  A.*,
  B.historical_clv
FROM wisely_share_v2.guests A
JOIN wisely_share_v2.processor_enriched_guest_info B ON A.id = B.guest_id
WHERE B.clv_percentile_rank <= 4 -- in top 20%
;

Get Average Recency of Guests by CLV Percentile Rank

SELECT
  clv_percentile_rank,
  AVG(days_since_last_visit) AS avg_days_since_last_visit
FROM wisely_share_v2.processor_enriched_guest_info
WHERE clv_percentile_rank IS NOT NULL
GROUP BY 1
ORDER BY 1
;

Queries on Automations Data

Get a list of all active automations

SELECT *
FROM wisely_share_v2.automations
WHERE is_active = TRUE
AND deleted_at IS NULL
;

Get a list of all recipients of an Email Action from a given Automation, including details on the guest's engagement with the email

SELECT
  g.id AS guest_id
  , g.fname AS fname
  , g.lname AS lname
  , g.phone AS phone
  , g.email AS email_address
  , aeh.delivered_at AS email_delivered_at
  , aeh.opened_at AS email_opened_at
  , aeh.clicked_at AS email_clicked_at
  , aeh.unsubscribed_at AS email_unsubscribed_at
  , aeh.dropped_at AS email_dropped_at
  , aeh.group_unsubscribe_at AS email_group_unsubscribed_at
FROM wisely_share_v2.automation_action_email_history aeh
JOIN wisely_share_v2.automation_action_history ah ON aeh.action_history_id = ah.id
JOIN wisely_share_v2.automation_actions ac ON ah.action_id = ac.id
JOIN wisely_share_v2.automations a ON ac.automation_id = a.id
JOIN wisely_share_v2.automation_history h ON ah.history_id = h.id
JOIN wisely_share_v2.guests g ON h.guest_id = g.id
WHERE a.id = 1 -- USAGE NOTE: Enter your own automation ID here
AND aeh.delivered_at IS NOT NULL
;

Get the message delivery rate for each of your currently active SMS automation actions

SELECT
  a.id                                                             AS automation_id
  , a.name                                                         AS automation_name
  , count(CASE WHEN ash.sent_at IS NOT NULL THEN ash.id END)       AS total_sent
  , count(CASE WHEN ash.delivered_at IS NOT NULL THEN ash.id END)  AS total_delivered
  , CASE
      WHEN total_sent > 0 THEN round(total_delivered/total_sent, 4)
      ELSE 0
    END                                                            AS delivery_rate
FROM wisely_share_v2.automation_action_sms_history ash
JOIN wisely_share_v2.automation_action_history ah ON ash.action_history_id = ah.id
JOIN wisely_share_v2.automation_actions ac ON ah.action_id = ac.id
JOIN wisely_share_v2.automations a ON ac.automation_id = a.id