Datenfreigabe 3_3: Hashtagdatenbank (Quelle: Instagram – API)

URL: https://www.pontipix.de/datenbank_downloads/hashtagdb_main.zip 

(Hinweis: gepackt ca. 13GB, entpackt ca. 40GB)

Die DB-Tabelle beinhaltet folgende Spalten:

ID, Tag (Hashtag), Haeufung, Datum

Zur Erläuterung:

ID und Tag / Hashtag sind selbsterklärend. Die Häufung ist die ausgelesene Gesamtanzahl aller Postings unterhalb des Hashtags zum jeweiligen Datum. 

#1 Basisabfragen

(a) Abfrage -> Anzahl: Datensätze

select count(*) from tags;

Ergebnis: 949.781.647

(b) Abfrage -> Anzahl: Datensätze / 2016

select count(*) from tags where datum like ‚%.2016%‘;

Ergebnis: 33.876.572

(c) Abfrage -> Alle Zeitstempel

select distinct datum from tags;

Ergebnis: 423, verteilt auf 2016, 2017, 2018

#2 Inhaltliche Abfragen

(a) Substrings

select * from tags where tag like ‚%leipzig%‘;

(b) Strings

select * from tags where tag = ‚berlin‘;

Für das Auslesen u. ggf. Interpretieren benötigt man eine geeignete Software (SQLite-Studio, Knime etc.). Die Datensätze sind aus 2016 bis 2018 und dienten während der Erhebung für Contentmarketingrecherchen und andere Aufgaben.

Datenfreigabe 2_3 (Quelle: Postingelemente aus Instagram in Verbindung mit Locations)

URL: https://www.pontipix.de/datenbank_downloads/instaloc_clean.zip 

Hinweis (1): 

Die Datenbank ist gepackt 13GB und entpackt 41GB.

Sie umfasst nur einen Teil unseres Datenbestands und ich gab hier eine zufällig ausgewählte Datenmenge je erfassten Länder frei. Technisch wurde der Datenübertrag über einen Zufallsgenerator gelöst, wobei hier kein Themenschwerpunkt gesetzt wurde. 

Die Freigabe erfolgte unter der Bedingung: die Datenbank darf ausschließlich für Weiterbildung- u. Ausbildungszwecke genutzt werden und bei Publikation erwarte ich selbstverständlich eine Namensnennung inkl. Link auf https://www.pontipix.de/.

Hinweis (2):

Die Datenquelle ist Instagram via Instagram-API in der jeweils gültigen Fassung unter Beachtung der jeweils aktiven Richtlinien. 

Die DB-Tabelle beinhaltet folgende Spalten:

id, url, tags, likes, comments, erstellzeit, contentinfo, country, country_code, city, county, state_district, suburb, city_district, pub, cafe, hotel, college, town_hall, florist, arts_centre, public_building, hospital, sport_centre, swimming_pool, travel_agency, community_centre, playground, clinic, books, pedestrian, chemist, car_wash, fort, golf_course, theme_park, water, castle, picnic_site, pharmacy, fast_food, track, garden, locality, residental, adress29, wood, retail, town, industrial, cycleway, village, guest_house, artwork, bus_stop, bar, bakery, fuel, zoo, car, university, neigbourhood, viewpoint, bank, stadium, hairdresser, place_of_worship, beach, mall, school, memorial, attraction, supermarket, footway, restaurant, theatre, parking, clothes, hamlet, raceway, aerodome, library, museum, commercial, ruins, building, PLZ, road

#1 Abfragebeispiele -> Bezug auf Land / Stadt etc. 

select * from locations where country_code = ‚de‘; 

select * from locations where country_code = ‚de‘ and city = ‚Berlin‘; 

select * from locations where country_code = ‚de‘ and city = ‚München‘; 

select * from locations where country_code = ‚de‘ and state = ‚Sachsen‘; 

select distinct country_code from locations;

select distinct city from locations where country_code = ‚de‘;

#2 Abfagebeispiele -> Bezug auf Locationtypen

select * from locations where country_code = ‚de‘ and restaurant <> “; 

select * from locations where country_code = ‚fr‘ and restaurant <> “

select * from locations where country_code = ‚us‘ and school <> “; 

#3 Abfragebeispiele -> Bezug auf Inhalte

select * from locations where country_code = ‚de‘ and school <> “ and contentinfo like ‚%1 person%‘

select * from locations where country_code = ‚de‘ and contentinfo like ‚%1 person%‘

select distinct(contentinfo) from locations where country_code = ‚de‘;

select distinct(tags) from locations where country_code = ‚de‘ and city = ‚Leipzig‘;

Weitere Abfragevarianten ergeben sich aus der Tabellenstruktur, den Spalten und der konkreten Fragestellung. Denkbar sind Verbindungen zwischen Tags, Taggruppen, den Bildinhalten („Contentinfo“) und konkreten Ländern. 

Datenfreigabe 1_3 (Quelle: Instagram-Locations mit Openstreetmap)

URL: https://www.pontipix.de/datenbank_downloads/locplain_big.zip

Hinweis (1): 

Die Datenbank ist gepackt 338MB und entpackt 818MB.

Die Freigabe erfolgte unter der Bedingung: die Datenbank darf ausschließlich für Weiterbildung- u. Ausbildungszwecke genutzt werden und bei Publikation erwarte ich selbstverständlich eine Namensnennung inkl. Link auf https://www.pontipix.de/.

Hinweis (2):

Die Haupt-Datenquelle ist Instagram via Instagram-API in der jeweils gültigen Fassung unter Beachtung der jeweils aktiven Richtlinien. 

Die DB-Tabelle beinhaltet folgende Spalten:

id, locations, lat, long, country, country_code, city, county, state_district, suburb, city_district, pub, cafe, hotel, college, town_hall, florist, arts_centre, public_building, hospital, sport_centre, swimming_pool, travel_agency, community_centre, playground, clinic, books, pedestrian, chemist, car_wash, fort, golf_course, theme_park, water, castle, picnic_site, pharmacy, fast_food, track, garden, locality, residental, adress29, wood, retail, town, industrial, cycleway, village, guest_house, artwork, bus_stop, bar, bakery, fuel, zoo, car, university, neigbourhood, viewpoint, bank, stadium, hairdresser, place_of_worship, beach, mall, school, memorial, attraction, supermarket, footway, restaurant, theatre, parking, clothes, hamlet, raceway, aerodome, library, museum, commercial, ruins, building, PLZ, road

#1 Abfragebeispiele -> Bezug auf Land / Stadt etc. 

select * from locations where country_code = ‚de‘; 

select * from locations where country_code = ‚de‘ and city = ‚Berlin‘; 

select * from locations where country_code = ‚de‘ and city = ‚München‘; 

select * from locations where country_code = ‚de‘ and state = ‚Sachsen‘; 

select distinct country_code from locations;

select distinct city from locations where country_code = ‚de‘;

#2 Abfagebeispiele -> Bezug auf Locationtypen

select * from locations where country_code = ‚de‘ and restaurant <> “; 

select * from locations where country_code = ‚fr‘ and restaurant <> “

select * from locations where country_code = ‚us‘ and school <> “; 

Weitere Abfragevarianten ergeben sich aus der Tabellenstruktur, den Spalten und der konkreten Fragestellung.

Instagram scrapen: ohne Token, Teil 2

Diverse Änderungen zwangen mich zur Recherche nach weiteren Endpoints mit inkl. JSON-Export

1. Suche nach Orten

//www.instagram.com/web/search/topsearch/?context=place&query=%22germany%22

2. Suche nach Accounts (öffentliche)

//www.instagram.com/web/search/topsearch/?context=user&query=%22germany%22

3. Suche nach Orten UND Accounts

//www.instagram.com/web/search/topsearch/?context=blended&query=%22germany%22

4. Suche nach Orten, Variante mit Pseudotoken

https://www.instagram.com/graphql/query/?query_hash=ac38b90f0f3981c42092016a37c59bf7&variables={%22id%22:%22236942553%22,%22first%22:12,%22after%22:%22XXXXXXXX%22}

Notiz: Query_Hash ggf. gegenprüfen und Alternativen organisieren!

Finalisierung von InstaLOC 1.0 (Beta) und weitere Datenauszüge

Vor einigen Tagen konnte ich die Datenerfassungsprozedur „InstaLOC 1.0 (BETA)“ finaliseren und mit den Openstreetmapmetadaten zusammen führen (siehe hierzu die Texte aus dem Blog).

Hier nun einige Basisdatenauszüge:

(1) 5000 beliebteste Tagwolken aus Deutschland
Link: https://bit.ly/2GoHgc1
Bit.ly-Statistik: https://bit.ly/2GoHgc1+

(2) 5000 beliebteste Tagwolken aus Bayern
Link: https://bit.ly/2S6mpeX
Bit.ly-Statistik: https://bit.ly/2S6mpeX+

(3) 5000 beliebteste Tagwolken aus Erfurt
Link: https://bit.ly/2UPRzce
Bit.ly-Statistik: https://bit.ly/2UPRzce+

(4) 5000 beliebteste Tagwolken aus Leipzig
Link: https://bit.ly/2QFKmgw
Bit.ly-Statistik: https://bit.ly/2QFKmgw+

(5) 5000 beliebteste Tagwolken aus Sachsen
Link: https://bit.ly/2Bvv8A3
Bit.ly-Statistik: https://bit.ly/2Bvv8A3+

Das Datenformat ist CSV und die Spalten sind: Tagwolke+Likes+Comments+Anzahl->Tags. Die Sortierung ist Likes->absteigend und die Tagzählung wurde mit der Anweisung length(tag) - length(replace(tag, ',', ''))+1 realisiert.

Bei Interesse an weiteren Einblicken freue ich mich auf einen Anruf oder eine eMail.

InstaLOC + Openstreetmap = Zusammenlegung und Erweiterung der Analyseoptionen

Durch die Zusammenführung beider Datenbanken wird eine wechselseitige Analyse auf Basis von

CREATE TABLE locations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
lat STRING,
long STRING,
name STRING,
osm_type STRING,
house_number STRING,
road STRING,
city STRING,
county STRING,
state_district STRING,
state STRING,
postcode STRING,
country STRING,
country_code STRING,
suburb STRING,
building STRING,
ruins STRING,
city_district STRING,
commercial STRING,
museum STRING,
library STRING,
aerodome STRING,
raceway STRING,
hamlet STRING,
hotel STRING,
clothes STRING,
parking STRING,
theatre STRING,
restaurant STRING,
footway STRING,
residental STRING,
supermarket STRING,
attraction STRING,
memorial STRING,
school STRING,
mall STRING,
beach STRING,
place_of_worship STRING,
hairdresser STRING,
stadium STRING,
bank STRING,
viewpoint STRING,
neigbourhood STRING,
university STRING,
car STRING,
zoo STRING,
fuel STRING,
bakery STRING,
bar STRING,
bus_stop STRING,
artwork STRING,
guest_house STRING,
village STRING,
cycleway STRING,
industrial STRING,
town STRING,
retail STRING,
wood STRING,
adress29 STRING,
residential STRING,
locality STRING,
garden STRING,
track STRING,
fast_food STRING,
pharmacy STRING,
picnic_site STRING,
castle STRING,
water STRING,
theme_park STRING,
golf_course STRING,
fort STRING,
car_wash STRING,
chemist STRING,
pedestrian STRING,
books STRING,
clinic STRING,
playground STRING,
community_centre STRING,
travel_agency STRING,
swimming_pool STRING,
sports_centre STRING,
hospital STRING,
florist STRING,
public_building STRING,
arts_centre STRING,
town_hall STRING,
cafe STRING,
pub STRING,
car_rental STRING,
fire_station STRING,
college STRING,
information STRING,
construction STRING,
viewpoint STRING
);

erlaubt. Themenwelten und Zielgruppen lassen sich nun auf Basis von hinterlegten Hotels, Straßen, PLZ, Stadtteilen oder auch Restaurants anstellen.

Projekt InstaLOC, aktueller Datenbestand (Stand: 27.11.2018)

Heute wurde ein Update der InstaLOC-Systeme veranlasst. Die Hauptdatendatei umfasst nun ~40GB mit folgenden Grundzahlen:

(1) Unique Beiträge: 64.000.547
(2) Unique (codierte) Usernames: 14.980.773
(3) Unique Locations: 2.096.459 (vorher: 1.7 Mio)

Eine Besonderheit wird hier von den Tagwolken eingenommen. Diese sind mit der aktuellen Abfragelogik nicht mehr als „Distinct“ in einer annehmbaren Geschwindigkeit auslesbar. Sehr wahrscheinlich werde ich einen Berechnungsdurchlauf in der kommenden Nacht durchführen lassen.

Das Projekt wurde, nach Rückmeldung(en) der vielen Geschäftsfreund_innen und Kund_innen, umkonzeptioniert, so dass externe Datenquellen angedockt werden können. Hier wurde ein spezielles Problem (final) gelöst, welches so beschrieben werden kann:

Die konkrete Bestimmung von Städten auf Basis der Instagramlocationangaben ist teilweise fehlerhaft / nicht möglich.
Die konkrete Bestimmung von Stadtteilen und deren Vergleichbarkeiten innerhalb EINER Stadt ist auf Basis der Instagramlocationangaben nur eingeschränkt möglich.

Die Ausgangslage wurde nun so entschärft:

(a) Export der Locationdaten in Form einer Distinct-Anweisung.
(b) Extrakt der Angaben „lat“, „long“ und „name“ aus dem Datenexport via for-to-do-schleife.
(c) Übergabe von „lat“ und „long“ an die Openstreetmap-API, Extrakt der Resultate und Speichern in eine neue Datenbankdatei.

Für die nachfolgenden Beispiele gilt:
„The Database made available under the Open Database License: http://opendatacommons.org/licenses/odbl/1.0/. Any rights in individual contents of the database are licensed under the Database Contents License: http://opendatacommons.org/licenses/dbcl/1.0/

Der Datenbestand umfasst (jeweils unique):
164.222 Eintragungen / 1.7 Mio
24.185 Stadtteile
221 Länder zu 203 Ländercodes
1630 States (Bundesländer etc.)
1131 State Districts (Bezirke etc.)
9715 County (Gemeinden, Regionen etc.)

Download Beispieldaten, Überblick (PDF)
Länder+Ländercodes
Länder+Ländercode+Aufzählung-Städte/a>
Berlin+Stadtteil+PLZ
Srilanka+Stadt+Stadtteil+PLZ
China+Stadt+Stadtteil+PLZ
Indien+Stadt+Stadtteil+PLZ
BRD+Stadt+Stadtteil+PLZ
USA+Stadt+Stadtteil+PLZ

Da die Abgleichsprozedur noch aktiv geschaltet ist, werden sich die Werte mit sehr hoher Wahrscheinlichkeit verändern. Interessant ist hier die zukünftige Beobachtung der TOP-Länder aus der 2. Beispieldatei.

Die verbleibenden 1.5 Mio Eintragungen werden „sehr wahrscheinlich“ in den nächsten 1 1/2 Wochen komplett abgearbeitet und mit Finalisierung wird das Recherchekonzept um einen weiteren Prüfpunkt erweitert. Folgende Szenarien sind dann „denkbar“

(a) Gezielte Analyse von Stadtteilen auf:
-> Zielgruppen
-> Trends via Kombination aus Tags, Tagwolken, Summe Likes + Comments, Zeitstempel
(b) Vergleich von Stadtteilen
(c) Vergleich von Städten auch auf Stadtteilebene
(d) Querprüfung auf Städte, Länder, Stadtteile etc. bzgl. der generellen Aktivitäten im Medium „Instagram“

Weitere Ideen, Anregungen oder Lust auf Austausch? Gern unter den bekannten Telefonnummern und office(at)pontipix.de

Datenauszugsfreigabe „InstaLOC“ (Stand: 12.11.2018)

Download: http://pontimania.de/_dbdownload/testauszuege.zip

Inhalt sind 3 Exceltabellen:
(a) Testdaten nach der Tagsuche „Urlaub“
(b) Testdaten nach der Locationsuche „Bayern“
(c) Testdaten nach der Locationsuche „Germany“.

Die Tabellen (b) und (c) beziehen sich auf Substrings, welche in der Spalte „Location“ befinden und die Beispieldatensätze haben ein festgelegtes (!) Limit i.H.v. 50.000 Zeilen. Die Dateien haben die Inhalte: Tags, Anzahl Likes + Comments, Locationangaben, Zeitstempel.

Je nach Abfrage und Projekt lassen sich folgende Fragen beantworten:

(a) Wieviele User schreiben in welchem Zeitfenster über den Urlaub an welchen Orten?
(b) Welche Orte werden an Hotspots von welchen Usergruppen besucht und was wird da geschrieben?
(c) Welche User / Usergruppen neigen zur Trendbildung an welchen Orten und / oder zu welchen Tags (Themen, Themenwelten)?
(d) Welche Orte werden zu welchen Zeitfenstern|Jahreszeiten|Monaten besonders stark frequentiert und was wird da geschrieben?
(e) An welchen Orten machen lokale Marketingkampagnen Sinn? (gemessen am Kommunikationsvolumen, Inhalte)
(f) Welche Emotionen werden mit Orten + Trends verbunden? (gemessen an Tags, Uservolumen, Sprachfarbe, Piktogramme und Fotofilter)
(g) Was wird mit Produkten vebrinden? (gemessen an Tags neben den Branchentags, Locations)
(h) Existiert eine Zielgruppe für mein Produkt? (gem. an Location, Tags, Taggestaltung, Zeitstempel)?
(i) Wo befindet sich meine Zielgruppe? (Query: Tag|Tagkombination auf Location)

Konkretere Antworten auf diese und weitere Fragen beantworte ich gern nach Projektsichtung und Teambesprechungen. Kontakt: office(at)pontipix.de oder via den üblichen Kanälen (siehe Impressum).

Statusupdate. InstaLOC: Zusammenführung und Bereinigung (Duplicate!) der Datenbanken

Heute wurde der vorhandene Datenbestand aus den einzelnen Dateien zusammengeführt und entsprechend gespeichert.
Der Befehl für den technischen Vorgang ist:

INSERT INTO instaloc_main.locations(url,tag,likes,comments,erstellzeit,post_id,username,location,filter,pruefzeit) select url,tag,likes,comments,erstellzeit,post_id,username,location,filter,pruefzeit FROM _main_10112018.locations group by url;

Die Datenbank hat folgende Daten

Speicher: ca 30GB

Analysierbar sind:
49.676.379 Beiträge
36.574.115 Tagwolken
12.705.076 (codierte) Nutzer
1.794.336 Loations
23.358.582 Zeitstempel

Etablierung der Zielgruppenforschung im Instagramumfeld und erste Zahlen aus der praktischen Anwendung

Mit Etablierung der InstaLOC-Datenbanken und den angedockten Zielgruppen- und Marktanalysen wurden zeitgleich diverse Tests mit bestehenden Accounts durchgeführt.
Diese Szenarien sollten klären:

(a) Followerwachstum
(b) Interaktionen auf den Medien, Videos, Stories
(c) Interaktionsqualitäten und Nachhaltigkeiten

Vor ca. 4 Wochen wurden 2 Testaccounts ausgewählt, Gespräche geführt und an das System angedockt.

Die beobachteten Effekte sind:
(a) Followerwachstum (gerundet)

Account: (A)
Thema: Kunst, Art, Fotografie
13.000 Follower -> 26.000 Follower -> 40.000 Follower

Account: (B)
Thema: Coaching/Consulting
1.500 Follower -> 17.000 Follower -> 20.000 Follower

(b) Interaktionen auf die Medien

Account: (A)
Thema: Kunst, Art, Fotografie
Interaktionsspannbreite, Bilder – Likes: 130 bis ~3.000

Interaktionsspannbreite, Videos – Likes + Views

Account: (B)
Thema: Coaching/Consulting
Interaktionsspannbreite, Bilder – Likes: ~350 – ~800

Account (A) entwickelt sich erstaunlich gut und es sind ca. 200-260 dauerhafte Besucher_innen auf die verlinkte Seite identifizierbar. Ebenfalls beobachte ich starke Kommunikation via „DM“, eMail und auch Telefon aus den Spektren der angesprochenen Zielgruppen (hier: Galerien, Fotograf_innen, Kunst allgem.).
Account (B) zeigt eine starke Etablierung der zielgruppenorientierten Kommentare und eine Einpendelung der Interaktionen auf ~800 / Medium. Erste Anfragen via DM und eMails sind ebenfalls nachvollziehbar.

Nach Zahlensichtung, Ergebnisanalysen und diverse Brainstormings wurden letzte Woche weitere Accounts aus dem Bereich „Handel“ und „Home & Living“ in die Strategie überführt. Hier sind Effekte analog zu Account (A) und Account (B) sichtbar.

Interesse an einer Partnerschaft? Fragen?
Gerne unter office(at)pontipix.de
Weitere Informationen unter: https://www.pontimania.de/