TOOL: Lighthouse: Reporting Dashboard

[lyndon-bot]

Lighthouse: Reporting Dashboard

OBJECTIVE

The market dev team needs a way to easily interpret and understand the behavior of our Customers, in order to make decisions informed by their purchasing behavior. In addition to internally utilizing this data, the team also needs the ability to export reports to be received by individuals outside our organization ODP Reps

TERMS

Customers: Individual Purchasing accounts ODP Reps: Sales Representatives for ODP Fiscal Quarter: A quarter is a three-month period on a company's financial calendar (Q1, Q2, ect) High Value Customers: the top 30% of our customers measured by those purchasing accounts achieving $ 20,000 dollars or more in NET_SALES Reports: Counts, Charts, Graphs or other visualizations made from data resources Views: Tables containing information from data resources Actions: Functions triggered by user actions that (creates,updates or deletes) from data resources Active Purchaser: ???? Spend: all transactions summed within category

Individual Purchaser CUSTOMER == ACCOUNT_ID == ChildAcountId == OdAccountId

USE CASES:

• Converting Individual Purchaser's behavior to assist in the creation of recommendations & insights for ODP [🎇high priority]
• Show Net Pricer Table and search by category, SKU, and other fields [ 🔮future]

PLANNING QUESTIONS:

• DEFINE BriefCase Fields
• MAP BriefCase Fields to Common EA Terms
• CREATE some source tables from the raw data with basic sorting , filtering, etc

BriefCase Fields:

+DECISION_MAKER_ID +DECISION_MAKER_NAME +GRAND_PARENT +GRAND_PARENT_NAME +CUST_PARENT_ID +PARENT_NAME +CUSTOMER +CUSTOMER_NAME +SHIP_TO_ID +ADDRESS_KEY +SHIP_TO_NAME +SITE_ADDRESS_LINE_1 +SITE_ADDRESS_LINE_2 +SITE_CITY +SITE_STATE +SITE_ZIP_CODE +CUST_CREATE_DT +FIRST_ORDER_DATE +CUSTOMER_DEPARTMENT +CUSTOMER_PURCHASE_ORDER +CUSTOMER_DESK_TOP +CUSTOMER_RELEASE_FIELD +CONTRACT_INDICATOR +CONTACT_NAME +TELEPHONE_NUM +WEB_USER_ID +OD_PRODUCT_CODE +PRODUCT_DESCRIPTION +UOM +WHOLESALER_PRODUCT_CODE +PACK_QTY +QTY_SOLD +CORE_PLAN_ID +NET_SALES +CONTRACT_NET_SALES +LIST_PRICE +PROGRAM_PRICE +RETAIL_LIST_PRICE +PARENT_ORDER_ID +ORDER_SUBORDER_ID +ORDER_SOURCE_NAME +ORDER_SOURCE_CODE +PRODUCT_CATEGORY_NAME +ITEM_DEPARTMENT_ID +ITEM_DEPARTMENT_NM +ITEM_CLASS_ID +ITEM_CLASS_NM +ITEM_SUBCLASS_ID +ITEM_SUBCLASS_NM +RECYCLED_FLAG +PRIMARY_SUPPLIER_ID +PRIMARY_SUPPLIER_NAME +ORDER_SUPPLIER_MINORITY_BUSINESS_CODE +ORDER_SUPPLIER_MINORITY_BUSINESS_DESC +ORDER_SUPPLIER_MINORITY_CATEGORY_CODE +ORDER_SUPPLIER_MINORITY_CATEGORY_DESC +ORDER_CREATE_DATE +ORDER_COMPLETE_DATE +INV_LOCATION +LOCATION_NM

REQUIREMENTS

PO:

Dashboard Overview:

• An ability to query for purchasing accounts by account number and display purchasing history related to the selected record
• The purchasing records should contain breakdowns and summaries related to the selected account grouped historically by the fiscal quarter

Reports:

• Number of orders purchased by an Individual Accounts
• Avg Spend Per Order by an Individual Accounts
• Avg Const Per Item by an Individual Accounts
• Line graph of Net_Sales grouped by month, year to date
• Total Net Sales Spend over the YTD
• Number of Ship to Addresses
• Number of Purchasers
• Percent of Active Purchasers
• Top 8 Categories ordered by Net Sales Spend descending
• Percent Net Sales grouped by Net Sales

Views:

• Top 20 SKUs ordered by total spent descending including two calculated fields Avg Item Price and Total Spent on Item

Actions:

• Ability to leave comments on an Individual Account
• Ability to export Top Purchased Items VIEW as CSV
• Ability to export Dashboard as PDF

P1:

• An Ability to view the price history of the most frequently purchased SKUs as a summary of the purchase history of all accounts
• Expand the last Fiscal year time box for viewing the purchase history of an account o incorporate the YTD time range as a selectable query option. This will necessitate the ability to combine purchase history across briefcase reports

REFERENCE FILES

• DIAGRAM Great Data Lakes
• Previous Examples:
• Wireframe:
• [BriefCase Terms}(https://docs.google.com/spreadsheets/d/1LS7KavDDgLSGGrMkibZCxIVpzEhXqepxLrsI1rM_kY8/edit?usp=sharing)

  DEPENDENCIES

  23

TASKS

[lyndon-bot]

Hedi: Need to understand the trends of our top 30% of customers

Important Top 30% = > $ 20,000 dollars of NET_SALES

Long term goal understand the changes in behavior:

Consistent Purchasing

Individual Accounts Important Properties:

• Orders

• Frequency

• Spend Per Order

• Categories

• Average Order Size

• Number of Purchasers (customer name)

• Number of Ship-to-locations

• Need to identify individual accounts with tags ( how can users edit accounts and give them properties) such as industry

Reports Over Time Ranking:

1. YTD
2. Fisical Quarter
3. Monthly

Data Freshness

• Briefcase (daily)
• Net Pricer (?monthly)

[tech-ea]

Worksheets in Snopwflake Link to docs