Skip to content

Instantly share code, notes, and snippets.

@akshatashan

akshatashan/gist:9ea76a23d9646b57c425143f774539ef

Last active January 12, 2018 08:51
Show Gist options
  • Save akshatashan/9ea76a23d9646b57c425143f774539ef to your computer and use it in GitHub Desktop.
Save akshatashan/9ea76a23d9646b57c425143f774539ef to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
Sample data
Group1 - B-1234
Group2 - B-1234, B-2345, B-5678
Venue1 - B-5678, 1234
with interval
-----------------
Groupbys: []
Response: no of days * (1 record with sums of common triggers for selected locations)
| date | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum |
|:-----------|:-------------|:---------|:------|:------------------|:---------------|:--------------|
| 2017-09-01 | 3137 | 3311 | 5000 | 3000 | 40 | 400 |
| 2017-09-02 | 3137 | 3311 | 5000 | 3000 | 40 | 400 |
Groupbys : [GROUP, VENUE, TRIGGER]
Response: No of records = (no of days) * (sum of all triggers) = 2*6 = 12
| date | group | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | venue | trigger | identifier | origin |
|:-----------|:-------|:-------------|:---------|:------|:------------------|:--------------|:-----------|:-----------|:----------|:----------|:---------|
| 2017-09-01 | Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| 2017-09-01 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| 2017-09-01 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-2345 | 2345 | beacons |
| 2017-09-01 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-5678 | 5678 | beacons |
| 2017-09-01 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | B-5678 | 5678 | beacons |
| 2017-09-01 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | 1234 | 1234 | geofences|
| 2017-09-02 | Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| 2017-09-02 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| 2017-09-02 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-2345 | 2345 | beacons |
| 2017-09-02 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-5678 | 5678 | beacons |
| 2017-09-02 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | B-5678 | 5678 | beacons |
| 2017-09-02 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | 1234 | 1234 | geofences|
Groupbys : [GROUP, VENUE]
Response: No of records = no of days * (no of locations) = 2*3=6
| date | group | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | venue |
|:-----------|:-------|:-------------|:---------|:------|:------------------|:--------------|:-----------|:-----------|
| 2017-09-01 | Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| 2017-09-01 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| 2017-09-01 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 |
| 2017-09-02 | Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| 2017-09-02 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| 2017-09-02 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 |
Groupbys : [GROUP, TRIGGER]
Response: No of records = no of days * (sum of all triggers for the groups) = 2*4=8
| date | group | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | venue | trigger | identifier | origin |
|:-----------|:-------|:-------------|:---------|:------|:------------------|:--------------|:-----------|:-----------|:------------|:-----------|:---------|
| 2017-09-01 | Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| 2017-09-01 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| 2017-09-01 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-2345 | 2345 | beacons |
| 2017-09-01 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-5678 | 5678 | beacons |
| 2017-09-02 | Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| 2017-09-02 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| 2017-09-02 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-2345 | 2345 | beacons |
| 2017-09-02 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-5678 | 5678 | beacons |
CASE 4
Groupbys : [VENUE, TRIGGER]
Response: No of records = no of days * (sum of all triggers for the venue) = 2*2=4
| date | group | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | venue | trigger | identifier | origin |
|:-----------|:-------|:-------------|:---------|:------|:------------------|:--------------|:-----------|:-----------|:----------|:----------|:---------
| 2017-09-01 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | B-5678 | 5678 | beacons |
| 2017-09-01 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | 1234 | 1234 | geofences|
| 2017-09-02 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | B-5678 | 5678 | beacons |
| 2017-09-02 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | 1234 | 1234 | geofences|
CASE 5
Groupbys : [TRIGGER]
Response: No of records = no of days * (sum of all distinct triggers) = 2*4=8
| date | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | trigger | identifier | origin |
|:-----------|:-------------|:---------|:------|:------------------|:--------------|:----------|:---------|:-----------|:---------|
| 2017-09-01 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | B-1234 | 1234 | beacons |
| 2017-09-01 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | B-2345 | 2345 | beacons |
| 2017-09-01 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | B-5678 | 5678 | beacons |
| 2017-09-01 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | 1234 | 1234 | geofences|
| 2017-09-02 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | B-1234 | 1234 | beacons |
| 2017-09-02 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | B-2345 | 2345 | beacons |
| 2017-09-02 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | B-5678 | 5678 | beacons |
| 2017-09-02 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | 1234 | 1234 | geofences|
CASE 6
Groupbys : [GROUP]
Response: No of records =no of days * (no of groups)
| date | group | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | venue |
|:-----------|:-------|:-------------|:---------|:------|:------------------|:--------------|:-----------|:-----------|
| 2017-09-01 | Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| 2017-09-01 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| 2017-09-02 | Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| 2017-09-02 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
CASE 7
Groupbys : [VENUE]
Response: No of records = no of days * (no of venues)
| date | group | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | venue |
|:-----------|:-------|:-------------|:---------|:------|:------------------|:--------------|:-----------|:-----------|
| 2017-09-01 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 |
| 2017-09-02 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 |
Raw
gistfile2.txt
Sample data
Group1 - B-1234
Group2 - B-1234, B-2345, B-5678
Venue1 - B-5678, 1234
with interval
-----------------
Groupbys: []
Response: no of days * (1 record with sums of common triggers for selected locations)
| date | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum |
|:-----------|:-------------|:---------|:------|:------------------|:---------------|:--------------|
| 2017-09-01 | 3137 | 3311 | 5000 | 3000 | 40 | 400 |
| 2017-09-02 | 3137 | 3311 | 5000 | 3000 | 40 | 400 |
CASE 1
Groupbys : [GROUP, VENUE, TRIGGER]
Response: No of records = (no of days) * (sum of all triggers) = 2*6 = 12
| date | group | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | venue | trigger | identifier | origin |
|:-----------|:-------|:-------------|:---------|:------|:------------------|:--------------|:-----------|:-----------|:------------|:-----------|:---------|
| 2017-09-01 | Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| 2017-09-01 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| 2017-09-01 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-2345 | 2345 | beacons |
| 2017-09-01 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-5678 | 5678 | beacons |
| 2017-09-01 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | B-5678 | 5678 | beacons |
| 2017-09-01 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | 1234 | 1234 | geofences|
| 2017-09-02 | Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| 2017-09-02 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| 2017-09-02 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-2345 | 2345 | beacons |
| 2017-09-02 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-5678 | 5678 | beacons |
| 2017-09-02 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | B-5678 | 5678 | beacons |
| 2017-09-02 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | 1234 | 1234 | geofences|
CASE 2
Groupbys : [GROUP, VENUE]
Response: No of records = no of days * (no of locations) = 2*3=6
| date | group | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | venue |
|:-----------|:-------|:-------------|:---------|:------|:------------------|:--------------|:-----------|:-----------|
| 2017-09-01 | Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| 2017-09-01 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| 2017-09-01 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 |
| 2017-09-02 | Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| 2017-09-02 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| 2017-09-02 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 |
CASE 3
Groupbys : [GROUP, TRIGGER]
Response: No of records = no of days * (sum of all triggers for the groups) = 2*4=8
| date | group | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | venue | trigger | identifier | origin |
|:-----------|:-------|:-------------|:---------|:------|:------------------|:--------------|:-----------|:-----------|:------------|:-----------|:---------|
| 2017-09-01 | Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| 2017-09-01 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| 2017-09-01 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-2345 | 2345 | beacons |
| 2017-09-01 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-5678 | 5678 | beacons |
| 2017-09-02 | Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| 2017-09-02 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| 2017-09-02 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-2345 | 2345 | beacons |
| 2017-09-02 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-5678 | 5678 | beacons |
CASE 4
Groupbys : [VENUE, TRIGGER]
Response: No of records = no of days * (sum of all triggers for the venue) = 2*2=4
| date | group | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | venue | trigger | identifier | origin |
|:-----------|:-------|:-------------|:---------|:------|:------------------|:--------------|:-----------|:-----------|:------------|:-----------|:---------|
| 2017-09-01 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | B-5678 | 5678 | beacons |
| 2017-09-01 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | 1234 | 1234 | geofences|
| 2017-09-02 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | B-5678 | 5678 | beacons |
| 2017-09-02 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | 1234 | 1234 | geofences|
CASE 5
Groupbys : [TRIGGER]
Response: No of records = no of days * (sum of all distinct triggers) = 2*4=8
| date | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | trigger | identifier | origin |
|:-----------|:-------------|:---------|:------|:------------------|:--------------|:-----------|:----------|:-----------|:---------|
| 2017-09-01 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | B-1234 | 1234 | beacons |
| 2017-09-01 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | B-2345 | 2345 | beacons |
| 2017-09-01 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | B-5678 | 5678 | beacons |
| 2017-09-01 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | 1234 | 1234 | geofences|
| 2017-09-02 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | B-1234 | 1234 | beacons |
| 2017-09-02 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | B-2345 | 2345 | beacons |
| 2017-09-02 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | B-5678 | 5678 | beacons |
| 2017-09-02 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | 1234 | 1234 | geofences|
CASE 6
Groupbys : [GROUP]
Response: No of records =no of days * (no of groups)
| date | group | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | venue |
|:-----------|:-------|:-------------|:---------|:------|:------------------|:--------------|:-----------|:-----------|
| 2017-09-01 | Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| 2017-09-01 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| 2017-09-02 | Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| 2017-09-02 | Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
CASE 7
Groupbys : [VENUE]
Response: No of records = no of days * (no of venues)
| date | group | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | venue |
|:-----------|:-------|:-------------|:---------|:------|:------------------|:--------------|:-----------|:-----------|
| 2017-09-01 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 |
| 2017-09-02 | | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 |
********************************************************************************************************************************************************
Without interval
Groupbys: []
Response: 1 record with sums of common triggers for selected locations
| new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum |
|:-------------|:---------|:------|:------------------|:--------------|:------------|
| 3137 | 3311 | 5000 | 3000 | 40 | 400 |
Groupbys : [GROUP, VENUE, TRIGGER]
Response: No of records = (sum of all triggers) = 6
| group | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | venue | trigger | identifier | origin |
|:------- |:-------------|:---------|:------|:------------------|:--------------|:-----------|:-----------|:------------|:-----------|:---------|
| Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-2345 | 2345 | beacons |
| Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-5678 | 5678 | beacons |
| | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | B-5678 | 5678 | beacons |
| | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | 1234 | 1234 | geofences|
Groupbys : [GROUP, VENUE]
Response: No of records = no of days * (no of locations) = 1*3=3
| group | new_visitors | visitors | visits | recurring_visitor | dwell_average | dwell_sum | venue |
|:-------|:-------------|:---------|:------ |:------------------|:--------------|:-----------|:-----------|
| Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 |
Groupbys : [GROUP, TRIGGER]
Response: No of records = (sum of all triggers for the groups) = 4
| group | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | venue | trigger | identifier | origin |
|:------- |:-------------|:---------|:------|:------------------|:--------------|:-----------|:-----------|:------------|:-----------|:---------|
| Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-1234 | 1234 | beacons |
| Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-2345 | 2345 | beacons |
| Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | | B-5678 | 5678 | beacons |
Groupbys : [VENUE, TRIGGER]
Response: No of records = (sum of all triggers for the venues) = 2
| group | new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | venue | trigger | identifier | origin |
|:------- |:-------------|:---------|:------|:------------------|:--------------|:-----------|:-----------|:------------|:-----------|:---------|
| | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | B-5678 | 5678 | beacons |
| | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 | 1234 | 1234 | geofences|
Groupbys : [TRIGGER]
Response: No of records = sum of all distinct triggers = 4
| new_visitors | visitors | visits| recurring_visitor | dwell_average | dwell_sum | trigger | identifier | origin |
|:-------------|:---------|:------|:------------------|:----------------|:-----------|:---------|:-----------|:---------|
| 3137 | 3311 | 5000 | 3000 | 40 | 400 | B-1234 | 1234 | beacons |
| 3137 | 3311 | 5000 | 3000 | 40 | 400 | B-2345 | 2345 | beacons |
| 3137 | 3311 | 5000 | 3000 | 40 | 400 | B-5678 | 5678 | beacons |
| 3137 | 3311 | 5000 | 3000 | 40 | 400 | 1234 | 1234 | geofences|
Groupbys : [GROUP]
Response: No of records = no of groups
| group | new_visitors | visitors | visits | recurring_visitor | dwell_average | dwell_sum | venue |
|:-------|:-------------|:---------|:------ |:------------------|:--------------|:-----------|:-----------|
| Group1 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
| Group2 | 3137 | 3311 | 5000 | 3000 | 40 | 400 | |
Groupbys : [VENUE]
Response: No of records = no of venues
| group | new_visitors | visitors | visits | recurring_visitor | dwell_average | dwell_sum | venue |
| | 3137 | 3311 | 5000 | 3000 | 40 | 400 | Venue1 |
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment