Chapter 16: BigQuery
16.16. Store Inventory Capacity Outlook: BigQuery and Looker Studio Case Study
In this advanced lesson, we'll explore a case study where a business uses Looker Studio and BigQuery to optimize their marketing efforts by analyzing store inventory capacity.
The Business Context
Our client operates around 30 stores across the United States. Each store has a specific capacity for the number of orders they can fulfill per day. The primary goal of their marketing strategy is to fill these capacities.
To achieve this, they need an efficient way to allocate advertising resources based on each store's needs. That's where Looker Studio and BigQuery come into play.
Custom Visualization for Store Capacity
Using Looker Studio, we've created a custom visualization that shows the remaining daily capacity for each store over the next eight weeks. This allows our client to see which stores still have unfulfilled capacity and adjust their ad budget accordingly.
For example, if a store has fulfilled its entire order capacity for the week, it doesn't require any more advertisements. This information allows our client to reallocate ad resources to other stores that need more attention.
Integrating Data with BigQuery and Looker Studio
To bring this data into Looker Studio, we use an SQL query that is dynamically executed from Looker Studio over the BigQuery instance. We have two main sources of data: inventory available in different stores and the number of orders placed for each day to be fulfilled from web data.
By merging these two datasets together and running an SQL query against them, we can provide our custom visualization with all the necessary data it needs to show an overview of what's happening in our client's business operations.
This enables our client to make informed decisions about where they should allocate their advertising budget, ultimately helping them optimize their marketing efforts based on store inventory capacities.
📩 Receive my weekly Looker Studio tips
🖇 Connect with me on LinkedIn