How To Generate Word Cloud From Search Terms Report In Power BI

There's lots of features being added in Power BI and custom visuals are one cool area to check. 

The data we're gonna use for Power BI is from Google Merchandise Store's Google Analytics>Behavior>Search terms report [Jan 01, 2017-Sep 19, 2017].

Download the Excel/CSV version and load the file in Power BI [or connect to GA API].

Once in, head to Visualizations > ... > Search from store > Type in 'word cloud'.


Load your Search term as the Category and Total Unique Searches under values.

You should now see the keyword cloud loaded with the size of the keyword representing the number of searches. Hover over any of the keyword to view the total unique searches as a data label.


How to find out if fans of a facebook page also like another page of the brand

Here's a situation that might happen to a bid brand where Facebook pages overlap and fans might join one or more pages, thinking that they're on the right page OR

They might be interested in two completely different product pages provided by the same brand.

Finding out audience insights about these pages can show details about either of these pages , but not together. Facebook Ad Manager allows one to check the overlap between two audiences. Knowing this can help you decide on ad set targeting, remarketing or exclusions. What you'll need for this:

1. Access to the pages you want to check. 

2. Access to a Facebook Ad Manager account that is connected to these pages [allowing you to create ads for these pages if you wanted to].

Sign in to Facebook Ad Manager and head to the 'Audiences' tab.

Go to create audience > Saved audience.

To create your audience, in terms of demographics, you need to enter at least one location. If you want to show overlap for all countries, just paste a list of all countries and Facebook will match the country names. You can then decide on the age/gender/language and interest targeting options to decide depending on your requirements. 

The last bit is about connections targeting. Scroll to the bottom and choose connections targeting> Fans of > Name of the page 1. Similarly, create the saved audience for other pages [you can choose up to 5 audiences to check on overlap].

Head back to the main Audiences tab and select the audiences you want to compare > Click Actions > Show audience overlap.

Once you select the pages, you can now see in the above screenshot that 4.5 K users [5% of page 1 fans] are also fans of page 2. 

Knowing the overlap can help multi-page brands in a few ways:

  • Understand if they want to move fans from one regional page to another.
  • Show specific content to users who might meet more specific requirements.
  • Help with co-branding campaigns.

That's it for this blog post. Till next time.


How To Fire DoubleClick Counter Tags For Multiple Countries/Languages More Efficiently In GTM

This post is a combination of my two threads opened on Google Tag Manager forum. Both were answered by Simo Ahava and explains how to do this [Always helpful!].

So we have a multi country/lang website that has DoubleClick counter tags on it [6 countries/up to 3 languages - EN/AR/FR]. With DoubleClick tags created specifically for each market/lang, creating individual triggers for each lang/market can make GTM triggers/tags section look messy. More importantly, if the 6 countries were now expanded to 10-20 countries, would you continue creating 1 trigger per DC tag? hmmm, nope!

Here's an example of the DC tags structure for just AE/EN site


The DoubleClick counter tags are setup to fire on brochure downloads. An example of a trigger would be:

if page path matches regex /ae/en/

         and click url matches regex /brochure.pdf

then fire DoubleClick tag corresponding to Brochure Download for this particular market + lang.

Here's how the DC tag would look like in GTM.


Step 1: Getting the country name and language of the page being accessed.

Here's my first thread on GTM forum asking about how to scoop the country name / lang from the page path and store it as a variable:!topic/tag-manager/nCgNpdRhaR4;context-place=topicsearchin/tag-manager/authorid$3AAPn2wQcoU-Azf0LsKlh6uHfYaSXH0hjoRDDwomZ0MCRNUL9Z7--q74gHU6hcPyRk3OS93PLJsUtZ%7Csort:date%7Cspell:false

Simo's respone to this was to create two JS variables:

JS variable to scoop country name:

