Brand_Sustainability_Data_Fetcher.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true,
      "authorship_tag": "ABX9TyO5+L0xbFdJfGa4L9ySS1+0",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/scisley/brand-sustainability-data-fetcher/blob/main/Brand_Sustainability_Data_Fetcher.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Overview\n",
        "\n",
        "Hi folks! 👋\n",
        "\n",
        "This notebook includes all the code you need to use large language models (LLMs - like those from OpenAI) to automatically find, process, and summarize the self-reported sustainability information for any list of brands. As a demonstration, this notebook does that for the **1,000 most widely recognized brands** in the US.\n",
        "\n",
        "And remember, this is brand *self-report* so it's going to sound very positive. It's the \"rah, rah, look how awesome we are!\" version of a company's sustainability efforts. This is still useful to know. What I'm trying to say is, please don't complain to me about greenwashing. If enough people ask, maybe I'll generate the \"what others say about a brand's sustainability efforts\" and then compare and contrast them. But that project is for another day...\n",
        "\n",
        "Quick background on myself. My name is Steve Isley ([LinkedIn](https://www.linkedin.com/in/stevecisley/)) and I'm a little obsessed with sustainable consumption. I spent 3 years at [NREL](https://www.nrel.gov/) as a behavioral scientist, 5 years at [Amazon](https://sustainability.aboutamazon.com/) leading sustainable customer research, and for the last two years I've been building a sustainable shopping experience called [CountOn](https://joincounton.com/), which is now a side project as I look for regular work.\n",
        "\n",
        "For CountOn, I've gotten deep into applied generative AI. And by \"applied\" I mean I'm not building new LLMs, rather I'm using existing LLMs to solve hard problems. It occurred to me recently that finding self-reported sustainability information about brands was one of those problems that hundreds, maybe thousands, of sustainable shopping websites have had to deal with over the years. With generative AI, we can now automate that work!\n",
        "\n",
        "The code is provided free of charge and with no guarantee of accuracy. I've evaluated a few results by hand and think they're pretty good, but I haven't reviewed nearly enough to make confident statements about accuracy. Use the data at your own risk. But, even if the data aren't high enough quality for you, there are lots of ways to improve the code (some of which I've documented) and this should give you a good starting point for your own data gathering project.\n",
        "\n",
        "### Links\n",
        "\n",
        "* [GitHub repo](https://github.com/scisley/brand-sustainability-data-fetcher): You can download a csv file of the outputs.\n",
        "* [Results viewer](https://scisley.github.io/brand-sustainability-data-fetcher/): You can search and view the results for all 1,000 brands\n",
        "\n",
        "### Cost\n",
        "\n",
        "Using AI is an order of magnitude faster and cheaper than the previous approach. I've done this type of manual work before, and I think a conservative estimate is 10 minutes of work per brand you want to evaluate. You have to find the right pages, take notes on what the company says, then produce a well-written summary. So that's 1,000*10/60 = 160.7 hours, that's over four weeks of work.\n",
        "\n",
        "I carefully tracked my time on this project. I came in at just under **12 hours** to write all the code below. The time needed to analyze a new website is on the order of seconds.\n",
        "\n",
        "In terms of variable costs, let's assume &dollar;15/hr for the manual process. That's &dollar;2.50 per brand. Here are the variable costs for this automated approach:\n",
        "\n",
        "* **Search API**: &dollar;4 per 1,000 searches, I did 1k searches, so &dollar;4.\n",
        "* **Crawlbase**: &dollar;0.006 per page, I scraped 1,885, so &dollar;11.31\n",
        "* **OpenAI LLM fees**: &dollar;60.30 (includes all usage, even debugging, and most of this is GPT-4, OpenAI's most expensive model)\n",
        "\n",
        "Total: **&dollar;75.61** or &dollar;0.076 per brand, thats 30 times cheaper than the manual approach.\n",
        "\n",
        "# Outline\n",
        "\n",
        "Using the latest AI orchestration tools makes the code remarkably simple. I'm using [LangChain](https://python.langchain.com/docs/get_started/introduction) here and highly recommend it, but others love [LlamaIndex](https://www.llamaindex.ai/). Here are the high level steps involved.\n",
        "\n",
        "**Step 1**: Find a list of brands you want to analyze. This can be surprisingly hard. I spent about an hour just doing this.\n",
        "\n",
        "**Step 2**: For each brand, use a Google search automation tool to return the results for \"*{brand name} sustainability*\"\n",
        "\n",
        "**Step 3**: For each candidate Google search result, run it through an LLM to decide if it's relevant or not. We are looking for pages owned by the brand itself, not what somebody else has said about the brand (that's a very valid but totally different dataset).\n",
        "\n",
        "**Step 4**: For each relevant URL, fetch the page HTML and convert it something nicer, in our case, markdown.\n",
        "\n",
        "**Step 5**: Create summaries by feeding the markdown for each brand into an LLM along with a prompt telling the LLM what to produce.\n",
        "\n",
        "**Step 6**: [Profit](https://en.wikipedia.org/wiki/Gnomes_(South_Park).\n",
        "\n"
      ],
      "metadata": {
        "id": "PlGLWMMJINnY"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Environment setup\n",
        "\n",
        "Do whatever you need to load environmental variables into your system. The key environment variables are:\n",
        "\n",
        "* OPENAI_API_KEY: Required for using OpenAI's LLMs. Get this from [OpenAI](https://platform.openai.com/api-keys)\n",
        "* SEARCHAPI_API_KEY: Required for fetching Google search results. Get from [SearchApi](https://www.searchapi.io/)\n",
        "* LANGCHAIN_API_KEY: This is optional, but I highly recommend it. It helps a ton with debugging. Get this from [LangChain](https://smith.langchain.com/).\n",
        "* CRAWLBASE_JS_API_KEY: Required for scraping websites. Get this from [Crawlbase](https://crawlbase.com/)"
      ],
      "metadata": {
        "id": "YnmiZIImulWQ"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "P8LwWO5wgmjR"
      },
      "outputs": [],
      "source": [
        "!pip3 install langchain langchain-community langchain-core langsmith \\\n",
        "    openai python-dotenv tiktoken pydantic==1.10.13 \\\n",
        "    beautifulsoup4 lxml python-slugify html2text crawlbase"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from dotenv import load_dotenv, dotenv_values\n",
        "from google.colab import drive\n",
        "from IPython.display import Markdown, display\n",
        "import re\n",
        "import pprint\n",
        "import os\n",
        "import json\n",
        "\n",
        "drive.mount('/content/drive/')\n",
        "\n",
        "# UPDATE THIS TO WORK ON YOUR SYSTEM.\n",
        "!cp /content/drive/MyDrive/Colab-Notebooks/prototypes/dotenv .env\n",
        "load_dotenv(override=True)\n",
        "\n",
        "# Optional, but LangSmith is *really* handy for debugging. Highly recommend.\n",
        "os.environ['LANGCHAIN_PROJECT'] = 'brand-data-fetcher'\n",
        "os.environ['LANGCHAIN_TRACING_V2'] = 'true'\n",
        "os.environ['LANGCHAIN_ENDPOINT']=\"https://api.smith.langchain.com\""
      ],
      "metadata": {
        "id": "MurVJnZsg6Jb"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Step 1: List of brands to analyze\n",
        "\n",
        "I searched around for a good list of brands. This was suprisingly hard. I wanted a list of well known consumer brands, as opposed to any B2B brand or obscure brands nobody has ever heard of.\n",
        "\n",
        "I eventually landed on YouGov's [\"Most Famouse Brands\"](https://today.yougov.com/ratings/consumer/fame/brands/all) list. This looks like exactly what I want, but there wasn't a downloadable option. So, I just kept going in the infinite scroll until the top 1,000 brands were displayed. Then, I opened my dev console and copied the HTML for the list into a file. The code below imports that HTML and uses Beautiful Soup (a popular Python library for manipulating HTML) to extract the relevant information.\n",
        "\n",
        "The very good people at YouGov even have an [FAQ](https://today.yougov.com/about/ratings-faq) saying folks can use the data on their website - thanks YouGov!"
      ],
      "metadata": {
        "id": "d5ZQ0s2eslzw"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from bs4 import BeautifulSoup\n",
        "root_path = '/content/drive/MyDrive/Colab-Notebooks/prototypes/brand-data-fetcher'\n",
        "file_path = f'{root_path}/data/yougov-brands.txt'\n",
        "with open(file_path, 'r', encoding='utf-8') as file:\n",
        "    raw_data = file.read()\n",
        "\n",
        "soup = BeautifulSoup(raw_data, 'lxml')\n",
        "\n",
        "# List to store extracted data\n",
        "data = []\n",
        "\n",
        "for li in soup.find_all('li', class_='ng-star-inserted'):\n",
        "    # Finding brand name\n",
        "    brand = li.find('img', class_='ng-star-inserted')['alt'] if li.find('img', class_='ng-star-inserted') else 'No Brand'\n",
        "\n",
        "    # Finding fame and popularity percentages\n",
        "    percentages = li.find_all('span', class_='rankings-item-active') + li.find_all('span', class_='compact')\n",
        "    fame = percentages[0].text.strip() if len(percentages) > 0 else 'No Fame'\n",
        "    popularity = percentages[1].text.strip() if len(percentages) > 1 else 'No Popularity'\n",
        "\n",
        "    data.append({\"brand\": brand, \"fame\": fame, \"popularity\": popularity})\n",
        "\n",
        "# Sample output\n",
        "pprint.pp(data[0:10])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "N6LmiyOooRR-",
        "outputId": "1aea0242-7edc-40db-8481-29ede0277065"
      },
      "execution_count": 86,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[{'brand': \"McDonald's\", 'fame': '99%', 'popularity': '63%'},\n",
            " {'brand': 'Amazon', 'fame': '99%', 'popularity': '76%'},\n",
            " {'brand': 'Ford', 'fame': '99%', 'popularity': '62%'},\n",
            " {'brand': 'Taco Bell', 'fame': '99%', 'popularity': '67%'},\n",
            " {'brand': 'UPS', 'fame': '99%', 'popularity': '77%'},\n",
            " {'brand': \"M&M's\", 'fame': '99%', 'popularity': '85%'},\n",
            " {'brand': 'KFC', 'fame': '99%', 'popularity': '67%'},\n",
            " {'brand': 'Adidas', 'fame': '99%', 'popularity': '72%'},\n",
            " {'brand': 'PayPal', 'fame': '98%', 'popularity': '67%'},\n",
            " {'brand': 'Oreo Cookies', 'fame': '98%', 'popularity': '80%'}]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Step 2: Google search automation\n",
        "\n",
        "Finding the homepage for a brand is (at least used to be) a common mechanical turk task. This can now be automated pretty easily. In this case, we're not looking for the homepage, but rather pages about sustainability owned by the brand itself. The goal is to collect self reported information.\n",
        "\n",
        "Google doesn't provide an automated way to retrieve their search results, but about a billion independent companies have sprung up to provide this service. I've used [SearchApi](https://www.searchapi.io/) for this and I've been very happy with the service. The costs are trivial. Never build yourself what you can pay peanuts for.\n",
        "\n",
        "### Ways to make this better\n",
        "\n",
        "The code just does a Google search for the brand name followed by the word \"sustainability\". This works fine most of the time, but it gets tripped up when you have a brand name that is also a common word (though even then it often works because the word \"sustainability\" isn't likely to follow that word in other contexts). For example, the top Google search results for the sportwear brand \"Columbia\" is about Columbia University. However, \"Milwaukee\" works ok as the second search result is about the tool company (instead of the city).\n",
        "\n",
        "This process also struggles with brands that are owned by a parent company and the parent company has sustainability information. For example, \"Oreo Cookies\" is owned by Mondelez International. A google Search for [\"Oreo cookies sustainability\"](https://www.google.com/search?q=Oreo+cookies+sustainability&rlz=1C5CHFA_enUS919US919&oq=Oreo+cookies+sustainability&gs_lcrp=EgZjaHJvbWUyBggAEEUYOdIBCDQ1NThqMGo3qAIAsAIA&sourceid=chrome&ie=UTF-8) does return a Mondelez link, but it's not specific to Oreo cookies and the next step actually filters it out because the LLM doesn't think it's related to Oreos."
      ],
      "metadata": {
        "id": "iy3XV3g5sh6S"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from langchain_community.utilities import SearchApiAPIWrapper\n",
        "from requests.exceptions import RequestException\n",
        "\n",
        "def fetch_via_serp(query):\n",
        "    # Example query: f\"site:www.ewg.org/skindeep/ingredients citric acid\"\n",
        "    try:\n",
        "        # See https://www.searchapi.io/docs/google\n",
        "        print(f\"Using SearchApi with query: {query}\")\n",
        "        search = SearchApiAPIWrapper()\n",
        "        results = search.results(query)\n",
        "        if \"organic_results\" in results and results[\"organic_results\"]:\n",
        "            return results\n",
        "        else:\n",
        "            msg = f\"WARNING: No SearchApi results for {query}\"\n",
        "            print(msg)\n",
        "            return None\n",
        "    except RequestException as e:\n",
        "        msg = f\"ERROR: RequestException for {query}: {e}\"\n",
        "        print(msg)\n",
        "        return None\n",
        "    except Exception as e:\n",
        "        msg = f\"ERROR: Unknown error for {query}: {e}\"\n",
        "        print(msg)\n",
        "        return None"
      ],
      "metadata": {
        "id": "eQtJI4IKp1zD"
      },
      "execution_count": 223,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Step 3: Determine relevant links\n",
        "\n",
        "We're looking for information from the brand itself, not from watchdog organizations or news agencies. Those data are super interesting, just not what we're looking for.\n",
        "\n",
        "Google returns the to search results, and generally that includes a brand's own sustainability page (if one exists). We're going to implement a pre-processing step where we use an LLM to evaluate each link and make a determine on if it's relevant or not.\n",
        "\n",
        "I've also implemented some simple caching by saving files to a folder. Whenever you're doing something complicated over hundreds of things, there's a good chance an error will occur. In order to make restarting simple, I will generally cache results along the way. Before running a process, I'll check the cache to see if it exists. If it does, I use those results. Clearing the cache is as simple as deleting the files I want to recreate.\n",
        "\n",
        "The code below is our first LLM prompt. You can see that I provide the Google search result URL, the domain, and the Google generated snippet.\n",
        "\n",
        "### Ways to improve this\n",
        "\n",
        "I hope somebody does the inverse of this. What do people *other than the brand* say about the brand's sustainability? \"Branding\" is what you say about yourself, \"reputation\" is what other people say about you.\n",
        "\n",
        "I didn't do much checking on these results. Just eye-balling it they looked pretty good. If you wanted to build something like this for real, you'd want to establish the accuracy of this process. That would likely involve a test set that you could use as a reference to measurably improve your results.\n",
        "\n",
        "For simplicity and to keep my owner personal costs down, I only examine the top 3 Google search results. For real usage, this should be upped to ~10. I've also filtered out search results that point to a PDF. Obviously PDFs can contain important sustainability information, but they are a pain to deal with. There are a lot of tools out there for adding PDF content to an LLM, I just didn't implement it."
      ],
      "metadata": {
        "id": "1rvn20WHvdUr"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from langchain_core.prompts import ChatPromptTemplate\n",
        "from langchain_core.runnables import RunnablePassthrough, RunnableLambda, RunnableBranch, RunnableParallel\n",
        "from langchain_community.chat_models import ChatOpenAI\n",
        "from langchain_core.output_parsers import StrOutputParser\n",
        "from slugify import slugify\n",
        "import json\n",
        "\n",
        "llm = ChatOpenAI(model_name=\"gpt-3.5-turbo\", temperature=0)\n",
        "\n",
        "# Create a chain that evaluates each link and returns yes or no\n",
        "prompt = ChatPromptTemplate.from_messages([\n",
        "    (\"system\", \"\"\"Your job is to evaluate information about a website and decide\n",
        "if it likely contains information about the sustainability efforts of {brand}.\n",
        "For this task, we want information ONLY provided by the brand itself, not other\n",
        "organizations. If the domain does not look like it's owned by {brand}, then it is\n",
        "not relevant. You respond with only a single word: yes or no.\"\"\"),\n",
        "    (\"user\", \"\"\"Website Title: {title}\n",
        "Website URL: {link}\n",
        "Website Domain: {domain}\n",
        "Snippet: {snippet}\n",
        "\"\"\")\n",
        "])\n",
        "\n",
        "relevance_chain = prompt | llm | StrOutputParser()\n",
        "\n",
        "def get_cached_serp_result(query):\n",
        "    cache_file_name = slugify(query)\n",
        "    cache_file_path = f\"{root_path}/data/serp_cache/{cache_file_name}.json\"\n",
        "    if os.path.exists(cache_file_path):\n",
        "        with open(cache_file_path, 'r') as file:\n",
        "            return json.load(file)\n",
        "    else:\n",
        "        return None\n",
        "\n",
        "def cache_serp_result(query, result):\n",
        "    cache_file_name = slugify(query)\n",
        "    cache_file_path = f\"{root_path}/data/serp_cache/{cache_file_name}.json\"\n",
        "    with open(cache_file_path, 'w') as file:\n",
        "        json.dump(result, file)\n",
        "\n",
        "def get_candidate_results(x):\n",
        "\n",
        "    query = f\"{x['brand']} sustainability\"\n",
        "\n",
        "    cached_result = get_cached_serp_result(query)\n",
        "    if cached_result is not None:\n",
        "        print('Found cached result', query)\n",
        "        return cached_result\n",
        "\n",
        "    serp_results = fetch_via_serp(query)\n",
        "    # Just grab the top three URLs\n",
        "    candidate_results = serp_results[\"organic_results\"][0:3]\n",
        "    # Add back in the brand information, and sometimes the snippet is absent, so providing a default\n",
        "    candidate_results = [{\"snippet\": \"Unknown\", **result, \"brand\": x[\"brand\"]} for result in candidate_results]\n",
        "    # Run the relevance chain to see which are relevant\n",
        "    candidate_results = [{**result, \"relevant\": relevance_chain.invoke(result)} for result in candidate_results]\n",
        "    cache_serp_result(query, candidate_results)\n",
        "\n",
        "    return candidate_results\n",
        "\n",
        "# Expects as input an item from \"data\"\n",
        "candidate_results_chain = RunnablePassthrough.assign(candidate_results = get_candidate_results)\n",
        "brand_data = candidate_results_chain.batch(data[0:1000])\n",
        "#pprint.pp(brand_data[0:5])"
      ],
      "metadata": {
        "id": "zfKv5Dyy0g_t"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Step 4: Fetch content of relevant URLs\n",
        "\n",
        "Now that we have a set of relevant URLs for each brand, we need to fetch them. It's definitely possible to fetch HTML using your own setup. But, I've found that it's just not worth the hassle. You have to deal with websites that require Javascript, meaning you need to implement a headless browser. You have to deal with website's anti-scraping measures (Amazon's especially good at this). It's just not worth it.\n",
        "\n",
        "Instead, just pay someone else to do it! I'm using [crawlbase](https://crawlbase.com/), but there are lots of services for this doing. I just found them easy to use from a developer point of view. Even at the most expensive tier, they charge &dollar;0.006 per page, and that covers all the problems mentioned above.\n",
        "\n",
        "Even though it's really cheap, it can take a while, and I don't like throwing money away, so I implemented basic caching again.\n",
        "\n",
        "Crawlbase returns HTML, but that's actually not a good format for feeding into an LLM. Nearly every page has many times more formating and javascript information that actual information you care about. This is extra crud for the LLM to wait through, and this increases the cost but more importantly can result in the LLM missing important information.\n",
        "\n",
        "Lots of AI tools out there simply pull all the text out of the HTML. However, I've found this misses important context, like what text is a heading or the context provided by a table structure. The best compromise I've found so far is converting HTML to markdown. There are lots of tools for this. I'm using Html-2-Text.\n",
        "\n",
        "Fetching pages is an I/O intensive process. If you do it one-by-one, it will work, but it could take forever and if you're using a Google Colab notebook then it has a tendency to disconnect you after ~90 minutes of inactivity. I implemented a simple async process to fetch 10 pages at a time to speed things up. Not gonna' lie, I just took my synchronous code, put it into ChatGPT-4 and asked it to parallelize it. Worked like a charm!\n",
        "\n",
        "### Ways to improve this\n",
        "\n",
        "A brand's main sustainability page often includes the highlights they are most proud of, but often times really important information is buried one or more clicks deep into the website. An important improvement would be to identify relevant links on the page (using a similar process as Step 3) and then fetch those pages as well.\n",
        "\n",
        "Note that note all pages were scrapped successfully. About two dozen had errors. I didn't chase these down, but in a production context you'd want to figure out what was going on with those."
      ],
      "metadata": {
        "id": "LuKgbzUsZnGK"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import concurrent.futures\n",
        "from langchain.schema import Document\n",
        "from crawlbase import CrawlingAPI\n",
        "from langchain_community.document_transformers import Html2TextTransformer\n",
        "\n",
        "html2text = Html2TextTransformer()\n",
        "crawlbase_api = CrawlingAPI({'token': os.environ['CRAWLBASE_JS_API_KEY']})\n",
        "\n",
        "def cache_html(url, html):\n",
        "    cache_file_name = slugify(url)\n",
        "    cache_file_path = f\"{root_path}/data/html_cache/{cache_file_name}.html\"\n",
        "    with open(cache_file_path, 'w') as file:\n",
        "        file.write(html)\n",
        "\n",
        "def load_html_doc(url):\n",
        "    html_file_name = slugify(url)\n",
        "    html_file_path = f\"{root_path}/data/html_cache/{html_file_name}.html\"\n",
        "    if os.path.exists(html_file_path):\n",
        "        with open(html_file_path, 'r') as file:\n",
        "            doc = Document(page_content=file.read(), metadata={\"url\": url})\n",
        "            doc_transformed = html2text.transform_documents([doc])\n",
        "            return doc_transformed[0]\n",
        "    return None\n",
        "\n",
        "# You might have to run this a couple times because crawling errors are skipped\n",
        "# rather than retried. Results are cached so only URLs that had an error will\n",
        "# actually be refetched\n",
        "def process_brand(brand):\n",
        "    urls = [page[\"link\"] for page in brand[\"candidate_results\"] if \"yes\" in page[\"relevant\"].lower() and not page[\"link\"].endswith(\".pdf\")]\n",
        "    #print('Processing brand', brand[\"brand\"], len(urls), 'URLs')\n",
        "    brand_docs = []  # This will store the docs for the current brand\n",
        "    for url in urls:\n",
        "        #print('Processing:', url)\n",
        "        html_doc = load_html_doc(url)\n",
        "        if html_doc is None:\n",
        "            #print('Crawlbase starting:', url)\n",
        "            try:\n",
        "                response = crawlbase_api.get(url)\n",
        "                if response['status_code'] == 200:\n",
        "                    html = response['body'].decode('utf-8')\n",
        "                    print('Crawlbase finished:', url)\n",
        "                    # Save the raw HTML\n",
        "                    cache_html(url, html)\n",
        "                    # Retrieve the saved raw HTML via beautiful soup\n",
        "                    html_doc = load_html_doc(url)\n",
        "                else:\n",
        "                    print('Crawlbase error for', url, response)\n",
        "            except Exception as e:\n",
        "                print('There was an error', url, e)\n",
        "        #else:\n",
        "            #print('Cache found for:', url)\n",
        "        if html_doc is not None:\n",
        "            html_doc.metadata[\"brand\"] = brand[\"brand\"]\n",
        "            brand_docs.append(html_doc)\n",
        "    return brand_docs\n",
        "\n",
        "# Initialize an empty list to hold all documents\n",
        "docs = []\n",
        "\n",
        "# Process each brand in parallel. This can takes hours upon hours if you don't\n",
        "# parallelize it.\n",
        "with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor:\n",
        "    # Use `executor.map` to apply `process_brand` to each brand\n",
        "    results = executor.map(process_brand, brand_data[0:1000])\n",
        "\n",
        "    # Iterate through the results (each result is a list of docs for a brand)\n",
        "    for brand_docs in results:\n",
        "        # Extend the main docs list with the docs from each brand\n",
        "        docs.extend(brand_docs)"
      ],
      "metadata": {
        "id": "hiIcfIksJcVh"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Step 5: Create summaries\n",
        "\n",
        "Now that we have webpage content formatted in markdown, we're nearly ready to create our summaries. First, we have to create a summary pipeline, and the prompt engineering is really important in this case. You can get radically different results if you change the prompt. You can change what information the model focuses on, what it explicitly ignores, what format it returns the information in, how to cite results, and oh-so-much more.\n",
        "\n",
        "When I started learning about LLMs I kinda scoffed at prompt engineering. I was wrong - it's a legit hard and extremely important skill. It also doesn't take any real programming skills. Liberal arts majors are probably better at it overall. If you want to learn more about prompt engineering, check out [this course](https://www.deeplearning.ai/short-courses/chatgpt-prompt-engineering-for-developers/) by Andrew Ng and Isa Fulford. I highly recommend it.\n",
        "\n",
        "### Ways to make this better\n",
        "\n",
        "When I started this project, I thought I was going to need to use a vector database and implement something called \"retrieval augmented generation\" or RAG. For details on that, see this [LangChain doc](https://python.langchain.com/docs/use_cases/question_answering/).\n",
        "\n",
        "However, I started getting into it, I realized that RAG was overkill here. Instead, we could just feed *all* the information collected into the LLM's context window and trust it to make sense of it. This wouldn't have been possible a year ago because context windows were too small to fit all the material. Just goes to show how quickly things have been changing. A year ago, 4k tokens in the context window was standard, now it's 128k for OpenAI and Google goes all the way up to 10M tokens!\n",
        "\n",
        "Neverless, you could probably improve the results by implementing RAG. That would allow you to add more pages and provide the LLM with just the right material. For this proof-of-concept though, RAG is overkill.\n",
        "\n",
        "Note: I implemented caching a little differently here. I think it's slightly better, but I didn't want to spend the time to update the earlier steps."
      ],
      "metadata": {
        "id": "iN79V6Jkdw2t"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from langchain.prompts.prompt import PromptTemplate\n",
        "from langchain.schema import format_document\n",
        "\n",
        "def get_cached_summary(brand):\n",
        "    cache_file_name = slugify(brand)\n",
        "    cache_file_path = f\"{root_path}/data/summary_cache/{cache_file_name}.json\"\n",
        "    if os.path.exists(cache_file_path):\n",
        "        with open(cache_file_path, 'r') as file:\n",
        "            return json.load(file)\n",
        "    else:\n",
        "        return None\n",
        "\n",
        "# Different than before, need a single input and return input for use as Runnable\n",
        "def cache_summary(x):\n",
        "    brand = x[\"brand\"]\n",
        "    cache_file_name = slugify(brand)\n",
        "    cache_file_path = f\"{root_path}/data/summary_cache/{cache_file_name}.json\"\n",
        "    with open(cache_file_path, 'w') as file:\n",
        "        json.dump(x, file)\n",
        "    return x\n",
        "\n",
        "\n",
        "def document_combiner(x):\n",
        "    brand = x[\"brand\"]\n",
        "    brand_docs = [doc for doc in docs if doc.metadata[\"brand\"] == brand]\n",
        "\n",
        "    document_prompt=PromptTemplate.from_template(\n",
        "        template=\"URL: {url}\\nWEBSITE CONTENT:\\n{page_content}\\n\"\n",
        "    )\n",
        "    doc_strings = [format_document(doc, document_prompt) for doc in brand_docs]\n",
        "    return {**x, \"context\": \"\\n\\n---\\n\\n\".join(doc_strings)}\n",
        "\n",
        "# Create a chain that evaluates each link and returns yes or no\n",
        "prompt = ChatPromptTemplate.from_messages([\n",
        "    (\"system\", \"\"\"You create summaries of a company's sustainability efforts.\n",
        "You focus on concrete activities and things that aren't already legally rquired.\n",
        "Do not include in the summary vague claims like, \"We prioritize worker welfare\"\n",
        "or \"we're constantly working to reduce our environmental impact\". Include any\n",
        "commitments the company has made, like \"We're committing to being a zero waste\n",
        "company by 2030.\"\n",
        "\n",
        "Your summaries include four sections (each starting with a markdown header). The\n",
        "first three sections should be one or more paragraphs of text, not bullets. At\n",
        "the end of each of the first three sections, you always cite your sources for\n",
        "any source you used in the answer. The format you use is markdown formatted\n",
        "links with incrementing numbers as the text, like this:\n",
        "\n",
        "sources: [1](https://www.some-site.com), [2](https://www.another-site.com)\n",
        "\n",
        "If the same source is used multiple times, it should have the same number.\n",
        "\n",
        "The sections include:\n",
        "\n",
        "# Environmental protection\n",
        "What is the company doing to protect the environment? Examples of things to\n",
        "include are carbon reduction efforts, waste minimization, renewable energy\n",
        "usage, and designing more environmentally friendly products. Include any\n",
        "environmental commitments and memberships like SBTi or CDP. If no information,\n",
        "this section should just state \"Nothing found\"\n",
        "\n",
        "# Worker welfare\n",
        "What is the company doing to ensure worker welfare. Examples of things to\n",
        "include are living wage programs, community building activities, fair trade\n",
        "certification, and women's rights programs. If no information, this section\n",
        "should just state \"Nothing found\"\n",
        "\n",
        "# Animal welfare\n",
        "What is the company doing to promote the health of animals used in their product\n",
        "or service and/or to minimize animal suffering. Examples of things to include\n",
        "are animal welfare certifications like Global Animal Partnership or cruelty free\n",
        "certifications like Leaping Bunny. If no information, this section should just\n",
        "state \"Nothing found\"\n",
        "\n",
        "# Transparency\n",
        "In this section, include two bulleted lists, one with the title \"Certifications\"\n",
        "that lists certifications the company has attained, and one with the title\n",
        "\"Commitments\" that lists the time-bound goals the company has set. Here is the\n",
        "output format to use:\n",
        "\n",
        "- Certifications\n",
        "    - (bulleted list of certifications or \"Nothing found\")\n",
        "- Commitments\n",
        "    - (bulleted list of commitments or \"Nothing found\")\n",
        "\n",
        "You will be provided with context material upon which to base your summary. Do\n",
        "not including anything in the summary that is not included in the provided\n",
        "context. If the provided context doesn't have any relevant information for the\n",
        "brand you're making the summary for, respond with two words: \"Nothing found\".\n",
        "Format your response in markdown.\"\"\"),\n",
        "    (\"user\", \"\"\"Write a sustainability summary for {brand}.\n",
        "\n",
        "CONTEXT:\n",
        "{context}\n",
        "\"\"\")\n",
        "])\n",
        "\n",
        "# I chose GPT-4 for creating the summary.\n",
        "summary_llm = ChatOpenAI(model_name=\"gpt-4-0125-preview\", temperature=0)\n",
        "\n",
        "summary_chain = RunnablePassthrough.assign(\n",
        "    summary = (\n",
        "        document_combiner\n",
        "        | prompt\n",
        "        | summary_llm\n",
        "        | StrOutputParser()\n",
        "    )\n",
        ") | RunnableLambda(cache_summary)\n",
        "\n",
        "\n",
        "def check_cache(x):\n",
        "    cached_summary = get_cached_summary(x[\"brand\"])\n",
        "    if cached_summary is None:\n",
        "        print(f'Creating summary for {x[\"brand\"]}')\n",
        "        return summary_chain\n",
        "    else:\n",
        "        print(f'Returning cached summary for {x[\"brand\"]}')\n",
        "        return RunnableLambda(lambda x: cached_summary)\n",
        "\n",
        "summary_with_cache_chain = RunnableLambda(check_cache)"
      ],
      "metadata": {
        "id": "ClL9mX5feXgw"
      },
      "execution_count": 200,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Took 37 Minutes\n",
        "summaries = summary_with_cache_chain.batch(data[0:1000])"
      ],
      "metadata": {
        "id": "p2AYXSig61Bs"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Display a single summary for reference\n",
        "print(summaries[0])\n",
        "display(Markdown(summaries[0][\"summary\"]))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 902
        },
        "id": "wys6-cy0k71f",
        "outputId": "0ef694e2-52de-4d18-8f09-c3cbf0131a3f"
      },
      "execution_count": 226,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "{'brand': \"McDonald's\", 'fame': '99%', 'popularity': '63%', 'summary': \"# Environmental protection\\nMcDonald's is actively engaged in various environmental protection initiatives focusing on climate action, sustainable packaging, and the preservation of natural resources. The company has submitted evolved science-based targets for validation by the Science Based Targets initiative (SBTi) to align with a 1.5°C pathway and the new FLAG framework. Projects executed between 2019 and 2023 are expected to contribute to a 33% reduction in greenhouse gas emissions from their global 2015 baseline. In terms of packaging and waste, McDonald's reports that approximately 81.0% of their primary guest packaging materials and 97.2% of their primary fiber packaging come from recycled or certified sources as of the end of 2022. They aim to drastically reduce plastics in Happy Meal toys and transition to more sustainable materials by the end of 2025, having already reduced virgin fossil fuel-based plastic in Happy Meal toys by 47.8% globally. Additionally, McDonald's is committed to supporting deforestation-free supply chains for its primary commodities, achieving 99.0% deforestation-free sourcing for beef, soy for chicken feed, palm oil, coffee, and fiber for guest packaging in 2022. The company has also joined the Consumer Goods Forum’s Forest Positive Coalition to address commodity-driven deforestation and climate change issues across the sector.\\n\\nsources: [1](https://corporate.mcdonalds.com/corpmcd/our-purpose-and-impact/our-planet.html)\\n\\n# Worker welfare\\nNothing found\\n\\n# Animal welfare\\nMcDonald's has implemented several policies aimed at promoting animal welfare across its supply chain. The company has committed to sourcing chickens not treated with antibiotics important to human medicine in the U.S. and has eliminated the use of Highest Priority Critically Important Antibiotics (HPCIAs) to human medicine from all chicken served in several countries, with plans for China to comply by the end of 2027. Additionally, McDonald's announced a policy in December 2018 to reduce the overall use of antibiotics important to human health in its beef supply chain, covering 10 beef sourcing markets around the world. The company is also on a journey to advance more sustainable beef production, striving to improve environmental practices, make a positive difference in the lives of farmers, and drive improvements in animal health and welfare. Furthermore, McDonald's has made a commitment to source only cage-free eggs by 2025 in the U.S. and Canada and is 60% towards achieving this goal. In terms of pork, more than 91% of the pork purchased in the U.S. comes from suppliers that have phased out the use of gestation stalls for housing confirmed pregnant sows, with a commitment to maximize the time that pregnant sows spend in a group environment. McDonald's has also announced a global commitment to source chickens raised with improved welfare outcomes, outlining eight Broiler Welfare Commitments expected to be fully implemented by the end of 2024 in 13 key markets.\\n\\nsources: [1](https://www.mcdonalds.com/us/en-us/about-our-food/our-food-philosophy/commitment-to-quality.html)\\n\\n# Transparency\\n- Certifications\\n  - Nothing found\\n- Commitments\\n  - Submit evolved science-based targets for validation by SBTi in line with 1.5°C and the new FLAG framework.\\n  - Contribute to a 33% reduction in GHG emissions from the global 2015 baseline through projects executed between 2019–2023.\\n  - Drastically reduce plastics and offer sustainable Happy Meal toys and transition to more sustainable materials by the end of 2025.\\n  - Support deforestation-free supply chains for primary commodities (beef, soy for chicken feed, palm oil, coffee, and fiber for guest packaging) achieving 99.0% deforestation-free sourcing in 2022.\\n  - Source only cage-free eggs by 2025 in the U.S. and Canada.\\n  - Phase out the use of gestation stalls for housing confirmed pregnant sows in the U.S. by the end of 2024.\\n  - Fully implement eight Broiler Welfare Commitments by the end of 2024 in 13 key markets.\"}\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.Markdown object>"
            ],
            "text/markdown": "# Environmental protection\nMcDonald's is actively engaged in various environmental protection initiatives focusing on climate action, sustainable packaging, and the preservation of natural resources. The company has submitted evolved science-based targets for validation by the Science Based Targets initiative (SBTi) to align with a 1.5°C pathway and the new FLAG framework. Projects executed between 2019 and 2023 are expected to contribute to a 33% reduction in greenhouse gas emissions from their global 2015 baseline. In terms of packaging and waste, McDonald's reports that approximately 81.0% of their primary guest packaging materials and 97.2% of their primary fiber packaging come from recycled or certified sources as of the end of 2022. They aim to drastically reduce plastics in Happy Meal toys and transition to more sustainable materials by the end of 2025, having already reduced virgin fossil fuel-based plastic in Happy Meal toys by 47.8% globally. Additionally, McDonald's is committed to supporting deforestation-free supply chains for its primary commodities, achieving 99.0% deforestation-free sourcing for beef, soy for chicken feed, palm oil, coffee, and fiber for guest packaging in 2022. The company has also joined the Consumer Goods Forum’s Forest Positive Coalition to address commodity-driven deforestation and climate change issues across the sector.\n\nsources: [1](https://corporate.mcdonalds.com/corpmcd/our-purpose-and-impact/our-planet.html)\n\n# Worker welfare\nNothing found\n\n# Animal welfare\nMcDonald's has implemented several policies aimed at promoting animal welfare across its supply chain. The company has committed to sourcing chickens not treated with antibiotics important to human medicine in the U.S. and has eliminated the use of Highest Priority Critically Important Antibiotics (HPCIAs) to human medicine from all chicken served in several countries, with plans for China to comply by the end of 2027. Additionally, McDonald's announced a policy in December 2018 to reduce the overall use of antibiotics important to human health in its beef supply chain, covering 10 beef sourcing markets around the world. The company is also on a journey to advance more sustainable beef production, striving to improve environmental practices, make a positive difference in the lives of farmers, and drive improvements in animal health and welfare. Furthermore, McDonald's has made a commitment to source only cage-free eggs by 2025 in the U.S. and Canada and is 60% towards achieving this goal. In terms of pork, more than 91% of the pork purchased in the U.S. comes from suppliers that have phased out the use of gestation stalls for housing confirmed pregnant sows, with a commitment to maximize the time that pregnant sows spend in a group environment. McDonald's has also announced a global commitment to source chickens raised with improved welfare outcomes, outlining eight Broiler Welfare Commitments expected to be fully implemented by the end of 2024 in 13 key markets.\n\nsources: [1](https://www.mcdonalds.com/us/en-us/about-our-food/our-food-philosophy/commitment-to-quality.html)\n\n# Transparency\n- Certifications\n  - Nothing found\n- Commitments\n  - Submit evolved science-based targets for validation by SBTi in line with 1.5°C and the new FLAG framework.\n  - Contribute to a 33% reduction in GHG emissions from the global 2015 baseline through projects executed between 2019–2023.\n  - Drastically reduce plastics and offer sustainable Happy Meal toys and transition to more sustainable materials by the end of 2025.\n  - Support deforestation-free supply chains for primary commodities (beef, soy for chicken feed, palm oil, coffee, and fiber for guest packaging) achieving 99.0% deforestation-free sourcing in 2022.\n  - Source only cage-free eggs by 2025 in the U.S. and Canada.\n  - Phase out the use of gestation stalls for housing confirmed pregnant sows in the U.S. by the end of 2024.\n  - Fully implement eight Broiler Welfare Commitments by the end of 2024 in 13 key markets."
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Step 6: Profit\n",
        "\n",
        "If you've read this far, you might be interested in trying to use these results or this code to build a tool to help people shop. If that's the case, that's awesome. Thanks for caring about this. You're free to use this data and these tools however you want.\n",
        "\n",
        "However, just know that hundreds of websites and browser extensions have tried to do just this. It's way harder than you think, and just providing information isn't enough. All those tools didn't fail because they found it too hard to scrape the data. There are deeper problems that need to be solved.\n",
        "\n",
        "If you want more of my thoughts on this, check out some of my writings, like [this](https://www.linkedin.com/pulse/decoding-sustainable-shopping-why-current-approaches-miss-steve-isley/?trackingId=fSNbrA%2FyTzG%2BXi5uTyci0w%3D%3D) and [this](https://www.linkedin.com/pulse/make-sustainable-shopping-work-change-questions-youre-steve-isley/?trackingId=1%2F3y2XLIRgqttR4j%2FvZw4g%3D%3D). And if you'd like to chat, just send me an email at steve.c.isley@gmail.com."
      ],
      "metadata": {
        "id": "_Xrwo5VpkgJC"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Generate Output\n",
        "\n",
        "This section creates the output files you can use to search and view the results.\n",
        "\n",
        "This first block of code just grabs all the cached files summaries and puts them in a list."
      ],
      "metadata": {
        "id": "r4m2W9IAGo3N"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import json\n",
        "import glob\n",
        "\n",
        "def load_json_files():\n",
        "    folder_path = f\"{root_path}/data/summary_cache/\"\n",
        "    file_paths = glob.glob(f\"{folder_path}/*.json\")\n",
        "    all_dicts = []\n",
        "\n",
        "    for file_path in file_paths:\n",
        "        with open(file_path, 'r', encoding='utf-8') as file:\n",
        "            data = json.load(file)\n",
        "            all_dicts.append(data)\n",
        "\n",
        "    return all_dicts\n",
        "\n",
        "all_brands = load_json_files()\n",
        "\n",
        "# Oops, my big export incorrectly used 2 spaces instead of 4 for nested lists.\n",
        "# This messes up the markdown to html conversion. This fixed that. I updated\n",
        "# the prompt above so this shouldn't be an issue in future runs.\n",
        "def fix_markdown_indentation(markdown_text):\n",
        "    fixed_lines = []\n",
        "    for line in markdown_text.split('\\n'):\n",
        "        if line.startswith(\"  -\"):\n",
        "            fixed_lines.append(\"    \" + line.lstrip())\n",
        "        else:\n",
        "            fixed_lines.append(line)\n",
        "    fixed_markdown = '\\n'.join(fixed_lines)\n",
        "    return fixed_markdown\n",
        "\n",
        "for item in all_brands:\n",
        "    item[\"summary\"] = fix_markdown_indentation(item[\"summary\"])\n",
        "\n",
        "print(f\"Loaded {len(all_brands)} dictionaries from JSON files.\")\n",
        "print(all_brands[0])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "OafdTBw5GokZ",
        "outputId": "bfc909a8-ecc9-4188-b400-e07981117ec5"
      },
      "execution_count": 280,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Loaded 998 dictionaries from JSON files.\n",
            "{'brand': 'UPS', 'fame': '99%', 'popularity': '77%', 'summary': \"# Environmental protection\\nUPS has made significant strides in environmental protection, focusing on carbon reduction, alternative fuel usage, renewable energy, and reforestation. The company has committed to achieving 100% carbon neutrality by 2050, a goal that encompasses scope 1, 2, and 3 emissions. In pursuit of this, UPS has seen a 6.9% decrease in CO2e emissions across these scopes year over year. A cornerstone of their strategy is the investment in alternative fuels; UPS has added over 15,600 alternate fuel and advanced technology vehicles to its global fleet, including more than 1,000 electric and plug-in hybrid electric vehicles. This aligns with their target of 40% alternative fuel utilization in ground operations by 2025. In the last year, UPS purchased 162 million gallons of alternative fuels, accounting for 26.5% of their ground fuel usage. Additionally, UPS is working towards powering 25% of its facilities with renewable energy by 2025, with 8% of its total electricity already generated from renewable sources in 2022. The company has also planted 28 million trees since 2012, aiming for 50 million trees by 2030 to contribute to a greener planet.\\n\\nsources: [1](https://about.ups.com/us/en/our-impact/sustainability/sustainable-services/2022-ups-sustainability-report-.html), [2](https://investors.ups.com/sustainability)\\n\\n# Worker welfare\\nUPS has outlined commitments towards enhancing diversity and inclusion within its workforce. The company aims to achieve 30% women in full-time management positions globally and 40% ethnically diverse full-time management in the United States by 2025. This initiative is part of UPS's broader strategy to create a more inclusive and equitable workplace, reflecting the company's dedication to social sustainability and worker welfare.\\n\\nsources: [1](https://about.ups.com/us/en/our-impact/sustainability/sustainable-services/2022-ups-sustainability-report-.html)\\n\\n# Animal welfare\\nNothing found\\n\\n# Transparency\\n\\n- Certifications\\n    - Nothing found\\n\\n- Commitments\\n    - Achieve 100% carbon neutrality by 2050\\n    - 40% alternative fuel utilization in ground operations by 2025\\n    - Power 25% of facilities with renewable energy by 2025\\n    - Plant 50 million trees by 2030\\n    - 30% women in full-time management globally by 2025\\n    - 40% ethnically diverse full-time management in the United States by 2025\"}\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Prepare for exporting the markdown data into HTML for viewing in a static webpage.\n",
        "import markdown\n",
        "import pandas as pd\n",
        "\n",
        "# convert markdown to HTML\n",
        "md = markdown.Markdown()\n",
        "all_brands_html = [{\n",
        "    **item,\n",
        "    \"fame\": int(item[\"fame\"][:-1]),\n",
        "    \"popularity\": int(item[\"popularity\"][:-1]),\n",
        "    \"summary\": md.convert(item[\"summary\"])\n",
        "} for item in all_brands]\n",
        "\n",
        "# Make sure it's sorted by fame descending\n",
        "all_brands_html.sort(key = lambda brand: brand[\"fame\"], reverse=True)\n",
        "print(all_brands_html[0:3])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "YXo0AjExrkd_",
        "outputId": "e1f5a29f-2764-49c7-9de6-23a7b5e39122"
      },
      "execution_count": 281,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[{'brand': 'UPS', 'fame': 99, 'popularity': 77, 'summary': '<h1>Environmental protection</h1>\\n<p>UPS has made significant strides in environmental protection, focusing on carbon reduction, alternative fuel usage, renewable energy, and reforestation. The company has committed to achieving 100% carbon neutrality by 2050, a goal that encompasses scope 1, 2, and 3 emissions. In pursuit of this, UPS has seen a 6.9% decrease in CO2e emissions across these scopes year over year. A cornerstone of their strategy is the investment in alternative fuels; UPS has added over 15,600 alternate fuel and advanced technology vehicles to its global fleet, including more than 1,000 electric and plug-in hybrid electric vehicles. This aligns with their target of 40% alternative fuel utilization in ground operations by 2025. In the last year, UPS purchased 162 million gallons of alternative fuels, accounting for 26.5% of their ground fuel usage. Additionally, UPS is working towards powering 25% of its facilities with renewable energy by 2025, with 8% of its total electricity already generated from renewable sources in 2022. The company has also planted 28 million trees since 2012, aiming for 50 million trees by 2030 to contribute to a greener planet.</p>\\n<p>sources: <a href=\"https://about.ups.com/us/en/our-impact/sustainability/sustainable-services/2022-ups-sustainability-report-.html\">1</a>, <a href=\"https://investors.ups.com/sustainability\">2</a></p>\\n<h1>Worker welfare</h1>\\n<p>UPS has outlined commitments towards enhancing diversity and inclusion within its workforce. The company aims to achieve 30% women in full-time management positions globally and 40% ethnically diverse full-time management in the United States by 2025. This initiative is part of UPS\\'s broader strategy to create a more inclusive and equitable workplace, reflecting the company\\'s dedication to social sustainability and worker welfare.</p>\\n<p>sources: <a href=\"https://about.ups.com/us/en/our-impact/sustainability/sustainable-services/2022-ups-sustainability-report-.html\">1</a></p>\\n<h1>Animal welfare</h1>\\n<p>Nothing found</p>\\n<h1>Transparency</h1>\\n<ul>\\n<li>\\n<p>Certifications</p>\\n<ul>\\n<li>Nothing found</li>\\n</ul>\\n</li>\\n<li>\\n<p>Commitments</p>\\n<ul>\\n<li>Achieve 100% carbon neutrality by 2050</li>\\n<li>40% alternative fuel utilization in ground operations by 2025</li>\\n<li>Power 25% of facilities with renewable energy by 2025</li>\\n<li>Plant 50 million trees by 2030</li>\\n<li>30% women in full-time management globally by 2025</li>\\n<li>40% ethnically diverse full-time management in the United States by 2025</li>\\n</ul>\\n</li>\\n</ul>'}, {'brand': 'Ford', 'fame': 99, 'popularity': 62, 'summary': '<h1>Environmental protection</h1>\\n<p>Ford is actively working towards a more sustainable future with a focus on environmental protection. The company has committed to achieving carbon neutrality by 2050, with an interim goal of carbon neutrality in Europe by 2035. This commitment encompasses Ford\\'s vehicles, operations, and supply chain, which together account for approximately 95% of its carbon emissions. To reduce its carbon footprint, Ford is transitioning to renewable, carbon-free electricity for its manufacturing processes. As of 2022, more than 60% of the electricity used in Ford\\'s global operations was carbon-free. Additionally, Ford is the first U.S. automaker to engage its global supply chain in the Manufacture 2030 initiative, aiming to assist suppliers in meeting their carbon reduction targets. The company has also joined the First Movers Coalition to support the commercialization of zero-carbon technologies. Ford\\'s efforts extend to water conservation, aiming to save billions of gallons of water and working towards zero waste by incorporating sustainable materials into its vehicles.</p>\\n<p>sources: <a href=\"https://corporate.ford.com/social-impact/sustainability.html\">1</a>, <a href=\"https://shareholder.ford.com/Investors/esg/default.aspx\">2</a></p>\\n<h1>Worker welfare</h1>\\n<p>Ford is committed to building a more inclusive and equitable society, which includes ensuring the welfare of its workers. The company has invested over $525 million to train the next generation of auto technicians, preparing the workforce and local communities for the transition to electric vehicles (EVs). Since 1949, Ford and the Ford Fund have invested more than $2.2 billion in initiatives aimed at providing access to essential services, building new skillsets, and opening pathways to high-quality jobs. Ford is also creating a culture of inclusion where team members are valued, respected, and empowered to bring their true selves to work. This approach is part of Ford\\'s broader effort to transform its business and foster a just transition for its workforce.</p>\\n<p>sources: <a href=\"https://corporate.ford.com/social-impact/sustainability.html\">1</a></p>\\n<h1>Animal welfare</h1>\\n<p>Nothing found</p>\\n<h1>Transparency</h1>\\n<ul>\\n<li>Certifications<ul>\\n<li>Nothing found</li>\\n</ul>\\n</li>\\n<li>Commitments<ul>\\n<li>Achieve carbon neutrality by 2050</li>\\n<li>Achieve carbon neutrality in Europe by 2035</li>\\n<li>Use 100% local, renewable electricity in all manufacturing by 2035</li>\\n<li>Reach true zero waste to landfill across operations</li>\\n<li>Eliminate single-use plastics from operations by 2030</li>\\n</ul>\\n</li>\\n</ul>'}, {'brand': 'Taco Bell', 'fame': 99, 'popularity': 67, 'summary': '<h1>Environmental protection</h1>\\n<p>Taco Bell has implemented several initiatives to protect the environment and reduce its carbon footprint. The company transitioned all of its cold beverage cups and lids in U.S. restaurants to recyclable polypropylene, eliminating polystyrene and reducing resin usage. This change is part of Taco Bell\\'s broader commitment to reducing landfill waste and greenhouse gas emissions. Additionally, Taco Bell has joined the NextGen Cup Consortium to support the development of recyclable hot cups. In terms of sustainable sourcing, Taco Bell uses palm oil in its Cinnabon Delights® that is certified by the Roundtable on Sustainable Palm Oil (RSPO), aiming to minimize the negative impact of palm oil production on the environment and communities. The company has also made strides in sustainable packaging, with paper bags made from 100% recyclable and sustainably sourced materials and a switch from polystyrene foam to polypropylene for side containers. Taco Bell has set goals for 2025 to make all customer-facing packaging recyclable, compostable, or reusable, remove PFAS, Phthalates, and BPA from all customer-facing packaging, and add recycling and/or composting bins to all restaurants where infrastructure permits.</p>\\n<p>In a significant move to improve the environmental effects of its supply chain, Taco Bell has partnered with Cargill and the National Fish and Wildlife Foundation (NFWF) to implement conservation and regenerative agriculture practices across cattle grazing lands. This $2 million partnership aims to conserve natural resources and reduce the climate impact of beef, one of Taco Bell\\'s top-selling ingredients. The partnership will support beef producers with technical and financial tools to expand regenerative ranching practices, expected to sequester up to 44,000 metric tons of carbon dioxide equivalent per year by 2030.</p>\\n<p>sources: <a href=\"https://www.tacobell.com/faqs/purpose/planet\">1</a>, <a href=\"https://www.tacobell.com/news/taco-bell-cargill-and-national-fish-and-wildlife-foundation-partner-to-restore-habitats-and-expand-sustainable-farming-practices-in-beef-supply-chain\">2</a></p>\\n<h1>Worker welfare</h1>\\n<p>Nothing found</p>\\n<h1>Animal welfare</h1>\\n<p>Nothing found</p>\\n<h1>Transparency</h1>\\n<ul>\\n<li>Certifications<ul>\\n<li>Roundtable on Sustainable Palm Oil (RSPO)</li>\\n</ul>\\n</li>\\n<li>Commitments<ul>\\n<li>Make all customer-facing packaging recyclable, compostable, or reusable by 2025</li>\\n<li>Remove PFAS, Phthalates, and BPA from all customer-facing packaging by 2025</li>\\n<li>Add recycling and/or composting bins to all restaurants, where infrastructure permits, by 2025</li>\\n</ul>\\n</li>\\n</ul>'}]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Export to HTML\n",
        "from jinja2 import Environment, BaseLoader, Template\n",
        "\n",
        "template_str = \"\"\"\n",
        "<!DOCTYPE html>\n",
        "<html>\n",
        "<head>\n",
        "    <title>Brand Sustainability Data</title>\n",
        "    <link rel=\"stylesheet\" type=\"text/css\" href=\"https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css\">\n",
        "    <script type=\"text/javascript\" src=\"https://code.jquery.com/jquery-3.5.1.js\"></script>\n",
        "    <script type=\"text/javascript\" src=\"https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js\"></script>\n",
        "</head>\n",
        "<body style=\"padding: 30px\">\n",
        "\n",
        "<h1>Brand Self Reported Sustainability Efforts</h1>\n",
        "\n",
        "<p>\n",
        "<b>Created by <a href=\"https://www.linkedin.com/in/stevecisley/\">Steven Isley</a>.</b>\n",
        "</p>\n",
        "\n",
        "<p>\n",
        "I used LangChain and generative AI to automate the process of finding, downloading,\n",
        "and summarizing the self reported sustainability efforts of the top 1,000 most famous\n",
        "US brands. This web page summarizes the results. The full code is available on\n",
        "<a href=\"https://github.com/scisley/brand-sustainability-data-fetcher\">GitHub</a>.\n",
        "You can download a csv file of the data there.\n",
        "</p>\n",
        "\n",
        "<p>\n",
        "The code is also available in a\n",
        "<a href=\"https://colab.research.google.com/drive/1vHvDxtA7-8-_xl6AdCx9Hr0qZBFUWNib#scrollTo=xDbexGbpxqhW\">\n",
        "Google Colab Notebook</a>.\n",
        "</p>\n",
        "\n",
        "<table id=\"data-table\" class=\"display\">\n",
        "    <thead>\n",
        "        <tr>\n",
        "            <th>Brand</th>\n",
        "            <th>Fame</th>\n",
        "            <th>Popularity</th>\n",
        "            <th>Summary</th>\n",
        "        </tr>\n",
        "    </thead>\n",
        "    <tbody>\n",
        "        {% for item in data %}\n",
        "        <tr>\n",
        "            <td><h1>{{ item.brand }}</h1></td>\n",
        "            <td>{{ item.fame }}</td>\n",
        "            <td>{{ item.popularity }}</td>\n",
        "            <td>{{ item.summary|safe }}</td>\n",
        "        </tr>\n",
        "        {% endfor %}\n",
        "    </tbody>\n",
        "</table>\n",
        "\n",
        "<script>\n",
        "$('#data-table').DataTable({\n",
        "    \"order\": [[1, \"desc\"]] // Sort by the second column (index 1) in ascending order\n",
        "});\n",
        "</script>\n",
        "</body>\n",
        "</html>\n",
        "\"\"\"\n",
        "\n",
        "template = Template(template_str)\n",
        "html_output = template.render(data=all_brands_html)\n",
        "\n",
        "with open(f\"{root_path}/data/brand_summary.html\", 'w', encoding='utf-8') as f:\n",
        "    f.write(html_output)"
      ],
      "metadata": {
        "id": "xDbexGbpxqhW"
      },
      "execution_count": 289,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Export CSV file\n",
        "import csv\n",
        "\n",
        "# Specify the CSV file name\n",
        "csv_file_name = f\"{root_path}/data/brand_summary.csv\"\n",
        "\n",
        "# Define the fieldnames based on the dictionary keys\n",
        "fieldnames = ['brand', 'fame', 'popularity', 'summary']\n",
        "\n",
        "# Open the CSV file in write mode\n",
        "with open(csv_file_name, mode='w', newline='', encoding='utf-8') as file:\n",
        "    writer = csv.DictWriter(file, fieldnames=fieldnames)\n",
        "\n",
        "    # Write the header\n",
        "    writer.writeheader()\n",
        "\n",
        "    # Write the data rows\n",
        "    for row in all_brands:\n",
        "        writer.writerow(row)\n",
        "\n",
        "print(f\"Data exported to {csv_file_name} successfully.\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ydFNJWoU8WFb",
        "outputId": "ae7615ad-130a-4ee8-98da-ef01e7bbfdc1"
      },
      "execution_count": 285,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Data exported to /content/drive/MyDrive/Colab-Notebooks/prototypes/brand-data-fetcher/data/brand_summary.csv successfully.\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Parking Lot for Random Notes\n",
        "\n",
        "This section is just random notes I took along the way that I don't want to delete but haven't found a better home for. Please ignore them.\n",
        "\n",
        "* [Consumer Product Safety Comission Recalls database](https://www.saferproducts.gov/PublicSearch): could be great, but looks limited in scope to physical goods (e.g. not food brands).\n",
        "* [CorpWatch API](http://api.corpwatch.org/): Really cool free data set, but is probably overkill. Hundreds of thousands of corporations but most are not of interest to me and it looks like too much work to filter.\n",
        "* [YouGov](https://today.yougov.com/ratings/consumer/popularity/brands/all): They have a great list of consumer brands, but not easy way to download it. You can sort by \"Fame\" defined as \"the % of people who have heard of a brand.\" which is exactly what I'm after. I just kept scrolling until the top ~1k brands were visible then copied the HTML via the developer console.\n",
        "\n",
        "\n",
        "Time keeping:\n",
        "* 3-6 pm on Mar 9, 2024.\n",
        "* 1-4 pm on Mar 10, 2024.\n",
        "* 8:30-2:30 on Mar 11, 2024."
      ],
      "metadata": {
        "id": "n3Dd3bZhjd1k"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Debugging\n",
        "print(all_brands_html[0:2])\n",
        "adidas = next(brand for brand in all_brands_html if brand[\"brand\"] == \"Adidas\")\n",
        "print(adidas[\"summary\"])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "nuPd1Z-TtSoL",
        "outputId": "ed75310e-a460-46fc-eafd-efe391858123"
      },
      "execution_count": 269,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[{'brand': 'UPS', 'fame': 99, 'popularity': 77, 'summary': '<h1>Environmental protection</h1>\\n<p>UPS has made significant strides in environmental protection, focusing on carbon reduction, alternative fuel usage, renewable energy, and reforestation. The company has committed to achieving 100% carbon neutrality by 2050, a goal that encompasses scope 1, 2, and 3 emissions. In pursuit of this, UPS has seen a 6.9% decrease in CO2e emissions across these scopes year over year. A cornerstone of their strategy is the investment in alternative fuels; UPS has added over 15,600 alternate fuel and advanced technology vehicles to its global fleet, including more than 1,000 electric and plug-in hybrid electric vehicles. This aligns with their target of 40% alternative fuel utilization in ground operations by 2025. In the last year, UPS purchased 162 million gallons of alternative fuels, accounting for 26.5% of their ground fuel usage. Additionally, UPS is working towards powering 25% of its facilities with renewable energy by 2025, with 8% of its total electricity already generated from renewable sources in 2022. The company has also planted 28 million trees since 2012, aiming for 50 million trees by 2030 to contribute to a greener planet.</p>\\n<p>sources: <a href=\"https://about.ups.com/us/en/our-impact/sustainability/sustainable-services/2022-ups-sustainability-report-.html\">1</a>, <a href=\"https://investors.ups.com/sustainability\">2</a></p>\\n<h1>Worker welfare</h1>\\n<p>UPS has outlined commitments towards enhancing diversity and inclusion within its workforce. The company aims to achieve 30% women in full-time management positions globally and 40% ethnically diverse full-time management in the United States by 2025. This initiative is part of UPS\\'s broader strategy to create a more inclusive and equitable workplace, reflecting the company\\'s dedication to social sustainability and worker welfare.</p>\\n<p>sources: <a href=\"https://about.ups.com/us/en/our-impact/sustainability/sustainable-services/2022-ups-sustainability-report-.html\">1</a></p>\\n<h1>Animal welfare</h1>\\n<p>Nothing found</p>\\n<h1>Transparency</h1>\\n<ul>\\n<li>\\n<p>Certifications</p>\\n<ul>\\n<li>Nothing found</li>\\n</ul>\\n</li>\\n<li>\\n<p>Commitments</p>\\n<ul>\\n<li>Achieve 100% carbon neutrality by 2050</li>\\n<li>40% alternative fuel utilization in ground operations by 2025</li>\\n<li>Power 25% of facilities with renewable energy by 2025</li>\\n<li>Plant 50 million trees by 2030</li>\\n<li>30% women in full-time management globally by 2025</li>\\n<li>40% ethnically diverse full-time management in the United States by 2025</li>\\n</ul>\\n</li>\\n</ul>'}, {'brand': 'Ford', 'fame': 99, 'popularity': 62, 'summary': '<h1>Environmental protection</h1>\\n<p>Ford is actively working towards a more sustainable future with a focus on environmental protection. The company has committed to achieving carbon neutrality by 2050, with an interim goal of carbon neutrality in Europe by 2035. This commitment encompasses Ford\\'s vehicles, operations, and supply chain, which together account for approximately 95% of its carbon emissions. To reduce its carbon footprint, Ford is transitioning to renewable, carbon-free electricity for its manufacturing processes. As of 2022, more than 60% of the electricity used in Ford\\'s global operations was carbon-free. Additionally, Ford is the first U.S. automaker to engage its global supply chain in the Manufacture 2030 initiative, aiming to assist suppliers in meeting their carbon reduction targets. The company has also joined the First Movers Coalition to support the commercialization of zero-carbon technologies. Ford\\'s efforts extend to water conservation, aiming to save billions of gallons of water and working towards zero waste by incorporating sustainable materials into its vehicles.</p>\\n<p>sources: <a href=\"https://corporate.ford.com/social-impact/sustainability.html\">1</a>, <a href=\"https://shareholder.ford.com/Investors/esg/default.aspx\">2</a></p>\\n<h1>Worker welfare</h1>\\n<p>Ford is committed to building a more inclusive and equitable society, which includes ensuring the welfare of its workers. The company has invested over $525 million to train the next generation of auto technicians, preparing the workforce and local communities for the transition to electric vehicles (EVs). Since 1949, Ford and the Ford Fund have invested more than $2.2 billion in initiatives aimed at providing access to essential services, building new skillsets, and opening pathways to high-quality jobs. Ford is also creating a culture of inclusion where team members are valued, respected, and empowered to bring their true selves to work. This approach is part of Ford\\'s broader effort to transform its business and foster a just transition for its workforce.</p>\\n<p>sources: <a href=\"https://corporate.ford.com/social-impact/sustainability.html\">1</a></p>\\n<h1>Animal welfare</h1>\\n<p>Nothing found</p>\\n<h1>Transparency</h1>\\n<ul>\\n<li>Certifications<ul>\\n<li>Nothing found</li>\\n</ul>\\n</li>\\n<li>Commitments<ul>\\n<li>Achieve carbon neutrality by 2050</li>\\n<li>Achieve carbon neutrality in Europe by 2035</li>\\n<li>Use 100% local, renewable electricity in all manufacturing by 2035</li>\\n<li>Reach true zero waste to landfill across operations</li>\\n<li>Eliminate single-use plastics from operations by 2030</li>\\n</ul>\\n</li>\\n</ul>'}]\n",
            "<h1>Environmental protection</h1>\n",
            "<p>Adidas has committed to becoming a more sustainable company with a clear focus on environmental protection. Their roadmap for 2025 and beyond includes ambitious targets to reduce their environmental impact significantly. By 2025, Adidas aims for nine out of ten articles to be sustainable, defined by the use of environmentally preferred materials. They have committed to achieving climate neutrality across their entire value chain by 2050, with intermediate goals of a 15% reduction in GHG emissions per product by 2025 and a 30% reduction in absolute GHG emissions across their entire value chain by 2030, both measured against 2017 levels. Adidas is also focusing on more sustainable materials and circular services, aiming to replace all virgin polyester with recycled polyester by 2024, and has already achieved 96% recycled polyester usage in 2022. They have partnered with Parley for the Oceans since 2015, using Parley Ocean Plastic as a replacement for virgin polyester. Additionally, Adidas has committed to sourcing 100% of its cotton from more sustainable sources since the end of 2018 and is working on increasing the use of recycled and organic cotton. They are also focusing on responsibly sourced leather, with more than 99% of their leather volume audited in accordance with the Leather Working Group protocol. Furthermore, Adidas is exploring plant-based materials and has launched products designed for circularity, including footwear and apparel that can be completely recycled after use.</p>\n",
            "<p>sources: <a href=\"https://www.adidas-group.com/en/sustainability/focus-sustainability/our-targets/\">1</a>, <a href=\"https://www.adidas-group.com/en/sustainability/environmental-impacts/more-sustainable-materials-and-circular-services/\">2</a></p>\n",
            "<h1>Worker welfare</h1>\n",
            "<p>Adidas has set clear targets for 2025 to ensure worker welfare within their operations and supply chain. They aim to maintain a Lost-Time Incident Rate (LTIR) below the industry average, achieve zero fatal accidents, and zero Occupational Illness Frequency Rate (OIFR). In their supply chain, Adidas targets 90% of Tier 1 strategic suppliers to achieve at minimum 4S in social impact indicators, with 100% achieving 3S or better. They are also focusing on fair wages, aiming for progressive improvement in compensation measured by fair wage benchmarks across strategic Tier 1 suppliers. Additionally, Adidas is working towards achieving gender wage parity for workers and their supervisors in strategic Tier 1 suppliers. These efforts are part of a broader strategy to manage high-risk human rights issues across their value chain by 2025.</p>\n",
            "<p>sources: <a href=\"https://www.adidas-group.com/en/sustainability/focus-sustainability/our-targets/\">1</a></p>\n",
            "<h1>Animal welfare</h1>\n",
            "<p>Adidas uses a small fraction of animal-derived materials, primarily leather, which constitutes approximately 3% of their total materials volume. They ensure that more than 99% of their leather volume is audited in accordance with the Leather Working Group (LWG) protocol, with most hides sourced from tanneries with the highest LWG rating (LWG Gold). Adidas is working with the LWG to broaden the scope of the audit to include traceability to the slaughterhouse by 2030, aiming for higher transparency on environmental impacts such as deforestation from the origin of the material.</p>\n",
            "<p>sources: <a href=\"https://www.adidas-group.com/en/sustainability/environmental-impacts/more-sustainable-materials-and-circular-services/\">2</a></p>\n",
            "<h1>Transparency</h1>\n",
            "<ul>\n",
            "<li>Certifications<ul>\n",
            "<li>Leather Working Group (LWG) protocol</li>\n",
            "</ul>\n",
            "</li>\n",
            "<li>Commitments<ul>\n",
            "<li>By 2025, nine out of ten Adidas articles will be sustainable</li>\n",
            "<li>By 2025, achieve a GHG emissions reduction of 15% per product</li>\n",
            "<li>By 2025, achieve climate neutrality (GHG) at Adidas' own operations</li>\n",
            "<li>By 2030, reduce absolute GHG emissions across the entire value chain by 30%</li>\n",
            "<li>By 2050, Adidas will be climate neutral</li>\n",
            "</ul>\n",
            "</li>\n",
            "</ul>\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Number of urls fetched\n",
        "tmpurls = [item[\"candidate_results\"] for item in brand_data[0:1000]]\n",
        "tmpurls = [sum([url[\"relevant\"] == \"Yes\" for url in item]) for item in tmpurls[0:1000]]\n",
        "sum(tmpurls)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "pKTdNyeVI4Qm",
        "outputId": "059b98ce-1abe-42dc-9a23-3732b1e07ead"
      },
      "execution_count": 222,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "1885"
            ]
          },
          "metadata": {},
          "execution_count": 222
        }
      ]
    }
  ]
}