Sun Theory Integrations and Analytics

1. Introduction

Overview:

Sun Theory operates multiple retail locations and a wholesale division, requiring seamless data connectivity and reporting across diverse systems. Facing complexity in integrating their wholesale POS platform and retail location POS systems, they sought to unify their data under a scalable, secure, and analytics-friendly infrastructure.

Challenge:

Fragmented data sources and difficulty connecting their wholesale database to Power BI had led to inefficient reporting and limited visibility. Additionally, the company needed to streamline their order management workflows between LeafLink and Pipedrive, as well as unify their retail data from FlowHub into a single data source. The client required solutions that would not only resolve existing integration challenges but also establish a foundation for ongoing operational improvements.

Objective:

• Migrate and optimize the existing database environment for improved performance and Power BI integration.
• Integrate LeafLink orders into Pipedrive to streamline order management and keep sales data up-to-date.
• Aggregate data from multiple retail locations via FlowHub and store it centrally for analytics.
• Implement scalable, maintainable solutions leveraging Azure Functions for automated data pulls and long-term growth.

2. The Problem

Background:

In the cannabis industry, accurate and timely data is crucial for managing compliance, inventory, and strategic decision-making. The client’s environment was dispersed across different hosting platforms and lacked a unified approach to integrations. This resulted in delayed reporting, cumbersome order management workflows, and a lack of holistic visibility into operations.

Pain Points:

• Limited Visibility: Difficulty connecting wholesale data to Power BI hindered timely, data-driven decisions.
• Manual Order Management: Handling LeafLink orders in Pipedrive was prone to delays and errors.
• Fragmented Retail Data: Multiple retail locations managed by separate POS systems required a centralized data solution.
• Complex Integrations: Synchronizing order updates, handling non-order data, and dealing with multiple authentication keys added technical complexity.

3. Our Solution

Discovery Process:

We began by auditing the client’s data infrastructure, identifying the critical integration points and mapping out workflows. Through close collaboration, we prioritized tasks: stabilizing the database environment, integrating LeafLink with Pipedrive, and setting up automated data pulls from FlowHub for Power BI analytics.

Proposed Solution:

• Database Migration & Optimization:
  Migrated from DigitalOcean to Azure, changing from Postgres to MySQL to align with the client’s reporting requirements and improve compatibility with Power BI.

• LeafLink to Pipedrive Integration:
  Implemented robust server-side webhooks using Django to standardize LeafLink’s webhook payloads before pushing data into Pipedrive. This ensured that whenever an order updated webhook was received from LeafLink, we retrieved the corresponding deal in Pipedrive and moved it through the correct pipeline stages (e.g., from “Shipped” to “Delivered”).

• FlowHub Retail Integrations for Power BI:
  Used Azure Functions to query FlowHub’s API every two hours. Each retail location had its own unique key, so we consolidated data from all sites into a single SQL database. This aggregation allowed Power BI to provide a holistic view across multiple locations.

• Additional Data Sync via Azure Functions:
  For LeafLink data not provided in webhooks, we created additional Azure Functions to fetch supplemental details every two hours, ensuring all order-related and non-order data remained current and comprehensive in the centralized database.

Technology Stack:

• Backend: MySQL on Azure for robust, scalable data storage.
• Integrations & Webhooks: Django for server-side webhook handling and standardization; Azure Functions for scheduled data pulls.
• Analytics & Visualization: Power BI for real-time reporting and interactive dashboards.

Development Approach:

We adopted an Agile, iterative approach, delivering incremental improvements, integrating feedback, and maintaining transparency with the client. This allowed us to adapt quickly to evolving requirements and ensure alignment with business objectives.

4. Implementation

Complex Webhook & Data Synchronization Setup:

• LeafLink to Pipedrive (Django & Webhooks): We established server-side webhook endpoints in Django to receive and process LeafLink’s incoming order data.
• Standardizing Payloads: Incoming LeafLink webhooks were normalized into a consistent format, ensuring Pipedrive’s API received uniform, error-free data.

