Data Studio – Quick Tips (Advanced)

Speed Up Data Studio Reports (Significantly) – Extract Data

To speed up your reports – you can “Extract Data” and cache it.

It can help to have 2 copies of the report up – so you can see which metrics and dimensions you need to select when adding the data to extract and cache (also a good idea to test the extract data method on a copy of the report in case you faff anything up)

Go to “Add Data” in the top menu-bar

  • Click on “Extract Data”
  • Choose the data you need – eg Google Analytics
  • Add the dimensions and metrics you need for the report
  • On the Right hand side – click to turn “Auto Update” on
  • Select “daily”
  • Click “Save and Extract”

Sometimes you have to faff around a bit with the dimensions – Google Analytics doesn’t seem to like caching a dimension, but still goes super-quick if you cache the metrics only.

Edit in Bulk

If you want to edit all of the charts or tables on the page, in “Edit” mode, right click – go to “Select” and then choose “Tables on page” or whatever type of chart, scorecard or table you’ve selected.

This works instead of CTRL clicking or SHIFT clicking – but you can only change charts or visualisations of the same type at the same time. You can change the style, add a comparison date range etc.

Brand Colour Theme in Data Studio

Click on “Them and Layout” at the top of the screen and then, near the bottom right click “Extract Theme from Image” – you can then upload your logo and choose a theme with your brand colours.

If your shite at presentation like me, this is helpful.

Copy & Paste Styles

In Data Studio – If you want to copy a style from a chart or table, right click it, then choose “copy”

Click another chart/table and the right click – Paste Special – Paste Style Only

Add Chart Filters to an Entire Report

If you want to add a filter to all the data in a report, then it can be a pain going through the charts individually.

Right click on a blank part of the page –

  • Click “Current Page Settings”
  • On the right hand side – click “Create a Filter”
  • Choose or create a filter to apply to all the page

To add a filter to multiple pages

  • Right click on a blank part of the page
  • click “Report Settings”
  • click “Add a filter” in the right side-menu

Add Elements to All Pages of a Report in Data Studio

If you want to add a header and date range selector, for example, to all the pages in the report – add the elements to a page, then right click on the element – and choose “Make report-level”

Quickly Align Elements in Data Studio

Click and drag to select all the elements

Right click – choose “align” – “middle” to get everything inline horizontally

To get an equal space between all the elements, so they’re spaced evenly:

– click and drag to select the elements

– right click – select “Distribute”

– “horizontally” to space evenly across the page, or “vertically” to distribute evenly in a vertical manner.

You can also tidy up individual tables to align the columns vertically – right click and select “”Fit to data”

See our Data Studio Fields & Filters blog post – https://businessdaduk.com/2021/12/15/data-studio-fields-filters-notes/

Data Studio – Data Blending

Bit of a ball ache to work out

There are a few ways to blend data, here’s my fave:

  • Go to “Resources” in the main menu at the top
  • Click “manage blended data” option
  • Click “Add a Data View”
  • Choose a Data Source e.g. Search Console
  • Then “Add a Table” and include another data Source for blending – e.g. GA
    or click “blend data” on an existing table or chart – and select another data source
  • Choose a common “key” to both data sources e.g. “Date”
  • Choose the metrics you want from each Data Source – I wanted to get daily revenue into my search console reports:

Using the blended data above, I can now add Revenue from Google Analytics to my search console reports. I have to remember however, that the revenue is simply attributed to each day and not any queries

**Update to the screenshot –

add a table filter to get organic only revenue from GA.

To be able to filter Revenue to organic only – you need to add a “Dimension” to the table on the right – click the “+” next to “Add dimension” in the GA data and then “Default Channel Grouping” – you can then create a filter in the report:

Blending Search Console Data in Data Studio

Another common reason to blend data – is to get average position data from Search Console “Site Impression reports, added to “URL Impression” data:

URL impression vs site impression

Incidentally –

