Share your best marketing analytics trick and get a chance to win! 🏆 🎉 (Ended)

This one is easy! I wish I knew about the Sparkline function in Google Sheets sooner! It is the most amazing thing if you just want to see a small visualization of data without having to do an entire chart. 

Here are two examples of how I use it in my Google Sheets reports: one is a bar chart (green and red) and the other is a simple line graph.

Here is a helpful article that gives inspiration: https://blog.coupler.io/sparkline-google-sheets-function-explained/

My favourite marketing analytics trick is adding secondary dimensions in Looker Studio. I wish I had known about it earlier as I remember previously telling people that it couldn’t be done, until I came across this blog post by Mehdi Oudjida: https://how.withlookerstudio.com/advanced-controls/20200825-google-data-studio-secondary-dimension-like-inside-google-analytics/. 

It’s very useful to be able to switch to campaign / ad group / keyword data easily. 
 

Here is a video showing it as an example I made in a google ads account (ignore the values and text, it has been mixed up using his Looker Studio Extension). 

My favourite marketing analytics trick is the sumifs function in Google Sheets to aggregate and connect data from different sources and for different timeframes in order to keep the number of API requests at a minimum.

My favourite looker studio trick that I discovered some 5 years ago, is using a (somewhat) hidden google API to fetch favicons for basically any domain. It really makes a nice, and more visual, presentation of traffic sources in my dashboards:

