Extract insights from Shopify using PyAirbyte

# Add virtual environment support for running in Google Colab
!apt-get install -qq python3.10-venv

# Install PyAirbyte
%pip install --quiet airbyte

import airbyte as ab
from google.colab import userdata

# Create and configure the source connector:
source = ab.get_source(
    "source-shopify",
    install_if_missing=True,
    config={
        "shop": userdata.get("SHOP"),
        "credentials": {
            "auth_method": "api_password",
            "api_password": userdata.get("SHOP_API_PASSWORD")
        }
    }
)

# Verify the config and creds by running `check()`:
source.check()

Installing 'source-shopify' into virtual environment '/content/.venv-source-shopify'.
Running 'pip install airbyte-source-shopify'...

Connector 'source-shopify' installed successfully!
For more information, see the source-shopify documentation:
https://docs.airbyte.com/integrations/sources/shopify#reference

Connection check succeeded for `source-shopify`.

# List the available streams available for the Shopify source
source.get_available_streams()

# Select the streams we are interested in loading to cache
source.select_streams(["products", "product_variants", "collections", "customers"])

# Read into DuckDB local default cache
cache = ab.get_default_cache()
result = source.read(cache=cache)

# Read from the cache into a pandas Dataframe:
products = cache["products"].to_pandas()
product_variants = cache["product_variants"].to_pandas()
collections = cache["collections"].to_pandas()
customers = cache["customers"].to_pandas()

import pandas as pd
import matplotlib.pyplot as plt

# Product Type Analysis
product_type_counts = products['product_type'].value_counts()
product_type_counts.plot(kind='bar')
plt.title('Product Type Distribution')
plt.xlabel('Product Type')
plt.ylabel('Count')
plt.show()

# Variant Price Range Analysis
product_variants['price'] = pd.to_numeric(product_variants['price'], errors='coerce')
product_variants['price'].hist(bins=20)
plt.title('Product Variant Price Distribution')
plt.xlabel('Price')
plt.ylabel('Number of Variants')
plt.show()

# Inventory Level Analysis
product_variants['inventory_quantity'] = pd.to_numeric(product_variants['inventory_quantity'], errors='coerce')
product_variants['inventory_quantity'].hist(bins=20)
plt.title('Inventory Level Distribution')
plt.xlabel('Inventory Quantity')
plt.ylabel('Number of Variants')
plt.show()

import json

# Parse JSON
def parse_json(x):
    try:
        return json.loads(x.replace("'", "\""))
    except:
        return {}

# Apply the function to unpack JSON content
customers['email_marketing_consent'] = customers['email_marketing_consent'].apply(parse_json)

# Extract relevant fields
customers['consent_opt_in_level'] = customers['email_marketing_consent'].apply(lambda x: x.get('opt_in_level', None))
customers['consent_state'] = customers['email_marketing_consent'].apply(lambda x: x.get('state', None))
     

# Analyze the distribution of opt-in levels
opt_in_level_counts = customers['consent_opt_in_level'].value_counts()

# Analyze the distribution of consent states
consent_state_counts = customers['consent_state'].value_counts()

# Opt-in level distribution
opt_in_level_counts.plot(kind='bar')
plt.title('Distribution of Consent Opt-In Levels')
plt.xlabel('Opt-In Level')
plt.ylabel('Number of Customers')
plt.show()

# Consent state distribution
consent_state_counts.plot(kind='bar', color='orange')
plt.title('Distribution of Consent States')
plt.xlabel('Consent State')
plt.ylabel('Number of Customers')
plt.show()

collections['products_count'] = pd.to_numeric(collections['products_count'], errors='coerce')
collections.groupby('title')['products_count'].sum().plot(kind='bar')
plt.title('Product Count in Each Collection')
plt.xlabel('Collection')
plt.ylabel('Total Products')
plt.show()

# Product Variants Inventory Analysis
product_variants.groupby('product_id')['inventory_quantity'].sum().plot(kind='bar')
plt.title('Total Inventory Quantity by Product')
plt.xlabel('Product ID')
plt.ylabel('Total Inventory Quantity')
plt.show()