Get the latest from Your Weed Data — expert insights, feature releases, and cannabis market trends, all in one place. - Medium

Your Weed Data - Medium


<h4>Why Smart Cannabis Decisions Still Feel Blindfolded</h4>
<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ojJIZEqUgj98R1mG86hzdg.png"></figure><h4>The Daily Grind of Cannabis Decision-Making</h4>
<p>It’s 7:00 AM. Jamie, a producer-processor outside of Yakima, opens the laptop and begins sorting through a mountain of reports. What moved last month? What didn’t? Why are sales down for Blue Dream but up for the new pre-rolls? Where’s the pricing headed across the state?</p>
<p>The information exists — but it’s <em>scattered</em>.</p>
<p>The state’s traceability system has a report. So does the lab. Then there’s an inventory spreadsheet, some sales exports, and a stale dashboard from last quarter. Jamie toggles between tabs, looking for signal in the noise while emails from retailers pile up.</p>
<p>This is what decision-making looks like for most cannabis operators in Washington: reactive, fragmented, and exhausting.</p>
<h3>The Problem Isn’t Lack of Data — It’s Usability</h3>
<p>Washington’s cannabis market is one of the most tightly monitored in the country. Every licensee is required to report detailed data each month: inventory changes, product transfers, sales transactions, lab results. This system captures a complete picture of the industry’s activity.</p>
<blockquote>But accessing that picture? That’s the hard part.</blockquote>
<ul>
<li>Data is siloed in separate spreadsheets, each formatted differently.</li>
<li>There’s no easy way to benchmark your performance against competitors.</li>
<li>By the time you’ve compiled and cleaned what you need, it’s already outdated.</li>
<li>Most licensees don’t have time — or a dedicated analyst — to make sense of it all.</li>
</ul>
<blockquote>Operators spend hours each month chasing down answers to basic questions:<br> — <strong>How are my prices trending compared to others?</strong><br> — <strong>Who’s moving the most volume in my category?</strong><br> — <strong>Are my lab results competitive?</strong><br> — <strong>Which retailers are shifting their buying habits?</strong>
</blockquote>
<p>It shouldn’t take hours to find this information. And yet, for most — it does.</p>
<h3>The Cost of Flying Blind</h3>
<p>Running a cannabis business is already difficult. Doing it without clear visibility into the market makes it harder.</p>
<ul>
<li>Misprice a product, and you lose revenue — or get undercut.</li>
<li>Miss a shift in buying trends, and you’re stuck with inventory that won’t move.</li>
<li>Neglect performance tracking, and you can’t measure what’s working.</li>
<li>Waste time building reports manually, and you’re not spending it where it matters most: growing the business.</li>
</ul>
<p>This isn’t just inefficient — it’s risky. The industry is too competitive, and the margins are too tight to operate without clarity.</p>
<h3>Introducing Your Weed Data</h3>
<p><strong>Your Weed Data</strong> was built to change all of that.</p>
<p>We’ve taken Washington’s full traceability dataset — four years of transactional sales, inventory movement, lab testing, and licensee-to-licensee transfers — and turned it into a powerful, intuitive analytics platform.</p>
<p>No POS data. No third-party integrations. Just the source-of-truth from the state, cleaned and compiled into a <a href="http://www.yourweeddata.com/demo">self-service dashboard</a> anyone can use.</p>
<figure><img alt="" src="https://cdn-images-1.medium.com/max/982/1*itYtPHe7cTP1adwTHZddnw.gif"><figcaption>Your Weed Data Comes With A Full Suite of Intuitive Self Service Dashboards</figcaption></figure><blockquote>With Your <a href="http://www.yourweeddata.com/">Weed Data</a>, you can:</blockquote>
<ul>
<li>Benchmark your performance against every licensee in the state</li>
<li>Monitor pricing trends and category sales across regions</li>
<li>Understand who’s selling to who and how product flows through the supply chain</li>
<li>Compare lab results to the market, including testing frequency, THC/CBD ranges, and more</li>
<li>Download clean, export-ready data for further analysis or internal reporting</li>
</ul>
<p>No more guesswork. No more spreadsheets. Just the insights you need — ready when you are.</p>
<h3>Built for Washington, Backed by Analysts</h3>
<p>This isn’t a plug-and-play cannabis analytics tool. It’s a platform custom-built for Washington’s traceability system and fine-tuned by <a href="http://www.alma-analytics.com/"><strong>Alma Analytics</strong></a>, a team of cannabis data experts who understand both the complexity of the system and the day-to-day needs of its operators.</p>
<p>We’ve done the hard work of transforming raw traceability files into business-ready intelligence, so you can spend less time wrangling data and more time making decisions.</p>
<figure><img alt="" src="https://cdn-images-1.medium.com/max/960/1*H11DGGBb_zNxZvwpeC2JwQ.jpeg"><figcaption>Founder and Chief Insight Officer for Your Weed Data — Joseph Holmesmeyer</figcaption></figure><h3>What’s Next</h3>
<p>In the coming blog posts, we’ll walk you through exactly how Your Weed Data works — starting with a tour of the dashboard and how you can filter, compare, and act on insights without any technical background.</p>
<p>Until then, if you’ve ever spent half a morning cleaning up a CSV just to answer a basic sales question… you’re not alone. And we’ve built this for you.</p>
<h3>Ready to See Your Market Clearly?</h3>
<p>If you’re ready to stop guessing and start leading with data, Your Weed Data is ready for you.</p>
<p><strong>Set up your profile today and get a free 30-day trial</strong> using promo code: <strong>PIONEER</strong>.</p>
<p>No credit card required. Just fast, accurate insights built for Washington’s cannabis industry.</p>
<p>👉 <a href="https://yourweeddata.com/">Start your free trial at yourweeddata.com</a></p>
<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SqU5PhKrhkjyN606FbW0Xw.png"></figure><img src="https://medium.com/_/stat?event=post.clientViewed&amp;referrerSource=full_rss&amp;postId=64ff3b1541b6" width="1" height="1" alt=""><hr>
<p><a href="https://medium.com/your-weed-data/introducing-yourweeddata-com-64ff3b1541b6">Introducing YourWeedData.com</a> was originally published in <a href="https://medium.com/your-weed-data">Your Weed Data</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>


