Selljam logo

Holy Sheet! Give Google Sheets Superpowers with These Add-ons

Holy-Sheet-Google-Addons
Looking for ways to supercharge your Google Sheets? Check out these amazing add-ons that can help you bring in data from your business or ecommerce store, automate tasks, analyze data, and create stunning visualizations.

As an online business owner, you have so many different tasks to perform. From understanding customer preferences, tracking your projects, collaborating with team member, and carrying out email marketing campaigns, this list is endless.

Google Sheets provides a free and low-tech way to monitor and carry out these tasks, and also to serve as a database for all your data, tasks, ideas and important content.

Google Sheet extensions offer exceptional capabilities to further boost your productivity. Some of my most useful and the best Google Sheets add-ons are outlined here, check out how these powerful tools can help you leverage and grow your business.

Best Use Cases for Google Sheets

While Google Sheets cater to the diverse needs of online businesses, some of the best and advanced uses for Google sheets could be found below.

Data Analysis and Reporting

Any business, particularly e-commerce, needs to manage huge volumes of data - pricing, customers, locations, products, sales, and more.

All the data may not be at a single storage point. That said, fetching data from multiple sources, cleaning them for formatting errors, doing analysis, could take up a lot of your valuable time and effort.

Google Sheets provide features like blending the data from Google Analytics and other sources like Salesforce to draw actionable insights. Besides, there are simple ways to create robust dashboards in Google Sheets, which make your complex information easy to understand and presentable.

Some of the best Google Sheets add-ons for reporting and data analysis include

Additionally, you could use ChatGPT for the Google Sheets extension to create regular expressions in analytics and reporting.

Data Analysis Use Case Example: How to Combine GA4 Traffic Data

How to combine traffic data and key ecommerce metrics from 13+ Global Ecommerce Stores into a single unified Looker Studio report .

Currently I'm serving as the Head of eCommerce for a European food-tech solutions provider that operates multiple ecommerce storefronts across UK, Europe and South Africa. This comes with some data and analytics challenges. Each eShop has it's own property, data and traffic reports setup inside of Google Analytics 4. So the challenge is combining all of the traffic data into a single report that's easy to read and is sent daily to my email inbox.

Below is a quick overview of how to achieve this.

The Tool Stack:

The Problem:

Having multiple GA4 properties (13+), each with their own data and reports, meant that it was time-consuming viewing reports and analysing traffic data, also it didn't provide a birds eye view of how the business was performing as a whole, and most importantly being able to benchmark key metrics across the individual GA4 properties.

The Solution:

A 2 step process was needed to create a report and dashboard that could combine all data, from all the GA4 properties and countries, into an easily readable report that could be delivered via email daily.

Step 1 - Setup the Google Sheet Report

A dataset of traffic data was needed, so I created a Gsheet that fetches data from each of the GA4 properties daily on a schedule. My tool of choice here is the Google Sheet extension called SyncWith.

It's a lightweight and powerful extension that has integrations for GA4, Google Ads, MySQL and more. Below is an example of how I setup one of the country's dataset. Every country has it's own sheet and report in SyncWith. We then bring all of these sheets together in an "Overview" sheet which combines them using the formula below.

Install the Syncwith Add-on: Google Sheet → Add-ons → Get Add-ons → Search "SyncWith".

Once you've setup and authorised the add-on you can start creating reports. Above is an example of the settings for a UK report, which will fetch all the selected metrics and dimensions from GA4. We're only looking at traffic data for this example, but you will have access to all of them, to build out the other reports in your Looker dashboard.

Google Sheets Query Formula

The query formula in Google Sheets is a powerful way to combine multiple sheets into a single unified sheet. Below is an example of the query used for this purpose, and combines the GA4 traffic data for multiple countries into a single sheet.

={QUERY(UK!A2:H,"select * where B is not null order by B");QUERY(France!A2:H,"select * where B is not null order by A");..[repeat here for all others]}

Step 2 - Create the Looker Studio Report

The next step is to create the Looker Studio report to combine all of the traffic data from the different GA4 web streams and countries that were created in the Dataset report.

Create the Looker Studio Report

  1. Setup your Looker Studio report how you'd like (above is in example of an "Overview" report page)
  2. Create a chart (seen in top left, for all the traffic and conversions) and use the above "Dataset" Google Sheet report from Step 1 as the data source.
  3. Add all the columns you wish to track and analyse (i.e. Total users, Sessions, Conversions etc)
  4. Include a "comparison column" to compare values against the previous period
  5. Below the main report add scorecards for the primary metrics you want to track. For example: AOV (average order value), Conversions, Conversion Rate **, etc.
  6. Make the report more dynamic by adding Filter boxes to select the "Date Range" and "Country" which will allow you to isolate countries and view specific time ranges.

Creating a Custom Conversion Rate Metric

Create the Conversion Rate % (CR%) Formula

  1. Click Add metric in the chart settings
  2. Add Field (as you're creating a new custom field)
  3. Name field: Give it a name like "Total Users Conversion Rate %"
  4. Data type field: Numeric → Percent
  5. In the formula field: type Conversions (it will highlight automatically, then select it) next add a slash to divide " / " then type and select Total users.
  6. Then select Average for the aggregation setting

This will divide the conversions by the total users and show it as a percentage, which is the Conversion Rate %.

And that's the gist of it...

Once you're happy with your Looker Studio report and have added all the charts you'd like to see then schedule the delivery of it to your email inbox each day. You do this by clicking the Share button → Schedule delivery and your email.


Other Ways to Supercharge Google Sheets

Google Sheets is not just for crunching numbers. It is incredibly versatile with many different use cases that range from project tracking, email marketing to storing complex data reports.

Project Tracking

You can efficiently manage and track your projects with Google Sheets. The best advantage is that it saves all the data in real time, and thus, the sheet is always updated. In addition, you can track the status of multiple projects worked on by different teams or individuals and know who is headed where.

Google Sheets is an extremely powerful tool that offers a hassle-free experience to e-commerce businesses that constantly deal with hundreds of customers.

When managing large projects, use some of the best Google Sheets add-ons like Lucidchart Diagrams. However, if you are a small e-commerce business with limited resources, use the best Google Sheets add-ons like Coupler.io to scrape data.

Benefits of Using Google Sheets for Project Tracking:

  • Smart Chips: Tag your teammates directly in Google Sheets cells for easy communication and updates, like mentioning them in comments. You can also insert files, places and more with smart chips.
  • Dropdowns: Use dropdown menus (in your cells) to select predefined options, ensuring consistency and saving time in data entry during project tracking. Colour code them for easy reference. This works great for tracking things like statuses of a task (ie "scheduled, doing, awaiting, done")
  • Task Checkboxes for Completion: Use checkboxes to create to-do lists and see at a glance which tasks are done—simple visual tracking.
  • Colorful Alerts with Conditional Formatting: Make cells stand out with colors based on rules you set. Great for highlighting deadlines, important dates, or budget limits.
  • Controlled Input with Data Validation: Ensure accurate data entry by setting rules. It helps prevent mistakes and keeps your project info clean and consistent.
  • Easy Sorting and Filtering for Quick Analysis: Quickly organize and view your project data the way you want. Filter or sort to spot trends, helping with decision-making.

Complex Formulae

As an e-commerce business, you need to do quite a bit of number crunching. Google Sheets makes your life easier by providing simple ways to perform complex calculations and carry out numerous comparisons and trends on the numbers. It, therefore, lets you always keep track of your company metrics while staying updated on your latest sales, profit, or expenses.

Alternatively, ChatGPT for sheets lets you enter queries in the tool, and it composes complex formulae in a few seconds.

Below are a few examples of some more advanced Google Sheet formulas that could be useful:

VLOOKUP:
Usage: To look up and retrieve data from a specified range.
Example: =VLOOKUP(A2, Products!A:B, 2, FALSE)
Explanation: In an e-commerce scenario, you might use VLOOKUP to fetch product details based on a product ID.

SUMIFS:
Usage: To sum values based on multiple criteria.
Example: =SUMIFS(Sales!C:C, Sales!A:A, "Product1", Sales!B:B, ">01/01/2023")
Explanation: This formula can help calculate the total sales for a specific product after a certain date.

IFERROR:
Usage: To handle errors and replace them with a specified value.
Example: =IFERROR(A2/B2, "Error: Division by zero")
Explanation: This formula can be useful to avoid displaying error messages, for example, in calculations involving division.

QUERY:
Usage: To query and filter data in a structured way.
Example: =QUERY(Sales!A:C, "SELECT A, SUM(C) GROUP BY A", 1)
Explanation: The QUERY function can help you analyze and summarize data from a large dataset, like calculating total sales per product.

IMPORTRANGE:
Usage: To import data from one Google Sheet to another.
Example: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sheet1!A1:B10")
Explanation: This formula allows you to pull data from another spreadsheet, which can be useful for consolidating information from various sources.

Organized Survey Feedback

Surveys are a key requirement of e-commerce owners who need to keep a tab on what customers like and what they don’t. Mostly, you might be using Google Forms to capture the feedback and responses to surveys, other contests, etc. Google Sheets acts like a database and records all the responses from Google Forms in a systematic format. You could use this information to interpret, analyze, or make meaningful decisions.

Using ChatGPT you could also run some message "sentiment" or "summarise" queries to understand quickly what the overall impression of the customers' feedback is about and take the necessary actions from there.

Mini-Email Marketing Campaigns

While Google cannot replace your email marketing app it can provide some pretty powerful features when you are just starting out in business, or if you have the need to email a smaller audience and want to keep it super simple.

With Gmail's Mail Merge feature you're able to send personalised emails, newsletters and other communications to a list of email contacts from Google sheets and Gmail - for free.

Email marketing campaigns are a core element of e-commerce marketing strategies. If you don’t wish to invest in email marketing software, there is a simple way in which Google Sheets (and Gmail) can help by using the Gmail Mail Merge.

Limitations

  • Send 500 outgoing daily emails (Standard Gmail account)
  • Send 2,000 outgoing daily emails (Google Workspace account)

Learn more about Gmail Mail Merge.

Additionally there are also addons like: Yet Another Mail Merge (YAMM), you can send out bulk personalized emails to a small list while also checking for parameters like clicks, bounce, and opens.

Google Sheets cannot replace a marketing automation tool. However, it can perform several tasks that could help small-scale e-commerce needs.

Enhanced collaboration with co-workers

In an online business or e-commerce scenario, your co-workers may work on different aspects of the same project simultaneously. This may require them to make entries of their inputs and outcomes or add comments concerning the tasks assigned to them.

Google Sheets is always improving and offers a seamless collaboration among the team members by ensuring they can simultaneously work on the same sheet without affecting each other’s efforts. This is one of the major reasons that makes Google Sheets a low-cost, simple yet powerful, sought-after collaboration software for professionals and businesses.

If you're working on ChromeOS, then using Chrome sheets will ensure you have all the power of Google Sheets on your device, and it also increases productivity and efficiency when offline, as it's a built-in app.

SEO Tasks: Perform backlink outreach

You need to expand your backlink outreach to increase your website’s domain authority and, in turn, the traffic. Google Sheets helps in this aspect as well. One of the ways to achieve this is by creating sub-sheets for each client, project, blog content topic, and such. Next, you could use a column of the sheet to include the website name, contact, email, etc., of the pitch carried out.

If you successfully get a feature on a pitch, you can highlight the column in a different color, say, green. So, this could serve as a starting point for increasing the backlink outreach.

Best AI-powered addons for Google Sheets

Here are a handful of the top add-ons for Google Sheets, some are even AI-enabled, and will give your sheets superpowers.

SyncWith

Syncwith is an AI-based add-on for Google Sheets that lets you move your live data into these sheets on an hourly or daily basis with no hassles. It’s a great way to collate all data in one place using a tool known to you - Google Sheets. Create insightful reports using pivot tables, charts, and more.

You don’t need to configure reports in multiple locations and use login access to check them each time. Syncwith lets you set up and manage all reports in a single place, and there is no need to keep shifting tabs. It offers APIs for other services, including Google Analytics, Google Ads, etc. Besides, you can include and exclude attributes as you wish.

Numerous.ai

Nunerous.ai is an excellent addon for Google Sheets that lets you use ChatGPT within these sheets. The special “AI” function allows you to extract text, generate formulae, make intelligent guesses, and perform many useful operations on Google Sheets. It can serve the diverse needs of your e-commerce business efficiently and effectively.

Apipheny

Apipheny is a no-code tool for seamlessly integrating with Google Sheets by allowing API data into these sheets. It allows you to complete everything in just a few simple steps. All you need to do is install this add-on for Google Sheets in the G Suite Marketplace, open it in the Google Sheet, and enter your API credentials.

Among the various features it offers, some of the major ones include text analytics, visualization, dashboard creation, website analytics, and performance metrics.

Power Tools

Power tools is an exceptional set of 30+ smart features which, when used as add-ons for Google Sheets, considerably reduce a lot of routine operations. In addition, it lets you do many of your complex tasks in Google Sheets in just a short time. Of these features, the most important and commonly used are - combining duplicates, matching and merging data, changing text cases, comparing sheets, splitting texts, and more.

Wrapping up

While there are various useful software and apps available for online and e-commerce businesses, Google Sheets extensions offer a simple, robust, and affordable solution.

By using these and other powerful Google Sheets add-ons you can leverage the full benefit of data analysis, project management, email marketing, collaboration, and much more, all from a familiar and easy to use Google Sheet interface.

Written by:
James Mew
Selljam Founder | Head of eCommerce

With 18 years of experience in the eCommerce industry, I have successfully launched and grown multiple e-commerce businesses, the 2nd one hit 7-figures in revenue within its first year. In 2022, I joined a European food technology equipment and IT service provider as their Head of E-commerce, overseeing 14 eshops across Europe and South Africa. Selljam is where I share all those ecommerce tips, tricks and hacks learned along the way that I hope will also help you on your journey to success. 

Related Posts

Customer Identity Verification Tools: The Best Apps to Protect Your Ecommerce Store

When secure card payments aren't enough and you need an extra layer of protection, then customer ID verification may be just what your business needs to level up. Comply with regulations and also protect your ecommerce business or app from fraud and chargebacks with the best customer identity verification tools.

Guide to Email Marketing Campaigns: The Automated Growth Hack for Ecommerce Businesses

Learn how to create and implement email marketing funnels to automate your sales and grow your ecommerce business. This article show you all the best campaigns and sequences to run on your ecommerce store.

How to Get Your PageSpeed Score In the 90s

Boost SERP rankings, improve your website's performance and SEO with these tips on how to get your PageSpeed score in the 90s. Get more traffic to your website and convert visitors on your "money pages".

Email Automation Tools and Platforms: Case Studies and Success Stories in the E-commerce Space

Learn how to use email automation to grow your ecommerce business. Discover the best email automation tools and platforms, and see how other ecommerce businesses are using them to achieve success.

Domain Buying Secrets: The Things I learned From Buying a €3,000 domain

Discover the secrets of buying a valuable domain name. Learn how to identify the best domains and avoid common pitfalls to securing your dream domain.

PDP Ecommerce: Anatomy of the Perfect Product Page

Learn what makes a perfect product page (PDP). Discover the key elements of it, and see how to optimize your product pages for conversions.

How to Use ChatGPT & AI for E-commerce to Boost Your Business

Discover how to use ChatGPT and AI to boost your ecommerce business, with tips on generating creative content, optimizing your website, and improving customer service.

Dictation Apps and Speech-to-Text Are Your Secret Weapons For Increased Efficiency and Productivity

Boost your productivity with dictation apps and speech-to-text technology. Learn how to choose the right tool for your needs and use it to create content, send emails, and more.

AI Chatbots for CX: The Future of Customer Service for eCommerce Stores

Learn how AI chatbots can help you improve customer service and boost sales for your ecommerce store. This guide includes information on the benefits of AI chatbots and tips on implementing them in your store.

The 30x eCommerce Metrics & KPIs That Matter (And To Monitor)

Track the right ecommerce metrics and KPIs to boost your sales and grow your business. This guide includes 30 of the most important ecommerce metrics and KPIs to track.

Productivity Hacks: The Best Text Expanders and Autotext Tools

Discover the best text expanders and autotext tools to boost your productivity and save time, essential for ecommerce sellers and online professionals.

Expert Guide: How to Sell an Online Business for 6 Figures or More

Get expert advice on how to sell your online business for 6 figures or more, from someone who's done it before, in this comprehensive guide for your exit strategy. You learn when to sell, where to start and how to boost your business valuation.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram