Number of unique IDs accumulated

JonasLang 31-07-2019

Dear Community

I was asked to deliver the number of unique customer IDs year to date split by month, and I cannot myself figure out whether it is possible to calculate this number.

Example:

In January 2019 I had the following customer IDs visiting my website:

AB12

AC13

AD14

This give me 3 unique customers that month.

In February 2019 I had the following customer IDs visiting my website:

AB12

BA21

This give me 2 unique customers that month.

In March 2019 I had the following customer IDs visiting my website:

AB12

AC13

CA31

This give me 3 unique customers that month.

I am then asked to deliver a table that looks like this:

January 2019      3 unique customers

February 2019     4 unique customers

March 2019          5 unique customers

I am using the APPROXIMATE DISTINCT COUNT within Calculated Metrics to find the number of unique customer IDs in total but it does not work with the CUMULATIVE function to split it per month.

Any suggestions on how to solve this?

Thanks in advance!

Best regards

Jonas

Accepted Solutions (1)

Accepted Solutions (1)

Andrey_Osadchuk
MVP
01-08-2019

Hi Jonas,

I think what you may want to do to get the desired result is to update the date ranges as follows:

  • Jan 2019
  • Jan-Feb 2019
  • Jan-Mar 2019

This would help you display cumulative number of visitors to a certain date (month).

Answers (8)

Answers (8)

ursboller
MVP
01-08-2019

I don't know of any fancy way to get de-duplicated cumulative sums of the distinct counts. means you either need to go with the solution described by Andrey Osadchuk​ or forget the idea to have an easy solution... sorry

JonasLang 01-08-2019

Hi Andrey

I was hoping that it could be done in a "clever" way. Potentially we want to break it down by week or day as well. It is a lot of custom date ranges to create.

Best regards

Jonas

JonasLang 31-07-2019

Hi Urs

It is not working for me - If I do that my table will look like this:

January 2019      3 unique customers

February 2019     2 unique customers

March 2019         3 unique customers

I want an accumulated table where it is only adding new customers. I have added the time intervals that each month should be covering to explain it a bit further: 

January 2019 (1/1-19 - 31/1-19)        3 unique customers

February 2019 (1/1-19 - 28/2-19)      4 unique customers

March 2019 (1/1-19 - 31/3-19)          5 unique customers

Best regards

Jonas

ursboller
MVP
31-07-2019

I would use the same function "approx distinct count" as well, it should work even with a month breakdown. maybe you need to count "minus 1" since it will count all "undefined" (users wothout customer id) as a new dimension item (and therefore adding an additional item to the count)