the main difference between Data Studio Search Console URL Impression Vs Site Impression data – is that Site Impression contains the Average Position metric and URL Impression contains the Landing Page metric. So when you’re blending the data from both sources, make sure you have “Landing Page” as a metric and “Average Position”.

Data Studio Fields & Filters – Notes

Once you’ve created a field – add it to your chart as a Dimension

Mixed Case URLs

Having a mix of URL cases in the letters can faff with your data as D.S. might think each capitalisation variation is a new URL:

  • Click on the Data Tab to the right – then “Create new field” or “Add a Field”
  • Name the field “Lower Source” type in “LOWER(“
  • Then add “Source” from “Available Fields” to the left
  • Click “Save” and add “Lower Source” to the table as a Dimension

Concatenate Data

  • Create or “Add a Field”
  • In the Formula box type “CONCAT” and then select the fields you want to use
  • Close the formula with the final “)” and save it

Internal Site Searches – Extract Search Query

Make the data look nicer and get the search term on its own in the table.

  • Select to a “Add a Field”
  • Use REGEXP_EXTRACT formula to pull out the search terms and get rid of “/search?q”
  • Use the REGEX shown below:

Search Queries – Pull Out Questions – What, Why, When?

  • Create / Add a New Field
  • Add REGEX as shown below and save
  • Add New Field as a Dimension to the table
  • Create a table/data Filter so that you include only table rows that equate to “True” in the new dimension/column

CASE Formulas in Data Studio

CASE formulas are basically “If this, then do that” formulas

When X happens, Then do Y

You can use the CASE Formula to classify and group channels together

For example:

WHEN query matches “who”, then display text “Who?” in the table

  • In the Formula – you need a “catch all” default for when nothing is true in the criteria
  • If the search doesn’t contain “who, what, why” etc. then:
    ELSE “others”

    So if the search term, doesn’t match any of the REGEX criteria – classify it as “others”
– Save the CASE statement
  • Add the new field – to the table or report

Date Ranges & Filter Controls

Taken me ages to work this out – I’ve only just twigged:

You can add Comparisons, Search Boxes, Drop Down Menus and all sorts to your Data Studio Reports

For controls and filters to work – you need the chart or table to have “Default Date Range” set to “Auto”

See my post about REGEX for SEO here.

Ta

If you want a pre-made Data Studio template, you can send me some money and hope for the best.

Online Marketing for New Businesses

Started a business?

Got a website?

Okay, now you need to look at building your brand and getting traffic and engagement on your website and social media accounts.

Website Checklist

  • Trust signals – include membership badges, university logos prominently
  • Humanize the site – include images and videos of people who work for the business
  • Social Proof – include reviews and testimonials. TrustPilot and video testimonials work well
  • Site Speed – make sure the site is quick and works perfectly on mobile devices
  • EAT – Expertise Authority & Trust – show your credentials on the about us page
  • Contact Form – Make it as easy as possible to be contacted
  • Trust & Transparency – Include full contact info if possible – address, tel number, email
  • Have you got Google Analytics & Search Console installed?

You will also need to think about the colour scheme and imagery.

Think about what mood you want to portray

https://www.bedtracks.com/blog/2017/6/15/how-to-coordinate-music-with-the-colour-in-your-video

  • Strong Call To Action

You will probably want a “Call to Action” or “CTA” button, such as “Buy Now”, “Learn More” or “Contact Us”

This CTA button is generally placed “above the fold” on most pages, so that people don’t have to scroll down or look for a way to get in touch or buy from you.

“Join Free for a Month” – is the CTA on Netflix’s homepage (at the time of writing)

For more expensive, high end or though-out purchases such as – buying a car or contacting a therapist, sometimes it’s better to have the CTA below the fold. The best thing to do is test it, with Google Optimize.

For more information about “Conversion Rate Opimization” (CRO), see this article:

https://blog.hubspot.com/marketing/conversion-rate-optimization-guide

For a full SEO (Search Engine Optimization) checklist for your website – to help get visibility on Google – see this article – https://backlinko.com/seo-checklist

Google My Business

Register your website and your office with Google My Business

You can go through the steps here:

https://www.google.com/intl/en_uk/business/

Google will send out a postcard to your office (or home) address

The postcard has a code – so you can confirm you are at that address

Local Directories

Register your business with high quality, local directories such as

  • Bing Places
  • Yelp
  • Yell
  • Free Index
  • Open Di

Try and get on any local government directories too.

Social Media & Captioned Videos

If relevant, register your business on:

  • Linkedin
  • Facebook
  • Instagram
  • TikTok


    Arguably the best way to get noticed on social media at the moment, is to create videos with captions – so they can be watched on mute.

Linkedin is said to have the greatest organic reach at the moment too – meaning you can get your video, image or text-post in front of more people, without paying for ads.

Social media sites like people posting videos too – because they drive a high rate of engagement and keep people on the site for longer.

  • Do NOT post to YouTube and then post a link on social media

Instead – upload your video direct to the platform.

Social media sites will tend to kill your reach if you post a link – they don’t want people to click and leave their website

YouTube is also showing on more and more Search Results Pages on Google.

Consider creating a YouTube channel with lots of informative, helpful and entertaining content. You can then edit the videos and post to specific social media platforms.

Jab, Jab, Jab – Right Hook

General principle of content and social media marketing by Gary V.

Identify your target market

Identify their issues and pain points

Post helpful content related to their pain points and problems

Do NOT constantly promote your business – slip in the odd “Right Hook”, every 3 or 4 posts

People do not want to be sold to constantly, they want helpful, insightful and funny content.

For example.

If you target market is small business owners, take a look on Quora and Reddit and see what people are talking about. If a common theme is Facebook advertising for example, make some helpful videos and blog posts about Facebook marketing.

SEO, PPC and More

The above is just a foundation.

If you have the time and resources, you will ideally produce lots of insightful blog content, earn lots of inbound links and work your way to the top of Google.

You will also want to consider “PPC” – Pay Per Click ads on Google, Facebook and Linkedin.

One beginner mistake to avoid with ads – is sending people to your homepage.

Have a specific “landing page” for each advertising campaign.

oh – make sure you have a good looking logo too. You can use Canva or hire someone on PeoplePerHour.com

A good place to start with SEO is to check your website using an On-Page SEO Checklist.

Videos are great for social media, and YouTube is also starting to show more and more often in the Google results. I would personally have a good go at gaining an online presence using videos and social media – particularly Linkedin at the moment.

Build a Brand

Here’s a good article that some hero wrote about building your brand as a small business

  • Nail down your USP
  • Identify other propositions “why use me/us and not the competitor?”
  • Write down your brand story
  • Use high quality photography & videography (avoid stock pictures)

Consider making customer support a key element of your brand – this can help with online reviews too. Pre-purchase, purchase and post-purchase consumer stages are all opportunities to impress and help.

Emotional Intelligence for Beginners

Emotional intelligence is the ability to perceive and control emotions, without impulsively acting upon them.

I also think of “emotional intelligence” as the ability to detach from the ego and your immediate, impulsive responses and choose a logical, kind or practical way to act:

The Terminator – choosing a reply. Height of E.Q.

Opinions & Emotional Intelligence

  • When something or someone contradicts a pre-conceived belief – your body kicks out a stress response with cortisol and adrenaline

  • When you force your opinion on someone, your body gives you a ‘dopamine reward’

If you take nothing else away from this post, please read those two bullet points above again!

Be aware of the stress response next time you get into a debate

For example, if you have any elderly parents or grandparents (just as an example from my own experience), who have always been told that being homosexual is “wrong”, they’ll usually complain or just be unable to watch the gay couples in Strictly Come Dancing.

If you question why they have a problem, they’ll rarely come up with a logical answer. They’ve been taught that being gay is wrong, so they’re body is producing stress hormones – which makes them feel pissed off for no logical reason.

If you don’t like someone or something, and you can’t explain why, chances are you’re being a dick!

This stress response can be heightened if your opinion is tied to your identity, or your group identity.

