Skip to main content
DataDoe HubHub
(opens in a new tab)
ChatGPT (opens in a new tab)
Claude (opens in a new tab)

Using Python Jupyter with BigQuery

Use a Jupyter notebook to explore and chart your DataDoe linked dataset in BigQuery.

Before you start

  • Connect to BigQuery and subscribe to the DataDoe listing in your Google Cloud project
  • Set up Google Cloud Application Default Credentials for an account that can query the linked dataset
  • Install Python 3.10 or later

The examples below use an Integrated data table. If you subscribed to Raw data, choose a table and its columns from the current DataDoe data scheme (opens in a new tab) instead.

Install notebook packages

Create a directory and install Jupyter and the BigQuery Python client:

bash
1mkdir my-datadoe-analysis && cd my-datadoe-analysis
2pip install jupyter google-cloud-bigquery pandas db-dtypes matplotlib

Start Jupyter

Run jupyter notebook, then create a notebook with a Python 3 kernel.

Connect to your linked dataset

In the first cell, enter the project, linked dataset name, and region you chose when subscribing. The linked dataset may not have DataDoe's _integrated suffix.

python
1from google.cloud import bigquery
2
3project = "YOUR_GOOGLE_CLOUD_PROJECT_ID"
4dataset = "YOUR_LINKED_DATASET_NAME"
5location = "us-east4"  # Use your linked dataset's region if different.
6
7client = bigquery.Client(project=project)
8print([table.table_id for table in client.list_tables(f"{project}.{dataset}")])

You should see the tables shared by DataDoe. The available tables depend on your DataDoe connections.

Run a test query

This query counts order line items by purchase date for the last 30 days. It uses the current date column in amazon_order_items_with_cogs.

python
1query = f"""
2SELECT date, COUNT(*) AS order_line_items
3FROM `{project}.{dataset}.amazon_order_items_with_cogs`
4WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
5GROUP BY date
6ORDER BY date
7"""
8
9daily_orders = client.query(query, location=location).to_dataframe(
10    create_bqstorage_client=False
11)
12print(daily_orders.head())
13daily_orders.to_csv("order_line_items_last_30d.csv", index=False)

If the query cannot find the table, check that you subscribed to Integrated data and that the table is available for your connections.

Find your top products

This query groups order line items by marketplace and child ASIN. It ranks products by units purchased, so it does not add amounts in different currencies.

python
1query = f"""
2SELECT
3  marketplace_id,
4  child_asin,
5  ANY_VALUE(product_name) AS product_name,
6  SUM(quantity) AS units_sold
7FROM `{project}.{dataset}.amazon_order_items_with_cogs`
8WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
9  AND child_asin IS NOT NULL
10GROUP BY marketplace_id, child_asin
11ORDER BY units_sold DESC
12LIMIT 20
13"""
14
15top_products = client.query(query, location=location).to_dataframe(
16    create_bqstorage_client=False
17)
18print(top_products)
19top_products.to_csv("top_products.csv", index=False)

Chart the results

Use Matplotlib to chart the five products with the most units purchased:

python
1import matplotlib.pyplot as plt
2
3top_five = top_products.head(5)
4plt.bar(top_five["child_asin"], top_five["units_sold"])
5plt.xlabel("Child ASIN")
6plt.ylabel("Units purchased")
7plt.title("Top 5 products by units purchased")
8plt.show()

For other measures or joins, check the DataDoe data scheme (opens in a new tab) before writing a query. Table grains and date fields differ.

Related guides