Ask HN: Good tech talks on how analytics systems are implemented?

Speaking as an analytics architect ...<p>You&#x27;ll be a lot better off spending your mental energy thinking about the outcomes you want to achieve (user engagement, upselling, growth, etc) and the types of analysis you&#x27;ll need to understand what changes you need to make to produce those outcomes. Protip: this is actually really hard, and people underestimate it by orders of magnitude. A blog post by Roger Peng (with indirect commentary from John Tukey) ... <a href="https:&#x2F;&#x2F;simplystatistics.org&#x2F;2019&#x2F;04&#x2F;17&#x2F;tukey-design-thinking-and-better-questions&#x2F;" rel="nofollow">https:&#x2F;&#x2F;simplystatistics.org&#x2F;2019&#x2F;04&#x2F;17&#x2F;tukey-design-thinkin...</a><p>One other immediate tip is to start thinking about correlating your telemetry with user surveys - again, strongly focusing on outcomes and the controllable aspects of those outcomes.<p>Don&#x27;t let the data lead the discuisson; decide on the question you&#x27;re asking, and the implications of all of the possible answers to that question (clearly yes, clearly no, mixed, etc) before you ask it.<p>Then engineer the lightest weight system possible to ingest, process, store, analyze, and visualize that data.<p>For me, that would just be:<p>1. Log data in whatever logging tool you like. Persist the raw stuff forever in a cheap data lake. 2. Batch at some fixed interval into a staging area of a relational DB. 3. Transform it with stored procedures for now (while you figure out what the right transforms are) into a flat fact table. 4. Visualize in Superset or PowerBI or even plain old Excel.<p>Once you&#x27;ve got the patterns of analysis at least fundamentally right you can consider stream processing (Flink or Kafka Streams are fine) to replace 2 and 3.

Seriously, just put everything in Postgres. You have so little data, you shouldn&#x27;t even be <i>thinking</i> about an &quot;analytics system&quot;.<p>I have seen so many developers over-engineer this exact problem. Forget about Kafka, Kinesis, Redshift, Airflow, Storm, Spark, Cassandra etc. You don&#x27;t need them, not even close. Unless you want to add a bunch of expensive distributed systems and operational overhead for fun&#x2F;resume building, they&#x27;re going to waste your time and hurt your stability.

I would be suspicious of most tech talks on this. If someone is giving a tech talk on their analytics systems, they are either working at enormous scale (Facebook, Google), selling something (Splunk), or over engineering their system (many startups).<p>I second advice elsewhere in this thread. Log it into PostgreSQL. If you start overloading that, look into sampling your data before you look into a fancier system. Make sure you have identifiers in each row for each entity a row is part of: user, session, web request. If you&#x27;re not building replicated PostgreSQL (which you probably won&#x27;t need for this), log to files first, and build another little process that tails the files and loads the rows into PostgreSQL. That log then load advice is hard learned experience from working at Splunk.

I was working for a startup implementing analytics tools. In my opinion, our setup was over-engineered, but I wasn&#x27;t there at the beginning, so I might be wrong. Also, requirements changed a couple of times, so this could also explain why something that looked necessary for scaling and speed, ended up being this over-engineered mess. This is how it worked: After javascript tracker fired, we got log files, passed them through Kafka, then parsed the log files and performed calculations through Storm (Java). For storage, we used Cassandra. The system also had other parts, but I don&#x27;t remember why they were there, tbh.<p>My thought process for solving your problem would be the following. First, you need to understand what&#x27;s good for you and for your company might not be the same. You want the challenge, you want to implement something that could scale and you want to use exotic tools for achieving this. It&#x27;s interesting and looks good in your CV. Your company might just want the results. You need to decide which is more important.<p>If we prioritize your companies needs over keeping you entertained, I&#x27;d follow this thought process:<p>Can&#x27;t you just use Google Analytics? You can also connect it to BigQuery and do lots of customizations. Maybe time would be better spent learning GA. It&#x27;s powerful, but most of us cannot use it well.<p>Second question: if for some reason, you don&#x27;t want to use Google Analytics, can you use another, possibly open-source and&#x2F;or self-hosted analytics solution? Only because you <i>can</i> design it from scratch, it doesn&#x27;t mean you should.<p>Third: Alright, you want to implement something from scratch. For this scale, you can probably just log and store events in an SQL database, write the queries, and display it in a dashboard.<p>Then, if you really want to go further, there are many tools that are designed to scale well and perform analytics, &quot;big data&quot;. By looking for talks about these tools, you will get a better understanding of how things work. There are various open-source projects you should read more about: Cassandra, Scylla, Spark, Storm, Flink, Hadoop, Kafka, Hadoop, Parquet, just to name a few.