For example, if someone is a devout Tory, they’ll use “motivated reasoning” and any possible confirmation bias possible to defend Boris.

More info here – https://www.psychologytoday.com/gb/blog/how-risky-is-it-really/201007/why-changing-somebody-s-mind-or-yours-is-hard-do

This physiological reaction can also be roused when a global belief is threatened – for example, that the world is safe or ethical.

Tell people for example, that Nazi scientists were responsible for putting the first man on the moon and you may get some funny looks or reactions.

Inconvenient truths or questions can be another one. Tell people that lead water pipes are still around in the UK or that there’s a decent amount of evidence that fluoride in water is bad for the brain and nervous system – and there’s a decent chance people will get angry or ridicule you.

Recent study on fluoride here – https://bmcpublichealth.biomedcentral.com/articles/10.1186/s12889-020-09765-4

You sometimes see a similar reaction when someone lower down in a hierarchy, be it an official one or a unwritten one, makes a suggest to someone higher up- its often met with a kind of frustrated dismissal.

Confirmation Bias

More of a logical fallacy – but it also crosses over into emotional intelligence.
People will for example, decide to behave in a certain way, and then later justify it by ignoring any information that contradicts theirbehaviour, whilst cherry picking any information that supports it.

For example – although I appreciate there’s now definitely a valid argument to ignore lockdown rules, people who choose to ignore lockdown in 2020, justified it because Sweden didn’t have one.

People who wanted to adhere to lockdown on the other hand – would point to the terrible situation that happened in Italy, and India.

Who is right – is up for debate I guess.

got kind of weird when my left wing mates started sharing Daily Mail articles though.

Confabulation

We’re all in different movies!

Confabulation is the creation of false memories, without intending to deceive or lie. Subconsciously, people’s egos or minds will create a false account of an event, that makes them look more favourable.

Being aware that you’ll do this on a subconscious level – can make you more conscious of bull sh!tting and making stuff up. Sometimes it’s like people are in different realities – if the event was on TV – they’d be completely different movies or TV shows!

You see a similar skewing of reality in real-time too. Arrogant people will skew reality to make out others are stupid or are at fault.

What Your Criticise Represents Your Own Insecurities

Similar to the stress response created when a pre-existing belief is threatened, if your own lifestyle choice is contradicted by someone, that can also create a stress response.

For example, I always get ear ache around Christmas for sticking to a strict diet (I’ve got IBD).

Sticking to a strict diet, makes other people feel bad about eating unhealthy food, and to compensate they’ll often mock or ridicule me.

In a similar fashion, I’ve been called obsessed with myself and all sorts, by people who don’t exercise, because I used to go to the gym in work every lunch time. (sorry, this section is turning into a bit of a personal vent/rant, ironically).

Being aware of this phenomenon, can help you to acknowledge your stress response to a threat, without acting upon it

Meditation is said to be one of the best way to build emotional intelligence. If you can learn to observe and detach from your thoughts, then you can choose not to act on them.

There are some good books that can help to, such as the Chimp Paradox or Dan Harris’ book – 10% Happier. If I remember rightly, he originally wanted to call the book – “The Voice in my head is an Ass Hole”.

The Wisest Man Knows how Little he knows

The Dunning Kruger effect – relates to ill informed or incompetent people, thinking they’re great at something and being over confident. Like people who’ve never done MMA thinking they’d be great at it.

don’t be that guy:

Get Content Ideas from Competitor Websites (SEMRush)

Requires:

  • SEMRush
  • A computer
  • The internet

If competitor has articles and blog posts inside a subfolder e.g. “buyers-guides” or “/blog” – make a note of the sub-folder name

  • Add Competitor’s homepage URL in “Search Bar” on SEMRush Homepage
  • Click “Organic Research”
  • Click on the “Positions” tab
  • Click on “Advanced Filter” and add the subfolder name e.g. “blog”
  • Export Results into Excel
  • Create a Pivot Table
  • Use the settings below – you’ll need to change position to “average” instead of “sum of”:
  • Tick the check-box for “URL” at the top
  • Drag search volume, traffic and position into “Values” box at the bottom right
  • Click the little arrow on the right of “Sum of Position” – go to “Value Field Settings” and choose “average”
  • Analyse which articles get the most traffic (approximately) and have most potential

Obvs. the URLs of my competitor have been blacked-out in the image above

If the competitor has all their articles at the root domain level e.g.

https://barbend.com/best-weightlifting-belts/

Just use SEMRush – Organic Research – Positions tab and download and pivot the pages data – no need for advanced filter

  • Once you’ve found the blog posts with the most traffic, you can analyse the “Exact URL” in SEMRush
  • This analysis, should show you the keywords on the page that generate most of the search traffic
  • I personally like to go after KWs with a Keyword Difficulty score of less than 20 for my personal blog and under 30 for my employer’s blog

You can also use Reddit & Quora for Content Ideas

Unsolicited #SEO tip: You can get great ideas for specific content ahead of features like PAAs being generated by using Google site operators with specific sites. For instance, I can use the command:

site:reddit[dot]com/r/amateur_boxing “how do i”

To search just the amateur boxing subreddit for questions starting with “how do I?” You can apply this on any niche or on other sites like Quora to get up to the minute questions people are asking.

https://www.linkedin.com/posts/markseo_seo-activity-6902220002146275329-MNVS/

How to Create a Topic Cluster for SEO

Using Ahrefs with Wikipedia

  • Find the most relevant Wikipedia page, for example:

https://en.wikipedia.org/wiki/Association_football

  • Add the Wikipedia URL and click “submit”
  • Click “Copy to Clipboard”
  • Paste all the keywords/topics into a Google Sheet and remove any that aren’t relevant
  • In Ahrefs (or a similar tool like SEMRush’s KW Manager) – go to KW explorer
  • Paste in the keywords/topics
  • In Ahrefs – click “matching terms” then “Questions”

Using SEMRush with UGC

  • Copy the homepage URL of a UGC* website such as Pinterest or Quora
  • Put the URL in the main “search bar” in SEMRush
  • Go to “Organic Research” using the sidebar
  • Click “Positions”
  • Add a Keyword to the “Filter by Keyword” search box
  • Click a relevant Keyword in the table of results
  • Under “Questions” click “View all….”
  • Go back and click other relevant keywords
  • Click the “Questions” tab again – make a note or download all the relevant questions
  • Organise KWs and questions into main pillar posts and “cluster content”

See Hubspot’s article about Pillar Pages and Cluster Content – here

*UGC – User Generated Content

Sorting XML Sitemap URLs by Folder Depth

This can be handy if for example, you only want a list of products and they reside on folders that are 3 “/” deep into your URLs/Domain

For example:

  • Myshop.com/categorypages/subcategorypage/productpage/

I only want the URLs that reside at the third level – i.e. /productpage/

  1. Go to your XML sitemap – usually at Myshop.com/sitemap.xml
  2. Right click and “save as” – save on your computer
  3. Open Excel
  4. Go to the Developer Tab (you might need to add this as it’s not there by default)
  5. Click “Import”
  6. Browse to find your sitemap.xml and import it into Excel
  7. This usually pulls all your URLs into column 1 and other info like priority into separate columns
  8. Delete all the columns except the first one with your URLs in it
  9. Remove the https:// from the URLs with “find and replace” – On “Home” tab under “Find & Select” on the right
  10. In cell B2 add the function: (change A2 – to the cell you have put the first URL in)
=LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))

11. Drag the formula down the rest of column B

12. You can now order column B by the number of “/” found in each URL

If different categories have different folder structures then you can conditionally format and use different colours for different categories and then do a multiple criteria sort – by colour, then folder depth (column B)

You can download an example spreadsheet with the formula in here

Setting Up Keyword Tracking in Google Ads

How to Use the Excel Template (in Google Ads Editor)

  • Export KW view from Campaign or ad group you want to ad tracking template too
  • Fill in the columns shown in sheet 2
  • Copy & Paste the Formula, as values, into the Tracking template column.
  • Find and Replace “space” with %20 in the Tracking Template column
  • Delete the ‘working out’ columns without headers and the formula column. Copy the populated rows and columns. And import into editor
  • Select all with headers – go to Editor – Account – Import – paste from clipboard.
  • Make sure no campaigns or anything are added (just add the tracking!)

When I used this in 2020, Editor would crash if I tried to upload more than 1 campaign’s tracking at a time. So it took ages!

How to Get More Followers on Linkedin (2021)

I’ve made some notes from a few videos on YouTube – thought I may as well turn it into a blog post…

Summary – Get More Linkedin Followers to Your Business Page

  • Pay for ads to get up to 1,000 followers to begin with
  • Get employees to like and share
  • Get employees to create own content & tag company page*
  • Don’t post too many links (links kill reach)
  • Don’t post about the company all the time – where’s the value?
  • Do post useful content that helps target audience – provide value
  • Engage in relevant groups
  • Post videos with transcripts – upload direct to Linkedin
  • Reuse videos that are under 1 minute long for YouTube Shorts
  • Super-admin – can invite up to 100 members per month
  • Tag customers and other businesses in your post
  • Share and comment on trending topics/news
  • The higher up in the business an employee is, the more engagement it will tend to get if they share company posts**

*Employees could create videos, shares quotes etc. doesn’t have to be perfect and professional, as it’s the user’s content not the company’s

**People use Linkedin for networking/brown-nosing, so the more influential a person is, the more engagement they will tend to get

Notes from videos:

Linkedin

  • Linkedin has the Most potential for organic reach at the moment
  • Use Linkedin Polls for engagement
  • Engage and post in relevant Linkedin groups
  • Don’t overdo self promotion

  • Find out what target audience are interested in – post about that
  • Don’t post about your company all of the time
  • Post 4 to 5 times per week
  • Post 25% about your service or product – 75% relevant, useful, informative content
  • Run ads and add the “follow button”
  • Get employees to engage with content – make sure they optimize their profiles and networks too

  • Get employees to share the posts on their personal profiles
  • Invite connections to like the business page (you have to be a page admin)

  • Define perfect follower/customer persona
  • Determine the needs of the perfect follower – personal and business life
  • What is their company/employer’s needs?
  • Become an authority in the niche by posting informative content that addresses their needs
  • Get your employees to engage and share posts
  • Get employees to share but with their own commentary
  • Get employees to reply to comments on your posts
  • Don’t just promote the company
  • Get employees to share their own unique content that is of value and 20% of the time promote your business – and tag company page
  • Create an industry specific Linkedin group and invite people – link group to company page so you can post as company
  • In the group create 90% value posts and 10% promotional posts
  • Linkedin Ads – target specific people, area, industry, job title etc. – use ads to get 1,000 followers to help organic reach afterwards

Notes from Reading Articles about Getting More Linkedin Followers

– social media platforms, want to be content platforms:

  • More videos (with in-video transcripts),
  • Business related Memes/quotes (scannable content, nobody really reads posts on FB or Linkedin)
  • Publish more posts without links (platforms tend to kill your reach if you link out to other sites, apparently),

– Whilst content that is posted directly to a social media platform will tend to be amplified, if it is hosted externally

e.g. on Youtube, the reach will be killed by the platform – they don’t want people to leave their site/app via links.

  • create “micro-content” for social media, by turning videos, webinars etc into smaller content
    e.g. quotes from videos into images and memes, longer videos into 30 second clips with in-video transcripts.
  • Targeting specific locations?  Use search function on Linkedin and Instagram to comment on relevant posts.
  • Incentivise employee engagement – top 3 people with most likes and shares each month get £100 voucher or some company stash – emphasise it is completely voluntary

Twitter

  • Use Twitter advanced search to find relevant questions and topics to engage with.
  • Twitter – find relevant influencers – engaged with (and potentially follow) people who engage with the influencer’s tweets. These people will tend to be relevant and active on Twitter