HFDN Customer Feedback

A Python application that automates the extraction of customer feedback data from Snowflake and syncs it to Google Sheets for real-time reporting and analysis.

Overview

This project fetches customer feedback data from a Snowflake data warehouse and automatically updates a Google Sheet named "HFDN Customer Feedback". The workflow includes data filtering by hub location, order performance metrics, and customer feedback ratings with timestamps.

Features

• Snowflake Integration: Connects to Snowflake to query customer feedback and on-time performance data
• Google Sheets Sync: Automatically updates Google Sheets with the latest data
• Comprehensive Data: Retrieves order information, delivery status, feedback ratings, and driver feedback
• Hub Mapping: Includes mapping for multiple distribution hubs across the US
• Logging: Detailed logging for monitoring and debugging the ETL process
• Role-Based Access: Uses Snowflake role-based access control for sensitive data

Prerequisites

• Python 3.12+
• Snowflake account with access to customer feedback data
• Google Cloud credentials with Google Sheets API access
• Required Python packages (see Installation section)

Installation

1. Clone the repository

   git clone <repository-url>
   cd customer_feedback

2. Install dependencies

   uv sync

3. Set up credentials

   • Place your Google Cloud credentials JSON file in the config/ directory as google_credentials.json
   • Configure Snowflake credentials in config/credentials.py
   • Create a .env file with necessary environment variables

Project Structure

customer_feedback/
├── main.py                          # Main application entry point
├── pyproject.toml                   # Project dependencies and metadata
├── README.md                        # This file
├── config/
│   ├── __init__.py
│   ├── credentials.py               # Snowflake and Google Sheets connection functions
│   └── google_credentials.json      # Google Cloud credentials (not committed)
└── queries/
    ├── cf_query.sql                 # Main customer feedback query
    └── on_time_performance.sql      # On-time performance query

Configuration

Credentials Setup

The config/credentials.py file should contain two main functions:

• get_snowflake_connection(): Returns a Snowflake connection object
• get_gspread_connection(): Returns an authenticated gspread client for Google Sheets

Environment Variables

Create a .env file in the project root with:

GOOGLE_SERVICE_ACCOUNT=<path_to_google_service_account_json>

Usage

Run the application with:

uv sync
uv run main.py

What the script does:

1. Connects to Snowflake using configured credentials
2. Executes the SQL query from queries/cf_query.sql
3. Retrieves customer feedback data with the following fields:
   • Reference Number
   • Order/Delivery Unit ID
   • Default Delivery Date
   • Last Mile Attempt Date
   • Current Status
   • On-Time Performance Group
   • Special Delivery Instructions
   • Feedback Rating and Comments
   • Driver Feedback
   • Hub and Location Information
4. Clears existing data from the Google Sheet (columns A:R)
5. Updates the 'data' worksheet with the new data

Data Processing

The application performs the following transformations:

• On-Time Performance Mapping: Categorizes orders as:

  • ONTIME: On time, early -1, or early -2
  • DELAYED: Late 1 or late 2
  • UNDELIVERED: Undelivered orders

• Hub Mapping: Maps internal hub codes to depot codes and city names across 22 distribution centers in the US

• Data Cleaning: Fetches results and converts them into a pandas DataFrame for easy manipulation

Dependencies

• gspread>=6.2.1 - Google Sheets API client
• gspread-dataframe>=4.0.0 - Pandas DataFrame support for gspread
• pandas>=2.3.0 - Data manipulation and analysis
• python-dotenv>=1.2.1 - Environment variable management
• snowflake-connector-python>=3.15.0 - Snowflake database connector

Error Handling

The application includes comprehensive error handling and logging:

• Connection errors are logged with descriptive messages
• Failed queries are caught and reported
• Google Sheets update failures are handled gracefully
• All operations are logged for audit trails

Logging

Logs are configured to display:

• Timestamp
• Logger name
• Log level
• Message

Example output:

2024-12-19 10:30:45 - __main__ - INFO - Starting HFDN Customer Feedback process
2024-12-19 10:30:46 - __main__ - INFO - Connecting to Snowflake...
2024-12-19 10:30:47 - __main__ - INFO - Retrieved 1,250 records from Snowflake
2024-12-19 10:30:50 - __main__ - INFO - Google Sheet updated

Troubleshooting

Connection Issues

• Verify Snowflake credentials are correct
• Check firewall/network connectivity to Snowflake

Google Sheets Issues

• Confirm the Google Sheet "HFDN Customer Feedback" exists
• Verify the service account has edit permissions on the sheet
• Check that google_credentials.json is correctly placed

Data Issues

• Verify SQL query is valid and returns results
• Check that required columns are present in the source data
• Ensure no schema changes have occurred

Contributing

When contributing to this project:

1. Test changes locally before committing
2. Update relevant SQL queries if data structure changes
3. Document any new functions or modifications
4. Keep logs informative but concise

Contact

For questions or issues, contact the US Logistics Analytics team.