One of the most common pitfalls is summing up unique values, such as reach. Reach is defined as the number of individuals that the pages' content reaches. This presents an interesting challenge as well as a common pitfall when we are examining reach over a period of time.

While reach over one day is fairly straightforward, how should reach be analysed over 3 days? The most common mistake is summing up the daily values.

Reach is a unique value in the sense that it represents individuals reached over one day. If 100 people are reached on Monday, 120 on Tuesday and 30 on Wednesday, this does not mean that our reach over the period of these 3 days is 250 people. The reason for this is that it could be that some people who visited our page on Monday, may have also visited on Tuesday.

The above example illustrates that by summing up unique values we'll lose the uniqueness. The image below may also help illustrate the importance of unique values.

Though reach is not the only unique value that may be encountered, it serves as an adequate example in this case.

There are some ways to counter this obstacle. One of them, that is specific to Facebook, is using daily, weekly or monthly interval for getting unique values according to our needs. Although this is a not so flexible solution, as it doesn't support custom date ranges and it is handled automatically on our backend. The field used in the frontend, namely the QQL editor, is page_impressions_unique. The conversion is carried out in the background by utilizing page_impressions_unique_day, page_impressions_unique_week or page_impressions_unique_days_28 according to the interval that was selected.

Another solution that can be utilized across all platforms and across any time frame is using the average value of the daily reach over a time frame. Using the above example the daily average reach over those three days would be calculated in the following fashion:

(100 + 120 + 30) / 3 = 83.33 people

Another common pitfall when creating custom metric comes when we are trying to illustrate multiple profiles over 2-dimensional charts.

The following QQL snippet was used in order to illustrate an example of this:

SELECT

"LIKES " AS dim1,

time AS dim2,

ZEROIFNULL(imagesLikes+videosLikes+carouselsLikes) AS totalCount

FROM instagram

JOIN

profiles ON instagram.profileId = profiles.id

UNION

SELECT

"COMMENTS " AS dim1,

time AS dim2 ,

ZEROIFNULL(imagesComments+videosComments+carouselsComments) AS totalCount

FROM instagram

JOIN

profiles ON instagram.profileId = profiles.id

What this metric is doing in this case, when visualized in any 2-dimensional chart (e.g. line chart), is illustrates one line for the comments and one line for the likes that the selected profile had over a period of time and on the interval of our selection (daily, weekly, monthly).

Although this metric is correct in the case of examining one profile, what is illustrated when multiple profiles are selected, is not what you'd probably expect. When multiple profiles are selected, the metric will still indicate the 2 lines, comments and likes, but the values for each day on each line are going to be the highest ones of the profiles selected.

For example, if on Monday profile A had 40 likes and 15 comments, profile B had 20 likes and 50 comments and profile C had 40 likes and 2 comments, the lines will show 40 likes and 50 comments on Monday.

In order to be able to illustrate the correct data, we first need to define what it is that we want to analyse for multiple profiles.

The first case is that we want to see a separate line for each profiles' like and each profiles' comments. In that case, the name of the profile should be included in dim1 in the following fashion.

SELECT

"LIKES " || name AS dim1,

time AS dim2,

ZEROIFNULL(imagesLikes+videosLikes+carouselsLikes)

FROM instagram

JOIN

profiles ON instagram.profileId = profiles.id

UNION

SELECT

"COMMENTS " || name AS dim1,

time AS dim2 ,

ZEROIFNULL(imagesComments+videosComments+carouselsComments)

FROM instagram

JOIN

profiles ON instagram.profileId = profiles.id

The second case, and probably what most would expect from selecting multiple profiles, would be that the likes of all profiles are summed and displayed in one line. Likewise for the comment count line. In order to achieve this, we need to sum up the values in the following fashion:

SELECT

"LIKES " AS dim1,

time AS dim2,

ZEROIFNULL(SUM(imagesLikes)+SUM(videosLikes)+SUM(carouselsLikes)) AS totalCount

FROM instagram

JOIN

profiles ON instagram.profileId = profiles.id

GROUP BY dim2

UNION

SELECT

"COMMENTS " AS dim1,

time AS dim2 ,

ZEROIFNULL(SUM(imagesComments)+SUM(videosComments)+SUM(carouselsComments)) AS totalCount

FROM instagram

JOIN

profiles ON instagram.profileId = profiles.id

GROUP BY dim2

As a reminder, in the likely case that the comments are far less than the likes and the scaling doesn't work well for the comments line, you can always toggle the likes by clicking on them in the graph and thus turning them off. That way, the comments line will be visualized by itself and thus its patterns will be more easily recognised.