I just add the source-dimension to this google url (https://www.google.com/s2/favicons?domain_url=supermetrics.com) and get a png back (sometimes you have to manually adjust some sources so it’s a domain, like if you’re using another naming conversion on utm_source tags for example)

Our favorite marketing analytics tip is to watch the playlist from the fabulous Siavash Kanani for data blending and understanding how different join types work (chapter 10), including an excellent hint on how to use the COALESCE() formula.

It helped us a lot in combining different data sources into one joint data source, resulting in more insightful charts and tables!

Happy campaigning everyone <3

Best regards.

Robin

While it was scary and took a bit to nail down properly, I think the biggest upgrade by far was implementing a data warehouse. After scaling our Looker Studio use case to the absolute max, and dealing with broken APIs from how many times our data was getting accessed, it was a breath of fresh air to see reports just populate in seconds without issue. Learning SQL was a journey, but if I knew how simple it all was by the end, I would have jumped on it from the beginning.

Use Single filter for Multiple Data Sources in Looker Studio

We know that in Looker Studio when we add data control like a dropdown selector we need to add a data source and the control only applies to charts with that data source. Yes there’s the option of “Cross-filtering” but that does not work with all chart types like scorecards.

The solution was to create custom fields with the same ID.

Lets say we have two data sources - GA4 and Google Ads and we want to filter for Campaign Name.

1. Create a calculated field in GA4, which is nothing but the the dimension name like Session Campaign. 
2. Make sure you copy the field ID on the top right, which is something like calc_12432435.
3. Now in the other data source, Google Ads; create a calc field and paste the same ID you copied from before. (VERY IMPORTANT TO CHANGE THE ID ON THE FIRST TIME ITSELF AS AFTER SAVING IT IS NOT ALLOWED TO EDIT THE FIELD)
4. You are good to go now, use any of the above calc fields as filters and it will work with both the data sources!

I couldn’t find the article where I learnt this from, but the author was Mehdi Oudjida

So hard to pick one! 😬

I would go for ‘user defined functions’ in Google Sheets.

it’s really magical 🪄

You know when your formula gets clipped in the formula bar? I’m done with those. if the formula is too long and tedious - I convert it to a custom formula.

Endless use cases - starting from formatting the endless ways users write phone numbers into a specific format, splitting campaign names based on different naming conventions, to integrate 3rd party APIs for various tasks.

It also makes debugging formulas much easier and saves a lot of time!

I’ll share one simple example we use: getting all statistical measurements in one simple formula
Instead writing 8 different formulas, I have only this formula, and it can be used anywhere and anytime

=analyzeRange("B2:B31")

to create this:

You all welcome to share more use cases you think a custom formula can solve!

Love a bunch of the ones listed already!

I really appreciate all the IMPORT functions. Sometimes I just need a quicker way to gather all my inputs and be able to organize information in the manner that I want to see it. Some ways I’ve used these in my past is tracking for SEO like metadata changes from websites. I’ve also used it to gather customer feedback/reviews.

• IMPORTHTML - Imports data from a table or list within an HTML page.
• IMPORTDATA - Imports data at a given url that is in cvs or tsv format
• IMPORTFEED - Imports RSS feeds
• IMPORTRANGE -Imports ranges from another spreadsheet

(Also similarly, sharing a nod to GOOGLEFINANCE functions that essentially are import functions for historical and real-time stock market data!)

Blending Data and collating multiple metrics/dimensions under a singular field has been a huge saviour! Using a simple code, I can blend all my tables’ matching metrics under a single field or combine similar metrics - for example:

• If I’m trying to blend impressions across multiple socials for a singular video, I can blend these sources then use the below statement to combine FB, IG and TT video views under the same metric “combined views”
• If I want to pull video thumbnail and image thumbnail under a singular ‘thumbnail’ field, rather than having both columns taking up room in a table and showing a lot of blanks

The formula is:

CASE

WHEN [insert metric 1] IS NOT NULL THEN [insert metric 1]

WHEN [insert metric 2] IS NOT NULL THEN [insert metric 2]

WHEN [insert metric 3] IS NOT NULL THEN [insert metric 3]

ELSE ‘N/A’

END

This solves my problem of having too many columns in a singular table - or I can pull a scorecard with combined values across multiple sources. 

My favorite marketing analytics trick is to use the SupermetricsQueries tab to update ALL the public social media data I need to track at all times. After changing the start date and end date, just click on "refresh all queries" and, like magic🎩, everything is updated! I don't need to go from tab to tab to do this and waste a lot of time. 

For those who use Google sheets integration, you can now directly create a new looker studio report from within Google Sheets.

I recently learned that in Looker Studio you could overcome some of the filtering limitations within a data source by blending it with itself.

In my case, I wanted to have Google Ads data that showed only metrics for a particular subset of campaigns (based on a substring within the Campaign name) and only a specific type of conversion (based on Segment Conversion Category). I set up one instance of the data to pull & filter the “front-end” (engine-side) data and one instance to pull & filter the “back-end” (conversion) data and joined on campaign ID:

This allowed the data to be aggregated and filtered the way I wanted without the errors I was getting when I tried to apply both filters to the overall data set.

Note: I almost certainly didn’t need to include Cost in both tables, but wanted to include it just in case because I’d be calculating CPA.

Instagram ImageURL to your Dashboard

If you already noticed, when you use SM dimension “image url” you receive a temporary image link, as Facebook keeps changing the image diretory.

Try this as a solution:

1. Export the media permalink;
2. Create a new field using this formula CONCAT(Media Permalink, “media”);
3. You’re good to go.

As reels use a slightly different permalink, you need to repalce “reel” for “p” in the URL. So you have this formula that works for feed posts and Reels.

The most common question we always seemingly get asked, and I was definitely a culprit of this question a lot early in my career. “When was this last updated?” 

A simple way to do this is to have a status bar at the top of the page, then using conditional formatting to highlight dates that are unexpected, if before today minus 1 day then highlight in yellow, is one I have used previously. 

A mock up of one of our dashboards is above, where we use logos of the data source and then have mapped the dimension to the logo name. 

The most common question we always seemingly get asked, and I was definitely a culprit of this question a lot early in my career. “When was this last updated?” 

A simple way to do this is to have a status bar at the top of the page, then using conditional formatting to highlight dates that are unexpected, if before today minus 1 day then highlight in yellow, is one I have used previously. 

A mock up of one of our dashboards is above, where we use logos of the data source and then have mapped the dimension to the logo name. 

I love that you managed to blend in this rather dry (but important) bit of info with the overall design of the dashboard. It shows what data sources are included and what their freshness is in one sleek header. 👌 chef’s kiss

Google Array Formula

Google’s Array Formula has been an awesome way to automate spreadsheet processes. If you are doing custom calculations or joining Supermetrics data with other data, you don’t have to worry about copying down formulas in your calculation columns.

For example, if you want to turn Supermetrics’ “Year & month” field, which appears as YYYY|MM, into a usable date format for each row of data. You can create a new column with a formula like =ARRAYFORMULA(IF(dateField = "" , "" , DATE(LEFT(dateField,4),RIGHT(dateField,2),1)))
Note: The IF() formula is important to use as it ensures that if there isn’t any data present, that it leaves it blank. Your spreadsheet and computer processor will thank you for that.

This formula is super handy paired with automatic refreshes from Supermetrics as you can alway count on whatever calculations you have setup in your ARRAYFORMULAs, they are going to automatically resize to the row length of your updated dataset. 
 

My favourite looker studio trick that I discovered some 5 years ago, is using a (somewhat) hidden google API to fetch favicons for basically any domain. It really makes a nice, and more visual, presentation of traffic sources in my dashboards:

I just add the source-dimension to this google url (https://www.google.com/s2/favicons?domain_url=supermetrics.com) and get a png back (sometimes you have to manually adjust some sources so it’s a domain, like if you’re using another naming conversion on utm_source tags for example)

 

We were blown away by the incredible tips and the enthusiastic participation in this first Community Challenge! 👏🏻

The post with the most likes goes to @Erik - Congratulations! Erik showed us how we can use the hidden Looker Studio feature of favicons - This concrete tip is sure to be valuable for many in the future!

Additionally, Bartosz from the Supermetrics team selected Gil's entry, highlighting it as “a hidden gem that would he wished he would have know about years ago.” Congratulations, @Gil!

Again, thank you everyone who participated! Look out for more exciting challenges coming soon. I'll be contacting you, @Erik & @Gil, about your prizes soon!

Instagram ImageURL to your Dashboard

 

If you already noticed, when you use SM dimension “image url” you receive a temporary image link, as Facebook keeps changing the image diretory.

Try this as a solution:

1. Export the media permalink;
2. Create a new field using this formula CONCAT(Media Permalink, “media”);
3. You’re good to go.

As reels use a slightly different permalink, you need to repalce “reel” for “p” in the URL. So you have this formula that works for feed posts and Reels.

 

 

@Filipe, so if I use this formula, the image link will no longer be temporary and I won't have to download these links again?

@Sandrinha that’s right!

I know that this works for assets that are posted in the feed. If you have a darkpost, I’m not sure if it has a permalink.

I don't know what I'm doing wrong. 😓

@Sandrinha pela interface percebi que você fala português rs

O “Post media URL” ainda é uma dimensão que vai carregar a imagem temporária. Utiliza o “Link to post” no lugar dele.

Assim você fica com CONCAT(Link to post, “media”). O “Link to post” vai te retornar o link com o /reel/ quando a publicação for um Reel. Então a formula final você pode usar:

CONCAT(REGEX_REPLACE(Link to post, “reel”, “p”),”media”)

Acredito que essa faça o truque ;)

@Sandrinha I think my previous answear is still under review as i answered in portuguese hehe

The problem here is because you are still using the temporarily image link, Here you want to use the “Link to post” instead. 

Your formula should look like this CONCAT(REGEX_REPLACE(Link to post, “reel”,”p”),”media”)

The regex_replace is making the needed change in the URL so it works with reels as well.

Hope that helps!

Não sei o que estou fazendo de errado, mas certo não está dando… rsrs 

A  ideia é trazer as imagens das publicações no looker, seja em planilha ou de outra forma. 

Um exemplo de link: https://www.instagram.com/reel/C2PjkZjO_NO/

REGEXP_REPLACE(REGEXP_REPLACE(Link to post, "reel", "p"), "media", "")

@Filipe,

I use Sheets to extract data with Supermetrics connectors and build my dashboard in Looker. By extracting the image addresses, I can view them for a few days until Facebook or Instagram change the image address again. I've tried converting the image address to base 64, so I can have a permanent image on my dashboard. However, since there are SO MANY of them, it's impossible to convert base 64 all the addresses.