Building terabyte-scale data models to improve customer acquisition

Context and background

SEEK is an ASX-listed corporate with multiple brands and domains, and significant digital presence across the APAC region.  They manage hundreds of millions of URLs addressing different parts of their customer funnel, from brand building and user engagement to monetization and account management.  The scale meant that many teams were involved to create and manage the website and its traffic, from marketing and customer support, product development, search algorithms and ontology, security and routing to event tracking and analytics.  Our primary stakeholders were the Search Engine Acquisition and Optimization teams. 

The teams needed to understand where URLs were ranked on Search Engine Result Pages (SERPs), which queries generated those results and which pages got clicked on.  Results on SERPs were in turn based on technical SEO and content relevance; sitemap contents and GoogleBot crawl activity; SEM competition; and page performance and user behaviour 

Problem statement 

The scale of the organization contributed to a fragmented data environment, and the SEA/SEO teams often worked on aggregated data in source systems (e.g., various SaaS tools for event data and application log data, Google Search Console for sitemap indexation and crawl data).  Nuances in data definitions and collection mechanics meant that historical analysis was truncated, cross-dataset comparison was error prone, and complex logic was promoted to the data consumption layer.  

The team had been unable to extract themselves from running the business to invest the time in thoughtful design and build of foundational data models and scalable data assets that could serve common analytical and reporting use cases.  This is where we came in. 

Our approach 

Discover and prioritise use cases

Our plan started with understanding the SEA/SEO team's daily operations; what questions did they seek answers to, what type of analysis would add value, what type of day-to-day operations does the data need to support.  As an aspirational end-state, we wanted all end users to own and self-serve top-line metrics, i.e. numbers used for regular monthly reporting cadences. Technical personas (e.g. data analysts) would undertake analytical deep-dives on specific topics that provide strategic guidance to product management.

A necessary precursor to this end-state was the presence of data products; high-quality self-describing data assets with the richness from a variety of data sources that promoted next-question analysis. 

Deeply understand the problem domain 

Based on these "jobs-to-be-done", we created a conceptual data model and metric tree to map outcomes to input metrics.  This led to a clearer understanding of causality between inputs and outputs, providing a clear litmus test of what sort of questions data assets must answer in order to consider the project a success.

Upon alignment with the business team, we sketched a subject-oriented data warehouse which combined dimension tables, fact tables, and aggregate tables which must help answer questions raised from the metric tree. The design was informed by the underlying data's granularity and completeness, bringing pragmatism to the conceptual data model.

Incrementally build against identified use cases

1. Foundational monthly reporting: With this "blueprint" in mind, we tackled the first set of tables that would unblock the first major internal use case - unblocking monthly reporting.  Data was made available in mart tables supporting sufficient drilldown that supported productive 'six why' style discussions. Mart tables were complemented with interactive Tableau dashboards that solved the last-mile problem of consuming insights visually.  
2. Mapping action to results: Tracking external events was critical to understanding performance trends. We designed a data-capture notebook that allowed users to input events such as Google Algorithm updates, internal product updates and other explanatory notes while mapping these updates to specific countries. This data was merged with performance information to produce an annoted timeline view, allowing discussions on which events contributed to dislocations or trend changes for, and correlation between, metrics.  
3. Performance optimisation: With positive feedback and having proven value with this foundational build and unlocking basic diagnostics capabilities, we had buy-in to further optimize the data pipeline - a key bottleneck to producing reports in a timely manner and updating data assets with new definitions efficiently. As an example, dynamically generated page URLs had to be classified by product type and subtypes, language and region. We completely rebuilt more than 3,000 lines of Python regular expression-based classifiers and optimised them for Spark-native compute, reducing page classification run time from >60 hours to a matter of minutes.  This enabled SEO analysts to respond to ongoing product updates without fear of incurring thousands of dollars of compute cost.
4. Dataset and use case expansion: Next, we incrementally tackled ingestion of a variety of data sources including sitemaps, user experience data, competitive performance data, Search Engine Marketing spend and performance, going further south in our metric tree to help answer deeper questions around site performance. With the release of each ingested source, we coupled releases with relevant user-facing data assets such as dashboards, notebooks or data catalogs, ensuring each release brought tangible incremental value to analytical work.
5. Enable self serve analytics: At the same time, we wanted to ease the way people could self-serve data for specific hypotheses. We used Databricks Genie space features, with purpose-built spaces augmented with business context, metric and dimension definitions, helped with the rich metadata that went alongside data model development. Alongside capability improvements with SQL, these data artifacts improved retrieval time for metrics, helping users extract numbers quickly, aiding faster analysis and freeing analyst time to focus on analytical deep-dives.

The results 

Our work unlocked SEEK's rich datasets and enabled the deep technical SEO experts to do what they do best - brining domain expertise and market knowledge to explain movements in metrics, without needing to also be data engineers, analysts and scientists.  Outcomes from this work mixed hard technical metrics with broader process change, but all aligned towards improving value creation from data-driven decision making.

• The business is having more effective performance conversations, discussions have been stimulated about team structure and individual responsibilities, and foundational data assets that are shaping customer acquisition decisions across SEO and SEM teams
• Monthly report preparation time dropped from ~10 days of activity involving multiple iterations across team members to less than half a day
• URL classification speed improvements from ~60 hours to a matter of minutes
• Data processing spend dropped by more than 50%.  Daily data transformation pipeline run times fell from 3-6 hours at project start to <30 minutes