Managing complex data sets directly in the Shopify dashboard can be quite challenging. Moreover, importing intricate data into Google Sheets without the right approach can result in inconsistencies and errors. When your data isn’t properly analyzed, valuable insights can be overlooked, leading to missed opportunities.
To address this, you need a simple reporting tool that seamlessly imports, manages, and automates Shopify Google Sheets integration. This guide will teach you how to integrate your Shopify store with Google Sheets and gain actionable insights. By the end of this guide, you can create data-backed Shopify reports that help accelerate the success of your online store.
Let’s get started.
How to connect Shopify to Google Sheets?
Here, we’ll provide a step-by-step guide on how to build a comprehensive Shopify report to analyze your store performance. By leveraging Google Sheets integration, you can easily import your Shopify store data via Two Minute Reports to make informed decisions.
To get started, install the Two Minute Reports add-on to Google Sheets. Here’s the installation tutorial: How to install TMR?
Once installed, you can proceed with the following methods to create your Shopify store report on Google Sheets:
Method 1: Connect Shopify to Google Sheets using the Two Minute Reports sidebar.
Method 2: Connect Shopify to Google Sheets using pre-built templates.
Method 1: Create your Shopify report using the sidebar
Step 1: Creating a Connection to your source
- Open a new Google Sheet and launch the TMR add-on from the extensions menu. The TMR sidebar will appear on the right side.
Note: The sidebar serves as the primary interface for TMR and has all its features.
- Navigate to the menu dropdown in the TMR sidebar and click Connections. Next, click Add to create a new Connection.
- The “Adding a new Connection” dialog appears. Here, we’ll perform three key steps to establish a Connection in Google Sheets, which takes less than 2 minutes.
- Selecting the Shopify Connector
- Authentication
- Mapping your Shopify store accounts with the respective Connector
- Select the Shopify Connector from the list of options. Whether you’re a store admin or a Shopify partner, you must generate API credentials from the Shopify dashboard and paste them into the TMR sidebar to connect your Shopify store.
- Select the App type “Shopify Admin” or “Shopify Partner”.
Note: If you are the store admin or a managing partner, refer to “How to create an access token?” to instantly generate your custom app and API key.
- Once you’ve successfully generated the credentials, you can copy and paste them in the Step 2: Authenticate section in the Adding new connection window, as shown below.
- Enter your Connection name and choose the app type as Method 1 - for Shop admins. Paste the API admin access token in the Access token field. Lastly, specify your shop name.
- Likewise, if you own a partner portal, select the app type as Method 2 - for Shopify Partners. Paste the API Key and API Secret you’ve copied from the partner portal. Enter your shop name and click Test and Save, as shown below.
- And that’s it. Your Shopify store will now be authenticated. Whether you are an admin or a Shopify partner, you can proceed with selecting the respective store accounts to extract actionable information.
- Enable the toggle for the store accounts you want to run queries. Please note that you cannot extract data from disabled accounts.
- The warm-up will begin automatically for the enabled Shopify store accounts. Once completed (approximately 2 to 24 hours), you can extract your online store data 10 times faster and gain valuable insights.
| Warm-up is when TMR fetches data from the respective platform. Meanwhile, periodic sync ensures that your data is updated automatically regularly. |
Note: Once you’ve enabled the associated accounts, you can hit “Refresh Connections” to ensure the connected Shopify accounts are reflected in the TMR sidebar.
With the Connection established, the next crucial step is to create a query. Let’s explore how to do that.
Step 2: Creating a query and report configuration
To pull your Shopify store performance data, navigate to the Data Queries section and create a new query.
- Click Add and choose the respective Shopify account data to analyze your campaign performance.
Note: You can manage multiple clients’ Shopify store accounts under one roof. Simply select one or more accounts to measure and analyze insights.
- Alternatively, navigate to the Clients section. Add your client and map their corresponding Shopify store account.
- Click on “Manage Clients” and map the necessary account details. Once done, click “Save & Close” to save your client’s details for future reference.
Next, add the Shopify store dimensions you want to analyze. Once you've done this, select the date range to analyze sales performance.
- Next, you configure your data by choosing how to customize it. You can also organize and filter your data from the sidebar.
- Once you finish the above steps, click Run query to visualize the final output. This query fetches the data from your Shopify store account onto the sheet and cell of your choice.
Pro TMR Tip: Run all your queries directly from the Extensions menu on your Google Sheets. Go to Extensions > Two Minute Reports > Choose your desired quick action.
While creating your Shopify report through the sidebar is straightforward, there’s also another effective way to streamline your store’s performance outcome. Let’s look at leveraging Shopify reporting via built-in templates.
Method 2: Create a Shopify report with ready-made templates
Two Minute Reports offers pre-built ecommerce templates that allow you to create data-driven Shopify reports faster. Each template is designed with Shopify KPIs and visual elements tailored to your needs.
- Once the Connection is created and authorized, you can access the Template Gallery directly from the Two Minute Reports sidebar menu.
Note: You can also open the template gallery directly from Extensions > Two Minute Reports > Templates.
- In the Template Gallery, you can filter the template that fits your purpose by category or data source. You can also search for specific templates if you have in mind.
- Once you find your template, click “Use” to build your report. Then, choose the preferred data source and relevant Accounts.
- Name your report and click Submit to create your report.
Note: Your Shopify report will be created in a new Google Spreadsheet.
- Once done, click View Report to open your template in Google Sheets.
- Your new report opens up. Next, click Extensions > Two Minute Reports > Run all queries to update your template with data within the date range.
- Once the queries are complete, the template will be updated with your data.
Scheduling data refresh and emails
So far, you have:
- Installed Two Minute Reports in Google Sheets.
- Created a data source (Shopify) in TMR.
- Created and executed a data query that fetches your data.
Now you have your data in the Google Spreadsheet. If you need the latest data, you can run the query again.
Or you can automate the data queries.
With the “Schedule Refresh” feature, TMR runs automatically, constantly refreshing your queries to your desired date, time, and duration. This feature is convenient for sending emails or refreshing your spreadsheets with live data and using them as a dashboard.
Here’s how you set this feature up:
Click on the menu and then on Schedule Reports to open the section. From this section, you can create a “Schedule,” an instruction for Two Minute Reports to run the query automatically.
- Name the Schedule and choose the time interval between the data refreshes. From this section, you can choose the following:
- You want your data daily, weekly, or monthly.
- The designated day for the refresh
- The time for refresh
- Choose the Action for every data refresh— choose whether you need an email notification for every refresh.
- Then choose the relevant sheet for the refresh and type in your email (in case you need an email notification)
- You can also choose to specify the reporting format. You can insert the sheet content in the email body, attach the selected sheet as a PDF or Excel file, or attach the spreadsheet as an Excel file.
- Save the refresh and ensure that TMR will refresh your data at the desired intervals.
Continue reading about this feature: “How to refresh data automatically in Google Sheets?”
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 data can be changed by the user dynamically. This traffic dashboard contains vital metrics like Top Revenue Sources, Gross Sales, Total Sales, Order Quantity, Tax refund, Net Sales, Shipping, etc. Here's how you can leverage this Shopify template:
- Top 20 revenue sources: This chart represents the top 20 traffic sources with respect to their order quantity.
- 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 represents the total, unfulfilled, and fulfilled orders for the chosen date range.
- Sales overview chart: This chart represents key metrics such as orders, gross sales, discounts, refunds, net amount, etc for the chosen date range.
- Top 20 products by sales chart: This chart displays the top 20 products which have the highest sales with respect to their order quantity.
Overall, you can effectively keep track of Shopify store revenue and customers every month.
Why connect Shopify to Google Sheets
In the dynamic realm of ecommerce, staying abreast with your store’s data is essential. When you connect Shopify to Google Sheets, you initiate an automated information flow, seamlessly syncing data from Shopify orders to intricate inventory metrics. The Shopify Google Sheets integration saves you from the tedium of manual data entry and ensures your business data is neatly organized in your Google Sheets spreadsheet.
Streamline your workflow with Google Sheets and Shopify integration
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.
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 automatically generated in your existing spreadsheet or a new spreadsheet that is readily accessible.
Enhanced Shopify customer insights in Google Sheets
Understanding your Shopify customers is crucial for growth. Connecting Shopify to Google Sheets lets 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 for 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 your team can access critical insights quickly and contribute to the latest data on Shopify orders, customer details, or automation tools that streamline ecommerce 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 zero effort.
Tips for managing Shopify data via Google Sheets integration
Follow these five proven tips to effectively manage your Shopify store data when integrating with Google Sheets.
1. Automate data syncing
Updating your latest performance data in Google Sheets can be time-consuming and prone to errors, negatively impacting measurable outcomes. Instead, you can automate the process to sync data at defined intervals, ensuring you can access real-time insights anytime at your fingertips.
How to automate? By leveraging Two Minute Reports’ Schedule Refresh, you can set up automatic data updates — daily, hourly, weekly, or monthly - so your Google Sheets has the latest store insights.
2. Use filters and data validation
You might feel overwhelmed as your Shopify store generates vast amounts of daily data (orders, inventory, sales, customers). With filters and validation, you keep your data organized and ensure only the necessary information is displayed, allowing you to act quickly on critical insights.
How to use filters and data validation?
Filters: You can apply filters to focus on specific subsets, such as viewing orders from a particular date range or customer demographics in a specific location.
Data validation: For example, you can set data validation to allow only email addresses in the customer email column or only valid currency entries in the payment column, eliminating manual struggles and complexities in managing data.
3. Organize data in separate tabs
Managing huge amounts of Shopify data in a single tab feels complex. Creating separate tabs for sales, orders, payments, etc., you can minimize clutter, maintain clarity, and enhance easy accessibility.
How to organize data effectively? Create different tabs within the same Google Sheets document based on your incoming data flow. You can also use VLOOKUP() to link data across Sheets hassle-free.
4. Use data aggregation for large datasets
Importing large datasets from Shopify can slow your spreadsheets' speed, accuracy, and overall performance. Aggregating data, however, helps you manage large amounts of data efficiently.
How to use data aggregation? You can group sales and payments by time periods such as week, month, or quarter. This would reduce the number of rows in Google Sheets, giving you a clear picture of what’s happening in your store. Alternatively, pivot tables can summarize data and present insights in a digestible way.
5. Ensure accurate data mapping
While importing your Shopify data, you must ensure that every piece is mapped with the corresponding columns in Google Sheets. If not, this might lead to data inconsistencies, impacting critical performance outcomes.
How can you accurately map Shopify data? With Two Minute Reports, you can sync your store accounts and automatically map the data fields, enabling seamless data accuracy. This saves time and ensures you work with real-time, consistent data, which lets you analyze data-driven opportunities effectively.
Track top KPIs that enhance your Shopify reports
In today’s ecommerce 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 the Shopify App Store, to streamline their operations, gain deeper insights into their business metrics, and manage Shopify orders more effectively.
Let’s explore the crucial Shopify KPIs that provide valuable insights into your store's performance and trends.
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. |
Generate data-driven Shopify reports in Google Sheets
By harnessing the power of connecting Shopify to Google Sheets, you can transform data into actionable insights, automate routine tasks, and focus on growing your business. Let Two Minute Reports handle the heavy lifting. Each new order from Shopify is automatically imported as a new row in your Google Sheets, ensuring accuracy and efficiency. Export Shopify orders to Google Sheets and watch your business flourish with high ROI. Bid adieu to lengthy spreadsheets and streamline valuable insights with this easy-to-use Shopify Google Sheets integration.
Get your Shopify store data in Looker Studio
Two Minute Reports simplifies ecommerce 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.
Combining data from multiple sources can enhance data management, eliminating the need for manual CSV reports. This strategic approach to business intelligence optimizes real-time Shopify metrics, streamlines workflows, and facilitates data-driven decision-making for ecommerce businesses.
Connect similar platform data sources with Google Sheets
The Two Minute Reports add-on can connect 30+ data sources to Google Sheets, extract actionable insights, and build real-time dashboards and white-label reports to effectively manage growing clients’ needs and expectations.
- Amazon Seller
- WooCommerce
- And other data sources.
