Working with Amazon data in Excel
Sometimes you just need Excel.
Analytics platforms like Tableau and Power BI are powerful, but can be intimidating and costly for new users. Companies often struggle to set up and maintain business intelligence tools, spending months on reports that are still no match for trusty Excel.
It’s familiar. Everyone knows the interface. Everyone can open an Excel file. Use tried-and-true formulas and analytical techniques.
It’s flexible. Put data and formulas in any cells you want. Import your data in any format—Excel can handle everything from a text file to a PDF.
It’s fast. Build a simple report in hours, not days or weeks. Create dashboards without overhauling your company’s data architecture.
It’s cheap. Most businesses already pay for Excel; it’s a sunk cost. Powerful solutions like Looker or Domo can be costly to implement and get value from.
For better or worse, we rely on Excel. When we demo a new tool, our first question is often “how do I export the data?” With that in mind we designed our product to work with Excel first. Customers can connect their Seller and Vendor Central data to Excel in minutes. Here are some suggestions for getting insights faster, and automating your reporting.
1. Create a persistent connection between your Amazon data and your analysis workspace
If you save your workbook after setting up a connection to Reason Automation, the connection remains. The next time you open your workbook, just click “Refresh” and your data updates automatically. If your reports and formulas use that data, they update automatically, too.
2. Transform your data before it ever hits Excel
…using SQL or PowerQuery. Combine multiple tables or sources to create the exact data feed you need. Aggregate your transactions by time period or ASIN—useful for keeping file sizes small, or getting around Excel’s 1-million row limit. Ex-Amazonians can pick up right where they left off, using the same query-writing and analysis methods.
3. Learn big-data basics for working in Excel
Amazon business managers swear by two functions: pivot tables and VLOOKUP. These functions help you summarize data (e.g. average sale price by date) so it can be presented (e.g. in a line graph).
4. Leverage AI tools
Microsoft’s partnership with OpenAI has introduced many new AI capabilities to Office products like Excel. Use the Analyze Data feature (previously called “Ideas”) to have AI suggest analyses and visualizations for your dataset.
5. Go even deeper on Add-ins
Excel plugins (Add-ins) can enhance your data analysis, visualization, and automation capabilities far beyond a spreadsheet.
Data Types & Stocks and Geography Features: These features use AI to recognize and enrich data with additional information. For example, if you have a list of companies or countries, Excel can automatically fetch related data such as stock prices or population.
Solver Add-in: Solver is an optimization tool in Excel that uses various algorithms, including some AI techniques, to find optimal solutions for decision problem models. It's particularly useful for resource allocation, scheduling, and budgeting problems.
Azure Machine Learning for Excel: This is an add-in that allows users to build, test, and deploy machine learning models directly from Excel using Azure Machine Learning. It enables users to access AI capabilities without leaving Excel or needing extensive programming skills.