Pivoting Pet Data - Big Query

What did I use?

1. Common Table Expression (CTE):
I began by creating a summary table that groups data by category_name and a new derived field, segment_name. This segment was determined using a simple CASE logic that checked if the product name contained keywords like “Dog” or “Cat”. Anything that didn’t match was labeled “Other”.

2. Aggregation:
Inside the CTE, I calculated:

• The average rating for each group

• The total number of products in each group

3. Pivoting:
Using BigQuery’s PIVOT operator, I transformed the data from a long format to a wide format. Each segment (Dog, Cat, Other) became its own set of columns, showing:

• avg_rating

• product_count

The final result is a clean, easy-to-read table that makes comparisons across segments much more intuitive—perfect for dashboards or reporting.

In this project, I worked with a fictional dataset from a pet product store called wisdom_pets.products. The objective was to analyse product performance by category—such as Food, Toys, or Accessories—and further break it down into three simple segments based on product names:

• Products that mention “Dog”

• Products that mention “Cat”

• Everything else falls into an “Other” segment

For each combination of category and segment, I wanted to uncover two key metrics:

• The number of products in that group

• The average product rating

Here is my code: