Tap into the power of segmentation with hit-level Google Analytics data!

Success in marketing is all about matching the right content with the right audience. You need segmentation for this.

Segmentation is what allows you to zoom in on your target group. It allows you to identify, then target, high-performing users whilst at the same time improving or removing low performers.

Measuring the total conversion rate is a start, but you should also break down your data to see if some users convert more than others.

For example, if you find that desktop users convert more than mobile users, you could adjust your campaign target-audience accordingly or — even better — make your website more mobile-friendly.

If you optimize systematically across many user groups (geographical location, content viewed, visit frequency, previous purchases, basket abandonment, etc.), imagine the effect on your overall campaign effectiveness and conversion!

Segmentation with hit-level data

While it is possible to segment users in Google Analytics, the data will often be sampled and, therefore, not necessarily accurate. In addition, there are a number of limitations to custom segments in GA such as a maximum date range of 90 days.

Fortunately, it is possible to avoid all of this!

Simply signup for and implement SCITYLANA (it’s free), pull out data from your GA account and transform it into a raw, hit-level form. Next, load the data into a self-service BI tool such as Power BI, Excel, Qlik, Tableau or Data Studio / BigQuery.

As it turns out, segmentation becomes easier, more powerful and more actionable once data is loaded into one of these tools.

In the rest of this post, I will walk you through how segmentation works in Power BI. You can use any of the tools, of course, but Power BI is free, easy to use and has great segmentation features.

Custom segments in Power BI

If you haven’t done so already, go ahead and download our free Power BI template for GA data extracted through SCITYLANA.

If you are not a SCITYLANA user, you can download a Power BI Desktop file with demo data here (requires Power BI Desktop).

Here is an overview of the main segmentation options once you have loaded your data into the template:

  • Date range selectors
  • Slicers
  • Multi-level filters
  • Calculated columns


Date range selectors

You can add as many slicers as you like directly to your dashboard (i.e. none of these limitations), and you can choose between different types of slicer, including date range selectors.

Simply select the Slicer icon, then drag & drop the relevant field onto the Slicer visual.


If you drag the Date field onto the visual, Power BI will automatically turn the slicer into a date range selector. You can switch between different types such as relative period, fixed period or lists with weeks, months, quaters and years.


Dimension slicers

Suppose you want to zoom in on visitors who entered your site through Paid Search, used a mobile or tablet device to browse your content, was situated in Copenhagen and had never visited your site before.

Would that be possible…? Absolutely!

Simply pull in the dimensions you need, turn them into slicers and select the relevant values.


Notice the logic behind these selections. Values selected across slicers are joined with AND logic (e.g. “Display” AND “Copenhagen”). Values selected within the same slicer are joined with OR logic (e.g. “mobile” OR “tablet”).

Multi-level filters

Multi-level filters work basically the same way as slicers. The difference is you can specify which report level they should affect. To do so, pull in the filter dimension to either the visual-level, page-level or report-level area under FILTERS:


To some extent, you can also define filtering levels for date range selectors and slicers. By default a slicer will affect the entire page – however,  you can overrule this setting by clicking Edit interactions of the Home ribbon (learn more here).

Calculated columns

Suppose you want to build a new variable, which doesn’t already exist in GA. Suppose you want to recode the deviceCategory dimension so that it only has two values instead of three:


You can do this by adding a conditional column in Power BI using DAX in much the same way as you would do in Excel.

Simply right-click on the Scitylana table under FIELDS and select New column:


Now the DAX formula bar will appear:


Enter the following text:

deviceCategory NEW =
IF ( Scitylana[deviceCategory] = "desktop", "desktop", "mobile/tablet" )

… which reads: If the deviceCategory variable equals “desktop”, then “desktop”, otherwise “mobile/tablet”.

And that’s it!

You have successfully created a new column which you can use as a slicer or as input in a chart:


[Download a demo Power BI file with all DAX examples in this post.]

More advanced segments

The IF() function is great if you only have a few items in your conditional expression. If you have many, however, you can take advantage of the SWITCH() function. With this function you avoid writing complex nested IF() expressions.

Suppose you want to divide all the US states into US regions: Northeast, Midwest, South and West. You can do so with the following SWITCH() expression:

US Region =
    "Connecticut", "Northeast",
    "Maine", "Northeast",
    "Massachusetts", "Northeast",
    "New Hampshire", "Northeast",
    "Rhode Island", "Northeast",
    "Vermont", "Northeast",
    "New Jersey", "Northeast",
    "New York", "Northeast",
    "Pennsylvania", "Northeast",
    "Illinois", "Midwest",
    "Indiana", "Midwest",
    "Michigan", "Midwest",
    "Ohio", "Midwest",
    "Iowa", "Midwest",
    "Kansas", "Midwest",
    "Minnesota", "Midwest",
    "Missouri", "Midwest",
    "Nebraska", "Midwest",
    "North Dakota", "Midwest",
    "South Dakota", "Midwest",
    "Wisconsin", "Midwest",
    "Delaware", "South",
    "Florida", "South",
    "Georgia", "South",
    "Maryland", "South",
    "North Carolina", "South",
    "South Carolina", "South",
    "Virginia", "South",
    "Washington D.C.", "South",
    "West Virginia", "South",
    "Alabama", "South",
    "Kentucky", "South",
    "Mississippi", "South",
    "Tennessee", "South",
    "Arkansas", "South",
    "Louisiana", "South",
    "Oklahoma", "South",
    "Texas", "South",
    "District of Columbia", "South",
    "Arizona", "West",
    "Colorado", "West",
    "Idaho", "West",
    "Montana", "West",
    "Nevada", "West",
    "New Mexico", "West",
    "Utah", "West",
    "Wyoming", "West",
    "Alaska", "West",
    "California", "West",
    "Hawaii", "West",
    "Oregon", "West",
    "Washington", "West",
    BLANK ()

The above expression reads: If the variable region equals Connecticut, then Northeast, else if region equals Maine, then Northeast [….], otherwise Blank.

[Download a demo Power BI file with all DAX examples in this post.]

This new calculated column can now be used as a slicer in Power BI, enabling the end-user to zoom in on the region he or she is interested in. For example, in the setup below, selecting Northeast will make the map dynamically zoom in on this region:


Now, suppose you want to build an even more complex segment. Suppose you want to create a variable which shows a specific geographical market division.

For example, you might want to create an overview of the US as your main market and Europe, Asia, Oceania and the rest of Americas as secondary markets.

The challenge here, however, is that whereas Europe, Asia and Oceania are continents, the US is a country belonging to the continent Americas.

To get an overview of your markets, you’ll have to create a new calculated column which draws on both Continent and Country.

You can do so by using SWITCH() in combination with TRUE():

Market =
    TRUE (),
    AND ( Scitylana[Continent] = "Americas", Scitylana[Country] = "United States" )"The US",
    AND ( Scitylana[Continent] = "Americas", Scitylana[Country] <> "United States" )"Rest of Americas",
Scitylana[Continent] = "Africa""Africa",
Scitylana[Continent] = "Asia""Asia",
Scitylana[Continent] = "Europe""Europe",
Scitylana[Continent] = "Oceania""Oceania",
    BLANK ()

Notice how the TRUE() function allows for logical expressions in your conditions, including not only the equal operator, but also the AND operator.

[Download a demo Power BI file with all DAX examples in this post.]

Again, the new variable can be used as a slicer or as input in a chart:


Beware of filtering scope

Now that you know how to use segments in both basic and advanced ways, you should know that variables in Google Analytics (dimensions and metrics) have different scope:

  • User scope
  • Session scope
  • Hit scope
  • Product scope

When building segments, user-scope variables filter users, session-scope variables filter sessions, hit-scope variables filter hits and so on.

