Have a specific service in mind or looking to share more about your project goals? Fill out the form on this page and one of our sales specialist will get back to you ASAP.
Curious about the association between a client’s best-performing Google Ads keywords in terms of revenue and their organic search rankings for those keywords, I imported the Google Ads keyword report and Ahrefs organic keyword report into Google Sheets. Unfortunately, I realized I had no idea how to create the analysis I wanted. Inspired by the Spike Jonze film “Her,” I turned to artificial intelligence to solve my problems, booted up ChatGPT, and started asking questions about how to tailor my datasets to my needs. As it turns out, if you ask the right questions, ChatGPT can be an amazing tool to help you create complex formulas and streamline data analysis in Google Sheets.
My starting point was a Google Sheet containing the client’s Google Ads keyword report and Ahrefs organic keyword report. My goal was to analyze this data to identify the most valuable keywords from Google Ads and illustrate organic search revenue opportunities. My problem was that my college Computer Science 101 spreadsheet unit was 10 years ago, and COUNTIF is probably the most complex formula I know. Looking at my final goal, I broke down what I needed to figure out into three parts: calculating total revenue by unique keywords, identifying the most profitable keywords, and auditing how this client ranks organically for them. So, with that in mind, I began asking ChatGPT questions.
The first step was to find the total revenue of each unique keyword from my exported Google Ads keyword list.
To break the ice, I asked ChatGPT, “In Google Sheets, I have the headings ‘User ID, Revenue, Campaign ID, Ad Group ID, Keyword, Device.’ What formula should I use to find the total revenue by keyword?”
ChatGPT responded, building a SUMIF formula to calculate the total revenue generated by each unique keyword.
=SUMIF(OriginalSheet!E2:E, UniqueKeywordsCell, OriginalSheet!B2:B)The formula finds the sum of keyword revenue, ultimately creating a list of unique keywords and their total revenue.
Next, I needed to sort my keywords by revenue, allowing me to easily identify the highest-grossing Google Ads Keywords.
My next question was a simple one, “How do I sort the sheet by keyword revenue?”
ChatGPT quickly crafted a SORT function.
=SORT(H2:I, 2, FALSE)To further analyze the data, I imported a new tab to my spreadsheet containing additional metrics for each keyword, such as search volume, keyword difficulty, and SERP features. I wanted to create a report that showed Client A’s Google Ads keyword revenue and compare it to Client A’s organic performance for the same keyword.
Unsure how much sense I made, I asked, “I important (sic) a new sheet formatted like: Keyword, Position, Previous Position, Volume, Traffic (desc), Keyword Difficulty, SERP features, URL, SERP features featured – how can I make sure the new columns can work off of the keyword column in the previous sheet?”
Despite my typo, ChatGPT explained that I can use the VLOOKUP function to pull data from a newly imported sheet based on the keywords in the original sheet. Creating the correct, working formula on the first try.
=IFERROR(VLOOKUP(D2, 'Organic Metrics'!A$2:I, 2, FALSE), "")This process allowed me to create new columns in the original sheet and fill them with relevant data from the organic metrics, resulting in a comprehensive view of how Client A organically ranks for each keyword. Moreover, along with the resulting dataset I created a visualization that compared keyword revenue with organic search positions.
Example Sheet:
| Keyword | Google Ads Keyword Revenue | Organic Position | Keyword Difficulty | Ranking URL |
|---|---|---|---|---|
| Keyword 1 | $90,000 | 7 | 66 | https://odd.dog/ |
| Keyword 2 | $85,000 | 1 | 66 | https://odd.dog/ |
| Keyword 3 | $80,000 | 6 | 70 | https://odd.dog/ |
| Keyword 4 | $75,000 | 6 | 62 | https://odd.dog/ |
| Keyword 5 | $70,000 | 24 | 48 | https://odd.dog/ |
| Keyword 6 | $70,000 | 24 | 26 | https://odd.dog/ |
| Keyword 7 | $70,000 | 1 | 5 | https://odd.dog/ |
| Keyword 8 | $50,000 | 5 | 69 | https://odd.dog/ |
| Keyword 9 | $50,000 | 18 | 36 | https://odd.dog/ |
| Keyword 10 | $45,000 | 2 | 59 | https://odd.dog/ |
| Keyword 11 | $30,000 | 32 | 24 | https://odd.dog/ |
| Keyword 12 | $15,000 | >100 | 50 | https://odd.dog/ |
| Keyword 13 | $7,000 | 33 | 81 | https://odd.dog/ |
Example visualization to illustrate where Client A organically ranks (orange) for their highest-grossing Google Ads keywords (blue):
By examining the connection between Google Ads keyword revenue and organic keyword positions, I identified high-impact opportunities to improve the client’s organic search performance and drive additional revenue. This is just one of many data analysis approaches that digital marketers can utilize to make informed decisions and develop effective strategies. With ChatGPT’s assistance, there’s no need to spend time searching and troubleshooting complex formulas. By simply asking straightforward questions, ChatGPT proves to be an invaluable resource for tackling intricate, technical tasks. Allowing us marketers to focus on what we do best, market.
Creating this analysis, even with the help of ChatGPT, can be tedious. To help you out I put together a template for you to use. Feel free to use it for your own analysis.
A fair trade, your email for the template
If you need further help, please don’t hesitate to contact us.
We are proud to be your local, trust-worthy Digital Marketing Company. Whether you need SEO Services, a PPC Agency, Social Media Management for Small Businesses, Small Business Marketing Packages or even a full Branding Agency, we’ve got you covered!
Please use the calendar to the right to book an initial meeting with our sales team.
Have a specific service in mind or looking to share more about your project goals? Fill out the form on this page and one of our sales specialist will get back to you ASAP.