counterweight/data-dwh-dbt-project
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
data-dwh-dbt-project/models/reporting/xero/xero__sales_monthly_trends.sql

33 lines
1.4 KiB
MySQL
Raw Permalink Normal View History

Merged PR 4228: First version of sales - monthly trends # Description New model that aggregates in a monthly basis the information from Xero sales. The aggregation is done by several categories, most of them to be included as filters in PBI. Metrics represent the amount without taxes in: 1) the current month, 2) the previous month, 3) twelve months ago, 4) current year YTD (fiscal year) and 5) previous year YTD (fiscal year). It also propagates the model to reporting # Checklist - [X] The edited models and dependants run properly with production data. - [X] The edited models are sufficiently documented. - [X] The edited models contain PK tests, and I've ran and passed them. - [X] I have checked for DRY opportunities with other models and docs. - [ ] I've picked the right materialization for the affected models. **I was hesitating between table or view. Table should speed up tests, but I intend to create a reporting table with a similar structure so probably a bit of waste of space. Anyway, not a big deal now** # Other - [ ] Check if a full-refresh is required after this PR is merged. Related work items: #26618
2025-01-31 08:33:15 +00:00
with
int_xero__sales_monthly_trends as (
select * from {{ ref("int_xero__sales_monthly_trends") }}
Merged PR 4236: Propagate contact name for reporting purposes only # Description Propagates contact name and id_deal + contact name combination for PBI in xero - sales monthly trends # Checklist - [X] The edited models and dependants run properly with production data. - [X] The edited models are sufficiently documented. - [X] The edited models contain PK tests, and I've ran and passed them. - [X] I have checked for DRY opportunities with other models and docs. - [X] I've picked the right materialization for the affected models. # Other - [ ] Check if a full-refresh is required after this PR is merged. Propagate contact name for reporting purposes only Related work items: #26618
2025-01-31 09:53:54 +00:00
),
int_xero__contacts as (select * from {{ ref("int_xero__contacts") }})
Merged PR 4228: First version of sales - monthly trends # Description New model that aggregates in a monthly basis the information from Xero sales. The aggregation is done by several categories, most of them to be included as filters in PBI. Metrics represent the amount without taxes in: 1) the current month, 2) the previous month, 3) twelve months ago, 4) current year YTD (fiscal year) and 5) previous year YTD (fiscal year). It also propagates the model to reporting # Checklist - [X] The edited models and dependants run properly with production data. - [X] The edited models are sufficiently documented. - [X] The edited models contain PK tests, and I've ran and passed them. - [X] I have checked for DRY opportunities with other models and docs. - [ ] I've picked the right materialization for the affected models. **I was hesitating between table or view. Table should speed up tests, but I intend to create a reporting table with a similar structure so probably a bit of waste of space. Anyway, not a big deal now** # Other - [ ] Check if a full-refresh is required after this PR is merged. Related work items: #26618
2025-01-31 08:33:15 +00:00
select
Merged PR 4236: Propagate contact name for reporting purposes only # Description Propagates contact name and id_deal + contact name combination for PBI in xero - sales monthly trends # Checklist - [X] The edited models and dependants run properly with production data. - [X] The edited models are sufficiently documented. - [X] The edited models contain PK tests, and I've ran and passed them. - [X] I have checked for DRY opportunities with other models and docs. - [X] I've picked the right materialization for the affected models. # Other - [ ] Check if a full-refresh is required after this PR is merged. Propagate contact name for reporting purposes only Related work items: #26618
2025-01-31 09:53:54 +00:00
smt.document_issued_month_utc as document_issued_month_utc,
smt.fiscal_year as fiscal_year,
smt.previous_fiscal_year as previous_fiscal_year,
smt.document_status as document_status,
smt.document_type as document_type,
smt.id_deal as id_deal,
c.contact_name as contact_name,
Merged PR 4734: Bugfix - Ensures accounts cannot be duplicated in Xero sales monthly trends # Description This fixes the duplication error when the same deal has more than one contact. Impact was not massive, it just affected because of the Home to Host account and this has happened during the past week. There were other cases that contained duplicates but these were not invoiced so it didn't affect the total sum. Changes: * Contacts now has a boolean field indicating if a deal exists. * Contacts now has a boolean field indicating if that contact has a deal informed and it's the last updated one. This is opinionated - it could have been done with creation, for instance. * Sales monthly trends forces the last updated contact to be true. * Improved robustness by adding tests. Note that modifying the logic in intermediate on `int_xero__sales_monthly_trends` by changing `id_deal` per `id_contact` would not have worked - we need to do this compute by deal to ensure that any invoicing is reported MoM and YTD. # Checklist - [X] The edited models and dependants run properly with production data. - [X] The edited models are sufficiently documented. - [X] The edited models contain PK tests, and I've ran and passed them. - [X] I have checked for DRY opportunities with other models and docs. - [X] I've picked the right materialization for the affected models. # Other - [ ] Check if a full-refresh is required after this PR is merged. Related work items: #28576
2025-03-19 08:19:20 +00:00
case
when c.contact_name is null
then smt.id_deal
else smt.id_deal || '-' || c.contact_name
end as id_deal_contact_name,
Merged PR 4236: Propagate contact name for reporting purposes only # Description Propagates contact name and id_deal + contact name combination for PBI in xero - sales monthly trends # Checklist - [X] The edited models and dependants run properly with production data. - [X] The edited models are sufficiently documented. - [X] The edited models contain PK tests, and I've ran and passed them. - [X] I have checked for DRY opportunities with other models and docs. - [X] I've picked the right materialization for the affected models. # Other - [ ] Check if a full-refresh is required after this PR is merged. Propagate contact name for reporting purposes only Related work items: #26618
2025-01-31 09:53:54 +00:00
smt.accounting_financial_l1_aggregation as accounting_financial_l1_aggregation,
smt.accounting_financial_l2_aggregation as accounting_financial_l2_aggregation,
smt.accounting_financial_l3_aggregation as accounting_financial_l3_aggregation,
smt.amount_wo_taxes_in_gbp as amount_wo_taxes_in_gbp,
smt.previous_month_amount_wo_taxes_in_gbp as previous_month_amount_wo_taxes_in_gbp,
smt.previous_year_amount_wo_taxes_in_gbp as previous_year_amount_wo_taxes_in_gbp,
smt.ytd_amount_wo_taxes_in_gbp as ytd_amount_wo_taxes_in_gbp,
smt.previous_year_ytd_amount_wo_taxes_in_gbp
as previous_year_ytd_amount_wo_taxes_in_gbp
from int_xero__sales_monthly_trends smt
Merged PR 4734: Bugfix - Ensures accounts cannot be duplicated in Xero sales monthly trends # Description This fixes the duplication error when the same deal has more than one contact. Impact was not massive, it just affected because of the Home to Host account and this has happened during the past week. There were other cases that contained duplicates but these were not invoiced so it didn't affect the total sum. Changes: * Contacts now has a boolean field indicating if a deal exists. * Contacts now has a boolean field indicating if that contact has a deal informed and it's the last updated one. This is opinionated - it could have been done with creation, for instance. * Sales monthly trends forces the last updated contact to be true. * Improved robustness by adding tests. Note that modifying the logic in intermediate on `int_xero__sales_monthly_trends` by changing `id_deal` per `id_contact` would not have worked - we need to do this compute by deal to ensure that any invoicing is reported MoM and YTD. # Checklist - [X] The edited models and dependants run properly with production data. - [X] The edited models are sufficiently documented. - [X] The edited models contain PK tests, and I've ran and passed them. - [X] I have checked for DRY opportunities with other models and docs. - [X] I've picked the right materialization for the affected models. # Other - [ ] Check if a full-refresh is required after this PR is merged. Related work items: #28576
2025-03-19 08:19:20 +00:00
left join
int_xero__contacts c
on smt.id_deal = c.id_deal
and c.is_latest_updated_contact_per_deal = true
Reference in a new issue Copy permalink