Best answer

Creating dynamic fields based on Google Sheets

  • 18 November 2019
  • 3 replies
  • 1084 views

Userlevel 1

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

3 replies

Userlevel 7
Badge +10

Hi @pim

A (possibly complicated) way to do this would be a summary table in GSheets that uses lookup formulas to find the relevant cells from other sheets and pulls all the data onto a single row you can then retrieve with Zapier and map.

You could also look to partially migrate to Integromat. They integrate with Google Sheets and have a "Get Range Value" search step, which as far as I know can be used to select a single cell 'range'.

https://www.integromat.com/en/integrations/google-sheets

Screen Shot 2019-11-18 at 12.09.30.pngYou could use a webhook in your zap to trigger a scenario in Intgeromat that finds the value in the cell you need and then returns it back to Zapier via a "Webhook Response":

https://www.integromat.com/en/integrations/gateway

Screen Shot 2019-11-18 at 12.11.07.pngThat response, complete with the data from the GSheets cell will then appear back in Zapier as output from the webhook step you created - and could then be mapped into your weekly email.


Userlevel 7
Badge

@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.




Userlevel 7
Badge +10

@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:

  1. Trigger on date/time (ex: Monday's 8 am)
  2. Formatter step - Date - to get yesterday's date (Sunday)
  3. Formatter step - Date - to get 8 days ago date (Sunday)
  4. (possible steps to combine or format dates)
  5. 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
  6. (repeat step 5 as needed to get data from different sheets)
  7. (possible formatter steps to combine/average the data points)
  8. 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.


Reply