Trade exports accounted for $37 billion of Georgia's economy in 2017. That translates to over 180,000 jobs in our state. With so many revisions to tariffs and NAFTA taking place, you may wonder: "What effect will these changes have on our State's economy?"
Starting with this blog article -- I'd like to share what I've learned about the subject. Together, we'll evaluate the most authoritative and detailed source of US import/export data that is available: the US Census Trade data. In this article I'll show you how to extract data from the Census using USA Trade Online. This is an easy-to-use and powerful method of retrieving trade-related data. In later articles I'll address:
- An alternate method of retrieving this data in bulk using the Census API,
- Migrating that raw-data content into SQL Server, and
- Analyzing it all in Microsoft Power BI.
The accompanying video to this article goes in more depth on these topics:
- How commodities are classified,
- How to choose between port, district and state geography, and
- Tips for navigating the USA Trade Online interface.
Why are we Mining Georgia Exports Data?
Our ultimate goal is to understand trends in Georgia relating to international trade. We'll pursue that goal by harvesting data from the Census, populating a SQL Server database with historical trade data, and finally analyzing it using Power BI.
From a data standpoint, our immediate needs are:
- Import and export data that is...
- Summarized at the month time-frame...
- Going back as many years as practical...
- For commodities imported for use in Georgia or exported from Georgia companies...
- That are classified using harmonized commodity codes....
- and that reference the specific country they are imported from or exported to.
Spoiler Alert: When done, we'll have a dataset comprised of around 10 million records spanning the period 2013 through 2018.
Primer on Census Trade Data
There are subscriptions you can purchase to retrieve bulk Census data. But anybody can freely retrieve this using the USATrade Online website. The registration page takes about 2 minutes to complete. You will then get an email confirmation to activate your account. Upon logging in to the system you'll be staring at this page.
You may ask -- what are all these options? The video describes it in more detail. But you have two initial decisions to make:
What Source US Geography to use?
- PORT -- like Brunswick, GA
- DISTRICT -- like Savannah District (comprised of ports Atlanta, Brunswick, Savannah)
- STATE -- which takes a little bit more of a nuanced approach in terms of classifying goods. It's not what port or district the product went out of -- but rather the state the product originated. This gives you a more accurate measure of the total goods originating from Georgia.
What Commodity Classification system to use?
- NAICS: North American industrial classification system. Which is a Dewey-Decimal-like method of classifying goods and services.
- HARMONIZED: Harmonized Codes. This is similar to the NAICS but is a system all international trading partners have agreed to use.
Based on your choices here -- the website will use a wizard to help you make your other selections. Here's a glimpse of that wizard.
From here you need to tell the system:
- What variables or data measures you want exported. Note: each variable is explained on the website page.
- How you want US Geography to be filtered. We will choose the State of Georgia
- How to filter your commodity classification. You can choose individual NAICS/Harmonized codes, or a range. It's at this step that you also specify the "grain" of detail -- ie., 2, 4 or 6 digits of precision.
- How you want to summarize the foreign destinations. In our example, we choose individual countries. Another common choice is Continents.
- And finally, what report period do you want. This will typically be by year or by month.
After telling the system what you want -- press the report download icon. After 4-5 minutes the site will give you an Excel file something like this:
What's Not to Like about USA Trade Online?
For occasional use or selective retrieval of data -- this site is great. If you visit the site often, there are a variety of steps you can take to make your life easier. The ability to save and re-apply selection sets is one example of this.
However, the USATrade web interface becomes tedious to use if you want many months of data with fine granular selections. Examples of fine granular selections would be opting for:
- 6-digit NAICS instead of 2-digit, or
- all individual countries instead of continents.
Those higher grain selections result in lots of records being returned, and the system limits the number of rows you can export. In the example we've been discussing -- it would require you to do individual downloads for every month in question. With a 5 minute delay between each download -- this becomes a tiresome and error-prone experience.
Fortunately, there is an alternate way of extracting this data that we'll cover in the next video, Census API for Trade Data Retrieval.
USA Trade Online Summary
To recap, we've used the USA trade website to generate one month of data from the site. It's a pretty intuitive process and the site allows you to pull your data in many alternative ways. If you're curious what type of exports occur in your industry -- why not create an account and take a look around?