For example, since the dimension Continent is session scope, selecting Europe will give you all sessions from Europe. Even if a user has previously visited your site from the US, you will still only get the sessions he or she made from Europe.

You can get an overview of GA variables and their scopes available in Scitylana here.

Scope is important because sometimes you want to filter a unit which is different from the variable’s scope.

Suppose you want to  build a segment with converted users, i.e. users who have completed one or more goals. The challenge here is that goals are hit scope, so a simple filter expression such as goal1Completion > 0, will not give you converted users, but only the specific hits that count as goal 1.

In my next post I will explain how to overcome this problem, how to give your goals and other hit-scope variables a broader scope.

Retroactive goals with your raw Google Analytics data

Goals in Google Analytics are not retroactive. They only start showing data once you have configured them.

So, if you forget to set them up, this will happen:

But don’t despair!

If you have downloaded your raw GA data with Scitylana, you can use tools like Power BI, Excel, Tableau, Qlik or similar to show goals retroactively.

Once the data are outside of GA, you can define or re-define your goals on the fly and see the effect immediately on the entire time range.

How to set up retroactive goals in Power BI

You can use Scitylana with any BI tool. However, here we will show you how to set up goals in Power BI simply because we know this tool better. It’s easy, even if you don’t know DAX.

  1. Open the Power BI file with your raw GA data
  2. Right-click on the Scitylana table under Fields and select New measure
  3. In the formula bar above the reporting canvas, enter this DAX expression:
Conversions = CALCULATE ( [Users], Scitylana[Page] = "/thank-you" )

But remember to replace “/thank-you” with the URL of your own conversion page!

And that’s it!

You have a new measure called Conversions which you can drag and drop into a chart, break down on time, traffic sources, geography and more – and it works instantly on all of your historical data.

Switch your base metric instantly

In the example above, we calculated the goal in terms of users, not sessions. We wanted to know how many users have seen the thank-you page.

If you want to calculate by sessions instead, simply replace [Users]  with [Sessions]:

Conversions = CALCULATE ( [Sessions], Scitylana[Page] = "/thank-you" )

Turn your goal into a percentage

You can also express your new measure as a percentage and turn it into a conversion rate. To do so, simply create a new measure called % Conversions and define it as the number of converted users (i.e. your original measure) divided by the total number of users:

% Conversion = [Conversions] / [Users]

Notice how we use the original measure within the new measure, which is a nice, powerful feature of DAX.

Conversions divided by users gives you a decimal number, but you can easily change format to percentage from the top Modeling ribbon.

Select the new % Conversion measure, then click the % icon:

Add as many filters as you like

Suppose you want to define your goal as the number of users who have seen the thank-you page with a mobile device while staying in London.

No problem!

You simply add more filters to your CALCULATE () function:

Conversions =
    Scitylana[Page] = "/thank-you",
    Scitylana[DeviceCategory] = "mobile",
    Scitylana[City] = "London"

… and you can add as many as you like!

Troubleshooting: Beware of your regional settings!

Please notice that the regional setting of your computer affects the required format of DAX (your goal expression).

In the above we have used US / UK regional setting, which requires you to use comma ( , ) as the list separator and period ( . ) as the decimal point.

If your computer has a different regional setting, you must use semicolon ( ; ) as the list separator and comma ( , ) as the decimal point. In this case, your goal expression would look like this:

Conversions = CALCULATE ( [Users]; Scitylana[Page] = "/thank-you" )

Next step

Now that you have created your own conversion measure and conversion rate, it’s time to add it to the Power BI template.

You can create a blank page dedicated to goals. Here you can insert a KPI chart that shows the status of your goal relative to a target.

You can also insert a combined line and bar chart that shows how your total number of users and conversion rate develop over time. The visualization possibilities are almost endless.

We hope you have enjoyed reading about how GA goals can be defined retroactively within Power BI. As always, we welcome any comments or questions you might have.