How to connect Shopify to Google sheets
Here, we'll show you a step-by-step guide of how we build our comprehensive Shopify Report to analyze what's going on with your current store. Without writing a single line of code you can automatically pull the latest data from Shopify to Google Sheets. Let me show you how to use our google sheets integration for importing data.
First, you can Check out the process to install, Two Minute Reports google sheets add on.
After installing the Two Minute Reports sidebar, we have two different approaches to create our Facebook ads Report on Google Sheets.
Method 1: Connect Shopify to Google Sheets using Two Minute Reports Sidebar.
Method 2: Connect Shopify to Google Sheets using in-built Templates of Two Minute Reports.
Method 1: Connect Shopify to Google Sheets using sidebar
Once you install the Two Minute Reports add-on for Google sheets, you can launch the sidebar by going to the Extensions tab in a new Google sheet.
Click on the Menu icon near Data Queries and choose Data Sources
Once the Data Sources section is open, click on Add+ to create a new Shopify Data source.
Name your new Shopify Data Source and Select Shopify from the drop down.
Select App Type as either “Store Admins” or “Shopify Partners”.
- If you are a store admin, you would need to generate the Access Token from your Shopify Admin Portal.
- Then copy paste the same Access Token in the Data source section here along with your Store Name and click on Test and Save .
- Click here to learn on, "How to create an access token".
Once you get a message “Data Source successfully authenticated save?” click on Yes.
Now, select the Menu icon and choose Data Queries.
Start naming your new Query, Select your newly created Shopify Data Source from the drop down below.
You can now choose your desired metrics from the listed categories below like Sales, Orders, Products etc.. from your Shopify account and within that consists Total Sales Amount, Orders, Inventory Quantity etc..
Once you choose your Metrics for your Shopify Report, it is time to categorize your metrics with its specific Dimensions. In dimensions you have categories like Time, Store, Product etc.. which consist of Date, Store name, Product name etc..
Once you have chosen your combination of Metrics and Dimensions, you can move onto the Date Section where you can either use pre defined Date Ranges available or create your own custom Date Range.
Next, Select the Sheet and Cell location for writing the data into your desired sheet and cell location.
Click on Run Query.
Click on “Yes” to save your newly created query.
You would have your new Query saved in the sidebar and your desired data in the Spreadsheet.
Method 2: Connect Shopify to Google Sheets using Templates
Once you have installed the Two Minute Reports Add on for Google Sheets, open the add on and choose Templates
You can search for Templates and also add individual search filters for categories and Data source.
Once you choose the Template, Click on Use to start mapping your Data Source and Ad Account.
- Start mapping your Template by adding your newly created Shopify Data Source and Spreadsheet.
Click on Submit.
Your Template would be ready now. Click on “Go to new Spreadsheet“ to open your Template.
Once the sidebar is open, Click on Run all Queries and all the Template data will be updated to your data.
Your Template is now ready!
Here! Have a Bonus….. Automate your Reports and Templates
Try out our exciting and unique feature, Schedule Refresh which automates your Reports and Templates to refresh automatically at your desired date, time and durations with a copy sent to your mail. Click here to learn more about Schedule Refresh.
Use our quick templates for Google Sheets
1. Shopify Dashboard with Traffic and Sales report
This is a unique Dashboard which infers data for 30 days, where the date can be changed by the user dynamically . This traffic dashboard contains vital data like Top Revenue Sources like Google Facebook etc. , Gross sales, Total sales, order quantity and key metrics like refund amount , tax amount, discount amount , net sales and shipping.
- TOP 20 REVENUE SOURCES: This chart represents the top 20 Traffic sources with respect to their order quantity which is crucial in finding the sources like
- GROSS SALES CHART: This chart represents the gross sales amount on each day for the chosen date range.
- ORDER QUANTITY CHART: This chart represents the number of orders placed on each day for the chosen date range
- CART OVERVIEW PIE CHART: This pie chart represent the total , fulfilled and unfulfilled orders for the chosen date range.
- SALES OVERVIEW CHART: This chart represents all the vital metric data like orders, gross sales, discount, refund amount, shipping, tax and net amount for the chosen date range.
- TOP 20 PRODUCTS BY SALES CHART: This chart displays the Top 20 products which has the highest sales with respect to its order quantity.
This helps to keep a track on your revenue and customers on monthly basis.
Why connect Shopify to Google Sheets
In the dynamic realm of e-commerce, it's essential to stay abreast with your store's data. When you connect Shopify to Google Sheets, you initiate an automated information flow, seamlessly syncing all Shopify data from Shopify orders to intricate inventory metrics. The Shopify Google Sheets integration not only saves you from the tedium of manual data entry but also ensures your business data is neatly organized in your Google Sheets spreadsheet.
Streamline Your Workflow with Google Sheets and Shopify
Automating the transfer of data from Shopify to Google Sheets simplifies your workflow significantly. Every time you link Shopify, new Shopify orders are instantly reflected in your Google Sheets spreadsheet, keeping your sheets consistently up to date. This Shopify Google Sheets integration provides a real-time view of your business's health, directly in your Google Sheets account.
Data Accuracy and Reporting in Google Sheets
While Shopify's built-in data capabilities are robust, connecting Shopify to Google Sheets exponentially enhances your ability to analyze, sort, and report on sales and customer information. This integration turns your Google Sheets account into a powerful data destination, offering detailed reports that are automatically generated in your existing spreadsheet or a new spreadsheet, and readily accessible.
Enhanced Customer Insights in Google Sheets
Understanding your Shopify customers is crucial for growth. By choosing to connect Shopify to Google Sheets, you gain deeper insights into customer behavior and sales trends. Shopify customers' data is automatically imported and formatted in your Google Sheets spreadsheet, enabling you to create personalized shopping experiences that boost retention and satisfaction.
Optimize Inventory Management with Google Sheets
Efficient inventory management is vital for any Shopify store. Sync your Shopify products and line items to Google Sheets to get a comprehensive view of your inventory levels in a new spreadsheet. Shopify automatically updates your Google Sheets spreadsheet, allowing you to track inventory metrics and automate restocking alerts.
Facilitate Collaboration and Accessibility with Google Sheets
Sharing business insights with your team is as simple as sharing a link to a Spreadsheet. This encourages collaboration, ensuring that your team can access the Google sheet with just a Google Sheets click and contribute to the latest data on Shopify orders, customer details, or automation tools that streamline e-commerce processes in Google Sheets. Every time there's a new Shopify order or a new order, your team is immediately in the loop as the data is imported and formatted into the Google Sheets spreadsheet with another Google Sheets click.
By harnessing the power of connecting Shopify to Google Sheets, you and your team transform data into actionable insights, automate routine tasks, and focus on growing your business. Let technology handle the heavy lifting of data synchronization. Each new order from Shopify is automatically imported as a new row in your Google Sheets spreadsheet, ensuring accuracy and efficiency. Export Shopify orders to Google Sheets and watch your business flourish with this integration. Simply sign in to connect your Shopify admin to connect your Shopify store Google Sheets today, and let the technology work for you and your team, shaping the future of your business through this Shopify Google Sheets integration.
Top KPIs which enhances your Shopify Reports
In today’s e-commerce landscape, harnessing the power of data analytics is crucial for success. Connecting Shopify to Google Sheets opens up a world of possibilities for merchants from everywhere like Shopify App Store to streamline their operations, gain deeper insights into their business metrics, and manage Shopify orders more effectively. Let’s explore the pivotal data points you can leverage by integrating these powerful tools, including detailed analysis of Shopify orders and Shopify products. This integration not only simplifies the tracking and management of Shopify products but also provides valuable insights into the performance and trends of your Shopify orders.
Order
Analyze total sales, gross sales, discounts, and tax amounts in Shopify reports to identify areas for improvement in operations. Use insights from this data to adjust pricing strategies, discount offerings, and tax calculations for more profitable sales.
| Title | Description |
| Orders | The total number of orders placed. |
| Ordered quantity | The quantity of a product or item that has been ordered |
| Returned quantity | The quantity of a product or item that has been returned by a customer |
| Net quantity | The quantity of a product or item that remains after accounting for both ordered and returned quantities |
Sales
Examine total sales, gross sales, discounts, and tax amounts in Shopify reports to pinpoint operational improvement areas. Utilize insights to refine pricing strategies, discount offerings, and tax calculations, enhancing sales profitability.
| Title | Description |
| Total sales amount | The total sales of order amount |
| Discount amount | The total amount of discounts applied on the order and line |
| Refund amount | The total amount refunded for the order |
| Tax amount | The total amount of tax for the order & line. Takes returns into account |
Product
Assess product title, SKU, sales and inventory quantity, product ID, variant title, product type, price, inventory value, and cost of goods sold on Shopify to optimize operations.
| Title | Description |
| Inventory value | The sell value of the product inventory |
| Inventory quantity | The product quantity in inventory |
| Cost of goods sold | The total cost of all the units of goods sold. |
Customer Geo
Examine customer geo data to discern their origins. Tailor marketing campaigns and personalize product offerings based on customer location. Enhance operations by identifying areas with a high concentration of customers, potentially requiring additional resources.
Store
Identify store names and IDs to segment sales and customer behavior across stores. Compare performance across locations, determine best sellers, and assess promotional tactics to enhance operations. Utilize this information to pinpoint profitable stores and streamline inventory quantity.
| Title | Description |
| Store name | This is the count of impressions for Direct Ads and Sponsored Updates and sends for InMails. |
| Store ID | The unique identifier for the shopify store. |
Traffic source
Examine customer behavior and refine marketing strategies by utilizing Shopify's first and last visit sources, dates, and types. Enhance operations by generating targeted reports based on these insights to elevate conversion rates and increase revenue.
| Title | Description |
| FV: Date | The first visit date |
| FV: Source | The First visit Source from which the customer visited the store, such as a platform (Facebook, Google), email, direct, a website domain, QR code, or unknown. |
| LV: Source | The source from which the customer last visited the store, such as a platform (Facebook, Google), email, direct, a website domain, QR code, or unknown. |
| LV: UTM medium | The AD medium of a marketing campaign from which the customer last visited the store. |
Customer Insights
Evaluate customer behavior using email, names, unique IDs, customer types, and tags. Create detailed customer profiles, personalize outreach, optimize websites, update inventory, and enhance customer experience for improved marketing and data-driven decisions.
| Title | Description |
| Customer first name | The first name of the customer |
| Customer email | The email of the customer |
| Customer type | Whether the customer a Returing customer (Customers whose order history includes two or more orders) or a First-time customer. |
| Account created date | The date the customer was added to the online store |
Get Shopify data in other tools too
Two Minute Reports simplifies e-commerce data analysis by integrating Shopify with Looker Studio, offering advanced analytics and visualization tools. This connection allows for automated data import from Shopify, enabling the creation of customized reports and dashboards directly in Looker Studio's user-friendly platform. The seamless integration combines data from multiple sources, enhancing data management and eliminating the need for manual CSV handling. It's a strategic approach to business intelligence that optimizes the use of real-time Shopify metrics, streamlines workflows, and facilitates data-driven decision-making for e-commerce businesses.
Get to know more about Looker Studio integration