function() {

  return document.location.pathname.split('/')[1];


JS Variable to scoop language:

function() {

  return document.location.pathname.split('/')[2];


Since the url taxonomy is  or, using document.location.pathname.split finds the first /and then saves the value (in this case, 'ae' as a string). The second JS variable finds the next '/' in the url and saves it as 'en' - again as a string.


So, having the country name and language currently being viewed as variable values takes care of the parameter - when to fire which tag.

Step2: Associating the right DoubleClick tag for each market/lang. 

Here's the second thread!topic/tag-manager/DHlRuPMj_3s;context-place=topicsearchin/tag-manager/authorid$3AAPn2wQcoU-Azf0LsKlh6uHfYaSXH0hjoRDDwomZ0MCRNUL9Z7--q74gHU6hcPyRk3OS93PLJsUtZ%7Csort:date%7Cspell:false

In this particular DC tag example, the advertiser ID was common for all sites, only type= and cat= parameters changed between individual DC tags. Creating one JS variable for each dynamic value, we would have 6 JS variables,

  1. Brochure download type JS variable.
  2. Brochure download cat JS variable.
  3. Tel clicks type JS variable.
  4. Tel clicks cat JS variable.
  5. Emails sent type JS variable.
  6. Emails sent cat JS variable.


Example, brochure download type= JS variable.

function() {

  var country = {{country variable}};
  var language = {{language variable}};

  if (country === 'ae' && language === 'en') return 'type=string value for ae/en DC tag';

  if (country === 'ae' && language === 'ar') return ...;

  return undefined;


In the above JS variable, the var country and language were solved in the first part of the thread. That's why in this variable, we've used a dynamic value of {{country variable}} the following lines, the script then checks if the values pulled in country/language variables matches both conditions. IF yes, return a type= string value that's specific to the particular DC tag.

Once this is done, you can go to the Brochure Download DC tag in GTM and in the Group tag string (type=) parameter, enter the dynamic tag value {{Brochure download type value}}, same for Activity tag (cat=) parameter, {{Brochure download cat value}}.

Something like this:


Hope this post makes it easier for others. Big thanks to Simo for answering these questions on GTM forum.



Facebook Ads Is Now Flagging Videos That Contain Too Much Text

Back in April, WeRSM reported on Facbeook relaxing the 20% text in image rule to give more flexibility to designers. The new rule gave much needed freedom to creative folks by bucketing the image into either of these four categories:

  • Image text: OK: You ad's image contains little or no text. This is the preferred image style. 
  • Image text: Low: Your ad may reach fewer people because there's too much text.
  • Image text: Medium: You ad's reach may be much lower
  • Image text: High: Your ad may not run

Coming back to videos, I just noticed this notification for a video ad.

Clicking on 'Heavy Text in Image' opens up this notification with the guidance link taking users back to the Facebook help link posted in this article.

This is really important to advertisers for a couple of reasons:

  • 85% of Facebook video plays are on mute. Videos need to be created for the 'silent generation'. Content should be short, easy to consume and understandable even on mute. 
  • Knowing the above figure, this opens to a discussion around how much text/captioning should be included in the video to facilitate consumption of key message(s) while balancing Facebook's guidance around including text.

Have you noticed this notification for any of your videos? How has this impacted the Cost Per Objective for such ads compared to non-flagged ads?





How To Frame Keywords Into Search Related Topics

Here's an interesting post I came across in my Feedly. It's from the guys at Seer Interactive, talking about free tools to identify customers. 

Search is obviously featured in this blog post. Going beyond the keyword planner, search console, GA (site search), the post mentioned about a free tool called Answer The Public. The site takes your topic and shows the most important searches related to that keyword.

How are the most important searches shown?  

Using questions and prepositions.

e.g. For this sample search, my topic was 'breakfast cereals'. The questions around breakfast cereals were build around the what / when / how / why / are / where / which / who.

Some really awesome questions showed up which could be a great opportunity for generic keywords. Yes, these are long-tail but it's natural in terms of keyword flow in query. These are questions being asked by people exploring this topic and are on the edge. Going through some of the questions is like a deep-dive into the mindset of consumers and if, people are getting more health conscious when it comes to breakfast category.

Some great questions here:

Q - Which breakfast cereals are high in protein?

Q - Are breakfast cereals nutrious?

Q - Why are breakfast cereals good for you?

Compare this visualization to Keyword Planner results for 'breakfast cereals'.

After going into Healthiest Cereals ad group, here's what we get:

Lots of ideas but still, not too many long-tail keywords that describe user searches in a more natural way. The best part about Answer The Public is the simple (yet awesome) visualization that puts the questions (and prepositions) at the centre of the search query and then separate the searches that show what's bothering potential consumers on the edge/converted customers looking for a change/addition or non-believers in your product/category (e.g. 'Are breakfast cereals bad for you?')

Answer The Public tool is totally free (as of now, no idea if freemium is something expected) and would definitely recommend playing around with searches. People are already on it




Measuring Audibility In Video Ads.

As video content increases its presence in our feeds, it's become more important to look beyond the aggregate metrics and focus on whether the message is actually conveyed or not. Came across a nice article from AdExchanger about audibility being the new viewability

It's quite true and is a serious topic. hmmm...let's take a look at the two biggest platforms: YouTube (Google AdWords) and Facebook. As surprising as it sounds, both platforms don't offer metrics on audibility yet to know what % of ads were watched with sound on.

YouTube/AdWords: As of today, you can't measure what percentage of your In-Stream or Disoveryads had sound on.

Facebook Ad Manager: Sound related metrics aren't available. You need to get this from Facebook Insights (will be combined for both, paid and organic).

How can you find out audibility related metrics from Facebook Insights?

Not exactly easy to spot.

Go to your page > Insights > Posts > Click on the video post. You should see something like this:

Click on 10 Second Views. You should now see this:

ok, you should definitely see more than that :)

Look at the last bit of info, gem right there. Sound On/ Sound Off. This shows the absolute number and %. Start comparing your recent videos and see what the % is like for sound on. If you're producing rather long-ish form content with the main message being conveyed via sound, the sound on metric becomes even more important.

How far can you go back in your timeline to check on this sound: on metric?

Facebook started providing this metric from Feb, 2016 only...hmmm, gotta live with it.

As Facebook tries to take a bigger piece of the video market, video measurement becomes critical. The standard definition for a view is when a user watches for more than 3 sec. That's not quite long and definitely not enough to know if brand messages are being consumed.

In order to tackle this, Facebook introduced the cost-per-10 sec-view bid besides the default option (impressions). Go to Facebook Ad Manager / Power Editor > Ad Set that has the video targeting > Scroll to the bottom > you'll see the option to bid by 10 sec.

This is a good option to test videos ads with. Although you won't be able to separate the sound:on metric for paid vs organic, if your total views are completely dominated by paid metrics, there's a strong possibility that bidding by 10 sec helped get an audience that heard your brand's message. These are the users who started the video on mute (default) and then cared enough to unmute. 

It's also possible that the effective CPV would be much higher in this test but here's where the education aspect comes in getting everyone on-board in understanding video metrics in details. 5M views may not necessarily be better than 2M views if the latter has more views with sound:on, better retention rate and completion rate.

What do you think about testing this approach for Facebook video ads? Any ideas for AdWords?

Update: Found this article in my Feedly. Facebook now testing ads with auto sound-on. Here's the link:


[FREE GA Tool] GAUPET: Google Analytics User Permission Data In Pivot Tables

Just found this tool from David Vallejo's blog [he created it] and thought it's really handy for agency folks.

GAUPET stands for Google Analytics User Permissions Tool and it does exactly what it says.

All you need to do is connect your GA account on this tool

and create pivot tables out of it. Something like this:

As you can see in the above example, I've dropped two fields under rows [account ID and GA ID] while columns has email address. For my personal GA account, it's much simpler to know this but with agency accounts, this can really help show governance rules setup in GA properties [or view level], especially when you want to check account level access [also included in this tool]. Something like this:

Be sure to try the visualization features to switch between bar charts/area/columns and heat map shown below.


Best part, it's FREE so that's always a good thing. Here's the full list of fields:

The included fields are:

  • Email Address
  • Email Domain
  • Access Level
  • Account ID
  • Account Name
  • Account Access Rights
  • Account Permissions
  • Property ID
  • Property Name
  • Property Access Rights
  • Property Permissions
  • View ID
  • View Name
  • View Access Rights
  • View Permissions