• Deal Lifecycle Management: Upon receiving an order update webhook, we programmatically located the corresponding deal within Pipedrive’s CRM. Depending on the order’s new status (e.g., shipped, delivered), the deal was automatically moved to the appropriate pipeline stage. This eliminated manual status updates, reduced delays, and improved data accuracy.

• FlowHub Data Aggregation (Azure Functions & Scheduled Pulls): Every two hours, Azure Functions were triggered to query FlowHub APIs for each retail location’s unique key.

• Key-Based Data Retrieval: Using stored credentials for each location, the function fetched the latest sales, inventory, and transactional data.

• Consolidation in MySQL: Retrieved data from all locations was aggregated into a single SQL database. Power BI then connected to this centralized source, enabling unified reporting across the entire retail network.

• Supplemental LeafLink Data via Azure Functions: Some order-related details or non-order data were not available via LeafLink’s webhooks.

• Scheduled Fetches: Another Azure Function ran every two hours to pull the missing data directly from LeafLink’s API.
• Unified Data Repository: By merging webhook data and scheduled pull data, we ensured a comprehensive, up-to-date dataset for all operational and analytical needs.

Challenges Encountered:

• Dealing with Multiple Keys & Sources: Ensuring each retail location’s data remained distinct yet aggregable required careful data handling and schema design.
• Real-Time Status Updates: Coordinating order statuses between LeafLink’s webhooks and Pipedrive’s CRM demanded robust error handling and retry logic.
• Maintaining Data Consistency: Different data refresh intervals and asynchronous webhook triggers required precise logic to prevent data overwrites or inconsistencies.

Timeline:

• Initial Planning (2–3 Weeks): Assessed data sources, mapped integration logic, and defined webhook endpoints.
• Development & Integration (1–2 Months): Implemented Django webhooks, Azure Functions, database migrations, and Power BI connections.
• Testing & Refinement (Ongoing): Iteratively tested integrations, fine-tuned scheduling intervals, and ensured data accuracy.

Collaboration:

We maintained open communication channels with the client, providing frequent updates and demonstrations. Transparent issue tracking and milestone reviews ensured timely feedback incorporation and reinforced trust.

5. Results

Product Outcomes:

Sun Theory now benefits from a streamlined data infrastructure where wholesale and retail systems seamlessly synchronize. Orders from LeafLink update in near real-time within Pipedrive, and Power BI visualizations deliver actionable insights across all retail locations.

Business Impact:

• Efficient Order Management: Automated updates and CRM stage movements for orders reduced manual workload and improved responsiveness.
• Holistic Analytics: Aggregating data from multiple retail sites into a single database empowered stakeholders to make informed decisions based on unified, accurate data.
• Future-Proof Foundation: The architecture, leveraging Azure Functions and Django-based webhooks, is flexible enough to integrate additional systems, adapt to changing requirements, and scale as the business grows.

6. Lessons Learned

Value of Unified Data Workflows:

Combining webhook-driven integrations with scheduled data pulls allowed for a comprehensive, always-current data ecosystem. Strategically choosing technologies (Django for webhooks, Azure Functions for scheduled tasks, MySQL for storage) enabled efficient handling of multiple data sources and complex synchronization logic.

Continuous Improvement Through Iteration:

Early testing and iterative refinement ensured robust error handling, accurate data mapping, and a stable production environment. Maintaining documentation and inline code comments facilitated easier handovers and internal maintenance.

7. Conclusion

Summary:

By implementing server-side webhooks in Django, employing Azure Functions for scheduled data pulls, and ensuring consistent data formats and logic, we helped this large cannabis wholesaler and retailer achieve an integrated, scalable data infrastructure. The client now enjoys automated order management, unified analytics via Power BI, and a flexible platform for future expansions.

Call to Action:

If you need help orchestrating complex data workflows, integrating multiple systems, or optimizing your analytics pipeline, don’t hesitate to reach out! Click the "Let’s Talk" button to get started.