# View: dt_insights

**View Name:** dt_insights
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Country, Art_Relations_Prod_Art_Relations.Orchadmin_Users, Frontend_Insights_Prod.Identifies, Prod.Fact_Sales, Prod.Vw_Dim_Abacus_Ar_Vendor + 3 more`
**File Path:** `dt_insights.view.lkml`

## Overview

- **File Size:** 4178 bytes
- **Lines of Code:** 138
- **Dimensions:** 8
- **Measures:** 2
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `email` | string |
| `user_id` | string |
| `label_id` | number |
| `label_name` | string |
| `label_manager` | string |
| `label_country` | string |
| `gross_revenue` | number |
| `revenue_bucket` | string |

## Measures

| Name | Type |
|------|------|
| `ws_visits` | sum |
| `insights_visits` | sum |

## Dimension Groups

| Name | Type |
|------|------|
| `report` | time |

## Derived Table

```sql
sql:with ws_visits as (

select to_date(timestamp) as date, email, user_id, label_id, count(distinct(user_id)) as ws_visits
from segment_events.prod_workstation01.identifies
where context_page_url like 'https://workstation.theorchard.com/analytics/%'
group by 1,2,3,4
order by 1,2,3,4) ,

insights_visits as (

select to_date(timestamp) as date, email, user_id, label_id, count(distinct(user_id)) as insights_visits
from segment_events.frontend_insights_prod.identifies
group by 1,2,3,4
order by 1,2,3,4
),

last_month_revenue as (
select labelid, coalesce(sum(gross),0) as gross_revenue
from FACTS.PROD.FACT_SALES
where accountingperiodid in (select max(accountingperiodid) from FACTS.PROD.FACT_SALES)
group by 1)

select nvl(w.date, i.date) as date, case when w.email is not null then w.email
else i.email
end as email,
nvl(w.user_id, i.user_id) as user_id,
w.label_id, o.f_name||' '||o.l_name  AS label_manager,
IFF(v.company IS NULL OR v.company  = '', v.name, v.company) as label_name, c.name as label_country, lr.gross_revenue as gross_revenue,
zeroifnull(w.ws_visits) as ws_visits, zeroifnull(i.insights_visits) as insights_visits
from ws_visits w
full outer join insights_visits i on i.date = w.date and w.user_id = i.user_id
left join royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v on v.vendor_id = w.label_id
left join orchard_app_reporting_v2.art_relations_prod_art_relations.country c on c.id = v.country
left join orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users o ON v.assigned_to = o.id
left join last_month_revenue lr on lr.labelid = v.vendor_id
group by 1,2,3,4,5,6,7,8,9,10;;
```