Introducing YourWeedData.com


<h3>Bringing Data to Life with Looker Studio</h3>
<p>With Looker Studio, your data is more than just numbers; it’s a compelling, visual narrative of your business story.</p>
<h4>Overview:</h4>
<p>Welcome back to our <a href="https://medium.com/@joey_30056/unleashing-small-business-superpowers-the-essential-tech-stack-for-robust-analytics-9e6a208d8c0c">educational series on embracing tech tools</a> for small businesses. Today, we spotlight <a href="https://lookerstudio.google.com/navigation/reporting">Looker Studio</a>, formerly Google Data Studio. With Looker Studio, your data is more than just numbers; it’s a compelling, visual narrative of your business story. And guess what? It’s free, powerful, scalable, and perfect for small businesses ready to experience the magic of data visualization.</p>
<figure><img alt="" src="https://cdn-images-1.medium.com/max/606/1*t2H4NKrRNCUOGCJgFeppOw.gif"></figure><h4>Prerequisites:</h4>
<p>Using Looker Studio doesn’t require any special coding prowess. It’s designed to be incredibly user-friendly, sporting a simple, drag-and-drop interface. As long as you have a Google account, you’re ready to dive in!</p>
<h4>Case Study: Shopify E-commerce Store</h4>
<p>Let’s revisit our Shopify e-commerce store scenario. We’ve consolidated data with <a href="https://medium.com/@joey_30056/harnessing-the-power-of-fivetran-for-efficient-data-integration-a-beginners-guide-6287a392ac3b">Fivetran</a>, shaped it using <a href="https://medium.com/@joey_30056/mastering-your-data-landscape-with-dbt-labs-a-game-changer-for-small-businesses-207ace176f2b">DBT Labs</a>, and analyzed it through Google <a href="https://medium.com/@joey_30056/cracking-the-code-of-cloud-based-databases-with-google-bigquery-a-small-business-guide-8bffc82fd90d">BigQuery</a>. Now, it’s time to visualize our data insights using Looker Studio.</p>
<h4>Instructions:</h4>
<ol>
<li>
<strong>Getting Started:</strong> Head over to <a href="https://lookerstudio.google.com/navigation/reporting">Looker Studio</a> and sign in with your Google account.</li>
<li>
<strong>Create a New Report:</strong> Click the “+ Blank Report” button. You’ll land on a blank canvas ready to become your business’s data masterpiece.</li>
<li>
<strong>Connect Data Source:</strong> Click on “Add Data”, select “BigQuery” (remember our consolidated and analyzed data?), and hit “Authorize”.</li>
<li>
<strong>Build Your Report: </strong>It’s time to breathe life into your data. Drag and drop the data fields you want to analyze, choose your visualization style (bar charts, pie charts, timelines — the world is your oyster!), and customize to match your branding. The ease and speed of creating professional-grade reports with Looker Studio is astounding.</li>
<li>
<strong>Share Your Report:</strong> Ready to share your insights? Click “Share” to distribute your report with anyone in your organization or, indeed, anyone with a Gmail account. Yes, it’s that easy to make data-backed decisions a company-wide culture.</li>
</ol>
<h4>Cost:</h4>
<p>Looker Studio doesn’t cost a dime. Dive into data visualization without an iota of financial worry!</p>
<p>We’ve reached the end of our series on supercharging your small business with robust tech tools. With Fivetran, DBT Labs, Google BigQuery, and Looker Studio, we’ve given you the keys to unlock a treasure trove of insights that can transform your business operations.</p>
<p>But, remember, our journey doesn’t have to end here. At Small Business Intelligence, we’re committed to helping businesses unravel the mysteries of their data. Swing by <a href="https://www.smallbusinessintelligence.co/">smallbusinessintelligence.co</a> and discover how we can partner to help you make data-informed decisions. We’re here, eager and ready to help you script your business’s success story. Let’s keep this exciting adventure going!</p>
<img src="https://medium.com/_/stat?event=post.clientViewed&amp;referrerSource=full_rss&amp;postId=cefe48ab5012" width="1" height="1" alt=""><hr>
<p><a href="https://medium.com/your-weed-data/bringing-data-to-life-with-looker-studio-a-small-business-guide-cefe48ab5012">Bringing Data to Life with Looker Studio: A Small Business Guide</a> was originally published in <a href="https://medium.com/your-weed-data">Your Weed Data</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>


