The Internal Analytics that is part of the **Integrated Performance Framework** of and derives its structure and design based on the [[Frameworks#Internal Analytics Template]]. It offers a centralised reporting environment where all team members at a company can track their performance metrics to support their day-to-day responsibilities. All relevant components that are part of the data model are presented here. ## Design Depending on the Analytics tool used, the design will look different. [[Looker Studio]] and [[PowerBI ]] allow for a menu-structure to be placed on the left-hand side of the report. [[Preset]] on the other hand, works with a per-dashboard approach, which is especially usefull when the number of report pages becomes too large to effectively fit in one report. ![[Looker Studio Report Demo.png]] *The report menu on the left side in Looker Studio* ## Metrics Each company's success is built around a handful of metrics that must be meticulously tracked. In the case of MAXQ Analytics, the core metrics for our engineering business are: `billable_utilisation`, `availability`, `gross_margin`, and `booked_hourly_rate`. Each metric designated as critical to a company's success is given its own dedicated page. While the exact layout can be tailored to the client’s needs, we generally include a few scorecards that show: - The **current metric value**, based on any active filters. - The **numerator** and **denominator** used to calculate that value. Alongside the metric, the client provides textual context on the metric, the owner, universal business standards and the preferred bandwidth within which the metric should move. ![[Internal Analytics Metric.png]] *A dedicated metric page in Looker Studio* <br>When you scroll down the page we have the option to show dimension-specific aggregation tables and scatterplots, which allow analysts to better understand the distribution of the metric across different axis. ![[Internal Analytics Metric Aggregation.png]] *Dimension specific aggregation of a single metric in Looker Studio* ## Sources The majority of data analytics setups have a multi-source architecture, this implies combining the data from different software applications into one single truth. To make sure that the source data is properly structured and sufficiently filled out, the Internal Analytics design always has a Source section that summarises all major source tables. The source pages contain a singular table representing either a dimensional table or an event table, with the contents of the source pages showing the data after the transformation. A core component are the aggregation tables where the events are aggregated by event property, which allows for a fast deepdive in how the events are distributed across its properties and whether there are any irregularities. ![[Internal Analytics Sources.png]] *A typical source page with counters, measures, timelines, records and aggregations* ## Data Quality The data quality is tracked using the *Data Entry Tests* which executes client specific tests on the dataset to make sure all data entries and business workflows are handling themselves accordingly. Each Data Entry Test receives an *Owner*, a *Deeplink* for resolving issues at the source, *Context Description* and a *Test ID* for ease of communication. ![[Internal Analytics Data Quality.png]] *Data Entry Tests as part of the overall Data Quality* ## Settings Each data architecture contains the following components which require documentation: - **Data Pipeline** flowchart of all systems used in the data architecture - **Definitions** describing all measures and dimensions - **Metric Catalog** for summarising all metrics, its definition and deep-dive page ![[Internal Analytics Data Pipeline.png]] *Data Pipeline flowchart visualising the data extraction, transformation and consumption* These settings are especially usefull when refactors and expansions are considered, because then all stakeholders can see how the data model elements are defined and how the data architecture is currently organised. ## Demonstration We published an [Internal Analytics](https://lookerstudio.google.com/u/0/reporting/86bd9839-3788-4b62-a1d9-4ce312bbff3e/page/p_etg6w1911c) report for the State of Iowa Alcoholic Beverages Division, which reports on all 18 million invoices sent by the division since 2012. The data is stored in [[BigQuery]], where we have built the underlying data model using [[dbt]] and the semantic model with [[Cube]]. ## Use cases The Internal Analytics report template can be applied to a wide range of business models. **Engineering** - **Billable hour tracking** for capacity-based and fixed-fee projects - **Hourly utilization monitoring** for support contracts - **Coverage ratio forecasting** to anticipate future capacity needs **Technology** - **Activity levels** per user, account, vertical and country - **Split tests** for validating working hypothesis - **Churn predcition** based on leading and lagging indicators **Events** - **Utilization tracking** by event, location, account manager, or division - **Gross margin monitoring** for overall financial health - **Near real-time data services** to provide timely insights to clients **Products** - **Inventory Turnover** per item, category, supplier and country - **Return Rate optimalisation** for improved gross margins - **Warehouse utilisation tracking** to enable high performance ## Comparing Vendors The three analytics systems MAXQ actively supports are [[Looker Studio]], [[PowerBI]] and [[Preset]], with a strong preference towards the latter, since it has the best price-quality ratio and scales well upwards to a 1000+ FTE headcount. | | Looker Studio | PowerBI | Preset | | ------------------------- | ------------- | ------- | ------ | | Granular chart formatting | Yes | Yes | Yes | | Email Reports | Yes | Yes | Yes | | Row-level-access | - | Yes | Yes | | Data access roles | - | Yes | Yes | | Semantic Sync | - | Yes | Yes | | PDF export | - | Yes | Yes | | Embed | - | Yes | Yes | | Drill to details | - | Yes | Yes | | Export/import | - | - | Yes | | Chart query | - | - | Yes | | Private development | - | - | Yes | | Ad-hoc SQL | - | - | Yes | | Object-based design | - | - | Yes | | Open Core | - | - | Yes | | Cloud-Native | Yes | - | Yes | | Free pricing tier | Yes | - | Yes | | Public sharing | Yes | - | - | %% Owner: Philip Boontje %%