My go-to v0 solution is JSON (simple, with no nested objects or lists) written to s3 (partitioned by date, see Hive date partitioning) and AWS Athena (serverless Presto) to do SQL queries on those JSONs. You can build the system in less than an hour, you don&#x27;t have to manage any VMs and it&#x27;s relatively easy to extend to a more serious solution (e.g. if you need major scale or Spark-like analytic jobs).<p>Relational databases like some are suggesting are fine, but you have to manage them unlike s3 + Athena and it tends to make you design around relational database concepts, which can make it difficult to migrate to a full-blown analytics solution that often abandons relational guarantees.<p>This solution also lets you be flexible in your raw data schema unlike relational databases where you have to have well defined schema or hacks like saving the raw JSON as a string.<p>When you need to evolve your data schema (you will as you learn what things you want to measure), a relational database requires you to be thoughtful about how to do this (e.g. you can&#x27;t have your data producer writing a new schema before the table has been changed). Often this requires you to add some sort of queue between data producers and database so that you can make changes to the table without stopping the data producers. With s3 + Athena, you can just upgrade your data producer, it will start saving the new format to s3 and then you upgrade your Athena table definition whenever you want to start querying the new data (because in relational databases, the schema defines how data is stored, but in s3+Athena world, the schema just tells the SQL engine how to read whatever data exists on s3).

1) Design the reports you want. Pay special attention to interactive elements like filters and drilldowns. List all dimensions and metrics you need. Think about privacy.<p>2) Find your visualisation tool of choice. This is more important than any architecture choice for the tracking because this makes your data useable. [1]<p>3) Select your main data storage that is compatible with your visualisation tool, data size, budget, servers, security, ... SQL is always better because it has a schema the vis tools can work with. For a low amount of data you might just want to use your existing database (if you have one) and not build up new infrastructure that has to be maintained.<p>4) If you need higher availability on the data ingress than your db can provide use a high availability streaming ingress [2] to buffer the data.<p>5) Design a schema to connect your db to the visualisation tool. Also think about how you will evolve this schema in the future. (Simplest thing in sql is: Add colunms.)<p>I hope this helps. If you have selected some tools it is fairly easy to search for blog posts and tech talks. But don&#x27;t think to big (data). &quot;A few thousands users&quot; and &quot;two dozen parameters&quot; may be handled with postgres and metabase. Also in most enterprise enviroments there already exists a data analytics &#x2F; data science stack that is covered by SLAs and accepted by privacy officers. Ask around.<p>[1] <a href="https:&#x2F;&#x2F;github.com&#x2F;onurakpolat&#x2F;awesome-bigdata#business-intelligence" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;onurakpolat&#x2F;awesome-bigdata#business-inte...</a> [2] <a href="https:&#x2F;&#x2F;github.com&#x2F;onurakpolat&#x2F;awesome-bigdata#data-ingestion" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;onurakpolat&#x2F;awesome-bigdata#data-ingestio...</a>

I have designed one for 500 dashboard users and various other requirements. My advise would be to get a cheap SQL compliant database that does not require a lot of maintenance (if you can afford buy a cloud one). Then for the analysis part the quickest thing to do is use Jupyter + SQLAlchemy. You can also use a dashboarding tool, there are many, to connect to the database, but I think with Jupyter you can ask more interesting questions that require more blending or transformations. That&#x27;s it, you&#x27;ll grow from here in the coming months and years, but if you over engineer analytics at the beginning you&#x27;ll most likely get tired of it and stop doing it at some point.

IMO, your requirements are too basic to need a serious system. Either log interaction to a file or a database, parse the output and query it with SQL to produce your basic metrics, or just write to Google Analytics.<p>When this starts creaking at the seams it&#x27;ll mean that you either have bigger analysis and&#x2F;or scalability requirements and it&#x27;ll much clearer what you need to look for.

I made one for our company using AWS Kinesis Firehose which I thought was really good having used GA, Mixpanel and Segment before. Shame we haven&#x27;t been able to put it into more wider use. Extremely simple and very robust, to deploy it you just have to run the CloudFormation stacks with Sceptre in a single command and then add the client library with some event listeners for clicks, pageviews et cetera. I&#x27;d love to be able to open-source it but I don&#x27;t know, should think through the benefits and disadvantages of both with my CEO. Probably couldn&#x27;t get customers to pay for an expensive custom analytics platform if it was open-source.<p>Having spent some time on this I&#x27;ll just say that don&#x27;t overthink it. Over-engineering such system is way too easy while the actual benefits might not be that great. Sure if you&#x27;re receiving a lot of data there might be some pitfalls to be aware of eg using proper bucket partitioning with Athena for queries.

Go with off the shelf. You&#x27;ll get something far better that you can build yourself, and if you need something custom, you&#x27;ll have a much clearer idea what your analysis is missing.<p>Writing to Google Analytics, Amplitude, Mixpanel (all of which have free tiers) or equivalent all should handle your case well.

Note: I build and maintain such systems for a living.<p>There&#x27;s a lot of context that&#x27;s missing from your post, some questions that can help us guide you in the right direction:<p>1) Can your website call out to external services, or are you limited to operating behind a company network?<p>2) Is this more of an ad-hoc analysis or do you want to invest in a framework to be able to track such metrics systematically over time?<p>3) How important is data accuracy? Adblock can easily mess with client-side metrics.<p>4) How real-time do metrics need to be? The big trade-off here is speed vs accuracy.<p>5) How long do you intend to keep this data? This is a pretty big concern with regards to privacy and storage costs.<p>If you&#x27;d rather not share some of these answers on a public forum, feel free to shoot me an email.

Start by adopting <a href="https:&#x2F;&#x2F;github.com&#x2F;snowplow&#x2F;snowplow" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;snowplow&#x2F;snowplow</a> then grow as and where you feel restricted.

You can just crunch your data with SQL&#x2F;service layer code in a background worker and store it in redis. Then you can use the objects from redis to render charts, build dashboards, etc...<p>Structure your code so you crunch your historical data once, store in redis, and then new data gets shoved in the redis cache as your time dimensions on your metrics progress based on business logic.<p>Until your data is at enterprise volume, you really don&#x27;t need an OLAP system.

I have an open-source project that collects the customer events via SDKs and stores it in a data warehouse.<p>It&#x27;s a distributed system, the mobile and web SDKs batch the user events on their devices and push it to our API in JSON format. The API servers enrich &amp; sanitize the data, validate the schema, convert it to a serialized AVRO binary, and push it to a commit-log system such as Kinesis or Kafka (It&#x27;s pluggable).<p>We have another project that fetches data from Kafka &amp; Kinesis in small batches, converts the data into columnar format and stores it in an S3 bucket &#x2F; Google Cloud Storage. Then, we integrate their preferred data-warehouse into their distributed filesystem. That way they have all their raw data in their infrastructure for other systems such as fraud detection, recommendation, etc. but they have SQL access to their data as well.<p>That being said, this architecture is for &gt;100M events per month. If your data is not that much, you can actually ingest your data into an RDBMS and it just works fine. We support Postgresql at Rakam and you need is the API server and a Postgresql instance in that case. Our open-source version supports Postgresql so you can look into the source code from here: <a href="https:&#x2F;&#x2F;github.com&#x2F;rakam-io&#x2F;rakam" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;rakam-io&#x2F;rakam</a> Would love to get some contribution. :)<p>For the analysis part, all these metrics can be created using just SQL, the modern data-warehouse solutions (BigQuery and Snowflake) also support javascript and it&#x27;s relatively easy to build funnel &amp; retention queries that way. It requires more work but now you have more control &amp; flexibility over your data.

This sounds like a classic case of build vs. buy. If analytics are not your core product, inventing a new solution is going to cost you more than buying an existing analytics solution. There are dozens, a few of which have even been in the news the last few days due to acquisitions.<p>I&#x27;m not going to endorse any of them over the others, but I will say you&#x27;ll be better off using a 3rd party than coding this yourself.

The quickest and easiest thing to do would be to hook up Segment or a similar system (heap analytics, google analytics, etc). I would stay away from GA given my own choice though. It’s free but google won’t give your own data back to you without an enterprise agreement which runs 6 figures minimum. For open source there’s snowplow, which I haven’t used but many in the data community do.

If your analytics are merely a &#x27;nice to have&#x27; but losing a day or two of results would be acceptable in a crisis, I&#x27;d log everything to Redis and then run a daily report to drag aggregated values into another database system. I would clogging your main database system up with analytics related queries on a day to day basis, for sure.

Definitely not a &quot;good talk&#x2F;blog post&#x2F;video course&quot; type of thing, however if you are interested on how we built Countly from ground up, together with the technology stack behind, you can check our source code here:<p><a href="https:&#x2F;&#x2F;github.com&#x2F;countly&#x2F;countly-server" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;countly&#x2F;countly-server</a><p>While we have used MongoDB, Nodejs, Linux as underlying platforms, there are several options out there you may check.<p>Note that some (if not most) of the effort would go into SDK development, and to tell you the truth, SDK development is no easy task as it requires knowledge of how different platforms work.<p>The point is (and take it as a warning): you will never be satisfied with what you have - after you are done with vital data, then there is custom events, raw data, user profiles, online users, and then you will start eating your own dog food as it becomes your part-time job.

Erlang Factory 2014 -- Step By Step Guide to Building an Application Analytics System<p><pre><code> https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=XBuQg1ZElao http:&#x2F;&#x2F;www.erlang-factory.com&#x2F;static&#x2F;upload&#x2F;media&#x2F;1395920416434704ef2014_anton_lavrik.pdf</code></pre>

For such a small scale, you can use a simple event tracking schema from your client-side and server-side code and have a simple stream processor to join these events and then save it to a simple event table in a SQL database. The DB tech you choose should be something suitable for OLAP workloads. For your scale, PostgreSQL or MySQL would just work fine. When your data grows you can look at more distributed systems like Vertica or Memsql or Clickhouse etc.<p>In this architecture, most of your brain cycles will go into designing the queries for generating aggregates at regular intervals from the raw events table and storing in various aggregate tables. You must be familiar with facts and dimensions tables as understood in data warehouse context.

I’ve worked with a GA implementation before which I don’t recommend if you want to own your data or if you want unsampled, detailed logs. I’ve also seen a full end to end implementation that uses server log shipping to s3, log parsing and complicated ETL processes which I also don’t recommend due to the sheer effort it would take to build.<p>I’d say go with something like Matomo (formerly Piwik) <a href="https:&#x2F;&#x2F;matomo.org" rel="nofollow">https:&#x2F;&#x2F;matomo.org</a>. If you wanted to build your own, I’d suggest keeping it simple. Look at Matomo’s architecture and replicate <a href="https:&#x2F;&#x2F;github.com&#x2F;matomo-org&#x2F;matomo" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;matomo-org&#x2F;matomo</a>.

Designing data intensive systems, M. Kleppmann

If it&#x27;s a only few thousand enterprise users, I&#x27;d actually say log to a bog standard relational database from server side like MySQL or Postgres. Think through table schemas for everything you&#x27;re going to log and make sure primary keys and nomenclature for everything talk to each other. Virtually any analytics platform or software talks to standard databases. Record as much as you can because analytics use cases typically get generated following first data collection and analysis and are iterative - so you also want to build flexibility.

I have a slightly adjusted question on any good talks &#x2F; online training programs that touch on digital measurement across channels (media pixel, web analytics, 3rd party data etc). Any pointers?

Some questions for you:<p>- Who will be viewing these reports when they are done? Who do you want to have a view of the data eventually?<p>- How fresh do you need the data to be? Is 24 hours, 4 hours, or 4 seconds okay to wait?<p>- Do you need to be alerted of anomalies in the data?<p>- How long do you intend to store the raw data? Aggregated data?<p>- Does your data need to contain anything that could personally identify a user in order to make a useful analysis? Do you serve customers in the EU?<p>I&#x27;ll check back later today and see if I can provide any insights based on your response.

Ahoy <a href="https:&#x2F;&#x2F;github.com&#x2F;ankane&#x2F;ahoy" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;ankane&#x2F;ahoy</a> is an interesting tool that we use to replace Google Analytics in most projects now.<p>It covers all the basic needs, and even if you&#x27;re not using Rails, I think you can draw inspiration from it!

Since no one is answering the question, this talk by Sonos engineers at AWS re:Invent 2015 is really good:<p><a href="https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=-70wNNrxf6Q" rel="nofollow">https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=-70wNNrxf6Q</a>

Here you go:<p>AWS re:Invent 2017: Building Serverless ETL Pipelines with AWS Glue (ABD315)<p><a href="https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=eQBHIINW8VY&amp;t=2692s" rel="nofollow">https:&#x2F;&#x2F;www.youtube.com&#x2F;watch?v=eQBHIINW8VY&amp;t=2692s</a>

<a href="https:&#x2F;&#x2F;druid.apache.org&#x2F;" rel="nofollow">https:&#x2F;&#x2F;druid.apache.org&#x2F;</a>

AWS ...SFTP-&gt; S3 -&gt; GLUE -&gt; REDSHIFT ... PowerBI, Tableau

I helped build an analytics platform that served more than millions of events. Nothing about it is really difficult <i>until you scale heavily</i>.<p>The tl;dr - It&#x27;s not worth your time &#x2F; energy to build from scratch at this scale. Leveraging an existing standard like the analytics.js spec [1] makes web analytics very easy and quick to get started. With this approach you just have to add one JS snippet to your website and never need to update it in your code. If you are interested in the internals, you might enjoy digging deeper into the spec to understand why it was designed this way.<p>Two services that implement this spec are Segment [2] and MetaRouter [3] [full disclosure: I helped build the product that became MetaRouter at a previous job]. They have different target audiences and pricing models but both are worth a look.<p>You can think of these types of services as a meta analytics service that routes your events to destination analytics services and data stores of your choice. The great thing about using the standard is you can benefit from all of the many integrations that have already been created with various analytics services, databases, data warehouses, etc [4]. These destination catalogs can also help you decide what services to explore and try next as you need more advanced features.<p>To get started with a meta analytics service, in the management dashboard, just add your API keys and config values for each service. For a simple service like Google Analytics this is literally just one simple key to copy and paste.<p>As far as adding custom even monitoring to your site, within the analytics.js spec, first, you mainly want to be concerned with the Track call [5] which is a way to say for an arbitrary event e.g., ProductAddedToCart, I would like to attach this JSON object of properties e.g., a product name and price.<p>And finally, user info like name, email, IP, etc are handled by Identify [6]. You can add custom fields too (traits on an identify are ~= properties to a track event but less transient).<p>Going with an existing standard and SaaS-based approach will save a ton of time and engineering effort.<p>[1]: <a href="https:&#x2F;&#x2F;segment.com&#x2F;docs&#x2F;spec&#x2F;" rel="nofollow">https:&#x2F;&#x2F;segment.com&#x2F;docs&#x2F;spec&#x2F;</a><p>[2]: <a href="https:&#x2F;&#x2F;segment.com&#x2F;" rel="nofollow">https:&#x2F;&#x2F;segment.com&#x2F;</a><p>[3]: <a href="https:&#x2F;&#x2F;www.metarouter.io&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.metarouter.io&#x2F;</a><p>[4]: <a href="https:&#x2F;&#x2F;segment.com&#x2F;docs&#x2F;destinations&#x2F;" rel="nofollow">https:&#x2F;&#x2F;segment.com&#x2F;docs&#x2F;destinations&#x2F;</a><p>[5]: <a href="https:&#x2F;&#x2F;segment.com&#x2F;docs&#x2F;spec&#x2F;track&#x2F;#example" rel="nofollow">https:&#x2F;&#x2F;segment.com&#x2F;docs&#x2F;spec&#x2F;track&#x2F;#example</a><p>[6]: <a href="https:&#x2F;&#x2F;segment.com&#x2F;docs&#x2F;spec&#x2F;identify&#x2F;#example" rel="nofollow">https:&#x2F;&#x2F;segment.com&#x2F;docs&#x2F;spec&#x2F;identify&#x2F;#example</a>