Bringing Data to Life with Looker Studio: A Small Business Guide


<p>You’ve created lots of code in a BigQuery project, but you don’t have a backup of the table definitions, views, functions or procedures. Use this Python frame work to create a file for every entity in your database</p>
<figure><img alt="" src="https://cdn-images-1.medium.com/max/960/1*H11DGGBb_zNxZvwpeC2JwQ.jpeg"><figcaption>Joey Holmesmeyer — Founder of Small Business Intelligence and Google Cloud Evangelist</figcaption></figure><h3>The Use Case</h3>
<p>You are developing in Google BigQuery and you have spent a lot of time setting up table structures, writing stored procedures to load the entities and create analytics products.</p>
<p>The best practice would be to store all your code in a code repository like GitHub, however you or your team doesn’t have this luxury. So the question begs, how can you create a backup copy of all you and your teams hard work?</p>
<p>In the article below I will walk you through how to implement a Python program that I wrote which you can use that leverages the information schema in BigQuery to create a local file directory that contains a sql file for every table, view, stored procedure and function in your BigQuery project.</p>
<h3>Getting Started — Prerequisites</h3>
<p>If you are a data or analytics engineer working in BigQuery the steps I will outline below will be critical for you to implement and maintain a working knowledge of for this use case and beyond.</p>
<ol>
<li>Download <a href="https://code.visualstudio.com/download">VS Code</a> (or an IDE for your choice), a free and widely used code editing tool that once mastered will change the way you work</li>
<li>Download <a href="https://www.python.org/downloads/">Python 3</a> or higher, once again a free program to download that will eventually end up supercharging your workflow</li>
<li>Download and install <a href="https://cloud.google.com/sdk/docs/install">Google Cloud CLI</a>, this package is critical to developing using python via the VS Code as it allows you to execute queries and commands in BigQuery from your IDE and local command line</li>
<li>Gain familiarity with the <a href="https://cloud.google.com/bigquery/docs/information-schema-intro">BigQuery information schema</a>. Lots of engineering work isn’t very sexy, it involves tedious work and using metadata from the information schema will not only give you visibility into the work you and your team have completed at scale, but also enable lots of automation</li>
</ol>
<h3>Building the Program — Information Schema SQL Query</h3>
<p>The sql query provided below will retrieve a list of all the entities currently in your BigQuery project:</p>
<pre>## Query to fetch all the entities and their respective DDLS<br><br>WITH<br><br>entity AS<br>(<br>SELECT<br>    table_catalog,<br>    table_schema,<br>    table_name,<br>    table_type,<br>    ddl,<br>FROM<br> `myProject.myDataset.INFORMATION_SCHEMA.TABLES;`<br><br>UNION ALL<br><br>SELECT<br>    routine_catalog,<br>    routine_schema,<br>    routine_name,<br>    routine_type,<br>    ddl,<br>FROM<br> `myProject.myDataset.INFORMATION_SCHEMA.ROUTINES`<br>)<br>SELECT <br>    table_catalog AS entity_catalog,<br>    table_schema AS entity_schema,<br>    table_name AS entity_name,<br>    table_type AS entity_type,<br>    ddl AS entity_ddl,<br>FROM entity<br></pre>
<p>In this query we are selecting data from the information_schema.tables and information_schema.routines backend tables and combining them together on their common fields. The final select statement simply renames our columns to have a more generic names since the columns will contain both routine (stored procedure) and table/views details.</p>
<h3>Building the Program — Python Code</h3>
<p>The Python code below will create a file for each entity in your BigQuery project!</p>
<p>Here’s how to execute it:</p>
<ul>
<li>The template can only take you so far, make sure you read the code below, make updates such as the directory you will need to write the files to and the BigQuery project you’ll want to extract from (this will be defined in the SQL)</li>
<li>Open VS Code</li>
<li>Create a new file named ‘bq_ddl_extractor.py’ and paste the code below into the file and hit save</li>
<li>Make sure that your Google Cloud CLI is connected to your BigQuery Project and can be interacted with via the VS Code command line terminal (<strong>Pro tip</strong> — ask <a href="https://chat.openai.com/auth/login">GPT</a> to help you do this if you don’t know how)</li>
<li>Hit the play button on the upper left hand corner of VS Code while in the newly created Python file</li>
<li>The output of your program will contain a parent folder named backup and within that folder you will the following directory structure: Dataset/Entity Type/File.sql</li>
</ul>
<pre>import os<br>import re  # Regular expressions library<br>from google.cloud import bigquery<br><br><br># Instantiate a BigQuery client<br>bq = bigquery.Client()<br><br><br># Query to fetch all the entities and their respective DDLS<br>query = """<br><br>SELECT<br>    table_catalog,<br>    table_schema,<br>    table_name,<br>    table_type,<br>    ddl,<br>FROM<br> `myProject.myDataset.INFORMATION_SCHEMA.TABLES;`<br><br>UNION ALL<br><br>SELECT<br>    routine_catalog,<br>    routine_schema,<br>    routine_name,<br>    routine_type,<br>    ddl,<br>FROM<br> `myProject.myDataset.INFORMATION_SCHEMA.ROUTINES`<br><br>"""<br><br><br># Fetch the data<br>data = bq.query(query).to_dataframe()<br><br><br># Iterate over each row<br>for _, row in data.iterrows():<br>    # Create directories for each dataset and table type if they do not exist<br>    dir_name = os.path.join('sproc_generator/backup', row['table_schema'], row['table_type'])<br>    if not os.path.exists(dir_name):<br>        os.makedirs(dir_name)<br><br>    # Create the full path for the file<br>    file_path = os.path.join(dir_name, f"{row['table_name']}.sql")<br><br>    # Write the DDL to a .sql file<br>    with open(file_path, 'w') as f:<br>        # Remove consecutive newlines<br>        cleaned_ddl = re.sub("\r?\n\s*\r?\n*", "\n", row['ddl'])<br>        f.write(cleaned_ddl)<br><br>    # Print the path of the created file<br>    print(f"File created: {file_path}")<br><br>print("Backup completed successfully.")</pre>
<h3>Explanation of the Python Code:</h3>
<p>The Python code we use in this article follows a systematic process to fetch the DDLs for each entity in your BigQuery project, and then writes these to SQL files organized by dataset and entity type. Let’s take a closer look at the steps:</p>
<h4>Step 1: Import Required Libraries</h4>
<pre>import os<br>import re<br>from google.cloud import bigquery</pre>
<p>This script uses three libraries:</p>
<ul>
<li>os module in Python provides functions for interacting with the operating system.</li>
<li>re module allows us to use regular expressions, a powerful tool for string manipulation.</li>
<li>google.cloud.bigquery is a client library for Google BigQuery that allows Python code to interact with the BigQuery service.</li>
</ul>
<h4>Step 2: Create BigQuery Client</h4>
<pre>bq = bigquery.Client()</pre>
<p>This line of code initializes a BigQuery client that will handle the interaction with the BigQuery service.</p>
<h4>Step 3: Define the SQL Query</h4>
<p>The SQL query fetches the required data from the information schema in BigQuery. This includes information about tables and routines (stored procedures and functions), including their DDLs.</p>
<h4>Step 4: Execute the Query and Fetch Data</h4>
<pre>data = bq.query(query).to_dataframe()</pre>
<p>Here, the query method of the BigQuery client executes the SQL query, and the to_dataframe method converts the result into a pandas DataFrame.</p>
<h4>Step 5: Iterating Over Rows and Writing to SQL Files</h4>
<p>The following loop iterates over each row in the fetched DataFrame. For each row, it:</p>
<ul>
<li>Creates a directory path based on the entity’s schema and type.</li>
<li>Checks if this directory already exists, and if not, creates it.</li>
<li>Constructs a file path for the .sql file and writes the DDL (cleaned of consecutive newlines) into the file.</li>
</ul>
<pre>for _, row in data.iterrows():<br>    dir_name = os.path.join('sproc_generator/backup', row['entity_schema'], row['entity_type'])<br>    if not os.path.exists(dir_name):<br>        os.makedirs(dir_name)</pre>
<pre>    file_path = os.path.join(dir_name, f"{row['entity_name']}.sql")<br><br>    with open(file_path, 'w') as f:<br>        cleaned_ddl = re.sub("\r?\n\s*\r?\n*", "\n", row['entity_ddl'])<br>        f.write(cleaned_ddl)<br><br>    print(f"File created: {file_path}")</pre>
<h4>Step 6: Backup Completion Message</h4>
<p>After the loop has processed all the rows in the DataFrame, the program prints a success message:</p>
<pre>print("Backup completed successfully.")</pre>
<p>This way, the user will know that the script has finished running and that all entity DDLs have been backed up.</p>
<p>In conclusion, this script uses the capabilities of Python, BigQuery’s information schema, and the local file system to create an organized backup of your BigQuery project. The combination of these tools makes it a powerful and flexible solution for this task.</p>
<h3>Thanks for reading!</h3>
<p>Hope you enjoyed this tutorial and are able to put this concept to use on your next or current BigQuery project. If you have any questions or comments I would love to hear from you so please drop a comment below! If you think this is interesting but would like our team to implement it, we’d love to help with that too, book 15 minutes on our <a href="https://smallbusinessintelligence.as.me/">calendar</a> to discuss!</p>
<p>Joey Holmesmeyer — Founder @ <a href="http://smallbusinessintelligence.co/">Smallbusinessintelligence.co</a></p>
<img src="https://medium.com/_/stat?event=post.clientViewed&amp;referrerSource=full_rss&amp;postId=b4f2405736a5" width="1" height="1" alt=""><hr>
<p><a href="https://medium.com/your-weed-data/automate-bigquery-code-backup-with-python-framework-tutorial-b4f2405736a5">Automate BigQuery Code Backup With Python Framework Tutorial</a> was originally published in <a href="https://medium.com/your-weed-data">Your Weed Data</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>
