Creating dynamic fields based on Google Sheets

Question

As a marketing agency we spend a lot of time on reporting. For larger clients a custom dashboard is created, but this is not always feasible for smaller clients. So, we update them weekly with metrics such as impressions, reach and sales through e-mail.

Running reports from multiple data sources (Facebook, Google Ads...) and typing statistics in a mail is a waste of time. Ideally we want to automate this reporting by sending campaign managers weekly e-mails with the latest statistics.

Tools such as Supermetrics and Funnel can update metrics from almost all advertising channels periodically in Google Sheets. Unfortunately, it is not possible with Zapier to select the contents of specific cells and convert them to dynamic fields we can use in Gmail.

An example of this automation would be a weekly mail that contains the following, with dynamic fields as brackets:

--

Hi {client},

Quick update on your {channel} campaign. {Previous week} your {channel} campaign had {amount1} {metric1} and {amount2} {metric2}

--

Any ideas?

icon

Best answer by PaulKortman 22 November 2019, 01:52

View original

PaulKortman · Accepted Answer

@pim just a little clarification as @ChrisP was pointing out, specifically when you said this:

Unfortunately, it is not possible with Zapier to select the contents of specific cells and convert them to dynamic fields we can use in Gmail.

That's not true.

It might not be straight forward or super clear, but I've often set up a Zap to grab a specific field from a sheet. The key is to have a column with the content you want to specify, for example in your case perhaps you want to look in the date field for this week, or the last 7 days, and then get the value from Column C D or E or all of them.

The way Google Sheet's "Find a row" works is all you need to do is provide a value you want to match in a column, and the whole row will be pulled in, if you only need one cell that's fine, you just specify in the email step which cell/column you need from the row that matched.

So this can get really "complex" but yet still be simple, say you have two different sheets you are trying to pull data from and then run some math (sum or average) on the results and present it to the client in an email. Here's how I would set it up:

Trigger on date/time (ex: Monday's 8 am)
Formatter step - Date - to get yesterday's date (Sunday)
Formatter step - Date - to get 8 days ago date (Sunday)
(possible steps to combine or format dates)
Google Sheets find a row - specify the date as the column and then {{results from step 3}}-{{results from step 1}} as the value to look for
(repeat step 5 as needed to get data from different sheets)
(possible formatter steps to combine/average the data points)
Gmail - send email -- use the data from column "clicks" or "spent" etc from step 5 (or the steps in 6 or 7 as needed) to flesh out the data in your template.

I've used this method to report activity that happened in the last 7 days... the cool part is by doing the date calculations within the Zap it will be the correct dates every Monday or when ever you have the zap set to trigger.

Let us know what other questions you have as you attempt to set this up, We're happy to help.

ChrisP · Answer

@pim Not sure if I'm understanding you correctly but maybe this:

As long as all the rows have the same headers and you get the full google row into Zapier (by automatic or manual trigger), you can then map the cells of each row to supported email integrations exactly as you wish above.

When you customise email template in your actions step you can map each column of the sheet to each {example} you have above. Each new row added will trigger a new instance.

If its a case of waiting until all rows contain all info then you can add a manual trigger step very easily for each row.

You can also add a schedule or delay step thereafter to send whenever time of day/week you wish.

Creating dynamic fields based on Google Sheets

3 replies

Reply

Reply

Sign up

Use your Zapier credentials

Log in to the Community

Use your Zapier credentials

Scanning file for viruses.

This file cannot be downloaded