Category: Analytics

Categories
Analysis Analytics Business Innovation

Behind the Scenes: The Phases of “The King’s Pantry” Dataset

Phase 1: The Vision

When I started The King’s Pantry, I wanted it to be more than just another themed dataset. My goal was to build something that mirrors real ERP and retail systems, while still being fun and creative. Every decision, from the product hierarchy to tax logic, came from things I’ve actually seen as an analyst in retail and distribution.

So while this project lives in a fictional Westerosi universe, it’s still rooted in business reality — complete with purchase costs, margins, vendors, customers, and sales channels that behave like a true mid-market grocery retailer.

Phase 2: Schema Design Decisions

I started with an ERP-inspired hybrid schema, structured like a star for analytics (for my PowerBI tutorial) but modeled like a snowflake behind the scenes (for SQL practice).


It’s the same foundation used in real retail and distribution systems — designed to evolve and scale just like one.

The focus for Launch 1 was on external stakeholders — the data that drives customer experience, product management, and vendor relationships.

💡 Real-world note:
In many companies, analysts rarely build the fact tables themselves, those are usually prepared by a data engineering or business intelligence team as part of the data warehouse. At least that was my experience so far in my career.


But in smaller organizations (or legacy environments), you often don’t have that luxury. You’re pulling granular transactional data straight from systems like SSMS, aggregating it manually in Excel through Power Query, or reshaping it in Power BI before analysis.

Call me old school, but I actually prefer the latter because it gives you more freedom — and it keeps you closer to the raw story in the data.

The challenge with pre-built fact tables is that they’re often fully aggregated, and the engineering team may keep the backend logic private behind RLS or security measures — so you can’t see how the metrics are actually calculated.

The biggest problem I’ve personally had with this were all the debugging moments I’d have when making DAX measures. If a KPI wasn’t calculating correctly and I was using a calculated field or column where I couldn’t see the actual formula (typically coded in SQL), I’d literally pull my hair out because it meant I’d have to confirm the backend with the data engineering team.

That’s why this dataset is designed to simulate both sides — the structured back end (for those practicing SQL joins and normalization) and the simplified reporting layer (for dashboard modeling and storytelling).

For Launch 1, I focused on the external stakeholders of the realm — the data that drives customer experience, product management, and vendor relationships.

🧺 Product
  • 540 SKUs across 8 major categories, including 40 new “Apothecary & Household Essentials.”
  • Added item_taxonomy_1 and item_taxonomy_2 to model product hierarchies used in analytics tools like Power BI.
  • Included boolean flags (is_active_flag, is_stock_item, is_discontinued_flag) so analysts can simulate filtering and segmentation logic in SQL.
  • Introduced private_label_tier and the private brands dimension (The King’s Pantry Select, Smallfolk Essentials, and Crown Reserve) to mirror how real retailers manage exclusive product lines. Think of Sam’s Club ‘Member’s Mark’ items or Costco’s ‘Kirkland Signature’.
  • Integrated vendor_id and brand_id for relational integrity, modeling the link between purchasing and merchandising systems.
  • Embedded freight cost within landed_cost, reflecting real-world pricing where freight is often factored into total COGS.
🏛️ Vendor
  • Represents all supplier entities, including wholesale producers, guild distributors, and artisan makers.
  • Added preferred_vendor_flag to enable conditional join exercises and performance reporting.
  • Designed to link one-to-many with dimProduct for realistic vendor-to-product mapping.
🧍 Customer
  • Unified B2C (consumer) and B2B (business) customer logic into a single dimension (reflecting how many ERP systems track both under one master table).
  • Added first_name / last_name for individual consumers, while allowing nulls for business accounts.
  • Added business attributes such as tax_id, payment_terms, and credit_limit to represent wholesale clients.
  • Introduced a parent–child hierarchy (parent_customer_id) for roll-ups, allowing analysts to practice self-joins (e.g., aggregating sales by parent guild).
  • Localized addresses into Westerosi equivalents for immersive storytelling while maintaining analytic structure:
    • manor_or_quarter (Address Line 1)
    • holdfast_name (Address Line 2)
    • town_or_keep (City)
    • realm (State/Region)
🧾 Sales_order
  • Header-level table containing one record per sales transaction.
  • Uses channel-coded order IDs for realism and practice with pattern-based joins:
    • RP-###### for Raven Prime (online)
    • MS-###### for Market Stall (in-store)
    • GS-###### for Guild Supply (wholesale)
    • CC-###### for Court Catering (institutional)
  • Attributes include:
    • sales_order_id, customer_id, order_date, channel_code, subtotal, tax_amount, discount_amount, total_amount.
  • Designed to teach time intelligence, discount calculations, and channel-based segmentation.
📦 Sales_order_details
  • Line-level table containing individual product sales.
  • Includes sales_order_id (FK), product_id, quantity, unit_price, discount, and calculated line_total.
  • Ties back to dimProduct for category insights and dimVendor for supplier contribution analysis.
  • Structured to enable exercises on joins, aggregations, and performance metrics such as margin by product, vendor, or category.
🛍️ sales_channel_lookuP
  • Lookup table for human-readable translation of sales channels.
  • Codes: CodeChannel NameReal-World EquivalentRPRaven PrimeeCommerceMSMarket StallIn-store / POSGSGuild SupplyB2B / WholesaleCCCourt CateringInstitutional / Catering
  • Created to model data normalization and to demonstrate how dimension lookups support clean reporting layers in BI tools.

Together, these tables form a scalable base that mirrors a real ERP data warehouse — ready for expansion into Phase 2 (internal stakeholders) with future additions such as:

  • purchase_orders, purchase_order_details, buyers, and buylines.
🛍️ brand

The brand table brings together both private labels and vendor-supplied brands — a key part of modeling how real grocery and retail businesses differentiate their assortments.

  • Private Labels:
    These are the in-house lines owned by The King’s Pantry itself. Each one serves a different tier of customers, from value to luxury.
    • Smallfolk Essentials → everyday affordable staples (Value Tier)
    • The King’s Pantry Select → premium curated goods (Premium Tier)
    • Crown Reserve → luxury imports and vintages (Luxury Tier)
  • Vendors:
    External suppliers and trade guilds from across the realm — from House Tyrell Provisions (grains and produce from The Reach) to Iron Isles Fisheries (smoked herring and seafood). These represent the merchant partnerships that give the dataset realism and variety.

The brand dimension links directly to the product table via brand_id, which lets analysts:

  • Compare private label vs. national brand performance.
  • Analyze vendor contribution by region.
  • Build margin and assortment reports (e.g., private label share by category).

By separating brand logic from vendor and product attributes, this structure mirrors how real ERP and category management systems track brand hierarchy and ownership.

Fun fact: This addition came from a real-world insight when I did some research — many analysts report that over 25–40% of grocery assortment belongs to private label. Modeling this helps simulate the balance between in-house innovation and vendor partnerships.

This architecture allows The King’s Pantry to evolve naturally into a complete business system: from procurement to customer sale. All in all, as I look back at these tables and see how I created it, it subtly showcases my experience as a pricing analyst, supply chain analyst, and data analyst (which is pretty awesome to see).

Phase 3: Product Strategy & Scale

Initially, I planned for 10 items per category (similar to my Harry Potter dataset) but to make sales insights more realistic, I scaled to ~540 SKUs with weighted category distributions that reflect how grocery assortments work in the real world:

I also created three private label tiers to simulate real-life pricing and margin structures:

  • Smallfolk Essentials (Value) – Lower price, lower margin, everyday goods
  • The King’s Pantry Select (Premium) – Balanced price and margin
  • Crown Reserve (Luxury) – High margin, low volume

Phase 4: Pricing, Margin & Costing

To teach financial analytics concepts, each product includes:

  • Purchase cost
  • Landed cost (includes freight markup)
  • Base price
  • Margin percentage

In real-world ERP systems, freight is often embedded in landed cost, so we replicated that. This teaches analysts how to work with true cost of goods sold (COGS) data and margin analysis in SQL or Power BI.

Phase 5: Transactional Layer

With the help of my AI Wizard (shout out to you ChatGPT 🫶), we generated 10,000 sales orders and ~50,000 order lines using:

  • Weighted category probabilities per channel (e.g., more beverages online, more produce in-store).
  • Sales channels that bring the world to life and mirror real operations:
    • 🦅 Raven Prime – Online/eCommerce
    • 🏪 Market Stall – In-store / POS
    • ⚙️ Guild Supply – B2B wholesale
    • 🍽️ Court Catering – Institutional / event clients

Each order ID was given a prefix code (RP-, MS-, GS-, CC-) to simulate how multiple systems generate records differently — just like in real ERP environments where analysts must decode naming conventions.

We also introduced minimum order thresholds for B2B channels, reflecting wholesale minimums (e.g., 300 gold dragons per order).

Phase 6: Documentation & Learning Design

As a reflection tool for myself and a resource for those who may use the dataset, I included a /docs folder with explainers for:

  • Category weighting logic
  • Discount and tax rules
  • Customer segmentation
  • Channel mapping

This gives learners an inside look into how analytical datasets are actually built and maintained — the “why” behind every number.

Learning Opportunities

Even naming conventions tell a story.

“RP-001043” doesn’t just mean an order — it means an eCommerce transaction with a digital payment, possibly from a loyalty shopper.

By working with this dataset, analysts can learn:

  • How star schemas are modeled in analytics projects.
  • How to trace data lineage from customer to product to sale.
  • How to clean, filter, and join real-world imperfect data.
  • Why documentation and metadata design matter as much as visuals.

I know this might seem like an area that’s easy to overlook, but I made sure to include proper naming conventions to show analysts just how critical this is to a healthy database.

Out in the real world (believe it or not), there are fully functioning businesses with god-awful naming conventions — or no process at all.

What does that mean?
It means garbage data eventually piles up in the system, especially if no one’s maintaining it.

And you know what garbage data does?
It makes things harder for analysts — like when you have five versions of the same product, all still active, all with different names, and each with its own pricing and sales history.

So what do we analysts hate? Let’s all say it together: GARBAGE DATA.

That’s when you find yourself in full-on “WTF” mode — retracing logs, comparing timestamps, and trying to figure out which item actually belongs in your report.

Sorry, I had some traumatic flashbacks and my coffee is just kicking in 😂

Alas, tangent done.

Closing Thoughts

I started The King’s Pantry because I wanted to challenge myself to see if I could create a fully functioning dataset from scratch that feels real. Somewhere along the way, it turned into a teaching tool — not just for others, but for me too.

Building this dataset reminded me that data modeling is both art and architecture — creative, detailed, and deeply human.

I also found myself reflecting on the sheer complexity of building even a simulated dataset. Between balancing relationships, crafting believable hierarchies, and maintaining business logic across every table, it gave me a whole new level of respect for the datasets we take for granted — like AdventureWorks or Northwind.

Those projects were built by full teams; The King’s Pantry was built by one analyst — and, well, a very helpful AI wizard 🧙‍♂️. It reminded me how much thought, iteration, and testing goes into bringing a fictional world to life with data.

I may not have the resources of a Microsoft dev team, but what I do have is the curiosity and creativity to simulate the process, piece by piece. And honestly, I’m proud of that. And it’s so much fun (sorry not sorry for being a nerd)!

one final reflection thought, i promise

If I’ve learned anything from this project, it’s that data modeling can be as personal as it is technical. Every column tells a story, every join reflects a relationship — and somewhere between the tables and constraints, I found pieces of my own journey stitched quietly into the schema.

Until next time,

Ardonna •ᴗ•

author’s note

Ardonna Cardines Avatar

Hi, I’m Ardonna Cardines — a data analyst and creator of Mercury Musings, where business meets imagination through data. I love blending analytics, design, and storytelling to make learning data modeling and visualization approachable — one creative dataset at a time.

If you’ve enjoyed this post, I’d love for you to follow along.
You can subscribe to my blog for new tips + tutorials, creative datasets, and behind-the-scenes projects — or connect with me on LinkedIn where I share updates and learning resources for analysts and data storytellers.

Categories
Analysis Analytics Business Decisions

The King’s Pantry — Where Business & Westeros Converge

🌾 Full Circle

It’s funny how things come full circle. Back in undergrad, Database Design and Modeling was one of my favorite classes — I loved the logic, the structure, and the creativity behind building relationships between tables. I didn’t realize then that I’d be revisiting those same concepts years later, this time through the lens of a working analyst.

Now, projects like The King’s Pantry let me bring that passion back to life, combining the fundamentals I first learned in the classroom with everything I’ve experienced in real-world ERP systems. It’s proof that even as our careers evolve, the subjects we once loved have a way of finding us again — just in more meaningful, applied ways.

Meet The King’s Pantry

A few weeks ago, I started building something new — a dataset inspired by the Game of Thrones universe, but modeled after real ERP systems I’ve worked with in retail and distribution.

Meet The King’s Pantry, a grocery market fit for the realm.

It’s more than a themed dataset, it’s a teaching tool designed to help analysts learn how to connect sales, products, vendors, and customers the way real business systems do.

the schema: where business logic meets design

Every dataset needs a solid foundation, and The King’s Pantry was designed with a structure that mirrors a real ERP system: simple enough for analysts to explore, yet realistic enough to teach key data relationships.

At its core, the schema includes five main tables that represent how business flows through the kingdom:

  • Customer — Holds details about every buyer in the realm, from noble households to local merchants. Includes hierarchy fields that let you practice self-joins and account roll-ups.
  • Vendor — Represents the suppliers (or noble houses) who provide goods to The King’s Pantry. Each vendor links to multiple products and includes a preferred vendor flag for analysis.
  • Product — The heart of the operation. This table lists every item sold in the market (from fresh produce to imported sweets along with category taxonomy, pricing, and cost details).
  • Sales Orders — Records transactions placed by customers. Each order includes details like customer ID, order date, payment method, and status, a perfect table for learning joins and date-based analysis.
  • Sales Order Details — Breaks each order down into individual line items. This is where quantities, pricing, and margins live, allowing for analysis at the most granular level.

Together, these tables create a relational map that connects vendors → products → sales → customers, forming the foundation for everything from category dashboards to SQL query practice.

Coming This saturday: Power BI Tutorial

The first tutorial will walk through category and sales insights using The King’s Pantry dataset, designed in the same storytelling style as my Daily Grind Coffee dashboard.

We’ll build visualizations that mirror real-world category management dashboards while keeping the aesthetic of a Westerosi market.

Coming next Monday: SQL Tutorial

Next, I’ll release a SQL tutorial with hands-on exercises based on queries I actually used in my analyst roles (from calculating margins to joining vendor data through products and sales orders).

This will show how technical SQL joins and calculation translate into the insights that Power BI visualizes, connecting backend logic to business storytelling.

Why I Built This Project

I wanted to bridge the gap between creativity and real-world analytics, showing that learning SQL, database design, and data modeling doesn’t have to be dry.

By re-imagining ERP systems through a fantasy lens, I’m creating a way for analysts to practice with data that feels approachable, fun, and still grounded in real business logic.

Sneak Peek: The Next Launch

This first release focuses on the external stakeholders of the realm — customers, vendors, and marketplace sales.

In the next phase of The King’s Pantry, we’ll turn the focus inward — toward the internal stakeholders who keep the kingdom running.

That means expanding the dataset to include purchase orders, purchase order details, buylines, buyline branches, and more, reflecting how businesses manage inventory, procurement, and supply chain performance behind the scenes.

It’ll be a deeper dive into the full ERP cycle (from purchase to sale) and I can’t wait to build it piece by piece.

A Little Poetic, Isn’t It?

When I started my blog and LinkedIn, it was mainly to build a professional portfolio and presence. But somewhere along the way, it evolved into something I genuinely love — a space to create projects, resources, and tutorials to help others do the same.

I took the advice to “build a project to stand out to recruiters”… and ended up building a project to help others build their own.

Project inception? Maybe.

The Realm Awaits

Stay tuned — the first Power BI tutorial drops this Friday, followed by the SQL tutorial on Monday.


Because even in Westeros… data reigns supreme.

Until next time,

Ardonna •ᴗ•

author’s note

Ardonna Cardines Avatar

Hi, I’m Ardonna Cardines — a data analyst and creator of Mercury Musings, where business meets imagination through data. I love blending analytics, design, and storytelling to make learning data modeling and visualization approachable — one creative dataset at a time.

If you’ve enjoyed this post, I’d love for you to follow along.
You can subscribe to my blog for new tips + tutorials, creative datasets, and behind-the-scenes projects — or connect with me on LinkedIn where I share updates and learning resources for analysts and data storytellers.

Categories
Analysis Analytics Business

ETL vs ELT: Why Knowing the Difference Still Matters

The Acronym That’s Everywhere

When I first learned about ETL (Extract, Transform, Load) during my undergrad (2017–2019), it felt like the golden rule of data movement.

Fast forward to my master’s program (2023–present), and suddenly… it’s ELT. Same letters, different order, but a completely new approach to data processing.

So, what changed?

FROM ETL TO ELT

ETL ruled the traditional data warehouse world — where storage was expensive and processing power was limited.
Transformation had to happen before data was loaded into the warehouse.

But as cloud storage (like Snowflake, BigQuery, and Azure Synapse) became faster and cheaper, the paradigm flipped.
Now, data gets loaded first, then transformed inside the warehouse itself. Hence: ELT.

Why This Still Matters — Even as ETL “Fades Out

It’s tempting to think ETL is outdated, but it’s not gone, just evolving.

Many companies today still rely on legacy systems or are in the middle of migrating to cloud environments. Others are merging with businesses already using ELT.

As an analyst, you might encounter:

  • Nightly ETL batch jobs in SSIS
  • Hybrid pipelines with external transformations
  • Full ELT stacks using dbt + Snowflake

Understanding both gives you a real-world edge. You’ll walk into a job ready to troubleshoot data flows, explain performance issues, or even guide migrations, not just run queries.

In one of my data analyst roles, I experienced this firsthand. LinkedIn is full of seasoned analysts offering advice about “what it’s really like” out there, but most of those conversations center around big tech companies, where systems are already deep into the ELT phase.

The reality?

Not everyone works in — or even wants to work in — big tech.
Many analysts thrive in industries that keep the world running:
🏥 Hospitals
🏦 Financial institutions
🎓 Higher education
🏪 Everyday businesses and distributors

And here’s the truth no one talks about:

Many of these organizations still rely on ETL, where nightly batch jobs quietly run behind the scenes to keep operations moving.

Some are just beginning to make the shift toward cloud-based storage, balancing modernization with legacy systems that have been around for decades.

✨ That’s why knowing both ETL and ELT isn’t just a technical advantage — it’s a career advantage. It allows you to step confidently into any environment — whether it’s a cutting-edge cloud setup or a legacy warehouse that still hums at midnight.

And personally, this where I thrive and feel I make the most impact. This is why I started this blog, as a resource for junior analysts stepping into the real world — because this kind of guidance is what I wish I had years ago.

Why Foundational Knowledge Sets You Apart

When you look at job descriptions, what do you see?
Azure. Snowflake. ERP. DBT.

These aren’t just buzzwords — they’re clues. They reveal the data ecosystem that drives a company’s analytics workflow.

Most analysts focus on the tools (SQL, Python, Power BI), but it’s the foundation that makes you stand out. Knowing how and where data is stored, structured, and transformed separates someone who writes queries from someone who designs solutions.

FINAL THOUGHTS

Whether it’s ETL or ELT, what matters most is understanding how data moves — and why that matters.

Until next time,

Ardonna •ᴗ•

author’s note

Ardonna Cardines Avatar

Hi, I’m Ardonna Cardines — a data analyst and creator of Mercury Musings, where business meets imagination through data. I love blending analytics, design, and storytelling to make learning data modeling and visualization approachable — one creative dataset at a time.

If you’ve enjoyed this post, I’d love for you to follow along.
You can subscribe to my blog for new tips + tutorials, creative datasets, and behind-the-scenes projects — or connect with me on LinkedIn where I share updates and learning resources for analysts and data storytellers.

Categories
Analysis Analytics Business Decisions Strategy Thoughtful Thoughts

House of Data: Data Professionals as Targarayens and their Dragons

“In the Realm of Data, we tame dragons — not dashboards.”

So… I swear I started this blog to share my data projects and build my portfolio. Somewhere along the way, I accidentally created an entire Targaryen-inspired data meme series instead. 😅

Because honestly? Data professionals are a little like Targaryens.


We’re passionate, fiery, and sometimes one bad join away from chaos. Our tools are our dragons — powerful, unpredictable, and capable of absolute brilliance or disaster, depending on how we use them.

So in true Mercury Musings fashion, meet the House of Data…

⚔️ House of Data: The Targaryens of Analytics

🩸 Rhaenyra Targaryen — The Data Analyst
Turns chaos into clarity, one query at a time.

🐍 Daemon Targaryen — The Data Engineer
Builds the pipelines that keep the realm alive.

☀️ Aegon II Targaryen — The KPI Executive
Rules by vanity metrics and victory charts.

🧠 Aemond Targaryen — The AI Strategist
Sees every move before it happens — and still plays to win.

🔮 Helena Targaryen — The Predictive Analyst
Sees patterns others can’t — the data whisperer of the realm.

👑 Viserys Targaryen — The Data Architect
Builds empires of tables and schemas that outlive kings.

💜 Rhaenys Targaryen — The Data Governance Lead
The queen who never was — but still led.

⚔️ Baela Targaryen — The Data Operations Manager
Keeps the data realm running — one project at a time.

If you were in the House of Data, which one would you be — the analyst, the engineer, the strategist, or the dragon?

Until next time,

Ardonna •ᴗ•

author’s note

Ardonna Cardines Avatar

Hi, I’m Ardonna Cardines — a data analyst and creator of Mercury Musings, where business meets imagination through data. I love blending analytics, design, and storytelling to make learning data modeling and visualization approachable — one creative dataset at a time.

If you’ve enjoyed this post, I’d love for you to follow along.
You can subscribe to my blog for new tips + tutorials, creative datasets, and behind-the-scenes projects — or connect with me on LinkedIn where I share updates and learning resources for analysts and data storytellers.

Categories
Analysis Analytics Business Decisions Growth Innovation Lifestyle Reflection Reflections Thoughts Tips

Wardrobe Optimization 101: The Closet That Felt Like a Database

There’s something oddly humbling about standing in front of a packed closet and realizing you still have “nothing to wear.”

As I stared at the rows of shirts, dresses, and jeans (some I hadn’t worn in years) I couldn’t help but draw data parallels to my closet… which was its own dataset. Messy, redundant, and full of null values.

So, I decided to treat it like a data problem.

If I can clean, model, and transform millions of rows of data — surely I could handle a hundred hangers.

STEP 1: EXTRACT – PULL OUT THE PIECES THAT MATTER

In data, extraction means pulling from a messy source and capturing what’s worth analyzing.

In closets, it means facing the pile and being brutally honest with yourself… and your consumption problems 🥲

I pulled everything out: clothes, shoes, purses, even those “just in case” items that hadn’t seen daylight in years.

This was my data extraction phase, full transparency.

As I started sorting, I noticed a pattern.

The pieces that truly stayed with me weren’t the trend-driven ones I’d bought on impulse — they were the classics. The high-quality basics I’d invested in over the years: the crisp white button-up shirt, the houndstooth work pants , the heavy grey cardigan. Many of my favorite pieces from Everlane and Zara.

It reminded me of the difference between fast data and clean data.

Fast fashion can be tempting, just like downloading flashy datasets for quick results but in the long run, it’s always the timeless pieces that hold their value.

ELT QUERY

SELECT * FROM closet WHERE joy = TRUE AND quality = ‘timeless’;

Step 2: Load — Stage Your Data (and Your Rack)

Once I’d extracted my favorite pieces, I loaded them onto a single clothing rack — my staging table. I went on Amazon the day before and bought one for $30 and telling myself: everything you choose has to fit on here. Instead of putting everything back in my closet, I needed to force myself to part ways with pieces I haven’t touched in so long and free up space that I could use for other storage like my yarn collection – ha.

Just like loading data into a staging environment, this step helped me visualize patterns and relationships.

I began noticing color palettes (my “columns”), favorite fits (my “key values”), and duplicates (“Do I really need three beige bottoms?”).

The rack became my mini data warehouse.

Step 3: Transform — Preparing for the Next Phase

In data, transformation is where separate tables come together through joins to create new meaning.

In fashion, the same principle applies. My base outfit, a classic striped button-up and wide-leg denim is like my primary table.

From there, I layered on new pieces: a denim vest, a paisley patterned jacket, a gingham coat. Styling instead of just “wearing”. Each addition felt like a join, combining two clean, distinct datasets to create a new, more insightful result.

🧮 A left join: keeping the core outfit, adding a jacket that changes the tone.

✳️ An inner join: when the base and layer perfectly align, polished yet effortless.

🧤 A cross join: when pattern meets pattern, bold, unexpected, but still connected.

Every transformation kept the base intact — proof that when your foundation is strong, creativity has infinite combinations.

Step 4: Optimize — Maintain and Measure

A capsule wardrobe isn’t just a one-time cleanup. It’s database maintenance.

I’ve learned it’s important to:

• Revisit each season (scheduled refresh).

• Add only what complements what I already own (controlled data inputs).

• Retire pieces that no longer align with my lifestyle (data depreciation).

And just like a well-designed data model, it’s made my life more efficient.

Capsule Wardrobe: The Loaded Dataset

(Every ETL project needs a final dataset — this one just happens to hang on a clothing rack.)

After the extract phase, here’s what officially made it into my Fall/Winter 2025 Capsule Wardrobe — the timeless pieces I’ve collected, loved, and worn through the years. Each one feels intentional, classic, and true to my style.

The Final Pieces

Tops & Layers

  • 6 button-ups
  • 6 tops
  • 2 polos
  • 3 sweaters
  • 1 cardigan
  • 2 jackets

The “primary keys” — the foundation of every future outfit join.

Bottoms

  • 4 skirts
  • 4 pants
  • 4 pairs of denim

Most are neutral, but the occasional statement color (hello, red skirt) keeps things interesting.

Dresses

  • 3 classic dresses
  • 2 tunic shirt dresses

Functionality > Flash: These are versatile, comfortable, and can easily move from casual to polished.

Footwear

  • 2 pairs of boots
  • 3 pairs of clogs (yes… I love clogs)
  • 1 pair of flats
  • 1 pair of sneakers

Balanced Load: Equal parts practicality and personality — because good footwear is basically good indexing.

Color Story: Classic Meets Creative

As I stepped back and looked at my final capsule, the color story felt like a reflection of me — grounded yet expressive. The foundation is built on soft neutrals: beige, cream, tan, and black — the kind of timeless tones that quietly do the heavy lifting, much like clean, reliable data. But then there are the pops of red and green, my visual outliers that make the dataset interesting. They’re bold, unapologetic, and full of life.

The mix of patterns — from paisley to gingham to classic stripes and leopard — adds just the right level of texture and personality. Together, it’s the perfect balance between classic and fun, a wardrobe that feels both analytical and artistic.

wrapping up

What surprised me most wasn’t how many clothes I had but how freeing it felt to simplify.

Decluttering my closet mirrored the process of decluttering my life, my workspace, and even my creative energy.

When we clear out what’s no longer serving us, whether it’s old data, cluttered dashboards, or unworn clothes, we make room for clarity.

For intention.

For transformation.

