Net-New User calculation

Question & Answer
, , , ,
1

I am trying to find Net new users for a webpage for a time period.
ie: Users who started using the website for the first time this month (ie they never logged in prior to this month) this can be calculated as *All users for last month MINUS users who logged in at least once in till last month

The below is the SQL equivalent for what I am trying to achieve

SELECT DISTINCT login, webpage, logintime
FROM "website_usage_data"
WHERE logintime
BETWEEN date_trunc('month', date_add('month', -1, current_timestamp)) #Gets current month data
    AND date_trunc('month', current_timestamp) - INTERVAL '1' SECOND
    AND login NOT IN (
        SELECT login 
        FROM "website_usage_data"
        WHERE logintime 
        BETWEEN date_trunc('month', date_add('year', -5, current_timestamp)) 
            AND date_trunc('month', date_add('month', -1, current_timestamp))
        );

I could not find any option to exclude a set of data using filters.

2

Hi @balumath - Welcome to AWS QuickSight community and thanks for posting the question. Couple of suggestions -

  1. Is your data source is relational database? If yes, you can test your sql in DB and then use custom sql in QuickSight and do the reporting.
  2. If you are trying to filter the users for the last month minus all users before that ( you do not need to check whether user is login or not), then try to use left join rather minus as minus is a costly operation.
  3. if you are looking for a solution to bring all data to QuickSight data set preparation, you can create some count w.r.t login and put some filter and build the report on the base data set.

Regards - Sanjeeb

1 Like
3

I am trying to bring all data to Quicksight and and prepare the data

  • I could do the SQL using Athena, but I am loosing the ability to calculate month over month calculations because NetNew users will be a new Dataset

Is there an example of doing Left joins using functions and filters as a filter typically doesn’t exclude the “intersection”

1 Like
4

Hi @balumath - Yes you can bring the data sets in QuickSight and do a left join and create custom data set and apply the filter at data set level.

Regards - Sanjeeb