Context
Som Comunitats (https://somcomunitats.coop/) és una agrupació d’entitats de l’economia social unides per impulsar Comunitats Energètiques i continuar avançant en la transició energètica transformadora.
Ofereixen una plataforma digital per facilitar l’activació i gestió de les Comunitats Energètiques que inclou eines de codi lliure com són una àrea de gestió per a les coordinadores i administradores de la Comunitat, una aplicació mòbil per a les persones sòcies, un mapa i espai web per visibilitzar les Comunitats i facilitar l’adhesió de noves sòcies i un f`rum d’ajuda mútua.
Des de Som Comunitats tenen la necessitat d’analitzar les dades de l’estat de les comunitats, tant les que estan actives, com les potencials comunitats en vies de creació. Tot aquest anàlisi s’ha de poder fer amb diferents nivells d’agregació geogràfica, i també ha de permetre a les entitats coordinadores de les comunitats energètiques veure les estadístiques de les seves comunitats.
A més també s’afegeixen analítiques sobre els tiquets de CRM o visites web de la pàgina, per a poder fer un anàlisi 360º.
Execució del projecte
La principal font de les dades de les comunitats és l’ERP Odoo. A l’odoo hi ha les dades de les comunitats, tant actives, com amb possiblitat d’activar-se, les dades de les persones, instal·lacions, facturació, etc. També ha calgut llegir dades de FreeScout per al control de tiquets de tasques i de google analytics per a les visites a la pàgina web.
Per aquest projecte hem pogut reutilitzar l’arquitectura estàndar de Business Intelligence implementada des de Coopdevs (postgresql + foreign data wrapper a la base de dades, airflow+dbt+python per als processos ETL i Superset per a la visualització de dades). Les dades d’odoo s’han connectat directament amb el foreign data wrapper, i les dades de freescout i GA s’han carregat des d’una API a través d’airflow.
La principal característica d’aquest projecte ha estat la necessitat de visualitzar les dades en format mapa, podent triar tant la granularitat geogràfica com els indicadors a mostrar de forma dinàmica.
Per a poder-ho aconseguir hem fet servir el SQL Templating de Superset amb jinja.
er a poder escollir la granularitat geogràfica o l’indicador, primer ha calgut crear un dataset amb la llista de granularitats granularitats geogràfiques o d’indicadors.
Aquest dataset és el que s’utilitzarà com a paràmetre al quadre de comandament.
Després cal definir un altre dataset, on hi ha les dades a mostrar al mapa, on es fa servir el dataset anterior. Concretament, cal tenir en compte el nom de la columna (ex. en l’exemple anterior: granularitat_geografica)
with dades as (
select coordinator_name as coordinator_name,
{% if filter_values('granularitat_geografica')|length<1 %}
x.community_provincia
{% else %}
case '{{ '''' + "'".join(filter_values('granularitat_geografica')) + '''' }}'
when 'Codi postal' then x.community_zip
when 'Ciutat' then x.community_city||'-'||x.community_provincia
when 'Comarca (Cat)' then x.community_comarca
when 'Província' then x.community_provincia
when 'CCAA' then x.community_ccaa
end
{% endif %} as geo
,sum(coordinator_sr_amount_untaxed) AS ImportSRcoordinadora
,sum(community_sr_amount_untaxed) AS ImportSRcomunitats
,sum(coordinator_sr_amount_untaxed)+sum(community_sr_amount_untaxed) AS "Import SR coordinadora (amb comunitats)"
,COUNT(distinct id_coordinator) AS "Coordinadores"
,count(distinct case when community_pack='Pack 1' then id_community end) AS Pack1
,count(distinct case when community_pack='Pack 2' then id_community end) AS Pack2
,count(distinct case when te_socies then id_community end) AS Gestiosocietària
,sum(socies) AS socies
,sum(cnt_autoconsum) AS Projectesautoconsum
,sum(pw_autoconsum) AS Potènciaautoconsum
,COUNT(distinct id_community) AS comunitats
,
{% if filter_values('indicador')|length<1 %}
COUNT(distinct id_community)
{% else %}
case '{{ '''' + "'".join(filter_values('indicador')) + '''' }}'
when 'Comunitats' then COUNT(distinct id_community)
when 'Sòcies' then sum(socies)
when 'Projectes autoconsum' then sum(cnt_autoconsum)
when 'Potència autoconsum' then sum(pw_autoconsum)
end
{% endif %} as val
from "external".comunitats_obertes_incr x
where data=current_date
{% if filter_values('name_comarca')|length>=1 %}
and community_comarca in {{ filter_values('name_comarca')|where_in }}
{% endif %}
{% if filter_values('name_provincia')|length>=1 %}
and community_provincia in {{ filter_values('name_provincia')|where_in }}
{% endif %}
{% if filter_values('name_municipi')|length>=1 %}
and community_city in {{ filter_values('name_municipi')|where_in }}
{% endif %}
group by coordinator_name,
{% if filter_values('granularitat_geografica')|length<1 %}
x.community_provincia
{% else %}
case '{{ '''' + "'".join(filter_values('granularitat_geografica')) + '''' }}'
when 'Codi postal' then x.community_zip
when 'Ciutat' then x.community_city||'-'||x.community_provincia
when 'Comarca (Cat)' then x.community_comarca
when 'Província' then x.community_provincia
when 'CCAA' then x.community_ccaa
end
{% endif %}
)
select d.*, g.coordinates_superset
from dades d
{% if filter_values('granularitat_geografica')|length<1 %}
join geoprovincia g on g.name_provincia = d.geo
{% else %}
{% if filter_values('granularitat_geografica')[0]=='Comarca (Cat)' %}
join geocomarca g on g.name_comarca = d.geo
{% elif filter_values('granularitat_geografica')[0]=='Codi postal' %}
join geocpmunicipicoordinates g on g.cp = d.geo
{% elif filter_values('granularitat_geografica')[0]=='Ciutat' %}
join geomunicipi g on g.name_municipi|| '-' || g.name_provincia = d.geo
{% elif filter_values('granularitat_geografica')[0]=='Província' %}
join geoprovincia g on g.name_provincia = d.geo
{% elif filter_values('granularitat_geografica')[0]=='CCAA' %}
join geoccaa g on g.name_ccaa = d.geo
{% endif %}
{% endif %}
En aquest exemple es valida que el paràmetre tingui valor (és necessari per quan s’està creant un gràfic i encara no s’ha afegit a un quadre de comandament):
{% if filter_values('granularitat_geografica')|length<1 %}
i, en cas que tingui valor, es recupera el valor per utilitzar-lo en el case when:
'{{ '''' + "'".join(filter_values('granularitat_geografica')) + '''' }}'
D’aquesta forma la query generada canvia segons el paràmetre indicat al quadre de comandament.
En la consula anterior a partir de la granularitat_geografica es tria el camp a fer group by i també la taula a fer join. Les gaules geo* són les taules que contenen els polígons per pintar les àrees geogràfiques a Superset.
La tria d’indicadors es fa exactament igual que la granularitat_geografica, cal crear un dataset jinja amb els indicadors possibles i, després, fer codi jinja dins del dataset principal per a que calculi la variable que volem:
{% if filter_values('indicador')|length<1 %}
COUNT(distinct id_community)
{% else %}
case '{{ '''' + "'".join(filter_values('indicador')) + '''' }}'
when 'Comunitats' then COUNT(distinct id_community)
when 'Sòcies' then sum(socies)
when 'Projectes autoconsum' then sum(cnt_autoconsum)
when 'Potència autoconsum' then sum(pw_autoconsum)
end
{% endif %} as val
Per últim, des de Som Comunitats s’ha volgut poder compartir els quadres de comandaments fets amb les coordinadores de comunitats energètiques. Per a fer-ho hem utilitzat la característica Row Level Security de Superset, que permet inserir codi SQL a dins dels datasets seleccionats. En aquest cas, per cada usuari de coordinadora s’ha afegit el where per a que només puguin veure les seves pròpies dades.
’aquesta forma, qualsevol canvi aplicat als quadres de comandament, automàticament és visible també per a totes les coordinadores.
Conclusió
El projecte d’analítica de dades de Som Comunitats ha permés poder tenir una visió global de l’estat de les comunitats energètiques de forma ràpida i, sobretot, flexible, permetent l’anàlisi de les dades en un sol quadre de comandaments amb diferents visions geogràfiques i diferents indicadors, adaptant-se a les necessitats de cada equip i moment. També ha permés donar accés a les coordinadores de les comunitats energètiques per a que puguin fer un anàlisi personalitzat de la seva evolcuió, millorant així la capacitat de decisió i la transparència.
Com sempre, podeu trobar tot el codi desenvolupat als següents
repositoris:https://github.com/somcomunitats-coop/cedata-dbt
https://github.com/somcomunitats-coop/cedata-dags
https://github.com/somcomunitats-coop/cedata