Because sometimes, the best insights don’t come from adding more, they come from refining what’s already there.

And yet, there’s one category I refuse to normalize or declutter, my handbags and purses.

They’re my beautiful exceptions to the rule and my little “data anomalies.” Each one carries a story, a moment, or a milestone.

If the rest of my closet is a clean, optimized dataset, my handbag collection is the carefully preserved archive, the one I’ll never delete.

But let’s be real… I’m probably gonna fail hard at this capsule thing because it’s too hard when you love pretty things 😭

Until next time,

Ardonna •ᴗ•

author’s note

Ardonna Cardines Avatar

Hi, I’m Ardonna Cardines — a data analyst and creator of Mercury Musings, where business meets imagination through data. I love blending analytics, design, and storytelling to make learning data modeling and visualization approachable — one creative dataset at a time.

If you’ve enjoyed this post, I’d love for you to follow along.
You can subscribe to my blog for new tips + tutorials, creative datasets, and behind-the-scenes projects — or connect with me on LinkedIn where I share updates and learning resources for analysts and data storytellers.

Categories
Analysis Analytics Business Uncategorized

I Created a Harry Potter Dataset…

and it’s pretty magical.

I didn’t build this just for fun, I grounded it in real analyst experience by designing it after tables I’ve used in the real world.

Customers, products, campaigns, sales, suppliers, and transactions are the everyday things an analyst might pull for an ad hoc report, campaign ROI analysis, or segmentation tests. And these are all of the things you can find in my dataset.

In the world of data, beginners are taught coding and analytical skills using datasets like “AdventureWorks” (for Microsoft SQL Server) or the “Titanic Survival Dataset” (for Python).

Here’s a table I made that shows how my dataset stacks up against the classics.

Rethinking Analyst Learning Resources

Data analytics is the driving force behind innovation. Yet while most resources focus on teaching analysts coding and technical skills, very few focus on innovating the resources themselves. At the same time, more people than ever are entering this field, eager to learn SQL, Python, and the tools that power today’s businesses.

💡Confession Time: I failed my first Python programming class. Even to this day, Python still scares me — and I’m still learning new things. It takes persistence, practice, and patience to get comfortable with writing code, debugging errors, and thinking like a programmer.

With that in mind, it’s time we start rethinking the kinds of practice datasets and learning materials we give analysts. These are the future professionals who will one day drive the very innovation that companies value and emphasize in their core values today. If learning resources are stale, repetitive, or disconnected from real-world scenarios, we’re holding them back before they even start.

We also talk so much about storytelling in analytics — but storytelling doesn’t have to start only at the presentation stage. What better way to help analysts learn than through a dataset where they can create stories while they query? By practicing in a world like the Harry Potter broomstick store, analysts can bring imaginative thinking into their training.

That means when they step into the real world, they’ll walk in with more than just technical skills, they’ll also have creativity skills. And those two together are what turn raw data into insights that inspire stakeholders and drive real impact.

That’s why I built this dataset: to prove that resources can be both realistic and imaginative, grounding analysts in real business logic while sparking the kind of storytelling and creativity that will carry through their careers.

And for me, this is a fulfilling beginning to a journey that I’m happy I finally decided to take.

Check out the full dataset here:

Mercury’s Broomstick Emporium

what’s in the dataset

🧙 customers.csv

  • Columns: customer_id, first_name, last_name, date_of_birth, house, occupation, year
  • Includes both students and adults. Occupations range from “Student” to roles like Auror, Healer, or Ministry of Magic employee. Adults are identified as those born before 01/01/2008.

🛒 sales.csv

  • Columns: sale_id, date, payment type , campaign_id, customer_id
  • Each record is a transaction linked to a customer and (optionally) a campaign.

📦 sale_details.csv

  • Columns: detail_id, sale_id, product_id, quantity, galleons_spent
  • Line-item details that make the dataset realistic for SQL joins and sales analysis.

🛍️ products.csv

  • Columns: product_id, product_name, speed, control, price, supplier_id
  • Brooms, Quidditch wear, potions, and wizard supplements—everything a witch or wizard might buy.

🏪 suppliers.csv

  • Columns: supplier_id, supplier_name, reliability, lead_time
  • Features Nimbus Racing Broom Co., Cleansweep Broom Co., and more wizarding suppliers.

🎯 campaigns.csv

  • Columns: campaign_id, name, start_date, end_date, spend
  • Hogwarts-style promotions and discounts—back-to-school specials, Quidditch sales, holiday bundles.

🎯 campaigns_products_with_id.csv

  • Columns: campaign_product_id, campaign_id, product_id
  • Bridge table connecting campaigns to multiple products (many-to-many).

LEARN TO DO A MARKET BASKET ANALYSIS IN PYTHON

If you’re interested in exploring the dataset and want to learn something new, I’ve created two notebooks on Kaggle that walk you through a market basket analysis.

Lesson 1: Explore here.

Lesson 2: Explore here.

Each notebook contains detailed steps and explains the “why” behind everything – see below. I wrote this with the intention that it can aid analysts and non-analysts (like product managers, business managers, etc.) because a market basket analysis is one of the most powerful tools in uncovering customer or purchasing behavior!

⚡ Now tell me, would you explore this dataset?

Until next time,

Ardonna •ᴗ•

author’s note

Ardonna Cardines Avatar

Hi, I’m Ardonna Cardines — a data analyst and creator of Mercury Musings, where business meets imagination through data. I love blending analytics, design, and storytelling to make learning data modeling and visualization approachable — one creative dataset at a time.

If you’ve enjoyed this post, I’d love for you to follow along.
You can subscribe to my blog for new tips + tutorials, creative datasets, and behind-the-scenes projects — or connect with me on LinkedIn where I share updates and learning resources for analysts and data storytellers.

Categories
Analysis Analytics Business Tips

Tutorial: SQL Joins Explained Through Tarot

I re-imagined SQL Joins into tarot cards! If you missed the original card deck reveal see [here].

This is part two of my little project and we dig a little more into the different joins.

DISCLAIMER 🔮

Not every database system supports all join types the same way. Think of them like tarot archetypes: some are the classic cards you’ll always pull, some are special but still standard, and a few are rare but powerful when you know them.

In this post, I’ll be discussing the nine different joins. Yes, nine.

THE CORE 4

Inner Join, Left Join, Right Join, and Outer Join

These are the backbone of SQL joins — supported across SQL Server, MySQL, PostgreSQL, Oracle, and almost every bootcamp, tutorial, or textbook.

Check out one of my very first blog posts on demystifying the core 4 [HERE].

THE STANDARD BUT LESS COMMONLY USED DUO

CROSS JOIN AND SELF JOIN

CROSS JOIN → Supported everywhere, but not used as often since it returns every possible pairing (easy to explode row counts).

If you ever find yourself in a situation where you weren’t expecting so many extra rows, make sure you’ve specified the table using ON.

If you neglect this, SQL won’t know what table you’re referring to and will default to a CROSS JOIN, hence the extra five million rows you didn’t ask for.

This is especially important in real business environments where there are A LOT of dimension tables within the database.

CROSS JOINS are a common pitfall. In some dialects (MySQL, SQLite), if you write FROM A, B without a join condition, it behaves like a CROSS JOIN.

In others (like PostgreSQL), you’re required to use CROSS JOIN explicitly, or else FROM A, B is just shorthand for the same thing.

Because it creates a Cartesian product, it’s supported everywhere but usually taught as a “special case” rather than part of the “core 4” joins.

SELF JOIN → Also standard, but not always taught early. It’s just a table joining to itself, like looking in a mirror. My first encounter in the real world with SELF JOIN was confusing because I thought “Why would you join a table to itself?” but it made so much sense after writing out the complex queries to pull the exact data I needed.

Eventually, you will come across a data pull or ad-hoc request where you’ll need to do a SELF JOIN to pull specific data. This is especially common in business entities where there’s a hierarchical structure or parent-child relationship stored in a single table.

For example, one of the most likely places you’ll find yourself using a SELF JOIN is when dealing with customer accounts in an ERP. This is where I encountered and discovered this join type.

In ERPs or CRMs, you often have one customer account with multiple relationships:

  • Customer ID = the main account
  • Bill-To ID = where invoices are sent
  • Ship-To ID = where products are delivered

All three may live in the same Customers table.

A self join lets you pull the parent customer name alongside the child account details, so you can distinguish billing entities from shipping ones.

the red flag

natural join

NATURAL JOIN → Supported in Oracle, PostgreSQL, and MySQL, but not SQL Server (SSMS).

It auto-matches columns with the same name — which feels magical but can be dangerous in production since it might pull in new columns you didn’t expect. That’s why it’s rarely used or covered in tutorials.

Like, sorry Mr. Houdini but you’re kind of a red flag.

If someone later adds a new column with the same name, your join condition changes without you realizing it.

That’s why NATURAL JOIN is rarely taught or used day-to-day. Still, it’s a valuable teaching tool to understand SQL’s quirks — the “Magician’s trick.”

Side note: this is how I feel about natural joins…

THE POWERFUL JOINS YOU PROBABLY DIDN’T REALIZE WERE JOINS…

semi join and anti join

SEMI JOIN → Returns rows that exist in another table, without bringing back details.

ANTI JOIN → Returns rows that don’t exist in another table.

Not every SQL dialect has SEMI or ANTI as direct keywords.

Most SQL users learn EXISTS / NOT EXISTS and IN / NOT IN as subquery filters, not as “join types.” This is how I learned. But if you dig deeper, EXISTS, NOT EXISTS, IN, and NOT IN are ways to implement SEMI JOIN and ANTI JOIN logic.

In our tarot deck, I call them the “Rare Cards” because they’re not always explicit supported like they are in PostgreSQL or Oracle.

For most of us junior or mid-level SQL Server/MySQL folks, we see them as filters (because I still certainly do) but the more advanced SQL masters out there can probably agree that under the hood, EXISTS / NOT EXISTS and IN / NOT are ways to implement that semi and anti join logic.

why i included them all

Because why not?

Most people only ever see the “core 4” (and maybe cross join). But including Self, Natural, Semi, and Anti shows the full spread of how SQL handles matches and non-matches. That’s why my Tarot deck has 9 archetypes instead of 4 or 5.

And it makes this fresh new approach of learning joins WERK.

Now let’s start the tutorial!

tarot-themed sample tables

We’ll use a Tarot Shop as our example:

the tarot join spread

❤️ INNER JOIN – THE MATCHMAKER

USE CASE: Combine everything from both customers and readings.

SYNTAX

SELECT c.customer_name, r.spread_type, r.card_drawn
FROM Customers c
INNER JOIN Readings r
ON c.customer_id = r.customer_id;

RESULTS: Returns rows where both tables match (Luna and Sol with their readings).

🌱 LEFT JOIN – THE CARETAKER

USE CASE: List all customers, even if they never booked a reading.

SYNTAX

SELECT c.customer_name, r.spread_type, r.card_drawn
FROM Customers c
LEFT JOIN Readings r
ON c.customer_id = r.customer_id;

RESULTS: All customers show up; Astra and Terra appear with NULLs.

🏛️ RIGHT JOIN – THE ALLY

USE CASE: List all readings, even if no matching customer exists.

SYNTAX

SELECT c.customer_name, r.spread_type, r.card_drawn
FROM Customers c
RIGHT JOIN Readings r
ON c.customer_id = r.customer_id;

RESULTS: All readings appear; the Horoscope Reading has NULL for the customer.

🌍 FULL OUTER JOIN – THE WORLD

USE CASE: Combine everything from both customers and readings.

SYNTAX

SELECT c.customer_name, r.spread_type, r.card_drawn
FROM Customers c
FULL OUTER JOIN Readings r
ON c.customer_id = r.customer_id;

RESULTS: Shows Luna, Sol, Astra, Terra, and the null customer.

🎲 CROSS JOIN – THE TRICKSTER

USE CASE: Generate all possible customer–reading pairings.

SYNTAX

SELECT c.customer_name, r.spread_type, r.card_drawn
FROM Customers c
CROSS JOIN Readings r;

RESULTS: 16 rows (every customer with every reading).

🔦 SELF JOIN – THE REFLECTION

USE CASE: Compare customers to each other (e.g., same zodiac sign).

SYNTAX

SELECT a.customer_name AS customer1,
b.customer_name AS customer2,
a.zodiac_sign
FROM Customers a
JOIN Customers b
ON a.zodiac_sign = b.zodiac_sign
AND a.customer_id < b.customer_id;

RESULTS: Pairs of customers with matching zodiac signs.

✨ NATURAL JOIN – THE MAGICIAN

USE CASE: Automatically match tables by columns with the same name.

SYNTAX

SELECT customer_name, spread_type, card_drawn
FROM Customers
NATURAL JOIN Readings;

RESULTS: Same as INNER JOIN, but SQL chooses join columns automatically.

Why Developers Avoid NATURAL JOIN

While NATURAL JOIN feels like magic, many SQL developers avoid it in real-world projects. Here’s why:

  • Hidden behavior: It joins on all columns with the same name — sometimes more than you expect.
  • Future risk: If a new column is added later with the same name in both tables, your query’s behavior can silently change.
  • Portability: Not every SQL engine supports it (e.g., SQL Server SSMS does not).

NATURAL JOIN can feel magical, but in production it’s risky.

Production = real-world, live environments where:

  • Your SQL isn’t just practice anymore — it powers dashboards, apps, or business reports.
  • Mistakes can affect decision-making, break dashboards, or even crash systems.
  • Small schema changes (like someone adding a new column with the same name in both tables) can suddenly change results without anyone realizing it.

This is why it is often discouraged or rarely mentioned by most SQL instructors.

📜 SEMI JOIN – THE SEER

USE CASE: Find customers who have at least one reading (but don’t list the reading details).

SYNTAX

SELECT c.customer_name
FROM Customers c
WHERE EXISTS (
SELECT 1
FROM Readings r
WHERE c.customer_id = r.customer_id
);

RESULTS: Luna and Sol only.

💀 ANTI JOIN – THE HERMIT

USE CASE: Find customers who never had a reading.

SYNTAX

SELECT c.customer_name
FROM Customers c
WHERE NOT EXISTS (
SELECT 1
FROM Readings r
WHERE c.customer_id = r.customer_id
);

RESULTS: Astra and Terra only.

CLOSING THE SPREAD

Together, these 9 joins form the Tarot SQL Join Spread:

  • INNER JOIN → The Matchmaker (true matches)
  • LEFT JOIN → The Caretaker (include everyone left)
  • RIGHT JOIN → The Ally (include everyone right)
  • FULL OUTER JOIN → The World (wholeness)
  • CROSS JOIN → The Trickster (endless possibilities)
  • SELF JOIN → The Reflection (introspection)
  • NATURAL JOIN → The Magician (automatic shortcut)
  • SEMI JOIN → The Seer (exists, but hidden)
  • ANTI JOIN → The Hermit (exclude what doesn’t belong)

And there you have it — the complete Tarot SQL Join Spread.
From The Matchmaker (INNER JOIN) to The Hermit (ANTI JOIN), each card reveals a different way tables connect, overlap, or stand alone.

My hope is that next time you write a query, you’ll picture the card: Do I want true matches, inclusivity, solitude, or a little chaos? That little image might just help the SQL logic click faster.

I’d love to hear from you: Which join (or tarot card) do you use most in your daily work? Share in the comments, and let’s see which archetypes get drawn the most often.

Until next time,

Ardonna •ᴗ•

author’s note

Ardonna Cardines Avatar

Hi, I’m Ardonna Cardines — a data analyst and creator of Mercury Musings, where business meets imagination through data. I love blending analytics, design, and storytelling to make learning data modeling and visualization approachable — one creative dataset at a time.

If you’ve enjoyed this post, I’d love for you to follow along.
You can subscribe to my blog for new tips + tutorials, creative datasets, and behind-the-scenes projects — or connect with me on LinkedIn where I share updates and learning resources for analysts and data storytellers.

Categories
Analysis Analytics Business Mindfulness Notes Personality Psychology

SQL Joins… But Make Them Tarot

SQL joins are usually explained with Venn diagrams, charts, or code snippets. And then I had this pretty cool idea.

Because guess what… analysts have fun too and data doesn’t have to be boring *wink-wink*. So —

What if we gave sql joins mystical archetypes instead?

I designed a tarot-inspired deck where each join type becomes a card — complete with symbols, archetypes, and short fortunes.

I included all join types (Inner, Left, Right, Outer, Cross, Self, Natural, Semi, and Anti) to make the set feel more complete, even if most people only cover the most common ones.

DISCLAIMER 🔮

Not every database system supports all join types the same way. Think of them like tarot archetypes: some are the classic cards you’ll always pull, some are special but still standard, and a few are rare but powerful when you know them.

The Core 4 (the joins everyone learns first):

  • INNER JOIN
  • LEFT JOIN (LEFT OUTER)
  • RIGHT JOIN (RIGHT OUTER)
  • FULL OUTER JOIN
    These are the backbone of SQL joins — standard across SQL Server, MySQL, PostgreSQL, Oracle, and almost every tutorial or bootcamp.

Standard but less commonly used:

  • CROSS JOIN – Supported everywhere, but less commonly taught because it returns every possible pairing (which can blow up row counts fast).
  • SELF JOIN – Also standard, but often taught later. It’s just a table joining to itself, like looking in a mirror.

Natural Join (The Magician):

  • Supported in Oracle, PostgreSQL, and MySQL, but not SQL Server (SSMS).
  • It auto-matches columns with the same name — which feels magical ✨ but can be dangerous in production since it might pull in new columns you didn’t expect. That’s why it’s rarely used or covered in tutorials.

The “Rare Cards” (not always explicit keywords):

  • SEMI JOIN and ANTI JOIN
    • In PostgreSQL and Oracle, they exist as recognized operations.
    • In SQL Server and MySQL, they’re simulated with subqueries (EXISTS / NOT EXISTS or IN / NOT IN).

Why I included them all:
Most people only ever see the “core 4” (and maybe cross join). But including Self, Natural, Semi, and Anti shows the full spread of how SQL handles matches and non-matches. That’s why my Tarot deck has 9 archetypes instead of 4 or 5.

Are you ready?

The SQL Tarot Deck

  • Does the Matchmaker speak to you — are you drawn to perfect harmony?
  • Do you feel like the Caretaker — giving even when nothing comes back?
  • Are you the Ally today — steadfast, joining where you can?
  • Does the World call to you — a place where everyone belongs?
  • Are you in a Trickster mood — craving endless possibilities and a little chaos?
  • Do you feel like the Reflection — turning inward to uncover hidden patterns?
  • Is the Magician with you — where connections happen like magic?
  • Does the Hermit resonate — choosing solitude and independence?
  • Are you guided by the Seer — glimpsing just enough to know what’s there?

inner join – the matchmaker

Only rows that match on both sides are kept. Harmony, balance, and true pairs.

Left Join – The Caretaker

All rows from the left remain, even if the right gives nothing back. Loyalty and nurture.

right join – the ally

All rows from the right remain, with the left joining where it can. Steadfast support.

full outer join – the world

All rows from both tables are included. Matches and loners alike find a place in the whole.

cross join – the trickster

Every row pairs with every other. Endless possibilities, but beware of chaos.

SELF JOIN – THE MIRROR

A table reflects on itself, revealing hidden patterns within.

natural join – the magician

Automatically connects matching columns — a shortcut that feels like magic.

ANTI JOIN – THE HERMIT

Only rows without matches are shown. Solitude and independence define this path.

SEMI JOIN – THE SEER

Rows are revealed if they exist in another set, but details remain veiled.

pretty cool right?

WHAT’S NEXT

It doesn’t end here, my fellow analysts.

COMING NEXT

  • A full overview of all the joins and their syntax using Tarot inspired tables.
  • Which SQL Join Are You? — an interactive quiz to reveal your archetype.

AND AFTER THAT…

  • Tarot Reader in Python (with ASCII art cards)

So tell me… which SQL Tarot card speaks to you?

Until next time,

Ardonna •ᴗ•

author’s note

Ardonna Cardines Avatar

Hi, I’m Ardonna Cardines — a data analyst and creator of Mercury Musings, where business meets imagination through data. I love blending analytics, design, and storytelling to make learning data modeling and visualization approachable — one creative dataset at a time.

If you’ve enjoyed this post, I’d love for you to follow along.
You can subscribe to my blog for new tips + tutorials, creative datasets, and behind-the-scenes projects — or connect with me on LinkedIn where I share updates and learning resources for analysts and data storytellers.

Categories
Analysis Analytics Reflection Strategy

Why Croissants Are King: A Market Basket Analysis

Last week I wrapped up a small but insightful Market Basket Analysis project.

Working off of the bigger Coffee Sales Dashboard project I completed (see here), I decided to explore a bit more and do some data science *tings*.

My goal was to explore how products are frequently purchased together and uncover patterns that could inform cross-selling strategies or promotions.

At first, I kept running into issues with the Apriori algorithm. No matter how I tuned it, the model kept failing to generate meaningful rules. I was just like:

If you haven’t gone through the bang-your-head-on-the-desk rite of passage working in analytics or coding, then you need to share your superpower with world.

As the saying goes:

“80% of the job is debugging and cleaning data. The other 20% is explaining why it took 80% of the time.”

After digging deeper, I realized the problem: in the dataset, each transaction only had one item! It’s always the simplest thing that is often the problem, amirite?

Without multiple items per basket, there were no associations to discover and this proved to be a great learning moment. I went back, rebuilt the dataset so that baskets could contain multiple items, and reran the analysis.

Instead of scrapping the project, I realized it’s perfectly fine (and actually really useful) to simulate data. If you’ve never created a dataset before, fear not. I have a tutorial in mind for that!

By redesigning the dataset so baskets could include multiple items, I was able to validate the workflow, uncover meaningful associations (hello coffee + croissant), and learn just as much from the setup as from the results.

TAKEAWAY: It’s okay if the data isn’t “real.” Simulation is a powerful way to practice, experiment, and build intuition—especially when you’re learning or building portfolio projects.

If you’re interested in the technical nitty-gritty, check out my GitHub [HERE] where I have all the code and documentation for this mini-project.

Until next time,

Ardonna •ᴗ•

author’s note

Ardonna Cardines Avatar

Hi, I’m Ardonna Cardines — a data analyst and creator of Mercury Musings, where business meets imagination through data. I love blending analytics, design, and storytelling to make learning data modeling and visualization approachable — one creative dataset at a time.

If you’ve enjoyed this post, I’d love for you to follow along.
You can subscribe to my blog for new tips + tutorials, creative datasets, and behind-the-scenes projects — or connect with me on LinkedIn where I share updates and learning resources for analysts and data storytellers.

Categories
Analysis Analytics Business Decisions Reflection

Behind the Beans: The Process of Visualizing Coffee Sales

are you team starbucks or team dutch bros?

Either way, coffee isn’t just this magical concoction that many of us love and *ahem* can’t live without — it’s also a dataset waiting to be explored. For my latest project, I built a Coffee Shop Dashboard to dig into customer habits, product sales, and time-based trends.

At first, I planned to keep it simple: load a flat file I found on Kaggle and throw some visuals on a page using Power BI. But the deeper I got, the more I realized this was the perfect chance to practice building a proper data model. What started as a “quick dashboard” turned into a full star schema project. And honestly? That decision made all the difference.

So grab yourself a cuppa joe and as we sip *pinkies out*, I’ll quickly walk you through this project.

my process (with a twist)

1. Data Prep

I used a fictional dataset that came from Kaggle (link here) featuring a coffee shop called Daily Grind Coffee. When I first skimmed the columns, it had everything that I was looking for — a realistic transactional dataset similar to the ones I’ve worked with in the real world. But once I started building from the flat file, new ideas started firing in my head, and I realized I could show even more with the data than I originally planned.

2. From Flat File to Schema

Originally, I was going to connect the flat file directly into Power BI. Instead, I built out a star schema — one fact table (orders) connected to dimension tables (customers, products, dates). Knowing how to build a proper data model is essential for data analysts. A clean schema doesn’t just make dashboards easier to maintain — it makes analysis:

  • Scalable: Add new data without breaking your model.
  • Flexible: DAX measures are simpler and more powerful.
  • Efficient: Queries run faster and avoid messy workarounds.

Having this background is a huge advantage, and it makes me grateful for everything I learned in my database courses in my undergrad. Not every business analyst has formal training in data modeling — many focus on tools and reports without understanding the structure underneath. But knowing the foundation of databases changes the way you think about business questions. You stop just visualizing numbers and start structuring the data so every future question is easier (and faster) to answer.

3. Measures & Metrics

I created DAX measures like average order value, customer retention rate, retained customers, etc.

For example, see the graph below.

This shows the trend of customer retention rate this year. The highest retention rate was in March at 29.91%, which dipped in April and May. Which leads us to asking questions like:

  • Did customer retention rate drop due to the warmer climate?
  • What items were these customers buying (iced vs hot drinks)?
  • What strategies can we take to keep customers buying in April and May? Promotions? Campaigns?

4. design

I kept the dashboard clean and minimal, with aligned visuals, consistent fonts, and filters for exploration.

insights that jumped out

  • Croissants and muffins were the surprise stars.
  • Customers split into two groups: daily cappuccino loyalists vs weekly tea drinkers.
  • Weekends had a completely different sales rhythm than weekdays.

This reminded me that dashboards aren’t just for reporting KPIs — they spark questions you wouldn’t have thought to ask otherwise.

what i learned

  • A good schema pays off. That extra effort upfront gave me flexibility and saved time.
  • Measures > calculated columns. Cleaner, leaner, and easier to maintain.
  • Design is analysis. Layout, spacing, and color choices shape how insights are understood.

💡 CONFESSION TIME:
I’ll admit it — I’m way more comfortable with calculated columns than measures. They feel familiar and straightforward, while DAX has been a tougher learning curve for me. Honestly, I’m still figuring it out.

But that’s part of why I write these reflections. My audience isn’t a room full of experts — to be honest, I’m writing for other analysts like me, all in different parts of their journey in data analytics. And sometimes the most helpful thing isn’t pretending you know everything, but being transparent about where you’re growing.

I’ve learned that while calculated columns get the job done, measures are worth the effort — they make dashboards cleaner, leaner, and much easier to maintain in the long run.

next steps

I’d love to add:

  • Customer Segmentation (Daily vs Weekly Buyers).
  • Forecasting so the dashboard can move from reporting the past to predicting the future.

Curious about the full project or want the technical nitty-gritty? You can check out the dataset, Power BI file, and documentation on my GitHub repo.

wrapping up: dashboard screenshots

☕ This dashboard gave me a great starting point, but it also got me wondering: what products do customers actually buy together? That’s where market basket analysis comes in. Stay tuned for my next post where I dig into those patterns.

And don’t worry, I won’t end this post without showing you what the dashboards look like!

Voila – a Coffee Sales Dashboard from a Virgo Mind.

Until next time,

Ardonna •ᴗ•

author’s note

Ardonna Cardines Avatar

Hi, I’m Ardonna Cardines — a data analyst and creator of Mercury Musings, where business meets imagination through data. I love blending analytics, design, and storytelling to make learning data modeling and visualization approachable — one creative dataset at a time.

If you’ve enjoyed this post, I’d love for you to follow along.
You can subscribe to my blog for new tips + tutorials, creative datasets, and behind-the-scenes projects — or connect with me on LinkedIn where I share updates and learning resources for analysts and data storytellers.